Add multiple rows in a preparedStatement to SQL
11 次查看(过去 30 天)
显示 更早的评论
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?
0 个评论
回答(1 个)
Samay Sagar
2024-2-15
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:
0 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Database Toolbox 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!