How to correctly execute sql query using bat file?

Question

SQL

Good day.
There is a task: to run a daily sql query using a bat file. firebird 2.1 server on localhost. I use the built-in isql utility. I'm trying to run a bat file, but it returns empty results, the same request works through cmd. What is wrong? Maybe through something else you need to make a request? I'm not familiar with PowerShell.

set isql="C:\\Program Files(x86)\\FireBird\\FireBird_2_1\\BIN\\isql.exe"

CONNECT '127.0.0.1/3080:E:\Torgovlya54\MAIN.GDB' USER 'sysdba' PASSWORD 'masterkey';

SELECT C.CODE AS "Табельный", C.NAME AS "Ф.И.О.", T.TRANZDATE AS "Дата", max(T.TRANZTIME) AS "Время", T.INFOSTR AS "Карта" sum(T.SUMM) AS "Сумма" FROM DOCUMENT D LEFT JOIN TRANZT T ON D.ID = T.DOCUMENTID JOIN CLIENT C ON D.CLIENTID = C.ID WHERE T.TRANZDATE = cast('now' as date) and D.STATE = 1 AND D.ISFISCAL = 1 AND D.CLIENTID >=0 AND T.TRANZTYPE = '36' GROUP BY C.CODE, C.NAME, T.TRANZDATE, T.INFOSTR;

set outputfile=outputfile.txt
echo %mydt% >> %outputfile%

Answer 1

Try like this:

set isql="C:\\Program Files(x86)\\FireBird\\FireBird_2_1\\BIN\\isql.exe"
echo ^
SELECT C.CODE AS "Табельный", C.NAME AS "Ф.И.О.", T.TRANZDATE AS "Дата", max(T.TRANZTIME) AS "Время", T.INFOSTR AS "Карта" sum(T.SUMM) AS "Сумма" FROM DOCUMENT D LEFT JOIN TRANZT T ON D.ID = T.DOCUMENTID JOIN CLIENT C ON D.CLIENTID = C.ID WHERE T.TRANZDATE = cast('now' as date) and D.STATE = 1 AND D.ISFISCAL = 1 AND D.CLIENTID >=0 AND T.TRANZTYPE = '36' GROUP BY C.CODE, C.NAME, T.TRANZDATE, T.INFOSTR; ^
| %isql% 127.0.0.1/3050:E:\Torgovlya54\MAIN.GDB sysdba masterkey > outputfile.txt

Each line passed to isql must be trailed with "^" to be included in the same command as echo and passed to isql as input.
All isql output is directed to a text file.
I don’t have Windows at hand, and even more so a firebird. so try it yourself. Let me know if it doesn't work, we'll keep looking.