title: Java call User Defined Table Function DB2 with prepared statement
body: <p>I'm developing a web app with <strong>Java 6 EE and DB2</strong>. I created a table function that receives 3 parameters and returns a table.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I'm trying to execute a function in Java with prepared statement as follows (using wildcards):</p>
  
  <pre><code>PreparedStatement stmt = conn.prepareStatement("SELECT * FROM TABLE(MY_FUNCTION(?, ?, ?)) AS TABLE");
  </code></pre>
  
  <p>But when I run my code, I get an SQLSyntaxErrorException in the prepared statement:</p>
  
  <pre><code>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.&lt;init&gt;(AS400JDBCPreparedStatement.java:248)
         at com.ibm.as400.access.AS400JDBCCallableStatement.&lt;init&gt;(AS400JDBCCallableStatement.java:120)
         at com.ibm.as400.access.AS400JDBCConnection.prepareCall(AS400JDBCConnection.java:1840)
         at com.ibm.as400.access.AS400JDBCConnection.prepareCall(AS400JDBCConnection.java:1741)
  </code></pre>
  
  <p><strong>Note:</strong> If I hardcode the parameters like this (without wilcards) works:</p>
  
  <pre><code>PreparedStatement stmt = conn.prepareStatement("SELECT * FROM TABLE(MY_FUNCTION('" + var1 + "', '" + var2 + "', '" + var3 + "')) AS TABLE");
  </code></pre>
  
  <p>What I want to achieve is to <strong>call the functions with the wildcards to improve the processing of the function</strong>.</p>
  
  <p>Thanks in advance</p>
  
  <p><strong>Solution with @user384842 answer</strong></p>
  
  <pre><code>PreparedStatement stmt = conn.prepareStatement("SELECT * FROM TABLE(MY_FUNCTION(cast(? as VARCHAR(5)), cast(? as VARCHAR(10)), cast(? as INTEGER))) AS TABLE");
  </code></pre>