SQL to change the column datatype

Microsoft sql server TSQL

ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME CHGDATATYPE

MYSQL TSQL

ALTER TABLE TABLENAME MODIFY COLUMNNAME CHGDATATYPE


For paradox and dbase we need to write the code as follows in delphi


unit autoincfld;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, BDE, DB, DBClient, DBTables, StdCtrls;

type

TChangeRec = packed record
szName: DBINAME; { field name }
iType: word; { field type }
iSubType: word; { field subtype }
iLength: word; { field length }
iPrecision: byte; { precision }
end;

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

procedure ChangeField(Table: TTable; Field: TField; Rec: TChangeRec);

public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);

const
TheTableName = 'MY_TABLE.DB';
TheFieldName = 'MY_FIELD';

var
CR: TChangeRec;

begin
Table1.TableName := TheTableName;
Table1.Exclusive := True;
Table1.Open;

FillChar(CR, sizeof(CR), #0);
CR.iType := fldPDXAUTOINC;
ChangeField(Table1, Table1.FieldByName(TheFieldName), CR);

end;

procedure TForm1.ChangeField(Table: TTable; Field: TField; Rec: TChangeRec);
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, AnsiString(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;

end.