Getting a syntax error using Informix dbaccess

ibminformix

I'm getting this syntax error in IBM Informix using the dbaccess utility:

root@guava:/opt/informix# bin/dbaccess - -
Your evaluation license will expire on 2015-12-22 00:00:00
> show databases;

  201: A syntax error has occurred.
Error in line 1
Near character position 1

Any suggestions?

Best Answer

You get the 'syntax error' because SHOW DATABASES is not a valid command in DB-Access. In fact, SHOW is not a valid keyword in either DB-Access or the underlying DBMS.

If anything was going to work, it would be INFO DATABASES; however, that is not actually supported in DB-Access (but it is in my SQLCMD program, which I use in preference to DB-Access, and have used since I first wrote it back in 1987).

There are other INFO commands to list tables, columns, etc.

INFO TABLES;                 -- List of user-defined tables and views
INFO COLUMNS FOR systables;  -- Columns for a specific table
INFO INDEXES FOR systables;  -- Indexes on a specific table

These commands are interpreted by DB-Access and not by the Informix database server, and translate into queries against the system catalog of the current database. The list of databases is, therefore, somewhat different because the information is not a part of the system catalog of the current database.

The list of databases is available from a table in the sysmaster database:

SELECT * FROM SysMaster:informix.sysdatabases;

Example output from one Informix server:

…

name            mode_ansi
partnum         1048920
owner           jleffler
created         2014-04-30
is_logging      1
is_buff_log     0
is_ansi         1
is_nls          0
is_case_insens  0
flags           -12283

name            utf8
partnum         1048988
owner           jleffler
created         2014-04-30
is_logging      1
is_buff_log     1
is_ansi         0
is_nls          0
is_case_insens  0
flags           -12285

…

Alternatively, if you run DB-Access in the curses-mode (either dbaccess or dbaccess dbname), then there is a menu option Databases which leads to a sub-menu that allows you to list, select, create, and drop databases.

Related Topic