DBD::Sybase stored procedure question

martymart has asked for the wisdom of the Perl Monks concerning the following question:

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');
[download]

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

Comment on DBD::Sybase stored procedure question Download Code

Replies are listed 'Best First'.
Re: DBD::Sybase stored procedure question by mpeppler (Vicar) on Sep 10, 2004 at 14:44 UTC
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"; [download] Michael	[reply] [d/l]
Re^2: DBD::Sybase stored procedure question by martymart (Deacon) on Sep 10, 2004 at 15:00 UTC
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	[reply]
Re^3: DBD::Sybase stored procedure question by mpeppler (Vicar) on Sep 10, 2004 at 15:08 UTC
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	[reply]
Re: DBD::Sybase stored procedure question by ccn (Vicar) on Sep 10, 2004 at 13:52 UTC
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');` [download] Update: code shorten and `$dbh->quote` added	[reply] [d/l] [select]