Transcript SQLCMD – A Better DBACCESS?

SQLCMD – A Better DBACCESS?
Utility by Jonathan Leffler
Presented by Joe Kennedy
([email protected])
(301-803-1974)
Sqlcmd as a SQL Command
Interpreter
DDL
●
DML
●
Select, Execute Procedure
●
What is it?
SQLCMD is an SQL Command Interpreter for Informix
databases. It is analogous to the Query-Language option
of DB-Access or ISQL, but it is designed to work better in
shell scripts.
SQLCMD is written in ESQL/C. It works with any version
of ESQL/C from 5.00 (circa 1990) upwards.
SQLCMD has a history mechanism that allows you to
review, edit and rerun commands you ran previously.
Alternative Output Formats
●
●
●
●
●
Quote
CSV
XML
Fixed
Unload (default)
-F from the command line!
Load & Unload Statements
●
Same Syntax as in DBACCESS or ISQL
●
No transaction management in load (add your
own begin work/commit statements)
Reload Statement
●
Syntactically similar to load statement
automatically initiates transactions, groups the
inserts, groups the inserts into smaller
transactions if there are many rows to be loaded
Info system
INFO DATABASES;
stores_demo@ids_joe
sysmaster@ids_joe
sysuser@ids_joe
sysutils@ids_joe
INFO CONNECTIONS;
...
INFO TABLES;
...
SQLunload
●
●
Extract data from the database and produces
ASCII output suitable for dbload or LOAD
command.
The database (-d) and table (-t) are always
specified on the command line.
Sqlreload
●
●
●
The inverse of Sqlunload
“Passing resemblance” to the Informix dbload
utlity.
Database (-d) and Table (-t) are always designated
at the command line using flags.
I/O Redirection
Unix/Linux redirection applies
echo “select * from customer” | sqlcmd -d
stores_demo -F XML
Contexts
●
●
●
●
●
●
●
●
●
●
●
●
Level:
1
/dev/stdin
Output: /dev/stdout
Date:
mm/dd/yyyy
Escape: '\\'
EOR: '\n'
Query limit:
0
Input Base: 0
XML Record Tag: RECORD
<BOS>
Heading: <TOS> off <BOS>
Continue: <TOS> off <BOS>
Trace:
<TOS> off <BOS>
Verbosity: <TOS> off <BOS>
Input:
Error:
/dev/stderr
Delimiter: '|'
Quote: '\"'
History size: 50
Transaction size: 1024
BlobDir: /tmp
Format:
<TOS> select
History: <TOS> off <BOS>
Silence: <TOS> off <BOS>
Types:
<TOS> off <BOS>
Benchmark: <TOS> off <BOS>
Command line options
Try “sqlcmd -help”
Basic flags:
-d database
-f FILE
-e 'SQL Statements'
How do I get the Source Code?
The International Informix User Group!
www.iiug.org – check the software repository...
distributed as a gzipped tar file with the extension
".tgz"
Compilation....
SQLCMD is written in ESQL/C, you must have
ESQL/C or ClientSDK on your machine before
installing SQLCMD.
On Unix systems (including Linux), the normal
sequence of events is:
./configure
LD_LIBRARY_PATH
make
make install