Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    37

    Unhappy Unanswered: need help with java stored procedure

    my environment is; websphere app. server 4.0, db2 7.2 fixpack 6 and jdbc 2.0 driver.
    i have a long query statement which i use in a jsp file. it works fine, but i want / have to use a stored procedure. the code is below.

    public static void getbolumler ( String IDs, ResultSet[] rs )

    the sp gets a string as a parameter like "199, 198, 178". it works fine when the string passed to it consists only of one number like this : "198", but when i increase the comma seperated numbers like "199, 198", the sp returns this error :

    SQLException: [IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=HY010

    in command center it works fine, even if i pass 2 values (comma seperated), command center shows me a result, as if i would have passed only one value.

    in command center :
    call getbolumler("199") --> works fine
    call getbolumler(199) --> works fine, although it is not a string !?
    call getbolumler("199, 198") --> works, but recognizes only the first number

    could anybody tell me what i'm doing wrong ?
    (i read in forums that i should set autocommit to false in the sp. i did that but that didn't change anything. the result is the same.

    need really help, (i have to finish my project)
    thanks in advance.
    Mehmet Gunacti

    in jsp file :
    cst = cn.prepareCall("{call getbolumler(?)}");
    cst.setString(1, "199, 198, 178");
    rs = cst.executeQuery();


    code of sp :

    /**
    * JDBC Stored Procedure ESC02.getbolumler
    */
    import java.sql.*; // JDBC classes

    public class Getbolumler
    {
    public static void getbolumler ( String IDs,
    ResultSet[] rs ) throws SQLException, Exception
    {
    // Get connection to the database
    Connection con = DriverManager.getConnection("jdbc:default:connecti on");
    PreparedStatement stmt = null;
    String sql;

    sql = "select id, gorunenad from (select * from v_disp_st_bolumler where id in ( ? ) union select * from v_disp_st_bolumler where id in (select bob from t_st_bolumler where id in ( ? )) union select * from v_disp_st_bolumler where id in (select bob from t_st_bolumler where id in (select bob from t_st_bolumler where id in ( ? ))) union select * from v_disp_st_bolumler where id in (select bob from t_st_bolumler where id in (select bob from v_disp_st_bolumler where id in (select bob from t_st_bolumler where id in ( ? )))) union select * from v_disp_st_bolumler where id in (select bob from t_st_bolumler where id in (select bob from t_st_bolumler where id in (select bob from v_disp_st_bolumler where id in (select bob from t_st_bolumler where id in ( ? ))))) ) t order by sort1, sort2, sort3, sort4";
    con.setAutoCommit(false);
    stmt = con.prepareStatement( sql );
    stmt.setString( 1, IDs );
    stmt.setString( 2, IDs );
    stmt.setString( 3, IDs );
    stmt.setString( 4, IDs );
    stmt.setString( 5, IDs );
    rs[0] = stmt.executeQuery();
    con.commit();
    con.setAutoCommit(true);
    if (con != null) con.close();
    }
    }

  2. #2
    Join Date
    Oct 2002
    Posts
    37

    Question it worked with Statement

    i changed the code and replaced the preparedstatement with a statement.

    the new code looks like this :
    Statement st = null;
    String sql;

    sql = "select id, gorunenad from (select * from v_disp_st_bolumler where id in ( "+IDs+" ) union select * from v_disp_st_bolumler where id in (select bob from t_st_bolumler where id in ( "+IDs+" )) union select * from v_disp_st_bolumler where id in (select bob from t_st_bolumler where id in (select bob from t_st_bolumler where id in ( "+IDs+" ))) union select * from v_disp_st_bolumler where id in (select bob from t_st_bolumler where id in (select bob from v_disp_st_bolumler where id in (select bob from t_st_bolumler where id in ( "+IDs+" )))) union select * from v_disp_st_bolumler where id in (select bob from t_st_bolumler where id in (select bob from t_st_bolumler where id in (select bob from v_disp_st_bolumler where id in (select bob from t_st_bolumler where id in ( "+IDs+" ))))) ) t order by sort1, sort2, sort3, sort4";
    st = con.createStatement();
    rs[0] = st.executeQuery(sql);
    ...

    why didn't it work with prepared statement ?
    thanks
    Mehmet Gunacti

Posting Permissions

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