Problems Binding Parameters for Stored Procedure

[geoffrobinson]

I have a stored procedure I need to call and get a value back from an
input/output parameter. The stored procedure is on a SQL Server 2005
DB. The first parameter is a varchar parameter. The second is a inout
variable which is returning a decimal value.

Via, SQL Server tools I would call it like this:
exec spMyStoredProcedure 'some string', @varname OUTPUT

So after research and tinkering the best code I could come up with is:

my $var1;
my $statement = "exec spMyStoredProcedure '?', ? OUTPUT";
my $sth-> $dbh->prepare($statement);
$sth->bind_param(1, "some string");
$sth->bind_param_inout(2, \$var1, 50);
$sth->execute();
$sth->finish();

The above code is giving me an error that I "can't bind unknown
placeholder '2'. I tried removing the quotes from the first question
mark, and I got a different error. The statement could not be
prepared.

If anyone has any insight into what is wrong, I would greatly
appreciate any help you can give me.

thanks,
Geoff

 

[4i4ko Trevi4ko]

Hi!

I dont know what kind of problem this could be. You can try to make it
more generic:
my $statement = "exec spMyStoredProcedure 'somestring'";
my $sth = $dbh->prepare($statement);
if(!$sth->err) {
$sth->execute();
#go on with fetchrow_array......
} else {
#error handling here

}

and inside of spMyStoredProcedure:
"SELECT value from foo WHERE col = @variable_containing_somestring"
or
"SELECT 'return_value:',value from foo WHERE col =
@variable_containing_somestring"

then scan the output (collected through fetchrow_array) of
spMyStoredProcedure for the desired value from the SELECT.


Best Regards

4i4ko Trevi4ko

 

[4i4ko Trevi4ko]

.... or try so:
instead of:
$sth->bind_param(1, "some string");
this one:
$sth->bind_param(1, "'some string'");

 

[4i4ko Trevi4ko]

another hint:

if you sql-script contains errors (more then one), DBD::ODBC saves in
$sth->errstr only the last produced error, in most cases: "The statement
could not be prepared."

To see/catch all produced errors, you must attach an error handler to
$dbh, like this (see POD documentation of DBI):

my $dbh = DBI->connect(here_comes_connection_string,
"user_name", "password_of_user",
{ RaiseError => 0, AutoCommit => 1, PrintError => 0, PrintWarn => 0}
);

$dbh->{odbc_err_handler} = \&err_handler;

err_handler looks something like:
sub err_handler
{
my($SQLState, $ErrorMessage, $NativeServerError) = @_;
#$SQLState -- ODBC state, from driver
#$ErrorMessage -- comes from server
#$NativeServerError -- error number from db-server

print "' number='.$NativeServerError.' state=ORIGSTATE
odbc_state='.$SQLState.' text='.$ErrorMessage;
# or do what you want here...

return 1; #DBD::ODBC will not ignore the error, if 0 -- ignores error
}



But be aware: MSSQL returns on some operation codes, that looks for
DBD::ODBC like *errors* (on these *errors* err_handler will be invoked),
but these *errors* are de facto "informative messages". Examples:
15338 -- thrown by sp_rename
15477 - thrown after renaming
15070 -- thrown from sp_recompile
Another error numbers (I dont remember what they mean...):
0
5021
5701
15472
15491
15492

This means: you must modify your err_handler so, that on some error codes
the return value 0 is.

Best Regards

 

[geoffrobinson]

... or try so:
instead of:
$sth->bind_param(1, "some string");
this one:
$sth->bind_param(1, "'some string'");

I still have to work out everything.

Currently, I'm only binding the inout parameter and am inserting the
string directly into the statement. The downside is that I can just
rebind and rerun when the string value changes, but that's ok for now.
At least I'm getting the variable value out.

Thanks for your help.