ip2syb

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
...

NOTES..............
note.1 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

note.2 If using TDS and a different port or you need to define the tds version,
please change setup/freetds.conf

[global]
        tds version = 8.0
# uncomment this for debugging
##      dump file = tdsdumpXsetup.fip
        debug level = 99
        client charset = UTF-8
    port = 1600

........................

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
;3v10    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-9 if zone starts or ends with a QuoteChr (" for Syb/MSsql and ' for TDS)
space pad
    ;10 17may19 cleanups for WINNT freetds_0.95

(copyright) 2024 and previous years FingerPost Ltd.