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]

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