Rene van Leeuwen wrote:
> Suppose I have an sp like this:
> create procedure sp_callableSample
> (@p1 int, @p2 varchar(255) out)
> as
>
> begin
> select @p1, @p2
> select @p2 = 'The Answer to Life, the Univers
> e, and Everything.'
> return 42
>
>
> ++++ This works OK:
> my $sth = $dbh->prepare("declare [EMAIL PROTECTED] varchar(255)
> exec sp_callableSample 3 , [EMAIL PROTECTED] OUTPUT");
> $sth->execute();
> if ($sth->{syb_result_type} == CS_PARAM_RESULT){
> print "OUTPUTVAL = $row[0]\n";
> }
>
> ++++ This works fine too on an sp that only takes 1 input integer param:
> my $sth = $dbh->prepare("exec sp_test1 ?");
> $sth->bind_param(1, $in , SQL_INTEGER );
> $sth->execute();
>
> ++++ But this doesn't work:
> my $sth = $dbh->prepare("declare [EMAIL PROTECTED] varchar(255)
> exec sp_callableSample ? , [EMAIL PROTECTED] OUTPUT");
> $sth->bind_params(1, $in, SQL_INTEGER);
> $sth->execute();
>
> DBD::Sybase::db prepare failed: Server message number=7332 severity=15
> state=1 line=1 server=ase12503 procedure=DBD1 text=The untyped variable
> ? is allowed only in in a WHERE clause or the SET clause of an UPDATE
> statement or the VALUES list of an INSERT statement
The reason you get that error is that to use placeholders with a stored
procedure the first word in the SQL batch *must* be EXEC. Your statement
above gets interpreted (by DBD::Sybase) as a normal SQL statement with
placeholders, and therefore sent to the ct_dynamic() API, which fails
(as you see).
The correct way to do this with placeholders is this:
my $sth = $dbh->prepare("exec sp_callableSample ?, ? OUTPUT");
my $in = 1234;
$sth->bind_param(1, $in , SQL_INTEGER );
$sth->bind_param(2, undef);
$sth->execute();
do {
print "Result type: $sth->{syb_result_type}\n";
while(my $row = $sth->fetch) {
print "@$row\n";
}
} while($sth->{syb_more_results});
which produces the following output:
Result type: 4040
Use of uninitialized value in join or string at /tmp/proc.pl line 17.
1234
Result type: 4043
42
Result type: 4042
The Answer to Life, the Universe, and Everything.
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html
