@JdbcExecuteQuery with parameters – the correct syntax

So you like the XPages Extension Library and you want to use the JDBC integration it offers? You did your own research or you let the web guide you, f.e. with my article about SQL Server and JDBC? Or you used the extraordinary good book Xpages Extension Library – A Step-by-Step Guide?

There’s a section in it explaining you can use @JdbcExecuteQuery with parameters, so that it becomes easier to add your own parameters/variables in the query you want to throw to that database server.

This is the example code from the book:

var con:java.sql.Connection;
try {
con = @JdbcGetConnection("HR");
var params = ['employees','1234'];
var results:java.sql.ResultSet =
@JdbcExecuteQuery(con,
"SELECT * FROM ? WHERE ACTIVE='?'", params);
} finally {
con.close();
}

The idea is that every value in the parameter array replaces a value in the query accordingly.  When you try this out, it doesn’t work. For some reason it doesn’t execute the query correctly.

The problem is in the quotes surrounding the question mark. When I tried removing them, it DOES work. Perhaps, since the string in the params array is already surrounded by quotes, the query gets “over-quoted” :-).  Anyway, if you’re ever in this situation, just try removing the quotes around the ?.  The correct syntax should be:

var con:java.sql.Connection;
try {
con = @JdbcGetConnection("HR");
var params = ['employees','1234'];
var results:java.sql.ResultSet =
@JdbcExecuteQuery(con,
"SELECT * FROM ? WHERE ACTIVE=?", params);
} finally {
con.close();
}

3 Comments

    • Thanks for your feedback, Luke :-). The only real resemblence between the PreparedStatements and SSJS here, is in the “?”, so it seems?

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

− 4 = 2

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>