want to specify DB.TABLE with ".import" and ".mode insert"

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

want to specify DB.TABLE with ".import" and ".mode insert"

Carl Edquist

I observe the following two issues with the command-line sqlite3, both
with version 3.6.20 (on RHEL6) and the latest compiled from

It appears that the '.import FILE TABLE' command doesn't support
specifying the database name (db.table) -- I get a "no such table" error.
(I'd guess that it treats "db.table" as the table name rather than
recognizing the db part, as you can do with sql commands.)

Similarly (but maybe a separate issue), the '.mode insert TABLE' output
mode doesn't work with a database name either -- but this is a little more
clear that it's just single-quoting the whole TABLE string, rather than
recognizing the '.' as the db.table separator.

Hopefully the example output log below demonstrates this adequately.

A side nit-- if the table name *does* need to be quoted for '.mode
insert', it should really be with double-quotes, since it's an identifier,
right? (https://www.sqlite.org/lang_keywords.html)

So I would expect/suggest this output:

  > .mode insert sdf.x
  > select 'hi';
  INSERT INTO sdf.x VALUES('hi');

  > .mode insert sdf-x
  > select 'hi';
  INSERT INTO "sdf-x" VALUES('hi');

  > .mode insert sdf-x.asd-y
  > select 'hi';
  INSERT INTO "sdf-x"."asd-y" VALUES('hi');

(Note that even when double-quotes are necessary, the dot should remain

But here is what I actually get:

  $ ./sqlite3
  SQLite version 2015-02-25 13:29:11
  Enter ".help" for usage hints.
  Connected to a transient in-memory database.
  Use ".open FILENAME" to reopen on a persistent database.
  sqlite> attach ':memory:' as sdf;
  sqlite> create table sdf.t1 (x);
  sqlite> select * from sdf.t1;
  sqlite> .mode list
  sqlite> select 'hi';
  sqlite> .output t1.dat
  sqlite> select 'hi';
  sqlite> .output stdout
  sqlite> -- here's the first error
  sqlite> .import t1.dat sdf.t1
  Error: no such table: sdf.t1
  sqlite> -- only works with unqualified table name
  sqlite> .import t1.dat t1
  sqlite> -- but, the table is there
  sqlite> select * from sdf.t1;
  sqlite> .mode insert sdf.t1
  sqlite> select 'bye';
  INSERT INTO 'sdf.t1' VALUES('bye');
  sqlite> .output t1.sql
  sqlite> select 'bye';
  sqlite> .output stdout
  sqlite> -- the 'db.table' name is broken when quoted
  sqlite> .read t1.sql
  Error: near line 1: no such table: sdf.t1

  sqlite> -- again by hand
  sqlite> INSERT INTO 'sdf.t1' VALUES('bye');
  Error: no such table: sdf.t1
  sqlite> -- works as it should when db.table is unquoted
  sqlite> INSERT INTO sdf.t1 VALUES('bye');
  sqlite> .mode list
  sqlite> select * from sdf.t1;

Is this intentional?  If not, would it be easy to fix?

sqlite-users mailing list
[hidden email]