MySQL connections

[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

[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

<cftransaction isolation="serializable">
<cfquery name="qryInsertInvoice">
insert into Invoices (Ref,Amy) values ('12345-123',150.23)
</cfquery>
<cfquery name="qryGetID">
select Max(InvoiceID) as NewID from Invoices
</cfquery>
</cftransaction>
<cfset pvtNewID = qryGetID.NewID>

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

[Michael Offner]

Connections are hold in a pool and every time you do a <cfquery> is executed it get a connection from the pool, this can be the same or not, to have a exclusive connection use cftransaction...

Micha 

--
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+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/7a60174e-5521-4cd9-9fc9-848c6f4788d6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Michael Offner]

You can also use the result attribute to get this info

Micha

--
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+un...@googlegroups.com.
To post to this group, send email to lu...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/lucee/5e1e84db-f019-4362-9950-f05420ba503c%40googlegroups.com.

[Simon Goldschmidt]

Super... thanks for the clear directions

[Enrico Rosso]

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

<cftransaction isolation="serializable">
<cfquery name="qryInsertInvoice">
insert into Invoices (Ref,Amy) values ('12345-123',150.23)
</cfquery>
<cfquery name="qryGetID">
select Max(InvoiceID) as NewID from Invoices
</cfquery>
</cftransaction>
<cfset pvtNewID = qryGetID.NewID>

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.

[Eric Reeves]

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:

<cfquery datasource="ecube" name="myQuery" result="myResult">

insert into test_table

(

attribute,

value,

environment

)

values

(

'thisIsEricsTest',

'12345',

'Eric'

)

</cfquery>

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?

[Enrico Rosso]

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!