ip2syb
This program reads data from incoming files and updates a MicroSoft SqlServer
or Sybase database.
Often this is a Fip Stat database design like FINO for Financial/Stox data or
one of the Sports databases like SOCCER.
A parameter file for the data is held in tables/stat and defaults to SYBASE.
Data is in the format of a single record per line (ends with a CR and/or LF) :
field1 (sep) field2 (sep) field3 (sep) etc
where sep is normally a pipe '|' but can be changed using the 'sep:' keyword.
eg :
SOCCER|GOAL|24-2-95|LIVE|MANU|5|0|Ian Rush|58
It is important the data is correct as ip2syb makes no attempt to parse except
for some minor character changes and forcing dbl quotes '"'.
The program will then run a SqlSvr/Sybase Stored Procedure called :
fip_dbase_table
(all lowercase) with all the fields as parameters. The example above would run
the following :
fip_soccer_goal "24-2-95", "LIVE", "MANU", 5, 0, "Ian Rush", 58
IP2SYB needs to know how many fields there are and what their types are as
CHAR/VARCHAR is handled different to all DATES which are handled differently to
INTS and other numbers.
In the parameter table, a line describing is :
table:FIXTURES date,char,char,int,int,char,int
If there are too many fields in the data, they are chucked away; too few and
NULL fields or ZEROS added; but Sybase insists on the correct number of fields
or the Stored procedure will fail.
Keywords for SYBASE parameter file are :
; comment line
user: sybase user name
user:sa
passwd: Sybase passwd
passwd:sports
server: Sybase server name
server:JERRYSYB
sep: Field separator if not a pipe '|'
sep:177
comment: Single chr sigifying a comment line in the data file
comment:%
table: Layout of an input record for a particular table.
table: (name) list of field types in order
eg:
table:FIXTURES char,char,date,int,int,char,date,int
for table FIXTURES (case sensitive if Sybase is)
the parameters are 2 character fields followed
by a data, 2 numbers, a char, a date and a number.
If there are more input fields than you have parameters,
the excess are ignored.
If there are too few, then blank or zero fields are added.
sp-replace: the stored procedure name if fixed
the default is 'fip_(name of database)_' where database is the first field of
data
sp-prefix: Prefix for the stored procedure
the default is 'fip_(name of database)_' where database is the first field of
data
sp-suffix: Suffix for the stored procedure
default nothing
chrmap:(old chr) (new Chr)
Replace single chrs with others. Use this to convert funnies to NewLines.
chrmap:34 n
log-line:FipSeq Log line to replace the Done message
log-line:ES ER TP TH
ignore-data: Ignore the data part of the file. default: use ONLY data part
This implies a 'before-file' and/or 'after-file'
before-file: (Path and Filename in FipSeq)
after-file: (Path and Filename in FipSeq)
File containing extra lines to insert in the same fashion as datalines.
Use FipSeq to check for different stored procedures
eg is the USERS file :
db-log DP:localhost DQ:2sybase SC:no DC:no JP:SP_LOG
db-error DP:localhost DQ:2sybase SC:no DC:no JP:SP_SHORT_ERROR
then in the SYBASE file
ignore-data:
before-file:/fip/tables/stat/JP.SP
and have two extra files in /fip/tables/stat
SP_LOG.SP
and SP_SHORT_ERROR.SP
which contain lines like :
FINO|LOG|$d-$m-$e$y|Warning|JE|JMn
dump-data: This makes a copy in /fip/dump of all sql calls and results
delete-chg-db-errors:yes/no
If YES any failures to change to the chosen database mean the file is
considered bad and is deleted.
Use NO to NOT delete.
convert-to-utf8:(FipHdr Field)
Convert any data to UTF8 unless this FipHdr field contains UTF8
eg convert-to-utf8:T6
force-database: (name)
use this database name
default first field
force-table: (name)
use this table
default second field
data-has-database: yes/no
flag if the data does NOT start with the DATABASE name
default is yes
data-has-sp: yes/no
flag if the data does NOT start with the Stored Procedure name
default is yes
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
Examples :
table:FIXTURES char,char,date,int,int,char,date,int
...
If you do NOT want to use an SP - say you have an insert ...:
force-database:sports
force-table:TSTATS
data-has-database:no
data-has-sp:no
sp-prefix:insert into Tstats (f1, f2, f3, f4, f5) values (
sp-suffix:)
table:TSTATS char,char,date,int,int
Input Parameters are (all optional) :
-i : input queue default: spool/2sybase
If this does NOT start with a '/', it is assumed under spool.
-o : done queue default: file is deleted
If this does NOT start with a '/', it is assumed under spool.
-c : Set the DBANSItoOEM and DBOEMtoANSI flags for MSQLsvr
-C : Clear the DBANSItoOEM and DBOEMtoANSI flags for MSQLsvr
-e : error queue default: file is deleted
Only input file which return errors are placed in this folder.
If this does NOT start with a '/', it is assumed under spool.
-E : only save files that errored in stataudit. default: save all files
-z : parameter file in tables/setup default: tables/stat/SYBASE
-s : do NOT log the start of a file default: log start and end
-S : logoff from the database after every file default: logon and leave on
-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
Note you are NOT allowed to write to the "master" database.
Version Control
;003v9 7sep97 sqlsvr as default NT ;a 1jun98 minor mods
;b 10jul98 dbdatecrack is 0-11 (syb) and 1-12 (mssql) !!
;c 19sep98 check we are still connected pls
;d 18jun99 added Conn/Discon log messages
;e/f 31aug01 added sp-prefix, log-line, errorq and chrmap
;g/h/j 10sep01 added ignore-data + before-file; and after-file:
;k/l 17oct01 added -c/-C for MsSqlSvr language option
;m 20may03 added dump-data
;n-q 27may03 better tracking of chg-db errors
;r-s 10dec04 freeTDS version
;t-u 10nov06 passwd as FipSeq ie passwd:JW
;v1-3 18jan08 added sp-suffix data-has-database data-has-sp force-database
;4-6 bad utf8 chrs
;7 woops VARCHAR type 'n' missing from "table"
;8 if zone starts or ends with a QuoteChr (" for Syb/MSsql and ' for TDS)
space pad
(copyright) 2014 and previous years FingerPost Ltd.