如何用delphi改变数据库结构？

[5201314]

我用的是Foxpro2.5数据，想把库结构改变一下，代码由10位变为12位，改变后，由旧库向新库追加数据，在转换的同时把年份的2位加到4位，如原代码为9903031234，追加到新库中应自动变为199903031234，请问有什么比较好的方法。谢谢。

[kxy]

1.从旧表中倒入FieldDefs,修改Field
2.从旧表中倒入IndexDefs
3.用TTable.CreateTable方法建立新表
4.处理数据
5.删除旧表

[kxy]

//changed from bde sample
//This example uses the following input:
//
// ChangeField(Table1, Table1.FieldByName('FOO'), MyChangeRec)

//ChangeRec is defined as follows:

type

ChangeRec = packed record
szName: DBINAME;
iType: Word;
iSubType: Word;
iLength: Word;
iPrecision: Byte;
end;


//The function is defined as follows:

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 not Table.Active then
raise EDatabaseError.Create('Table must be opened to restructure');
if not Table.Exclusive 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 (Length(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;

var MyChangeRec:ChangeRec;
begin
Fillchar(MyChangeRec, sizeof(MyChangeRec), 0);
MyChangeRec.iLength:=20;
ChangeField(MyTable, MyTable.FieldByName('strfield1'), MyChangeRec);
end;

[zdg]

可以用
Alter table table_name Modify column_name data_type
改变字段的结构...