-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Mufaddal wrote:
No one has ever reported a bug like this before with version 2.0.14 of the driver, and I can't seem to reproduce it with the code that you given in your bug report. What is your default table type? If it is 'InnoDB', then you are running into the issue that the different connections hold on to different 'snapshots', so a delete on one connection won't be seen in other connections until that connection 'commits', and then the other connection(s) 'commit' as well.PROBLEM with your JDBC driver - mysql connector.
I am developing using jdk1.4.1 on Mac OS X. I am using the mysql
driver - mysql connector java 2.0.14. I am creating a table in mysql
thru java ... the code to do this is below:
try
{
Statement stmt = con.createStatement();
stmt.executeUpdate( "CREATE TABLE PARTICIPANT ("
+
"LOGINID INTEGER NOT NULL, "
+
"USERNAME VARCHAR (20) NOT NULL, " +
"PASSWORD VARCHAR (20) NOT NULL, "
+
"FIRSTNAME VARCHAR (20) NOT NULL, "
+
"LASTNAME VARCHAR (20) NOT NULL,"
+
"EMAILADD VARCHAR (20) NOT NULL,"
+ "ORGANIZATION VARCHAR (20) NOT NULL," +
"ENABLED BIT NOT NULL,"
+
"GRADELEVELS VARCHAR (80)," +
"PRIMARY KEY( LOGINID )"
+
")");
stmt.close();
}
catch(SQLException ex)
{
ex.printStackTrace();
}
The table gets created properly. I check this from the mysql prompt.
Now i insert 2 records in the table above thru my java code . The
code
that does that is below:
try
{
Statement stmt = con.createStatement();
PreparedStatement pstmt = con.prepareStatement("INSERT
INTO PARTICIPANT VALUES"
+
"(?,?,?,?,?,?,?,?,?)");
pstmt.setInt(1, p.getLoginId());
pstmt.setString(2, p.getUserName());
pstmt.setString(3, p.getPassword());
pstmt.setString(4, p.getFirstName());
pstmt.setString(5, p.getLastName());
pstmt.setString(6, p.getEmailAdd());
pstmt.setString(7, p.getOrganizationName());
pstmt.setBoolean(8, p.isEnabled());
pstmt.setString(9, (new
DataTypeConverter()).getString(p.getGradeLevels()));
pstmt.executeUpdate();
pstmt.close();
}
catch(SQLException ex)
{
ex.printStackTrace();
}
For both the above sniplets of code i am using a connection object
from a pool of connections that i create to the database at start of
application startup. These connections are closed when the
application
exits.
PROBLEM:
The records get inserted in a blank table when i use my java code to
do so. When i delete a record from the table from the mysql prompt and
then go back to my java code to insert the same record it gives me
the following error:
java.sql.SQLException: Invalid argument value: Duplicate entry '1' for
key 1
at com.mysql.jdbc.MysqlIO.sendCommand(Unknown Source)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(Unknown Source)
at com.mysql.jdbc.Connection.execSQL(Unknown Source)
at com.mysql.jdbc.PreparedStatement.executeUpdate(Unknown
Source)
at com.mysql.jdbc.PreparedStatement.executeUpdate(Unknown
Source)
at
com.wavesinmotion.cw.classes.db.DBWriter.setParticipant(DBWriter.java:8 5
)
at
com.wavesinmotion.cw.classes.db.DBWriter.main(DBWriter.java:36)
This i dont understand since the record with that primary key i have
already deleted from the mysql prompt.
When i try to enter a record with the same key using the mysql
prompt
the record successfully gets inserted into the table;
I suspect a bug in your jdbc driver .. .whats the solution or fix to
this ?
regards,
Mufaddal.
If there is an issue, it would most likely not be with the driver itself, as the error message is coming from the server, and your code is definitely sending the correct values, because you _expect_ to be able to insert the key value '1' from JDBC. Have you tried performing a similar operation with two different mysql command-prompt connections? What happens?
Have you tried the latest version of the driver (3.0.5). What happens with it? Have you enabled the general query log in your server (starting mysqld with --log) to see exactly what SQL is getting sent?
-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+PEaHtvXNTca6JD8RAptqAJ9pA7gAqvYmHhnJYu80/ohmdxcA8ACeOSz3
/vJZDmdg0ybuR1QLM5PiPqA=
=ogI1
-----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
