SQLEXP: INGRES SQL-driven data export utility

Download sqlexp.bin (1251k) and save as sqlexp. (Executable on Digital Unix)

1. Descriptions

SQLEXP is the program that extracts the data from INGRES database using INGRES SQL "SELECT" statement. It reads the select statement from unix standard input and prints the results to unix standard output in either tab, comma or user defined character delimited text file format.

The features include:

2. Calling convention

The syntax of sqlexp is as follow: 
sqlexp <dbname> [-u<user>] [-t|-f<char>] [-m<num>] [-G<group>]
       [-show_null] [-v] [-i<fmt>]

    <dbname>    INGRES database name
    -u<user>    INGRES user id
    -G<group>   INGRES group id
    -t          tab as output field seperator
    -f<char>    character as output field seperator
    -m<num>     maximum number of rows to be returned
    -show_null  show null value as "#NULL#"
    -v          verbose mode
    -i<fmt>     Floating point display format(fkxM.N)

<Note>

1. sql|quel option will work only when the value of II_PATTERN_MATCH is 'sql' wh
ich is default.
2. fkxM.N format will be taken effect only when the explicit type converion func
tion is specified for the floating point data type in SQL statement, otherwise n
o effect. The default is -f8g10.3.
3. Examples

3.1 Create comma delimited text file from INGRES table 

$ sqlexp rubisdb << ! > rubis.dat
select style, first_name, last_name from rubis_committee;
!
$ cat rubis.dat
Monsieur,Dominique,CENTENO
Monsieur,Hervé,DuBois
Monsieur,Georges,DUMOULIN
Monsieur,Eric,EGGEN
Monsieur,Hubert,FREY
Monsieur,Akram,HAJJAOUI
Madame,Monique,LONG
Monsieur,Daniel,PETITHUGUENIN
Monsieur,Michel,ROCH
Monsieur,Michel,WALTER
Monsieur,Wolfgang,WOHLLEBER
3.2 Simple report example using awk text formating utility 
$ cat rubis.sh                   
#!/usr/bin/ksh
#
sqlexp -t rubisdb << ! |
select
        style, first_name, last_name,
        company, department, street, post_box,
        zip_code, city, phone, fax
from rubis_com
order by last_name;
!
nawk '
BEGIN { FS = "\t"}
{
        printf "%s %s, %s\n", $1, $3, $2
        if ($5 != "") print $5
        if ($4 != "") print $4
        if ($6 != "") print $6
        if ($7 != "") print $7
        tmp = $8 $9
        if (tmp != "") print tmp
        if ($10 != "")
                print "TEL: " $10
        if ($11 != "")
                print "FAX: " $11
        print ""
}'

$ ./rubis.sh                                                

Monsieur CENTENO, Dominique
Institut de Pathologie
25, rue du Bugnon
1011LAUSANNE
TEL: 021/314.71.17
FAX: 021/652.08.80

Monsieur DUMOULIN, Georges
QUOD S.A.
Grand-Rue, 4
1095LUTRY
TEL: 021/792.12.83
FAX: 021/792.12.90

Monsieur DuBois, Hervé
IPB - NCS
29-31, rte de l'Aéroport
Case postale 599
1215GENEVE  15
TEL: 022/929.83.56
FAX: 022/929.83.83

Monsieur EGGEN, Eric
Loterie Suisse Romande
15, rue du Marterey
1005LAUSANNE
TEL: 021/311.45.55
FAX: 021/320.00.25

Monsieur FREY, Hubert
Computer Associates S.A.
Avenue Reverdil, 2
1260NYON
TEL: 022/362.26.10
FAX: 022/362.26.51

Monsieur WALTER, Michel
Service des Bâtiments
DTPAT
10, place de la Riponne
1014LAUSANNE
TEL: 021/316.73.02
FAX: 021/316.73.47

Monsieur WOHLLEBER, Wolfgang
UIT
Place des Nations
1211GENEVE  20
TEL: 022/730.58.97
FAX: 022/730.53.37
3.3 More complex report example using lout text formating utility 
$cat rubis.sh
#!/usr/bin/ksh
#
sqlexp -t rubis << ! |
select
        style, first_name, last_name,
        company, department, street, post_box,
        zip_code, city, phone, fax, remarks, ' '
from rubis_committee
order by last_name;
!
nawk '
BEGIN {
        FS = "\t"
        print "@SysInclude{tab}"
        print "@SysInclude{doc}"
        print "@Doc @Text @Begin"
        print "@Display @Heading {+4p @Font {"
        print "@ShadowBox {List of RUBIS Committee member} } }"
        print "@DP"
        print "@Tab"
        print "hmargin {0.3c}"
        print "vmargin {0.2v}"
        print "above {single}"
        print "below {single}"
        print "side {single}"
        print "@Fmta{"
        print " @Col 7c @Wide {Bold -2p} @Font A !!"
        print " @Col 7c @Wide {Bold -2p} @Font B !!"
        print " @Col 7c @Wide {Bold -2p} @Font C"
        print " }"
        print "@Fmtb{"
        print " @Col 7c @Wide {-2p} @Font A !!"
        print " @Col 7c @Wide {-2p} @Font B !!"
        print " @Col 7c @Wide {-2p} @Font C"
        print " }"
        print "{"

        print "@Rowa vmargin {0.5vx} A {Name} B{Address} C{Remarks}"
}
{
        gsub("/", "\"/\"")

        printf "@Rowb\n"
        printf " A{ lines @Break {%s\n%s, %s} }\n", $1, $2, $3
        printf " B{ lines @Break {"
        if ($5 != "")
                printf "%s\n", $5
        if ($4 != "")
                printf "%s\n", $4
        if ($6 != "")
                printf "%s\n", $6
        if ($7 != "")
                printf "%s\n", $7
        if ($8 != "")
                printf "CH-%s ", $8
        if ($9 != "")
                printf "%s\n", $9
        if ($10 != "")
                printf "TEL: %s\n", $10
        if ($11 != "")
                printf "FAX: %s\n", $11
        printf "} }\n"

        if ($12 != "")
                printf " C{ @Break {%s} }\n", $12
}
END {
        print "}"
        print "@End @Text"
}' | lout -s | lpr
4. Revision History 
October 1993/v1.0
     - Written from scratch on INGRES 6.4, DEC ULTRIX 4.1


August 1994/v1.0
     - Ported to OSF/1 v2.0, Dec Alpha AXP 3000

March 1995/v1.1
     - Bug fix:    Unalignment warning message
     - New option: dos style pattern match

May 1995/v1.2
     - Bug fix:    incorrect literal value handling

October 1995/v2.0
     - Rewritten completely in order to support WEB application
     - Old options are still valid
     - New options:
       . Verbose mode on/off for interactive/batch run
       . User defined field delimiter character support
       . INGRES Group id support
       . NULL value display
       . In-line comments support(begining with "#")