Compare structure of two tables programmatically. 1

[69mach]

Is there an easy way to compare the structure of two tables programmatically? I need to create a program that can compare two sets of tables and modify the structure of the tables in location A based on the structure of the tables in location B.

All tables to be compared/altered are free tables.
Each data location contains the same tables.
All tables need to be compared to a Master set of tables so as to identify any structural differences and then programmatically alter them so their structures are the same.

Thanks in advance for any help!

 

[AirCon]

You can use AFIELDS(), i.e.:

lnFCount1 = AFields(aTest1, 'Table1')
lnFCount2 = AFields(aTest2, 'Table2')
if (lnFCount1 != lnFCount2)
   ?'Field counter is not the same'
else
   for i = 1 to lnFCount1
      if (aTest1[i,1] != aTest2[i,1]) or ;
         (aTest1[i,2] != aTest2[i,2]) or ;
         (aTest1[i,3] != aTest2[i,3]) or ;
         (aTest1[i,4] != aTest2[i,4]) or ;
         (aTest1[i,5] != aTest2[i,5]) or ;
         ?'Field structure is not the same'
      endif
   next
endif

Regards

-- AirCon --

 

[rgbean]

While I used to try to do this myself for a number of years, things can get real complicated in a big hurry. If you also need to support 2x and VFP tables, fix headers, recreate indexes, handle tables with RI or persistant relationships in DBCs, etc., there are just too many variants to consider. I now use Stonefield's Database Toolkit (SDT) and recommend it wholeheartedly. (

http://www.stonefield.com/)

Disclaimer: While I know the author, and have worked as a beta tester for him, I have no financial stake in this company.

Rick

 

[69mach]

Thanks for the quick replys!

AirCon - AFields() identifies the structure as being different but does not tell me what the difference is. Without knowing the difference, I still can't update the table. Any idea how to determine the actual fields structure?

rgbean - Yes, We support FoxPro from 2.6 up to current. I have a utility for fixing corrupt headers already. I'll take a look at SDT, but the only thing I'm short of completing what I need is the ability to identify a fields structure.

Again, thanks for the fast responses.

 

[AirCon]

AFields() identifies the structure as being different but does not tell me what the difference is
What do you mean by this?

if (aTest1[i,1] != aTest2[i,1]) or ;
   (aTest1[i,2] != aTest2[i,2]) or ;
   (aTest1[i,3] != aTest2[i,3]) or ;
   (aTest1[i,4] != aTest2[i,4]) or ;
   (aTest1[i,5] != aTest2[i,5])

It does telling the differents. I'm sorry am I missing something here ?

-- AirCon --

 

[kenndot]

I have written the following code.... and it works for me each time.
I have a form at the beginning where the user browses to the tables to compare, then this is the code in the click event.

Then I generate a report showing the structural differences

** compare 2 tables to ensure that table structure is the same



PUBLIC lnFldsInEmp, lnFldsInOldEmp, i, lnElmtFnd
exactsetting = set("exact&quot
SET EXACT ON
** the tables could have the same name, so
** create a cursor to hold them so there's
** no issue there
IF FILE( "DATA\tblDifference.DBF" )
IF USED("tblDifference&quot
USE IN tblDifference
ENDIF
DELETE FILE DATA\tblDifference.DBF
DELETE FILE DATA\tblDifference.CDX
ENDIF

IF FILE( "DATA\tblStructdiff.DBF" )
IF USED("tblStructDiff&quot
USE IN tblStructDiff
ENDIF
DELETE FILE DATA\tblStructDiff.DBF
DELETE FILE DATA\tblStructDiff.CDX
ENDIF



CREATE TABLE DATA\tblDifference ( tblname c(254), ;
tbl2name c(254), ;
nonmatch c(25), ;
tbl1attrib c(25), ;
tbl2attrib c(25), ;
fldname c(25) )

concatstring = "frmmain.edit1.value + chr(13)"
lnFldsInEmp = AFIELDS(firstdbf,"csrFirstTable&quot
lnFldsInOldEmp = AFIELDS(seconddbf,"csrSecondTable&quot
frmMain.edit1.VALUE = &concatstring. + "Beginning comparison..."

IF lnFldsInEmp != lnFldsInOldEmp && Same # of fields ?
frmMain.edit1.VALUE = "Tables do NOT have matching # of fields."

INSERT INTO tblDifference ( tblname, ;
tbl2name, ;
nonmatch, ;
tbl1attrib, ;
tbl2attrib ) VALUES ;
(frmMain.text1.VALUE, ;
frmMain.text2.VALUE, ;
"# of fields", ;
STR(lnFldsInEmp), ;
STR(lnFldsInOldEmp))

=diffnumoffields()

ELSE
frmMain.edit1.VALUE = &concatstring. + "Tables have matching # of fields... beginning check of specifics"
ENDIF

= lstrcmpi()
SELECT tblDifference
if reccount('tblDifference') < 1
messagebox(&quot;No structural differences found between the 2 tables.&quot;, 0, &quot;NHPRI&quot
frmMain.edit1.VALUE = &concatstring. + &quot; COMPARISON FINISHED. NO DIFFERENCES FOUND.&quot;
*!* SELECT TBLDIFFERENCE
*!* APPEND BLANK
*!* REPLACE nonmatch WITH &quot;None found.&quot;

endif
_screen.visible = .t.
REPORT FORM REPORTS\RPTSTRUCTUREDIFF PREVIEW IN SCREEN
_screen.visible = .f.

SET EXACT &EXACTSETTING.
RETURN



***************************************************************************************************************

FUNCTION diffnumoffields
*!* at this point we know that there are not equal numbers of fields
*!* produce a report that shows the missing fields, from both tables
*!* points of view.

CREATE TABLE DATA\tblStructDiff ( fndintbl c(254), ;
notfndtbl c(254), ;
fldname c(25), ;
fldtype c(5), ;
fldlength i(1))

** check table A to table B
FOR i = 1 TO lnFldsInEmp
foundinsecond = ASCAN(seconddbf, firstdbf[i,1])
IF foundinsecond < 1
INSERT INTO tblStructDiff (fndintbl, notfndtbl, fldname, fldtype, fldlength ) VALUES ;
(frmMain.text1.VALUE, frmMain.text2.VALUE, firstdbf[i,1], firstdbf[i,2], firstdbf[i,3] )

ENDIF
ENDFOR
i = 0

** check table B to table A
FOR i = 1 TO lnFldsInOldEmp
foundinfirst = ASCAN(firstdbf, seconddbf[i,1])
IF foundinfirst < 1
INSERT INTO tblStructDiff (fndintbl, notfndtbl, fldname, fldtype, fldlength ) VALUES ;
(frmMain.text2.VALUE, frmMain.text1.VALUE, seconddbf[i,1], seconddbf[i,2], seconddbf[i,3] )

ENDIF

ENDFOR


SELECT tblStructDiff


_screen.visible = .t.

REPORT FORM REPORTS\rptmissingflds PREVIEW IN SCREEN
_screen.visible = .f.
ENDFUNC




FUNCTION lstrcmpi
* Note the fields may be in a different order in the two tables but still
* be equivalent.

* We could have just as easily sorted the two arrays
* emp & oldemp and avoided the row test (ascan).

* Also, you'll notice I didn't bother converting the element
* found (lnElmeFnd) to a row element ( oldemp[lnElmeFnd+1] vs oldemp[lnRowElmnt,2] )

* This could also be much simpler, or, much more complex depending on your needs. But oh well...




FOR i = 1 TO lnFldsInEmp

lChanges = .T.
lnElmtFnd = ASCAN(seconddbf,firstdbf[i,1])
secondtype =&quot;&quot;
secondlen = &quot;&quot;
seconddec = &quot;&quot;

** the prev. statement looks for a fieldname match
** if it's not found then we don't need to check
** the rest either
IF lnElmtFnd > 0

DO CASE
*!* frmMain.edit1.VALUE = &concatstring. + &quot;Checking field names... &quot;
*!* CASE lnElmtFnd == 0 && A field name is different
*!* INSERT INTO tblDifference ( tblname, ;
*!* tbl2name, ;
*!* nonmatch, ;
*!* tbl1attrib, ;
*!* tbl2attrib ) VALUES ;
*!* (frmMain.text1.VALUE, ;
*!* frmMain.text2.VALUE, ;
*!* &quot;field name&quot;, ;
*!* firstdbf[i,1], ;
*!* seconddbf[i,1])
*!* frmMain.edit1.VALUE = &concatstring. + frmMain.text1.VALUE + &quot; field name: &quot; + firstdbf[i,1]
*!* frmMain.edit1.VALUE = &concatstring. + frmMain.text2.VALUE + &quot; field name: &quot; + seconddbf[i,1]
*!* lChanges = .F.
frmMain.edit1.VALUE = &concatstring. + &quot;Checking field types...&quot;
CASE firstdbf[i,2] != seconddbf[lnElmtFnd+1] && Field type is different
secondtype = str(seconddbf[lnElmtFnd+1])
INSERT INTO tblDifference ( tblname, ;
tbl2name, ;
nonmatch, ;
tbl1attrib, ;
tbl2attrib, ;
fldname) VALUES ;
(frmMain.text1.VALUE, ;
frmMain.text2.VALUE, ;
&quot;field type&quot;, ;
firstdbf[i,2], ;
secondtype, ;
firstdbf[i,1])
frmMain.edit1.VALUE = &concatstring. + frmMain.text1.VALUE + &quot; field type: &quot; + firstdbf[i,2]
frmMain.edit1.VALUE = &concatstring. + frmMain.text2.VALUE + &quot; field type: &quot; + seconddbf[i,2]
lChanges = .F.
frmMain.edit1.VALUE = &concatstring. + &quot;Checking field lengths...&quot;

CASE firstdbf[i,3] != seconddbf[lnElmtFnd+2] && Field length is different
secondlen = str(seconddbf[lnElmtFnd+2])
INSERT INTO tblDifference ( tblname, ;
tbl2name, ;
nonmatch, ;
tbl1attrib, ;
tbl2attrib, ;
fldname ) VALUES ;
(frmMain.text1.VALUE, ;
frmMain.text2.VALUE, ;
&quot;field length&quot;, ;
STR(firstdbf[i,3]), ;
secondlen, ;
firstdbf[i,1])

&& STR(seconddbf[i,3]), ;
frmMain.edit1.VALUE = &concatstring. + frmMain.text1.VALUE + &quot; field length: &quot; + STR(firstdbf[i,3])
frmMain.edit1.VALUE = &concatstring. + frmMain.text2.VALUE + &quot; field length: &quot; + STR(seconddbf[i,3])
lChanges = .F.
frmMain.edit1.VALUE = &concatstring. + &quot;Checking decimal places...&quot;
CASE firstdbf[i,4] != seconddbf[lnElmtFnd+3] && Field decimal digits are different
seconddec = str(seconddbf[lnElmtFnd+3])
INSERT INTO tblDifference ( tblname, ;
tbl2name, ;
nonmatch, ;
tbl1attrib, ;
tbl2attrib, ;
fldname ) VALUES ;
(frmMain.text1.VALUE, ;
frmMain.text2.VALUE, ;
&quot;decimal places&quot;, ;
STR(firstdbf[i,4]), ;
seconddec, ;
firstdbf[i,1])
frmMain.edit1.VALUE = &concatstring. + frmMain.text1.VALUE + &quot; field decimal digit places: &quot; + STR(firstdbf[i,2])
frmMain.edit1.VALUE = &concatstring. + frmMain.text2.VALUE + &quot; field decimal digit places: &quot; + STR(seconddbf[i,2])
lChanges = .F.
ENDCASE
endif

NEXT
RETURN lChanges
ENDFUNC

 

[rgbean]

Well the reason things can get complicated is that sometimes fields may be in a different order (and still the same!), or they may have the same name but a different type, or they may be a different length. Another thing that caused me problems was users who decided to &quot;skip&quot; upgrading a version because the new version didn't offer anything they wanted. So then an upgrade might actually have to cover 2 or more versions. SDT handles this much better than my 'own' code.

Rick

 

[69mach]

Aircon - Sorry, I didn't look at what AFields() returned. You are correct, everything I need is there, now I can complete my table comparison/update routine.

THANKS!

 

[AirCon]

You're welcome 69mach

But one thing (Rick has the right), if the table have a different order then you have to take care of it.

Regards

-- AirCon --

 

[69mach]

rgbean - I know what you're saying regarding a 'skipped' update throwing everything out of whack. I've got a pretty good handle on the data though and am pretty sure I can create a comparison/update utility that will work (only for a specific application though, only because I know all the changes to this particular application's data since it's inception). I'll sure look into SDT though, it sounds like a good tool to have.

kenndot - Thanks for your input. I'll run some tests and see if what you have will work for me. I'm not too concerned about the report (of course that comes in handy when testing), I want to just make the changes and go.

AirCon - You had the answer I was looking for right off the bat, thanks again.

Thanks to everybody - you all make tek-tips a great tool.