On Sat, Jun 13, 2009 at 4:16 PM, Florian v. Savigny<[email protected]> wrote:
> I'm very sorry if this is a very stupid question. Intuitively, I would
> assume that of course, any TEXT or BLOB field may contain
> newlines. I'm, however, puzzled about two things (it all refers to the
> commandline interface):
>
> - while I can insert values with newlines by using the X'ABCD'
> notation, i.e. saying sth like
>
> INSERT INTO my_table VALUES (x'0a');
>
> it does not seem possible to use a newline literally in a string,
> e.g. like this:
>
> INSERT INTO my_table VALUES ('
> ');
Yes you can:
SQLite version 3.6.14.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table x ( a );
sqlite> INSERT INTO x VALUES('line1
...> line2');
sqlite> SELECT a FROM x;
line1
line2
sqlite> SELECT hex(a) FROM x;
6C696E65310A6C696E6532
sqlite>
> But does this mean you can only specify newlines in x'abcd'
> notation? Or even, that SQLite stores any string with newlines as a
> BLOB?
You could also use: "VALUES ('some line' || x'0A' || 'other line')"
> - (Almost the same question) When I use quote() to SELECT some
> columns, is it conceivable that SQLite returns something like this
> (assuming line mode):
>
> quote(text) = '
> '
> Or will it always use the X'ABCD' notation to output newlines?
The quote() function only assures you that the return string can be
used on a SQL statement without the risk of SQL injection attacks, by
doubling the internal quotes.
If it thinks the field is a BLOB (because you used the "x" prefix),
then it will output an hex dump, but I don't think you can't really
rely on that behavior (I don't think it's specified on any SQL
standard).
Continuing the previous example:
sqlite> INSERT INTO x VALUES('It''s ok');
sqlite> INSERT INTO x VALUES(x'3031320A303132');
sqlite> .mode col
sqlite> .h 1
sqlite> SELECT oid, quote(cast(a AS TEXT)) FROM x;
rowid quote(cast(a AS TEXT))
---------- ----------------------
1 'line1
line2'
2 '012
012'
3 'It''s ok'
sqlite> SELECT oid, quote(cast(a AS BLOB)) FROM x;
rowid quote(cast(a AS BLOB))
---------- -------------------------
1 X'6C696E65310A6C696E6532'
2 X'3031320A303132'
3 X'49742773206F6B'
So, as you can see, if you insert values as BLOBs it will return BLOBs
and if you insert values as TEXT it will return text.
Regards,
~Nuno Lucas
>
> (I have already found out that newlines stored as blobs are returned
> literally if you do not use quote(), but as I said, I haven't been
> able to store newlines as text, so I could not test how they are
> returned.)
>
>
> The background of my question is not curiosity, but the fact that I
> have designed an Elisp interface that uses SQLite's commandline
> interface with quote() and parses its line output. As long as the
> values cannot contain newlines, that's quite straightforward, but if
> they can, it's suddenly very intricate. Thus, it's the output that I'm
> chiefly concerned about, but also the fact whether I always have to
> use the X'ABCD' notation to insert a value that contains newlines (and
> perhaps also carriage returns, which I haven't tested).
>
> I'm sorry if I merely haven't found the pertinent documentation, but I
> have rummaged the docs.
>
> Thanks for any help here!
>
> Florian
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
