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

Answer 1

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.