-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Sean Moss-Pultz wrote:
> How can I get the string to stay 0123?
It isn't a string - you supplied an integer and SQLite treated it that way
and then stored it. If you really wanted a string then you must quote it
(remember to use single quotes). If you are using the C api then you can
use bindings and bind exactly as the type you want.
> I've read the docs about "Column Affinity."
Some context about them may help. In SQLite version 2 everything was stored
as a string. That codebase used various heuristics to establish what you
really meant (integer, floats, a string etc). Other more conventional SQL
databases strictly type what can go in each column and have all sorts of
messy syntaxes for declaring the types and length limits.
The column affinity for SQLite 3 shows how various values could be stored
and what happens if you use various type declarations for the column.
However no erroneous conversion is ever done. Consider the affinity a
suggestion to the database on how to store a supplied value but the
suggestion will only be used if it is safe (eg a non-numeric string will
never be stored as a number, an integer can be safely turned into a string).
> BTW, I can't put the number into quotes first because that would break
> my code,
Your code is already broken. A string and an integer are not the same thing :-)
> IE, the user could insert '0123' (string) or 0123 (number). I need these to
> stay
> unique.
That is exactly how SQLite works. It is not weakly typed - just dynamically
typed. ie it is not like PHP (weak typing) but like Python (dynamic
typing). What you put in is what you get out modulo the column affinity.
That is exactly what your console session shows (text affinity). Here is
another to clarify:
sqlite> create table test(t text);
sqlite> insert into test values(0123);
sqlite> insert into test values('0123');
sqlite> select * from test;
123
0123
sqlite> .dump test
BEGIN TRANSACTION;
CREATE TABLE test(t text);
INSERT INTO "test" VALUES('123');
INSERT INTO "test" VALUES('0123');
COMMIT;
If you don't want the values messed with then you want the "None" affinity
which you can get by using "blob" as part of the typename:
sqlite> create table test(t blobmeansnotypeaffinity);
sqlite> insert into test values(0123);
sqlite> insert into test values('0123');
sqlite> select * from test;
123
0123
sqlite> .dump test
BEGIN TRANSACTION;
CREATE TABLE test(t blobmeansnotypeaffinity);
INSERT INTO "test" VALUES(123);
INSERT INTO "test" VALUES('0123');
COMMIT;
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkq/EbYACgkQmOOfHg372QSWjQCgo5FQoNYiYWuEevj98mhNTCvJ
DlYAnj9ehncFoAhmig60PnIcD+cWtPkr
=yf5y
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
