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