Converting auto-increment fields to integer

How can you convert (change) a auto-increment field to a integer field and visa versa at run time without destroying the existing data (integer values)?
skeletonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
hi skeleton,
for what database?
meikl
0
RBertoraCommented:
What DB are you using?
Rob ;-)
0
skeletonAuthor Commented:
Sorry, for Paradox!
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

kretzschmarCommented:
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 ;-)
0
skeletonAuthor Commented:
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!

 
0
kretzschmarCommented:
hi skeleton

>Database desktop does it
really? not by me!
integer->autoinc with a not empty table?

maybe you should explain a bit more about what you want to do, because, if database desktop can do it, then should it also possible to do it at runtime.

meikl ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skeletonAuthor Commented:
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.
0
kretzschmarCommented:
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 ;-)
0
skeletonAuthor Commented:
hi kretzsmar,
not at all - trying to loose weight ;-)

thanks
skeleton
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.