Topic
8 replies Latest Post - ‏2005-07-27T16:04:32Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic Cursor State not valid

‏2005-07-25T00:43:18Z |
My apologies, but I'm not sure if this should go into the Java forum or DB2, but I'll take a chance....

I have written a very simple java servlet to send a select statement, and return the results. However, I keep receiving a SQLException of "Cursor State not Valid". I am posting the code below.

I have tried checking if the ResultSet is null first, before doing anything.

I'm not sure why I am receiving this error. If it's because it's null, I've already got a check for that. Can anyone help?

Java Code:
<snip>
try {
String query = "select * from APAXSON.F604101 where I$LITM='AC2160'";
out.println("Running query: " + query +"\n\n");
rs = statement.executeQuery(query);
req.setAttribute("resultSet",rs);
req.setAttribute("queryString",query);
//out.println(rs.toString());
statement.close();
} catch (SQLException e){
e.printStackTrace();
}
try {
while (rs.next()) { <------ SQLException (Cursor state not valid) here
out.println("Item number: " + rs.getString("I$LITM")+"\n");
out.println("Unit Price: " + rs.getString("I$UPRC")+"\n");
out.println("Description: " + rs.getString("I$DSC1")+"\n\n");
}

} catch (SQLException e3) {
// TODO Auto-generated catch block
e3.printStackTrace();
}
</snip>
Updated on 2005-07-27T16:04:32Z at 2005-07-27T16:04:32Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    3129 Posts
    ACCEPTED ANSWER

    Re: Cursor State not valid

    ‏2005-07-25T07:23:10Z  in response to SystemAdmin
    Hello.

    Remove "statement.close()" command. You shouldn't close statement before you end scrolling ResultSet object.

    Sincerely,
    Mark B.

    P.S.:
    You should close ResultSet object after end of scrolling.
    --
    ResultSet rs = null;
    try
    {
    rs = statement.executeQuery(query);
    while (rs.next()) {...}
    }
    catch (SQLException ex) {...}
    finally {if (rs!=null) rs.close();}
    --
    • SystemAdmin
      SystemAdmin
      3129 Posts
      ACCEPTED ANSWER

      Re: Cursor State not valid

      ‏2005-07-25T11:50:53Z  in response to SystemAdmin
      Yep! That did it. Many thanks, Mark!

      Any reason why I am getting garbage from one of my fields?

      When I use, rs.getString("fieldname") I get this value "C1C3F2F1F6F0404040404040404040404040" instead of "AC2160". I know that, because it's in my where clause of my select statement and if it didn't exist, I wouldn't have returned it. And the field length is only supposed to be 18 characters. This is like 36 characters.

      Forgive the questions. This is my first time using Java against DB2 on iSeries, and it's quite different than your standard DB.

      Thanks!
      • SystemAdmin
        SystemAdmin
        3129 Posts
        ACCEPTED ANSWER

        Re: Cursor State not valid

        ‏2005-07-26T00:52:08Z  in response to SystemAdmin
        FWOW: "C1C3F2F1F6F0404040404040404040404040" is the hex value, in EBCDIC,
        for AC2160 right padded with blanks.

        If this is the only character field in your record, then it's probably a
        CCSID issue causing a failure to translate correctly to ASCII. Possibly
        your table is CCSID 65535, which is Hex, i.e, don't convert to ASCII. Or
        maybe your JDBC is misconfigured. Unfortunately, I have just enough
        experience to make a guess like this.

        Sam

        <apaxson@decorativeconcepts.com> wrote in message
        news:926816250.1122292283538.JavaMail.wassrvr@ltsgwas007.sby.ibm.com...
        > Yep! That did it. Many thanks, Mark!
        >
        > Any reason why I am getting garbage from one of my fields?
        >
        > When I use, rs.getString("fieldname") I get this value
        > "C1C3F2F1F6F0404040404040404040404040" instead of "AC2160". I know that,
        > because it's in my where clause of my select statement and if it didn't
        > exist, I wouldn't have returned it. And the field length is only supposed
        > to be 18 characters. This is like 36 characters.
        >
        > Forgive the questions. This is my first time using Java against DB2 on
        > iSeries, and it's quite different than your standard DB.
        >
        > Thanks!

        • SystemAdmin
          SystemAdmin
          3129 Posts
          ACCEPTED ANSWER

          Re: Cursor State not valid

          ‏2005-07-26T01:06:17Z  in response to SystemAdmin
          Saml,

          Then you must have lots of experience, because you are dead on! Some of our records are set to CCSID 65535. In order to translate the EBCDIC to ASCII, I had to add another parameter to my JDBC URL:

          ;translate binary=true

          Thanks so much, everyone! Now, I'll just have to figure out how to pass my ResultSet to my JSP, without closing the statement.
          • SystemAdmin
            SystemAdmin
            3129 Posts
            ACCEPTED ANSWER

            Re: Cursor State not valid

            ‏2005-07-26T15:39:31Z  in response to SystemAdmin
            You should also strongly consider of not using the 65535 CCSID to store your
            data.

            Kent Milligan, DB2 & BI team
            PartnerWorld for Developers, iSeries
            kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
            >>> www.iseries.ibm.com/db2
            (opinions stated are not necessarily those of my employer)
            • SystemAdmin
              SystemAdmin
              3129 Posts
              ACCEPTED ANSWER

              Re: Cursor State not valid

              ‏2005-07-27T15:52:34Z  in response to SystemAdmin
              Kent Milligan wrote:
              > You should also strongly consider of not using the 65535 CCSID to store your
              > data.
              >

              Another perspective: It's not my files that are the problem, it's the
              vendor's files that are wrong.

              I do agree with your point, though.

              -Stevers
              • SystemAdmin
                SystemAdmin
                3129 Posts
                ACCEPTED ANSWER

                Re: Cursor State not valid

                ‏2005-07-27T16:04:32Z  in response to SystemAdmin
                Steve,

                As is the case in my situation. Some of the software we are running (i.e. JDE and others) uses 65535 for only some of the data. Not much we can do there....
      • SystemAdmin
        SystemAdmin
        3129 Posts
        ACCEPTED ANSWER

        Re: Converting from another code page

        ‏2005-07-26T05:57:35Z  in response to SystemAdmin
        May be it will be helpful.
        Try this:
        --
        declare global temporary table t(c char(10) ccsid 65535)
        on commit preserve rows;
        insert into session.t values 'string';
        select c, cast(cast(c as char(10) for bit data) as char(10) ccsid 37)
        from session.t;
        --