Examples in this article were produced using the provided “stores_demo” database in a Docker container from ibmcom/informix-developer-database.
If you enter “dbaccess” alone, the following menu is displayed which is designed for use on a traditional 24×80 terminal by default:
You can start a menu-driven SQL session by pressing Q for Query-language and choosing the database:
Press N for new, enter your SQL, press Esc when complete, then R to run the SQL:
If the output will not fit across within 80 characters, it reformats with columns on separate lines:
The same applies when running directly in CLI mode (database and hyphen supplied):
That used to be very annoying when using a larger terminal window or in a script when saving a wider document.
From version 12.10.xC9, the fix is to set undocumented environment variable DBACCESS_COLUMNS:
Command Line History and Editing
When using DB-Access in a direct CLI session (not via the menu), you can enable similar facilities to the Linux Bash shell or Windows command lines, i.e. being able to press:
- up and down arrow keys to scroll through previous commands;
- left and right arrows to position within a command;
- backspace or delete to remove parts of a command;
- characters to be inserted at the current position.
This is achieved via the Linux “rlwrap” tool. If not present:
Note that this is already built into the standard DB-Access on Windows.
You can then run a CLI DB-Access session as follows, demonstrating that a command could be corrected and rerun without having to retype it, and that the history persists between sessions:
Alias to enhance dbaccess
You can go further and create an alias for “dbaccess” to apply the CLI enhancements automatically. I have this line in my “.bashrc” file which is then applied whenever a new shell starts:
The variable COLUMNS is maintained by Bash and some other Unix shells, containing the current width of the terminal window. We can use that to set DBACCESS_COLUMNS appropriately when starting “dbaccess”. However, if you change the terminal window width mid-session, you would need to exit and restart “dbaccess” for it to be updated.
Environment variables can be set in-line with name=value pairs before a shell command. The alias then has “rlwrap” in front of “dbaccess” for command editing and history as previously described.
With that in place, when you run “dbaccess”, you are actually invoking the “alias”, so those two enhancements will be enabled automatically.
When running a script containing multiple SQL statements separated by semi-colons, should an error be encountered, the script is:
- aborted when run in the DB-Access menu;
- continues when run as an automated script by default.
Firstly, notice two things in the shell command above:
- The “-e” option echoes commands as they are run.
- A script can be automated when the database and file name are supplied.
Should you need an automated script to abort on any error, set DBACCNOIGN=1 (“DB-Access no ignoring of errors”), which is very long-standing and documented here. That changes the behaviour as follows: