executeSQLScript maximum allowable character vector length error

I am trying to run an sql script which has approximately 2,000 lines and approximately 100,000 characters with the executeSQLScript function

executeSQLScript( DB_conn , "script_name.sql" ) 

and i get the error

Error while executing the SQL script: SQL script exceeds maximum allowable character vector length.

Is there a way to increase the allowed character vector length for sql script that can be run using executeSQLScript ?

Thanks,

Answer 1

[Piyush Kumar]

Hi,

The error you're encountering is due to a limitation in the executeSQLScript function, which cannot process SQL scripts exceeding 25,000 characters. To work around this, you can read and execute the SQL statements in smaller chunks -

% Open the SQL file
fid = fopen('script_name.sql', 'r');

% Connect to the database (replace with your actual database details)
conn = database('', 'username', 'password', 'Vendor', 'MySQL', 'Server', 'localhost', 'PortNumber', 3306);

% Read and execute each SQL statement line-by-line
while ~feof(fid)
    % Read a line from the file
    sqlStatement = fgetl(fid);
    
    % Execute the SQL statement if it's a valid string
    if ischar(sqlStatement) && ~isempty(sqlStatement)
        exec(conn, sqlStatement);
    end
end

% Close the file and the database connection
fclose(fid);
close(conn);

This script processes each line individually, which might not work if your SQL statements span multiple lines. You may need to adjust the logic to handle multi-line statements if necessary.