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.
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
