Remote Informix 11.5 Command Line Client

Question

Does a command line tool ship with Informix 11.5 similar to SQLCMD for SQL Server?

If yes, how do I connect to a remote server and perform regular SELECT/INSERT/UPDATE queries using it?

Answer 1

Try DB-Access

... DB–Access provides a user interface for entering, executing, and debugging Structured Query Language (SQL) statements and Stored Procedure Language (SPL) routines...

Answer 2

As Michal Niklas says, the standard tool provided with IBM Informix Dynamic Server (colloquially IDS or even just Informix) is DB-Access. However, it is distributed only with IDS itself, not with the Informix Client SDK (CSDK) or Informix Connect (I-Connect) products.

If you want to access IDS from a machine that does not have IDS installed, you need either CSDK or I-Connect on the machine, and some other software - perhaps the original (pre-Microsoft by a decade and more) version of SQLCMD. This is what I use - and have used in various versions over the last (cough, splutter, ouch) twenty-two years or so; I wrote it because I didn't like the command line behaviour of a program called isql (part of the product Informix SQL), which was the pre-cursor to DB-Access. (Lot's of history - not too important to you.)

Usage - SQLCMD has more options than you know what to do with. The basics are simple, though:

 sqlcmd -d dbname@dbserver -e 'select * from table' -x -f file.sql

This connects to a database called 'dbname' at the database server known as 'dbserver' as specified in the sqlhosts file (normally $INFORMIXDIR/etc/sqlhosts). The '-e' indicates an SQL expression - a select statement; the results will be printed to standard output in a strict format (Informix UNLOAD format), one logical line per record. The '-x' turns on trace mode; the '-f' option means read the named file for further commands. The '.sql' extension is not mandatory (beware: DB-Access requires the '.sql' extension and will add it for you). (Arguments not prefixed by either '-e' or '-f' are interpreted heuristically; if it contains spaces, it is SQL; if it does not, it is a filename.) The '-H' option prints column headings (labels) before a result set; the '-T' option prints the column types (after the headings, before the results). The '-B' option runs in benchmark mode; it turns on trace, prints the statement, the time when the statement started, and times how long it took. (Knowing when the statement started is helpful if the SQL takes many minutes to run - as it can in benchmarking scenarios). There are controls over the output format (including CSV and even variant of XML - but not an XML using namespaces) and date format, and so on. There are 'built-in' commands to redirect input and output and errors; most command line options can also be used in the interpeter, etc. SQLCMD also provides a history mechanism; it saves SQL statements and you can view, edit or rerun them. In conjunction with output redirection, you can save off a list of statements executed, etc.

The only gotcha with SQLCMD is that it is not currently ported to Windows. It did work on Windows once upon about 6 or 7 years ago. Since then, Microsoft's compilers have gotten antsy about non-MS API functions, insisting that even if I ask for them by name (by requesting POSIX functionality), the functions must be prefixed by an underscore, and by deprecating a bunch of functions that can be used safely if you pay attention to what you are doing (but, regrettably, can be abused by those who are not paying attention, and there are more inattentive than attentive coders around, it seems) - I mean functions like 'strcpy()' which can be used perfectly safely if you know the size of the source and destination strings before you call it. It is on my list of things to do - it just hasn't been done yet because it isn't my itch.

There is also another Open Source tool called SQSL that you can consider. It has some advantages over SQLCMD (conditional logic, etc) but I think SQLCMD has some advantages over SQSL.

You could also consider whether Perl + DBI + DBD::Informix + dbish would work for you.