Hi,
At work we use a framework that translates queries in the
object-orientated form to sql, eg.
[implied: select from Company where]
Company.Branch.ContactPerson.PhoneNumber = 'xxx'
to
select company.* from company
inner join branch
on company.id = branch.company_id
inner join contact_person
on branch.contact_person_id = contact_person.id
where contact_person.phone_number = 'xxx'
The framework parses the object orientated query, and builds prepared
statements. Integer literals in the object-orientated query are inserted
as parameters in the jdbc prepared statement, and set using
PreparedStatement.setInt(). I recently saw someone perform a query that
failed:
[implied: select from someObject where]
(1 = 1)
Which was translated to:
select * from sometable where ? = ?
followed by
preparedStatement.setInt(1, 1);
preparedStatement.setInt(2, 1);
H2 threw an exception about unknown data type during optimisation of the
"? = ?" comparison.
Is this type of comparison supported? It works fine on MySQL. I haven't
tested PostgreSQL. We could change the framework to embed the integer
literals in the SQL query string, but we prefer using prepared statement
parameters.
Here is a simple test case and exception.
public class Main
{
public static void main(String[] args) throws
ClassNotFoundException, SQLException
{
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:/tmp/x",
"sa", "");
PreparedStatement pth = conn.prepareStatement("select * from
information_schema.catalogs where ? = ?");
pth.setInt(1, 1);
pth.setInt(2, 1);
pth.executeQuery().close();
pth.close();
conn.close();
}
}
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Unknown data
type: "(?1 = ?2)"; SQL statement:
select * from information_schema.catalogs where ? = ? [50004-126]
at org.h2.message.Message.getSQLException(Message.java:110)
at org.h2.message.Message.getSQLException(Message.java:121)
at org.h2.message.Message.getSQLException(Message.java:74)
at org.h2.expression.Comparison.optimize(Comparison.java:178)
at org.h2.command.dml.Select.prepare(Select.java:720)
at org.h2.command.Parser.prepareCommand(Parser.java:236)
at org.h2.engine.Session.prepareLocal(Session.java:416)
at org.h2.engine.Session.prepareCommand(Session.java:377)
at
org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1066)
at
org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
at
org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:233)
at Main.main(Main.java:24)
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.
