ipora

ipora, ipmysql and ipodbc

This program is used to input and output to and from any Oracle, Mysql or ODBC
Database like the Fip Statistics module

Either it can poll one or more tables in the database for entries left by a
Front End like PowerBuilder or Perl DBI/DBD or PHP

And/Or scavenges a spooled queue for external trigger files which can be from
crontab or a wire file or the like.

When it finds a item, it runs the stored procedure of that name and dumps the
result to a file which is then spun off to spool/2go for further processing.

An optional audit of the sql and optionally-optionally the data retreived is
normally stuff in files in spool/stataudit using the -a and -A input switches.

A parameter file for DataBasy sort of things is held in table/stats and
defaults to ORACLE, MYSQL or ODBC.

Keywords for the parameter file are :
    ; comment line
    user: database user name
        user:sa
    passwd: database passwd
        passwd:sports
    server: database server name (for ODBC this is the DSN)
        server:JERRYORA
    database: name of database  (Mysql only)
        database:fino
    sep:    database data - End OF Record separator if not a pipe '|'
        sep:\177
    field-sep:  Output data - Field separator (defaults to nothing)
        field-sep:|
    no-field-sep:
    no-sep: Output data - Use these to have NO separators for record or field.
    input-sep: In the input data file, the separator between fields. default is a
pipe '|'
        eg for a csv - SOCCER,FIPLOG,D3,Start of Season,LeagueTables,hsa2199
            input-sep:,
    comment: In the input data, lines starting with this chr are ignored. default:
';'
    encode-sep: yes/no
        Should the database field sep chr (normally a single quote), be encoded if
found in data
        default is no unless -e switch has been specified
        (ie use ipxchg to make sure the data is correct beforehand)
    fiphdr: extra FipHdr information to preserve
        remember to use a hash as a Hdr field seperator
        fiphdr:#JN:\SN#HH:\$d#JK:Pls Save - Testing
    newname: FipSeq for the filename of the output file.
    copy-data-before:
    copy-data-after:
        Normally the data part of the input file is ignored
        Use one or the other of these to slot the existing data either
            before or after any data from the database.
    poll:   Database and table to poll (If any - this is optional)
        Generally there is just one entry :
        poll:TOBEOUTPUT
            where 'TOBEOUTPUT' is the name of the table to poll
            This requires a stored procedure called 'fip_poll' to be loaded.
        Remember case if that is important.
    key-field: field in the incoming data which is used for the filename in
tables/stat/sql
        default is field 2 as 'FIPLOG' in
            SOCCER|FIPLOG|D3|Start of Season|LeagueTables|hsa2199
    minimum-fields: Number of fields below which data lines will be ignored
        no default

For the -W - wire2 uploader only
--------------------------------

    either  w2-account: Logons for Wire2
        w2-logon:
        w2-password:
    or  w2-telex-logon: This is the FipHdr fields (or Combie) for the AB

        w2-device:  Logon device for either logon

        w2-before:  String to add before the data of the file
        w2-after:   String to add after  the data of the file

        The following are in FipSeq so we can use FipHdr fields..

        w2-address  Wire2 address field
        w2-mime-type:   Default is 'TEXT'
        w2-soh-date:    Start of Header date/time
        w2-stx-date:    Start of Text   date/time
        w2-etx-date:    End of Trans    date/time
            dates are in format dd-mmm-yyyy hh:mm:ss
        w2-term-code:   Termination code    'M'mmm, 'L'llll, '+'+++

How does it Work ?
------------------

1. Polling
----------

For each 'poll' line in the parameter file, an SQL call is generated in the
form :

This SQL is run every few seconds and for each row of data, the program runs
the sql in a stat/sql file called OUTPUT-SELECT
There are usually 6 parameters in order - 2 dates, 2 chars and 2 ints - which
can be nothings.
EG :
    select name, d1, d2, c1, c2, i1, i2, InsertKey from TOBEOUTPUT where rownum =
1 order by InsertKey

    This select might us a row like of :
        EA  'result'
        EB  '24-feb-1995'
        EC  (nothing)
        ED  'PREM'
        EE  (nothing)
        EF  5
        EG  (nothing)
        EH  '911828282'

(nothing being an empty FipHdr field in this case.)

It then runs the sql in file stat/sql/(Contents of EA forced uppercase) -
RESULTS in the above example

The resultant file has certain FipHdr fields already filled in :

#SN:soco.0#YD:SOCCER#YT:TOBEOUTPUT#YN:soco#SC:ascii#HS:96-2-22_19:55:35_4_52#DU:stat_soco
    where   SN is the actual filename as normal
        YD is the database
        YT is the table
        YN is the type of output (and stored procedure used)
        SC is the source chr set as normal
        HS is the data time stamp as normal
        DU is the destination and MUST be in tables/sys/USERS for correct onward
routing.
        YE is the result code from the Oracle Stored Procedure
        EA-E? are the results from the initial select

And finally deletes the entry in by running stat/sql/OUTPUT-DELETE
    delete TOBEOUTPUT where InsertKey = '\EH'

2. Scrolling a queue - Any old Style
---------------------------------------

If the input switch '-i' is set, then this queue is polled. Any file found is
pulled apart for FipHdr fields.

For each line
    SOCCER|FIPLOG|D3|Start of Season|LeagueTables|hsa2199

Fake Fiphdr fields are temporarily created with the fields
    EA=SOCCER
    EB=FIPLOG
    EC=D3
    ED=Start of Season
    EE=LeagueTables
    EF=hsa2199

Parameter file is FIPLOG
;
;   SOCCER - FIPLOG
; SOCCER|FIPLOG|(checkBox)|Url|Msg
;
replace:QD  ED  "'"="-"
replace:QE  EE  "'"="-"
replace:QF  EF  "'"="-"
;
sql:insert into STATUS
sql:    (ThsTime, Type, Idx, CKey, Remarks)
sql:    values (sysdate, 'FIPLOG', 0, '\EC', '\QD|\QE|\QF')

Take care to strip/duplicate any quote marks and that NON-NULL fields have some
data.

Example 2 ------------

There can be one or many lines like the following ....
    FINO|SIS|MID-PRICE|BP.|25-may-2004|2322.00|

The second field - 'SIS' in the above example - is used as the name of another
parameter file containing Sql in FipSeq describing how to insert/update/delete
the transaction.

This file is in /fip/tables/stat/sql.

Example
    combie:QC   EC,XX
    combie:QD   ED,DUD
    combie:QF   ED,0
    combie:QG   EG,-nothing-

    sql-new-line:use fino
    sql-new-line:insert into DAILY
    sql:    (Exch, Stox, Fld, LstUpdate, TranDate, Numo, Viso)
    sql:    values ('SIS', '\QD', '\QC',
    sql:        sysdate, to_date ('\EE', 'dd-mon-yyyy'),
    sql:        '\QF', '\QG')

    insert into DAILY
        (Exch, Stox, Fld, LstUpdate, TranDate, Numo, Viso)
        values ('SIS', 'BP.', 'MID-PRICE',
            sysdate, to_date ('\EE', '25-may-2004'),
            '2322.00', '-nothing-')

The syntax of the 'sql' keyword is
    If there is NO space after the colon, it is a new sql command.
    If there is a space, it is a continuation line.

Use Fipseq, combie, replace etc to make sure the data both Exists for Non-Null
fields, does NOT contain separators like apostrophes and to use default values.

The 'file-on-error' keyword means - if you get this error number, try the
transaction using Sql fromthis file. generally there are two files for each
transaction - an Insert and an Update.

    insert  file-on-error:1062  SIS-UPDATE

    update  file-on-error:1403  SIS-INSERT

Note that for Mysql, an Update which updates no rows gives an error of -28163

    send-output:(FipSeq filename)
        where ipora/ipmysql is being used to EXTRACT data, add this parameter in the
SQL file.

3. FipHdr/FipSeq
----------------

Where sections of FipHdr fields are required or changes to the output style,
use keywords : fixed, partial, combie, optional, repeat, newdate and/or style.
(see The SysAdmin manual for more information).

    They are normally specified :
        fixed:QZ    1234543
        partial:QT  ST,3,2,U,<,>
        combie:QY   ep|na,(0000000)a
        option:QE   ep,11,7,s
        repeat:QK   XK,-,3
    or  repeat:QP   PK,,4,#X
        style:QS    XN,%.03d
        newdate
...

Input Parameters are (all optional) :
    -8 : run output Speedy but input NOT speedy default: see speedy
    -9 : do not run in Speedy mode          default: see speedy
    -a : Create audit file in spool/stataudit   default: no
        This file, one per incoming file, will show the actual SQL and
        any result codes and messages if it went wrong.
    -A : as -a but also show all the data retrieved. default: no
    -e : encode any quotes in data          default: no (for some reason?)
    -i : input queue        default: spool/2oracle or spool/2mysql
        If this does NOT start with a '/', it is assumed under spool.
    -w : file wait for files arriving across a network. def: 0 secs
    -W : run as Wire2 input loader          default: no
    -S : Connect and disconnect for each file   default: no
        Normally the program connects on the first file
        and leaves the connection up.
    -l : do NOT log files in            default: log
    -Q : quiet mode                 default: log all errors
        -Q 10 - do NOT log missing parameter files in stat/sql
        -Q 20 - ditto plus do NOT log database errors and connects
    -v : print version number and exit

Version Control
;004d9  04aug06 added ODBC
    ;b 27apr07 added filesend and speedy
    ;c 06aug07 buglette mysql chg_db  and **Chg default -o is nothing
    ;d1 12nov07 buglette mysql - on error wiping out errnos
        ;3 -e sep in data needs to be duplicated
        ;4 27mar08 added -8 and -9
        ;5-6 1feb09 added key-field and comment-start and minimum-fields
        ;7-8  6mar09 added log-line and script-after
        ;9 30apr14 added file-trace
;003d   13feb01 added MYSQL
    ;a/b 09mar01 cleanup
    ;c 12jul01 added check on zero length files
    ;d 07aug01 added check for Oracle Zombies
;002d   16oct00 added Wire2 package stuff..a/b cleanup
        ;c/d 08dec00 added w2-telex-logon
;001    02sep00 added error-sql: and cleanedup output and added STATAUDIT -a/-A
;000    27jul99 Chris from ipxsyb 07d ;d-15jan00 for FIPLOG

(copyright) 2017 and previous years FingerPost Ltd.