Vanessa
asked on
Change paradox field type at run time
Hi all,
In a paradox table I have a field
Field Name Type Size
Info Content A 250
I want to be able to change this permanently to a memo 100 ( and retain the same field name) in a run time application. This update application will be separate to the database program.
Basically I am updating a clients database and just want him to press a button to make all the changes.
Data is currently being stored in this field and I don't want to lose the data.
Please any suggestions gratefully received
In a paradox table I have a field
Field Name Type Size
Info Content A 250
I want to be able to change this permanently to a memo 100 ( and retain the same field name) in a run time application. This update application will be separate to the database program.
Basically I am updating a clients database and just want him to press a button to make all the changes.
Data is currently being stored in this field and I don't want to lose the data.
Please any suggestions gratefully received
from my paq
alpha->tmemo for paradox
so it goes
include in your uses clause the unit bde
and add this code to your unit
//paste from the url
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('Tab le must be opened to restructure');
if Table.Exclusive = False then
raise EDatabaseError.Create('Tab le must be opened exclusively to restructure');
Check(DbiSetProp(hDBIObj(T able.Handl e), curxltMODE, integer(xltNONE)));
// Get the table properties to determine table type...
Check(DbiGetCursorProps(Ta ble.Handle , Props));
// Make sure the table is either Paradox or dBASE...
if (Props.szTableType <> szPARADOX) and (Props.szTableType <> szDBASE) then
raise EDatabaseError.Create('Fie ld 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(Tab le.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(hDB IObj(Table .Handle), objDATABASE, hDBIObj(hDb)));
// Put the table name in the table descriptor...
StrPCopy(TableDesc.szTblNa me, Table.TableName);
// Put the table type in the table descriptor...
StrPCopy(TableDesc.szTblTy pe, 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;
//paste end
//change a field
procedure TForm1.Button4Click(Sender : TObject);
var CR : ChangeRec;
begin
Table1.Open;
FillChar(CR,SizeOf(CR),0);
CR.iType := fldBlob;
CR.iSubType := fldstMemo;
ChangeField(Table1,Table1. FieldbyNam e('F2'),CR );
Table1.Close;
end;
hope it helps
meikl ;-)
alpha->tmemo for paradox
so it goes
include in your uses clause the unit bde
and add this code to your unit
//paste from the url
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('Tab
if Table.Exclusive = False then
raise EDatabaseError.Create('Tab
Check(DbiSetProp(hDBIObj(T
// Get the table properties to determine table type...
Check(DbiGetCursorProps(Ta
// Make sure the table is either Paradox or dBASE...
if (Props.szTableType <> szPARADOX) and (Props.szTableType <> szDBASE) then
raise EDatabaseError.Create('Fie
' 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(Tab
// 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(hDB
// Put the table name in the table descriptor...
StrPCopy(TableDesc.szTblNa
// Put the table type in the table descriptor...
StrPCopy(TableDesc.szTblTy
// 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
finally
if pFields <> nil then
FreeMem(pFields);
if pOp <> nil then
FreeMem(pOp);
end;
end;
//paste end
//change a field
procedure TForm1.Button4Click(Sender
var CR : ChangeRec;
begin
Table1.Open;
FillChar(CR,SizeOf(CR),0);
CR.iType := fldBlob;
CR.iSubType := fldstMemo;
ChangeField(Table1,Table1.
Table1.Close;
end;
hope it helps
meikl ;-)
learn more about bde-api
http://www.borland.com/devsupport/bde/bdeapiex/dbidorestructure.html
see third sample
http://www.borland.com/devsupport/bde/bdeapiex/dbidorestructure.html
see third sample
ASKER
Sorry Dr Delphi I didn't make it clear that I would not be running the db application while using the update application.
Would it be easier to do this through a query?
Would it be easier to do this through a query?
>Would it be easier to do this through a query?
nope, did not work->type mismatch->Alpha<>memo
nope, did not work->type mismatch->Alpha<>memo
ASKER
Additionally I will be making about 30 changes (I know that sounds bad but the client has asked for some radical changes) and I want the update application to be very stable.
30 changes!
code a migration-app and a empty table with the desired structure, in your app iterate through the records, do your fieldmappings and post the record to your empty table
depending on the complexity of changes you could use the tbatchmove-component
meikl ;-)
code a migration-app and a empty table with the desired structure, in your app iterate through the records, do your fieldmappings and post the record to your empty table
depending on the complexity of changes you could use the tbatchmove-component
meikl ;-)
Wouldn't just a few SQL statements work?
ALTER TABLE1 add Content2 Memo;
UPDATE TABLE1 set Content2 = Content;
ALTER TABLE1 drop Content;
ALTER TABLE1 add Content Memo;
UPDATE TABLE1 set Content = Content2;
ALTER TABLE1 drop Content2;
That's all there is to it, in my opinion. Just 6 SQL statements would do it quickly, w/o messing with BDE.
If you're working with more changes, they can (in some cases) all be worked on the same query, for example, creating/dropping several temp columns at once, running several updates in the same table at once, and so on.
yours,
Alex
ALTER TABLE1 add Content2 Memo;
UPDATE TABLE1 set Content2 = Content;
ALTER TABLE1 drop Content;
ALTER TABLE1 add Content Memo;
UPDATE TABLE1 set Content = Content2;
ALTER TABLE1 drop Content2;
That's all there is to it, in my opinion. Just 6 SQL statements would do it quickly, w/o messing with BDE.
If you're working with more changes, they can (in some cases) all be worked on the same query, for example, creating/dropping several temp columns at once, running several updates in the same table at once, and so on.
yours,
Alex
are you sure, alex?
you can't drop columns
you can't drop columns
ASKER
ALEX and others,
Below is the code I am using for just one update.
It falls over on Update line message = "Invalid use of keyword line 2 TOKEN UPDATE"
I am not sure what I am doing wrong.
Additionally do I use BLOB as MEMO is unsupported?
Query1.SQL.Clear;
Query1.SQL.Add('ALTER TABLE "VessInfo.DB" ADD CFTM BLOB');
Query1.SQL.Add('UPDATE TABLE "VessInfo.db" SET CFTM = CFT');
Query1.SQL.Add('ALTER TABLE "VessInfo.DB"');
Query1.SQL.Add('DROP CFT');
Query1.SQL.Add('ALTER TABLE "VessInfo.DB"');
Query1.SQL.Add('ADD CFTM BLOB');
Query1.SQL.Add('UPDATE TABLE "VessInfo.DB"');
Query1.SQL.Add('SET CFT = CFTM');
Query1.SQL.Add('ALTER TABLE "VessInfo.DB"');
Query1.SQL.Add('DROP CFTM');
Query1.ExecSQL
Cheers
Vanessa
Below is the code I am using for just one update.
It falls over on Update line message = "Invalid use of keyword line 2 TOKEN UPDATE"
I am not sure what I am doing wrong.
Additionally do I use BLOB as MEMO is unsupported?
Query1.SQL.Clear;
Query1.SQL.Add('ALTER TABLE "VessInfo.DB" ADD CFTM BLOB');
Query1.SQL.Add('UPDATE TABLE "VessInfo.db" SET CFTM = CFT');
Query1.SQL.Add('ALTER TABLE "VessInfo.DB"');
Query1.SQL.Add('DROP CFT');
Query1.SQL.Add('ALTER TABLE "VessInfo.DB"');
Query1.SQL.Add('ADD CFTM BLOB');
Query1.SQL.Add('UPDATE TABLE "VessInfo.DB"');
Query1.SQL.Add('SET CFT = CFTM');
Query1.SQL.Add('ALTER TABLE "VessInfo.DB"');
Query1.SQL.Add('DROP CFTM');
Query1.ExecSQL
Cheers
Vanessa
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question is reasonably current, others are not. All of them need your attention. ADMINISTRATION WILL BE CONTACTING YOU SHORTLY. Moderators Computer101 or Netminder will return to finalize these if they are still open in 14 days. Experts, please post closing recommendations before that time.
Below are your open questions as of today. Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response. This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database. If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
--> Post comments for expert of your intention to delete and why
--> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.
For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process. https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.
Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added. When you grade the question less than an A, please comment as to why. This helps all involved, as well as others who may access this item in the future. PLEASE DO NOT AWARD POINTS TO ME.
To view your open questions, please click the following link(s) and keep them all current with updates.
https://www.experts-exchange.com/questions/Q.20185150.html
https://www.experts-exchange.com/questions/Q.20185271.html
https://www.experts-exchange.com/questions/Q.20292502.html
***** E X P E R T S P L E A S E ****** Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
POINTS FOR EXPERTS awaiting comments are listed in the link below
https://www.experts-exchange.com/commspt/Q.20277028.html
Moderators will finalize this question if in @14 days Asker has not responded. This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
Thanks everyone.
Moondancer
Moderator @ Experts Exchange
Below are your open questions as of today. Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response. This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database. If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
--> Post comments for expert of your intention to delete and why
--> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.
For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process. https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.
Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added. When you grade the question less than an A, please comment as to why. This helps all involved, as well as others who may access this item in the future. PLEASE DO NOT AWARD POINTS TO ME.
To view your open questions, please click the following link(s) and keep them all current with updates.
https://www.experts-exchange.com/questions/Q.20185150.html
https://www.experts-exchange.com/questions/Q.20185271.html
https://www.experts-exchange.com/questions/Q.20292502.html
***** E X P E R T S P L E A S E ****** Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
POINTS FOR EXPERTS awaiting comments are listed in the link below
https://www.experts-exchange.com/commspt/Q.20277028.html
Moderators will finalize this question if in @14 days Asker has not responded. This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
Thanks everyone.
Moondancer
Moderator @ Experts Exchange
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept answer from simonet
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
Paul (pnh73)
EE Cleanup Volunteer
I will leave a recommendation in the Cleanup topic area that this question is:
Accept answer from simonet
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
Paul (pnh73)
EE Cleanup Volunteer
didn't agree
Please can you explain why you dont agree, then I may consider your objection.
Paul (pnh73)
EE Cleanup Volunteer
Paul (pnh73)
EE Cleanup Volunteer
Close App1.(using Main.DB)
CreateTable Temp.DB (identitical to Main.DB table, except blob field)
Copy Main.DB to Temp.DB
Drop Main Table.
Rename Temp.DB -> Main.DB
Hope this helps.
Good luck!!