MySQL connections

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?

Simon

Super… thanks for the clear directions

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.

Joe

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?

Simon


You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+unsubscribe@googlegroups.com
<javascript:_e(%7B%7D,‘cvml’,‘lucee%2Bunsubscribe@googlegroups.com’);>.
To post to this group, send email to lucee@googlegroups.com
<javascript:_e(%7B%7D,‘cvml’,‘lucee@googlegroups.com’);>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/7a60174e-5521-4cd9-9fc9-848c6f4788d6%40googlegroups.com
https://groups.google.com/d/msgid/lucee/7a60174e-5521-4cd9-9fc9-848c6f4788d6%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

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.

Joe


You received this message because you are subscribed to the Google Groups
“Lucee” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to lucee+unsubscribe@googlegroups.com.
To post to this group, send email to lucee@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/lucee/5e1e84db-f019-4362-9950-f05420ba503c%40googlegroups.com
https://groups.google.com/d/msgid/lucee/5e1e84db-f019-4362-9950-f05420ba503c%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

for the little I’ve read about it each engine uses his own method (under
the hoods) to return the generated key.

I know the oracle jdbc client implements such retrieval as describer
here: http://viralpatel.net/blogs/oracle-java-jdbc-get-primary-key-insert-sql/

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:

insert into test_table ( attribute, value, environment ) values ( 'thisIsEricsTest', '12345', 'Eric' )

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.

Any thoughts?

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:

insert into test_table ( attribute, value, environment ) values ( 'thisIsEricsTest', '12345', 'Eric' )

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.

Any thoughts?