LiveCode Forums.

Hello
I'm just learning databases with Livecode. I'm sure there's a simple answer to this somewhere.
I've got a simple database just trying to update records with a simple statement. The query ends up reading like this

UPDATE photo SET favorite = "0" WHERE refnum = "123462"

This works just fine in DB Browser but give the following error when running from Livecode only if there are more than one records that equal the search in the WHERE statement.

execution error at line 333 (Database INSERT failed: 7)

Here's the code -
-------------------------------------------------------------
put "UPDATE photo " into tSql
put "SET favorite = " & quote & "0" & quote after tSql
put " WHERE refnum = " & quote & gCurrentRefNum & quote after tSql
revExecuteSQL gDatabaseConnection, tSql

if the result is not 1 then
throw "Database INSERT failed:" && the result
end if
--------------------------------------------------------------------------------

The statement runs and works if there is only one record in the database matching the WHERE variable in Livecode and DB Browser.
Doesn't work in Livecode if there are multiple records that match, but does run fine in DB Browser.
Do I need to run a repeat loop to update all the matching records in the database one at a time?
Is there a different way to form the query in Livecode that will update all matching records in the database at the same time?

Thanks!
Jon

Hi,
your script just checks if the result is not 1. But this is the case if more than one records are affected.

The dictionary entry for revexecuteSQL says:

The revExecuteSQL command places a return value into the result, to indicate the outcome of the query. For successful queries, the revExecuteSQL command returns the number of rows affected for INSERT, UPDATE and DELETE statements. For all other statements, 0 is returned.
For unsuccessful queries, an error string is returned, describing the problem.

Thank You! I didn't actually know what that part of the code I copied did for sure. Super simple, but I was stuck.

While you are tidying, you might also check the wording of the error message thrown, and adjust it to reflect the type of query (it could be misleading to have the error saying the INSERT failed when the query is to UPDATE).
But that's not making the code work or fail.