At 11:22 AM 8/11/2011 +0200, you wrote:
> >
> > Does anyone know if a tool exists to do this? Basically we want to compare
> > table structures and data differences with the ability to move the correct
> > data to the final production tables.
> >
Below is a quick thing I threw together to check table structures. I ran a
couple tests on it but not exhaustive. This also checks data types in
addition to just field names. As you see I don't have a lot of error
handling in this either and there may be some ways to optimize the code.
But it sounds like you're wanting a routine for a 1-time (or infrequent)
process so this may suffice.
Again the function at the bottom, "comp_struc", only checks structure. If
you wanted to find data differences you could use SCATTER/GATHER commands.
Since you said you had a primary key, here is a general outline of what to
do there (this is not executable code):
SELECT tbl1
scan
scatter fields <list of fields you want to compare> memo name otbl1rec
if seek(otbl1rec.<primary key>, tbl2, <primary key index>)
select tbl2
scatter fields <list of fields mentioned above> memo name otbl2rec
if CompObj(otbl1rec, otbl2rec)
*-- you may not want to use CompObj. If field names etc are
different it'll return true, etc. Also if
*-- and if the fields can contain nulls I think this may not
work as expected.
*-- you could create another routine that steps through each
object's properties and do
*-- your own logic. Take a look at AMEMBERS(). But if you know
the fields that you want to compare
*-- did not change name, you could probably get by with
COMPOBJ() and a specific list of field names
*-- in the SCATTER statement
else
*-- code here to show the records were not equal - e.g. at
least one of the fields differed.
endif
else
*-- this is where you'd put code to note that the record wasn't
found in table 2 at all
endif
endscan
FUNCTION comp_struc
*-- compare the structure of 2 tables (or cursors)
*-- The code builds up a string saying things like "...this field
doesn't exist in that table, etc..."
*-- That string is returned as the result of the function
*--
*-- Parameters:
*-- - tbl1: could be any table/cursor, etc
*-- - tbl2: could be any table/cursor, etc
*-- Assumptions:
*-- - both passed aliases (tables/cursors) are "open"
*--
PARAMETERS tbl1, tbl2
LOCAL nOldSel, cret, nfld1, nfld2, ni, cfld, ctype, nsize, cchkfld, nfound
cRet = ""
nOldSel = SELECT(0)
nfld1 = AFIELDS(at1_,tbl1)
nfld2 = AFIELDS(at2_,tbl2)
FOR ni = 1 TO nfld1
cfld = at1_[ni,1]
ctype = at1_[ni, 2]
nsize = at1_[ni, 3]
nFound = ASCAN(at1_, cfld, 1, -1, 1, 1+2+4+8)
IF nfound > 0
IF ctype = at2_[nfound, 2] AND nsize = at2_[nfound, 3]
*-- looks like it matches OK
ELSE
cRet = cret + IIF(EMPTY(cret), '', CHR(13)) + cfld
+ " diff type/size in " + tbl2 + ;
" " + ctype + "," + ALLTRIM(STR(nsize)) +
" | " + ;
at2_[nfound,2] + "," +
ALLTRIM(STR(at2_[nfound,3])) + ;
" index ref: " + ALLTRIM(STR(nfound))
ENDIF
ELSE
cRet = cret + IIF(EMPTY(cret), '', CHR(13)) + cfld + "
does not exist in " + tbl2
ENDIF
ENDFOR
*-- reverse check to find what exists in tbl2 but not 1
FOR ni = 1 TO nfld2
cfld = at2_[ni,1]
ctype = at2_[ni, 2]
nsize = at2_[ni, 3]
nFound = ASCAN(at2_, cfld, 1, -1, 1,1+2+4+8)
IF nfound > 0
ELSE
cRet = cret + IIF(EMPTY(cret), '', CHR(13)) + cfld + "
does not exist in " + tbl1
ENDIF
ENDFOR
SELECT (noldsel)
RETURN cRet
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
