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

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

    Re: Cursor State not valid

    ‏2005-07-25T07:23:10Z  
    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

    Re: Cursor State not valid

    ‏2005-07-25T11:50:53Z  
    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();}
    --
    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

    Re: Cursor State not valid

    ‏2005-07-26T00:52:08Z  
    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!
    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

    Re: Cursor State not valid

    ‏2005-07-26T01:06:17Z  
    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!

    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

    Re: Converting from another code page

    ‏2005-07-26T05:57:35Z  
    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!
    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;
    --
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Cursor State not valid

    ‏2005-07-26T15:39:31Z  
    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.
    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

    Re: Cursor State not valid

    ‏2005-07-27T15:52:34Z  
    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)
    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

    Re: Cursor State not valid

    ‏2005-07-27T16:04:32Z  
    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
    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....