Very Computer

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

  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

        { 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

        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

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