Tried with .read - Execute SQL script stored in a table
(1) By anonymous on 2020-08-28 13:55:17 [source]
Obviously I can read from a file and the ability to read piped output from a shell batch/command file is available (now, if I choose to compile the source, or very likely) in the next release. I realise that I cannot store code (functions , stored procedures) in SQLite databases but I can store SQL scripts as text in a table. I'd like to be able to execute SQL scripts stored in a table; I tried the following with no success: sqlite> .open ./db/chinook.db sqlite> select SQL from tblSQL where id = 1; select * from artists; sqlite> .read '| select SQL from tblSQL where id = 1;' 'select' is not recognized as an internal or external command, operable program or batch file. sqlite> .read '| "select SQL from tblSQL where id = 1;"' 'select' is not recognized as an internal or external command, operable program or batch file. Is there a way to achieve this?
(2) By Larry Brasfield (LarryBrasfield) on 2020-08-28 14:24:01 in reply to 1 [link] [source]
One way:
> sqlite3 sqlcmd.sdb
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite> create table ShellCmds (name text unique, cmd text, rank integer);
sqlite> insert into ShellCmds values('DoIt', '.schema', 1);
sqlite> insert into ShellCmds values('Blat', 'select * from People', 2);
sqlite> .q
> sqlite3_v3r33r0_patched.exe furd.sdb
SQLite version 3.33.0 2020-08-14 13:23:32, except patched
per https://www.sqlite.org/src/info/6c716f4b556ea8f9 .
Enter ".help" for usage hints.
sqlite> .read '| sqlite3 -noheader sqlcmd.sdb "select cmd from ShellCmds where rank = 2;"'
1|Jack|Sprat|could.eat@no.fat|
2|His|wife|could.eat@no.lean|
3|And|so|betwixt.the@two.of them|
4|They|licked|the.platter@clean.|
5|Jack|ate|all.the@lean.,|
6|Joan|ate|all.the@fat.|
7|The|bone|they.picked@it.clean,|
8|Then|gave|it.to@the.cat|
9|Jack|Sprat|was@wheeling|
10|His|.wife|by.the@ditch.|
11|The|barrow|turned@over,|
12|And|in|she@did.pitch.|
13|Says|Jack|"She'll@be.drowned!"|
14|But|Joan|did@reply,|
15|"I|don't|think@I.shall,|
16|For|the|ditch.is@quite.dry."|
17|Snurdly|Snodgrass|.@.|
sqlite>
(3) By anonymous on 2020-08-28 14:46:44 in reply to 2 [link] [source]
Perfect! My session: SQLite version 3.33.0 2020-08-14 13:23:32, except patched per https://www.sqlite.org/src/info/6c716f4b556ea8f9 . Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .open ./db/chinook.db sqlite> .read '| sqlite3 -noheader d:/sqlite32/db/chinook.db "select sql from tblSQL where id=1;"' 1|AC/DC 2|Accept 3|Aerosmith 4|Alanis Morissette sqlite> select sql from tblSQL where id = 1; select * from artists Limit 4; sqlite> In my first attempt, I did NOT .open the target DB which is the same as that specified as the 2nd argument. With hindsight, this works very well in that I can read the SQL/CMD from a DB without attaching it i.e. I can structure the DB that contains the SQL/CMD with total flexibility e.g. I can have another column to indicate which DB the SQL/CMD applies to. PS: - I used the 32-bit binary you kindly provided; thanks again. - I need to look up the hyphen (-) parameters for sqlite3