ipxsyb (Sat Oct 25 2014 01:31:01)

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