FreeTDS Development Group

[Greg Reeves <GregR AT sportodds.com>]

Hi All,

summary: 
I am wondering if anyone knows a way of executing a stored procedure using
placeholders to bind the variables to as "?" don't seem to be supported
using freeTDS and MSSQL.

details:
I have been trying to improve the performance of a perl script I am writing,
which runs a
stored proc on an MSSQL 2000 server and processes the results.

I am using perl 5.6.0 on redhat 7.2, DBI-1.35, DBD-Sybase-0.95, freeTDS 0.61
talking to a  MSSQL 2000 database on NT 4 sp 4.

After connecting to the database, I prepare my sql statement. Then I execute
it. It returns 20 rows thru which I loop and process the results. The
performance hit seems to be on preparing the sql statement and also on
executing it. Here is a sample of my code:

$sql_1 = "myStoredProc ".$int_1.",".$int_2.",'".$string_1."',".$int_3;
if (!($sth_1 = $dbh_1->prepare($sql_1))) {
        #error handling
}

if (!($sth_1->execute())) {
        #error handling
}

while (($globalKey_sp1, $tableName_sp1, $action_sp1, $localID_sp1) =
$sth_1->fetchrow_array()) {
#loop thru rows and do stuff
}

$sth_1->finish();

I am wondering if anyone knows a way of executing a stored procedure using
placeholders to bind the variables to as "?" don't seem to be supported
using freeTDS and MSSQL. For example I would like to do something like:

$sql_1 = "exec myStoredProc ?,?,?,?";
$sth_1 = $dbh_1->prepare($sql_1)
$sth_1->bind_param(1, $int_1);
$sth_1->bind_param(2, $int_2);
$sth_1->bind_param(3, $string_1);
$sth_1->bind_param(4, $int_3);

I read from Michael Peppler's notes in DBD-Sybase that this method uses the
ct_param() API calls to execute an RPC call to the server (instead of a
language call) which removes all SQL compiling
from the request, and achieves better performance.

Is there any way to achieve this using freeTDS with MSSQL 2000? Michael
Peppler mentioned that the freeTDS developers were working on something like
this, so I was wondering if you guys knew of any workarounds or progress in
this area?

Thanks for any help or directions you can point me in!

Regards, Greg Reeves



I am using perl 5.6.0 on redhat 7.2
DBI-1.35
DBD-Sybase-0.95
freeTDS 0.61
talking to a 
MSSQL 2000 database on NT 4 sp 4