Code for Updating database upon Closing Card

[montymay]

Hello:

Searched the forum and couldn't find this problem.

Users in my application will be entering text into empty fields or modifying text in other fields in one card. Before entry or modification, the text of fields containing the text will have been downloaded from a sqlite database when the card was opened. I need a script that identifies the fields that the user changed so that the database field or fields can be updated upon closing the card. I know that the textchanged handler can identify a change in one field, but I do not know how the name of every changed field can be put into memory for inclusion in the update.

For all I know the script may be very complex; if so, I would appreciate just a description of a method or links to explanations. Thanks for your replies.

Monty

[jmburnod]

Hi Monty

I do not know how the name of every changed field can be put into memory for inclusion in the update.

Many ways to do that.
Why not using a customprop uMyText for each fld on opencard
and check if the uMyText of each fld = the text of each fld in a loop on closecard ?
Best regards
Jean-Marc

[Mikey]

why not just update every field in the table with the values in the fields? Name the fields the same as the fields in the table, which also makes populating them easier, iterate the number of fields on the card, get the name of the field, build the SQL, use parameters to make sure you don't suffer an SQL injection, execute it.

[montymay]

Thanks Jean-Marc and Mikey.

Going with Jean-Marc's suggestion, I wrote the following script:

Code: Select all

global gDatabaseID
on closecard
  put fld "pcfnum" into tPCFnum --the identifier of the correct row
  repeat with i=1 to 13  --the number of fields the user may have modified
    put the short name of fld i into tName
    put the text of fld i into tTextNew
    put the cText of fld i into tTextOld
    if tTextNew <> tTextOld then
      put "UPDATE OpsDetails SET tName='"&tTextNew&"' WHERE pcfnum='"&tPCFnum&"'" into tSQL
      revExecuteSQL gDatabaseID, tSQL, "tName"
    end if
  end repeat
end closecard

But it doesn't update the database. I am a beginner at SQL in LC. I'm thinking it's some syntactical mistake in lines 8 and/or 9.

Monty

[AxWald]

Hi.

Code: Select all

put "UPDATE OpsDetails SET tName='"&tTextNew&"' WHERE pcfnum='"&tPCFnum&"'" into tSQL
      revExecuteSQL gDatabaseID, tSQL, "tName"

Assume it's the 3rd param in revExecuteSQL:

Code: Select all

revExecuteSQL databaseID,SQLStatement [,{variablesList | arrayName}]

You don't have no varList nor array here, as far as I see. And is the field name in the database really "tName"?

Btw., I do such in a slightly other way:

1. I grab my "record to be edited" via SELECT query & "revdb_querylist(FldDel,RecDel,MyDBID,StrSQL)".
2. This is then a.) stored in a custom property and b.) displayed in the fields.
3. The fields then can be edited, and have a suitable validity check each.
4. At "saving time" I just combine the fields to a matching record,
5. compare this to the stored one and update if required.

And I always check my results (if revdb_execute(MyDBID,StrSQL) is not 1 then ...) as well as the errorDialog message/ revdb_connectionerr function for such things - external databases may have lost connection, may be busy, the record may be locked by another user ...

To keep this simple, I usually have both separate "GetSQL" (for select queries) & "PutSQL" (for action queries) handlers, where all the error handling is concentrated ;-)

Finally, I don't trust long open connections anymore. Opening and closing a connection is this fast these days, I do it for each call to the DB now.

Hope I cold help!

[MaxV]

Instead of trying to see which fields changed and which not, send an UPDATE with all fields to the database.
Databases are really super fast, even a 100 fields sqlite update is faster than a 100 fields livecode check.

[Mikey]

well, not in the database, but in the table, and exactly what I was proposing, earlier. Plus the code is simpler/easier/etc.