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