-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 John Bateman wrote:
HiIs there any reason you're not using MySQL Connector/J? It's much more widely used, and you can access last_insert_id() using Statement.getGeneratedKeys(), which avoids another round-trip to the database.
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.
See http://www.mysql.com/downloads/api-jdbc-dev.html
-Mark
- -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mmma
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE+IbnbtvXNTca6JD8RAhUCAJ4qzQo4ptzn4Bb1yFzf7oRgSWXnJwCgjUjJ
F99apThftxRehvuWvS2IypI=
=sT5x
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
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
