Prepared Statement bug?
I think there's a bug in the driver.
My JSP code:
<%
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost/outsourcingdocs?autoReconnect=true&useUnicode=true&characterEncoding=utf8",
"username","password");
PreparedStatement stmt = con.prepareStatement("select doc0_.item_id as id, doc0_1_.version as version1_, " +
"doc0_1_.created as created1_, doc0_1_.price as price1_, doc0_1_.sale_price as sale5_1_, " +
"doc0_1_.on_sale as on6_1_, doc0_.name as name3_, doc0_.active as active3_, " +
"doc0_.abstract_text as abstract4_3_" +
" from document_items doc0_ inner join items doc0_1_ on doc0_.item_id=doc0_1_.id" +
" where doc0_.item_id = ?");
stmt.setLong(1,7);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {%>
Doc: <%=rs.getString("id")%> - <%=rs.getString("name3_")%>
<% }
stmt = con.prepareStatement("select childcateg0_.parent_id as parent5___, childcateg0_.id as id__, " +
"childcateg0_.id as id0_, childcateg0_.version as version13_0_, childcateg0_.created as created13_0_," +
" childcateg0_.name as name13_0_, childcateg0_.parent_id as parent5_13_0_ from categories childcateg0_" +
" where childcateg0_.parent_id = ?");
stmt.setLong(1,3);
rs = stmt.executeQuery();
while (rs.next()) {%>
Cat: <%=rs.getString("id__")%> - <%=rs.getString("name13_0_")%>
<% }
stmt.close();
con.close();
%>
Basically, I'm preparing two statement, each with one parameter. In both statements I set the parameter to a valid ID. The first one returns no rows, the second one returns two results.
If I change the queries to normal queries (by typing in the value of each parameter in the place of the "?"), the first query then returns one row.
I'm using Connector/J 3.1.8a and MySQL 4.1.11-nt-max
I tried the same code using Connector/J 3.2alpha, same results.
My workaround will be to append the following to the connection URL of my datasource:
"&useServerPrepStmts=false"