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