Calling a Java Stored Procedure from another Java Stored Stored Procedure

Calling a Java Stored Procedure from another Java Stored Stored Procedure

Post by rudolp.. » Thu, 23 Mar 2000 04:00:00



Hi,
I'm using the stored procedure builder of DB2 UDB v6.1 on NT to create a
Java Stored Procedure to call another Java Stored Procedure. Both of
them belong to the same project in stored procedure builder.
The sp that calls another sp has the code as follows:
// Calling another java sp -- ErrorHandler
ErrorHandler err = new ErrorHandler();
err.execute("Test #1", 100, "Testing #1");
When I want to build the sp which calls another sp from within, it gave
me an error as follows:
C:\IBMVJava\ide\tools\com-ibm-db2-tools-dev-spb-ivj\spb\bld953747549650\
com\intertrac\datamart\sp\SelectBillShipAddr.java:18: Class
com.intertrac.datamart.sp.ErrorHandler not found in type declaration.
ErrorHandler err = new ErrorHandler();
Does anyone have any clue of how this can be properly done ? Please let
me know.
Thanks,
ra

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Calling a Java Stored Procedure from another Java Stored Stored Procedure

Post by Serge Riela » Fri, 24 Mar 2000 04:00:00


Hi,

I don't think that you can have nested stored procedure calls in DB2
V6.1
(this will be in V7 though).
There are however workarounds AFAIK... maybe Doug has some thoughts on
how these look like?
Cheers
Serge

 
 
 

Calling a Java Stored Procedure from another Java Stored Stored Procedure

Post by Andrew V. Bennet » Sat, 25 Mar 2000 04:00:00


Hi,

Yes it is possible.  We do it on a project I am currently working on and
it works just fine.
You merely instantiated the class and call it's method.

Example:

SP#1 class is called ABC and implements the method aBC, takes one input
string and returns a single output string.

SP#2 class is called XYZ and implents the method xYZ and returns a
ResultSet. It calls SP#1 passing in a value and retrieving the output
value.

So in SP#2 you would call SP#1 in the following way:

try

{
   ABC.aBC('A',out);

Quote:}

Where 'A' is the value being passed as the input parameter, and out is the
value that ABC.aBC will return. At this point you can do whatever you want
with the out variable.  Use as input into another called SP etc.....
ABC.aBC could also return a resultset that you could loop through or have
XYZ.xYZ return out.

Andrew V. Bennett
Chicago Systems Group


> Hi,

> I don't think that you can have nested stored procedure calls in DB2
> V6.1
> (this will be in V7 though).
> There are however workarounds AFAIK... maybe Doug has some thoughts on
> how these look like?
> Cheers
> Serge

 
 
 

Calling a Java Stored Procedure from another Java Stored Stored Procedure

Post by rudolp.. » Sat, 25 Mar 2000 04:00:00


Hi,
I tried to call SP1 from SP2 by first instantiating an instance of SP1
inside SP2. However, when I build the code in SP2 (I am using v6.1
Stored Procedure Builder), the stored procedure builder returns me with
this error:
C:\IBMVJava\ide\tools\com-ibm-db2-tools-dev-spb-ivj\spb\bld953920972071\
SP2.java:16: Class SP1 not found in type declaration.
SP1 sp1 = new SP1()
And the build was unsuccessful. Do you have any clue on this ?
Thanks,
ra


> Hi,

> Yes it is possible. We do it on a project I am currently working on
and
> it works just fine.
> You merely instantiated the class and call it's method.

> Example:

> SP#1 class is called ABC and implements the method aBC, takes one
input
> string and returns a single output string.

> SP#2 class is called XYZ and implents the method xYZ and returns a
> ResultSet. It calls SP#1 passing in a value and retrieving the output
> value.

> So in SP#2 you would call SP#1 in the following way:

> try

> {
> ABC.aBC('A',out);
> }

> Where 'A' is the value being passed as the input parameter, and out is
the
> value that ABC.aBC will return. At this point you can do whatever you
want
> with the out variable. Use as input into another called SP etc.....
> ABC.aBC could also return a resultset that you could loop through or
have
> XYZ.xYZ return out.

> Andrew V. Bennett
> Chicago Systems Group


> > Hi,

> > I don't think that you can have nested stored procedure calls in DB2
> > V6.1
> > (this will be in V7 though).
> > There are however workarounds AFAIK... maybe Doug has some thoughts
on
> > how these look like?
> > Cheers
> > Serge

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

Calling a Java Stored Procedure from another Java Stored Stored Procedure

Post by Andrew V. Bennet » Sat, 25 Mar 2000 04:00:00


Oh yes I forgot to mention some of the lovely feature of the GUI stored
procedure builder.  In order to compile a SP that is called by another SP
using the SP builder, you must move the class file(s) of the called SP from
within its JAR and place it in the directory of the Stored Procedure Builder
tool.  So if you have Stored Procedure Builder installed in D:\sqllib\  you
must put the class file in D:\sqllib\spb  The builder should find it when it
tries the compile.  Why it needs to look there and not in /sqllib/function
is very strange to me.  But that's how we have gotten around that.

Andrew


> Hi,
> I tried to call SP1 from SP2 by first instantiating an instance of SP1
> inside SP2. However, when I build the code in SP2 (I am using v6.1
> Stored Procedure Builder), the stored procedure builder returns me with
> this error:
> C:\IBMVJava\ide\tools\com-ibm-db2-tools-dev-spb-ivj\spb\bld953920972071\
> SP2.java:16: Class SP1 not found in type declaration.
> SP1 sp1 = new SP1()
> And the build was unsuccessful. Do you have any clue on this ?
> Thanks,
> ra


> > Hi,

> > Yes it is possible. We do it on a project I am currently working on
> and
> > it works just fine.
> > You merely instantiated the class and call it's method.

> > Example:

> > SP#1 class is called ABC and implements the method aBC, takes one
> input
> > string and returns a single output string.

> > SP#2 class is called XYZ and implents the method xYZ and returns a
> > ResultSet. It calls SP#1 passing in a value and retrieving the output
> > value.

> > So in SP#2 you would call SP#1 in the following way:

> > try

> > {
> > ABC.aBC('A',out);
> > }

> > Where 'A' is the value being passed as the input parameter, and out is
> the
> > value that ABC.aBC will return. At this point you can do whatever you
> want
> > with the out variable. Use as input into another called SP etc.....
> > ABC.aBC could also return a resultset that you could loop through or
> have
> > XYZ.xYZ return out.

> > Andrew V. Bennett
> > Chicago Systems Group


> > > Hi,

> > > I don't think that you can have nested stored procedure calls in DB2
> > > V6.1
> > > (this will be in V7 though).
> > > There are however workarounds AFAIK... maybe Doug has some thoughts
> on
> > > how these look like?
> > > Cheers
> > > Serge

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

Calling a Java Stored Procedure from another Java Stored Stored Procedure

Post by rudolp.. » Wed, 29 Mar 2000 04:00:00


I tried to put the class file (.class) of the stored procedure being
called to C:\SQLLIB\spb, i.e. where DB2 stored procedure builder is
installed. I then compiled the sp that calls this sp, but the same
problem occur. I guess that could be some other problem. Anyway, thanks
for your help. Should you have any more tricks, please let me know.

ra



> Oh yes I forgot to mention some of the lovely feature of the GUI
stored
> procedure builder. In order to compile a SP that is called by another
SP
> using the SP builder, you must move the class file(s) of the called SP
from
> within its JAR and place it in the directory of the Stored Procedure
Builder
> tool. So if you have Stored Procedure Builder installed in D:\sqllib\
you
> must put the class file in D:\sqllib\spb The builder should find it
when it
> tries the compile. Why it needs to look there and not in
/sqllib/function
> is very strange to me. But that's how we have gotten around that.

> Andrew


> > Hi,
> > I tried to call SP1 from SP2 by first instantiating an instance of
SP1
> > inside SP2. However, when I build the code in SP2 (I am using v6.1
> > Stored Procedure Builder), the stored procedure builder returns me
with
> > this error:

C:\IBMVJava\ide\tools\com-ibm-db2-tools-dev-spb-ivj\spb\bld953920972071\

- Show quoted text -

> > SP2.java:16: Class SP1 not found in type declaration.
> > SP1 sp1 = new SP1()
> > And the build was unsuccessful. Do you have any clue on this ?
> > Thanks,
> > ra


> > > Hi,

> > > Yes it is possible. We do it on a project I am currently working
on
> > and
> > > it works just fine.
> > > You merely instantiated the class and call it's method.

> > > Example:

> > > SP#1 class is called ABC and implements the method aBC, takes one
> > input
> > > string and returns a single output string.

> > > SP#2 class is called XYZ and implents the method xYZ and returns a
> > > ResultSet. It calls SP#1 passing in a value and retrieving the
output
> > > value.

> > > So in SP#2 you would call SP#1 in the following way:

> > > try

> > > {
> > > ABC.aBC('A',out);
> > > }

> > > Where 'A' is the value being passed as the input parameter, and
out is
> > the
> > > value that ABC.aBC will return. At this point you can do whatever
you
> > want
> > > with the out variable. Use as input into another called SP
etc.....
> > > ABC.aBC could also return a resultset that you could loop through
or
> > have
> > > XYZ.xYZ return out.

> > > Andrew V. Bennett
> > > Chicago Systems Group


> > > > Hi,

> > > > I don't think that you can have nested stored procedure calls in
DB2
> > > > V6.1
> > > > (this will be in V7 though).
> > > > There are however workarounds AFAIK... maybe Doug has some
thoughts
> > on
> > > > how these look like?
> > > > Cheers
> > > > Serge

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.