CODEWITHSUNDEEP
sql-serverdelphiadouniqueidentifier
gator88
gator88

Reputation: 1223

How to retrieve the value of uniqueidentifier generated while insert in Delphi ADO?

Suppose I generate the PK for my SQL Server DB table with the help of newid() function. In Java I can do something like this:

...
String query = "DECLARE @newGuid uniqueidentifier "+
"SET @newGuid = newid() "+
"INSERT INTO myTable(id, stringval) "+
"VALUES (@newGuid, "Hello") "+
"SELECT uid FROM @newGuid";
PreparedStatement ps = conn.prepareStatement(query);
ResultSet rs = ps.executeQuery();
String uid = rs.getString("uid");

But when I try to make that with Delphi+ADO I get stuck cause ADO can either get data from DB (Open method of AdoQuery) or put data to DB (ExecSQL method). So I can't insert new value to the table and get the parameter value afterwards.

Upvotes: 1

Views: 1354

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You are treating @newGuid as if it was a table. Your last row in the query should be:

SELECT @newGuid as uid

Upvotes: 1

Wodzu
Wodzu

Reputation: 6979

You could solve this problem atleast in two ways.

  1. You can put both of your SQL queries into one string (just like you have in your example) and call TADOQuery.Open or TADOQuery.Active := True. it doesn't matter that you have INSERT statement there as long as query returns something.

  2. You can define parameter's direction as pdOutput in ADOQuery.Parameters collection and read value of that parameter after executing the query.

Upvotes: 4

Related Questions