Getting Autoinc value after insert with ODBC

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
mika
Posts: 20
Joined: Thu 08 May 2014 06:15

Getting Autoinc value after insert with ODBC

Post by mika » Mon 13 Jan 2020 14:25

Hi,
I was wondering back in 2016 how to get autoinc value after sql insert
viewtopic.php?f=28&t=33967&p=117649#p117649

Is the feature metioned by AlexP implemented? I haven't seen that in version history.
Currently If i so
q.sql.text := 'select id, name from table'; (id is autoinc field)
q.open;
q.append;
q.fieldbynaname('name').asstring := 'xx';
q.post;
I have q.keyfield := 'id';
I get access violation in TDaDatasetUpdater.GetIdentityfieldValue
(my version of unidac is 7.4.12)

Code: Select all

|7FFFFFFE|03     |00000000|0090F58F|uqedit.exe    |0050F58F|DBAccess.pas                          |DBAccess      |TDADataSetUpdater|GetIdentityFieldValue                    |                                         |
|00000060|03     |0019F160|0090F484|uqedit.exe    |0050F484|DBAccess.pas                          |DBAccess      |TDADataSetUpdater|SetIdentityFieldValue                    |                                         |
|00000060|03     |0019F194|009136C4|uqedit.exe    |005136C4|DBAccess.pas                          |DBAccess      |TDADataSetUpdater|PerformAppend                            |                                         |
|00000020|04     |0019F1B0|00830776|uqedit.exe    |00430776|Vcl.DbGrids.pas                       |Vcl.DBGrids   |TCustomDBGrid    |RecordChanged                            |3937[18]                                 |
|00000060|03     |0019F1C4|0086A81E|uqedit.exe    |0046A81E|MemDS.pas                             |MemDS         |TDataSetUpdater  |DoPerformAppend                          |                                         |
|00000020|03     |0019F1E0|00865ABF|uqedit.exe    |00465ABF|MemDS.pas                             |MemDS         |TMemDataSet      |DoPerformAppend                          |                                         |
|00000020|03     |0019F1E4|00850039|uqedit.exe    |00450039|MemData.pas                           |MemData       |TData            |InternalAppend                           |                                         |
|00000020|03     |0019F1EC|00856232|uqedit.exe    |00456232|MemData.pas                           |MemData       |TMemData         |InsertRecord                             |                                         |
|00000020|03     |0019F1F8|0086517F|uqedit.exe    |0046517F|MemDS.pas                             |MemDS         |TMemDataSet      |InternalPost                             |                                         |
|00000020|03     |0019F210|009080FD|uqedit.exe    |005080FD|DBAccess.pas                          |DBAccess      |TCustomDADataSet |InternalPost                             |                                         |
|00000030|04     |0019F330|00803195|uqedit.exe    |00403195|Data.DB.pas                           |Data.DB       |TDataSet         |CheckOperation                           |14599[5]                                 |
|00000020|04     |0019F338|008031A4|uqedit.exe    |004031A4|Data.DB.pas                           |Data.DB       |TDataSet         |CheckOperation                           |14600[6]                                 |
|00000030|04     |0019F358|00802C88|uqedit.exe    |00402C88|Data.DB.pas                           |Data.DB       |TDataSet         |Post                                     |14450[7]                                 |
|00000020|03     |0019F374|00865242|uqedit.exe    |00465242|MemDS.pas                             |MemDS         |TMemDataSet      |Post                                     |                                         |

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Getting Autoinc value after insert with ODBC

Post by Stellar » Mon 27 Jan 2020 11:18

Unfortunately, we can't reproduce the issue. To investigate this behavior of UniDAC, please compose a small sample demonstrating the issue and send it to us, including database objects creating scripts.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

mika
Posts: 20
Joined: Thu 08 May 2014 06:15

Re: Getting Autoinc value after insert with ODBC

Post by mika » Wed 29 Jan 2020 07:26

ok! Thanks for reply. I'll try to do it in next few days!

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Getting Autoinc value after insert with ODBC

Post by Stellar » Wed 29 Jan 2020 10:00

We're still waiting for your example illustrating the issue with retrieving the value of an AUTO INCREMENT field after adding records though the ODBC driver.

mika
Posts: 20
Joined: Thu 08 May 2014 06:15

Re: Getting Autoinc value after insert with ODBC

Post by mika » Wed 05 Feb 2020 17:35

Sorry about the delay. We are small company and i'm forced to other project and didn't have time for this. But Sample will be sent soon. Actually the Access violation has gone, but I still can't get autoincremnt field value after post.
I upgraded UniDac 7.5.13 which helped to AV.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Getting Autoinc value after insert with ODBC

Post by Stellar » Thu 06 Feb 2020 09:31

We're glad that the issue with AV is resolved.
If a dataset contains an AUTO INCREMENT field, UniDAC automatically updates the value of the corresponding field in a recordset after a new record has been added.

If you are using your own INSERT SQL query in the dataset, then to retrieve the inserted record ID, you should :
- set TUniQuery.Options.ReturnParams to True
- in the TUniQuery.BeforeUpdateExecute event specify explicitly the ptInputOutput parameter type for the Identity field

For example:

Code: Select all

DetailUniQuery.Options.ReturnParams := True;
...
procedure TForm1.DetailUniQueryBeforeUpdateExecute(Sender: TDataSet;
StatementTypes: TStatementTypes; Params: TDAParams);
begin
  if StatementTypes = [stInsert] then
    Params.ParamByName('ID').ParamType := ptInputOutput;
end;

mika
Posts: 20
Joined: Thu 08 May 2014 06:15

Re: Getting Autoinc value after insert with ODBC

Post by mika » Thu 06 Feb 2020 16:12

Thanks for reply.
I have two problems with this
1) I have to put 0 to Id field before posting it (with this I can live ). Otherwise I get parameter 'id' not found exception.
2) But after post I get refresh failed, found 0 records.
I have tried with options.InsertAllSetFields := true.

Code: Select all

TUpdateexecutehelper = class
  procedure UniQueryBeforeUpdateExecute(Sender: TDataSet; StatementTypes: TStatementTypes; Params: TDAParams);
end;
procedure test;
var
  lCon: TUniConnection;
  lquery: TUniQuery;
  I: Integer;
  id: integer;
  Helper: TUpdateexecutehelper;
begin
  lCon := TUniConnection.Create( nil );
  lquery := TUniQuery.Create( nil );
  try
    Helper := TUpdateexecutehelper.Create;
    lCon.server := 'demodata';
    lCon.ProviderName := 'ODBC';
    lCon.LoginPrompt := false;
    lCon.Connect;
    lquery.Connection := lCon;
    lquery.SQL.Text := 'select * from  tuition';
    // ID   Degree   Residency   Cost_Per_Credit   Comments
    Lquery.Options.ReturnParams := True;
    lquery.RefreshOptions := [roAfterInsert];
    lquery.Options.InsertAllSetFields := true;
    lquery.BeforeUpdateExecute := Helper.UniQueryBeforeUpdateExecute;
    lquery.Open;
    lquery.KeyFields := 'id';
    lquery.Insert;
    lquery.FieldByName('id').AsVariant := 0;
    lquery.FieldByName( 'comments' ).AsString := TGuid.NewGuid.ToString;
    lquery.FieldByName( 'Cost_Per_Credit' ).asfloat := random( 1000 ) / 10;
    lquery.FieldByName( 'Degree' ).AsString := 'x';
    lquery.FieldByName('Residency').AsBoolean := false;
    lquery.Post;
    // field id should have value
    for I := 0 to lquery.fields.Count - 1 do
      writeln( lquery.fields[ I ].fieldname + ': ' + lquery.fields[ I ].AsString );

  finally
    lquery.Free;
    lCon.Free;
  end;
end;


procedure TUpdateexecutehelper.UniQueryBeforeUpdateExecute(Sender: TDataSet; StatementTypes: TStatementTypes; Params: TDAParams);
begin
  if StatementTypes = [stInsert] then
    Params.ParamByName('ID').ParamType := ptInputOutput;
end;




Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Getting Autoinc value after insert with ODBC

Post by Stellar » Fri 07 Feb 2020 12:00

Could you please specify what ODBC driver you're using?

mika
Posts: 20
Joined: Thu 08 May 2014 06:15

Re: Getting Autoinc value after insert with ODBC

Post by mika » Fri 07 Feb 2020 12:18

Uuh, sorry.
I'm using Actian Zen( or Pervasive PSQL as it was known previously)
version 13.2.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: Getting Autoinc value after insert with ODBC

Post by Stellar » Mon 16 Mar 2020 15:20

Retrieving the value of an auto-incremented field after adding a new record to the database varies across database management systems. You can write your own SQL statement that will return the value of an auto-incremented field after a new record is added.
Here's an example of adding a record in MS SQL Server:

Code: Select all

// TUniQuery.SQLInsert
INSERT INTO Dept
  (DNAME, LOC)
VALUES
  (:DNAME, :LOC)
SET :DEPTNO = IDENT_CURRENT('Dept')

Post Reply