struts-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Bateman <jbate...@tinyntubby.com>
Subject Connection timed out when accessing DB to get 'last insert id'.
Date Sun, 12 Jan 2003 18:24:11 GMT
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.


--
To unsubscribe, e-mail:   <mailto:struts-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail: <mailto:struts-user-help@jakarta.apache.org>


Mime
View raw message