Restructure table

[sbell]

What is code to add new field to table?

 

[Langley]

Below is a cut-n-paste from the old Opal newsgroup.
---------------------------------------------------

Example of using the restructure method in Paradox 8 or 9. The code takes advantage of the Customer.db table found in the Samples folder.

var
tbl Table
tcFlds TCursor
dynNewStru DynArray[] Anytype
endvar

tbl.attach( "Customer.db" ) ;Customer.db in the Samples folder
tbl.enumFieldStruct( "field_struct.db" )

tcFlds.open("field_struct.db" )
tcFlds.edit()

scan tcFlds :
if tcFlds."Field Name" = "Name" then
tcFlds."Field Name" = "Company Name"
quitLoop
endif
endscan

;//delete Phone field from Customer table
scan tcFlds :
if tcFlds."Field Name" = "Phone" then
tcFlds.deleteRecord()
quitLoop
endif
endscan

;//insert field Test before the Country field
scan tcFlds :
if tcFlds."Field Name" = "Country" then
tcFlds.insertBeforeRecord()
tcFlds.(1) = "Test" ;field name
tcFlds.(2) = "ALPHA" ;field type
tcFlds.(3) = 10 ;size
quitLoop
endif
endscan

;//add field Test2 at the end of table
tcFlds.end()
tcFlds.insertAfterRecord()
tcFlds.(1) = "Test2" ;field name
tcFlds.(2) = "ALPHA" ;field type
tcFlds.(3) = 10 ;size

tcFlds.endEdit()
tcFlds.close()

dynNewStru["FIELDSTRUCT"] = "field_struct.db"
tbl.restructure( dynNewStru )

{Invalid array of validity check descriptors in most cases means that the
table structure has been changed by inserting a column anywhere but as the
last column. When you do, field name references sometimes refers to another

column than the one you expect. Try to Rebuild the table.

Anytime you change the structure of a table by removing columns and adding
columns, rebuild the table. The only valid place for a new column is as the

last column.

runExpert( "CoreUI", "TableRepair&quot}


-----------------------------------------------

Mac
Mac

"Strange women lying in ponds and distributing swords is no basis for a system of government" - Dennis, age 37

mailto:langley_mckelvy@cd4.co.harris.tx.us

 

[footpad]

sbell,

Mac's example should work fine, however, keep in mind that you need to keep such changes fairly basic. In my experience, you can add and delete fields, however, things get a little dicey when you try to add fields, indexes, valchecks, and referential integrity rules in a single call to the Restructure() method. YMMV.

If you need to perform more extensive structural changes, it may be better to use a different technique. For example, SQL Queries support the ALTER TABLE command. (See the LOCALSQL Help file in your \Program Files\Common Files\Borland Shared\BDE directory for details.)

In addition, you can also replace tables on the target machine. The basic process is:

1. Create the final structure on your development machine.

2. Distribute it to the target machines by copying it to the target machine's temporary directory.

3. Transfer the data from the existing table into the new table using a script or other ObjectPAL method. Handle problem data as needed.

4. Once the data is moved, replace the old table with the new one.

It seems like a lot of work and I suppose it is, but it does work. You do need to make certain that you also update any documents (forms and reports) that depend on the table you're modifying, but that's another issue.

Hope this helps...

-- Lance

 

[sbell]

I added 2 new fields to end of answer table to hold data selected by users who scroll a table displayed on a form.

Then I put the fields on a report. I did not see how to make WriteEnvironmentString() work. Then I decided I didn't want to use it in this case anyway because I want the selected data saved with the answer table, which I renamed to keep it for more than one session.

Thanks loads! I am delighted at the response time and results.

PS. I'm a finance/administration manager who designed and wrote our multi-state sales/production/inventory control program, and not a real programmer. Paradox user/fan since 1992.