Board index » delphi » Modifying table structure

Modifying table structure

What is the best way to modify the structure of a paradox table? (ie:
increasing a field length)  The field contains data that we need to
preserve.  I took a look at the tquery and tryed to use Alter table <table>
Modify (fieldname length) but it complains and says "invalid use of keyword
modify" any help would be appreciated.

Dottie

 

Re:Modifying table structure


If this has to be done programmatically, you will need the BDE unit, which
is documented at http://www.inprise.com/devsupport/bde/bdeapiex/index.html

However, this takes some getting used to. I can give you a start from my own
code if you wish.

If you want to do it manually, keep in mind that the database desktop, in
the tools menu, allows you to do it very easily.

Cheers,

John Bleau

Re:Modifying table structure


My godforsaken Outlook Express keeps losing half my messages. Here goes
again:

If this has to be done programmatically, you will need the BDE unit, which
is documented at http://www.inprise.com/devsupport/bde/bdeapiex/index.html

However, this takes some getting used to. I can give you a start from my own
code if you wish.

If you want to do it manually, keep in mind that the database desktop, in
the tools menu, allows you to do it very easily.

Cheers,

John Bleau

Re:Modifying table structure


Thanks John, yes I have to do it programmatically.

Dottie

Quote
"John Bleau" <jrbleauNOS...@sympatico.ca> wrote in message

news:3c740c6f_2@dnews...
Quote
> My godforsaken Outlook Express keeps losing half my messages. Here goes
> again:

> If this has to be done programmatically, you will need the BDE unit, which
> is documented at http://www.inprise.com/devsupport/bde/bdeapiex/index.html

> However, this takes some getting used to. I can give you a start from my
own
> code if you wish.

> If you want to do it manually, keep in mind that the database desktop, in
> the tools menu, allows you to do it very easily.

> Cheers,

> John Bleau

Re:Modifying table structure


I wrote a general form and a procedure that detects the changes of the
structure of the tables of a project and when detect any change restructure
the modified table.

If you want I can send you the form, anyway I upload it to
www.delphi3000.com

The links is

http://www.delphi3000.com/articles/article_2315.asp

Alejandro

"Dottie" <dottie.w...@fhwa.dot.gov> escribi en el mensaje
news:3c740983_2@dnews...

Quote
> What is the best way to modify the structure of a paradox table? (ie:
> increasing a field length)  The field contains data that we need to
> preserve.  I took a look at the tquery and tryed to use Alter table
<table>
> Modify (fieldname length) but it complains and says "invalid use of
keyword
> modify" any help would be appreciated.

> Dottie

Re:Modifying table structure


Here's an example of the voluminous code just to alter a field. However,
once you get the hang of it, BDE becomes usable programmatically! If you set
it to courier font, it will be more readable.

uses BDE;

type
  ChangeRec = packed record
    szName: DBINAME;
    iType: word;
    iSubType: word;
    iLength: word;
    iPrecision: byte;
  end;

var MyChangeRec: changerec; zT: ttable;

The procedure below would now be called as follows:

      MyChangeRec.iLength:=mNF[zJ].iLength;
      MyChangeRec.iPrecision:=0; //mNF[zJ].iPrecision;
      MyChangeRec.iSubType:=mPFT[mNF[zJ].id].iSubType;
      MyChangeRec.iType:=mPFT[mNF[zJ].id].iType;
      MyChangeRec.szName:=mNF[zJ].szname;

      gChangeField(zT, FieldByName(zT.fields[zI].FieldName), MyChangeRec);

I adapted gChangeField from the documentation whose link I posted. There's
redundant code in the whole mess, but it works. My users and I have invoked
it many times without a hitch. But before going further, here's a table of
Paradox field types:

(* 'date'; mNF[zI].szname:=''; mNF[zI].itype:=2; mNF[zI].iLength:=0;
mNF[zI].iprecision:=0; mNF[zI].iSubType:=0;
1-ALPHA 1  2-NUMBER 3-MONEY 4-DATE 5-SHORT 6-MEMO 7-BINARY 8-FMTMEMO 9-OLE
10-GRAPHIC
{ID Name    Type Subtype Physize Length
1  ALPHA      1     0      1       n
2  NUMBER     7     0      8       0
3  MONEY      7    21      8       0
4  DATE       2     0      4       0
5  SHORT      5     0      2       0
6  MEMO       3    22      1       1   has to have min length of 1
7  BINARY     3    23      1       ?
8  FMTMEMO    3    24      1       1
9  OLE        3    25      1       0
10 GRAPHIC    3    26      1       0}

*)
{Example 3: Alter a field in a Paradox or dBASE table.
This example will alter an existing field in a Paradox or dBASE table.
NOTE: You must fill in all options in the ChangeRec with 0 or '' if the
option is not used in the restructure. FillChar can be used to do this:
Fillchar(MyChangeRec, sizeof(MyChangeRec), 0);
This example uses the following input:
ChangeField(Table1, Table1.FieldByName('FOO'), MyChangeRec)
ChangeRec is defined as follows:}

procedure gChangeField(Table: TTable; Field: TField; Rec: ChangeRec);
var
  Props: CURProps;
  hDb: hDBIDb;
  TableDesc: CRTblDesc;
  pFields: pFLDDesc;
  pOp: pCROpType;
  B: byte;
//  zB: boolean;
begin
  // Initialize the pointers...
  pFields := nil; pOp := nil;
  // Make sure the table is open exclusively so we can get the db handle...
  if Table.Active = False then
    raise EDatabaseError.Create('Table must be opened to restructure');
  if Table.Exclusive = False then
    raise EDatabaseError.Create('Table must be opened exclusively to
restructure');
//  zB:=false;

  if uppercase(field.FieldName)='DATE' then
  try
    table.DeleteIndex('Date');
//    zB:=true;
    imprimer.fregen.richedit1.lines.add('Table
'+uppercase(table.tablename)+' : index Date enlev');
  except
//    zB:=false;
  end;

  Check(DbiSetProp(hDBIObj(Table.Handle), curxltMODE, integer(xltNONE)));
  // Get the table properties to determine table type...
  Check(DbiGetCursorProps(Table.Handle, Props));
  // Make sure the table is either Paradox or dBASE...
  if (Props.szTableType <> szPARADOX) and (Props.szTableType <> szDBASE)
then
    raise EDatabaseError.Create('Field altering can only occur on Paradox' +
                ' or dBASE tables');
  // Allocate memory for the field descriptor...
  pFields := AllocMem(Table.FieldCount * sizeof(FLDDesc));
  // Allocate memory for the operation descriptor...
  pOp := AllocMem(Table.FieldCount * sizeof(CROpType));
  try
    // Set the pointer to the index in the operation descriptor to put
    // crMODIFY (This means a modification to the record is going to
happen)...
    Inc(pOp, Field.Index);
    pOp^ := crMODIFY;
    Dec(pOp, Field.Index);
    // Fill the field descriptor with the existing field information...
    Check(DbiGetFieldDescs(Table.Handle, pFields));
    // Set the pointer to the index in the field descriptor to make the
    // midifications to the field
    Inc(pFields, Field.Index);

    // If the szName portion of the ChangeRec has something in it, change
it...
    if StrLen(Rec.szName) > 0 then
      pFields^.szName := Rec.szName;
    // If the iType portion of the ChangeRec has something in it, change
it...
    if Rec.iType > 0 then
      pFields^.iFldType := Rec.iType;
    // If the iSubType portion of the ChangeRec has something in it, change
it...
    if Rec.iSubType > 0 then
      pFields^.iSubType := Rec.iSubType;
    // If the iLength portion of the ChangeRec has something in it, change
it...
    if Rec.iLength > 0 then
      pFields^.iUnits1 := Rec.iLength;
    // If the iPrecision portion of the ChangeRec has something in it,
change it...
    if Rec.iPrecision > 0 then
      pFields^.iUnits2 := Rec.iPrecision;
    Dec(pFields, Field.Index);

    for B := 1 to Table.FieldCount do begin
      pFields^.iFldNum := B;
      Inc(pFields, 1);
    end;
    Dec(pFields, Table.FieldCount);

    // Blank out the structure...
    FillChar(TableDesc, sizeof(TableDesc), 0);
    //  Get the database handle from the table's cursor handle...
    Check(DbiGetObjFromObj(hDBIObj(Table.Handle), objDATABASE,
hDBIObj(hDb)));
    // Put the table name in the table descriptor...
    StrPCopy(TableDesc.szTblName, Table.TableName);
    // Put the table type in the table descriptor...
    StrPCopy(TableDesc.szTblType, Props.szTableType);
    // The following three lines are necessary when doing any field
restructure
    // operations on a table...

    // Set the field count for the table
    TableDesc.iFldCount := Table.FieldCount;
    // Link the operation descriptor to the table descriptor...
    TableDesc.pecrFldOp := pOp;
    // Link the field descriptor to the table descriptor...
    TableDesc.pFldDesc := pFields;
    // Close the table so the restructure can complete...
    Table.Close;
    // Call DbiDoRestructure...
    Check(DbiDoRestructure(hDb, 1, @TableDesc, nil, nil, nil, FALSE));
{    if zB then
    try
      table.open;
      table.AddIndex('DateFld', 'DateFld', []);
      zB:=true;
    except
      zB:=false;
    end;

Quote
}  finally

    if pFields <> nil then
      FreeMem(pFields);
    if pOp <> nil then
      FreeMem(pOp);
  end;
end;

Re:Modifying table structure


Code sample to resize a Paradox table field at run-time

Calling Syntax:
  ResizeField('c:\database\sometable.db', 'FieldName', 80);
  // (where 80 is the new field size)

Uses  DBITypes, DBIProcs, DBTables, DB;

// Structure used to specify new field properties
type TChangeRec = Packed Record
    szName: DBINAME;
    iType: word;
    iSubType: word;
    iLength: word;
    iPrecision: byte;
end;

// Procedure to modify Paradox table field properties
// (based on example from the Borland BDE Reference help)
procedure ChangeField(Table : TTable; FieldName : String; Rec : TChangeRec);
var
  Props: CURProps;
  hDb: hDBIDb;
  Res : DBIResult;
  TableDesc: CRTblDesc;
  pFields: pFLDDesc;
  pOp: pCROpType;
  B: byte;
  Field : TField;
begin
  // Make sure table is opened exclusively
  If (Table.Active and Not Table.Exclusive) Then Table.Close;
  If (Not Table.Exclusive) Then Table.Exclusive := True;
  If (Not Table.Active) Then Table.Open;

  Field := Table.FieldByName(FieldName);

  // Get table properties (needed to get table type)
  Check(DbiSetProp(hDBIObj(Table.Handle), curxltMODE, integer(xltNONE)));
  Check(DbiGetCursorProps(Table.Handle, Props));

  // Allocate memory for pointers
  pFields := AllocMem(Table.FieldCount * sizeof(FLDDesc));
  pOp := AllocMem(Table.FieldCount * sizeof(CROpType));

  try
    // Set the pointer to the index in the operation descriptor
    // to crMODIFY (to modify field properties)
    Inc(pOp, Field.Index);
    pOp^ := crMODIFY;
    Dec(pOp, Field.Index);

    // Get existing field properties
    Check(DbiGetFieldDescs(Table.Handle, pFields));
    Inc(pFields, Field.Index);

    // Check for field changes specified by caller
    if StrLen(Rec.szName) > 0 then pFields^.szName := Rec.szName;
    if Rec.iType > 0 then pFields^.iFldType := Rec.iType;
    if Rec.iSubType > 0 then pFields^.iSubType := Rec.iSubType;
    if Rec.iLength > 0 then pFields^.iUnits1 := Rec.iLength;
    if Rec.iPrecision > 0 then pFields^.iUnits2 := Rec.iPrecision;

    Dec(pFields, Field.Index);

    for B := 1 to Table.FieldCount do begin
      pFields^.iFldNum := B;
      Inc(pFields, 1);
    end;
    Dec(pFields, Table.FieldCount);

    // Clear the structure
    FillChar(TableDesc, sizeof(TableDesc), 0);

    // Get database handle from the table cursor's handle
    Check(DbiGetObjFromObj(hDBIObj(Table.Handle), objDATABASE,
hDBIObj(hDb)));

    // Set table name and type into the descriptor
    StrPCopy(TableDesc.szTblName, Table.TableName);
    StrPCopy(TableDesc.szTblType, Props.szTableType);

    // Set field count for this table
    TableDesc.iFldCount := Table.FieldCount;

    // Link the operation descriptor to the table descriptor
    TableDesc.pecrFldOp := pOp;

    // Link the field descriptor to the table descriptor
    TableDesc.pFldDesc := pFields;

    Table.Close;

    // Do the actual table restructuring
    Res := DbiDoRestructure(hDb, 1, @TableDesc, nil, nil, nil, FALSE);
    Check(Res);
  finally
    if pFields <> nil then FreeMem(pFields);
    if pOp <> nil then FreeMem(pOp);

    Table.Exclusive := False;
    Table.Open;
  end;
end;

// Procedure to resize a Paradox table field
procedure ResizeField(TablePathName, FieldName : String; FieldSize :
Integer);
var
  FldChanges : TChangeRec;
  MyTable : TTable;
begin
  // First create a table component
  MyTable := TTable.Create(Application.MainForm);
  MyTable.DatabaseName := ExtractFilePath(TablePathName);
  MyTable.TableName := ExtractFileName(TablePathName);

  // Set the new field size into the field structure
  FillChar(FldChanges, SizeOf(FldChanges), 0);
  FldChanges.iLength := FieldSize;

  // Call ChangeField to do the actual dbiDoRestructure call
  ChangeField(MyTable, FieldName, FldChanges);

  MyTable.Free;
end;
--
Vinnie Murdico
The BDE Support Page
http://www.bdesupport.com

Other Threads