Hi Thomas,
Sorry this has taken a while. I have written a test case in Java, as I
fear the problem has to do with parameters in a PreparedStatement, which
I have no way of testing from an SQL script.
Attached is the full Java class. This class is a self contained program
depending only on H2. I have tried with H2 1.1.108. and the problem is
present. Version 1.0.79 is working correctly.
The problem is this: I create a query. When I insert the values into the
query, like "select * from abc where col1 = 'hello'" then it does not
work. When I use parameters, like "select * from abc where col1 = ?",
and use PreparedStatement.setString(1, "Hello"), then it does not work.
This is the output when working correctly:
DB is: H2 version 1.0.79 (2008-09-26)
Driver is: H2 JDBC Driver version 1.0.79 (2008-09-26)
Trying without params...
Thing matched: Car
Finished trying without params...
Trying with params...
Thing matched: Car
Finished trying with params...
This is the output when not working correctly:
DB is: H2 version 1.1.108 (2009-02-28)
Driver is: H2 JDBC Driver version 1.1.108 (2009-02-28)
Trying without params...
Thing matched: Car
Finished trying without params...
Trying with params...
Finished trying with params...
Cheers,
Jesse
Thomas Mueller wrote:
> Hi,
>
> Sorry, it's very hard to reproduce this problem without a test case.
> Could you create one please? It would be great if it's just a simple
> SQL script.
>
> Regards,
> Thomas
>
> On Tue, Feb 24, 2009 at 11:43 AM, Jesse Long <[email protected]> wrote:
>
>> Hi all,
>>
>> I have just tested version 1.0.79 - it works fine. I get the results I
>> expect.
>>
>> Thanks,
>> Jesse
>>
>> Jesse Long wrote:
>>
>>> Hi all,
>>>
>>> I query a database using a PreparedStatement without parameters
>>> (parameters are manually inserted into the sql), and all works fine -
>>> 2 results are returned. When using parameters no result are returned.
>>>
>>> 1.1.107 using log4jdbc just for the sake of debug, result are same
>>> without log4jdbc.
>>>
>>> EZConnection conn = Database.getConnection();
>>> PreparedStatement pth = conn.prepareStatement("SELECT ID FROM ARCHIVE " +
>>> "LEFT JOIN (SELECT INDEXES.ARCHIVE_ID AS ARCHIVE_ID FROM INDEXES INNER
>>> " +
>>> "JOIN INDEX_FIELDS ON INDEXES.FIELD_ID = INDEX_FIELDS.ID WHERE " +
>>> "INDEX_FIELDS.NAME = 'color' AND INDEXES.CONTENT = 'red' GROUP BY " +
>>> "INDEXES.ARCHIVE_ID) j0e859ed2f0c145838333114a1040a101 ON ARCHIVE.ID " +
>>> "= j0e859ed2f0c145838333114a1040a101.ARCHIVE_ID LEFT JOIN (SELECT " +
>>> "INDEXES.ARCHIVE_ID AS ARCHIVE_ID FROM INDEXES INNER JOIN INDEX_FIELDS
>>> " +
>>> "ON INDEXES.FIELD_ID = INDEX_FIELDS.ID WHERE INDEX_FIELDS.NAME =
>>> 'size' " +
>>> "AND INDEXES.CONTENT = '10' GROUP BY INDEXES.ARCHIVE_ID) " +
>>> "j6a1c684412a543e98e0aa5efbe8a1f78 ON ARCHIVE.ID = " +
>>> "j6a1c684412a543e98e0aa5efbe8a1f78.ARCHIVE_ID WHERE " +
>>> "j0e859ed2f0c145838333114a1040a101.ARCHIVE_ID IS NOT NULL AND " +
>>> "j6a1c684412a543e98e0aa5efbe8a1f78.ARCHIVE_ID IS NOT NULL");
>>> try {
>>> ResultSet rs = pth.executeQuery();
>>> try {
>>> while (rs.next()){
>>> System.out.println("got result: " + rs.getString(1));
>>> }
>>> ...
>>>
>>> LOG:
>>>
>>> 11000 [http-8080-1] INFO jdbc.resultset - 1. ResultSet.new ResultSet
>>> returned
>>> 11000 [http-8080-1] INFO jdbc.audit - 1.
>>> PreparedStatement.executeQuery() returned
>>> net.sf.log4jdbc.resultset...@1237512
>>> 11000 [http-8080-1] INFO jdbc.resultset - 1. ResultSet.next() returned
>>> true
>>> 11000 [http-8080-1] INFO jdbc.resultset - 1. ResultSet.getString(1)
>>> returned bc17719d-63af-48eb-b9c8-b177bc0c43de
>>> got result: bc17719d-63af-48eb-b9c8-b177bc0c43de
>>> 11000 [http-8080-1] INFO jdbc.resultset - 1. ResultSet.next() returned
>>> true
>>> 11000 [http-8080-1] INFO jdbc.resultset - 1. ResultSet.getString(1)
>>> returned 38173f2c-571e-4e22-8086-44a534f16fe3
>>> got result: 38173f2c-571e-4e22-8086-44a534f16fe3
>>> 11000 [http-8080-1] INFO jdbc.resultset - 1. ResultSet.next() returned
>>> false
>>> 11000 [http-8080-1] INFO jdbc.resultset - 1. ResultSet.close() returned
>>> 11000 [http-8080-1] INFO jdbc.audit - 1. PreparedStatement.close()
>>> returned
>>>
>>>
>>> EZConnection conn = Database.getConnection();
>>> PreparedStatement pth = conn.prepareStatement("SELECT ID FROM ARCHIVE " +
>>> "LEFT JOIN (SELECT INDEXES.ARCHIVE_ID AS ARCHIVE_ID FROM INDEXES INNER
>>> " +
>>> "JOIN INDEX_FIELDS ON INDEXES.FIELD_ID = INDEX_FIELDS.ID WHERE " +
>>> "INDEX_FIELDS.NAME = ? AND INDEXES.CONTENT = ? GROUP BY " +
>>> "INDEXES.ARCHIVE_ID) j0e859ed2f0c145838333114a1040a101 ON ARCHIVE.ID " +
>>> "= j0e859ed2f0c145838333114a1040a101.ARCHIVE_ID LEFT JOIN (SELECT " +
>>> "INDEXES.ARCHIVE_ID AS ARCHIVE_ID FROM INDEXES INNER JOIN INDEX_FIELDS
>>> " +
>>> "ON INDEXES.FIELD_ID = INDEX_FIELDS.ID WHERE INDEX_FIELDS.NAME = ? " +
>>> "AND INDEXES.CONTENT = ? GROUP BY INDEXES.ARCHIVE_ID) " +
>>> "j6a1c684412a543e98e0aa5efbe8a1f78 ON ARCHIVE.ID = " +
>>> "j6a1c684412a543e98e0aa5efbe8a1f78.ARCHIVE_ID WHERE " +
>>> "j0e859ed2f0c145838333114a1040a101.ARCHIVE_ID IS NOT NULL AND " +
>>> "j6a1c684412a543e98e0aa5efbe8a1f78.ARCHIVE_ID IS NOT NULL");
>>> try {
>>> pth.setString(1, "color");
>>> pth.setString(2, "red");
>>> pth.setString(3, "size");
>>> pth.setString(4, "10");
>>> ResultSet rs = pth.executeQuery();
>>> try {
>>> while (rs.next()){
>>> System.out.println("got result: " + rs.getString(1));
>>> }
>>> ...
>>>
>>> LOG:
>>>
>>> 8190 [http-8080-1] INFO jdbc.resultset - 1. ResultSet.new ResultSet
>>> returned
>>> 8190 [http-8080-1] INFO jdbc.audit - 1.
>>> PreparedStatement.executeQuery() returned
>>> net.sf.log4jdbc.resultset...@893e69
>>> 8190 [http-8080-1] INFO jdbc.resultset - 1. ResultSet.next() returned
>>> false
>>> 8190 [http-8080-1] INFO jdbc.resultset - 1. ResultSet.close() returned
>>> 8190 [http-8080-1] INFO jdbc.audit - 1. PreparedStatement.close()
>>> returned
>>>
>>>
>>> Any help would be appreciated.
>>>
>>> Thanks,
>>> Jesse
>>>
>>>
>>
>
> >
>
> !DSPAM:49a5aac59181193727852!
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.UUID;
public class Main
{
public static void main(String[] args) throws SQLException, ClassNotFoundException
{
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:/tmp/" +
UUID.randomUUID().toString(), "sa", "sa");
try {
System.out.println("DB is: " + conn.getMetaData().getDatabaseProductName() +
" version " + conn.getMetaData().getDatabaseProductVersion());
System.out.println("Driver is: " + conn.getMetaData().getDriverName() +
" version " + conn.getMetaData().getDriverVersion());
Statement sth = conn.createStatement();
try {
sth.executeUpdate("create table things ( " +
"id uuid not null default random_uuid() primary key, " +
"name varchar not null unique " +
")");
sth.executeUpdate("create table attributes ( " +
"id uuid not null default random_uuid(), " +
"name varchar not null unique " +
")");
sth.executeUpdate("create table thing_attributes ( " +
"thing_id uuid not null references things(id) " +
"on delete cascade on update cascade, " +
"attribute_id uuid not null references attributes(id) " +
"on delete cascade on update cascade, " +
"value varchar not null" +
")");
sth.executeUpdate("insert into attributes (name) values ('Color')");
sth.executeUpdate("insert into attributes (name) values ('Size')");
sth.executeUpdate("insert into things (name) values ('Car')");
sth.executeUpdate("insert into things (name) values ('Plane')");
sth.executeUpdate("insert into thing_attributes (thing_id, attribute_id, value) " +
"select things.id as thing_id, attributes.id as attribute_id, 'red' as value " +
"from things, attributes " +
"where attributes.name = 'Color' and things.name = 'Car'");
sth.executeUpdate("insert into thing_attributes (thing_id, attribute_id, value) " +
"select things.id as thing_id, attributes.id as attribute_id, '1' as value " +
"from things, attributes " +
"where attributes.name = 'Size' and things.name = 'Car'");
}finally{
try {
sth.close();
}catch (SQLException e){
System.err.println("Error closing Statement");
e.printStackTrace(System.err);
}
}
System.out.println("Trying without params...");
PreparedStatement pth1 = conn.prepareStatement(
"SELECT name as NAME FROM things " +
"LEFT JOIN (" +
"SELECT " +
"thing_attributes.thing_id AS thing_id " +
"FROM thing_attributes " +
"INNER JOIN attributes " +
"ON thing_attributes.attribute_id = attributes.id " +
"WHERE " +
"attributes.name = 'Color' " +
"AND thing_attributes.value = 'red' " +
"GROUP BY thing_attributes.thing_id " +
") j842e7075ca7e483a808de2b5879ed242 " +
"ON things.id = j842e7075ca7e483a808de2b5879ed242.thing_id " +
"LEFT JOIN (" +
"SELECT " +
"thing_attributes.thing_id AS thing_id " +
"FROM thing_attributes " +
"INNER JOIN attributes " +
"ON thing_attributes.attribute_id = attributes.id " +
"WHERE " +
"attributes.name = 'Size' " +
"AND thing_attributes.value = '1' " +
"GROUP BY thing_attributes.thing_id " +
") jaa55c505d2a049ae95849044345e426c " +
"ON things.id = jaa55c505d2a049ae95849044345e426c.thing_id " +
"WHERE " +
"j842e7075ca7e483a808de2b5879ed242.thing_id IS NOT NULL " +
"AND jaa55c505d2a049ae95849044345e426c.thing_id IS NOT NULL");
try {
ResultSet rs = pth1.executeQuery();
while (rs.next()){
System.out.println("Thing matched: " + rs.getString("NAME"));
}
}finally{
try {
pth1.close();
}catch (SQLException e){
System.err.println("Error closing PreparedStatement");
e.printStackTrace(System.err);
}
}
System.out.println("Finished trying without params...");
System.out.println("Trying with params...");
PreparedStatement pth2 = conn.prepareStatement(
"SELECT name as NAME FROM things " +
"LEFT JOIN (" +
"SELECT " +
"thing_attributes.thing_id AS thing_id " +
"FROM thing_attributes " +
"INNER JOIN attributes " +
"ON thing_attributes.attribute_id = attributes.id " +
"WHERE " +
"attributes.name = ? " +
"AND thing_attributes.value = ? " +
"GROUP BY thing_attributes.thing_id " +
") j842e7075ca7e483a808de2b5879ed242 " +
"ON things.id = j842e7075ca7e483a808de2b5879ed242.thing_id " +
"LEFT JOIN (" +
"SELECT " +
"thing_attributes.thing_id AS thing_id " +
"FROM thing_attributes " +
"INNER JOIN attributes " +
"ON thing_attributes.attribute_id = attributes.id " +
"WHERE " +
"attributes.name = ? " +
"AND thing_attributes.value = ? " +
"GROUP BY thing_attributes.thing_id " +
") jaa55c505d2a049ae95849044345e426c " +
"ON things.id = jaa55c505d2a049ae95849044345e426c.thing_id " +
"WHERE " +
"j842e7075ca7e483a808de2b5879ed242.thing_id IS NOT NULL " +
"AND jaa55c505d2a049ae95849044345e426c.thing_id IS NOT NULL");
try {
pth2.setString(1, "Color");
pth2.setString(2, "red");
pth2.setString(3, "Size");
pth2.setString(4, "1");
ResultSet rs = pth2.executeQuery();
while (rs.next()){
System.out.println("Thing matched: " + rs.getString("NAME"));
}
}finally{
try {
pth2.close();
}catch (SQLException e){
System.err.println("Error closing PreparedStatement");
e.printStackTrace(System.err);
}
}
System.out.println("Finished trying with params...");
}finally{
try {
conn.close();
}catch (SQLException e){
System.err.println("Error closing Connection");
e.printStackTrace(System.err);
}
}
}
}
