Hi all,
Where I work, we use a lot (thousands) of Sybase stored procedures and have only
recently adopted DBI and DBD::Sybase. Is there any DBIx-type module out there
that handles fetching the return status and output params in an intuitive way?
A quick search of CPAN has revealed nothing.
I have a proof-of-concept module that does this, tentatively called
DBIx::Sybase::StoredProc, but I want to research any similar efforts before I
work much more on it. I think such a module should:
1. Generate a sub that takes the SP arguments as Perl arguments, calls the SP,
and returns the status. This seems to require looking up the argument types in
syscolumns and systypes to find out which arguments must be quoted.
2. For output params, accept scalar refs and fill them in with the output
values. This seems to require looking up the types in order to generate
declarations of T-SQL output variables.
3. Fetch all result sets, checking each one's syb_result_type. Results that are
not output params or the return status should be accumulated using
fetchall_arrayref, or the caller should be allowed to provide a callback to
fetch them. Accumulated results should be available as a list of return values
($status, $arrayref1, $arrayref2, ...) if the call is in list context.
4. Include some form of support for both named and positional parameters.
5. Possibly allow using AUTOLOAD to simplify usage and to cache generated subs.
This can benefit from looking up all database names in sysdatabases and creating
a package named after each one. My module does this when invoked with either of
two syntaxes:
use Module 'server' => $server, 'username' => $username, 'password' =>
$password;
or:
use Module 'dbh' => $dbh;
Thanks
-John
