Problem with sqlite3 .import command

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

Problem with sqlite3 .import command

Константин Краснов
Then i try to import following .csv file

col1;col2
1;1
2;2
3;3

with .import command into a table with name what contents "-" sign

sqlite> .open test.db
sqlite> drop table if exists [my-test-table];
sqlite> .mode csv
sqlite> .separator ;
sqlite> .import 'my-test-table.csv' [my-test-table]

And here sqlite3 returns the following error: "Error: no such table:
[my-test-table]"

However, a table with name " my-test-table" was created in the database and
is empty

sqlite> .schema
CREATE TABLE [my-test-table](
  "col1" TEXT,
  "col2" TEXT
);

and the command
sqlite> SELECT * FROM [my-test-table];
returns none.

I try the latest precompiled sqlite3 binary from sqlite.org and binary
compiled from sqlite-amalgamation-3240000.zip source.

So, when I replace
sqlite3_snprintf(nByte+20, zSql, "INSERT INTO \"%w\" VALUES(?", zTable);
with
sqlite3_snprintf(nByte+20, zSql, "INSERT INTO %s VALUES(?", zTable);
in function do_meta_command on line 13700 of shell.c file
from sqlite-amalgamation-3240000.zip and recompile sqlite3 all works fine.

The CREATE TABLE statement on line 13658 in this file use %s modifier for
table name
char *zCreate = sqlite3_mprintf("CREATE TABLE %s", zTable);

I think, that the same %s modifier should be used in INSERT INTO statement.
_______________________________________________
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: Problem with sqlite3 .import command

Simon Slavin-3
On 28 Jun 2018, at 9:21am, Константин Краснов <[hidden email]> wrote:

> However, a table with name " my-test-table" was created in the database and
> is empty
>
> sqlite> .schema
> CREATE TABLE [my-test-table](
>  "col1" TEXT,
>  "col2" TEXT
> );

Dear Konstantin,

The above is not showing a table with the name "my-test-table".  The brackets you see are part of the table name.  So the table is called "[my-test-table]".  This is the cause of your problem:

SQLite version 3.22.0 2017-12-05 15:00:17
[...]
sqlite> .import 'my-test-table.csv' [my-test-table]
Error: no such table: [my-test-table]
sqlite> .schema
CREATE TABLE [my-test-table](
  "col1" TEXT,
  "col2" TEXT
);
sqlite> .import 'my-test-table.csv' testTable
sqlite> .schema
CREATE TABLE [my-test-table](
  "col1" TEXT,
  "col2" TEXT
);
CREATE TABLE testTable(
  "col1" TEXT,
  "col2" TEXT
);
sqlite> .import 'my-test-table.csv' test-table
Error: near "-": syntax error
sqlite> .import 'my-test-table.csv' [testTable]
Error: no such table: [testTable]

The problem seems to be that either the shell tool or SQLite itself is not consistent in how it understands square brackets around table names.

Using square brackets around identifiers is not standard SQL.  It's used by SQL Server and SQLite allows it in some places for compatibility with SQL Server.  And of course, as the above shows, without the square brackets around the identifier you can't use the '-' character as part of an identifier name.

Whether these is considered bugs or not, and whether they will be fixed or not, are things I don't know.  Perhaps one of the developer team will comment.

If you are writing code from scratch I recommend that you use identifiers like "MyTestTable" instead of "[my-test-table]".  On the other hand if you are trying to transfer hundreds of lines of existing SQL Server code then you might want to wait for a response from the SQLite developer team.

Hope this helps.

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