CODEWITHSUNDEEP
javadb2named-parameters
madtyn
madtyn

Reputation: 1559

Parameter marker XXX was not present in the SQL String

I'm trying to use named parameter markers in a SQL query with Java and db2, like this:

SELECT ...
FROM mytable c ...
WHERE c.id_field = :myParam

I configure the connection like this:

Properties properties = new Properties(); // Create Properties object
properties.put("user", config.getDbUser());         // Set user ID for connection
properties.put("password", bdPassword);     // Set password for connection
properties.put("enableNamedParameterMarkers", 1);
currentConnection = (DB2Connection) DriverManager.getConnection(config.getDbUrl(), properties);

I made a DB2PreparedStatement with the query and set the value in this way:

DB2PreparedStatement ps = (DB2PreparedStatement) currentConnection.prepareStatement(sqlString);
ps.setJccStringAtName("myParam", "value");

But I get the next error:

Error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: 
[jcc][10448][12695][4.19.66] Parameter marker 'myParam' was not present in the SQL String. ERRORCODE=-4461, SQLSTATE=42815

I can't guess why this is happening.

Additional data:

    <dependency>
        <groupId>com.ibm.db2</groupId>
        <artifactId>db2jcc</artifactId>
        <version>4.19.66</version>
    </dependency>

IBM Data Server Driver for JDBC and SQLJ 4.19.66

SELECT VERSIONNUMBER FROM SYSIBM.SYSVERSIONS

VERSIONNUMBER
-10050900
9070900
10050800
10051000
11010405
11010406
11050700

Upvotes: 0

Views: 437

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12339

Try:

properties.put("enableNamedParameterMarkers", "1");

instead of:

properties.put("enableNamedParameterMarkers", 1);

Upvotes: 1

mao
mao

Reputation: 12287

Your question does not show your real code so I cannot guess what mistake or omission that you are making.

Named Parameters work well with DB2PreparedStatement, as long as you follow the documented rules, and you are using a currently supported version of the Db2-server and a currently supported version of the type4 db2jcc4.jar driver.

IBM provides a working example of using named parameter markers with a Type-4 jdbc connection, in the file TbSel.java .

The sample code is here, and on github, and in your Db2-LUW server ~$DB2INSTANCE/sqllib/samples/java/jdbc directory (if the samples programs are installed). You can study these, and the readme that accompanies the samples, and build them yourself.

In that example code, IBM enables the named parameter markers programatically: (you can also enable them via a connection string attribute ;enableNamedParameterMarkers=1;

    javax.sql.DataSource ds=null;
    ds=new com.ibm.db2.jcc.DB2SimpleDataSource();
    ...
    ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setEnableNamedParameterMarkers(1);
    ..

You may want to study this example code, and all the other IBM supplied jdbc samples, and get them working. This is one way to learn.

Upvotes: 1

Related Questions