I’m having trouble getting sqlite3 to update a table with info gathered from my app interface.
When inserting items my handlers work perfectly, yet when trying to update those entries it doesn’t do anything !?
Here’s the code I’m using for the update part:
set ReSADBLocation to GetReSAHomeFolder()
set ReSADBLocation to quoted form of (POSIX path of (ReSADBLocation & "Setup Files:JobLog.db")) & space
set theSQLstring to "sqlite3 " & ReSADBLocation & "\"update RESA_JOB_LOG set SessionName =" & NameSessionUpdated & " AND Description =" & thedescriptionUpdated & " AND Date =" & thedateUpdated & " AND Operator =" & OperatorUpdated & " AND Facility =" & FacilityUpdated & " AND Suite =" & SuiteUpdated & " AND Function =" & FunctionUpdated & " AND TimeIN =" & theTimeINUpdated & " AND TimeOUT =" & theTimeOUTUpdated & " AND DailyHRS =" & DailyHRSUpdated & " AND OverTimeHRS =" & OverTimeHRSUpdated & " AND PublicHolidayHRS =" & PublicHolidayHRSUpdated & " AND OtherHRS =" & OtherHRSUpdated & " AND Notes =" & theNotesUpdated & " AND Path =" & thePathUpdated & " AND RateCode =" & RateTypeUpdated & " AND DateCreated =" & DateCreatedUpdated & " AND DateModified =" & DateModifiedUpdated & " AND RequiresAction =" & RequiresActionUpdated & " where SessionName =" & NameSession & " AND Description =" & thedescription & " AND Date =" & thedate & " AND Operator =" & Operator & " AND Facility =" & Facility & " AND Suite =" & Suite & " AND Function =" & Function & " AND TimeIN =" & theTimeIN & " AND TimeOUT =" & theTimeOUT & " AND DailyHRS =" & DailyHRS & " AND OverTimeHRS =" & OverTimeHRS & " AND PublicHolidayHRS =" & PublicHolidayHRS & " AND OtherHRS =" & OtherHRS & " AND Notes =" & theNotes & " AND Path =" & thePath & " AND RateCode =" & RateType & " AND DateCreated =" & DateCreated & " AND DateModified =" & DateModified & " AND RequiresAction =" & RequiresAction & " ;\"" as Unicode text
set theFile to open for access ((path to desktop as string)) & "tester" with write permission
set eof of theFile to 0
write theSQLstring to theFile
close access theFile
try
do shell script theSQLstring
on error errMsg
beep 2
display dialog errMsg
end try
The output from my test file reads correct, yet sqlite does not update. What am I missing here?
For this type of thing, it is usually some small little typo that messes things up. The best way to test any do shell script is to get the variable text into your Script Editor Results window, copy it, and paste it into Terminal and see what happens. If it still does not work, you then know that there is some kind of error in the creation of your variable.
As for the UPDATE command, depending on what you are doing here, it may simpler to use REPLACE:
REPLACE into clean (clean_id, clean_Path) VALUES (2,'~/Library/Mail Downloads/');
I’ve tried the replace command but it keeps throwing errors. Not sure why. Also when trying the text command in terminal I get the same failure. So suppose i will have to try to update one column at a time until I find the one with the variable problem.
I think the problem is that your SQL syntax is wrong. The syntax for an update statement requires a comma, not the “AND” that you’re using, between successive field = value assignments.
