RE: ER and ats/ris Files
Madison
Thanks for this. Bit beyond my C skills to risk tonight, I will catch
up using sql comparisons, but this is certainly something I will
follow up with over the next few days, get it compiled and test.
Can't remember at the moment, is there something like this built into
9.x or 10.x?
Keith
-----Original Message-----
From: Madison Pruet [mailto:mpruet@comcast.net]
Sent: Monday, February 13, 2006 8:10 PM
To: informix-list@iiug.org
Subject: Re: ER and ats/ris Files
It's been quite some time since I used it....
--------------------------------------------------------------------
#include <stdio.h>
#include <stdarg.h>
typedef struct _colList {
struct _colList *next;
char *val;
int num;
} colList_td;
typedef struct _partdef {
char *server;
char *db;
char *tabowner;
char *tabname;
char *stmt;
colList_td *cols;
colList_td *keys;
} partdef_td;
typedef struct _repdef {
struct _repdef *next;
int repid;
char *repname;
partdef_td *source;
partdef_td *target;
} repdef_td;
#define x2b(x) (((x) <= '9' && (x) >= '0') ? (x) - '0' : toupper((x)) - 'A'
+ 10)
/* Flags used to parse the ATS/RIS file */
#define SourceFlag "TXH Source"
#define TargetFlag "TXH Target"
#define RRHFlag "RRH Row"
#define RRDFlag "RRD "
#define DbOpFlag "DbOp:"
#define ReplFlag "Replicate Id:"
static char buff[50000]; /* buffer for reading the ATS file */
/* current source/target information */
$char sourceServer[129];
$char targetServer[129];
$int sourceID;
$int targetID;
#define ERR(MSG) errorExit(__LINE__, fmtmsg MSG )
/*----------------------------------------------------------
* Function: trim
*
* Parameters: strParm IN - String to trim
*
* Purpose: trim trailing spaces/tabs from a tring
*
*---------------------------------------------------------
*/
static void
trim(char* strParm)
{
char *str;
for (str = strParm; *str; str++);
while (str > strParm && ((*(str-1) == ' ') || (*(str-1) == '\\t')))
{
*(--str) = '\\0';
}
return;
}
/*-------------------------------------------------
* Function: checkSql
*
* Parameters: msg, ... IN - a sprintf format list of parameters
*
* Purpose: Check the sqlcode value for the last SQL statement
*----------------------------------------------------------
*/
static void
checkSql(char* msg, ...)
{
va_list ap;
va_start(ap,msg);
if (sqlca.sqlcode < 0)
{
fprintf(stderr,"** ERROR ** sqlcode:%d isamcode:%d\\n", sqlca.sqlcode,
sqlca.sqlerrd[1]);
vfprintf(stderr, msg, ap);
fprintf(stderr,"\\n");
exit(-1);
}
va_end(ap);
}
/*--------------------------------------------------------
* Function: fmtmsg
*
* Parameters: msg, ... IN - a sprintf format list of parameters
*
* Returns: formated message to be returned
*
* Purpose: Format the message in as with sprintf and return the
* formated string
*-------------------------------------------------------
*/
static char*
fmtmsg(char* msg, ...)
{
static char msgbuff[800];
va_list ap;
va_start(ap, msg);
vsprintf(msgbuff,msg,ap);
va_end(ap);
return msgbuff;
}
/*----------------------------------------------------------
* Function: errorExit
*
* Parameters: lineno IN - line number where failure occured
* msg IN - message to print as part of the exit
*
* Purpose: Error exit with a message printed to stderr
*----------------------------------------------------------
*/
static void
errorExit(int lineno, char* msg)
{
fprintf(stderr,"ERROR at line %d\\n%s\\n", lineno, msg);
exit(-1);
}
/*------------------------------------------------------------
* Function: freeList
*
* Parameters: head IN - head of list to free
*
* Purpose: Free the list of buffers
*------------------------------------------------------------
*/
static void
freeList( colList_td *head)
{
colList_td *titem;
while ((titem = head) != NULL)
{
head = titem->next;
free(titem->val);
free(titem);
}
return;
}
/*----------------------------------------------------------------
* Function: getPrimaryKeys
*
* Parameters: tableNameParm IN - Name of table
* tableOwnerParm IN - Owner of table
* cols IN - List of columns in the select list
*
* Returns: colList_td List of columns making up the primary key
*
* Purpose: Obtain the primary key(s) for a given table
*-----------------------------------------------------------------
*/
static colList_td*
getPrimaryKeys(char *tableNameParm, char *tableOwnerParm, colList_td *cols)
{
$char *tableName = tableNameParm;
$char *tableOwner = tableOwnerParm;
$int tabid;
$int part[16];
$char colname[130];
$int colno;
$char stmt[500];
int tnum;
colList_td *projcol;
colList_td *tcol;
colList_td *tcolend = NULL;
colList_td *tcolstart = NULL;
/*
* Get the parts for the primary key index
*/
$select st.tabid,
si.part1, si.part2, si.part3, si.part4, si.part5, si.part6,
si.part7, si.part8, si.part9, si.part10, si.part11, si.part12,
si.part13, si.part14, si.part15, si.part16
into :tabid, :part[0], :part[1], :part[2], :part[3], :part[4], :part[5],
:part[6], :part[7], :part[8], :part[9], :part[10], :part[11],
:part[12], :part[13], :part[14], :part[15]
from systables st, sysconstraints sc, sysindexes si
where st.tabname = :tableName
and st.owner = :tableOwner
and sc.constrtype = 'P'
and sc.tabid = st.tabid
and si.idxname = sc.idxname;
checkSql("Fetch Index for %s.%s", tableOwner, tableName);
/*
* Get a cursor for the table to find the columns of the PK
*/
$declare c_col cursor for
select colname, colno
from syscolumns
where tabid = :tabid
order by colno; checkSql("declare cursor for syscolumns where tabid is %d", tabid);
$open c_col;
checkSql("open c_col using %d", tabid);
for (;;)
{
/*
* Get the next column
*/
$fetch c_col into :colname, :colno;
if (sqlca.sqlcode == 100)
break;
checkSql("fetch c_col");
/*
* Check to see if this column is one of the PK columns
*/
for (tnum = 0; tnum < 16; tnum++)
{
if (colno == part[tnum])
break;
}
/*
* Nope - then get the next column
*/
if (tnum >= 16)
continue;
trim(colname);
/*
* Find the column in the projection list
*/
for (projcol = cols; projcol; projcol = projcol->next)
{
if (!strcmp(projcol->val, colname))
break;
}
if (!projcol)
ERR(("Can not find key %s in projection list", colname));
if ((tcol = (colList_td
yes - 10 has this functionality.
It might not be as risky as you think. It's not directly updating the rows,
but converting the ats files into an sql script.
M.P.
"Simmons, Keith" <keith.simmons@office2office.biz> wrote in message
news:mailman.562.1139869291.740.informix-list@iiug.org...
Madison
Thanks for this. Bit beyond my C skills to risk tonight, I will catch
up using sql comparisons, but this is certainly something I will
follow up with over the next few days, get it compiled and test.
Can't remember at the moment, is there something like this built into
9.x or 10.x?
Keith
-----Original Message-----
From: Madison Pruet [mailto:mpruet@comcast.net]
Sent: Monday, February 13, 2006 8:10 PM
To: informix-list@iiug.org
Subject: Re: ER and ats/ris Files
It's been quite some time since I used it....
--------------------------------------------------------------------
#include <stdio.h>
#include <stdarg.h>
typedef struct _colList {
struct _colList *next;
char *val;
int num;
} colList_td;
typedef struct _partdef {
char *server;
char *db;
char *tabowner;
char *tabname;
char *stmt;
colList_td *cols;
colList_td *keys;
} partdef_td;
typedef struct _repdef {
struct _repdef *next;
int repid;
char *repname;
partdef_td *source;
partdef_td *target;
} repdef_td;
#define x2b(x) (((x) <= '9' && (x) >= '0') ? (x) - '0' : toupper((x)) - 'A'
+ 10)
/* Flags used to parse the ATS/RIS file */
#define SourceFlag "TXH Source"
#define TargetFlag "TXH Target"
#define RRHFlag "RRH Row"
#define RRDFlag "RRD "
#define DbOpFlag "DbOp:"
#define ReplFlag "Replicate Id:"
static char buff[50000]; /* buffer for reading the ATS file */
/* current source/target information */
$char sourceServer[129];
$char targetServer[129];
$int sourceID;
$int targetID;
#define ERR(MSG) errorExit(__LINE__, fmtmsg MSG )
/*----------------------------------------------------------
* Function: trim
*
* Parameters: strParm IN - String to trim
*
* Purpose: trim trailing spaces/tabs from a tring
*
*---------------------------------------------------------
*/
static void
trim(char* strParm)
{
char *str;
for (str = strParm; *str; str++);
while (str > strParm && ((*(str-1) == ' ') || (*(str-1) == '\\t')))
{
*(--str) = '\\0';
}
return;
}
/*-------------------------------------------------
* Function: checkSql
*
* Parameters: msg, ... IN - a sprintf format list of parameters
*
* Purpose: Check the sqlcode value for the last SQL statement
*----------------------------------------------------------
*/
static void
checkSql(char* msg, ...)
{
va_list ap;
va_start(ap,msg);
if (sqlca.sqlcode < 0)
{
fprintf(stderr,"** ERROR ** sqlcode:%d isamcode:%d\\n", sqlca.sqlcode,
sqlca.sqlerrd[1]);
vfprintf(stderr, msg, ap);
fprintf(stderr,"\\n");
exit(-1);
}
va_end(ap);
}
/*--------------------------------------------------------
* Function: fmtmsg
*
* Parameters: msg, ... IN - a sprintf format list of parameters
*
* Returns: formated message to be returned
*
* Purpose: Format the message in as with sprintf and return the
* formated string
*-------------------------------------------------------
*/
static char*
fmtmsg(char* msg, ...)
{
static char msgbuff[800];
va_list ap;
va_start(ap, msg);
vsprintf(msgbuff,msg,ap);
va_end(ap);
return msgbuff;
}
/*----------------------------------------------------------
* Function: errorExit
*
* Parameters: lineno IN - line number where failure occured
* msg IN - message to print as part of the exit
*
* Purpose: Error exit with a message printed to stderr
*----------------------------------------------------------
*/
static void
errorExit(int lineno, char* msg)
{
fprintf(stderr,"ERROR at line %d\\n%s\\n", lineno, msg);
exit(-1);
}
/*------------------------------------------------------------
* Function: freeList
*
* Parameters: head IN - head of list to free
*
* Purpose: Free the list of buffers
*------------------------------------------------------------
*/
static void
freeList( colList_td *head)
{
colList_td *titem;
while ((titem = head) != NULL)
{
head = titem->next;
free(titem->val);
free(titem);
}
return;
}
/*----------------------------------------------------------------
* Function: getPrimaryKeys
*
* Parameters: tableNameParm IN - Name of table
* tableOwnerParm IN - Owner of table
* cols IN - List of columns in the select list
*
* Returns: colList_td List of columns making up the primary key
*
* Purpose: Obtain the primary key(s) for a given table
*-----------------------------------------------------------------
*/
static colList_td*
getPrimaryKeys(char *tableNameParm, char *tableOwnerParm, colList_td *cols)
{
$char *tableName = tableNameParm;
$char *tableOwner = tableOwnerParm;
$int tabid;
$int part[16];
$char colname[130];
$int colno;
$char stmt[500];
int tnum;
colList_td *projcol;
colList_td *tcol;
colList_td *tcolend = NULL;
colList_td *tcolstart = NULL;
/*
* Get the parts for the primary key index
*/
$select st.tabid,
si.part1, si.part2, si.part3, si.part4, si.part5, si.part6,
si.part7, si.part8, si.part9, si.part10, si.part11, si.part12,
si.part13, si.part14, si.part15, si.part16
into :tabid, :part[0], :part[1], :part[2], :part[3], :part[4], :part[5],
:part[6], :part[7], :part[8], :part[9], :part[10], :part[11],
:part[12], :part[13], :part[14], :part[15]
from systables st, sysconstraints sc, sysindexes si
where st.tabname = :tableName
and st.owner = :tableOwner
and sc.constrtype = 'P'
and sc.tabid = st.tabid
and si.idxname = sc.idxname;
checkSql("Fetch Index for %s.%s", tableOwner, tableName);
/*
* Get a cursor for the table to find the columns of the PK
*/
$declare c_col cursor for
select colname, colno
from syscolumns
where tabid = :tabid
order by colno; checkSql("declare cursor for syscolumns where tabid is %d", tabid);
$open c_col;
checkSql("open c_col using %d", tabid);
for (;;)
{
/*
* Get the next column
*/
$fetch c_col into :colname, :colno;
if (sqlca.sqlcode == 100)
break;
checkSql("fetch c_col");
/*
* Check to see if this column is one of the PK columns
*/
for (tnum = 0; tnum < 16; tnum++)
{
if (colno == part[tnum])
break;
}
/*
* Nope - then get the next column
*/
if (tnum >= 16)
continue;
trim(colname);
@@NL