Hi,
I have had a problem with using '?' parameters instead of the standard
sql. I assume it should be possible to take any statement, replace
values with ? and set parameters accordingly to transform it into an
equivalent statement. Unfortunately this is not the case (see test
case pasted below).
I got identical result using both 1.2.147 and 1.3.154.
thanks for any help!
mike
----------------test case----------------
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class PokeParameters {
/////////
// JDBC nonsense
////
static{
try {
Class.forName("org.h2.Driver");
} catch (Exception e) {
throw new java.lang.Error("ERROR: failed to load JDBC
H2Database
driver.", e);
}
}
static public void main(String[] args) throws SQLException {
PokeParameters poke = new PokeParameters();
poke.run(false);
System.err.println("---");
poke.run(true);
}
final Connection conn;
PokeParameters() throws SQLException{
this.conn = DriverManager.getConnection("jdbc:h2:mem:aname",
"sa", "");
}
void execute(String sql) throws SQLException{
conn.createStatement().execute(sql);
}
ResultSet executeQuery(String sql) throws SQLException{
return conn.createStatement().executeQuery(sql);
}
void printCol(String sql) throws SQLException{
ResultSet rs = executeQuery(sql);
while(rs.next()){
System.err.println(rs.getString(1));
}
}
void run(boolean param) throws SQLException{
execute("DROP TABLE IF EXISTS t");
execute("CREATE TABLE t (id BIGINT PRIMARY KEY)");
execute("ALTER TABLE t ADD c INTEGER");
execute("INSERT INTO t (id,c) VALUES(1,1)");
execute("INSERT INTO t (id,c) VALUES(2,2)");
execute("INSERT INTO t (id,c) VALUES(3,null)");
printCol("select c from t");
execute("ALTER TABLE t ADD c_temp VARCHAR");
if(!param){
execute("UPDATE t SET c_temp=(CASE WHEN (c = 1) THEN
'A' ELSE CASE
WHEN (c = 2) THEN 'B' ELSE 'C' END END)");
}else{
PreparedStatement ps = conn.prepareStatement(
"UPDATE t SET c_temp=(CASE WHEN (c = ?) THEN ?
ELSE CASE WHEN (c =
?) THEN ? ELSE ? END END)");
ps.setInt(1,1);
ps.setString(2,"A");
ps.setInt(3,2);
ps.setString(4,"B");
ps.setString(5,"C");
ps.execute();
}
execute("ALTER TABLE t DROP COLUMN c");
execute("ALTER TABLE t ALTER COLUMN c_temp RENAME TO c");
System.err.println("-");
printCol("select c from t");
}
}
------------output--------------
1
2
null
-
A
B
C
---
1
2
null
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Unknown data
type: "?, ?"; SQL statement:
UPDATE t SET c_temp=(CASE WHEN (c = ?) THEN ? ELSE CASE WHEN (c = ?)
THEN ? ELSE ? END END) [50004-154]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.value.Value.getHigherOrder(Value.java:312)
at org.h2.expression.Function.optimize(Function.java:1679)
at org.h2.expression.Function.optimize(Function.java:1643)
at org.h2.command.dml.Update.prepare(Update.java:169)
at org.h2.command.Parser.prepare(Parser.java:202)
at org.h2.command.Parser.prepareCommand(Parser.java:214)
at org.h2.engine.Session.prepareLocal(Session.java:426)
at org.h2.engine.Session.prepareCommand(Session.java:374)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1100)
at
org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:243)
at dev.scratch.PokeParameters.run(PokeParameters.java:67)
at dev.scratch.PokeParameters.main(PokeParameters.java:27)
--
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.
