Page 1 of 1
Code for Updating database upon Closing Card
Posted: Sat Sep 24, 2016 10:03 am
by 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
Re: Code for Updating database upon Closing Card
Posted: Sat Sep 24, 2016 10:32 am
by 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
Re: Code for Updating database upon Closing Card
Posted: Sat Sep 24, 2016 2:08 pm
by 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.
Re: Code for Updating database upon Closing Card
Posted: Sat Sep 24, 2016 11:46 pm
by 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
Re: Code for Updating database upon Closing Card
Posted: Mon Sep 26, 2016 12:22 pm
by 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:
- I grab my "record to be edited" via SELECT query & "revdb_querylist(FldDel,RecDel,MyDBID,StrSQL)".
- This is then a.) stored in a custom property and b.) displayed in the fields.
- The fields then can be edited, and have a suitable validity check each.
- At "saving time" I just combine the fields to a matching record,
- 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!
Re: Code for Updating database upon Closing Card
Posted: Tue Oct 04, 2016 1:32 pm
by 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.
Re: Code for Updating database upon Closing Card
Posted: Tue Oct 04, 2016 1:33 pm
by Mikey
well, not in the database, but in the table, and exactly what I was proposing, earlier. Plus the code is simpler/easier/etc.