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

[Richard Hipp]

On 4/6/18, jon baldry  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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.00 sys 0.00

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

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.00 sys 0.00

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.00 sys 0.00
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users