Informix SQL 11.5 storing query results in a file with a dynamic name

Question

I am trying to store the results of a query in a file. The following command accomplishes this task:

UNLOAD TO '/usr/data/report.csv' DELIMITER ';'
SELECT COUNT(*) FROM table1;

Problem: I don't want to overwrite the file (report.csv) every time I execute the query.

Question: How can I include a timestamp or date in the UNLOAD TO filename parameter?

Already tried and not working:

UNLOAD TO ('/usr/data/report_' || (SELECT REPLACE(TODAY, '.', '_') FROM systables WHERE tabid = 1) || '.csv') DELIMITER ';'
SELECT COUNT(*) FROM table1;

Error message displayed:

#
# 809: SQL Syntax error has occurred.
#

Version: Informix SQL 11.50

Answer 1

The first thing to realize about the UNLOAD statement (also the LOAD statement — and the INFO and OUTPUT statements) is that it is (they are) implemented by the client programs such as DB-Access or I4GL or ISQL, and not by the Informix DBMS proper. That is, the DB-Access program reads the command and acts on it. In particular, the 'file related' part — the UNLOAD TO 'file' DELIMITER ';' part of the statement — is never seen by the database server; only the SELECT part is sent to the DBMS. You therefore cannot use SQL concatenation in it; in fact, you can only write literal file names in it (in DB-Access; I4GL allows a variable for the file name).

The way I'd do what you're after is:

#!/bin/sh

timestamp=$(date +'%Y%m%d.%H%M%S')
dbaccess ${DATABASE:-default_db} - << EOF
UNLOAD TO 'report-$timestamp.csv' DELIMITER ';'
SELECT COUNT(*) FROM table1;
EOF

This uses the date command to generate the timestamp in ISO 8601 (compact) notation. It then uses the shell 'here document' to generate the file name that's fed to DB-Access. The ${DATABASE:-default_db} notation uses the database named by the environment variable $DATABASE, but if it is unset, uses default_db as the database name.

In this example, there's nothing in the here document except the timestamp that will be expanded by the shell. In fact, SQL generally stays well away from the shell metacharacters that can cause trouble (dollar and back-quote, primarily). So it is not usually something you have to worry about.