struts-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Robert Taylor" <rtay...@mulework.com>
Subject RE: Connection timed out when accessing DB to get 'last insert id'.
Date Sun, 12 Jan 2003 19:11:55 GMT
John, this is an off topic post and should be labeled as such.

Have you tried writing a unit test outside of Struts with the same code?
Does it fail too?
If so, then I assume last_insert_id() is a prepared statement that has
already been compiled?
Have you tried closing the prepared statement after executing the update and
before
preparing the next statement?
Where exactly does the program hang?
Could your connection pool be at its allocation limit?

Also I don't know if the mySQL driver supports JDBC3.0, but you want to look
at using
PreparedStatement.getGeneratedKeys() after you insert a record. It will
automatically
return the next identity value for that column (if its an auto-increment
column).

robert

> -----Original Message-----
> From: John Bateman [mailto:jbateman@tinyntubby.com]
> Sent: Sunday, January 12, 2003 1:24 PM
> To: Struts Users Mailing List
> Subject: Connection timed out when accessing DB to get 'last insert id'.
>
>
> 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>


--
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