Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    6

    Unanswered: Need Help: Error SQL0104N -> SQLSTATE=42601

    Hey guys,

    I am trying to do some dynamic SQL using JDBC 2.0 against a DB2 v8.1.4 database. I am developing on a WinXP machine under WSAD 5.1 with Sun JDK 1.4.1. installed on the machine is DB2 v8.1 with FP 4.

    I'm using a PreparedStatement and trying to plug in the params with values. Here is the dynamic query I generate:

    Code:
    SELECT * FROM SOQADM01.TEST_JOB_STATUS WHERE inboundstatus = ? OR outboundstatus = ? OR inboundstatus IS ? AND outboundstatus = ?
    And I plug it with these values [debug output]:

    Code:
    [DEBUG] pstmt.parameter[1]  class=[class java.lang.Integer]  value=[9]  expected sqltype=[INTEGER]
    [DEBUG] pstmt.parameter[2]  class=[class java.lang.Integer]  value=[9]  expected sqltype=[INTEGER]
    [DEBUG] pstmt.parameter[3]  class=[null]  value=[null]  expected sqltype=[INTEGER]
    [DEBUG] pstmt.parameter[4]  class=[class java.lang.Integer]  value=[9]  expected sqltype=[INTEGER]
    where the value=[] is the actual value going in and the sqltype=[] is the java.sql.Types value for the appropriate column. I am using the pstmt.setObject(paramIdx, value, sqlType) method where as you can see from above, i think i am slapping in the right values. The column(s) I am querying are nullable, btw.

    Essentially I am trying to weed out some data from the database and need to query where "X is NULL" hence in parameter 3 I have a null value going in. The column type is correct - it's Integer, but i need the null in there. [I also tried setting the sqlType to a proper java.sql.Types.NULL but that threw a whole different error about Program Not Allowed or something]

    It throws this when it comes to actually executing the query:

    Code:
    COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0104N  An unexpected token "IS" was found following "= ? OR inboundstatus".  Expected tokens may include:  "IN".  SQLSTATE=42601
    if i cut and paste the query as it should be into my fav tool it runs no problems:

    Code:
    SELECT * FROM SOQADM01.TEST_JOB_STATUS WHERE inboundstatus = 9 OR outboundstatus = 9 OR inboundstatus IS null AND outboundstatus = 9
    Can anyone spot something i am missing here?

    Any help is appreciated.

    Thanks

  2. #2
    Join Date
    May 2004
    Posts
    6
    Gyah! I dont understand this! I changed the "IS NULL" to "= null" and it worked! But if i cut & paste the query into my db tool and execute it, it failes on the IS NULL.

    Is the JDBC drievr doing something else?!


    <I hate computers>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •