Anomoly when storing empty values into SQLite integer fields

[Pete]

Bob,
Check back in my earlier emails on this thread.  As you experienced,
specifying NULL with no quotes results in the literal string "NULL" going
into the column which is totally wrong.  NULL means "no value", not empty
nor the literal value "NULL".  Just to confuse things even more, when you
read that value back with a SELECT, LC does treat "NULL" as meaning no
value instead of the literal value "NULL".  That's the gist of the second
bug report I entered at QCC.  LC is really messed up on this.

I've come across so many niggling issues with LC's database handling that
I'm seriously considering using the C externals library to access SQLite
instead of the revDB commands.  Not only does it work right, but it also
gives access to functionality that LC doesn't provide, eg a callback at
regular intervals during the execution of a long running SELECT statement,
allowing you to update a progress indicator, or even cancel the operation.

Pete

On Wed, Feb 22, 2012 at 10:28 AM, Bob Sneidar <bobs at twft.com> wrote:

> 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.
> >>>
> >>>
> >>
> >>
> >> --
> >> View this message in context:
> >>
> http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4410631.html
> >> Sent from the Revolution - User mailing list archive at Nabble.com.
> >>
> >> _______________________________________________
> >> use-livecode mailing list
> >> use-livecode at lists.runrev.com
> >> Please visit this url to subscribe, unsubscribe and manage your
> >> subscription preferences:
> >> http://lists.runrev.com/mailman/listinfo/use-livecode
> >>
> >>
> >
> >
> > --
> > Pete
> > Molly's Revenge <http://www.mollysrevenge.com>
> > _______________________________________________
> > use-livecode mailing list
> > use-livecode at lists.runrev.com
> > Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>


-- 
Pete
Molly's Revenge <http://www.mollysrevenge.com>