insert into database and get primary key back
4 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
I would like to use the following query to insert data into a database and get the primary key back:
query = INSERT INTO source(timestamp,latitude,longitude,types_id) OUTPUT INSERTED.ID VALUES ('2015-10-14 06:55:41.000','44.0683','-9.5347','13')
curs = fetch(exec(conn, query0));
This sql statement works in Microsoft SQL server Management studio. When Using matlab the data is inserted into the database, but I don't get the primary key back.
The output is generated using curs = fetch(exec(conn, query0)) and curs.Message: 'Invalid Cursor: A result set was generated for update.'
Does anybody has a suggestion.
0 Commenti
Risposte (1)
Sudhanshu Bhatt
il 28 Ott 2015
Hi Robert van Driel,
I understand that you want to retrieve the ID of the record just inserted using the Database Toolbox commands.
Unfortunately, this functionality is not yet supported by the Database Toolbox.
However, there are two workarounds for retrieving the ID/primary key of the record just inserted.
Workaround 1: You can use "@@IDENTITY" keyword to retrieve the ID of the latest inserted row, for example:
r = exec(conn,'SELECT @@IDENTITY')
r = fetch(r)
Workaround 2: Execute the query on a lower level using:
% Get a low level handle
connectionHandle = conn.Handle;
% Create a new statement
queryStatement = connectionHandle.createStatement;
% executeQuery does allow resultsets as output
resultSet = queryStatement.executeQuery('INSERT INTO table_name(value1, value2,..)")
% Get the first resultset row
resultSet.next;
% Get the first column of the returned resultset
ID = resultSet.getInt(1)
% Clean-up
resultSet.close
queryStatement.close;
If this does not resolve this issue, please create a Technical Support Request by clicking on the link below: Create Technical Support Request
0 Commenti
Vedere anche
Categorie
Scopri di più su Database Toolbox in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!