Problems Binding Parameters for Stored Procedure

Discussion in 'Perl Misc' started by geoffrobinson, Apr 10, 2007.

  1. 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
    geoffrobinson, Apr 10, 2007
    #1
    1. Advertising

  2. 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
    4i4ko Trevi4ko, Apr 10, 2007
    #2
    1. Advertising

  3. .... or try so:
    instead of:
    $sth->bind_param(1, "some string");
    this one:
    $sth->bind_param(1, "'some string'");
    4i4ko Trevi4ko, Apr 10, 2007
    #3
  4. 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
    4i4ko Trevi4ko, Apr 10, 2007
    #4
  5. On Apr 10, 3:49 pm, 4i4ko Trevi4ko <> 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.
    geoffrobinson, Apr 11, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. kavitha N via .NET 247

    passing parameters to stored procedure from crystal reports

    kavitha N via .NET 247, Jun 8, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    10,787
    galahad
    Feb 15, 2011
  2. Mike P
    Replies:
    0
    Views:
    3,280
    Mike P
    Jun 19, 2006
  3. sck10
    Replies:
    0
    Views:
    594
    sck10
    Jun 21, 2007
  4. Homer J. Simpson
    Replies:
    3
    Views:
    988
    Homer J. Simpson
    Aug 27, 2007
  5. Dropdownlist binding to stored procedure

    , Jun 5, 2006, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    120
Loading...

Share This Page