Restructuring Paradox 5 tables via BDE API

Restructuring Paradox 5 tables via BDE API

Post by William Sorens » Sat, 12 Apr 1997 04:00:00



At work, we have some legacy tables (Paradox 5.0) that
use Alpha 10 fields to store dates.  We want to convert
these to fields of type Date.
As the tables are at clients' sites, we want to do it
programmatically.  I can't get a restructure to work.

We're using Delphi 1.02, BDE 2.5 (not all clients have
been updated to 2.52), and Paradox 5.0.  The code I'm
using follows.  The problem is, it works on any test
table I set up (with or without records, primary/secondary
indexes/etc.), but it fails on "real" tables with a
"Number is out of range.  Table xxx.DB." exception
when dbiDoRestructure() is called.  However, if I use
Database Desktop to Borrow the structure of one of
these troublesome tables (copying fields, indexes,
and everything else), then Add all records from the
original to my copy, I can restructure the copy!  DBD
can restructure the original just fine, though.

I'm baffled.  I could do it in SQL, or we could dial up
each client and do it via DBD, but either method would
be slooooow (some tables have 15,000+ records).

Please email me with any suggestions/code fragments you
may have.  If I can get this working, I will repost the
"good" version here.  TIA!

 Bill Sorensen
 te...@ia.net

Example call:

  ConvertAlphaFieldsToDates(CONST_VCSBase,'SALE',
    ['slsdate','listdate','dateentered']);

USES
  DBIProcs, DBITypes;

CONST
  CONST_MaxNumOfFields = 512;         { >= max. # of fields in Paradox
table }

  { Actual size of arrays may vary - only allocating what we need. }
  TFLDDescArray   = ARRAY [0..(CONST_MaxNumOfFields - 1)] OF FLDDesc;
  TpFLDDescArray  = ^TFLDDescArray;
  TCROpTypeArray  = ARRAY [0..(CONST_MaxNumOfFields - 1)] OF CROpType;
  TpCROpTypeArray = ^TCROpTypeArray;

implementation

PROCEDURE ConvertAlphaFieldsToDates(NameOfAlias: STRING;
  NameOfTable: STRING; CONST asFields: ARRAY OF STRING);
{ Not using TTable - do NOT call when table is open!!!!

}

{ Requires exclusive access to table - raises exception if not.
}

{ Final parameter is open array - pass list of alpha date field names.
}

{ Properly skips restructure if fields have already been converted.
}

{ Exception raised on error.
}

VAR
  DatabaseHandle         : hDBIDb;
  TableCursor            : hDBICur;
  TableFieldCount        : WORD;
  FieldArraySizeInBytes  : WORD;
  OpTypeArraySizeInBytes : WORD;
  pFieldArray            : TpFLDDescArray;
  pOpTypeArray           : TpCROpTypeArray;
  StructLoop             : WORD;
  RestructNeeded         : BOOLEAN;
  ParamLoop              : WORD;
  UpperFieldName         : STRING;
  FieldFound             : BOOLEAN;
  pTblDesc               : pCRTblDesc;

  FUNCTION GetFieldCount(hCursor: hDBICur): WORD;
  { Counts fields in table structure.     }
  { hCursor must be a valid table cursor. }
  { Exception raised on error.            }
  VAR
    pCursorProperties : pCURProps;
  BEGIN
    Result := 0;

    New(pCursorProperties);

    TRY
      Check(dbiGetCursorProps(hCursor,pCursorProperties^));
      Result := pCursorProperties^.iFields;
    FINALLY
      Dispose(pCursorProperties);
    End; { Try..Finally. }
  END;

BEGIN
  { quick cheat so can easily convert to PCHARs }
  NameOfTable := NameOfTable + #0;
  NameOfAlias := NameOfAlias + #0;

  { get handle for database }
  Check(dbiOpenDatabase(@NameOfAlias[1],NIL,dbiREADWRITE,dbiOPENEXCL,
                        NIL,0,NIL,NIL,DatabaseHandle));

  TRY
    { open table to get cursor handle }
    { use xltNONE rather than xltFIELD - otherwise, have to change
fldPDXDATE }

Check(dbiOpenTable(DatabaseHandle,@NameOfTable[1],szPARADOX,NIL,NIL,0,
          dbiREADONLY,dbiOPENEXCL,xltNONE,FALSE,NIL,TableCursor));

    TRY
      { find number of fields in table }
      TableFieldCount        := GetFieldCount(TableCursor);
      FieldArraySizeInBytes  := TableFieldCount * SizeOf(FLDDesc);
      OpTypeArraySizeInBytes := TableFieldCount * SizeOf(CROpType);

      { allocate arrays based on number of fields }
      GetMem(pFieldArray,FieldArraySizeInBytes);
      GetMem(pOpTypeArray,OpTypeArraySizeInBytes);
      { NOTE - SizeOf(pXXXArray^) will not be correct! }

      TRY
        { fill in field descriptor array with original table structure

}

        Check(dbiGetFieldDescs(TableCursor,pFLDDesc(pFieldArray)));

        { close table before restructure }
        Check(dbiCloseCursor(TableCursor));
        TableCursor := NIL;

        { initialize optype array }
        FOR StructLoop := 0 TO (TableFieldCount - 1) DO
          pOpTypeArray^[StructLoop] := crNOOP;

        { see if specified fields in the structure need to be updated

}

        RestructNeeded := FALSE;

        FOR ParamLoop := 0 TO High(asFields) DO
          begin
            UpperFieldName := UpperCase(asFields[ParamLoop]);

            FieldFound := FALSE;

            FOR StructLoop := 0 TO (TableFieldCount - 1) DO
              WITH pFieldArray^[StructLoop] DO
                IF (UpperFieldName = UpperCase(StrPas(szName))) THEN
                  begin
                    FieldFound := TRUE;

                    IF (iFldType <> fldPDXDATE) THEN
                      begin
                        (* NOTE: I've tried
                           commenting out code from here
                           to just before RestructNeeded := TRUE;
                           the "no action" restructure still fails.
                           The "see above" commented lines are
                           ones I've added to see if they make
                           a difference; they don't.
                           I've also tried nulling the last 5
                           elements of the structure.  Sigh.
                         *)
                        iFldType := fldPDXDATE;
                        iUnits1  := 1;  { see above }
                        iUnits2  := 0;  { see above }
                        iLen     := 4;  { see above }

                        pOpTypeArray^[StructLoop] := crMODIFY;

                        RestructNeeded := TRUE;
                      end;

                    Break;  { exit the innermost FOR loop }
                  end;

            IF (NOT FieldFound) THEN
              RAISE EDatabaseError.Create('Field "' +
asFields[ParamLoop] +
                                          '" not found in ' +
NameOfTable);
          end;

        IF (RestructNeeded) THEN
          begin
            { allocate table descriptor structure }
            New(pTblDesc);

            TRY
              { fill in table descriptor structure }
              FillChar(pTblDesc^,SizeOf(pTblDesc^),#0);

              WITH pTblDesc^ DO
                begin
                  { this is correct - structure reserves string array
space }
                  StrCopy(szTblName,@NameOfTable[1]);
                  StrCopy(szTblType,szPARADOX);

                  iFldCount := TableFieldCount;
                  pecrFldOp := pCROpType(pOpTypeArray);
                end;

              { can't put in WITH due to name conflict }
              pTblDesc^.pfldDesc := pFLDDesc(pFieldArray);

              { restructure table - fails here }
              Check(dbiDoRestructure(DatabaseHandle,1,pTblDesc,
                                     NIL,NIL,NIL,FALSE));
            FINALLY
              { free table descriptor structure }
              Dispose(pTblDesc);
            End; { Try..Finally. }
          end;
      FINALLY
        { free arrays }
        FreeMem(pOpTypeArray,OpTypeArraySizeInBytes);
        FreeMem(pFieldArray,FieldArraySizeInBytes);
      End; { Try..Finally. }
    FINALLY
      IF (Assigned(TableCursor)) THEN
        dbiCloseCursor(TableCursor);  { Check() might overwrite err.
msg. }
    End; { Try..Finally. }
  FINALLY
    { free handle for database }
    dbiCloseDatabase(DatabaseHandle);  { Check() might overwrite err.
msg. }
  End; { Try..Finally. }
END;

++++
William Sorensen
te...@ia.net

 
 
 

Restructuring Paradox 5 tables via BDE API

Post by William Sorens » Wed, 16 Apr 1997 04:00:00


Never mind; I found the problem.  I had been doing ALTER TABLEs via
Local SQL on these tables to test some stuff in the past.  I ADDed,
DROPped, and reADDed some fields to the table structure.  In doing so,
the BDE (both 2.5 and 2.52 - I tested this today) sets the iFldNum
field of the FLDDesc array to a number higher than the field count.
This appears to be what it should do from the documentation (as
iFldNum can be checked to see if a restructure was done by comparing
it against the ordinal field values), but dbiDoRestructure doesn't
like it at all.

The fix is to add in code like

FOR StructLoop := 0 TO (TableFieldCount - 1) DO
  begin
     { If columns were dropped and then added via SQL, this   }
     { next bit will prevent "Number is out of range" errors. }

     WITH pFieldArray^[StructLoop] DO
         IF iFldNum > TableFieldCount THEN
            iFldNum := StructLoop + 1;
  end;

This doesn't actually alter iFldNum permanently, but it lets
dbiDoRestructure work.  Seems like a BDE bug to me.
++++
William Sorensen


 
 
 

1. Paradox 5 table in Paradox 8 - restructure disaster

I've had a working simple database (a form linking to a main db and a
couple of supplementary db's) which has worked fine since it was
thrown together.

I've now upgraded to Paradox 8.

Allong the line somewhere, all the files (which are in one directory)
have been moved to another, and the alias redefined.

Last week I tried to restructure the main table (nothing complex, just
A70 to A80) so no entries would have failed revalidation.

However I could not save the new structure. The ultimate refusal was
telling me that a higher order (?) table was required, which was
exactly the table I wanted to restructure.

Worse, as I tried this a few times, I started losing validity checks
and look-up links one by one. For example a default entry for a field
would disappear. So my table IS being restructured, but not the way I
wanted!

I've never had restructuring problems in Paradox 5.

Any ideas for a longtime user but relative newbie to the
technicalities?

Also, this exercise showed that the tables, forms etc are still in
Paradox 5 format. Can these be converted to the later Paradox format?

Thanks in advance,
Peter

____________________________________________


Melbourne PC User Group
____________________________________________

2. SQL 7 Replication with Access 97 or 2000

3. Using BDE to Restructure a table

4. Showing values from a linked table

5. BDE Call to restructure a table

6. WANTED: Programmer & Tech Support People

7. Restructure Table at runtime using Delphi/BDE

8. finding related records... how to find multiple sets ?

9. BDE restructure of Interbase Table

10. Linking to Access table via BDE via ODBC

11. Restructure Table via ObjectPal ???

12. BDE 5.0 SQL on Paradox tables so slow in comparisont to BDE 2.52

13. Help: Paradox 5.0 table loses restructuring