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) 2014 and previous years FingerPost Ltd.