Hi,
I would like to report what seems like a bug in H2. I have a 'merge
into' prepared statement with a parameter in the insert sub-statement. I am
using the JDBC driver to send the statement. Following are the details.
First the table on which the query is being invoked:
CREATE TABLE MY_TEST_TABLE
(
ATTR1 VARCHAR2(128) NOT NULL,
ATTR2 VARCHAR2(50),
ATTR3 VARCHAR2(100),
ATTR4 VARCHAR2(100),
ATTR5 VARCHAR2(2000),
ATTR6 NUMBER(10),
ATTR7 TIMESTAMP(6),
ATTR8 VARCHAR2(10),
ATTR9 BLOB,
ATTR10 VARCHAR2(70),
ATTR11 TIMESTAMP,
ATTR12 TIMESTAMP(6),
ATTR13 VARCHAR2(200),
ATTR14 NUMBER(10),
ATTR15 NUMBER(10),
ATTR16 VARCHAR2(100),
ATTR17 VARCHAR2(128)
);
Following is the JAVA code that sends a 'merge into' update statement using
JDBC. Attached is a self-contained application code that runs the following
function.
static void bug_case (Connection conn)
{
System.out.
println ("Test for which we check the param count in isolation");
PreparedStatement statement = null;
try
{
statement =
conn.
prepareStatement
("MERGE INTO MY_TEST_TABLE D USING ( SELECT '1234' as ATTR1, 'TYPE1' as
ATTR2, 'FailType1' as ATTR4, 'Sample Details' as ATTR5, '1' as ATTR6,
'REPLAY' as ATTR8, 'abcd' as ATTR3 , '2' as MAX_ATTR6, 'null' as ATTR17
,null as ATTR7, null as ATTR9, null as ATTR10, null as ATTR11, null as
ATTR12, null as ATTR13, null as ATTR14 , null as ATTR15 FROM dual ) S ON
(D.ATTR1 = S.ATTR1 AND D.ATTR2 = S.ATTR2) WHEN MATCHED THEN UPDATE SET
D.ATTR6 = CASE WHEN S.ATTR6 < 0 THEN -1 ELSE D.ATTR6 + 1 END, D.ATTR8 =
CASE WHEN D.ATTR6 < S.MAX_ATTR6-1 THEN S.ATTR8 ELSE 'FAIL' END, D.ATTR7 =
sysdate, D.ATTR4 = S.ATTR4, D.ATTR5 = S.ATTR5, D.ATTR3 = S.ATTR3, D.ATTR17
= S.ATTR17 WHEN NOT MATCHED THEN INSERT (ATTR1, ATTR2, ATTR4, ATTR5, ATTR6,
ATTR7, ATTR8, ATTR3, ATTR9, ATTR17) VALUES (S.ATTR1, S.ATTR2, S.ATTR4,
S.ATTR5, S.ATTR6, sysdate, S.ATTR8,S.ATTR3,? ,S.ATTR17)");
Blob b1 = conn.createBlob ();
byte[]bytes = new byte[10];
for (int i = 0; i < 10; ++i)
bytes[i] = (byte) (2 * i);
b1.setBytes (1, bytes);
statement.setBlob (1, b1);
statement.executeUpdate ();
}
catch (Exception e)
{
System.err.println ("ERROR: " + e.toString ());
}
finally
{
try
{
if (statement != null)
{
statement.close ();
}
}
catch (SQLException e)
{
System.err.println ("SQL ERROR: " + e.toString ());
}
catch (Exception e)
{
System.err.println ("ERROR: " + e.toString ());
}
}
}
I get the following exception:
ERROR: org.h2.jdbc.JdbcSQLException: Invalid value "1" for parameter
"parameterIndex" [90008-197]
I have confirmed that the issue is not on the client side. The client-side
driver sends the same prepared statement to the H2 server and the server
sends back 0 as parameter count (as received by the function
void CommandRemote::prepare(SessionRemote s, boolean createParams)in the line
highlighted below. Here readInt returns 0 despite the parameter highlighted in
green above).
private void prepare(SessionRemote s, boolean createParams) {
id = s.getNextId();
for (int i = 0, count = 0; i < transferList.size(); i++) {
try {
Transfer transfer = transferList.get(i);
boolean v16 = s.getClientVersion() >=
Constants.TCP_PROTOCOL_VERSION_16;
if (createParams) {
s.traceOperation(v16 ? "SESSION_PREPARE_READ_PARAMS2"
: "SESSION_PREPARE_READ_PARAMS", id);
transfer.writeInt(
v16 ? SessionRemote.SESSION_PREPARE_READ_PARAMS2
: SessionRemote.SESSION_PREPARE_READ_PARAMS)
.writeInt(id).writeString(sql);
} else {
s.traceOperation("SESSION_PREPARE", id);
transfer.writeInt(SessionRemote.SESSION_PREPARE).
writeInt(id).writeString(sql);
}
s.done(transfer);
isQuery = transfer.readBoolean();
readonly = transfer.readBoolean();
cmdType = v16 && createParams ? transfer.readInt() : UNKNOWN;
int paramCount = transfer.readInt();
if (createParams) {
parameters.clear();
for (int j = 0; j < paramCount; j++) {
ParameterRemote p = new ParameterRemote(j);
p.readMetaData(transfer);
parameters.add(p);
}
}
} catch (IOException e) {
s.removeServer(e, i--, ++count);
}
}
}
Will much appreciate any feedback.
Best regards
Jay
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
import java.util.*;
import java.lang.*;
import java.io.*;
import org.apache.commons.dbcp.*;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.*;
import java.lang.System;
import java.sql.DriverManager;
public class H2Client
{
private static String DB_DRIVER = "org.h2.Driver";
private static String DB_CONNECTION = ""; //= "";
private static String DB_USER = ""; // = "sa";
private static String DB_PASSWORD = ""; // = "";
private static String DB_SCHEMA = "";
private static Connection connection = null;
public static Connection getDBConnection ()
{
if (connection == null)
try
{
Class.forName (DB_DRIVER);
try
{
connection =
DriverManager.getConnection (DB_CONNECTION, DB_USER, DB_PASSWORD);
if (connection != null)
{
System.out.println ("Connection to DB established");
return connection;
}
}
catch (SQLException ex)
{
System.out.println ("Connection to DB could not be established");
return null;
}
}
catch (ClassNotFoundException ex)
{
System.out.println ("Connection to DB could not be established");
return null;
}
return null;
}
public static void printUsage ()
{
System.out.
println
("Usage : executable -D driver -L connection-string -U username -P password @sqlfile1 @sqlfile2 ...");
}
public static void main (String[]argv)
{
if (argv.length == 0)
{
printUsage ();
return;
}
System.out.println (argv[0]);
String connectionString;
ArrayList < String > files = new ArrayList < String > ();
for (int i = 0; i < argv.length; ++i)
{
if (argv[i].equals ("-D"))
{
if (argv.length > (i + 1))
{
DB_DRIVER = argv[i + 1];
++i;
}
}
else if (argv[i].equals ("-C"))
{
if (argv.length > (i + 1))
{
DB_CONNECTION = argv[i + 1];
++i;
}
}
else if (argv[i].equals ("-L"))
{
if (argv.length > (i + 1))
{
String[]parts = argv[i + 1].split ("/");
System.out.println ("Number of parts = " + parts.length);
DB_USER = "sa"; // parts[0];
DB_PASSWORD = ""; //parts[1].substring(0, parts[1].length()-1);
String hostPort = parts[3];
String dbname = parts[4];
// Here is an example of the connection string "jdbc:h2:tcp://127.0.0.1:9092/test"
DB_CONNECTION =
"jdbc:h2:tcp://" + hostPort + "/" + dbname + ";MODE=Oracle";
if (parts[0] != "sa")
{
DB_SCHEMA = parts[0];
}
++i;
}
}
else if (argv[i].equals ("-S"))
{
if (argv.length > (i + 1))
{
DB_SCHEMA = argv[i + 1];
++i;
}
}
else if (argv[i].equals ("-U"))
{
if (argv.length > (i + 1))
{
DB_USER = argv[i + 1];
++i;
}
}
else if (argv[i].equals ("-P"))
{
if (argv.length > (i + 1))
{
DB_PASSWORD = argv[i + 1];
++i;
}
}
else if (argv[i].charAt (0) == '@')
{
files.add (argv[i].substring (1));
}
}
if (DB_CONNECTION.equals (""))
{
System.out.println ("Database connection was not specified");
printUsage ();
return;
}
if (DB_USER.equals (""))
{
System.out.println ("Username was not specified");
printUsage ();
return;
}
System.out.println ("DB Connection:<" + DB_CONNECTION + ">");
System.out.println ("Username :<" + DB_USER + ">");
System.out.println ("Password :<" + DB_PASSWORD + ">");
for (String file:files)
{
System.out.println ("Source file:" + file);
}
BasicDataSource basicDataSource = new BasicDataSource ();
basicDataSource.setDriverClassName (DB_DRIVER);
if (DB_SCHEMA == "")
{
basicDataSource.setUrl (DB_CONNECTION + ";SCHEMA_SEARCH_PATH=PUBLIC,INFORMATION_SCHEMA"); //jdbcSlabPasswordUtil.getServer());
}
else
{
basicDataSource.setUrl (DB_CONNECTION + ";SCHEMA=" + DB_SCHEMA +
";SCHEMA_SEARCH_PATH=PUBLIC,AME_CUSTODY_DBO,INFORMATION_SCHEMA");
}
basicDataSource.setUsername (DB_USER); //jdbcSlabPasswordUtil.getUsername());
basicDataSource.setPassword (DB_PASSWORD); //jdbcSlabPasswordUtil.getPassword());
basicDataSource.setInitialSize (10);
//basicDataSource.setMaxIdle(maxIdle);
//basicDataSource.setMaxActive(maxActive);
basicDataSource.setConnectionProperties ("v$session.program=DSJdbc");
run_tests (basicDataSource);
}
static void run_tests (BasicDataSource basicDataSource)
{
Connection conn = null;
Statement statement = null;
try
{
conn = basicDataSource.getConnection ();
bug_case (conn);
}
catch (Exception e)
{
System.err.println ("ERROR: " + e.toString ());
}
finally
{
try
{
if (conn != null)
{
conn.close ();
}
}
catch (SQLException e)
{
System.err.println ("SQL ERROR: " + e.toString ());
}
catch (Exception e)
{
System.err.println ("ERROR: " + e.toString ());
}
}
}
static void bug_case (Connection conn)
{
System.out.
println ("Test for which we check the param count in isolation");
PreparedStatement statement = null;
try
{
statement =
conn.
prepareStatement
("MERGE INTO MY_TEST_TABLE D USING ( SELECT '1234' as ATTR1, 'TYPE1' as ATTR2, 'FailType1' as ATTR4, 'Sample Details' as ATTR5, '1' as ATTR6, 'REPLAY' as ATTR8, 'abcd' as ATTR3 , '2' as MAX_ATTR6, 'null' as ATTR17 ,null as ATTR7, null as ATTR9, null as ATTR10, null as ATTR11, null as ATTR12, null as ATTR13, null as ATTR14 , null as ATTR15 FROM dual ) S ON (D.ATTR1 = S.ATTR1 AND D.ATTR2 = S.ATTR2) WHEN MATCHED THEN UPDATE SET D.ATTR6 = CASE WHEN S.ATTR6 < 0 THEN -1 ELSE D.ATTR6 + 1 END, D.ATTR8 = CASE WHEN D.ATTR6 < S.MAX_ATTR6-1 THEN S.ATTR8 ELSE 'FAIL' END, D.ATTR7 = sysdate, D.ATTR4 = S.ATTR4, D.ATTR5 = S.ATTR5, D.ATTR3 = S.ATTR3, D.ATTR17 = S.ATTR17 WHEN NOT MATCHED THEN INSERT (ATTR1, ATTR2, ATTR4, ATTR5, ATTR6, ATTR7, ATTR8, ATTR3, ATTR9, ATTR17) VALUES (S.ATTR1, S.ATTR2, S.ATTR4, S.ATTR5, S.ATTR6, sysdate, S.ATTR8,S.ATTR3,? ,S.ATTR17)");
Blob b1 = conn.createBlob ();
byte[]bytes = new byte[10];
for (int i = 0; i < 10; ++i)
bytes[i] = (byte) (2 * i);
b1.setBytes (1, bytes);
statement.setBlob (1, b1);
statement.executeUpdate ();
}
catch (Exception e)
{
System.err.println ("ERROR: " + e.toString ());
}
finally
{
try
{
if (statement != null)
{
statement.close ();
}
}
catch (SQLException e)
{
System.err.println ("SQL ERROR: " + e.toString ());
}
catch (Exception e)
{
System.err.println ("ERROR: " + e.toString ());
}
}
}
}
