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:\034 \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|\JM\n 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. -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 -l : do NOT log files in default: log errors -L : log all files default: log -o : done queue default: file is deleted If this does NOT start with a '/', it is assumed under spool. -S : logoff from the database after every file default: logon and leave on -w : file wait for files arriving across a network. default: 0 secs -X : do NOT log the start of an upload default: log start and end -z : parameter file in tables/stat default: SYBASE -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) 2017 and previous years FingerPost Ltd.