Getting ID value after insert

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
JensFudge
Posts: 35
Joined: Mon 12 Jan 2009 08:37

Getting ID value after insert

Post by JensFudge » Thu 24 Jun 2010 14:44

Hi

I am using SDAC to handle a Delphi app on a MS SQL server.
I want to insert a record in a master table, and thereafter some records in a detail table.
How do I obtain the ID of the master table with SDAC?

The ID field is a identity field (autoinc).

I tried with

query.insert;
query.Fieldbyname('blabla').asString := 'blabla';
query.post;
ID := query.FieldByName('ID').asInteger;

But I always get 0 back in the ID field.

Anyone know how to do this?

Thanks

Jens Fudge

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 24 Jun 2010 15:04

If you use SDAC SQL Generator, check that the value of the TMSQuery.Options.QueryIdentity property is True and you have INDENTITY columns in the result set. In this case an identity field value will be set automatically.
If you use your own SQL query then add this line to the query:
SET :Identity_field = SCOPE_IDENTITY()
In this case you will get the field value as an output parameter.

JensFudge
Posts: 35
Joined: Mon 12 Jan 2009 08:37

Tried that

Post by JensFudge » Fri 25 Jun 2010 07:54

Thanks for the fast respons, but I cannot get it to work. I build the query manually like this:

var
aQry : TMSQuery;
begin
aQry := TMSQuery.Create(nil);

try
aQry.Connection := msConnection;
aQry.SQL.Add('Select booking.BookingID, booking.BilID, Booking.BookingTime, booking.BrugerID, Booking.KundeID, booking.Note');
aQry.SQL.Add('FROM');
aQry.SQL.Add('BOOKING');

aQry.SQLInsert.Add('INSERT INTO booking');
aQry.SQLInsert.Add('(BilID, KundeID, BrugerID, BookingTime, Note)');
aQry.SQLInsert.Add('VALUES');
aQry.SQLInsert.Add('(:BilID, :KundeID, :BrugerID, :BookingTime, :Note)');
aQry.SQLInsert.Add('SET :BookingID = SCOPE_IDENTITY()');

aQry.SQLRefresh.Add('SELECT BilID, KundeID, BrugerID, BookingTime, Note FROM booking');
aQry.SQLRefresh.Add('WHERE');
aQry.SQLRefresh.Add('BookingID = :BookingID');

aQry.Options.QueryIdentity := true;

aQry.Open;
aQry.Append;
aQry.FieldByName('BOOKINGTIME').AsDateTime := now;
aQry.FieldByName('BRUGERID').AsInteger := 12;
aQry.Post;
ShowMessage(intTostr(aQry.FieldByName('BOOKINGID').asInteger));
finally
aQry.Free;
end;
end;


The refresh part I only inserted to try to get it to work, but that didnt make any difference, same with options.queryIdentity := true;
The Identity field is BOOKINGID.

The ShowMessage just show a 0 every time.
I have checked the database, it does in fact insert the record correctly and the BOOKINGID field is correctly filled in.

If I stick the
aQry.RefreshOptions := aQry.RefreshOptions + [roAfterInsert];
in before opening the query, I get an EDatabaseError: "Refresh failed, found 0 records"


Thanks for the help
Jens Fudge

Lithium™
Posts: 42
Joined: Wed 23 Jun 2010 06:42

Post by Lithium™ » Fri 25 Jun 2010 08:40

I'm never have any problems to get identity value after post.
I suspect your problems occurs because two select clauses in your query are presented. You need to call msQuery.Open and then msQuery.OpenNext for second fetch, which just set value to 'BOOKINGID' field.
You must set Options.StrictUpdate to FALSE to avoid exception 'Refresh failed, found 0 records', that raised after first fetch, because this one return nothing.

In general I'm not recommend such query constructions. It's better to execute a separate query to insert records rather than results are selected.

JensFudge
Posts: 35
Joined: Mon 12 Jan 2009 08:37

Post by JensFudge » Fri 25 Jun 2010 09:03

Lithium™ wrote:I'm never have any problems to get identity value after post.
I suspect your problems occurs because two select clauses in your query are presented. You need to call msQuery.Open and then msQuery.OpenNext for second fetch, which just set value to 'BOOKINGID' field.
You must set Options.StrictUpdate to FALSE to avoid exception 'Refresh failed, found 0 records', that raised after first fetch, because this one return nothing.

In general I'm not recommend such query constructions. It's better to execute a separate query to insert records rather than results are selected.
Lithium, thanks for the effort to pitch in here, but I think you missed the point somewhere.
I am trying to insert a record in a table, and get whatever ID MSSQLServer decided to stick in the Identity field.
Lithium™ wrote: I suspect your problems occurs because two select clauses in your query are presented. You need to call msQuery.Open and then msQuery.OpenNext for second fetch
I have only one Select statement..
A TMSQuery's SQL consists of a SQL (for selecting), a InsertSQL, a UpdateSQL, a DeleteSQL and a RefreshSQL.
So there is nothing OpenNext about it
I have no first fetch and secon fetch.. just one fetch alltogether.

I do agree, i should just send INSERT INTO ... to the table, and this would insert a record.. This would however not return the ID in the BOOKINGID field. So just doing a Insert wont work.
I cannot do an insert followed by a Select either, as another user could have inserted a record (or many more for that matter..)

I think however I have solved the problem by inserting an eventhandler on the BEFOREUPDATEEXECUTE event, setting the params.parambyname('BOOKINGID') to a ptInputOutput parameter...

Regards

Jens Fudge

Lithium™
Posts: 42
Joined: Wed 23 Jun 2010 06:42

Post by Lithium™ » Fri 25 Jun 2010 10:19

Oh, I'm sorry, there is missed point realy :oops: .
I've seen "EDatabaseError: Refresh failed, found 0 records" first, and I have such problem early. So I was shown all queries are one select statement... But Options.SrtictUpdate = FALSE you can use for avoid exception above along.

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 29 Jun 2010 08:47

To solve the problem you should set ParamType of the BookingID parameter to ptInputOutput in the TMSQuery.BeforeUpdateExecute event handler, like this:

Code: Select all

Params.ParamByName('BookingID').ParamType := ptInputOutput;

tinof
Posts: 39
Joined: Fri 16 Dec 2005 07:41

Post by tinof » Fri 10 Feb 2012 10:38

Hi,

i have the same problem with SDAC 6.1.6 / Delphi 2009 / MSSQL 2005.
The sample connects with the northwind - database.

If i set the query.sql property to

Code: Select all

 "SELECT * FROM categories"
and leave all properties at default i get the categoryID after an insert as expected.

If i now use the SQL - Query Builder from SDAC i get the i.e. the update statement i get

Code: Select all

INSERT INTO categories
  (CategoryName, Description, Picture)
VALUES
  (:CategoryName, :Description, :Picture)
SET :CategoryID = SCOPE_IDENTITY()
But i never get an CategoryID - value after insert / append now.

- Tfield.autogeneratevalue = arDefault / arNone /arAutoInc --> no affect
- TMSQuery.Options.QueryIdentity = true/false --> no affect
- TMSQuery.BeforeUpdateExecute :
Params.ParamByName('CategoryID').ParamType:=ptInputOutput;
--> no affect

If i delete the Update SQL Statement and leave the "SELECT * FROM .." - it will work.

If i set debug=on i see, that Params.ParamByName('Category_ID').ParamType:=ptInputOutput; influences the final code. But i dont get an ID back.


What's wrong with my code?

Thanks für any Help!
Tino

tinof
Posts: 39
Joined: Fri 16 Dec 2005 07:41

Post by tinof » Fri 10 Feb 2012 12:32

:idea: :idea:
* SOLVED *

we have to set

MSQuery.Options.ReturnParams:=true!

complete:

1. place an TMSQuery, generate the Scripts
2. set Options -> ReturnParams = true
3. edit TMSQuery.BeforeUpdateExecute:

Code: Select all

procedure Tdm_Projekt.queryBeforeUpdateExecute(
  Sender: TCustomMSDataSet; StatementTypes: TStatementTypes; Params: TMSParams);
begin
  If stInsert IN statementTypes then  // otherwise there is no param with this name
    Params.ParamByName('query_ID').ParamType := ptInputOutput;
end;
And you get an ID.

Regards
Tino

AndreyZ

Post by AndreyZ » Fri 10 Feb 2012 14:30

You have found the correct solution.

MosCom
Posts: 4
Joined: Sun 04 Oct 2009 04:03

Re:

Post by MosCom » Tue 29 Aug 2017 13:58

Hi @tinof

I'm going your way, but I have not been successful. I can not get the last number in memory because I do not use INSERT INTO. For this reason, the record gets mixed up when someone else gets the last number.

What exactly should I do on the Delphi side?

Very pleased to be of assistance. Best regards

tinof wrote::idea: :idea:
* SOLVED *

we have to set

MSQuery.Options.ReturnParams:=true!

complete:

1. place an TMSQuery, generate the Scripts
2. set Options -> ReturnParams = true
3. edit TMSQuery.BeforeUpdateExecute:

Code: Select all

procedure Tdm_Projekt.queryBeforeUpdateExecute(
  Sender: TCustomMSDataSet; StatementTypes: TStatementTypes; Params: TMSParams);
begin
  If stInsert IN statementTypes then  // otherwise there is no param with this name
    Params.ParamByName('query_ID').ParamType := ptInputOutput;
end;
And you get an ID.

Regards
Tino

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Getting ID value after insert

Post by ViktorV » Tue 29 Aug 2017 14:32

To solve the issue, please try to set the TMSQuery.Options.QueryIdentity property to True.
If it does not help to solve the problem, please compose a small sample demonstrating the issue and send it to usvia form e-support: https://www.devart.com/company/contactform.html including scripts for creating database objects.

MosCom
Posts: 4
Joined: Sun 04 Oct 2009 04:03

Re: Getting ID value after insert

Post by MosCom » Thu 31 Aug 2017 06:57

Hi @ViktorV,

I am using uniquery and I do not have the QueryIdentity property.

Best Regards.

Image

ViktorV wrote:To solve the issue, please try to set the TMSQuery.Options.QueryIdentity property to True.
If it does not help to solve the problem, please compose a small sample demonstrating the issue and send it to usvia form e-support: https://www.devart.com/company/contactform.html including scripts for creating database objects.

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Getting ID value after insert

Post by ViktorV » Thu 31 Aug 2017 10:02

You can use next code:

Code: Select all

UniQuery.SpecificOptions.Values['SQL Server.QueryIdentity'] := 'True';

Post Reply