Converting auto-increment fields to integer

hi skeleton,
for what database?
meikl

What DB are you using?
Rob ;-)

Sorry, for Paradox!

hi skeleton,

vice versa is not possible,
only the direction autoinc->integer

my test-unit

unit db_res_ainc_u;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables, StdCtrls, bde;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Table1: TTable;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}


//pasted from borland - BDE-API sample
Type
  ChangeRec = packed record
    szName: DBINAME;
    iType: word;
    iSubType: word;
    iLength: word;
    iPrecision: byte;
  end;

procedure ChangeField(Table: TTable; Field: TField; Rec: ChangeRec);
var
  Props: CURProps;
  hDb: hDBIDb;
  TableDesc: CRTblDesc;
  pFields: pFLDDesc;
  pOp: pCROpType;
  B: byte;

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');

  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));
  finally
    if pFields <> nil then
      FreeMem(pFields);
    if pOp <> nil then
      FreeMem(pOp);
  end;
end;



//autoInc->Integer
procedure TForm1.Button1Click(Sender: TObject);
var CR : ChangeRec;
begin
  Table1.Close;
  Table1.Exclusive := True;
  Table1.Open;
  FillChar(CR,SizeOf(CR),0);
  CR.iType := fldINT32;
  ChangeField(Table1,Table1.FieldbyName('ID'),CR);
  Table1.Close;
end;

//integer->autoinc, does not work, if table is not empty
procedure TForm1.Button2Click(Sender: TObject);
var CR : ChangeRec;
begin
  Table1.Close;
  Table1.Exclusive := True;
  Table1.Open;
  FillChar(CR,SizeOf(CR),0);
  CR.iType := fldPDXAUTOINC; //works only if table empty
  ChangeField(Table1,Table1.FieldbyName('ID'),CR);
  Table1.Close;
end;

end.

try it out

meikl ;-)

Hi kretzsmar,

Thanks, I'll give it a try but I need
it to work both ways because I need
to create a new structure a table (extra fields and fields changes) but keep the same auto-inc values.
Database desktop does it but I need to
do it with code!

I'll give you 50 points if
auto-inc->integer works.

Any other ideas to solve my problem!

 

hi kretszmar

Youre right! Integer->auto-inc does
not work in DB Desktop if not
empty table. I must have
done on a empty table and thought it will work in any cenario.

Thanks.

hi skeleton,
(are you really so thin?)

thanks for accepting my comment as an answer. ;-)

sorry, about the impossibility of the vice versa implementation.

good luck again

meikl ;-)

hi kretzsmar,
not at all - trying to loose weight ;-)

thanks
skeleton