Problem getting the last insert id in my application -> connectiontimed out

[John Bateman]

Hi

I"m getting a "connection timed out" on my web page when I try and add information to the database. This happen regardless if I use a "comm.setautocommit( true) or 'false'.

Also, it's not just ANY information that causes the time out. It's when I add info to my Questions table, then try and get the last_insert_id, then add info into my answers tables. One question has many answers so I have a FK in Answers table to Questions

This 'time out' always happens at the part where I try and execute 'select last_insert_id() as lastID'

My application is in Java, (using the Struts framework) but this isn't really the problem as ALL other functionality on my site works. This could include "inserts", "getById" etcetc.. anything I run against the database seem to work fine so long as I don't us 'last_insert_id()'. Not the interesting this to note is that if I run this function in the mysql client command line (mysql -DmyDB) it also works fine and returns the right information.

Here is an example of my code... If I adjust comments to comment out blocks of code it will ALWAYS fails once I uncomment the line where it's trying to execute the last_inset_id. In addition, everything here is REAL Code (minus all my error checking up to the comment "END REAL CODE"

try {
// Get the connection and turn transaction processing on.
conn = super.getConnection();

conn.setAutoCommit( false );

// Insert the question. (Should be in a transaction);
String sqlCommand = "INSERT INTO Questions ( question, questionTypeID ) VALUES ( ?, ? )";

pStmt = conn.prepareStatement( sqlCommand );
pStmt.setString( 1, pQuestion.getQuestion() );
pStmt.setInt( 2, pQuestion.getQuestionTypeID() );
pStmt.executeUpdate();

// Get the identity of the last entered Question.
sqlCommand = "select last_insert_id() as lastID";
pStmt = conn.prepareStatement( sqlCommand );

// This next line is where it will cause the timeout.
// If I comment from here to end of my script, it runs fin.
rs = pStmt.executeQuery();

int lastQuestionID = -1;
if( rs.next() ) {

lastQuestionID = rs.getInt( "lastID" ); // END OF REAL CODE!

// I do move stuff here like adding my 'answers'.

}
} catch ( Exception e ) {
// I process the exceptions here.
}
conn.commit();


Here is also an example of what driver I am using in Resin for Mysql.

<resource-ref>
<res-ref-name>jdbc/FormsAreUs</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<init-param driver-name="com.caucho.jdbc.mysql.Driver"/>
<init-param url="jdbc:mysql_caucho://localhost:3306/FormsAreUs"/>
<!-- login information omotted for security -->
</resouce-ref>

Understand that ALL Other functionality that calls on this JNDI Datasource works perfectly.

Thank you all for your help.


---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php