On Sun, Jul 03, 2005 at 02:59:19PM -0400, Tom Shaw wrote:
> Using PHP 5 and SQLite 2.8.14 (This also occurs when commanding
> SQLite directly)
>
> I create a table:
>
> CREATE TABLE dnsbl (ip INTEGER PRIMARY KEY, flags VARCHAR(8), ctime
> INTEGER, mtime INTEGER, cnt INTEGER, ptr TEXT, refcon INTEGER);
>
> Then I insert a record:
>
> INSERT INTO dnsbl VALUES(-596248527,"IP", 1120286944, 1120286944, 1, "", 0);
>
> Note the value of flags="IP". Now I update the record updating flags
> and ctime because I did keep track if they changed:
>
> UPDATE dnsbl SET flags="IP", ctime=1120286944, mtime=1120311794,
> cnt=2, refcon=0 WHERE ip=-596248527;
>
> After the update I check the update by
>
> SELECT * FROM dnsbl WHERE ip=-596248527;
>
> Flags somehow change from "IP" to -596248527. Note that the value
> -596248527 now appears in the DB variable ip as well as the flags
> variable.
>
> Now there should be nothing wrong with the above that I can see BUT
> if I change the DB variable name from ip to ip_num all works OK
>
> Help and understanding is appreciated.
It appears to me that in the update statement that "IP" is getting set
to the value of the "ip" column. That is, when you do the update the
value of the flags column is getting set to the value of the "ip" column
in the same row.
A more concrete example:
$sqlite test.db
SQLite version 2.8.15
Enter ".help" for instructions
sqlite> c eate table t1 (i,j,k);
sqlite> insert into t1 values (1,'foo','bar');
sqlite> insert into t1 values (1,'some','value');
sqlite> select * from t1;
1|foo|bar
1|some|value
sqlite> update t1 set j="I" where i = 1;
sqlite> select * from t1;
1|1|bar
1|1|value
Now if you select the possible permutations of K from the table:
sqlite>.mode columns
sqlite> select k,"k",'k',K,"K",'K',"S",'S' from t1;
bar bar k bar bar K S
S
value value k value value K S
S
sqlite> select S from t1;
SQL error: no such column: S
In all of these cases the "K",'K',K,"S", etc. tokens are 'expressions'
according to sqlite. See
http://www.sqlite.org/lang_update.html
http://www.sqlite.org/lang_select.html
And http://www.sqlite.org/lang_expr.html explains that an 'expression'
can be a column name or a literal value. From your example, since IP is
the name of a column in your table "IP" evaluated to the column name.
If you wanted the literal 'IP' to be the value in the flags columns then
it would be better to use 'IP' instead of "IP" as "<something>" appears
to first evaluate to a column name and if that is not the case then it
is a literal.
That is, as far as expressions are concerned:
without single or double quotes -> column name
with double quotes -> column name first, if that fails literal
with single quotes -> literal
This is what I gleaned from the documentation and experimenting. Did I
get this correct?
enjoy,
-jeremy
--
========================================================================
Jeremy Hinegardner [EMAIL PROTECTED]
