sqlite bug? .mode insert does not quote identifiers

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite bug? .mode insert does not quote identifiers

jon baldry
I have identifiers that unfortunately have a hyphen within them.

I want to output the data from the tables with those identifiers using
.mode insert and .out foo.sql for re-loading.

I can't use .dump easily as i want to modify the tables before
re-loading the data, so .mode insert seemed my best option.

Now obviously, I could avoid the problem by avoiding hyphens, but my
database was written originally in XML and I'll have to change a fair
amount of code to undo my naming convention.

I have worked around this in PHP to modify the output, but figured it
would be useful to report the issue in the hope that it could be fixed
(or I could be shown a way to have this work correctly)

Regards

   Jon


.mode insert does not appear to quote identifiers when needed.

qlite3 --version

3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f

Example:

sqlite> create table "d-asher" ( "d-asher" TEXT );
Run Time: real 0.001 user 0.000000 sys 0.000000

sqlite> insert into "d-asher" ("d-asher") values ('a b c');
Run Time: real 0.000 user 0.000000 sys 0.000000

sqlite> .mode insert "d-asher"
sqlite> select * from "d-asher";
INSERT INTO 'd-asher'(*d-asher*) VALUES('a b c');
Run Time: real 0.000 user 0.000000 sys 0.000000

Re-entering that line throws a syntax error.

sqlite> INSERT INTO 'd-asher'(d-asher) VALUES('a b c');
Run Time: real 0.000 user 0.000000 sys 0.000000
Error: near "-": syntax error

.dump gets it right.

sqlite> .dump "d-asher"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE *"d-asher" *(*"d-asher"*TEXT );
INSERT INTO "d-asher" VALUES('a b c');
COMMIT;


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite bug? .mode insert does not quote identifiers

Richard Hipp-3
On 4/6/18, jon baldry <[hidden email]> wrote:
> I have identifiers that unfortunately have a hyphen within them.
>
> I want to output the data from the tables with those identifiers using
> .mode insert and .out foo.sql for re-loading.
>
> I have worked around this in PHP to modify the output, but figured it
> would be useful to report the issue in the hope that it could be fixed
> (or I could be shown a way to have this work correctly)

That is fixed in 3.19.0.  Suggest you update to the latest - 3.23.0.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users