INSERT INTO vs UPDATE SET

[marksmithhfx]

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

[SparkOut]

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.

[marksmithhfx]

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

[marksmithhfx]

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:

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

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