Hi,
Thanks a lot for your test case! I can reproduce the problem now. I
simplified the test case to:
Class.forName("org.h2.Driver");
DeleteDbFiles.execute("data/test", "test", true);
Connection conn = DriverManager.getConnection(
"jdbc:h2:data/test/test");
conn.createStatement().execute(
"create table t(i identity, n varchar) as select 1, 'x'");
PreparedStatement prep = conn.prepareStatement(
"select 1 from dual " +
"inner join(select n from t where i=?) a on a.n='x' " +
"inner join(select n from t where i=?) b on b.n='x'");
prep.setInt(1, 1);
prep.setInt(2, 1);
prep.execute();
conn.close();
This throws an exception. The problem is that the second parameter is
used incorrectly. This will be fixed in the next release of course.
Regards,
Thomas
On Sun, Mar 8, 2009 at 7:15 PM, Jesse Long <[email protected]> wrote:
>
> Sorry, typo, when I insert values into the query, then it does work.
>
> Thanks,
> Jesse
>
> Jesse Long wrote:
>> 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
-~----------~----~----~----~------~----~------~--~---
