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?
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.
