Ok, here's a question for the SQL guru's in the crowd (my knowledge is only cursory... oh gawd, did I just make an SQL joke? Never mind!)
I have some perfectly good UPDATE code that looks like this
UPDATE file SET "var1"=:1, "var2"=:2, "var3"=:3 etc where SOME_ID = X, "array-of-values"
and, using revExecuteSQL, LC tosses the values into the appropriate columns in the database. That works fine.
I have some perfectly good INSERT code that looks like this
INSERT INTO file ("var1","var2","var3" etc) values(:1,:2,:3 etc), "array-of-values"
and again LC tosses the right array values into the right database columns.
But I am bothered by the lack of symmetry in these two SQL statements. I have tried to make INSERT INTO look like UPDATE SET but revExecuteSQL complains (and I am not sure why).
What I did was simply write
INSERT INTO file "var1"=:1, "var2"=:2, "var3"=:3 etc, "array-of-values"
So I have two questions. 1. Is it possible for these two statements to share common formatting? 2. And if not, is there a reason why not? Just so you know there is a reason behind wanting to find as much commonality as possible; I am writing code to write this code. Ie. I want it to be machine generated. The more "in common" it is, the fewer "exceptions" I have to code for. If SQL is just "arbitrarily different" in these two instances, I can appreciate that (par for the course with most computer languages). I just want to be sure I am not missing something obvious.
Thanks for any insights you may have into this.
Mark
INSERT INTO vs UPDATE SET
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- VIP Livecode Opensource Backer
- Posts: 931
- Joined: Thu Nov 13, 2008 6:48 am
- Location: London, UK
INSERT INTO vs UPDATE SET
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS
Targets: Mac, iOS
Re: INSERT INTO vs UPDATE SET
1. No (AFAIK)
2. That's how SQL was developed. (Nothing to do with LiveCode) I think it's just that 'arbitrary difference' you found.
2. That's how SQL was developed. (Nothing to do with LiveCode) I think it's just that 'arbitrary difference' you found.
-
- VIP Livecode Opensource Backer
- Posts: 931
- Joined: Thu Nov 13, 2008 6:48 am
- Location: London, UK
Re: INSERT INTO vs UPDATE SET
SparkOut wrote:1. No (AFAIK)
2. That's how SQL was developed. (Nothing to do with LiveCode) I think it's just that 'arbitrary difference' you found.
Ok, thanks. I figured as much. I have 95% of the "second set" of code written anyway... just wanted to be sure.
Cheers,
Mark
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS
Targets: Mac, iOS
-
- VIP Livecode Opensource Backer
- Posts: 931
- Joined: Thu Nov 13, 2008 6:48 am
- Location: London, UK
Re: INSERT INTO vs UPDATE SET
So, given a LC form with a bunch of fields on it, the following LC routine will generate the following SQL code
INSERT INTO master ("delivery_date", "subject number", "mothers_name", "mothers_dob", "mothers_age", "mothers_pc", "mothers_ed", "mothers_occ", "fathers_age", "marital_status", "Site", "care_provider1", "adolescent setting", "cfs involved", "cfs reason", "apprehension order", "contraception", "admit_date", "admit_time", "mothers_lang", "note" ) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21 )
and put the appropriate form values in an array called "myArray"
The code is:
It remains to add the following line of LC to actually store the values
revExecuteSQL gConnectID, tCmd, "myArray"
It's fairly trivial to generalize this to UPDATE file SET and also to buttons as well as fields. The advantage, if I can use that term, is that once written you can use this for any LC form (in a button or on the card) to "scrape" the data into an SQL file WITHOUT having to make reference to either the number of fields or their names - it is completely generalized. I have corresponding routines (written very differently) that copy the data back from the database to display the values. If anyone is interested let me know. I may put it all together in one place as much for my own reference as anything.
Mark
INSERT INTO master ("delivery_date", "subject number", "mothers_name", "mothers_dob", "mothers_age", "mothers_pc", "mothers_ed", "mothers_occ", "fathers_age", "marital_status", "Site", "care_provider1", "adolescent setting", "cfs involved", "cfs reason", "apprehension order", "contraception", "admit_date", "admit_time", "mothers_lang", "note" ) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21 )
and put the appropriate form values in an array called "myArray"
The code is:
Code: Select all
put "INSERT INTO master (" into tCmd -- this is the code specifically for INSERT INTO
put 1 into x
repeat with y = 1 to the number of fields of this card
if the short name of field y <> "Label Field" and toupper(the cStoreMe of field y) <> "N" then
put the short name of field y into tfieldname
put quote & tfieldname & quote & comma & space after tCmd -- build the SQL statement, fieldname by fieldname
put the text of field y into myArray[x] -- put the field VALUE into myarray
end if
add 1 to x
end if
end repeat
put space into char -2 of tCmd -- remove last comma
put ") values (" after tCmd
put 1 into x
repeat with y = 1 to the number of fields of this card -- do this to build up the "values" portion of INSERT INTO
if the short name of field y <> "Label Field" and toupper(the cStoreMe of field y) <> "N" then
put ":" & x & comma & space after tCmd --
add 1 to x
end if
end repeat
put space into char -2 of tCmd -- remove last comma
put ")" after tCmd -- closing brace
revExecuteSQL gConnectID, tCmd, "myArray"
It's fairly trivial to generalize this to UPDATE file SET and also to buttons as well as fields. The advantage, if I can use that term, is that once written you can use this for any LC form (in a button or on the card) to "scrape" the data into an SQL file WITHOUT having to make reference to either the number of fields or their names - it is completely generalized. I have corresponding routines (written very differently) that copy the data back from the database to display the values. If anyone is interested let me know. I may put it all together in one place as much for my own reference as anything.
Mark
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS
Targets: Mac, iOS