Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: FireDAC, MySQL


This question is answered.


Permlink Replies: 4 - Last Post: Apr 9, 2018 5:15 AM Last Post By: Robert Triest Threads: [ Previous | Next ]
Jim Sawyer

Posts: 214
Registered: 1/3/10
FireDAC, MySQL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 8, 2018 4:55 PM
I'm using the code

      AConnection.ExecSQL( 'Insert into CSZ ( Zip, City, State ) values ( :Z, :C, :S )',
        [txtZipcode.Text, txtCity.Text, FieldValues['St']] );


to insert a record into the table CSZ. It has an Auto_Increment primary key. How can
I use the MySQL function

Last_Insert_ID()

to return the value of that last primary key inserted????

Thanks,
Jim Sawyer
Robert Triest

Posts: 687
Registered: 3/24/05
Re: FireDAC, MySQL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 9, 2018 12:14 AM   in response to: Jim Sawyer in response to: Jim Sawyer
Last_Insert_ID()
to return the value of that last primary key inserted????

SELECT LAST_INSERT_ID();
Lajos Juhasz

Posts: 801
Registered: 3/14/14
Re: FireDAC, MySQL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 9, 2018 1:00 AM   in response to: Jim Sawyer in response to: Jim Sawyer
Jim Sawyer wrote:

I'm using the code

      AConnection.ExecSQL( 'Insert into CSZ ( Zip, City, State )
values ( :Z, :C, :S )',         [txtZipcode.Text, txtCity.Text,
FieldValues['St']] ); 


to insert a record into the table CSZ. It has an Auto_Increment
primary key. How can I use the MySQL function

Last_Insert_ID()

to return the value of that last primary key inserted????

Thanks,
Jim Sawyer

I've never used MySQL so this is based on
https://dev.mysql.com/doc/refman/5.7/en/information-functions.html you
should execute a select statement right after the insert:

lId := AConnection.ExecSQLScalar('SELECT LAST_INSERT_ID()', []);
Mark Williams

Posts: 120
Registered: 5/8/10
Re: FireDAC, MySQL  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 9, 2018 4:34 AM   in response to: Jim Sawyer in response to: Jim Sawyer
Jim Sawyer wrote:
I'm using the code

      AConnection.ExecSQL( 'Insert into CSZ ( Zip, City, State ) values ( :Z, :C, :S )',
        [txtZipcode.Text, txtCity.Text, FieldValues['St']] );


to insert a record into the table CSZ. It has an Auto_Increment primary key. How can
I use the MySQL function

Last_Insert_ID()

to return the value of that last primary key inserted????
This will do it without a further query:

v:=AConnection.GetLastAutoGenValue('id');

v is a variant value and 'id' needs to be the name of your auto gen key.
Robert Triest

Posts: 687
Registered: 3/24/05
Re: FireDAC, MySQL
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 9, 2018 5:15 AM   in response to: Mark Williams in response to: Mark Williams
+1
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02