Hi,

I've created a connection pool for myself a time ago, for MySQL and
Postgres. Now I wanted to use that, and I realized, that in same cases
queries does not update the database in auto-commit mode. I have
created a small test that simulates this behaviour. If the bug is in
my code then I really sorry, but I'm not able to track it nor in my
code neither in the source of the driver.

What does it do?

It gets the id of user from a table, changes him password and tries
the get the id again, but now with new password. Methods get their
connections from a connection pool. I've replaced the complicated
connection pool with a simple method: getConnection. On startup it
opens two connections, and once it returns the first connection after
the second and so on.

Every method I use is in auto-commit mode, but I set autocommit to
false on startup, because this bug happens only if the
connections were in use in non-autocommit mode before the "password
change" part run. Of course, before getConnection returns a
connection, it always set autocommit to true.

At some places it may not be clear why I put or not a setAutoCommit,
but the reason is I wanted to make the bug reproducable.



The question is if this code is correct or not? If not, of course it
should not work, but I have not found any part that is against the
JDBC API.

Please help...

Many thanks,
daniel


-------------------8<------------------------

import java.sql.*;
import java.util.*;
import com.devlabs.util.pool.*;

public class JDBCTest {

private static int curr = 0;
private static Connection conn1;
private static Connection conn2;

// to create the table use this:
//
// create table test (
// id int,
// username varchar(10),
// passwd varchar(10)
// );
//
// insert into test values (1, 'daniel', 'daniel');

// This method simulates my connection pool. Of course, the pool is much
// complicated, but it also shows the main idea. Now it has two connections,
// on first call it returns conn1, after conn2, after that conn1 again, and so on
public static Connection getConnection()
{
Connection conn = (curr == 1 ? conn1 : conn2);
try {
curr = curr ^ 1;

// If I set uncomment this line, this example works well, otherwise not.
// with this hack I made my pool work, but I don't understand it...
// conn.setAutoCommit(true);

// This is a simple sql to test, if the database is really working. This
// is not in getConnection in the real connection pool, but if I remove this,
// the example works again.
Statement pst = conn.createStatement();
pst.executeQuery("select 1 from rdb$database");
pst.close();

// be default we set autocommit to true before we give out the connection.
conn.clearWarnings();
if (!conn.getAutoCommit())
conn.setAutoCommit(true);
}
catch (Exception e) {
e.printStackTrace();
}
return conn;
}

public static void releaseConnection(Connection conn)
{
// this is not important, the real pool marks the connection is
// available again.
}

// finds the id for a given username and password
public static int getId(String username, String password)
{
System.out.println("GET ID");
int result = -1;
Connection conn = getConnection();
try {
PreparedStatement pst = conn.prepareStatement("select id from test where username=? and passwd=?");
pst.setString(1, username);
pst.setString(2, password);
ResultSet rs = pst.executeQuery();
if (rs != null && rs.next())
result = rs.getInt(1);
}
catch (Exception error) {
error.printStackTrace();
}
finally {
releaseConnection(conn);
}
return result;
}

// changes the password for ID to a new password
public static int changePassword(long id, String new_password)
{
System.out.println("CHANGE PASSWORD");
int result = 0;
Connection conn = getConnection();
try {
System.out.println("CHANGE PASSWORD: autocommit: " + conn.getAutoCommit());
System.out.println("CHANGE PASSWORD: new password: " + new_password);
PreparedStatement pst = conn.prepareStatement("update test set passwd=? where id=" + id);
pst.setString(1, new_password);
result = pst.executeUpdate();

// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// there is no exception here, but the update is not
// commited, the password is not changed.
}
catch (Exception error) {
error.printStackTrace();
}
finally {
releaseConnection(conn);
}
return result;
}

public static void main(String[] args)
{
try {
Class.forName("org.firebirdsql.jdbc.FBDriver");
conn1 = DriverManager.getConnection("jdbc:firebirdsql:localhost/3050:/programs/develop/firebird/test.gdb",
"sysdba", "masterkey");
// if I remove this, it works, but I don't understand why...
conn1.setAutoCommit(false);
conn2 = DriverManager.getConnection("jdbc:firebirdsql:localhost/3050:/programs/develop/firebird/test.gdb",
"sysdba", "masterkey");
// if I remove this, it works, but I don't understand why...
conn2.setAutoCommit(false);
}
catch (Exception e) {
return;
}

long id = getId("daniel", "daniel"); // we have conn1 here
System.out.println("current id=" + id);
changePassword(id, "asdfgh"); // passwd changes in conn2
id = getId("daniel", "asdfgh"); // we have conn1 here again which does not see what
// happened in conn2. simply it returns -1 for id.
System.out.println("id for the new password=" + id);

try {
conn1.close();
conn2.close();
}
catch (Exception e) {
return;
}
}
}