Java Stored Procedures (V7.2)

[Rhino]

Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a
Throwable back to the calling program as an OUT parameter?

If yes, what datatype should I use when registering the Throwable as an OUT
parameter and what datatype should I use in the CREATE PROCEDURE and DROP
PROCEDURE statements?

Here's what I tried:
- the method signature for the stored procedure included: Throwable[]
throwable
- every catch block in the stored procedure included: throwable[0] =
sql_excp
- the calling program included: callStmt.registerOutParameter(5,
Types.OTHER);
- the calling program included: Throwable throwable = (Throwable)
callStmt.getObject(5);
- the CREATE PROCEDURE and DROP PROCEDURE statements used VARCHAR(4000) as
the datatype for the Throwable

My calling program and stored procedure both compiled just fine and the
preparation procedure which included DROP PROCEDURE for the old version of
the stored proc and CREATE PROCEDURE for the new version of the stored proc
worked without errors. Unfortunately, I got a CLI0123E error (SQL data type
out of range) at runtime, specifically when I call the execute() method on
the CallableStatement.

I can't see anything in the documentation that suggests any other approach
so it seems to me that:
a) it simply isn't permissible to pass a Throwable from a Java stored
procedure to its calling program
-OR-
b) some datatype other than VARCHAR(4000) should be used in the CREATE
PROCEDURE and DROP PROCEDURE statements.

Since I don't see anything remotely resembling Throwable in Table 31 (SQL
Data Types Mapped to Java Declarations) in the Application Building Guide,
I'm strongly inclined to think that a) is the truth. I'd just like to be
sure that's right before I give up on passing a Throwable to the caller.

--
Rhino
---
rhino1 AT sympatico DOT ca
"If you're getting something for nothing, you're not using your own credit
card."

[Sean McKeough]

I'm surprised that the JVM found a match for the method with a throwable
type in the signature...we generate the method signature based on the
cataloged parameter types (for a varchar it'll be a String type), so
this must mean that the classloader is treating the throwable like a
string?!? Sounds wierd...is it really a string? If not, maybe you can
pass it as a byte array?? (If this is a real structure couldn't you have
problems with endian differences etc)?

Rhino wrote:

Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a
Throwable back to the calling program as an OUT parameter?

If yes, what datatype should I use when registering the Throwable as an OUT
parameter and what datatype should I use in the CREATE PROCEDURE and DROP
PROCEDURE statements?

Here's what I tried:
- the method signature for the stored procedure included: Throwable[]
throwable
- every catch block in the stored procedure included: throwable[0] =
sql_excp
- the calling program included: callStmt.registerOutParameter(5,
Types.OTHER);
- the calling program included: Throwable throwable = (Throwable)
callStmt.getObject(5);
- the CREATE PROCEDURE and DROP PROCEDURE statements used VARCHAR(4000) as
the datatype for the Throwable

My calling program and stored procedure both compiled just fine and the
preparation procedure which included DROP PROCEDURE for the old version of
the stored proc and CREATE PROCEDURE for the new version of the stored proc
worked without errors. Unfortunately, I got a CLI0123E error (SQL data type
out of range) at runtime, specifically when I call the execute() method on
the CallableStatement.

I can't see anything in the documentation that suggests any other approach
so it seems to me that:
a) it simply isn't permissible to pass a Throwable from a Java stored
procedure to its calling program
-OR-
b) some datatype other than VARCHAR(4000) should be used in the CREATE
PROCEDURE and DROP PROCEDURE statements.

Since I don't see anything remotely resembling Throwable in Table 31 (SQL
Data Types Mapped to Java Declarations) in the Application Building Guide,
I'm strongly inclined to think that a) is the truth. I'd just like to be
sure that's right before I give up on passing a Throwable to the caller.

[Rhino]

First, let me apologize for being so late getting back to this thread. I got
snarled up in some other things.

I'm not quite sure what you are telling me. Are you suggesting that it *is*
possible to pass a Throwable from a stored procedure to a calling program?
For what it's worth, I've tried defining the throwable as a VARCHAR(4000)
and as a VARCHAR(4000) FOR BIT DATA in the CREATE PROCEDURE statement; the
statement executed without errors but both had the same runtime error,
CLI0123E. If there is a way to pass a Throwable back to my calling program,
I'd be interested in trying it.

Basically, I'd like to return more than just the SqlState, SqlCode, and
SqlMessage back to my calling program; I think it would improve the quality
of the error handling if a Throwable could be passed back so that a
stackTrace could be obtained. I've already satisfied myself that a
stackTrace can be obtained within the stored procedure, converted to a
String, and then passed back but now I'd like to see if there is some way to
pass the Throwable itself back so that the calling program could get the
stackTrace from it. Otherwise, I'd have to have a small extra method in each
stored procedure for turning the stacktrace into a String.

Any pointers you could offer would be greatly appreciated; you appear to be
*the* stored procedures expert at the Toronto lab.

Rhino
"Sean McKeough" <mc******@nospam.ca.ibm.com> wrote in message
news:c5**********@hanover.torolab.ibm.com...

I'm surprised that the JVM found a match for the method with a throwable
type in the signature...we generate the method signature based on the
cataloged parameter types (for a varchar it'll be a String type), so
this must mean that the classloader is treating the throwable like a
string?!? Sounds wierd...is it really a string? If not, maybe you can
pass it as a byte array?? (If this is a real structure couldn't you have
problems with endian differences etc)?

Rhino wrote:

Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter?

If yes, what datatype should I use when registering the Throwable as an OUT parameter and what datatype should I use in the CREATE PROCEDURE and DROP PROCEDURE statements?

Here's what I tried:
- the method signature for the stored procedure included: Throwable[]
throwable
- every catch block in the stored procedure included: throwable[0] =
sql_excp
- the calling program included: callStmt.registerOutParameter(5,
Types.OTHER);
- the calling program included: Throwable throwable = (Throwable)
callStmt.getObject(5);
- the CREATE PROCEDURE and DROP PROCEDURE statements used VARCHAR(4000) as the datatype for the Throwable

My calling program and stored procedure both compiled just fine and the
preparation procedure which included DROP PROCEDURE for the old version of the stored proc and CREATE PROCEDURE for the new version of the stored proc worked without errors. Unfortunately, I got a CLI0123E error (SQL data type out of range) at runtime, specifically when I call the execute() method on the CallableStatement.

I can't see anything in the documentation that suggests any other approach so it seems to me that:
a) it simply isn't permissible to pass a Throwable from a Java stored
procedure to its calling program
-OR-
b) some datatype other than VARCHAR(4000) should be used in the CREATE
PROCEDURE and DROP PROCEDURE statements.

Since I don't see anything remotely resembling Throwable in Table 31 (SQL Data Types Mapped to Java Declarations) in the Application Building Guide, I'm strongly inclined to think that a) is the truth. I'd just like to be
sure that's right before I give up on passing a Throwable to the caller.

[Sean McKeough]

I used to be the stored procedures guy (in a former life). :-)

With parameter style java, we went to a standard argument method called
SQLJ part1...it defines what types can be used in an sp interface etc.,
and the only one that isn't a base sql type is the result set object. So
we're pretty constrained by these rules. I think you'll be stuck with
passing the callstack info around as database type parameters.

Rhino wrote:

First, let me apologize for being so late getting back to this thread. I got
snarled up in some other things.

I'm not quite sure what you are telling me. Are you suggesting that it *is*
possible to pass a Throwable from a stored procedure to a calling program?
For what it's worth, I've tried defining the throwable as a VARCHAR(4000)
and as a VARCHAR(4000) FOR BIT DATA in the CREATE PROCEDURE statement; the
statement executed without errors but both had the same runtime error,
CLI0123E. If there is a way to pass a Throwable back to my calling program,
I'd be interested in trying it.

Basically, I'd like to return more than just the SqlState, SqlCode, and
SqlMessage back to my calling program; I think it would improve the quality
of the error handling if a Throwable could be passed back so that a
stackTrace could be obtained. I've already satisfied myself that a
stackTrace can be obtained within the stored procedure, converted to a
String, and then passed back but now I'd like to see if there is some way to
pass the Throwable itself back so that the calling program could get the
stackTrace from it. Otherwise, I'd have to have a small extra method in each
stored procedure for turning the stacktrace into a String.

Any pointers you could offer would be greatly appreciated; you appear to be
*the* stored procedures expert at the Toronto lab.

Rhino
"Sean McKeough" <mc******@nospam.ca.ibm.com> wrote in message
news:c5**********@hanover.torolab.ibm.com...

I'm surprised that the JVM found a match for the method with a throwable
type in the signature...we generate the method signature based on the
cataloged parameter types (for a varchar it'll be a String type), so
this must mean that the classloader is treating the throwable like a
string?!? Sounds wierd...is it really a string? If not, maybe you can
pass it as a byte array?? (If this is a real structure couldn't you have
problems with endian differences etc)?

Rhino wrote:

Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass
a
Throwable back to the calling program as an OUT parameter?

If yes, what datatype should I use when registering the Throwable as an
OUT
parameter and what datatype should I use in the CREATE PROCEDURE and
DROP
PROCEDURE statements?

Here's what I tried:
- the method signature for the stored procedure included: Throwable[]
throwable
- every catch block in the stored procedure included: throwable[0] =
sql_excp
- the calling program included: callStmt.registerOutParameter(5,
Types.OTHER);
- the calling program included: Throwable throwable = (Throwable)
callStmt.getObject(5);
- the CREATE PROCEDURE and DROP PROCEDURE statements used VARCHAR(4000)
as
the datatype for the Throwable

My calling program and stored procedure both compiled just fine and the
preparation procedure which included DROP PROCEDURE for the old version
of
the stored proc and CREATE PROCEDURE for the new version of the stored
proc
worked without errors. Unfortunately, I got a CLI0123E error (SQL data
type
out of range) at runtime, specifically when I call the execute() method
on
the CallableStatement.

I can't see anything in the documentation that suggests any other
approach
so it seems to me that:
a) it simply isn't permissible to pass a Throwable from a Java stored
procedure to its calling program
-OR-
b) some datatype other than VARCHAR(4000) should be used in the CREATE
PROCEDURE and DROP PROCEDURE statements.

Since I don't see anything remotely resembling Throwable in Table 31
(SQL
Data Types Mapped to Java Declarations) in the Application Building
Guide,
I'm strongly inclined to think that a) is the truth. I'd just like to be
sure that's right before I give up on passing a Throwable to the caller.

[Rhino]

Fair enough! Thanks for the information, Sean!

Rhino

"Sean McKeough" <mc******@nospam.ca.ibm.com> wrote in message
news:c5**********@hanover.torolab.ibm.com...

I used to be the stored procedures guy (in a former life). :-)

With parameter style java, we went to a standard argument method called
SQLJ part1...it defines what types can be used in an sp interface etc.,
and the only one that isn't a base sql type is the result set object. So
we're pretty constrained by these rules. I think you'll be stuck with
passing the callstack info around as database type parameters.

Rhino wrote:

First, let me apologize for being so late getting back to this thread. I got snarled up in some other things.

I'm not quite sure what you are telling me. Are you suggesting that it *is* possible to pass a Throwable from a stored procedure to a calling program? For what it's worth, I've tried defining the throwable as a VARCHAR(4000) and as a VARCHAR(4000) FOR BIT DATA in the CREATE PROCEDURE statement; the statement executed without errors but both had the same runtime error,
CLI0123E. If there is a way to pass a Throwable back to my calling program, I'd be interested in trying it.

Basically, I'd like to return more than just the SqlState, SqlCode, and
SqlMessage back to my calling program; I think it would improve the quality of the error handling if a Throwable could be passed back so that a
stackTrace could be obtained. I've already satisfied myself that a
stackTrace can be obtained within the stored procedure, converted to a
String, and then passed back but now I'd like to see if there is some way to pass the Throwable itself back so that the calling program could get the
stackTrace from it. Otherwise, I'd have to have a small extra method in each stored procedure for turning the stacktrace into a String.

Any pointers you could offer would be greatly appreciated; you appear to be *the* stored procedures expert at the Toronto lab.

Rhino
"Sean McKeough" <mc******@nospam.ca.ibm.com> wrote in message
news:c5**********@hanover.torolab.ibm.com...

I'm surprised that the JVM found a match for the method with a throwable
type in the signature...we generate the method signature based on the
cataloged parameter types (for a varchar it'll be a String type), so
this must mean that the classloader is treating the throwable like a
string?!? Sounds wierd...is it really a string? If not, maybe you can
pass it as a byte array?? (If this is a real structure couldn't you have
problems with endian differences etc)?

Rhino wrote:

Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass

a

Throwable back to the calling program as an OUT parameter?

If yes, what datatype should I use when registering the Throwable as an

OUT

parameter and what datatype should I use in the CREATE PROCEDURE and

DROP

PROCEDURE statements?

Here's what I tried:
- the method signature for the stored procedure included: Throwable[]
throwable
- every catch block in the stored procedure included: throwable[0] =
sql_excp
- the calling program included: callStmt.registerOutParameter(5,
Types.OTHER);
- the calling program included: Throwable throwable = (Throwable)
callStmt.getObject(5);
- the CREATE PROCEDURE and DROP PROCEDURE statements used VARCHAR(4000)

as

the datatype for the Throwable

My calling program and stored procedure both compiled just fine and the
preparation procedure which included DROP PROCEDURE for the old version

of

the stored proc and CREATE PROCEDURE for the new version of the stored

proc

worked without errors. Unfortunately, I got a CLI0123E error (SQL data

type

out of range) at runtime, specifically when I call the execute() method

on

the CallableStatement.

I can't see anything in the documentation that suggests any other

approach

so it seems to me that:
a) it simply isn't permissible to pass a Throwable from a Java stored
procedure to its calling program
-OR-
b) some datatype other than VARCHAR(4000) should be used in the CREATE
PROCEDURE and DROP PROCEDURE statements.

Since I don't see anything remotely resembling Throwable in Table 31

(SQL

Data Types Mapped to Java Declarations) in the Application Building

Guide,

I'm strongly inclined to think that a) is the truth. I'd just like to

besure that's right before I give up on passing a Throwable to the caller.