On Sat, Jun 13, 2009 at 4:16 PM, Florian v. Savigny<lor...@fsavigny.de> 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users