We have a process that creates a bunch of purchases and invoices and we use
the last_insert_id() function to put the correct invoice number in our
purchase records. Occasionally (one in about 10,000) an incorrect number is
inserted in this field, which looks like it comes from another table. This
makes me think that the MySQL connection is being shared by another process.
Will Lucee ever share a database connection, whilst processing a single
script, with other concurrent processes, or is there a way we can specify
that this shouldn’t occur?
I’ve experienced the same situation on MS SQL and MySQL using various “get
the recently inserted row id” techniques.
What I do that seems to have worked 100% of the time. I know it’s nowhere
near the most efficient method and I cringe at having to use a serializable
isolation, but the syntax works for me across different SQL engines so I
don’t have to modify my code depending on engine.
Assuming table Invoices has an Auto-Increment field called InvoiceID
insert into Invoices (Ref,Amy) values ('12345-123',150.23)
select Max(InvoiceID) as NewID from Invoices
The extra query’s performance is usually trivial, if you tend to have
“artificial” primary keys - in the above example InvoiceID would not only
be auto-increment, but also the only field in your Primary Key or at least
the first field in your Primary Key field list.
Connections are hold in a pool and every time you do a is
executed it get a connection from the pool, this can be the same or not, to
have a exclusive connection use cftransaction…
MichaAm Mittwoch, 4. März 2015 schrieb Simon Goldschmidt :
We have a process that creates a bunch of purchases and invoices and we
use the last_insert_id() function to put the correct invoice number in our
purchase records. Occasionally (one in about 10,000) an incorrect number is
inserted in this field, which looks like it comes from another table. This
makes me think that the MySQL connection is being shared by another process.
Will Lucee ever share a database connection, whilst processing a single
script, with other concurrent processes, or is there a way we can specify
that this shouldn’t occur?
I found using myQueryResult.GENERATEDKEY very reliable across connections
and multiple users. Transactions should really be used only when really
necessary.Il giorno mercoledì 4 marzo 2015 08:06:42 UTC+1, Michael Offner ha scritto:
You can also use the result attribute to get this info
Micha
Am Mittwoch, 4. März 2015 schrieb Joe Matte :
I’ve experienced the same situation on MS SQL and MySQL using various
“get the recently inserted row id” techniques.
What I do that seems to have worked 100% of the time. I know it’s nowhere
near the most efficient method and I cringe at having to use a serializable
isolation, but the syntax works for me across different SQL engines so I
don’t have to modify my code depending on engine.
Assuming table Invoices has an Auto-Increment field called InvoiceID
insert into Invoices (Ref,Amy) values ('12345-123',150.23)
select Max(InvoiceID) as NewID from Invoices
The extra query’s performance is usually trivial, if you tend to have
“artificial” primary keys - in the above example InvoiceID would not only
be auto-increment, but also the only field in your Primary Key or at least
the first field in your Primary Key field list.
So I’d try to use a jdbc datasource installing the jdbc driver in your
system or inside lucee.
That’s my 2 cents, and really just 2 cents, I hope someone more experienced
can help you better. So far mysql+lucee (or railo) returns the generated
key correctly, as you described.
Good luck!Il giorno giovedì 12 marzo 2015 21:59:06 UTC+1, Eric Reeves ha scritto:
Joining the game a bit late… just started looking into migrating to
Lucee from ACF.
Is there any kind of trick to getting the result attribute to have the
GENERATEDKEY?
I’ve been taking an insert query that was working in ACF, and it doesn’t
seem to return GERENATEDKEY at all on Lucee. I’m using the Oracle thin
client and simply running the following code:
myQuery isn’t defined (as it should be for an insert query), and myResult
is a struct that contains cached, executionTime, executionTimeNano,
RECORDCOUNT, and SQL. The result struct from the exact same query in ACF
also contains ROWID and GENERATEDKEY.
Joining the game a bit late… just started looking into migrating to Lucee
from ACF.
Is there any kind of trick to getting the result attribute to have the
GENERATEDKEY?
I’ve been taking an insert query that was working in ACF, and it doesn’t
seem to return GERENATEDKEY at all on Lucee. I’m using the Oracle thin
client and simply running the following code:
myQuery isn’t defined (as it should be for an insert query), and myResult
is a struct that contains cached, executionTime, executionTimeNano,
RECORDCOUNT, and SQL. The result struct from the exact same query in ACF
also contains ROWID and GENERATEDKEY.