Anomoly when storing empty values into SQLite integer fields

[Bob Sneidar]

Allow me to expand on the subject a bit with a couple methods I use when working with databases. First method is this. When I am working with a record from a table, I store all the column values in an array whose keys are the column names. If I am inserting a new record, I manually create the array record first, by getting the schema of the table, which when parsed will give me the unique column that identifies the record, and all the default values assigned to each column. I populate the array keys either with the default values as in the case of an insert, or the values from the query if updating. 

The other method you can employ is to populate an array first with only a key for the unique column, set to empty for an insert and the unique value from the table for an insert. As the user modifies values, only add keys for the columns whose values the user alters. 

In each case you can accomplish this by having a closeField handler in the script of the card that gets the selectedObject and then updates the array value based upon the new contents of the field. For my part, I like to name my fields "fld" & the column name. Buttons "btn" & the column name. Menus "mnu" & the column name. For buttons trap mouseUp (check boxes and radio buttons). For menus trap menuPick. Handle each accordingly. 

That is the basics, although I get more complex than that in that each card has a formFields property, a formButtons property and a formMenus property. I check the object name against these to see if the objects represents a column in my table and proceed accordingly. 

This technique has the added advantage that you can do some field validation at this point to make sure the user is entering the proper information. When ready, insert or update the values in the array. There is no chance you will get an error as the values come either from the table schema or the table itself. 

Bob

> Forgive me if I misunderstand, but I was under the impression that empty and NULL are two different things. I just updated a column in a mySQL table from LC with the value NULL (not enclosed in quotes) and when I checked the table, the value was NULL, not an empty string, as I would expect. If I had passed it an empty string I would expect it to be an empty string, not the NULL value. I am not sure if sqLite works the same way, but I cannot conceive of how it would not. 
> 
> Bob
> 
> 
> On Feb 22, 2012, at 9:43 AM, Pete wrote:
> 
>> No problem Mark.  I've come across other anomalies in the way LC handles
>> databases over the last few months.
>> 
>> The bug report numbers are 10022 for the empty/zero problem and 10023 for
>> the incorrect handling of the reserved keyword NULL.
>> 
>> Pete
>> 
>> On Wed, Feb 22, 2012 at 7:07 AM, Mark Smith <Mark_Smith at cpe.umanitoba.ca>wrote:
>> 
>>> Peter, very cool. I'm going to have fun with that one. LC is very powerful
>>> at
>>> times. Oh, and thanks for posting to the bug list. While it is possible
>>> that
>>> RR will say this behavior conforms to a particular standard and you just
>>> have to code for it (as you have below) its still way, way to much work for
>>> a supposed high level rapid application development environment. My 2 cents
>>> anyway.
>>> 
>>> Cheers,
>>> 
>>> -- Mark
>>> 
>>> 
>>> Peter Haworth-2 wrote
>>>> 
>>>> Hi Mark,
>>>> I think this may not be difficult.  Turns out you can use an array with
>>>> numbered keys instead of a list of variables with revExecuteSQL, so
>>> here's
>>>> some code ( completely untested)
>>>> 
>>>> 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
>>>> 
>>>> Shouldn't be too hard to generalise it as a command/function if
>>> necessary.