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_stmt_execute() UPDATE Customers SET ID = ID;

Has anyone else seen this?

asked 03 Nov '14, 16:18

Terry%20Wilkinson's gravatar image

Terry Wilkinson
accept rate: 40%

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

 int sasql_stmt_affected_rows( sasql_stmt $stmt )


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%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
accept rate: 29%

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

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%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
accept rate: 29%

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_stmt_execute() UPDATE SalesOrderItems SET Quantity = Quantity + 1;

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...
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 03 Nov '14, 16:18

question was seen: 744 times

last updated: 01 Dec '14, 17:18