Informix: How to get the table contents and column names using dbaccess?

Question

Supposing I have:

• an Informix database named "my_database"
• a table named "my_table" with the columns "col_1", "col_2" and "col_3":

I can extract the contents of the table by creating a my_table.sql script like:

unload to "my_table.txt"
select * from my_table;

and invoking dbaccess from the command line:

dbaccess my_database my_table.sql

This will produce the my_table.txt file with contents like:

value_a1|value_a2|value_a3
value_b1|value_b2|value_b3

Now, what do I have to do if I want to obtain the column names in the my_table.txt? Like:

col_1|col_2|col_3
value_a1|value_a2|value_a3
value_b1|value_b2|value_b3

Solution 1

None of the standard Informix tools put the column names at the top of the output as you want.

The program SQLCMD (not the Microsoft newcomer - the original one, available from the IIUG Software Archive) has the ability to do that; use the -H option for the column headings (and -T to get the column types).

sqlcmd -U -d my_database -t my_table -HT -o my_table.txt
sqlunload -d my_database -t my_table -HT -o my_table.txt

SQLCMD also can do CSV output if that's what you need (but — bug — it doesn't format the column names or column types lines correctly).

Solution 2

Why you don't use dbschema?

To get schema of one table (without -t parameter show all database)

dbschema -d [DBName] -t [DBTable] > file.sql

To get schema of one stored procedure

dbschema -d [DBName] -f [SPName] > file.sql