SAP SQL Anywhere

Question

I'm getting different results from sasql_affected_rows() with sasql_query() vs sasql_stmt_execute() and I can't see what I'm doing wrong. (I use sasql_stmt_execute() because I use prepared statements.)

After sasql_query, I get the proper number of rows, while after sasql_stmt_execute, the affected rows are always zero. Here's my test code:

    $cstring = "DSN=demo16;UID=DBA;PWD=sql";
    $sql = "UPDATE Customers SET ID = ID;";
//test 1
$conn = sasql_connect( $cstring );
sasql_set_option( $conn, "auto_commit", "off" );
print "sasql_query() $sql\n";
$rs = sasql_query( $conn, $sql );
print "sasql_affected_rows()=".sasql_affected_rows($conn)."\n";
sasql_close( $conn );

//test 2
$conn = sasql_connect( $cstring );
sasql_set_option( $conn, "auto_commit", "off" );
print "sasql_stmt_execute() $sql\n";
$pstmt = sasql_prepare( $conn, $sql );
sasql_stmt_execute( $pstmt );   
print "sasql_affected_rows()=".sasql_affected_rows($conn)."\n";
sasql_close( $conn );

and the output is:

sasql_query() UPDATE Customers SET ID = ID;
sasql_affected_rows()=126
sasql_stmt_execute() UPDATE Customers SET ID = ID;
sasql_affected_rows()=0

Has anyone else seen this?

Accepted Answer

2

The function you need to use for the sasql_stmt_execute() case is a different one

 int sasql_stmt_affected_rows( sasql_stmt $stmt )

http://dcx.sybase.com/index.html#sa160/en/dbprogramming/php-stmt-affected-rows.html

Calling sasql_affected_rows() is picking up the result set from the connection object and thus the last query.

Hopefully this will make this a little clearer.

permanent link

answered 01 Dec '14, 16:01

Nick Elson S...
7.3k●35●107
accept rate: 32%

Nick, thanks a lot for looking into this - big help! I should have been able to see that :-(

(01 Dec '14, 17:18) Terry Wilkinson

flat view

Answer 2

0

Maybe I missed something important but the operation

UPDATE Customers SET ID = ID;

by definition makes no changes. In SQL anywhere that will always affect 0 rows, and will not even get logged.

The difference here is that the sasql_stmt_execute() execution only performs the update (thus rowcount==0) where as the sasql_affected_rows() execution returns the result set of candidate rows (thus a rowcount >0).

This difference is because of a new feature introduced in version 12.0 documented as "Support for selecting from DML statements" . Look to the 3rd from last item near the bottom.

permanent link

answered 04 Nov '14, 12:16

Nick Elson S...
7.3k●35●107
accept rate: 32%

edited 04 Nov '14, 12:19

Thanks for the answer, Nick - however, I probably used too simple an example. In my real use-case, the update does in fact change some column values, but I still get sasql_affected_rows() returning 0;

Also, if it really has something to do with ID not changing in my example above, why does get sasql_affected_rows() return 126 after sasql_query() in test1, but 0 after sasql_stmt_execute() in test2?

(04 Nov '14, 13:13) Terry Wilkinson

Here's a better example: change the above query to:

UPDATE SalesOrderItems SET Quantity = Quantity + 1;

It gives the results:

sasql_query() UPDATE SalesOrderItems SET Quantity = Quantity + 1;
sasql_affected_rows()=1097
sasql_stmt_execute() UPDATE SalesOrderItems SET Quantity = Quantity + 1;
sasql_affected_rows()=0

1097 for sasql_query() and 0 for sasql_stmt_execute().

(04 Nov '14, 15:06) Terry Wilkinson

It looks like I (or someone else) will need to take a closer/deeper look at this ...

(05 Nov '14, 10:51) Nick Elson S...

flat view

SAP SQL Anywhere

possible bug in sa16 PHP sasql_affected_rows?

Follow this question

Related questions