I share the same problem, and my research on the subject has lead me to the
following:
SQL Server stored procedures can return data in one of two ways - result
sets and output (or input-output) parameters.
Also, SQL Server stemmed from Sybase. Due to this legacy, communications to
SQL Server databases and Sybase databases use the same 'Tabular Data Stream'
protocol (though SQL Server only supports a subset of TDS versions).
You can call SQL Server stored procedures in the following ways:
1) DBI with DBD::ODBC
You can call stored procedures with SQL or even Transact-SQL statements and
iterate through multiple result-sets just as if you had executed a 'select'
SQL statement. However, 'bind_param_inout()' is currently not implemented
by DBD::ODBC, hence you cannot get the values of output parameters. I am
assured by its author, Jeff Urlwin, that this functionality is "imminent".
2) DBI with DBD::Sybase
You also need either the Sybase client libraries (requires a Sybase
license), or the free, open source 'FreeTDS' library. It seems FreeTDS is
intended to compile on Windows (as well as UNIX), but I cannot get the
latest version to build on Windows. DBD::Sybase treats output parameters as
an extra single row result set that is accessible after any real result sets
have been processed (advance to the next result set with a call to
$sth->more_results()).
3) MSSQL::Sqllib
This is not a DBI driver, but is firmly based on Sybperl. It supports calls
to SQL Server stored procedures and can retrieve both result sets and output
parameters, e.g (from memory):
use MSSQL::Sqllib;
$s = MSSQL::Sqllib::sql_init($dsn, $user, $passwd);
@params = ($eg_input_param, \$eg_output_param);
@entire_results = $s->sql_sp("MyStoredProc", \@param,
MSSQL::Sqllib::MULTIPLE_RESULTS, MSSQL::Sqllib::MULTIPLE_ROWS, etc...);
Note that:
* Options 2 & 3 support output params.
* Options 1 & 2 are supposedly platform independant (except for the problem
with compiling FreeTDS on Windows).
* Option 3 is firmly a Windows-only solution (the distribution includes one
or more .dll libraries).
Personally, I will wait a short while for DBD-ODBC to include an
implementation for bind_param_inout() - it is a platform independent
solution that doesn't require Sybase licenses for clients. (If I were more
fluent with perl internals / DBD programming and debugging I would attempt
it myself).
Regards
Lee Jonas
-----Original Message-----
From: Fernando Almeida [mailto:[EMAIL PROTECTED]]
Sent: 16 February 2001 18:38
To: '[EMAIL PROTECTED]'
Cc: Fabiano Bianchi; Christian Pinheiro
Subject: Problems accessing stored procedures in SqlServer 7.0
Hi,
I known this question is similar to another one thousand posted to
this list, and I would like to say that Ive searched the list archives for
hours before send this email.
All the answers that I found are related to Oracle or Sybase, but
not to SqlServer... My problem is how to execute stored procedures in the
SqlServer and retrieve data from it... Ive found a lot of examples
using the functions bind_param and bind_param_out (or something like that).
I tried to use all this techniques, but still cant retrieve the data from
SqlServer.
If anybody has already done this, please point me to some
documentation about....
Thanks,
-----
Fernando Costa de Almeida
[EMAIL PROTECTED]
ICQ 72293951
