Trouble with DB2 XMLPARSE and JDBC using PreparedStatement

arkanoid · #1 (**permalink**) 12-09-10, 08:56

Hello everyone,

I want to INSERT XML-data into a DB2 Table. The following exemplary statement works fine:

INSERT INTO test VALUES ( '1' , 'something', XMLPARSE(DOCUMENT '<test></test>')) ;

I'd like to use PreparedStatements, for escaping reasons etc., instead but it is giving me some trouble. I've tried the following variants:

Variant 1:

Code:

PreparedStatement ps = 
          connection.prepareStatement(INSERT INTO test VALUES ( '?', '?', XMLPARSE(DOCUMENT'?')) ;
ps.setString(1,"1");
ps.setString(2,"something");
ps.setString(3,"<test></test>");
-> SQLCODE: -16132, SQLSTATE: 2200M, SQLERRMC: null

Variant 2:

Code:

PreparedStatement ps = 
          connection.prepareStatement(INSERT INTO test VALUES ( '?', '?', '?'));
ps.setString(1,"1");
ps.setString(2,"something");
ps.setString(3,"XMLPARSE(DOCUMENT'<test></test>')");
-> SQLCODE: -16132, SQLSTATE: 2200M, SQLERRMC: null

Can anybody give me some pointers as to what I'm doing wrong? I'm somewhat in doubt that it is really a XML-structure problem.

Greetings,
arkanoid

EDIT1: added closing bracket to PreparedStatement 2
EDIT2: added code tags

n_i · #2 (**permalink**) 12-09-10, 10:13

Parameter markers should not be quoted.

arkanoid · #3 (**permalink**) 12-09-10, 10:34

Thanks for your reply.
Tried that too, like this right?

Code:

String param = "XMLPARSE(DOCUMENT '<a></a>')";
PreparedStatement ps = 
          connection.prepareStatement("INSERT INTO test VALUES ( ?, ?, ?)");
ps.setString(9,param);
-> -16132, SQLSTATE: 2200M, SQLERRMC: null

Code:

String param = "<test></test>"; || param = "'<test></test>'";
PreparedStatement ps = 
          connection.prepareStatement("INSERT INTO test VALUES ( ?, ?, XMLPARSE(DOCUMENT ?))");
ps.setString(9,param);
-> SQLCODE: -418, SQLSTATE: 42610, SQLERRMC: null

Edit1 : added code tags

n_i · #4 (**permalink**) 12-09-10, 11:16

Quote:

Originally Posted by arkanoid

Code:

String param = "<test></test>"; || param = "'<test></test>'";
PreparedStatement ps = 
          connection.prepareStatement("INSERT INTO test VALUES ( ?, ?, XMLPARSE(DOCUMENT ?))");
ps.setString(9,param);
-> SQLCODE: -418, SQLSTATE: 42610, SQLERRMC: null

Try an explicit cast:

PreparedStatement ps =
connection.prepareStatement("INSERT INTO test VALUES ( ?, ?, XMLPARSE(DOCUMENT cast(? as varchar(256))))");

arkanoid · #5 (**permalink**) 12-09-10, 11:26

Quote:

Originally Posted by n_i

Try an explicit cast:

PreparedStatement ps =
connection.prepareStatement("INSERT INTO test VALUES ( ?, ?, XMLPARSE(DOCUMENT cast(? as varchar(256))))");

That did the trick, thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech