Firebird iSQL - How to input SQL query from command line, and retrieve output in simpler format

Question

I'm using Firebird's isql.exe tool to query an existing database:

isql -u <username> -p <password> <database> -i <file.sql> -o <output.txt>

which reads my SQL statements from file.sql and saves the results to output.txt.

But is there a way to feed the SQL statements into isql via command line, and not from a file?

This is because I actually plan to execute the command above in my .exe installer script (via ExecWait of NSIS Installer).

Also, is there a way to format the output such that only the actual info needed are returned? Currently the output's first line contains the column names, second line a bunch of "====" as separator, third line the actual info, with an arbitrary number of spaces in between each column. This output format makes it hard for me to use in my script.

Regarding your second question: [SET HEADING](http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/isql-set.html#isql-set-heading) — Mark Rotteveel, Dec 04 '14 at 09:56

Mark Rotteveel · Accepted Answer · 2020-12-31T16:04:30.623

7

For your first problem, you can use Firebird isql without an input file as:

echo select * from rdb$database; | isql -u sysdba -p password C:\path\to\db.fdb

This redirects the standard output from echo (value select * from rdb$database;) to the standard input of isql. The result of the query (or queries) will be printed to the standard output of isql (or to file if you use -o).

However as echo is a feature of the command prompt, I am not certain if this will work from NSIS. Otherwise you will need something that does the same thing as echo: print its parameter values to the standard out.

As I commented before, for your second question you should look at SET HEADING and maybe SET WIDTH.

edited Dec 31 '20 at 16:04

answered Dec 04 '14 at 18:24

Mark Rotteveel

82,132
136
114
158

Perfect! I got it to work without the `$database` part. Thank you very much – Obay Dec 05 '14 at 08:44
I'm using `SET LIST ON`, but it seems `SET HEADING OFF` doesn't affect it. Is there a way to hide the column names when setting `LIST` to `ON`? – Obay Dec 05 '14 at 08:56
@Obay Not as far as I know. – Mark Rotteveel Dec 05 '14 at 11:35
@Obay There is no `$database` part in my answer: `rdb$datbase` is a (system) table I used for the example – Mark Rotteveel Dec 05 '14 at 11:36

score 1 · Answer 2 · answered Dec 05 '14 at 08:50

I case others find this useful, I was actually able to implement this in NSIS by using $PLUGINSDIR, which is a temporary folder created by NSIS at runtime. I created a temporary input and output file in that folder. I wrote the SQL statements into the input file using FileWrite and read the output using FileRead

Firebird iSQL - How to input SQL query from command line, and retrieve output in simpler format

2 Answers2