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 -~----------~----~----~----~------~----~------~--~---
