[yannick]

6 years, 1 month ago #14719

Hi,

I would like to execute an insert and receive the ID of the insert dataset.

sample query:

INSERT INTO tbl(description)
VALUES('rec1'),
VALUES('rec2');
SELECT LAST_INSERT_ID();

using default mysql settings, this query should return 2.

According to your documentation -> https://community.simplifier.io/doc/current-release/#8319:
Inserts are just possible using the mode execute,
but the execute mode doesn’t return any result.

2 Connector Calls in a row doesn’t work, the result of the SELECT LAST_INSERT_ID() querry always returns 0!

INFO: For the Select LAST_INSERT_ID() query it is necessary that both statements are executed using the same connection ID.

How do i do that?

[anuragkumar]

6 years ago #15435

Up

1

Down

::

Hi Chris,

But I don’t have any defining feature other than the table primary key to do a select based on that.

Regards
Anurag

[Chris Bouveret]

6 years ago #15510

Up

1

Down

::

The best way would be to create a business object and a method for your insert. You can then go ahead and call your SQL Connector. Just du something like that in your method:

try{
var connectorCallInsert = Simplifier.Connector.MySQLConnector.MySQLcall({"mode": "execute","request" : "Your Insert query"});
var connectorCallLastId = Simplifier.Connector.MySQLConnector.MySQLcall({"mode": "query","request" : "SELECT LAST_INSERT_ID();"});
output.lastId = connectorCallLastId;
output.success = true;
} catch (e) {
    output.message = e.message;
    output.success = false;
}

Make sure to add the output parameters “lastId”,”success” and “message” in your Business Object Method
The above BusinessObject executes your insert first and then gets the last Id inserted as output

Hope that helps

[Chris Bouveret]

6 years ago #15575

Up

1

Down

::

maybe you should try this query

SELECT rowid from your_table_name order by ROWID DESC limit 1

let me know if this works for you

[Kahn]

6 years, 1 month ago #14777

Up

0

Down

::

Hello,

as of the moment you can write a stored procedure put in your Simplifier and then call it. Or you write an own business object, where you first insert the row and then use a select with a defining feature and get your, probably auto incremented id.

Cheers

[anuragkumar]

6 years ago #15567

Up

0

Down

::

Hi Team, @chris,
Will the above code snippet with below command apply for Sqlite db as well:
SELECT LAST_INSERT_ID();

Because I have a connector created with below inputs:
“mode”:”query”,
“request”:”SELECT LAST_INSERT_ID()”;

But it give below error message;

{
“message”: “[SQLITE_ERROR] SQL error or missing database (no such function: LAST_INSERT_ID)”,
“success”: false
}

Please have a check and kindly suggest.

Regards
Anurag

[Chris Bouveret]

6 years ago #15568

Up

0

Down

::

Hi,

In SQLite you need to use

SELECT last_insert_rowid()

[anuragkumar]

6 years ago #15570

Up

0

Down

::

@Chris

I tried this just now multiple times and its returning 0 always.

Below is the output

"id": [
{
"last_insert_rowid()": 0
}
]

It should return the primary key of the record inserted.

Am I missing something here, some database config?

[anuragkumar]

6 years ago #15576

Up

0

Down

::

Thanks @Chris,

Its returning the correct id which was last inserted.
Only concern is that will it return another id if someone else did an insert from another application session at the same time?
I hope that its restricted to the current user session only.

[Mathieu Roll]

6 years ago #15624

Up

0

Down

::

Hi,

the problem is that Simplifier is generating a new session each time you call the connector.

so you can’t use the last inserted methods because they are pointing on the session wich is a new one and so you will always get 0 as last inserted id.

with the method chris gave you he is always reading the highest number in the datebase so its not session based.

so yes if an other user inserts something at the same time you will get his id.

a better way to get the id is by using the max function. this will save some performance.

SELECT MAX(id) AS id FROM test;

but same here you will always get the latest id from any user.

[anuragkumar]

6 years ago #15627

Up

0

Down

::

Hi Team, @mathieuroll, @chris

Is there any way I can get the correct id of the inserted record irrespective of other user sessions.
Because its crucial in a parent-child table model in database.

Regards