Error "Variable syntax" on shell command within SQL script
Jacob Salomon found !echo with $(date) in a dbaccess script failed with Variable syntax and stty errors because informix uses csh. He worked around it with printf and date. David Link and Art Kagel suggested a SELECT of CURRENT YEAR TO SECOND from sysmaster:sysdual, which Jacob adopted. David Williams advised fixing .cshrc or using csh -f.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Greetings family.
I started writing this in order to present a problem I was having. But while articulating my problem I hit on a workaround that mostly works. I am posting it because I think it may solve a similar problem for someone else.
I have an SQL script that runs a bunch of delete commands. I want to pepper it with messages and time stamps. Logging in as myself I can do this:
dbaccess sysmaster -
> !echo Delete from table yutz at $(date +"%Y-%m-%d %H:%M:%S")
Delete from table yutz at 2026-09-22 11:48:28So it works as I would like it to in the script. I peppered the SQL script with the same !echo command. I got these errors at every !echo command:
stty: : No such device or address
stty: : No such device or address
stty: : No such device or address
stty: : No such device or address
Variable syntax
Ok, lemme back up and run this in an interactive dbaccess session, as user informix this time. (T
he script MUST run as user informix.)
dbaccess sysmaster -
Database selected.
> !echo Delete from table yutz at $(date +"%Y-%m-%d %H:%M:%S")
Variable syntax
Well, at least no stty messages. The main difference between my login and the informix login is that my default shell is ksh and informix's shell is csh. But I get the same error even when I have exec's to ksh before starting the dbaccess session. It would appear that each !echo command is spawning a C-shell and csh does not like that command. I tested this as myself running csh:
% echo Delete from table yutz at $(date +"%Y-%m-%d %H:%M:%S")Variable syntax
I realized that dbaccess is spawning the default shell of the user (be it informix or myself). And each invocation of csh runs .cshrc, which has a bunch of stty commands. These barf because, in my script, the STDIN to dbaccess is a pipe. Hence the stty errors. I can live with this. But what about the "variable syntax" errors?
At first I went googling for a csh equivalent to the $(
command
) syntax. But since Google flooded me with useless solutions to different problems, I came up with a workaround in the sql script:
!printf "Delete from table yutz at ";date +"%Y-%m-%d %H:%M:%S" -- Table name changed, of course
Note: No \\n in the printf command. Thus, I get this in the output:
stty: : No such device or address
stty: : No such device or address
stty: : No such device or address
stty: : No such device or address
Delete from table yutz at 2026-09-22 12:36:08
BTW, if I redirect 2>/dev/null, the date does not show up either.As to how to spawn a csh instance without executing the .cshrc (and getting those stty errors)? That is not an informix issue. But if
a csh expert on this forum chooses to give me a hand with a easy way to skip the .cshrc, I tip my yarmulka to you. ;-)
I hope it's at least been an interesting read.
------------------------------
+-----------------------------------------------------------+
| I am pleased to report that I had no problems today. |
| I had only issues, opportunities, challenges and valuable |
| learning experiences. |
+------------------------------------------ Jacob S --------+
------------------------------
Jacob,
Why not do this?
SELECT "
Delete from table yutz at" || CURRENT YEAR TO SECOND FROM sysmaster:sysdual;
To get you the output you are looking for without spawning a shell.
That is what I have done in the past.
Jacob:
I agree with David, just use SELECT. Also, when I tested your echo command, it works for me:
> !echo Delete from table yutz at $(date +"%Y-%m-%d %H:%M:%S")
Delete from table yutz at 2026-09-22 14:56:35> select "Delete from table yutz at " || current year to second;
(expression)
Delete from table yutz at 2026-09-22 14:57:21Art
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
David suggested: SELECT "Delete from table yutz at " || CURRENT YEAR TO SECOND FROM sysmaster:sysdual; David, That is SO beautiful and simple. And I just flattened my forehead! ♂ Too bad Windows does not supply an emoji of someone tipping his hat, let alone a yarmulka. Jacob S
Art,
it worked for me as well, as jacob. Bur jacob's login shell is ksh so any command I spawn from within dbaccess runs under ksh. Hence, the $(date...) syntax was acceptable. But in my environment, informix's login shell is csh, which does not likes that syntax. Hence, my workaround. Also, you didn't get any stty complaints for at least one of all of these reasons.
You are running it interactively and your STDIN is the terminal, which will not engender complaints from the stty command in your .kshrc. I am running in a script and sending th SQL commands via a pipe so stty complains. I have otehr reasons for using the pipe and I don't want to complicate things by including that technique here.
You may have no stty commands in in you .kshrc
One advantage in using my workaround over David's SELECT is that I chose to run with dbaccess -e (echo the SQL commands) and the SELECT get's in the way a bit. So my choice is the stty errors before each command or the visible SELECT, along with the useless "1 rows retrieved". Either way is imperfect but it's humming along now so I don't mind.
------------------------------
+-----------------------------------------------------------+
| I am pleased to report that I had no problems today. |
| I had only issues, opportunities, challenges and valuable |
| learning experiences. |
+------------------------------------------ Jacob S --------+
------------------------------
Jacob:
OK. Note that if you redirect stderr then the "1 rows retrieved" goes poof! Witness:
$ dbaccess art - 2>/dev/null> select "Delete from table yutz at " || current year to second;
(expression)
Delete from table yutz at 2026-09-22 16:55:03>
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------
Adding -q will remove the rows retrieved but you are correct about the error trapping.
Hi, Yes select but also... fix your broken .cshrc file! tty -s && ssty erase ^H etc To not execute the .cshrc try export SHELL="<path>/csh -f" Regards, David. ------------------------------ David Williams Senior Database Platform Engineer Flutter London ------------------------------
That's a useful workaround to share. Adding messages and timestamps directly into a long SQL script can make it much easier to track which operations have completed and identify where something goes wrong. ------------------------------ Alexander Charlie ------------------------------
Art suggestedL
OK. Note that if you redirect stderr then the "1 rows retrieved" goes poof! Witness:
$ dbaccess art - 2>/dev/null
(expression)
Delete from table yutz at 2026-09-22 16:55:03Yeah, but so do the
345678 row(s) deleted
messages for each successful delete.
I think that for readability of the output file, my original printf scheme was the best. Since I am running the dbaccess with the -e (echo) option, would not see the shell commands that generate the timestamped message for each table purge; I would see only my "purging table yutz at <time stamp>" message. The other ideas here have been terrific and I will likely use them in another situation.
Jacob S
Pipe stderr to grep "row"
Interesting troubleshooting journey. The explanation about dbaccess spawning the user's default shell and the csh command-substitution issue makes the workaround much clearer. Thanks for sharing the practical solution.
------------------------------
Alexander Charlie
------------------------------