Tried with .read - Execute SQL script stored in a table
(1) By anonymous on 2020-08-28 13:55:17 [link]
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
One way: <code> \> 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> </code>
(3) By anonymous on 2020-08-28 14:46:44 in reply to 2 [link]
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