Re: getting the number of affected rows

Hans Bulfone wrote:
> i need a way to get the number of affected rows of an
> sql update or delete expression, but it seems this is
> not supported by the current clsql version.

The number of affector rows can be handy. Unfortunately, the SQL
specification does not include that ability, thus CommonSQL does not
include that ability:
http://www.lispworks.com/documentation/lw445/LWRM/html/lwref-460.htm

> this could be easily implemented, at least for postgresql and mysql,
> by changing database-execute-command to return the number
> of affected rows (got via PQcmdTuples or mysql_affected_rows)
> and execute-command to return the result of database-execute-command.

Yes, some SQL implementations do provide that ability since it can be
useful information. I suspect the standard SQL approach would be to
lock the table, perform a SELECT count(*) matching the rows you'll be
modifying, perform your EXECUTE-COMMAND, then unlock the table.

Obviously, if commands could return counts in SQL, then that
SELECT step could be avoided.

> what do you think of this?

Pros:
 - more efficient than the above SELECT first approach above

Cons:
 - not backwards compatible with CommonSQL (not a huge problem)
 - can be worked around by above SELECT method [relatively inefficent] 
   or by using CLSQL backend internals like
   (mysql:mysql_affected_rows (clsql-sys::database-mysql-ptr db))
   [not portable across backends, depends on undocumented features] 
 - not known to be able to supported by all supported backends.
   One of the advantages (and goals) of CLSQL is try to have
   neutrality toward sql backends. Obviously, this isn't completely
   provided. However, adding a fundemental feature to EXECUTE-COMMAND
   should be able to be supported by ideally all CLSQL backends.
   db-aodbc (not a popular backend) likely can't
   support this. However, db-aodbc can not support all CLSQL as it.
   ODBC and Oracle are more popular backends. I think
   those backends would need to support this if extension were to be added,
   documented, and offically supported.

> i can write a patch with the proposed changes for postgresql and mysql,
> (and perhaps sqlite2/3).

Very good. For mysql, it's a one line addition (as above). What do you
think about adding support ODBC and Oracle in addition to the backends
you mentioned?

Also, additions would need to be made to the test suite to verify
proper working of this over time. The update to the documentation
would be quite simple.

I realize this task may seem larger than you originally thought. You
might prefer to use one of the alternative approaches above rather
than working on a robust addition to CLSQL.

Kevin