SELECT LAST_INSERT_ID from servoy client - MYSQL

Classic Servoy
1

Hi there,

I have the following code to get the last insert id from a table.

   var SQL = "INSERT INTO seq VALUES(NULL)";
   var done = plugins.rawSQL.executeSQL(globals.topVars.serverName, "seq", SQL);
   var SQL = "SELECT LAST_INSERT_ID() AS invoice_no;";
   var dataset = databaseManager.getDataSetByQuery(globals.topVars.serverName, SQL, null, 1);

Most of the time the last_insert_id value was returned correctly but some time it returned wrong value.

I understand last_insert_id() function is maintained uniquely to a session. So I wonder why I am getting wrong value.

Please help.

Thanks,
Hameed

2

faheemhameed:
I understand last_insert_id() function is maintained uniquely to a session. So I wonder why I am getting wrong value.

“The ID that was generated is maintained in the [MySQL] server on a per-connection basis.” From:

Servoy doesn’t maintain a direct connection from each individual Servoy Client to the MySQL server. Instead it uses connection pooling from Servoy Server to MySQL:

http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-j2ee.html

My guess is that between lines 3 and 5 of your code you aren’t guaranteed to be using the same MySQL connection.

3

David is right, you have no guarantee that the connection will be the same.
To do this properly, define an insert Stored Procedure in MySQL like that:

DELIMITER $

CREATE
    PROCEDURE `insertSeq`()
    BEGIN
	INSERT INTO seq VALUES (NULL);
	SELECT LAST_INSERT_ID();
    END$

DELIMITER ;

Then you will be able to do this:

   	var SQL = "CALL insertSeq();";
   	var dataset = databaseManager.getDataSetByQuery(globals.topVars.serverName, SQL, null, 1);

And you will retrieve the LAST_INSERT_ID() from the exact same connection!

4

Another approach is database transactions.
When you use a db transactions then everything is send over one single connection that holds the transaction.
Of course don’t do this with very lengthy processes and make sure to commit the transaction.

Hope this helps.

5

ROCLASI:
Another approach is database transactions.
When you use a db transactions then everything is send over one single connection that holds the transaction.
Of course don’t do this with very lengthy processes and make sure to commit the transaction.

Hi Robert!

Are you sure that transactions will be valid for calls to the plugins.rawSQL.executeSQL as well?
I’m surprised.

I would say that there is a need for some test cases, unless someone from Servoy can confirm?

6

Hi Patrick,

ptalbot:
Are you sure that transactions will be valid for calls to the plugins.rawSQL.executeSQL as well?

I have to say I haven’t tested this but the releasenotes tell me that since version 3.1.6 the rawSQL plugin uses the database transaction from Servoy client.

7

I have used the stored procedure approach and it seems to work well.

Thanks for all your support.

8

ROCLASI:
Hi Patrick,

ptalbot:
Are you sure that transactions will be valid for calls to the plugins.rawSQL.executeSQL as well?

I have to say I haven’t tested this but the releasenotes tell me that since version 3.1.6 the rawSQL plugin uses the database transaction from Servoy client.

Hi Robert!

I just had a look at the sources of the rawSQL plugin (they are included in the .jar) and indeed the executeSQL and executeStoredProcedure are called inside a transaction if there is one.
That’s nice to know.

Thanks for the tip!