Add multiple rows in a preparedStatement to SQL

Hello together,

I'd like to insert data from Matlab to a Postrgres database. I wrote a query in SQL that works when using within the database:

'INSERT INTO s_lists.t_technologies (name, version_id) VALUES ('Heat pump', 5), ('PV', 5) ON CONFLICT(name) DO UPDATE SET name=EXCLUDED.name RETURNING id;'

The idea is to insert so new technologies, returning their ID (which is auto increasing by default). If the technologies already existed, the ID will still be returned.

I'm now trying to use that statement from within Matlab. However, when I use a preparedStatement:

% My data consists of more than one row that should be inserted
data = {
    'Heat pump', 5;
    'PV',        5};


% Query
query = ['INSERT INTO s_lists.t_technologies (name, version_id) VALUES (?, ?) ON CONFLICT(name) DO UPDATE SET name=EXCLUDED.name RETURNING ID;'];

% Create preparedStatement
statement = databasePreparedStatement(conn, query);

% Bind parameters
statement = bindParamValues(statement, [1, 2], data);

When executing this code, I receive

Error using database.preparedstatement.SQLPreparedStatement/bindParamValues (line 255)
Expected a cell array of scalar values of length 2.

Since I try to insert more than one row. The only alternative that I currently see is to write a huge Query with strcat and execute it with exec().

Is there a way to bind multiple rows or a batch to a preparedStatement in Matlab?

Answer 1

[Samay Sagar]

In order to insert multiple rows while handling potential conflicts, you can iterate over your data and execute the prepared statement for each row. Here’s how you can modify your above code to achieve this:

data = {
    'Heat pump', 5;
    'PV',        5
};

% Your SQL query with placeholders for prepared statement
query = ['INSERT INTO s_lists.t_technologies (name, version_id) ' ...
         'VALUES (?, ?) ON CONFLICT(name) DO UPDATE SET name=EXCLUDED.name RETURNING id;'];

% Assuming `conn` is your database connection object
% Create a preparedStatement
statement = databasePreparedStatement(conn, query);

% Loop through each row of data
for i = 1:size(data, 1)
    % Create a variable to hold the bind parameters for the current row
    bindParams = bindParamValues(statement, [1, 2], data(i, :));
    
    % Execute the prepared statement with the bind parameters
    execResult = fetch(execute(conn, bindParams));
    
end

% Close the prepared statement when done
close(statement);

Alternatively you can also use “sqlwrite” function to add multiple data rows in a table.

Read more about “sqlwrite” here:

https://www.mathworks.com/help/releases/R2020b/database/ug/database.odbc.connection.sqlwrite.html#mw_ba6e1caa-6a4a-4dc9-9b77-c3859df6f17b:~:text=close(conn)-,Insert,-Data%20into%20New