Hi Steve:
The IBM documentation for their driver is nearly silent on the subject of
stored procedures. The only mention they make in the Perldoc of DBD::DB2 is
the following:
Attributes are not generally required as the statement
will be "described" and appropriate values will be used.
However, attributes are required under the following
conditions:
. . .
- Statement is a CALL to an unregistered stored procedure
. . .
Even though attributes are not always required, providing
them can improve performance as it may make the "describe"
step unnecessary. Specifically, 'db2_type' and 'SCALE'
must either be provided in the attributes or must be
obtained automatically via SQLDescribeParam.
I'm not sure how you would get access to an unregistered stored procedure in
DB2, however, as the CREATE PROCEDURE sql command apparently registers the
procedure during its creation. Perhaps they can be "unregistered" somehow.
We haven't made much use of stored procedures in our DB2 systems here and
I've never tried to access one via Perl (only C), so I'm just a journeyman,
here.
Stph
> -----Original Message-----
> From: Steven Lembark [mailto:[EMAIL PROTECTED]]
> Sent: Monday, April 29, 2002 8:39 AM
> To: [EMAIL PROTECTED]
> Subject: RE: How to call stored procedure?
>
>
>
>
> -- Stephen Keller <[EMAIL PROTECTED]>
>
> > Ed, you asked about stored procedures in DBD::DB2 land...
> >
> > According to the IBM CLI documentation, you can only call a stored
> > procedure with parameter place holders, which implies that
> you must use
> > bind_param() after you've prepared the statement instead of
> passing the
> > values in directly (as you show). I've not tried this with
> Perl, but I
> > would expect it to work something like the following:
> >
> > $sth=$dbh->prepare( 'CALL MYPROC(?,?)' );
> > $sth->bind_param(0, 'test');
> > $sth->bind_param(1, 'missing');
> > $sth->execute();
> >
> >
> > In the CLI Stored procedures use the same SQLExecuteDirect or
> > SQLPrepare/SQLExecute C calls that regular queries use.
> The Perl driver
> > also uses these C calls, so using CALL in a prepare
> statement should work
> > in the DBD::DB2 driver just fine. One caveat, however.
> IBM does not
> > document any stored procedure call examples in its driver
> docs. This
> > suggests the possibility that the IBM driver does not
> support such calls.
> > For confirmation or an example, you might try a search on
> the IBM DB2
> > website: http://www.ibm.com/db2.
>
> If you prepare the query with placeholders and use
> exec-with-arguments I thought DBI/DBD::Oracle dealt with
> binding the parameters on the fly as the query was passed
> through to Oracle (i.e., the bind_param, above, are
> implicit). Might be worth checking the code if it saves a
> lot of bind_param calls throughout the original perl code.
>
> --
> Steven Lembark 2930 W. Palmer
> Workhorse Computing Chicago, IL 60647
> +1 800 762 1582
>
