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