Rick,
>Everything you wrote sounds good, except for one thing. I use the result
>count from a select or delete for logical branching within my code. I.e. I
>perform a select and if rowcount is zero, then I can do an update. That is
>a problem.
>
>Any thoughts?
The basic rowcount functionality should work ok, because the mysql
client returns right values when I do SELECTs, UPDATEs, INSERTs,
or DELETEs. What I meant was that small differences between table types
may occur if you receive, for example, a duplicate key error when you do an
insert of a set of rows, or a transaction deadlock which results in a
rollback. We have to test these special cases and check that they
return the same (sensible) values for all table types. Thus, if you get
an error from your SQL statement, currently you have to be extra careful
to check what the rowcount returned really means.
I looked at the MySQL manual section 7.21 and found the following:
....
If you specify the keyword IGNORE in an INSERT with many value rows, any
rows that duplicate an existing PRIMARY or UNIQUE key in the table are
ignored and are not inserted. If you do not specify IGNORE, the insert
is aborted if there is any row that duplicates an existing key value.
You can determine with the C API function mysql_info() how many rows
were inserted into the table.
....
But when I tested with Innobase and MyISAM tables, I noticed that
MySQL seems to assume the IGNORE option for all INSERTS, for both table types.
On the other hand, ANSI SQL specifies that a duplicate key error should
result in the rollback of the whole SQL statement, i.e., no rows should
be inserted.
The conclusion is that you should not rely on the current behavior if
the SQL statement returns an error. The error handling procedure is likely
to change.
Regards,
Heikki
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
