Java call User Defined Table Function DB2 with prepared statement

Question

I'm developing a web app with Java 6 EE and DB2. I created a table function that receives 3 parameters and returns a table.

CREATE FUNCTION MY_FUNCTION (PARAM1 VARCHAR(5), PARAM2 VARCHAR(10), PARAM3 INTEGER)
RETURNS TABLE (
    FIELD1 VARHCHAR(5),
    FIELD2 VARCHAR(10),
    FIELD3 INTEGER
)
RETURN 
SELECT FIELD1, FIELD2, FIELD3 
FROM TABLE_1 WHERE FIELD1 = PARAM1 || '_MAIN' 
AND FIELD2 = PARAM2 || '_MAIL' AND FIELD3 = PARAM3 + 47

I'm trying to execute a function in Java with prepared statement as follows (using wildcards):

PreparedStatement stmt = conn.prepareStatement("SELECT * FROM TABLE(MY_FUNCTION(?, ?, ?)) AS TABLE");

But when I run my code, I get an SQLSyntaxErrorException in the prepared statement:

java.sql.SQLSyntaxErrorException: [SQL0418] A statement contains a use of a parameter marker that is not valid
       at com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:828)
       at com.ibm.as400.access.JDError.throwSQLException(JDError.java:699)
       at com.ibm.as400.access.JDError.throwSQLException(JDError.java:669)
       at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1660)
       at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:248)
       at com.ibm.as400.access.AS400JDBCCallableStatement.<init>(AS400JDBCCallableStatement.java:120)
       at com.ibm.as400.access.AS400JDBCConnection.prepareCall(AS400JDBCConnection.java:1840)
       at com.ibm.as400.access.AS400JDBCConnection.prepareCall(AS400JDBCConnection.java:1741)

Note: If I hardcode the parameters like this (without wilcards) works:

PreparedStatement stmt = conn.prepareStatement("SELECT * FROM TABLE(MY_FUNCTION('" + var1 + "', '" + var2 + "', '" + var3 + "')) AS TABLE");

What I want to achieve is to call the functions with the wildcards to improve the processing of the function.

Thanks in advance

Solution with @user384842 answer

PreparedStatement stmt = conn.prepareStatement("SELECT * FROM TABLE(MY_FUNCTION(cast(? as VARCHAR(5)), cast(? as VARCHAR(10)), cast(? as INTEGER))) AS TABLE");

user384842 · Accepted Answer · 2014-11-26 23:40:32Z

3

After hunting a bit on google, looks like maybe you need to cast them to the appropriate type? I found this documentation:

requiresCastingOfParametersInSelectClause() 
DB2 in fact does require that parameters appearing in the select clause be wrapped in cast() calls to tell the DB parser the type of the select value.

here: https://docs.jboss.org/hibernate/orm/3.5/api/org/hibernate/dialect/DB2Dialect.html

Not sure if it's relevant, but might be worth a go? I guess it would look something like cast(? as varchar(30))

Link on casting here http://www.dbatodba.com/db2/how-to-do/how-to-convert-data-types-on-db2/

answered Nov 26, 2014 at 23:40

user384842

1,9853 gold badges20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Java call User Defined Table Function DB2 with prepared statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related