May I run a script with SQL statements and . SQLite commands?
Add a comment
|
4 Answers
Mixing SQL statements with SQLite .commands can be a bit tricky:
$ sqlite3 test.db 'create table X(x integer); .dump'
Error: near ".": syntax error
The easiest and most universal way to deal with this is probably to provide all commands to the standard input of the SQLite3 command line shell, separated by newlines as you would type them:
$ sqlite3 test.db <<EOF
> CREATE TABLE TEST(X INTEGER);
> .dump
> EOF
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE TEST(X INTEGER);
COMMIT;
Since here documents are not available in all shells, you could use an intermediate file to bypass this limitation:
$ cat test.sql
CREATE TABLE TEST(X INTEGER);
.dump
$ sqlite3 test.db <test.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE TEST(X INTEGER);
COMMIT;
Comments
For me works this query:
sqlite3 test.db "select * from sometable"
Comments
Sure you can. Use .read directive in the command line and you'll be there.
Create your script:
sample.sql
DROP TABLE IF EXISTS test;
CREATE TABLE test (id PRIMARY KEY,
rand1 REAL DEFAULT (RANDOM()),
rand2 REAL DEFAULT (RANDOM()));
INSERT INTO test (id) VALUES(1);
INSERT INTO test (id) VALUES(2);
.dump
Then you can run your script:
$ sqlite3 test.db '.read sample.sql'
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (id PRIMARY KEY, rand1 REAL DEFAULT (RANDOM()), rand2 REAL DEFAULT (RANDOM()));
INSERT INTO test VALUES(1,-479816953303475072.01,7897905953557517311.9);
INSERT INTO test VALUES(2,3449088292611145728.0,-595585280596709760.01);
COMMIT;
Comments
echo 'create table X(x integer); .dump' | sqlite3 test.db
