Hi,

first of all I am new to using Lazarus/FPC for database communication. I have set up my database connection, transaction and queries via the Object Inspector and all is working as it should. But I have a little problem getting the PK value of a newly inserted record (the PK field is an auto-incremented one). Actually it's not really a problem, I just don't like the way I do it and found no better one.

Here is some code:

SQLQuery.Insert;
SQLQuery.FieldByName('SomeField').AsString:=SomeValue;
SQLQuery.FieldByName('AnotherField').AsString:=AnotherValue;
... (setting more fields)
SQLQuery.Post;
SQLQuery.ApplyUpdates;
SQLTransaction.Commit;
// Now the part I don't like
SQLQuery.Close;
SQLQuery.Params.ParamByName('SomeField').AsString:=SomeValue;
SQLQuery.Params.ParamByName('AnotherField').AsString:=AnotherValue;
... (setting more params, the same as the fields in the insert)
SQLQuery.Open;
NewID:=SQLQuery.FieldByName('AutoIncrementedPrimaryKey').AsInteger;

It seems to work but it's IMHO not very nice.
Ideally I'd like to have TSQLQuery do it for me and offer me some property like SQLQuery.LastInsertID or something along those lines. Is there any way to get the new PK value without manually querying for the inserted record?

BTW the wiki (http://wiki.freepascal.org/Working_With_TSQLQuery) mentions that you should send 0 for the ID and add an appropriate 'ON DUPLICATE KEY' phrase to your SQL. I did not do this. I just omitted the ID in my SQLQuery.InsertSQL and AFAICT it works flawlessly.

R.

I use an own "TPreparedStatement" (equivalent to TSQLQuery prepared + Params, but my structure does not uses TDataset) that I get the last inserted ID with (pseudo codes):

[code]
uses
Core.SQL.PreparedStatement, ...

  VStmt := TPreparedStatement<TMyObject>.Create;
  ID := VStmt.Execute; // get last inserted ID instead of rowsaffected
[/code]

or:

[code]
  VStmt := TPreparedStatement<TMyObject>.Create;
  ID := VStmt.Execute.Values.ID; // get last inserted ID
[/code]

or:

[code]
uses
Core.SQL.ConnectionFactory, ...

  PK := TConnectionFactory.GetInstance(Context).Insert(MyObject);
[/code]

or:

[code]
  TConnectionFactory.GetInstance(Context).Insert(MyObject);
  WriteLn('The generated ID is: ', MyObject.Id);
[/code]

or with on conflict:

[code]
TConnectionFactory.GetInstance(Context).UpdateWithOnConflict(MyObject, TConnection.CONFLICT_REPLACE);

  WriteLn('The generated or existing ID is: ', MyObject.Id);

[/code]

I'll send it soon to the Github. =)

Hi,
Take a look in Greyhound project: https://github.com/mdbs99/Greyhound

Here you can see an example using "last id":
https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285

Regards,
Marcos Douglas

I use an own "TPreparedStatement" (equivalent to TSQLQuery prepared + Params, but my structure does not uses TDataset) that I get the last inserted ID with (pseudo codes):

<snip>
Sorry, you lost me there, but not a problem (see my response to Marcos).

Thanks anyway
R.

Hi, Take a look in Greyhound project: https://github.com/mdbs99/Greyhound

I did and could not see how it helps.

Here you can see an example using "last id": https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285

Yes, if you are using Greyhound, which I am not.

So it looks like there's nothing in TSQLQuery that does the job for me. I now just use another TSQLQuery with the following SQL:

SELECT LAST_INSERT_ID() AS SomeNameIChoose;

Seems to work well, reduces the lines of code as I don't have to set parameters (my primary goal), can be reused for other inserts (being not table dependent) and took about 1 Minute to implement.

Thanks anyways.
R.

It has a code that working with auto-inc.

> Here you can see an example using "last id": > https://github.com/mdbs99/Greyhound/blob/master/test/ghsqltest.pas#L285 Yes, if you are using Greyhound, which I am not.

Yes, I use everyday. I'm the author. ;-)

Best regards,
Marcos Douglas

Connections for databases that have lastinsertID you can call the method created for this:

Function GetInsertID: int64;

There is a plan to let SQLDB retrieve the value of some fields fro the DB, but it is not implemented yet.

Michael.

Hi Michael,

To do that is need to implement a GetInsertID method in all drivers of SQLdb and include a code, like bellow, in TSQLQuery.ApplyRecUpdate:

var
  I: Integer;
  LastId: NativeInt;
  Fld: TField;
begin
  inherited;
  if UpdateKind  ukInsert then
    Exit;
  for I := 0 to Fields.Count -1 do
  begin
    Fld := Fields.Fields[I];
    if Fld.IsNull and
       ((Fld.DataType = ftAutoInc) or (LowerCase(Fld.FieldName) =
'id') and (Fld is TNumericField)) then
    begin
      LastId := FLib.GetLastAutoIncValue;
      if LastId <= 0 then
        Exit;
      if Fld.ReadOnly then
        Fld.ReadOnly := False;
      Edit;
      Fields[I].SetData(@LastId);
      Post;
      Exit;
    end;
  end;
end;

https://github.com/mdbs99/Greyhound/blob/master/src/ghsqldblib.pas#L157

Marcos Douglas

I saw.

The "problem" which I see is just:

Query.SQL.Text := 'SELECT LAST_INSERT_ID() AS SomeNameIChoose;';
Query.Open; << here
ID := Query.Fields[0].AsInt64;

This approach is ugly IMHO.

So I use "INSERT X (FIELDS) VALUES (PARAMS) RETURNING PK", returning the generated ID in same SQL cursor which I used to prepare my statement. This idea is from JDBC and I'm using it daily. Works fine in all popular SGBDs. =)
Even when the SGBD does not provides the "RETURNING" feature, I use the SQL cursor from the database driver to generate it without TDataset.Open.

My complete code works only in FPC 2.7.1+, but in short words I get the generated ID via buffer (pointer) on database driver.

On Fri, Nov 14, 2014 at 10:15 AM, Michael Van Canneyt <

wrote:

[...]

Awesome news! =)

Some branch to follow this new implementations? (trunk?)

Thanks!

No. Not all SQL engines support lastID. Postgres and Firebird don't.

A more general mechanism is needed.

Michael.

It will be implemented in trunk.

Michael.

Connections for databases that have lastinsertID you can call the method created for this: Function GetInsertID: int64;

That is actually exactly what I was looking for.
I was just too dense to realize that it belongs to the connection, not the query and didn't look for it there. Shame on me.

Thanks a lot
R.

I know. For these the method returns 0 or the user need to code some more to work.

If a DMBS does not support transaction, for example, I can't implement transactions on SQLdb? Is better to implement transactions for all DMBS that works and do nothing in few cases that not supported. The same for auto-inc, IMHO.

A more general mechanism is needed.

The code I posted is general, only in TSQLQuery.ApplyRecUpdate method. In my example before, FLib instance (Greyhound's code) would be a driver for SQLdb that will call GetLastAutoIncValue. A code for each DBMS driver.

Marcos Douglas

If you do not put the value, automatically, on register that you included... what the point?
IMHO this is not a general code. The user can implements the same but he will need to put the value on record... manually.

The code I showed before works in any DMBS. Just test it.

Best regards,
Marcos Douglas

No worries. We will implement it, but simply not in the way you suggest.

By the very nature of the problem: GetLastAutoIncValue simply cannot be implemented in general.

In firebird, postgres, these values are generated using a generator/sequence. You don't know the name of the generator, and even if you did, it is impossible to retrieve the value that was used to fill your particular record because the sequence/generator may have been updated several thousands of times by the time your second statement arrives.

The only way to do this correctly is using a RETURNING clause when doing the insert.

The idea is to add [pfRefresh] to providerflags.
When doing an insert, the fields with this flag will be added to the RETURNING clause and used to update the buffer.

Fields of type ftautoInc can be added to this list (i.e. they automatically get pfRefresh in their providerflags)

This approach will work on all SQL databases that support RETURNING. (That should include any SQL database available today)

Michael.

+1

ID/Sequence generation must work out of transactions. It will be a nice new feature in SQLdb! =)

Currently I use it via cursor from the database driver. It is very very fast and is transaction independent. =)

On Fri, Nov 14, 2014 at 10:40 AM, Michael Van Canneyt <

Thanks dude. I'll follow it there! (y)

...as well as could have a trigger for these cases, encapsulating the "problem" in database... but the code in these cases should be different.

You don't know the name of the generator, and even if you did, it is impossible to retrieve the value that was used to fill your particular record because the sequence/generator may have been updated several thousands of times by the time your second statement arrives.

If you use a sequence/generator, you should call it before INSERT. If you use auto-inc, the DMBS have support to return the last id on your session.

The only way to do this correctly is using a RETURNING clause when doing the insert.

This RETURNING clause works for all DBMS?

Nice. But this is only a part of code. How do you will get the last ID and put in record?

Marcos Douglas

I suggest [pfRefreshInsert,pfRefreshUpdate] as in MSEgui. MSEgui (and AFAIK Zeos) also automatically updates a simple master key field by LASTINSERTID if the DB supports it.

Martin

On Fri, Nov 14, 2014 at 11:11 AM, Michael Van Canneyt <

wrote:

[...]

Indded.

Just a suggestion: it would be nice to implement it using interface or abstract class (like ConnectionDef). So if a database X does not provide the RETURNING support, a "mortal programmer" could do that by himself on his own project. =D

E.g:

// implemented in FPC PQConnection.pas, using the RETURNING feature
TPGSequence = class(..., ISQLSequence)
..
  function GetLastId: int64;
// implemented in programmer project, using X feature
TXSequence = class(..., ISQLSequence)
..
  function GetLastId: int64;

So:

PQConnector1.SetSequencer(TPGSequence.Create);

or:

XYZConnector1.SetSequencer(TXSequence.Create);

No problem.
I didn't know MSEGui has it, and the idea to use 2 different flags for insert/update is good :)

Michael.

I know. RETURNING will handle this case correctly.

That is one way, but not obligatory. Mostly it is done in triggers.

This is not a problem. RETURNING handles all cases.

If you use auto-inc, the DMBS have support to return the last id on your session.

I know.

AFAIK Only sqlite does not (maybe it does today).
But that is not a DBMS :-)

This is the complete code, because the "returning" clause will contain the ID.

Your remarks sound like maybe you don't understand/know exactly what RETURNING does ? It was invented exactly to deal with such things.

Michael.

I was planning some fallback mechanism, yes.
And a "class function SupportReturningClause : Boolean" or something similar. Or maybe

TDBCapability = (lastID,returningclause);
TDBCapabilities = set of TDBCapability;

and

Class function DatabaseCapabilities : TDBCapabilities;

I don't really like using interfaces when things can be done more simple.

Michael.

2014-11-14 11:43 GMT-03:00 Michael Van Canneyt :

MySQL also does not implement it

This is the workaround i use today:

var
  Info: TSQLStatementInfo;
  ActualConnection: TSQLConnection;
  InsertQuery: TSQLQuery;
begin
  Result := '';
  if (FConnection is TSQLConnector) then
    ActualConnection := TSQLConnectorAccess(FConnection).Proxy
  else
    ActualConnection := FConnection;
  Info :=
TSQLConnectionAccess(ActualConnection).GetStatementInfo(Query.SQL.Text,
True, stNoSchema);
  InsertQuery := CreateInsertQuery(Query, Info.TableName,
ActualConnection.FieldNameQuoteChars);
  try
    if (ActualConnection is TPQConnection) or (ActualConnection is
TIBConnection) then
    begin
      InsertQuery.SQL.Add(Format('Returning %s', [FPrimaryKey]));
      InsertQuery.Open;
      if InsertQuery.RecordCount > 0 then
         Result := InsertQuery.Fields[0].AsString;
     ActualConnection.Transaction.CommitRetaining;
  end
  else
  begin
    InsertQuery.ExecSQL;
    ActualConnection.Transaction.CommitRetaining;
    if (ActualConnection is TSQLite3Connection) then
      Result := IntToStr(TSQLite3Connection(ActualConnection).GetInsertID)
    else if (ActualConnection is TConnectionName{MySql}) then
      Result := IntToStr(TConnectionName(ActualConnection).GetInsertID);
  end;
  finally
    InsertQuery.Destroy;
  end;
end;

Luiz
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20141114/697d236e/attachment.html>

Hm. I thought it did. Well, it shows that MySQL is even less of a RDBMS than sqlite.

People trusting their data to these "databases" don't care about their data :)

No problem: Like I said, there will be a fallback mechanism.

I understand sometimes you don't have a choice what database to use, so we must cater for that as well.

Michael.

On Fri, Nov 14, 2014 at 11:43 AM, Michael Van Canneyt <

wrote:

[...]

AFAIK Only sqlite does not (maybe it does today). But that is not a DBMS :-)

Yes. It would be nice execute the "last_insert_rowid()" to return it in the same cursor (AFAIK, Android SDK use this mechanism in the nativeExecuteForLastInsertedRowId). So it can be same to the MySQL (LAST_INSERT_ID()). =)

And you can use the TSQLite3Connection.getinsertid method, no? =/

E.g in a simple raw query in MySQL:

===============================================================================================
INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

This will get you back the PRIMARY KEY value of the last row that *you* inserted:

The ID that was generated is maintained in the server on a *per-connection basis*. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column *by that client*.

So this is unaffected by other queries that might be running on the server from other users.

S:
http://stackoverflow.com/questions/17112852/get-the-new-record-primary-key-id-from-mysql-insert-query ===============================================================================================

On Fri, Nov 14, 2014 at 11:47 AM, Michael Van Canneyt <

wrote:

[...]

Good too dude. =)

Protected virtual methods would be welcome too. =)

It works fine in SQLite (my "test" table have ten records and "test2" one record):

procedure TForm1.Button1Click(Sender: TObject);
var
  id1, id2: Int64;
begin
  SQLQuery1.Close;
  SQLQuery1.SQL.Text := 'insert into test (foo) values (''TEST'')';
  SQLQuery1.ExecSQL;
  id1 := SQLite3Connection1.GetInsertID;
  SQLQuery2.Close;
  SQLQuery2.SQL.Text := 'insert into test2 (foo) values (''TEST'');';
  SQLQuery2.ExecSQL;
  id2 := SQLite3Connection1.GetInsertID;
  ShowMessageFmt('ID1: %d; ID2: %d', [id1, id2]);
end;

[Window Title]
project1

[Content]
ID1: 11; ID2: 2

[OK]

I'll test it in MySQL ...

On Fri, 14 Nov 2014 12:49:44 -0300
silvioprog wrote:

<snip>

This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column *by that client*.

Just to clarify, the 'first' here is important as it means that if you insert multiple rows in one statement you will get the first generated id and not the last one as some might expect (see http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id).

R.

Of course. :-)

AFAIK RETURNING is a SQL clause. I know that but I never used before.

You tell me SQLdb will use RETURNING clause if the DMBS have support, right? Well, AFAIK MSSQL does not have support for this... but MSSQL have IDENTITY (auto-inc) fields. How I will use RETURNING on MSSQL? If this clause don't exists -- if already exists, sorry -- how I can get the ID auto-inc?

Please, don't tell me that will be implemented a parser to search a RETURNING clause on SQL, cut this part, execute, do other things to get the ID and paste the RETURNING again. :)

Marcos Douglas

Nono.

What I have in mind is the following:

Add pfRefreshUpdate/pfRefreshInsert for ProviderFLags

Then:

a) If the DB engine supports RETURNING clause (it seems 3 engines do, 3 don't), that will be used to fetch the values. This is the most optimal approach.

b) For engines that do not support "returning" there is the option to specify a refresh SQL statement. (coincidentally: these are the engines that use autoInc type fields instead of sequences)

the 'last_insert_id' parameter for this statement will be detected (using the reserved name or the name of the ftAutoInc field) and filled with the contents of the last generated ID.

This if the engine supports getting the last insert id, this refresh statement will be constructed and automatically used in 2 cases:

1) if pfRefreshUpdate/pfRefreshInsert is found in the list of fields.
2) if no pfRefreshUpdate/pfRefreshInsert is found in the list of fields, but there is 1 ftAutoInc field and the database engine supports getting the last generated ID.

That should cover most, if not all, cases where a refresh is needed, with minimal interference of the programmer...

Michael.

:)

OK

b) For engines that do not support "returning" there is the option to specify a refresh SQL statement. (coincidentally: these are the engines that use autoInc type fields instead of sequences)

This option, to specify a SQL statement, is the programmer that write the SQL? This should come from driver, don't?

OK

That should cover most, if not all, cases where a refresh is needed, with minimal interference of the programmer...

Looks good.

Marcos Douglas

Unfortunately it does not work and returns only 0, while my custom query using LAST_INSERT_ID() works as expected. Debugging this is very hard as Lazarus crashes on me left and right when trying to step through the code and an exception is encountered. I have one connection, one transaction and some queries bound to them on a FPWebModule.

ID1:=MySQL55Connection1.GetInsertID;
SQLQuery1.Open;
ID2:=SQLQuery1.FieldByName('LastInsertID').AsLargeInt;

ID1 is 0 and ID2 the correct id value.

Any ideas what I could be doing wrong?

R.

This is what it does:

function TConnectionName.GetInsertID: Int64;
begin
   CheckConnected;
   Result:=mysql_insert_id(GetHandle);
end;

a) Did you do an insert right before the call to GetInsertID ? b) Is the transaction active after the insert ?
c) The connection transaction should be the same as the transaction of the insert.

Michael.

If the driver can create it: yes.

Michael.

On Fri, Nov 14, 2014 at 3:23 PM, Michael Van Canneyt <

Work fine here (MySQL 5.5 32 bits / Lazarus 1.2.6 r46529 FPC 2.6.4 i386-win32-win32/win64 / Windows 7 64 bits):

unit1.lfm:

object Form1: TForm1
Left = 238
Height = 240
Top = 144
Width = 320
Caption = 'Form1'
ClientHeight = 240
ClientWidth = 320
LCLVersion = '1.2.6.0'
object Button1: TButton
Left = 10
Height = 25
Top = 8
Width = 75
Caption = 'Button1'
OnClick = Button1Click
TabOrder = 0

  end
  object MySQL55Connection1: TMySQL55Connection
    Connected = False
    LoginPrompt = False
    DatabaseName = 'test'
    KeepConnection = False
    Password = 'root'
    Transaction = SQLTransaction1
    UserName = 'root'
    HostName = '127.0.0.1'
    LogEvents = []
    left = 40
    top = 16
  end
  object SQLQuery1: TSQLQuery
    FieldDefs =
    Database = MySQL55Connection1
    Transaction = SQLTransaction1
    Params =
    left = 88
    top = 116
  end
  object SQLTransaction1: TSQLTransaction
    Active = False
    Database = MySQL55Connection1
    left = 147
    top = 45
  end

end

...

unit1.pas:

unit Unit1;
{$mode objfpc}{$H+}
interface
uses
  SysUtils, mysql55conn, sqldb, Forms, Dialogs, StdCtrls;
type
  { TForm1 }
  TForm1 = class(TForm)
    Button1: TButton;
    MySQL55Connection1: TMySQL55Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
  end;
var
  Form1: TForm1;
implementation
{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.SQL.Text := 'insert into test (foo) values (:foo)';
  SQLQuery1.Params.ParamByName('foo').AsString := DateTimeToStr(Now);
  SQLQuery1.ExecSQL;
  ShowMessageFmt('%d', [MySQL55Connection1.GetInsertID]);
end;
end.

Result after three clicks:

[Window Title]
project1

[Content]
4

[OK]

SQL:

-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net

Michael Van Canneyt wrote

By the very nature of the problem: GetLastAutoIncValue simply cannot be implemented in general. In firebird, postgres,

(and Oracle afaik)

IIRC the currval from a sequence is per session, so if you didn't insert again in the same transaction that is ok too.

Moverover there is lastval to avoid knowing the sequence name

from http://www.postgresql.org/docs/8.4/static/functions-sequence.html

lastval

Return the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it fetches the value of the last sequence used by nextval in the current session. It is an error to call lastval if nextval has not yet been called in the current session.

And PostgreSQL provides the RETURNING feature too. =)

a) Did you do an insert right before the call to GetInsertID ? b) Is the transaction active after the insert ? c) The connection transaction should be the same as the transaction of the insert.

Got it working.

MySQL55Connection1.Connected:=True;
SQLTransaction1.Active:=True;
SQLQuery.Active:=True;
SQLQuery.Insert;
SQLQuery.FieldByName(...);
...
SQLQuery.Post;
SQLQuery.ApplyUpdates;
writeln('ID1: ', MySQL55Connection1.GetInsertID);
SQLTransaction1.Commit;
writeln('ID2: ', MySQL55Connection1.GetInsertID);
SQLQuery1.Open;
writeln('ID3: ', SQLQuery1.FieldByName('LastInserID').AsLargeInt);

ID1 and ID3 are correct but ID2 = 0

So GetInsertID works only before the commit.

Thanks
R.

Reimar Grabowski wrote

It seems to work but it's IMHO not very nice. Ideally I'd like to have TSQLQuery do it for me and offer me some property like SQLQuery.LastInsertID or something along those lines. Is there any way to get the new PK value without manually querying for the inserted record?

Do you have control over the database structure? If so, simply change the "auto-increment" fields to standard Int or Int64 data type then use the well known Ambler High/Low method to generate your identify values. This method is also multi-user and multi-insert (multiple inserts one after the other) friendly. See tiOPF's Options/tiOIDInteger.pas unit for an implementation. tiOPF is available on SourceForge.

Alternative (my preferred solution) is to use a GUID as your identify value. In is table and database unique, so opens up a world of opportunity.

There are SO MANY benefits to generating your own identify value BEFORE you insert a record. I have worked for more years than I care to remember with databases, and even with something as hugely popular as MS SQL Server there is inherent issues with auto-increment fields.

My advice, avoid auto-increment as much as possible!

Regards,
- Graeme -

Requires DEFAULT in the corresponding VALUES field though, I don't know if that is cross-db.