[Lazarus] TSQLQuery: Getting autoincremented ID value after insert (MySQL)

[silvioprog]

On Fri, Nov 14, 2014 at 9:53 AM, Reimar Grabowski <reimgrab at web.de> wrote:

> On Thu, 13 Nov 2014 11:33:58 -0300
> silvioprog <silvioprog at gmail.com> wrote:
>
> > 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


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.

-- 
Silvio Clécio
My public projects - github.com/silvioprog
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20141114/29b4c604/attachment-0003.html>