possible bug in sa16 PHP sasql_affected_rows?

[former_SQLA_member1694873]

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?  

[Former Member · Accepted solution]

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.

[former_SQLA_member1694873 · Accepted solution]

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

[Former Member]

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.

[former_SQLA_member1694873]

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?

[former_SQLA_member1694873]

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().

[Former Member]

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