Update problems in PHP + Sybase

[Ashish]

Hi,

I am using PHP 4.2.3 and Sybase 12.0

I want to insert records in multiple tables in a database.
I also want to use transactions so that I can rollback in case there
was a problem.

I tried the following code, but I can not figure out how to retrieve
the "status" from the batch.

If I run the code in sql, it works fine. If I run it in PHP, it again
works fine. But I cannot retrieve the "status". And I get the
following error message in the php log file.

=== START OF CODE ====

$sql = <<< SQL

declare @sys_code smallint

select @sys_code = isnull(max(code),0) from t_system
select @sys_code = @sys_code + 1

begin tran

insert into t_system values (@sys_code, "$sys_name", "$sys_fullname",
"$descr", $multi, $days_taken, $sys_group)

if (@@error > 0)
begin
rollback tran
select ret_status = -1
end
else
begin
commit tran
select ret_status = 0
end

SQL;
$dbh = sybase_connect($db_server, $db_login, $db_password,
$db_charset);
$res = sybase_query($sql, $dbh);
$rc = sybase_result($res,0,0);
if ($rc == 0) {
echo "The record was updated." ;
} else {
echo "Problem while updating database. The return code was : $rc" ;
}
=== END OF CODE ===

=== START OF ERROR MESSAGE ===

[18-Mar-2004 15:09:20] PHP Warning: sybase_result(): supplied
argument is not a valid Sybase result resource in ...

=== END OF ERROR MESSAGE ===

"Ashish" <ou****@india.com> wrote in message
news:dd**************************@posting.google.c om...

Hi,

I am using PHP 4.2.3 and Sybase 12.0

I want to insert records in multiple tables in a database.
I also want to use transactions so that I can rollback in case there
was a problem.

I tried the following code, but I can not figure out how to retrieve
the "status" from the batch.

If I run the code in sql, it works fine. If I run it in PHP, it again
works fine. But I cannot retrieve the "status". And I get the
following error message in the php log file.

=== START OF CODE ====

$sql = <<< SQL

declare @sys_code smallint

select @sys_code = isnull(max(code),0) from t_system
select @sys_code = @sys_code + 1

begin tran

insert into t_system values (@sys_code, "$sys_name", "$sys_fullname",
"$descr", $multi, $days_taken, $sys_group)

if (@@error > 0)
begin
rollback tran
select ret_status = -1
end
else
begin
commit tran
select ret_status = 0
end

SQL;
$dbh = sybase_connect($db_server, $db_login, $db_password,
$db_charset);
$res = sybase_query($sql, $dbh);
$rc = sybase_result($res,0,0);
if ($rc == 0) {
echo "The record was updated." ;
} else {
echo "Problem while updating database. The return code was : $rc" ;
}
=== END OF CODE ===

=== START OF ERROR MESSAGE ===

[18-Mar-2004 15:09:20] PHP Warning: sybase_result(): supplied
argument is not a valid Sybase result resource in ...

=== END OF ERROR MESSAGE ===

Since you are doing an insert, you do not get a result set back.
You need to remove the sybase_result() line.
Here is what I use for an update test, insert should be the same

<?
function update($query) {
if (!$sybResult = @sybase_query($query,$this->conn)) {
error_log ("Sybase Error : - Query failed ");
exit();
}
return true;

}
?>

[Ashish]

> Since you are doing an insert, you do not get a result set back.

You need to remove the sybase_result() line.

Thanks for your reply.

I actually want to get a result set back. A 1 column 1 row result set.
So, in my code, I say :

if (@@error > 0)
begin
rollback tran
select -1
end
else
begin
commit
select 0
end

This is after the Insert. So, I should get a -1 or a 0 back.
When I run this same query from isql, I do get a 1 col 1 row result set back.

[Michael Peppler]

On Thu, 18 Mar 2004 17:17:46 -0800, Ashish wrote:

Since you are doing an insert, you do not get a result set back. You
need to remove the sybase_result() line.

Thanks for your reply.

I actually want to get a result set back. A 1 column 1 row result set. So,
in my code, I say :

if (@@error > 0)
begin
rollback tran
select -1
end
else
begin
commit
select 0
end

This is after the Insert. So, I should get a -1 or a 0 back. When I run
this same query from isql, I do get a 1 col 1 row result set back.

I think the problem is related to the fact that PHP doesn't really support
multiple result sets. I haven't looked closely at the code recently, but I
know that it was something that bothered me with PHP's implementation of
Sybase connectivity.

Michael
--
Michael Peppler Data Migrations, Inc.
mp******@peppler.org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html