Problems with retrieving auto-increment ID in foreach iteration

Data and Databases
1

I’m running into trouble when trying to get the auto-increment ID inside a foreach loop:

<xsl:for-each select="(1,2)">
    <xsl:variable name="insertQuery">INSERT users SET name="User{.}", created=NOW()</xsl:variable>
    <saxon:do action="sql:execute($dbConnection, $insertQuery)"/>
    <xsl:variable name="newId" select="sql:prepared-query($dbConnection, 'SELECT LAST_INSERT_ID() AS new_id')()?new_id"/>
    <xsl:variable name="updateQuery">UPDATE orders SET user_id={$newId} WHERE order_id={position()}</xsl:variable>
    <saxon:do action="sql:execute($dbConnection, $updateQuery)"/>
    <saxon:do action="$dbConnection?commit()"/>
</xsl:for-each>

During the first iteration everything works fine. The record gets inserted, I retrieve the correct LAST_INSERT_ID(), and the orders table gets updated properly. But in the second iteration, even though the new record is inserted successfully, the LAST_INSERT_ID() query returns the ID from the first iteration instead of the current one.

I think maybe the second prepared query is running out of order or getting cached since the query string is the same. When I add unique text like position() to make each query different, it works correctly. What’s the best approach to make sure the ID query always runs after the insert statement?

2

sounds like saxon might be caching the results. try adding saxon:do action="sql:clearCache($dbConnection)" right after each insert to fix it. wrapping in a transaction block can also help. alternatively, just use sql:query() since you’re not using params here.

3

Saxon’s caching identical prepared statements - that’s your problem. Since your LAST_INSERT_ID() query stays the same each time through the loop, Saxon just reuses the cached result from the first run. You’re right about adding position() to make each query unique. That’ll work.

You could also move the commit outside the loop completely. LAST_INSERT_ID() should stick around for the whole connection/transaction, but committing after each iteration might mess with how it retrieves the ID. Try committing just once after the entire loop finishes.

Or use sql:execute-query() with a dynamic query string that includes something from the current iteration. That way the XSLT processor treats each call as different.

4

Interesting issue! Have you tried storing the insert result directly? Some dbs let you capture the ID right from the insert statement. Also, are you running this on multiple threads or single-threaded? Could be affecting the connection state.