Re^4: Perl DBI execute statement

Replies are listed 'Best First'.
Re^5: Perl DBI execute statement by Marshall (Canon) on Dec 14, 2016 at 19:49 UTC
The execute will return a value indicated the number of rows affected. In the case of an UPDATE, it will be the number of rows modified. If no rows are modified, it will return "0E0" which means that the statement "succeeded" (a "true" logical value), but no rows were modified (a numeric zero). If say something is "not right" in the WHERE clause of the update, a 0E0 return value would happen. This is not considered an "error". Method 1: You could try to UPDATE the record and then if that doesn't update the record ("0E0"), then INSERT that record, of course checking that the SQL INSERT succeeded with a "1" value (one row inserted). This is two SQL statements, although just 1 if the record already exists (UPDATE updates the existing row). Method 2: You could also do a simple SELECT on the primary key and see if it is there or not? (just check number of rows returned). If the primary key exists, then UPDATE, else INSERT. I suspect that this is better for your debugging... print the results of the select, update and insert execute values in your debugging code. This is 2 actually executed SQL statements. I think Method 2 will work well for you. Set things up so that the INSERT will never fail due to a "duplicate primary key" error. Yes, it is possible to intercept the "die" from a failed INSERT and do something else, but I think that is ~~too complicated to mess with~~ not appropriate in this situation. Your final code should not have any IGNORE's in the SQL. You have to set things up so that this either never happens (unless real catastrophic error) or you explicitly deal with this exception. IGNORE shouldn't be one of the options. It is a very bad idea to IGNORE something that would normally "crash" the program.	[reply]

Replies are listed 'Best First'.

Re^5: Perl DBI execute statement
by Marshall (Canon) on Dec 14, 2016 at 19:49 UTC

Method 1: You could try to UPDATE the record and then if that doesn't update the record ("0E0"), then INSERT that record, of course checking that the SQL INSERT succeeded with a "1" value (one row inserted). This is two SQL statements, although just 1 if the record already exists (UPDATE updates the existing row).

Method 2: You could also do a simple SELECT on the primary key and see if it is there or not? (just check number of rows returned). If the primary key exists, then UPDATE, else INSERT. I suspect that this is better for your debugging... print the results of the select, update and insert execute values in your debugging code. This is 2 actually executed SQL statements.

I think Method 2 will work well for you. Set things up so that the INSERT will never fail due to a "duplicate primary key" error. Yes, it is possible to intercept the "die" from a failed INSERT and do something else, but I think that is ~~too complicated to mess with~~ not appropriate in this situation.

Your final code should not have any IGNORE's in the SQL. You have to set things up so that this either never happens (unless real catastrophic error) or you explicitly deal with this exception. IGNORE shouldn't be one of the options. It is a very bad idea to IGNORE something that would normally "crash" the program.

[reply]