SPUG: bind_param and stored procedure

[Adam Monsen]

On 15/07/02 09:38 -0700, Martin Korb wrote:
> I  get  the  following  error  message   when  I use bind_param on
> the following stored procedure called within a perl script.
>
> Syntax error near @P1. Can not execute......
>
> here is the code snippet:
> my $sql_set_identity_shopper = "EXEC dt_IdentityOnOff(?,?)";
> my $sth_set_identity_shopper =
> $dbh_7->prepare($sql_set_identity_shopper);
> $sth_set_identity_shopper->bind_param(1,"tblshopper");
> $sth_set_identity_shopper->bind_param(2,"on");
> $sth_set_identity_shopper->execute() or die "can not execute $!";

Hmm, I'm not sure why this isn't working. I'm guessing this code will be
run against Microsoft SQL server or Sybase.

Next time, please include the code you used to create the database
handle. This will tell us what database you're using and help us debug
problems. For instance, did you set RaiseError => 1? If so, you don't
need the "or die" after the call to $sth->execute().

If this is a query with static data being inserted for the placeholders,
why are you using bind parameters? Or is this just a test?


> When I  do  not specify the order (which I really shouldn't have to)
> I get a different but also fatal error:
>
> Can't bind unknown placeholder 'tblshopper'
>
> here is the code snippet:
>
> my $sql_set_identity_shopper = "EXEC dt_IdentityOnOff(?,?)";
> my $sth_set_identity_shopper =
> $dbh_7->prepare($sql_set_identity_shopper);
> $sth_set_identity_shopper->bind_param("tblshopper","on");
> $sth_set_identity_shopper->execute() or die "can not execute $!";

Try this:

my $sql_set_identity_shopper = "EXEC dt_IdentityOnOff(?,?)";
my $sth_set_identity_shopper =
$dbh_7->prepare($sql_set_identity_shopper);
$sth_set_identity_shopper->execute("tblshopper","on")
  or die "can not execute $!";


> SQL 7.0 Stored Procedure:
>
> CREATE PROCEDURE dt_identityOnOff
>   @tablename varchar(64),
>   @action varchar(3)
> as
> if @tablename = 'tblshopper'
> begin
>   if @action = 'on'
>       set identity_insert tblshopper on
>   else
>     set identity_insert tblshopper off
> end
> else
> begin
>   if @action = 'on'
>     set identity_insert tblfirms on
>   else
>     set identity_insert tblfirms off
> end
>
>
>
> Any suggestion on what I am overlooking.
>
> Thanks
>
>
>
> Martin

-- 
Adam Monsen

 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     POST TO: spug-list at pm.org       PROBLEMS: owner-spug-list at pm.org
      Subscriptions; Email to majordomo at pm.org:  ACTION  LIST  EMAIL
  Replace ACTION by subscribe or unsubscribe, EMAIL by your Email-address
 For daily traffic, use spug-list for LIST ;  for weekly, spug-list-digest
     Seattle Perl Users Group (SPUG) Home Page: http://seattleperl.org