Hello, there.

I was unable to find any reference to this issue in Google, the mailing list archives or anywhere else. If this is known and resolved, then feel free to shoot me.

PROBLEM

It appears that the Connector/J 3.0.8 does not properly handle updatable prepared statements using table aliases. After executing the statement, Connector/J retrieves meta data about unique keys using the alias as the table name, rather than the actual table name.

SCENARIO

I was trying to write a CMP entity bean using MySQL 3.23 as the back end. My tool of choice is WebSphere Studio Application Developer v5.0.

I created a database, table with primary key and CMP entity bean. I imported the table into WSAD, mapped the CMP fields to the database table, generated the deploy code then ran the IBM Universal Test Client.

I was able to create an entity, but not update it. The error message was

java.sql.SQLException: General error, message from server: "Table 'banking.t1' doesn't exist"

The database name is 'banking'. The table name is 'account'. Here, 't1' is an alias for 'account' as in the statement

SELECT t1.account_number FROM account t1

I was able, after a few hours, to isolate the problem to a repeatable test, which I include at the end of this message.

A PreparedStatement using table aliases works IF it is not updatable. The test shows executing the statement as a default PreparedStatement and as an updatable PreparedStatement. Since a CMP entity bean's finder will find for update, this means that it is impossible to use MySQL as the backend for CMP entity beans from WSAD without tweaking the generated code.

I am interested in any comments anyone has, including how we can fix the problem.

The test follows. It is a JUnit test. To execute it, pass four parameters to the JVM using -D:

* databaseUrl * userName * password * queryString

The query string must be a SELECT statement that uses a table alias. I used the following parameters:

* jdbc:mysql://localhost/banking * jbrains * (hidden) * "select t1.account_number from account t1"

Also included below is a transcript of the queries that WSAD's Universal Test Client sent to MySQL. The "SHOW KEYS" statement is incorrect.

=========================== TestCase ======================== import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException;

import junit.framework.TestCase;

import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;

public class UpdatablePreparedStatementTest extends TestCase { private String databaseUrl; private String userName; private String password; private String queryString; private Connection connection; private PreparedStatement findByPrimaryKeyStatement; private ResultSet findByPrimaryKeyResults;

protected void setUp() { databaseUrl = System.getProperty("databaseUrl"); userName = System.getProperty("userName"); password = System.getProperty("password"); queryString = System.getProperty("queryString"); }

public void testUsingTableAliases() throws Exception { MysqlConnectionPoolDataSource dataSource = new MysqlConnectionPoolDataSource();

dataSource.setUrl(databaseUrl); connection = dataSource.getConnection(userName, password);

tryNonUpdatableStatement(); tryUpdatableStatement(); }

private void tryUpdatableStatement() throws SQLException { findByPrimaryKeyStatement = connection.prepareStatement( queryString, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

findByPrimaryKeyResults = findByPrimaryKeyStatement.executeQuery(); }

private void tryNonUpdatableStatement() throws SQLException { findByPrimaryKeyStatement = connection.prepareStatement(queryString);

findByPrimaryKeyResults = findByPrimaryKeyStatement.executeQuery(); }

protected void tearDown() throws Exception { if (findByPrimaryKeyResults != null) findByPrimaryKeyResults.close();

if (findByPrimaryKeyStatement != null) findByPrimaryKeyStatement.close();

if (connection != null) connection.close(); } } =============================================================

=========================== Queries ========================= 030805 13:48:25 4 Connect jbrains@localhost on 4 Init DB banking 4 Query SHOW VARIABLES 4 Query SET autocommit=1 4 Query SHOW VARIABLES LIKE 'transaction_isolation' 4 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 4 Query SET autocommit=0 4 Query SELECT T1.account_number, T1.balance FROM account T1 WHERE T1.account_number = '123456' 4 Query SHOW KEYS FROM `T1` FROM `banking` 030805 13:48:26 4 Query rollback =============================================================

The SHOW KEYS statement should read SHOW KEYS FROM `ACCOUNT` FROM `BANKING`, since ACCOUNT is the table name.