mysql callable statement problem

Discussion in 'Java' started by lightning, Feb 11, 2007.

  1. lightning

    lightning Guest

    my Mysql is 5.0.18

    In database "test" I created a procedure:

    mysql> delimiter //
    mysql> create procedure demoSp(IN inputParam VARCHAR(255),INOUT
    inOutParam INT)
    -> begin
    -> declare z INT;
    -> set z=inOutParam + 1;
    -> set inOutParam = z;
    -> select inputParam;
    -> select CONCAT('dfjd',inputParam);
    -> end//

    just add 1 to the IN/OUT param and select some stuff.
    In fact this code is from the Mysql connector/J 's doc

    In java code I wrote:

    CallableStatement cStmt = con.prepareCall("{call demoSp(?,?)}");
    cStmt.registerOutParameter(2, Types.INTEGER);
    cStmt.setString("inputParam", "abc");
    cStmt.setInt("inOutParam",2);

    boolean had = cStmt.execute();
    while (had) {
    ResultSet rs = cStmt.getResultSet();
    while (rs.next())
    System.out.println(rs.getString(1));
    had=cStmt.getMoreResults();
    }
    System.out.println(cStmt.getInt("inOutParam"));
    con.close();

    -----------------------------------
    but the result is an error says "Out of range value adjusted for
    column 'inOutParam' at row 1"
    so I go on the net and search something says that I can solve this by
    edit the my.ini, just remove the "STRICT_TRANS_TABLES" from the sql-
    mode option and restart the db server.

    I did this ,and the result give me:


    abc
    dfjdabc
    1


    no matter how I change the value of the "inOutParam", it just return
    "1".

    By accident , I just replace the line "

    cStmt.setInt("inOutParam",2);

    to

    cStmt.setInt(1,2);

    the result changes to be this:

    2
    dfjd2
    3


    My default charset is set to UTF-8

    my connector/J version is 3.1.0
    my jdk version 1.5.0

    Is this a bug or did I miss something???
    lightning, Feb 11, 2007
    #1
    1. Advertising

  2. lightning

    petersprc Guest

    Hi,

    You can upgrade to the latest connector version which hopefully should
    fix this.

    There's a bug report here:
    http://bugs.mysql.com/bug.php?id=15464

    On Feb 11, 1:15 am, "lightning" <> wrote:
    > my Mysql is 5.0.18
    >
    > In database "test" I created a procedure:
    >
    > mysql> delimiter //
    > mysql> create procedure demoSp(IN inputParam VARCHAR(255),INOUT
    > inOutParam INT)
    > -> begin
    > -> declare z INT;
    > -> set z=inOutParam + 1;
    > -> set inOutParam = z;
    > -> select inputParam;
    > -> select CONCAT('dfjd',inputParam);
    > -> end//
    >
    > just add 1 to the IN/OUT param and select some stuff.
    > In fact this code is from the Mysql connector/J 's doc
    >
    > In java code I wrote:
    >
    > CallableStatement cStmt = con.prepareCall("{call demoSp(?,?)}");
    > cStmt.registerOutParameter(2, Types.INTEGER);
    > cStmt.setString("inputParam", "abc");
    > cStmt.setInt("inOutParam",2);
    >
    > boolean had = cStmt.execute();
    > while (had) {
    > ResultSet rs = cStmt.getResultSet();
    > while (rs.next())
    > System.out.println(rs.getString(1));
    > had=cStmt.getMoreResults();}
    >
    > System.out.println(cStmt.getInt("inOutParam"));
    > con.close();
    >
    > -----------------------------------
    > but the result is an error says "Out of range value adjusted for
    > column 'inOutParam' at row 1"
    > so I go on the net and search something says that I can solve this by
    > edit the my.ini, just remove the "STRICT_TRANS_TABLES" from the sql-
    > mode option and restart the db server.
    >
    > I did this ,and the result give me:
    >
    > abc
    > dfjdabc
    > 1
    >
    > no matter how I change the value of the "inOutParam", it just return
    > "1".
    >
    > By accident , I just replace the line "
    >
    > cStmt.setInt("inOutParam",2);
    >
    > to
    >
    > cStmt.setInt(1,2);
    >
    > the result changes to be this:
    >
    > 2
    > dfjd2
    > 3
    >
    > My default charset is set to UTF-8
    >
    > my connector/J version is 3.1.0
    > my jdk version 1.5.0
    >
    > Is this a bug or did I miss something???
    petersprc, Feb 11, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Steve Gilbert
    Replies:
    3
    Views:
    2,234
    Gordon Beaton
    Apr 14, 2004
  2. ChaosKCW
    Replies:
    1
    Views:
    275
    Larry Bates
    Feb 25, 2006
  3. Replies:
    2
    Views:
    331
    =?ISO-8859-1?Q?Arne_Vajh=F8j?=
    Oct 10, 2006
  4. exiquio
    Replies:
    2
    Views:
    520
    exiquio
    Oct 7, 2008
  5. Ulrich Eckhardt
    Replies:
    6
    Views:
    95
    Peter Otten
    Jul 12, 2013
Loading...

Share This Page