Cursor Not Returned Query if Execute SQL Statement with Return Value

Question

I have an issue when executing query bellow with DBExpress and Delphi XE. I need to get last identity id from executed query :

function TServerDBUtils.ExecuteQueryWithIdentity(ASQLConn: TSQLConnection): Integer;
var
  newSQLQuery: TSQLQuery;
begin
  Result := -1;  
  newSQLQuery := TSQLQuery.Create(nil);
  try
    with newSQLQuery do
    begin
      SQLConnection := ASQLConn;

      SQL.Clear;
      SQL.Add('Insert into SampleTable(uomname) values(' + Quotedstr('bag') +')';

      SQL.Add('Select Scope_Identity()');
      Open;
      Result:= Fields[0].AsInteger;     
    end;
  finally
    FreeAndNil(newSQLQuery);
  end;
end;

I get error "cursor not returned query". I have used same method before, using FireDac & Delphi XE5 and got no error. No, I'm wondering if "open" is not allowed to do such thing in DBExpress. What method I should use? (We have to use DBExpress in our project) I've tried this :

function TServerDBUtils.ExecuteQueryWithIdentity(ASQLConn: TSQLConnection): Integer;
var
  newSQLQuery: TSQLQuery;
begin
  Result := -1;  
  newSQLQuery := TSQLQuery.Create(nil);
  try
    with newSQLQuery do
    begin
      SQLConnection := ASQLConn;

      SQL.Clear;
      SQL.Add('Insert into SampleTable(uomname) values(' + Quotedstr('bag') +')';
      ExecSQL;

      SQL.Clear;
      SQL.Add('Select Scope_Identity()');
      Open;
      Result:= Fields[0].AsInteger;     
    end;
  finally
    FreeAndNil(newSQLQuery);
  end;
end;

And always got null values, maybe because different session. Sorry for my bad english, and thanks in advance for any help.

Update : It works if we used @@identity :

SQL.Add('Insert into SampleTable(uomname) values(' + Quotedstr('bag') +')';
ExecSQL;

SQL.Clear;
SQL.Add('Select @@Identity');
Open;
Result:= Fields[0].AsInteger;

But, there's problem as SQLServer told, that if there was a trigger on that table fired (on Insert), The return value is the last ID of table that trigger inserted.

Use a stored procedure, will make your life easier.

whosrdaddy
– whosrdaddy

2014-10-20 12:13:08 +00:00
Commented Oct 20, 2014 at 12:13 — whosrdaddy
– whosrdaddy, Commented Oct 20, 2014 at 12:13

bummi · Accepted Answer · 2014-10-20 09:29:45Z

3

The most elegant way on SQL-Server might be to use the OUTPUT Clause which is not only capable to return one ID but all new genered one in case of a multipart insert.

INSERT into aTable (aField)
OUTPUT Inserted.ID 
Values ('SomeValue')

If you have got a trigger on your table you will have to define a destination table for your OUTPUT

DECLARE @tmp table (ID int)
INSERT into aTable (aField)
OUTPUT Inserted.ID into @tmp 
Values ('SomeValue')
Select * from @tmp

Another advice would be to use parameters instead of hard coded values.

With TSQLQuery adding SET NOCOUNT ON before the statement will prevent the cursor not returned query error an deliver the expected result:

begin
  SQLQuery1.SQL.text :='SET NOCOUNT ON'
                +#13#10'DECLARE @tmp table (ID int)'
                +#13#10'INSERT into aTable (aField)'
                +#13#10'OUTPUT Inserted.ID into @tmp'
                +#13#10'Values (:P)'
                +#13#10'Select * from @tmp';
  SQLQuery1.Params.ParamByName('P').Value := 'SomeText';
  SQLQuery1.Open;
  Showmessage(SQLQuery1.Fields[0].asString);
end;

edited Oct 20, 2014 at 9:29

answered Oct 20, 2014 at 5:19

bummi

27.4k14 gold badges67 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fmanda Over a year ago

Thank you ,Still the problem is TSQLQuery it self. We have to use TSQLQuery.Open to retrieve any return from query. But TSQLQuery.Open doesn't allow us to execute insert, update, etc. We'll get error "cursor not returned query". And TSQLQuery.ExecSQL can execute insert, update, etc but doesn't have any return value. If we used ExecSQL followed by Open, it's doesn't work because of different scope. CMIIW

fmanda Over a year ago

Thank you very much, 'SET NOCOUNT ON' is the solution. We've tried in our SQL Server Connection Properties and make 'NO COUNT' checked = true. But it doesn't work. But when we use your solution : add 'SET NOCOUNT ON' before primary SQL then followed by Select scope_identity(), this method works. And we'll tried your advice too, we'll try adding output Inserted.ID and generate in our class.

bummi · Accepted Answer · 2014-10-20 12:08:57Z

3

I use to do this using XE2 and SQL Server

with newSQLQuery do
try      
  SQLConnection := ASQLConn;
  SQL.Clear;
  SQL.Add('Insert into SampleTable(uomname) values(' + Quotedstr('bag') +')');
  ExecSQL;

  SQL.Clear;
  SQL.Add('Select Scope_Identity() as id');
  Open;

  if not Eof then
     Result := FieldByName('id').asInteger;
finally
  FreeAndNil(newSQLQuery);
end;

So, I introduce the as keyword on the second SQL statement. When I open the Query, just check for the field with name 'id' that was requested.

edited Oct 20, 2014 at 12:08

bummi

27.4k14 gold badges67 silver badges105 bronze badges

answered Oct 20, 2014 at 11:20

Izuel

4526 silver badges14 bronze badges

1 Comment

fmanda Over a year ago

Thank you, I've tried your solution but the return value always 0. Maybe because ExecSQL and Open is the same session but different scope. So scope_identity() doesn't work. If we used the @@identity, we'll get the result because it will read any scope, but we'll get wrong result if there was a trigger within table that insert into other table.

Collectives™ on Stack Overflow

Cursor Not Returned Query if Execute SQL Statement with Return Value

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related