Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: dynamic SQL in java code not working in db2 - SQLcode : -401

    I have software appliction in java that has SQL statement.The software communicates with SQL server and DB2 as the backend databse. The SQL statement is a dynamically generated one that uses application variables in the where clause...Here is how the SQL in the code looks like..

    void getData(int pIDvarFromAppl)
    {
    Statement st= createStatement();
    String stSQl= "SELECT * FROM " + toUppercaseFunc("sampletbl") + " WHERE p_id = '"+ pIDvarFromAppl+"'";
    return st.executeQuery(stSQL);
    }

    In the snippet above toUppercaseFunc() is a method which converts the string to upperCase.
    p_id is the collum in the table sampletbl.Its int type.
    pIDvarFromAppl is the int variable in the application which is assigned some integer value like for eg pIDvarFromAppl =45.

    This code works perfectly with SQL database but not on DB2.In DB2 the error got is SQLcode: -0401 SQLSTATE:42818

    Any idea on what may be the possible reason.....

    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You compared integer(p_id) and character constant(for eg '45').
    Please try to remove quotation marks before and after pIDvarFromAppl.

    By the way, it will be better to include whole error messages in your post.

    For example, you may be able to know the operation in error.
    SQL0401N The data types of the operands for the operation operator are not compatible.

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    Thanks a million for your answer. Looks like that was the thing that i was missing. For some reason, I think ,SQL server is quite forgiving, in the sense the code runs perfectly for SQL server with the quotes, but it seems db2 dosent like it.

    I will run the code with the changes made on monday and will let you know the results.

    Once again thanks a lot for your help.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 is not case sensitive when it comes to object names (like table name) so long as you leave the quotes off.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Marcus_A
    DB2 is not case sensitive when it comes to object names (like table name) so long as you leave the quotes off.
    Otherwise said, the following should also work (and is a bit more readable):
    Code:
    void getData(int pIDvarFromAppl)
    {
      Statement st= createStatement();
      String stSQl= "SELECT * FROM sampletb WHERE p_id = "+ pIDvarFromAppl;
      return st.executeQuery(stSQL);
    }
    Also, consider replacing "SELECT *" by an explicit SELECT of the columns you want.
    For one thing, this will save you surprises when the table would ever be modified to have more columns;
    and also, this will improve the program's readability because of the better visibility (and order) of the column names.
    And finally, in case your program doesn't need all table columns, it will improve the performance.
    Last edited by Peter.Vanroose; 03-15-09 at 09:35.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Mar 2009
    Posts
    3
    Thanks a lot to everybody for your help. The extra quote was an issue. I took it off and issue resolved.

    Once again a BIG Thanks to all of you.

Posting Permissions

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