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