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>

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

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

Mark Rotteveel
– Mark Rotteveel

2014-12-04 09:56:45 +00:00
Commented Dec 4, 2014 at 9:56 — Mark Rotteveel
– Mark Rotteveel, Commented Dec 4, 2014 at 9:56

Mark Rotteveel · Accepted Answer · 2020-12-31 16:04:30Z

10

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, 2020 at 16:04

answered Dec 4, 2014 at 18:24

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Obay Over a year ago

Perfect! I got it to work without the $database part. Thank you very much

Obay Over a year ago

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?

Mark Rotteveel Over a year ago

@Obay Not as far as I know.

Mark Rotteveel Over a year ago

@Obay There is no $database part in my answer: rdb$datbase is a (system) table I used for the example

Obay · Accepted Answer · 2014-12-05 08:50:17Z

1

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

answered Dec 5, 2014 at 8:50

Obay

3,20517 gold badges58 silver badges81 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related