SQLite Forum

Input from Windows Batch file
Login

Input from Windows Batch file

(1) By anonymous on 2020-08-26 08:44:35 [link] [source]

Using Windows. How do I pass the output from a DOS batch file unto the command line?

I have a batch file with the following line:

echo SELECT * FROM [%1] ORDER BY RANDOM() LIMIT %2;

When I execute it in SQLite CLI:

sqlite> .shell "D:/SQLite32/SCRIPTS/Techniques/N Random Records.BAT" tblSalesRevenue 10

D:SQLite32>echo SELECT * FROM [tblSalesRevenue] ORDER BY RANDOM() LIMIT 10; SELECT * FROM [tblSalesRevenue] ORDER BY RANDOM() LIMIT 10; sqlite>

The batch file correctly constructs the SQL statement:

SELECT * FROM [tblSalesRevenue] ORDER BY RANDOM() LIMIT 10;

How do I execute that statement (without writing it to a file, or copy/paste)?

(2.2) By Richard Hipp (drh) on 2020-08-26 12:55:47 edited from 2.1 in reply to 1 [source]

Here is one approach:

  1. Apply the source-code patch shown at https://www.sqlite.org/src/info/6c716f4b556ea8f9. (This change will likely be part of the next release.)

  2. Prepend your script with | and make it the argument to ".read":

        .read '| "D:/SQLite32/SCRIPTS/Techniques/N Random Records.BAT" tblSalesRevenue 10'
    

    Note that quoting is important in the example. The entire command is quoted by '...'. Internally, the command uses "..." around the name of the script, as the script name contains spaces.

Edit: Reformatting to work around Markdown goofiness.

(3) By anonymous on 2020-08-26 12:45:23 in reply to 2.0 [link] [source]

Thanks for the response; I'll wait for the next release as applying the patch is beyond my reach at present.

Not having much success with the work around; here's my session on Windows 10:

/* the database is open & has the table */

sqlite> .open ./db/techniques.db

sqlite> .tables

tblSalesRevenue

/* I am using the latest pre-compiled binary, I believe */

sqlite> .version

SQLite 3.33.0 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f zlib version 1.2.11 gcc-5.2.0

/* trying the workaround */

sqlite> .read '"D:/SQLite32/SCRIPTS/Techniques/N Random Records.BAT" tblSalesRevenue 10'

Error: cannot open ""D:/SQLite32/SCRIPTS/Techniques/N Random Records.BAT" tblSalesRevenue 10"

/* the batch file exists AND produces the SQL statement correctly */

sqlite> .shell "D:/SQLite32/SCRIPTS/Techniques/N Random Records.BAT" tblSalesRevenue 10

D:SQLite32>echo SELECT * FROM [tblSalesRevenue] ORDER BY RANDOM() LIMIT 10;

SELECT * FROM [tblSalesRevenue] ORDER BY RANDOM() LIMIT 10;

The batch file has just one line:

echo SELECT * FROM [%1] ORDER BY RANDOM() LIMIT %2;

What am I missing? (thanks for your patience, I've just started looking as SQLite)

(4.1) By Richard Hipp (drh) on 2020-08-26 12:56:19 edited from 4.0 in reply to 3 [link] [source]

You are missing two things:

  1. There was a formatting error on my previous post. Markdown was eating the | character at the beginning of the argument to the .read command. Now fixed.

  2. I just checked in the changes to support having ".read" read from a pipeline earlier this morning. You will need to recompile "sqlite3.exe" from the latest sources that you download from Fossil. Either that, or wait on the next release to come out, which should be late this year.

(5) By Larry Brasfield (LarryBrasfield) on 2020-08-26 18:21:33 in reply to 3 [link] [source]

If you like, you can use this .exe, built this morning: "patched SQLite v3.33.0 shell, for Windows 10, 64-bit".

I could put the patched source up, but it is nothing more or less than as explained in the .txt file beside the .exe .

(6) By anonymous on 2020-08-26 19:08:58 in reply to 5 [link] [source]

Thanks for sharing.

The txt file indicates that the executable is 64-bit; Currently, I am using the 32 bit pre-compiled binary.

If it is not too much trouble, can you build the 32-bit version please?

(9) By Larry Brasfield (LarryBrasfield) on 2020-08-26 23:38:21 in reply to 6 [link] [source]

Done, and put in the same place.

(10) By anonymous on 2020-08-27 05:22:09 in reply to 9 [link] [source]

Thank you for the 32-bit version and the hints on compilation. Much appreciated.

(7.1) Originally by anonymous with edits by Richard Hipp (drh) on 2020-08-27 01:42:21 from 7.0 in reply to 5 [link] [source]

I tried the version you posted. This is 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 'd:/sqlite32/db/techniques.db'
sqlite> .mode column
sqlite> .headers on
sqlite> .read '| "D:/SQLite32/SCRIPTS/Techniques/N Random Records.BAT" blSalesRevenue 3'
Error: near line 2: near "E": syntax error
RecID  SalesPerson  Category    Revenue  Period
-----  -----------  ----------  -------  ------
24     Reynolds     Commercial  489.25   Q2
54     Green        Commercial  150.94   Q2
5      White        Commercial  406.13   Q2

It worked except for :

Error: near line 2: near "E": syntax error

I have VS2019 enterprise (I use C#) - might I have a brief guide on how you compile the source with VS2019, please?

(8.1) By Larry Brasfield (LarryBrasfield) on 2020-08-26 23:26:12 edited from 8.0 in reply to 7.0 [link] [source]

Here is a session screen scrape: [C:Tmp] > sqlite3_v3r33r0_patched 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 '| oschema.cmd' CREATE TABLE Dummy(id int primary key); CREATE TABLE People ( id INTEGER PRIMARY KEY, fname TEXT, lname TEXT, email TEXT UNIQUE NOT NULL, password TEXT ); CREATE TABLE Config ( name TEXT UNIQUE, value TEXT, validate TEXT ); CREATE VIEW DummyTagged as select p.id as id, p.fname||'Boy' as dname from People p, Dummy d where d.id=p.id/* DummyTagged(id,dname) */; sqlite> .q

[C:Tmp] > type oschema.cmd @echo off echo .schema

[C:Tmp] >

Note the pipe character at the start of the single-quoted parameter to .read .

The amalgamation comes with a makefile which can be used thusly: nmake -f Makefile.msc USE_CRT_DLL=1 OPTIMIZATIONS=3 from a CLI shell (cmd.exe, unfortunately) which has had its environment setup by running one of the .bat files found in "%VCINSTALLDIR%AuxiliaryBuild". The choice among those .bat files determines your machine target.