Anomoly when storing empty values into SQLite integer fields

[Mark Smith]

Peter Haworth-2 wrote
> 
> put 1 into x
> put "UPDATE test SET " into mySQL
> repeat for each item myField in "One,Two,Three"
>    if field myField is not empty then
>       put myField & "=:" & x & comma after mySQL
>       put field myField into myArray[x]
>       add 1 to x
>    end if
> end repeat
> put space into char -1 of mySQL
> put "where ID=" & tID after mySQL
> revExecuteSQL gConnectID, mySQL, myArray
> 

Hi Pete, as suggested I had some fun with this code and in the process may
have uncovered a few more LC problems in this area (I would interpret that
cautiously at the moment as some of it may just be operator error and/or
superstitious behavior on my part). Here's a summary of what I found:

1. I don't think you can pass field names, I think they have to be variables
2. Every attempt to pass an array failed: I can display the array contents
and they show the variables names I am intending to pass. If I quote the
array it puts the variables names into each of the fields (so we know
something works but we don't want the variable names, we want the values).
If I quote the individual variable names inside the array, I get empty
results (even though a check of the variable watcher shows the correct
values in each variable... btw the names were just tvar1....tvarN). If I
don't quote any of it I also get empty values. 
3. I can print off the list of variable names from inside the array I am
constructing and cut and paste that after revExecuteSQL gConnectID, tCmd and
everything works fine. I can pass the list of variables from the same array
source as a single parameter and it fails. Odd stuff. 
4. Since the last part might have been confusing here is another attempt at
an explanation: If I construct the statement as:
revExecuteSQL gConnectID, tCmd, tParameters   where tParameters = "tvar1",
"tvar2", "tvar3", etc
it fails. If instead I put the tParameters string in the msg box and then
cut and past it after:
revExecuteSQL gConnectID, tCmd   it works fine. 

I suspect somewhere in there LC is expecting quoted values but is just
failing to handle the quotes or commas properly. 

However all of this was not for nothing...  using the above concepts and a
single cut and paste, I am able to store all of the field values from a form
(any form actually) to an SQLite table without writing any additional code.
That will be a tremendous time saver as the application progresses, never
mind the hours saved in trying to debug hand written code.

Cheers,

-- Mark

The final working draft looks something like this.... keep in mind I am
storing into text fields so I am just using this to automate what would
otherwise be a tedious process of hand coding.

on mouseup
   set itemdelimiter to tab
   put item 1 of gCurrentMom into tEventID
   put "UPDATE prenatal SET " into tCmd
   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 fieldname
         put fieldname & "=:" & x & comma after tCmd
         do "put fld fieldname into tvar" & x  -- copies the value from the
field to a temp var
         put quote & "tvar" & x & quote into myArray[x] -- puts the temp var
name into an array to pass to revExecuteSQL -- not working
         -- ps also tried without the quotes, and quoting the arrayname but
neither of those worked either  -- could be another problem?
         add 1 to x
      end if
   end repeat
   put space into char -1 of tCmd
   put "where EventID=" & tEventID after tCmd
   -- now, build a paramters string of tvar names to pass to revExecute SQL
-- instead of the array
   repeat with z = 1 to the number of elements of myArray 
      put myArray[z] & ", " after tParameters
   end repeat
   put space into char -2 of tParameters
   put tCmd  -- debug
   put return & tParameters after msg  -- debug -- passing a list of
parameters did not work so grab this line and paste it below
   
   revExecuteSQL gConnectID, tCmd, "tvar1", "tvar2", "tvar3", "tvar4",
"tvar5", "tvar6", "tvar7", "tvar8", "tvar9", \
         "tvar10", "tvar11", "tvar12", "tvar13", "tvar14", "tvar15",
"tvar16", "tvar17", "tvar18", "tvar19"
   
   put return & the result after msg -- debug
   put return & the number of elements in myArray after msg -- debug
   go card "forms"
end mouseup



--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4414775.html
Sent from the Revolution - User mailing list archive at Nabble.com.