ipxsyb This program is used to output from the Statistics Database. Either it can poll one or more tables in the database for entries left by a Front End like PowerBuilder 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. A parameter file for Sybasy sort of things is held in tables/stats and defaults to SYBOUT. Keywords for SYBOUT parameter file are : ; comment line user: sybase user name user:sa passwd: Sybase passwd passwd:sports server: Sybase server name server:JERRYSYB sep: End OF Record separator if not a pipe '|' sep:\177 field-sep: Field separator (defaults to nothing) field-sep:| no-field-sep: no-sep: Use these to have NO separators for record or field. 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:soccer table:TOBEOUTPUT fields:name,d1,d2,c1,c2,i1,i2,(createtime) where 'soccer' is the database name 'TOBEOUTPUT' is the name of the table to poll the fields in order are called 'name,d1,d2,c1,c2,i1,i2,createtime' The key of fields 1 and 8 is needed so that each row can be deleted uniquely when finished. (Note if the entry is deleted in stored procedure, specify 'no-poll-delete:yes' The table should have a unique key of name and createtime. Remember case if that is important. no-poll-delete:yes see above default: deleted How does it Work ? ------------------ 1. Polling ---------- For each 'poll' line in the parameter file, an SQL call is generated in the form : use soccer select name, d1, d2, c1, c2, i1, i2 from TOBEOUTPUT This SQL is run every few seconds and for each row of data, the program runs a Sybase Stored Procedure based on the contents of the first field : fip_output_(name) (all lowercase) with the other 6 parameters There are always 6 parameters in strict order - 2 dates, 2 chars and 2 ints - which can be nothings. EG : Say the result of the select gave us : name 'result' d1 '24-2-1995' d2 nothing c1 'PREM' c2 nothing i1 5 i2 nothing gives use soccer exec fip_output_results "24-2-95", "", "PREM", "", 5, 0 It is up to the stored procedure to do whatever with these parameters - if anything. 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 Sybase Stored Procedure Y1 is the first date field if specified Y2 is the second date field if specified Y3 is the first charstring field if specified Y4 is the second charstring field if specified Y5 is the first number field if specified Y6 is the second number field if specified YH is the hostname of this server If the table does not have the 2 dates, 2 ints, 2chrs - which the vast majority do NOT - you can use "" as padding : ; Poll ADBASE database for new quark entry poll:adbase table:ms_mp_fipout fields:fip_id,pubdate,"",id,"",adinsert,ad,id The key to the table MUST be the 8th field - it can be a duplicate of another firld. 2. Scrolling a queue - Normal Fip Style --------------------------------------- If the input switch '-i' is set, then this queue is polled too. Any file found is pulled apart for FipHdr fields. where SN is the actual filename as normal YD is the database YT is the table to scroll (ignored) YN is the name of the stored procedure to use ie if YN is 'bets', stored procedure 'fip_output_bets' will be run. Y1 is the first date field if required Y2 is the second date field if required Y3 is the first charstring field if required Y4 is the second charstring field if required Y5 is the first number field if required Y6 is the second number field if required Again Y1-Y6 may be ignored if not required. However the stored procedure MUST always accept them even if not used. Again Remember case if that is important - SOCCER is different to soccer. 3. 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. This switch allows up to 26 parameters to be specified - EA to EZ - these are ALL char or VARCHAR. where SN is the actual filename as normal YD is the database YZ is the maximum number of parameters - in E? fields Default is none. The stored procedure MUST always accept all of them even if not used. YN is the name of the stored procedure to use in this case this is the actual name - no extra bits are added. ie if YN is 'bets', stored procedure 'bets' will be run. YU is the (optional) destination to send to. This becomes the DU of the output file and should be in tables/sys/USERS. Again Remember case if that is important - 'SOCCER' is different to 'soccer'. Use the '-E' input switch to NOT prefic 'exec ' before the stored procedure name when you want to run pure SQL. In this case the filename (SN) and destination (DU) are forced to defaults if there is no 'outname' parameter and 'YU' FipHdr field. On output the YE FipHdr field contains the return code from the stored procedure (not WINNT). 4. 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 ... Input Parameters are (all optional) : -i : input queue - Fip Style Stored Procedures default: none -I : input queue - for any old procedures default: none Only one : -i or -I can be specified. If this does NOT start with a '/', it is assumed under spool. -E : do NOT add 'exec ' before name of stored procedure. default: do Only for use with the -I flag -o : output or done queue default: file is deleted If this does NOT start with a '/', it is assumed under spool. -z : parameter file in tables/setup default: tables/stats/SYBASE -w : file wait for files arriving across a network. def: 0 secs -l : do NOT log files in default: log -v : print version number and exit Version Control ;007m 18mar99 filename better plus 'no-sep' and 'no-field-sep' ;a 21apr99 added script ;b 11may99 added pollsep ;c 18may99 addedconn/discon msgs ;d 1jul99 better connection tracking ;e 30jul99 woops - bug in poll and seps ;f/g 28feb01 cleanup plus strparse of sql ;h-i 18aug03 added no-poll-delete ;j 26oct04 added YH with hostname ;k 04jan05 freetds version ;l 10nov06 passwd in FipSeq ;006 5jun98 added TEXT and BINARY !! ;a 11jun98 added sep at end of TEXT/BIN ;b/c 23jun98 minor mod - add prams to log message ;d 10jul98 dbdatecrack is 0-11 (syb) and 1-12 (mssql) !! (copyright) 2017 and previous years FingerPost Ltd.