Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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: 25%

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: 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

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: 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_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]( "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: 2,843 times

last updated: 01 Dec '14, 17:18