Cmd Isql Less Operator

Question

I have a firebird database and I'm trying to do something. When i try this command it's not working.

echo DELETE FROM TXN WHERE SALESBEGIN < current_date - 2;" | isql -u sysdba -p masterkey localhost:C:\aaa.fdb

The SALESBEGIN column is of type DATETIME

but when i delete "<" operator its working. How can i fix it? This is output;

    C:\Program Files (x86)\Firebird\Firebird_2_0\bin>echo "DELETE FROM TXN 
    WHERE SALESBEGIN < current_date - 2;" | isql -u sysdba -p masterkey 
    localhost:C:\aaa.fdb
    Database:  localhost:C:\aaa.fdb, User: sysdba
    SQL> CON> Expected end of statement, encountered EOF

score 2 · Accepted Answer · 2017-11-06T11:41:50.347

The problem is the < which is the redirection character for the command line. You need to escape that using ^.

However, according to this site:

When a pipe is used, the expressions are parsed twice. First when the expression before the pipe is executed and a second time when the expression after the pipe is executed. So to escape any characters in the second expression double escaping is needed

So you need three ^ characters to escape the < sign:

echo DELETE FROM TXN WHERE SALESBEGIN ^^^< current_date - 2; | isql -u sysdba -p masterkey localhost:C:\aaa.fdb

In my experience things like that are much easier if you simply put the statements into a SQL script and then run that script. In isql that would be isql -i delete.sql

If you still want to keep everything in a single batch file, you could do something like this:

echo DELETE FROM TXN WHERE SALESBEGIN ^< current_date - 2;> _temp_delete.sql 
isql -i _temp_delete.sql -u sysdba -p masterkey localhost:C:\aaa.fdb

You still need to escape the < in there but only once as the | is not involved.

See also Batch character escaping for more information on how to escape special characters on the Windows command line or batch scripts.

It didn't work. `C:\Program Files (x86)\Firebird\Firebird_2_0\bin>echo DELETE FROM TXN WHERE SALEBEGIN ^< current_date - 2; | isql -u sysdba -p masterkey localhost:C:\aaa.fdb Database: localhost:C:\aaa.fdb, User: sysdba SQL> Statement failed, SQLCODE = -104 Dynamic SQL Error -SQL error code = -104 -Unexpected end of command - line 1, column 36 SQL> C:\Program Files (x86)\Firebird\Firebird_2_0\bin>` — lilrevo, Nov 06 '17 at 10:52
@lilrevo: try `>` instead of the `|` to "send" the statement to `isql`. — , Nov 06 '17 at 11:04
When I change `<` operator to the `=` operator, script is working. The problem is only `<` and `>` operators. — lilrevo, Nov 06 '17 at 11:09

Cmd Isql Less Operator

1 Answers1