SQLite Forum

Tried with .read - Execute SQL script stored in a table
Login

Tried with .read - Execute SQL script stored in a table

(1) By anonymous on 2020-08-28 13:55:17 [link] [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 [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