Anomoly when storing empty values into SQLite integer fields

[Mark Smith]

Hi, I've encountered a bit of an anomaly when storing empty values into
integer fields in SQLite, and wonder if others have observed the same
behavior. If I store an empty value from a LC field into an integer field in
a database, and then subsequently read it back LC displays it as a 0. Where
this translation occurs is not 100% clear to me, but it certainly is not
desirable (0 and undefined are not the same thing). Has anyone else
encountered this?

Here's an example:

put fld "One" into tOne -- One is an empty field, so tOne is empty too
put "UPDATE test SET one=:1 where ID=" & tID into tCmd
revExecuteSQL gConnectID, tCmd, "tOne"

If the field called one in the database is defined as type int and fld "one"
on the form is empty then when I execute this:

put "Select * FROM test where ID = " & tID into tQuery
put revDataFromQuery(,,gConnectID,tQuery) into tRecord
-- display the record
put item 1 of tRecord into fld"ID"
put item 2 of tRecord into fld"one"

I get a 0 (zero) for the value in fld "one".

The only way I can see to get around this is to store all numeric values in
text fields because when an "empty" text field is read back its value
remains the same (ie it remains empty). I think LC is doing the translation
on integer type fields but….. I'm not 100% sure of that.

Your thoughts? (I'm using 5.0.2 for these tests)

-- Mark

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

_______________________________________________
use-livecode mailing list
use-li...@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

[Bob Sneidar]

What is the field type? What is the default? If numeric and not null then it may be that the value defaults to 0

Bob Sneidar
IT Manager
Calvary Chapel CM
Sent from iPhone

[Pete]

Hi Mark,
I tested this out after emailing you earlier today. It's definitely
Livecode that is doing this and it's a serious problem for more reasons
than you've found.

Long story short, the only way I could find to make this work correctly is
to not include the integer column in the UPDATE statement. Not sure if
your update was just a test with that one column or if there;d normally be
more columns to update, but you'd have to test your integer fields for
empty and leave them off the UPDATE statement if they are.

For anyone interested, here's what I tried.

Using LC, I Inserted a record into a table with empty in an integer field.
I used sqlite3 (SQLite's command line db admin tool) to select and read it
back and it showed no value in the integer field. I then displayed the
entry using LC and it showed zero in the integer field, so something
somewhere in LC is converting empty to zero for integer columns. That is
not good.

I also tried supplying a value of NULL for the integer field. When I did
this in slqite3, then read the record back, it showed empty in both sqlite3
and Livecode so I thought I had found a way round it. But then I added a
record using LC and specifying NULL as the value for the integer field.
When I displayed that record in sqlite3, it showed up with a literal value
of "NULL", not empty and it still showed up as zero in LC. So LC is
treating NULL as a literal value insetad of a keyword on the way in to the
database, but converting that literal NULL value to zero on the way back
out. That is also not good

I think this is a bug. There's no way LC should do any data
transformations from how it is stored in a database, and it should
recognise all SQL keywords including NULL.

Pete

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

[Mark Smith]

slylabs13 wrote

>
> What is the field type? What is the default? If numeric and not null then
> it may be that the value defaults to 0
>

Thanks Bob, field type is integer, no default is defined. Firefox plugin has
a zero in the Not Null column,(which means?) and a zero in the Primary Key
column (which I presume means the field is not a primary key).

Just to add to my previous comments, I thought I would check to see if it
had anything to do with the substitution form of revExecuteSQL. It did not.
Switching back to

put "UPDATE test SET " & \
merge("one='[[tOne]]', two='[[tTwo]]', three='[[tThree]]'") & \
" WHERE id = " & tID into tCmd -- old format

had the same result. One advantage though was that if I put a "Put tCmd" to
throw the statement to the msg box I could actually see what the values
were. For the first save of the empty fields (UPDATE statement above) it
looks like this:

UPDATE test SET one='', two='', three='' WHERE id = 10

field types are integer, integer and text respectively

After reading it back in, it displays 0's in the int fields. If I then try
saving it again (running the same code as above) I get this:

UPDATE test SET one='0', two='0', three='' WHERE id = 10

and as you can see the int fields now contain 0's while the text field does
not. No data entry has occurred on my part, I am just executing a repeated
read and write on the same record using the same code. Bob may be on to
something though.... maybe I have to define a default... but what should
that be (I want undefined to remain undefined)?

-- Mark

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4409072.html

[Mark Smith]

Peter Haworth-2 wrote

>
> Long story short, the only way I could find to make this work correctly is
> to not include the integer column in the UPDATE statement. Not sure if
> your update was just a test with that one column or if there;d normally be
> more columns to update, but you'd have to test your integer fields for
> empty and leave them off the UPDATE statement if they are.

> --
> Pete
> Molly's Revenge &lt;http://www.mollysrevenge.com&gt;
>

Thanks Pete, nice to have confirmation, although its not so much fun to
contemplate the alternatives :)

I guess there are 2 really: in the first case, since I am only going to be
using this app with SQLite and SQLite has pretty relaxed type affinity I
could store everything in text fields. LC seems to be leaving those alone
when they are undefined. However, thats not a good solution if you are
writing an app that needs to work with other SQL databases. In that case
your suggestion of including or excluding the columns based on whether they
are defined or not defined seems the better solution. However, my own LC
skills are not at the level where I would know how to make that work
(although I am sure it can be done).

For example, if one were to use the substitution form of revExecuteSQL how
would you write it so that if one of the fields was undefined it would
disappear from the statements:

put "UPDATE test SET one=:1, two=:2, three=:3 where ID=" & tID into tCmd
revExecuteSQL gConnectID, tCmd, "tOne", "tTwo", "tThree"

I guess this might be easier to do in the merge() form ??? (since you are
only dealing with 1 string):

put "UPDATE test SET " & \
merge("one='[[tOne]]', two='[[tTwo]]', three='[[tThree]]'") & \
" WHERE id = " & tID into tCmd -- old format

But then you have to worry about escaping characters in text fields... etc

-- Mark

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4409110.html

[Bob Sneidar]

Right. So in a column defined as integer not null, there must then be a default value when you pass empty otherwise there would be an inconsistency. What I do ( and how I do it is too much to go into on an iPhone) is I preload all the values I intend to insert/update with default values, then get input from the user. If the user does not alter the defaults, then the SQL table gets the ones I defined. The best way however is to define your table columns with some default. I have learned to do this at pains to The contrary.

Bob Sneidar
IT Manager
Calvary Chapel CM
Sent from iPhone

[Pete]

Bob,
The field type is integer as Mark said. Since he doesn't have a default
specified, the default value is NULL and that should not come back as zero
and doesn't in any tool except LC - tried it with sqlite3 and the Firefox
plugin. Nor should an empty value he's supplying (which is entirely
different than NULL to SQL) come back as zero and, once again, it doesn't
in any tool except LC.

The only way round this that I've found as in my earlier email is to not
include the integer column in any INSERT or UPDATE statements if you want
it to come back as empty instead of zero.

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

[Bob Sneidar]

Is it important to discern between null and a value? If so, uncheck the not null in the table definition and don't include the column in inserts/updates

Bob Sneidar
IT Manager
Calvary Chapel CM
Sent from iPhone

[Pete]

Yes, it causes a headache, you're right. You would have to build the
UPDATE command in a variable by examining each field for empty and adding
it to the variable list if not empty. Don't have time to give some example
code right now, but will try to do something tomorrow.
Pete

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

[Pete]

I absolutely agree that default values should be defined in the database
but it wouldn't help in this case since he wants the default to be either
NULL or empty and LC has taken it upon itself to decide it knows better
than the database and interpret that as zero.
Pete

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

[Mark Smith]

Peter Haworth-2 wrote

>
> Yes, it causes a headache, you're right. You would have to build the
> UPDATE command in a variable by examining each field for empty and adding
> it to the variable list if not empty. Don't have time to give some
> example
> code right now, but will try to do something tomorrow.
> Pete
>

Thats ok, thats enough of a hint... I can work something up from there. If
fld"one" is not empty then
put "some string" after "the string I'm trying to build"
should do it.

Thanks

-- M

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4409145.html

[Bob Sneidar]

Ah ic. I think that in a situation where you are passing an empty string to a numerical field with no default, either SQL must throw an error, or else insert what amounts to nothing in a numeric column.

I think the nature of sqLite is that Instead of tossing an error it puts 0 in the column. You are right that not including columns you do not intend to update is the way to go. What I do is I have a function called buildRecord which gets the defaults of the table and populates an array accordingly. I then get user input passing that thru validation functions before inserts/updates. Then i update all columns in the table. By this method I avoid any unexpected results/errors in my SQL queries. I suppose that checking for null in values can be used as a method of detecting an uninitialized value, but there are other ways to go about that IMHO.

Bob Sneidar
IT Manager
Calvary Chapel CM
Sent from iPhone

[Mark Smith]

slylabs13 wrote

>
> Is it important to discern between null and a value? If so, uncheck the
> not null in the table definition and don't include the column in
> inserts/updates
>

Awkward but yes, that seems to be the solution.

-- mark

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4409162.html

[Mark Smith]

Peter Haworth-2 wrote

>
> The only way round this that I've found as in my earlier email is to not
> include the integer column in any INSERT or UPDATE statements if you want
> it to come back as empty instead of zero.
>

Which sort of works, until you consider the case of wanting to back out a
value (revert from some value to NULL). At present, I think I'll just stick
with using text fields, but we should definitely report this to RR. If the
user wants to put an undefined value into an integer field (because the
value is in fact UNDEFINED... that does happen in the real world) then the
user should be able to use empty or NULL to signal that (I have no
preference... to my failing eyesight they both look equally missing to me :)

-- Mark

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4409186.html

[Pete]

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.

But then the easiest thing might be to just use text instead.

I plan to enter this as bug tomorrow, as well as the incorrect handling of
the NULL keyword.

Pete

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

[Mark Smith]

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

[Bob Sneidar]

I think you CAN use NULL as a value to pass, but the NOT NULL of the column has to be unchecked.

Bob

[Robert Brenstein]

On 21.02.2012 at 19:46 Uhr -0800 Mark Smith apparently wrote:
>Which sort of works, until you consider the case of wanting to back out a
>value (revert from some value to NULL). At present, I think I'll just stick
>with using text fields, but we should definitely report this to RR. If the
>user wants to put an undefined value into an integer field (because the
>value is in fact UNDEFINED... that does happen in the real world) then the
>user should be able to use empty or NULL to signal that (I have no
>preference... to my failing eyesight they both look equally missing to me :)
>
>-- Mark

Have you tested that you get the same behavior with not-null setting
for that integer field on and off? You should get different results.

Robert

[Pete]

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

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

[Bob Sneidar]

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

[Bob Sneidar]

It may be a small distinction, but NULL is ASCII 0. An empty string is nothing at all, so far as I know. To a human this is splitting hairs, but to a computer all hairs must be split.

Bob

[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

[Bob Sneidar]

Ok. But if it really were the string value "NULL" that gets saved to the database, wouldn't you get "NULL" in your select statement?? Try using a lowercase null in your update statement, then view the sqLite table with a utility to see what it says the value is. If it's capital NULL it is actually the NULL character.

LC is not going to reinterpret a string value as empty just because the word "NULL" was what the value was. This seems to indicate that the update command actually DID save the NULL properly. As for a select statement returning empty string for a NULL value, this was discussed some time ago when I was first getting into database access from LC. The idea is that LC never wanted to return an ASCII 0 character in ANY string because it tended to wreak havoc with displaying text in fields and other objects. I believe that an empty string is precicely what the RunRev people WANT to return for a NULL value.

Sorry, I don't mean to sound argumentative, but it "seems" to me that it is doing what it was at least designed to do.

Bob

On Feb 22, 2012, at 12:54 PM, Pete wrote:

> 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".

[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:
>

_______________________________________________

[Pete]

Bob,
Sorry but you're wrong on all counts. Read my earlier mails for info In
particular, LC is not retuning empty for a NULL value in an integer column
- it's returning zero, that's where this whole mess started!!! I am quite
happy for it to return empty for a NULL value but that's not what is
happening, at least for integer fields.

On Wed, Feb 22, 2012 at 1:27 PM, Bob Sneidar <bo...@twft.com> wrote:

> Ok. But if it really were the string value "NULL" that gets saved to the
> database, wouldn't you get "NULL" in your select statement?? Try using a
> lowercase null in your update statement, then view the sqLite table with a
> utility to see what it says the value is. If it's capital NULL it is
> actually the NULL character.
>
> LC is not going to reinterpret a string value as empty just because the
> word "NULL" was what the value was. This seems to indicate that the update
> command actually DID save the NULL properly. As for a select statement
> returning empty string for a NULL value, this was discussed some time ago
> when I was first getting into database access from LC. The idea is that LC
> never wanted to return an ASCII 0 character in ANY string because it tended
> to wreak havoc with displaying text in fields and other objects. I believe
> that an empty string is precicely what the RunRev people WANT to return for
> a NULL value.
>
> Sorry, I don't mean to sound argumentative, but it "seems" to me that it
> is doing what it was at least designed to do.
>

--

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

[Bob Sneidar]

I just tested this with a numeric column in mySQL. The column has NOT NULL unchecked and the default value set to NULL. When I updated from LC setting the column to NULL that is the value that the column was set to. HOWEVER if I updated the column with an empty string, I got 0 just as you say. We good so far? Okay.

So I wanted to see what would happen if I bypassed LC altogether and did the same updates in a mySQL utility. I got THE EXACT SAME RESULTS!!! When I updated a numeric column with an empty string, I got the number 0. What does this all mean? It means that LC is NOT borking the update. It is just the way SQL reacts when you send an empty string to a numeric field!

They only real issue left is that LC converts a NULL value to an empty string in it's queries for the reason I explained, that LC abhors an ASCII 0 in a string. I hope I have made this clear now. I have screen snapshots to verify my results if you would like me to send them to you off list.

Bob

[Mark Smith]

Robert Brenstein wrote

>
>
> Have you tested that you get the same behavior with not-null setting
> for that integer field on and off? You should get different results.
>

HI Robert, while I was hoping it would unfortunately no, it didn't make a
difference.
-- Mark

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4412904.html

Sent from the Revolution - User mailing list archive at Nabble.com.

_______________________________________________

[Mark Smith]

slylabs13 wrote

>
> It may be a small distinction, but NULL is ASCII 0. An empty string is
> nothing at all, so far as I know. To a human this is splitting hairs, but
> to a computer all hairs must be split.
>
>

The scenario seems to be (1) if you create a new empty record all the
undefined values are null. (2) If you try to store an undefined field (LC
field), LC puts an empty string. It does this for any SQLite field type. (3)
When you read it back, if the SQlite field is an integer LC displays a
zero, if the SQLite field is text, then LC leaves it as an empty string.

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4412919.html

Sent from the Revolution - User mailing list archive at Nabble.com.

_______________________________________________

[Bob Sneidar]

I know I repeat myself, but I did the same think Mark was doing only with mySQL and I used MySQLWorkbench and avoided LC. I got the same results. It seems that if you use an empty string "" on a numeric column, SQL interprets that as zero 0. It begs the question however, why in the world you would pass a string value to a numeric column? I am not sure what happens in sqLite when you pass NULL (notice that is not "NULL"). With mySQL it works as expected. The value of NULL (not the string but NULL) is inserted/updated as you would expect.

I see no difference between the results using an SQL editor and LC in this regard, at least when using mySQL. So although the results might not be what some would expect, it is not LC's doing.

Bob

[Mark Smith]

slylabs13 wrote

>
> It seems that if you use an empty string "" on a numeric column, SQL
> interprets that as zero 0.
>

Hi Bob, that basically is the problem right there.

slylabs13 wrote

>
> It begs the question however, why in the world you would pass a string
> value to a numeric column?
>

I'm not, or at least I'm not intending to. I have a form with a bunch of LC
fields on them. Some are going to have text strings in them, some numbers.
When the user leaves the form I want to save everything to the database.
When the user comes back to the form I want to reload everything they have
saved, and redisplay it for further editing/viewing. However, fields in LC
are not typed. LC does not distinguish between a text field and a number
field. I think they are just all text to LC.

I am just copying whats in the field to the database. If nothing is in the
field and I copy it to an SQlite text field, it comes back empty when I
reload it. Which is great. However, if I copy nothing (as in nothing in the
LC field) to an integer field in SQLite when I subsequently reload it, it
comes back as 0. That translation is an error in my opinion.

One could code missing values as -99 or something (using defaults as you
suggest) and do conversion to nothing before displaying... there are lots of
possible workarounds (I'm just storing everything in text fields in SQLite
for now, because that seems to be the simplest solution and requires no
additional code). If you don't enter anything into a field (number or text)
then after recall I think LC should display nothing. There may still be some
debate about this, but I think that would be the ideal scenario. That's how
Pete has written up the bug report.

-- Mark

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4414587.html

[Mark Wieder]

Bob Sneidar <bobs@...> writes:

>
> I just tested this with a numeric column in mySQL. The column has NOT NULL
unchecked and the default value set
> to NULL. When I updated from LC setting the column to NULL that is the value
that the column was set to.
> HOWEVER if I updated the column with an empty string, I got 0 just as you say.
We good so far? Okay.
>
> So I wanted to see what would happen if I bypassed LC altogether and did the
same updates in a mySQL utility. I
> got THE EXACT SAME RESULTS!!! When I updated a numeric column with an empty
string, I got the number 0. What
> does this all mean? It means that LC is NOT borking the update. It is just the
way SQL reacts when you send an
> empty string to a numeric field!
>
> They only real issue left is that LC converts a NULL value to an empty string
in it's queries for the reason I
> explained, that LC abhors an ASCII 0 in a string. I hope I have made this
clear now. I have screen snapshots to
> verify my results if you would like me to send them to you off list.

What happens if you use numtochar(0) instead of NULL or an empty string?

--
Mark Wieder

[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

[Mark Smith]

I should have added many of the ideas are things others on the list have
suggested in response to my "looping over all buttons" post so thanks to all
who made suggestions.... including Jacqueline, who's comment about not doing
do is something I am still working on. I really appreciate all of the help
here.

Cheers,

-- Mark

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4414784.html

[Pete]

Hi Mark,
I didn't get a chance to test this code so sorry, yes, the array name
should be quoted. I'm not sure why the rest of it isn't working though.
If you look at the array in debug, you should see its keys being 1,2, etc
and the value of each key being the value of the field, not the name of the
field. You could try changing the line in my sample code "put field
myField into myArray[x]" to "put the text of field myField into myArray[x]".
Pete

On Thu, Feb 23, 2012 at 10:37 AM, Mark Smith <Mark_...@cpe.umanitoba.ca>wrote:

> 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.
>

--

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

[Mark Schonewille]

Hi,

In SQL land, empty doesn't exist. Whenever you want a value to be empty, you must set the value to null. Be careful: in SQLite null is not NULL (I can never remember which one I need; you'll have to try). Note that if you're doing a query on fields that contain null values, those records won't be returned by default. If you really want to be able to query an empty field, you have to look for null values explicitly or use a special code to indicate that the field is empty (e.g. 'empty' for strings; -99 may work for positive integers; null would be best).

--
Best regards,

Mark Schonewille

Economy-x-Talk Consulting and Software Engineering
Homepage: http://economy-x-talk.com
Twitter: http://twitter.com/xtalkprogrammer
KvK: 50277553

Download the Installer Maker Plugin 1.7 for LiveCode here http://qery.us/za

[Mark Smith]

Peter Haworth-2 wrote

>
>
> If you look at the array in debug, you should see its keys being 1,2, etc
> and the value of each key being the value of the field, not the name of
> the
> field. You could try changing the line in my sample code "put field
> myField into myArray[x]" to "put the text of field myField into
> myArray[x]".
>

Ok now I get it, thanks (this makes sense, particularly after I went back
and reread the revExecuteSQL dictionary entry carefully).

-- Mark

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4414879.html

Sent from the Revolution - User mailing list archive at Nabble.com.

_______________________________________________

[Bob Sneidar]

Using numToChar(0) from within LC puts NULL into the column. I hope that clears it up a bit more.

Bob

[Mark Smith]

Peter Haworth-2 wrote

>
> You could try changing the line in my sample code "put field
> myField into myArray[x]" to "put the text of field myField into
> myArray[x]".
> Pete
>

Yipppeee! That worked. The following code copies all of a forms values into
an sql database... no manual coding required. Truly awesome... thanks to
everyone who contributed suggestions along the way.

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

put the text of field fieldname into myArray[x] -- put the field
VALUE into myarray

add 1 to x
end if
end repeat
put space into char -1 of tCmd
put "where EventID=" & tEventID after tCmd

revExecuteSQL gConnectID, tCmd, "myArray"

You can use a custom parameter called cStoreMe with a value of N to indicate
you don't want a field copied to the database (and you don't have to define
it for the fields you do want copied). I have 20 entry forms with up to 100
values per form to code, so this little snippet is going to save a TON of
time.

I've really appreciated the discussion.

-- Mark

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4415011.html

Sent from the Revolution - User mailing list archive at Nabble.com.

_______________________________________________

[Mark Smith]

And as a bonus.... (sorry, in my excitement I forgot to mention this)... you
don't have to escape anything either, since this is using the substitution
form of the revExecuteSQL command.

-- M

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4415024.html

[Pete]

Here's an illustration of how null works in sqlite, using sqlite3, the
"official" command line tool for sqlite admin and therefore most likely to
work correctly. You can also go to http://www.sqlite.org/nulls.html for a
write up on how null is treated in various sqlite expressions.

First create a table and put some entries in it:

create table t1(a int, b int, c int);
insert into t1 values(1,0,0);
insert into t1 values(2,0,1);
insert into t1 values(3,1,0);
insert into t1 values(4,1,1);
insert into t1 values(5,null,0);
insert into t1 values(6,null,1);

insert into t1 values(7,null,null);

I've used lowercase null above but uppercase NULL works too.

Now query all the rows

select * from t1;

a b c

---------- ---------- ----------

1 0 0
2 0 1
3 1 0
4 1 1
5 0
6 1
7

All records are returned, including rows 5-7 which have a null value in
columns b and c. Notice they just show no value, not the word null.

Now try this SELECT:

select * from t1 where b is null;
a b c
---------- ---------- ----------
5 0
6 1
7

All rows with null in column b are correctly returned. Note you cannot use
the expression "b=null" - you'll get nothing returned. You must use the
special "is null" or "is not null" operators.

Pete

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

[Bob Sneidar]

For the sake of satisfying my own curiosity, I just used the Firefox sqLite manager to open an sqLite db file. I added a column to a table for testing purposes, called bobnum integer default NULL. When browsing, the column indicates it's value is Null. But when I update a row using update activities set bobnum = "" where id = 41275 and then I browse the contents, it tells me the contents is an empty string! THAT is NOT what I would have expected!! It certainly is NOT what mySQL did.

So now that my curiosity was piqued, I used a real string. I put "test" into the numerical column, and lo and behold sqLite accepted a text string into a numerical column!!

sqLite should have either thrown an error or else put a 0 in the column, but how can an integer typed column contain a string of any type?? What else will sqLite accept as valid? Apparently sqLite could care less about typing (which is probably why RunRev chose it for it's defacto database). For this reason we cannot depend on errors thrown in sqLite as a means of error checking what a user enters. (Some people actually do that yes). I have to think that sqLite is being VERY forgiving in this regard. If sqLite is going to behave this way, then for the purposes of LC we may as well define all our columns to be text and be done with it.

Bob

[Mark Smith]

slylabs13 wrote

>
> For the sake of satisfying my own curiosity, I just used the Firefox
> sqLite manager to open an sqLite db file. I added a column to a table for
> testing purposes, called bobnum integer default NULL. When browsing, the
> column indicates it's value is Null. But when I update a row using update
> activities set bobnum = "" where id = 41275 and then I browse the
> contents, it tells me the contents is an empty string! THAT is NOT what I
> would have expected!! It certainly is NOT what mySQL did.
>
> So now that my curiosity was piqued, I used a real string. I put "test"
> into the numerical column, and lo and behold sqLite accepted a text string
> into a numerical column!!
>
> sqLite should have either thrown an error or else put a 0 in the column,
> but how can an integer typed column contain a string of any type?? What
> else will sqLite accept as valid? Apparently sqLite could care less about
> typing (which is probably why RunRev chose it for it's defacto database).
> For this reason we cannot depend on errors thrown in sqLite as a means of
> error checking what a user enters. (Some people actually do that yes). I
> have to think that sqLite is being VERY forgiving in this regard. If
> sqLite is going to behave this way, then for the purposes of LC we may as
> well define all our columns to be text and be done with it.
>
> Bob
>

Hi Bob, check out #3 in the SQLite FAQ <http://www.sqlite.org/faq.html#q3>

Cheers,

- Mark

--
View this message in context: http://runtime-revolution.278305.n4.nabble.com/Anomoly-when-storing-empty-values-into-SQLite-integer-fields-tp4408942p4415835.html

Sent from the Revolution - User mailing list archive at Nabble.com.

_______________________________________________

[Pete]

Thanks for pointing that out Mark.

SQLite allows any type of data in any column, it doesn't have strong
typing. But if you want strong typing, you can use a CHECK constraint on
any column to limit what type of data is acceptable.

I'll take a leaf out of Ken Ray's book here and start the next section with
the tag:

<soapbox>

Yes, some people do rely on a db's data checks to flag errors, and wise
people they are. That's where data checks belong, not in your application
code. Why duplicate the work the db already does for you? It probably
does it more efficiently and most of the time it's easier to define.

If you need to add/delete/change a validation check, you can go into your
schema and do it, no need to create a new version of your application and
send it to all your users.

It's the only way to protect your database from invalid data being inserted
by anyone, either inadvertently or intentionally, who can get their hands
on any one of of the available SQLite admin tools.

You can even specify custom error messages if you choose, at least in
SQLite (see the RAISE function, don't know about other SQL implementations).

I'm a firm believer in getting as much data handling out of my application
code and into the database. The DEFAULT, UNIQUE, FOREIGN KEY, NOT NULL
constraints are all valuable tools provided by SQL; use them where
appropriate, they will serve you well.

</soapbox>

Pete

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

[Bob Sneidar]

Well tickle me pink and call me Porky! Whoda thunk it? I like the part: "SQLite does use the declared type of a column as a hint that you prefer values in that format." That as good as saying that apart from the unique key nothing is typed because nothing is enforced! I think I am going down to the bank tonight after hours and engage in some "legal withdrawal affinity".

Bob

[Bob Sneidar]

For basic things, using SQL to check for consistency is acceptable I suppose. But how would you use SQL to verify a phone number was properly formatted based upon whether or not it was international? How about a valid zip code depending on the country? How about a reservation, making sure it doesn't fall within the date and time range of another reservation for the same room? Given these examples, I don't think it can be said that all validations should happen in SQL, and if I am doing SOME in the application, my own feeling is that I may as well do them all and be sure all is well before the insert/update happens. Also, all doing the updates myself gives me control over which validations to apply to my data, and I have a validation system where I can turn things on and off for each field/column. I would have to modify the schema to do the same thing in SQL, even if I could do all validating there.

Bob

On Feb 23, 2012, at 6:35 PM, Pete wrote:

> Yes, some people do rely on a db's data checks to flag errors, and wise
> people they are. That's where data checks belong, not in your application
> code. Why duplicate the work the db already does for you? It probably
> does it more efficiently and most of the time it's easier to define.