0

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
madtyn
  • 1,469
  • 27
  • 55
  • 1
    Does this answer your question? [Named parameters in JDBC](https://stackoverflow.com/questions/2309970/named-parameters-in-jdbc) – data_henrik Mar 31 '22 at 13:09
  • 1
    If your Db2-server is running on Linux/Unix/Windows , and is a currently supported version (e.g. v11.5.x) and you are using a current `db2jcc4.jar` version, then `Db2PreparedStatement` will support named parameters only if you *either* have `enableNamedParameterMarkers=1;` in the connection string, or if you explicitly set that property on the data source. The `setJccStringAtName` will support named parameter in this case. – mao Mar 31 '22 at 13:21
  • @data_henrik Yes, I knew and read that resource. I'm thinking about making my custom solution but I'd rather not if there is a chance – madtyn Mar 31 '22 at 13:27
  • @mao How do I do that? I tried fiddling with the reconfigureDB2Connection() method from the DB2Connection object, but I failed – madtyn Mar 31 '22 at 13:29
  • 1
    In your connection-string, append `enableNamedParameterMarkers=1;` or follow the IBM Db2 Knowledge Centre online instructions. – mao Mar 31 '22 at 13:40
  • I tried in several ways and the same error continues to show up – madtyn Mar 31 '22 at 14:57

2 Answers2

1

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.

mao
  • 11,321
  • 2
  • 13
  • 29
  • You are absolutely right. I edited and added the code for the connection creation. I'm about to look at the example you provided. Thanks. – madtyn Apr 01 '22 at 14:59
  • Thank you @mao, the example worked. But I don't see much difference apart from the fact that one uses a DataSource before creating the connection and my code doesn't. I'm investigating further. – madtyn Apr 01 '22 at 15:47
1

Try:

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

instead of:

properties.put("enableNamedParameterMarkers", 1);
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16