Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    3

    Unanswered: Help with db2 syntax!

    Hi all,

    i am using an third party client to connect to db2 database. The statement is like
    select SNO,AGE from TEST where AGE > '?' order by AGE

    in which the question mark will be replaced with an integer by the client software. this syntax works fine with mysql and mssql.

    But with db2 it throws the following error

    "DB2 SQL error: SQLCODE: -401, SQLSTATE: 42818, SQLERRMC: > state(42818)
    Vendorcode(-401)"
    .

    It says that this syntax is not acceptable. should i use different syntax with db2? Thanks for your help

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    SQL0401N The data types of the operands for the operation "<operator>"
    are not compatible or comparable.

    mysql implicitly casts the data types of the operands. But db2 does not, unti. 9.7

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down

    Quote Originally Posted by karthik110885 View Post
    Hi all,

    i am using an third party client to connect to db2 database. The statement is like
    select SNO,AGE from TEST where AGE > '?' order by AGE

    in which the question mark will be replaced with an integer by the client software. this syntax works fine with mysql and mssql.

    But with db2 it throws the following error

    "DB2 SQL error: SQLCODE: -401, SQLSTATE: 42818, SQLERRMC: > state(42818)
    Vendorcode(-401)"
    .

    It says that this syntax is not acceptable. should i use different syntax with db2? Thanks for your help
    If you want use parameter markers you have to do it like this:
    Code:
    select SNO,AGE from TEST where AGE > ? order by AGE
    Lenny

  4. #4
    Join Date
    Mar 2010
    Posts
    3
    Hi lenny and sathyaram,

    thanks for your quick reply.

    I tried giving without the quotes. as below

    select SNO,AGE from TEST where AGE > ? order by AGE

    But the following error is thrown now

    Exception during SQL execute: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: order;rom TEST
    where AGE >;IS NULL state(42601) Vendorcode(-104)


    I think the problem is with using the special character ? in the syntax. Is there a way to use special characters in the the select statement.

    Once again, thanks for your reply.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb

    Quote Originally Posted by karthik110885 View Post
    Hi lenny and sathyaram,

    thanks for your quick reply.

    I tried giving without the quotes. as below

    select SNO,AGE from TEST where AGE > ? order by AGE

    But the following error is thrown now

    Exception during SQL execute: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: order;rom TEST
    where AGE >;IS NULL state(42601) Vendorcode(-104)


    I think the problem is with using the special character ? in the syntax. Is there a way to use special characters in the the select statement.

    Once again, thanks for your reply.
    It has to be string:
    Code:
    set :Str1 = 'select SNO,AGE from TEST where AGE > ? order by AGE';
    set :AGE0 = 33;
    From this string you have to prepare statement:
    Code:
    prepare Stmt1 from :Str1;
    Then (for example):
    Code:
    declare curs1 cursor from Stmt1;
    Then you have to Open cursor, using the value of AGE:
    Code:
    Open curs1 using :AGE0
    Then you have to Fetch cursor in Host variables:
    Code:
    Fetch curs1 into :SNO, :AGE
    until sqlcode = +100

    Lenny
    Last edited by Lenny77; 03-29-10 at 17:52.

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Jdbc

    For JDBC it could look like:

    Code:
    package com.ibm.itso.sg246435.jdbc;
    
    import java.math.BigDecimal;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    /**
    * Sample test program to retrieve all employees
    * in the EMP sample tables whose salary is in
    * a given range.
    *
    * @author Lenny77
    */
    public class Get_RS {
    /** JDBC URL to the database. */
    private static final String url = "jdbc:db2://wtsc63.itso.ibm.com:33756/DB7Y"
    + ":retrieveMessagesFromServerOnGetMessage=TRUE;";  
    
    /** User name to connect to the database. */
    private static final String user = "lenny77"; // your user
    
    /** Password for the database connection. */
    private static final String password = "pob7777";
    
    public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
    // Load the JDBC driver.
    Class.forName("com.ibm.db2.jcc.DB2Driver"); 
    // Connect to the database server.
    conn = DriverManager.getConnection(url, user, password);  
    // Prepare the SELECT statement.
    stmt = conn.prepareStatement(  
    "select SNO, AGE from TEST where AGE > ? order by AGE");
    
    // Set parameters for the SELECT statement.
    stmt.setBigDecimal(1, new BigDecimal(33));  
    
    // Execute the query to retrieve a ResultSet.
    rs = stmt.executeQuery(); 
    
    // Iterate over the ResultSet.
    while (rs.next()) { 
    String SNO = rs.getString(1); // SNO
    BigDecimal AGE= rs.getBigDecimal(2); // AGE
    System.out.println(SNO + ", " + AGE);
    }
    } catch (SQLException e) {
    // Print exceptions to the console.
    System.err.println(e.getMessage());
    } catch (Exception e) {
    System.err.println(e);
    } finally {
    // Clean up.
    try {
    if (rs != null) rs.close();
    if (stmt != null) stmt.close();
    if (conn != null) conn.close();
    } catch (SQLException ignored) {
    }
    }
    }
    Lenny
    Last edited by Lenny77; 03-29-10 at 17:21.

  7. #7
    Join Date
    Mar 2010
    Posts
    3
    Hi Lenny,

    Thanks for your help.

    The problem is, i am using an third party client, and i cannot code any changes into it. There are fixed set of parameters that i can provide, but that's it

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool Syntax !

    Quote Originally Posted by karthik110885 View Post
    Hi Lenny,

    Thanks for your help.

    The problem is, i am using an third party client, and i cannot code any changes into it. There are fixed set of parameters that i can provide, but that's it
    All problems you have to solve using syntax of DB2.

    Lenny

Posting Permissions

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