DB2 5.0 stored procedures and servlets

Daniel Kirby

DB2 5.0 stored procedures and servlets
Dear All, I am trying to call a DB2 5.0 (mainframe) stored procedure from a
java servlet.
I am using driver COM.ibm.db2.jdbc.app.DB2Driver on a NT server using
servletexpress.
The stored procedure returns one result set.
Trying to call a stored procedure with IN parameters fails on
"executeQuery".
I tried to call a new stored procedure with no IN or OUT parameters.
This time the executeQuery worked, but I get a CLI failure on rs.next(),
telling me that I have executed something out of sequence.
What else can you do after an executeQuery other than rs.next()??
There is a problem either with my code or with the driver.
Has anyone else experienced similar problems?
I know that stored procedures can be called in servlets.
All I know is that at the moment it cannot be done by me.
I will be grateful for any pointers.



Steven Camitta

Re: DB2 5.0 stored procedures and servlets
(in response to Daniel Kirby)
Daniel,
Are you closing the result set?
rs.close()
Rgds,
Steve


-----Original Message-----
From: Daniel Kirby [mailto:[login to unmask email]
Sent: Friday, December 10, 1999 11:43 AM
To: [login to unmask email]
Subject: DB2 5.0 stored procedures and servlets


Dear All, I am trying to call a DB2 5.0 (mainframe) stored procedure from a
java servlet.
I am using driver COM.ibm.db2.jdbc.app.DB2Driver on a NT server using
servletexpress.
The stored procedure returns one result set.
Trying to call a stored procedure with IN parameters fails on
"executeQuery".
I tried to call a new stored procedure with no IN or OUT parameters.
This time the executeQuery worked, but I get a CLI failure on rs.next(),
telling me that I have executed something out of sequence.
What else can you do after an executeQuery other than rs.next()??
There is a problem either with my code or with the driver.
Has anyone else experienced similar problems?
I know that stored procedures can be called in servlets.
All I know is that at the moment it cannot be done by me.
I will be grateful for any pointers.








Daniel Kirby

Re: DB2 5.0 stored procedures and servlets
(in response to Steven Camitta)
Thanks for the reply. My problem seems to be that I am not even reaching the stored procedure, let alone returning a result set. The stored procedure execute count is not incrementing. I
moved the servlet to the OS390 WebSphere platform, hoping that having both servlet and stored procedure on the same platform would help, but no. The stored procedure works when called by a
mainframe program. I am using the following java, which seems pretty standard:

CallableStatement CStmt = con.prepareCall("{call STPROC6}");
ResultSet rs = CStmt.executeQuery();

Do I have to use SQLJ instead to make this work?

Steven Camitta wrote:

> Daniel,
> Are you closing the result set?
> rs.close()
> Rgds,
> Steve
>
> -----Original Message-----
> From: Daniel Kirby [mailto:[login to unmask email]
> Sent: Friday, December 10, 1999 11:43 AM
> To: [login to unmask email]
> Subject: DB2 5.0 stored procedures and servlets
>
> Dear All, I am trying to call a DB2 5.0 (mainframe) stored procedure from a
> java servlet.
> I am using driver COM.ibm.db2.jdbc.app.DB2Driver on a NT server using
> servletexpress.
> The stored procedure returns one result set.
> Trying to call a stored procedure with IN parameters fails on
> "executeQuery".
> I tried to call a new stored procedure with no IN or OUT parameters.
> This time the executeQuery worked, but I get a CLI failure on rs.next(),
> telling me that I have executed something out of sequence.
> What else can you do after an executeQuery other than rs.next()??
> There is a problem either with my code or with the driver.
> Has anyone else experienced similar problems?
> I know that stored procedures can be called in servlets.
> All I know is that at the moment it cannot be done by me.
> I will be grateful for any pointers.
>
>
>
>
>
>
>
>



Daniel Kirby

Re: DB2 5.0 stored procedures and servlets
(in response to Daniel Kirby)
Just after my last e-mail, the DBA granted access to the package to public and the stored procedure call now works. Progress. I then tried to call a simple stored procedure that uses an
integer output parameter and a string input-output parameter, but I get a -113 SQLCODE (invalid characters in a string) when performing "executeQuery()":

CallableStatement CStmt = con.prepareCall("{call STPROC3[?,?]}");
CStmt.registerOutParameter(1,java.sql.Types.INTEGER);
CStmt.setString(2,"P1234567");
CStmt.registerOutParameter(2,java.sql.Types.VARCHAR);
ResultSet rs = CStmt.executeQuery();

Any ideas?

Steven Camitta wrote:

> Daniel,
> Are you closing the result set?
> rs.close()
> Rgds,
> Steve
>
> -----Original Message-----
> From: Daniel Kirby [mailto:[login to unmask email]
> Sent: Friday, December 10, 1999 11:43 AM
> To: [login to unmask email]
> Subject: DB2 5.0 stored procedures and servlets
>
> Dear All, I am trying to call a DB2 5.0 (mainframe) stored procedure from a
> java servlet.
> I am using driver COM.ibm.db2.jdbc.app.DB2Driver on a NT server using
> servletexpress.
> The stored procedure returns one result set.
> Trying to call a stored procedure with IN parameters fails on
> "executeQuery".
> I tried to call a new stored procedure with no IN or OUT parameters.
> This time the executeQuery worked, but I get a CLI failure on rs.next(),
> telling me that I have executed something out of sequence.
> What else can you do after an executeQuery other than rs.next()??
> There is a problem either with my code or with the driver.
> Has anyone else experienced similar problems?
> I know that stored procedures can be called in servlets.
> All I know is that at the moment it cannot be done by me.
> I will be grateful for any pointers.
>
>
>
>
>
>
>
>



John O'Conner

Re: DB2 5.0 stored procedures and servlets
(in response to Daniel Kirby)
We had this problem back in June, and I opened a PMR with IBM. The problem
is passing a string as an INOUT parameter. The workaround for this is to
use one parameter for input and separate parameter for output. I have
included a portion of the interchange I had with IBM.

<snip>

-IBM -5740XYR00 - 99/06/28-12:01-
I have spoken with John about this problem and the suspected bug. I
have also suggested a couple of possible temp. bypasses/workarounds
(e.g. change stored proc to use IN or OUT but not INOUT parms)
to try until a fix can be provided. We also discussed the possibility
of providing the fix for this as part of the JDBC driver rewrite
(PQ19814) rather than fixing in the current JDBC driver. John will
perform some additional testing to try to determine if they will require
a fix before the closure of PQ19814. Tom T.
-IM06949 LNK1 -5740XYR00 - 99/06/29-15:11-
RESPOND ELECTRONICALLY.
Tom,
I tried swapping the setString and the registerOutputParm. I got a
different error. I intend to try using IN and OUT only and not using
INOUT. I'll let you know how it goes. John O'

-IBM -5740XYR00 - 99/06/30-08:07-
Hello John, Thanks for the update. Please let us know the results.
Regards, Maria Weinerth
-IM06949 LNK1 -5740XYR00 - 99/07/02-12:55-
RESPOND ELECTRONICALLY.
I replaced the INOUT parm with two parms, one IN the other OUT, and it
worked. I believe that this is an adequate work around for us until the
new JDBC/SQLJ driver is available.

<snip> (four months later, same PRM)

-IBM -5740XYR00 - 99/11/12-13:56-
Hi John,
Sorry for the delay in responding....We will be implementing the
sqlj 1.0 specification. As you've probably already figured out, we
didn't make the aug 15 date. I just got out of a sqlj status meeting,
and it looks like we will make the Nov. 30th date.
Thanks for your patience,
Tom Brooks

-SYSTEM GENERATED TEXT--5740XYR00 - 99/12/10-21:58-
THIS INACTIVE RECORD WILL CLOSE IN 36 DAYS UNLESS REASSIGNED OR UPDATED
.

-----Original Message-----
From: Daniel Kirby [mailto:[login to unmask email]
Sent: Wednesday, December 15, 1999 9:03 AM
To: [login to unmask email]
Subject: Re: DB2 5.0 stored procedures and servlets


Just after my last e-mail, the DBA granted access to the package to public
and the stored procedure call now works. Progress. I then tried to call a
simple stored procedure that uses an
integer output parameter and a string input-output parameter, but I get a
-113 SQLCODE (invalid characters in a string) when performing
"executeQuery()":

CallableStatement CStmt = con.prepareCall("{call STPROC3[?,?]}");
CStmt.registerOutParameter(1,java.sql.Types.INTEGER);
CStmt.setString(2,"P1234567");
CStmt.registerOutParameter(2,java.sql.Types.VARCHAR);
ResultSet rs = CStmt.executeQuery();

Any ideas?

Steven Camitta wrote:

> Daniel,
> Are you closing the result set?
> rs.close()
> Rgds,
> Steve
>
> -----Original Message-----
> From: Daniel Kirby [mailto:[login to unmask email]
> Sent: Friday, December 10, 1999 11:43 AM
> To: [login to unmask email]
> Subject: DB2 5.0 stored procedures and servlets
>
> Dear All, I am trying to call a DB2 5.0 (mainframe) stored procedure from
a
> java servlet.
> I am using driver COM.ibm.db2.jdbc.app.DB2Driver on a NT server using
> servletexpress.
> The stored procedure returns one result set.
> Trying to call a stored procedure with IN parameters fails on
> "executeQuery".
> I tried to call a new stored procedure with no IN or OUT parameters.
> This time the executeQuery worked, but I get a CLI failure on rs.next(),
> telling me that I have executed something out of sequence.
> What else can you do after an executeQuery other than rs.next()??
> There is a problem either with my code or with the driver.
> Has anyone else experienced similar problems?
> I know that stored procedures can be called in servlets.
> All I know is that at the moment it cannot be done by me.
> I will be grateful for any pointers.
>
>
>
the
>
>
>
>
>










Daniel Kirby

Re: DB2 5.0 stored procedures and servlets
(in response to John O'Conner)
Thanks for the reply. I solved my own problem. I used sample code that had [] around the parameters in the escape clause:
CallableStatement CStmt = con.prepareCall("{call STPROC3[?,?]}");.
I found another code sample that used () instead and it worked. I guess I must be using the corrected version of JDBC driver since the INOUT parameter in my stored procedure worked fine. It
is so hard doing this stufff to know whether the mistake is mine or not. This time it was mine, with an assist from www.utcluj.ro.

"O'Conner, John" wrote:

> We had this problem back in June, and I opened a PMR with IBM. The problem
> is passing a string as an INOUT parameter. The workaround for this is to
> use one parameter for input and separate parameter for output. I have
> included a portion of the interchange I had with IBM.
>
> <snip>
>
> -IBM -5740XYR00 - 99/06/28-12:01-
> I have spoken with John about this problem and the suspected bug. I
> have also suggested a couple of possible temp. bypasses/workarounds
> (e.g. change stored proc to use IN or OUT but not INOUT parms)
> to try until a fix can be provided. We also discussed the possibility
> of providing the fix for this as part of the JDBC driver rewrite
> (PQ19814) rather than fixing in the current JDBC driver. John will
> perform some additional testing to try to determine if they will require
> a fix before the closure of PQ19814. Tom T.
> -IM06949 LNK1 -5740XYR00 - 99/06/29-15:11-
> RESPOND ELECTRONICALLY.
> Tom,
> I tried swapping the setString and the registerOutputParm. I got a
> different error. I intend to try using IN and OUT only and not using
> INOUT. I'll let you know how it goes. John O'
>
> -IBM -5740XYR00 - 99/06/30-08:07-
> Hello John, Thanks for the update. Please let us know the results.
> Regards, Maria Weinerth
> -IM06949 LNK1 -5740XYR00 - 99/07/02-12:55-
> RESPOND ELECTRONICALLY.
> I replaced the INOUT parm with two parms, one IN the other OUT, and it
> worked. I believe that this is an adequate work around for us until the
> new JDBC/SQLJ driver is available.
>
> <snip> (four months later, same PRM)
>
> -IBM -5740XYR00 - 99/11/12-13:56-
> Hi John,
> Sorry for the delay in responding....We will be implementing the
> sqlj 1.0 specification. As you've probably already figured out, we
> didn't make the aug 15 date. I just got out of a sqlj status meeting,
> and it looks like we will make the Nov. 30th date.
> Thanks for your patience,
> Tom Brooks
>
> -SYSTEM GENERATED TEXT--5740XYR00 - 99/12/10-21:58-
> THIS INACTIVE RECORD WILL CLOSE IN 36 DAYS UNLESS REASSIGNED OR UPDATED
> .
>
> -----Original Message-----
> From: Daniel Kirby [mailto:[login to unmask email]
> Sent: Wednesday, December 15, 1999 9:03 AM
> To: [login to unmask email]
> Subject: Re: DB2 5.0 stored procedures and servlets
>
> Just after my last e-mail, the DBA granted access to the package to public
> and the stored procedure call now works. Progress. I then tried to call a
> simple stored procedure that uses an
> integer output parameter and a string input-output parameter, but I get a
> -113 SQLCODE (invalid characters in a string) when performing
> "executeQuery()":
>
> CallableStatement CStmt = con.prepareCall("{call STPROC3[?,?]}");
> CStmt.registerOutParameter(1,java.sql.Types.INTEGER);
> CStmt.setString(2,"P1234567");
> CStmt.registerOutParameter(2,java.sql.Types.VARCHAR);
> ResultSet rs = CStmt.executeQuery();
>
> Any ideas?
>
> Steven Camitta wrote:
>
> > Daniel,
> > Are you closing the result set?
> > rs.close()
> > Rgds,
> > Steve
> >
> > -----Original Message-----
> > From: Daniel Kirby [mailto:[login to unmask email]
> > Sent: Friday, December 10, 1999 11:43 AM
> > To: [login to unmask email]
> > Subject: DB2 5.0 stored procedures and servlets
> >
> > Dear All, I am trying to call a DB2 5.0 (mainframe) stored procedure from
> a
> > java servlet.
> > I am using driver COM.ibm.db2.jdbc.app.DB2Driver on a NT server using
> > servletexpress.
> > The stored procedure returns one result set.
> > Trying to call a stored procedure with IN parameters fails on
> > "executeQuery".
> > I tried to call a new stored procedure with no IN or OUT parameters.
> > This time the executeQuery worked, but I get a CLI failure on rs.next(),
> > telling me that I have executed something out of sequence.
> > What else can you do after an executeQuery other than rs.next()??
> > There is a problem either with my code or with the driver.
> > Has anyone else experienced similar problems?
> > I know that stored procedures can be called in servlets.
> > All I know is that at the moment it cannot be done by me.
> > I will be grateful for any pointers.
> >
> >
> >
> the
> >
> >
> >
> >
> >
>
>
>
>
>
>
>
>
>
>