Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Problems Binding Parameters for Stored Procedure

Reply
Thread Tools

Problems Binding Parameters for Stored Procedure

 
 
geoffrobinson
Guest
Posts: n/a
 
      04-10-2007
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

 
Reply With Quote
 
 
 
 
4i4ko Trevi4ko
Guest
Posts: n/a
 
      04-10-2007
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


On Tue, 10 Apr 2007 10:42:54 -0700, geoffrobinson wrote:

> 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


 
Reply With Quote
 
 
 
 
4i4ko Trevi4ko
Guest
Posts: n/a
 
      04-10-2007
.... or try so:
instead of:
$sth->bind_param(1, "some string");
this one:
$sth->bind_param(1, "'some string'");

 
Reply With Quote
 
4i4ko Trevi4ko
Guest
Posts: n/a
 
      04-10-2007
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
 
Reply With Quote
 
geoffrobinson
Guest
Posts: n/a
 
      04-11-2007
On Apr 10, 3:49 pm, 4i4ko Trevi4ko <(E-Mail Removed)> wrote:
> ... 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.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
passing parameters to stored procedure from crystal reports kavitha N via .NET 247 ASP .Net 1 02-15-2011 08:20 AM
Stupid question - binding an <asp:label> --> stored procedure returning a scalar? Homer J. Simpson ASP .Net 3 08-27-2007 06:15 PM
GridView Paging: Binding to Oracle Stored Procedure sck10 ASP .Net 0 06-21-2007 09:43 PM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
Dropdownlist binding to stored procedure matija.svec@gmail.com ASP .Net Web Controls 0 06-05-2006 06:01 PM



Advertisments