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 doe
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
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 se
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
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 activi
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
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-emp
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 requir
Using numToChar(0) from within LC puts NULL into the column. I hope that clears
it up a bit more.
Bob
On Feb 23, 2012, at 10:08 AM, Mark Wieder wrote:
> Bob Sneidar writes:
>
>>
>> I just tested this with a numeric column in mySQL. The column has NOT NULL
> unchecked and the default value
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 tex
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 wo
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
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 he
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 re
Bob Sneidar 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
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 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 wou
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 v
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 co
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. W
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
h
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 ar
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 actual
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
rea
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
On Feb 22, 2012, at 8:58 AM, Robert Brenstein wrote:
> On 21.02.2012 at 19:46 Uhr -0800 Mark Smith appar
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
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 wrote:
> P
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
I think you CAN use NULL as a value to pass, but the NOT NULL of the column has
to be unchecked.
Bob
On Feb 21, 2012, at 7:46 PM, Mark Smith wrote:
>
> 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 I
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
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 my
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
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-revoluti
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. Y
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 somethi
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
On Tue, Feb 21, 2012 at 7:01
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
On Tue, Feb 21
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
On Feb 21, 2012, at 18:58, Mark Smith wrote:
>
> Peter Haworth-2 wrote
>>
>> Lon
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
dif
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 ge
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 t
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
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 statem
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
On Feb 21, 2012, at 17:35, Mark Smith wrote:
> Hi, I've encountered a bit of an anomaly when storing empty values into
> in
44 matches
Mail list logo
