DBD::Sybase stored procedure question

[martymart]

Fellow Monks,

I'm using Sybase 11 on a linux box. my perl script is on a windows machine. And I'm using Sybase.pm version: 1.02 Revision: 1.47
Basically I have a stored procedure 'spSubComponentADD' which simply puts strings into 'SubComponentName' and 'SubComponentDescription'. 'SubComponentID' is an ID number thats created by the stored procedure, it simply starts at 1 and increments with each new row. I have a perl script that calls 'spSubComponentADD' and puts values into 'SubComponentName' and 'SubComponentDescription'.
My problem is with the returned value, its killing the script. How do I get around this?

my $sth = $dbh->prepare("exec spSubComponentADD \@SubComponentName = ?
+, \@SubComponentDescription = ?, \@SubComponentID = ? OUTPUT ");
$sth->execute($subcomponentname, $subcomponentdescription)
        or handle_error ("Can't execute SQL" . $sth->errstr(), $dbh, $
+sth);
my (@data) = $sth->func('syb_output_params');

I can get this thing to work by changing the stored proecdure so that it doesn't return a value, but I'm gonna have to get this working for loads of stored procedures, so this really isn't an option. I realise that this is probably a problem with the syntax I'm using. Any advice (or sample working code) would be greatly appreciated.
Martymart

[mpeppler]

You have to tell the driver what type of values you are sending over, and you need to bind three values as you have three placeholders.

The following works:

#!/usr/bin/perl -w
                                                                      
+          
use strict;
use DBI qw(:sql_types);
                                                                      
+          
my $dbh = DBI->connect('dbi:Sybase:server=SYBASE;database=testdb', 'sa
+', 'some password');
                                                                      
+          
my $sth = $dbh->prepare("exec spSubComponentAdd \@subComponentName = ?
+,
\@subComponentDescription  = ?, \@subComponentId = ? output");

$sth->bind_param(1, "one");    # bind_param() defaults to SQL_CHAR.
$sth->bind_param(2, "two");
$sth->bind_param(3, undef, SQL_INTEGER);
$sth->execute;
                                                                      
+          
my (@data) = $sth->func('syb_output_params');
print "Got @data\n";

Michael

[martymart]

Hi Michael and ccn,
Thanks to you both for your prompt responses. Michael, just so you know, I tried your solution, and while my script didn't die or anything, nothing got added to the database unfortunately. I had previously tried a solution very similar to this, but also without success.
ccn's code did work for me however, so I was wondering if the binding is necessary??
Thanks,
Martymart

[mpeppler]

No the binding isn't absolutely necessary. You can always use a "normal" SQL statement and pass all the values as literals.

However, I wonder why nothing got added - maybe you should run a test with DBI->trace(3) and check that things actually run. There might be something that fails but that doesn't produce an error message for some reason.

Michael

[ccn]

You may try something like this:

$_ = $dbh->quote($_)
  for
  $subcomponentname,
  $subcomponentdescription;

my $sth = $dbh->prepare(<<"SQL")  or die "Can't prepare: " . $sth->err
+str();
declare \@SubComponentID int
exec spSubComponentADD $subcomponentname,
                       $subcomponentdescription,
                       \@SubComponentID OUTPUT
SQL

$sth->execute()
  or handle_error ("Can't execute SQL" . $sth->errstr(), $dbh, $sth);

my (@data) = $sth->func('syb_output_params');

Update: code shorten and $dbh->quote added