How to change table inside DBC 1

[gguidarelli]

Sorry in advance if this was answered elsewhere, but I couldn't find anything like this...

I have a DBC with some tables in it. Some of these tables have memo fields. I just ran into a problem with the FPT file sizes becoming HUGE. Turns out the blocksize was set to 64 (the default), and after some testing, realized that a blocksize of 1 (SET BLOCKSIZE TO 0) would be best (since the data being stored in the memo fields is usually 1 char, but occasionally a lot more).

Anyway, I need to re-create this table using the new BLOCKSIZE. How do I do this while keeping its database link and all the other database-type stuff (like relations, etc.) intact?? Oh, and I want to keep all this stuff intact without having to know what it all is. Am I asking too much??

use mytable
set blocksize to 0
copy to newtable with cdx database abc
rename table mytable to oldtable
rename table newtable to mytable
* BUT! all this does is add a new table to the database, but the one I want to replace is still there with the relations pointing to it...

 

[Mike Gagnon]

gguidarelli

Is there a reason you are not doing this "visually"?
OPEN DATABASE ABC EXCLUSIVE
MODIFY DATABASE
and remove the offending table, and reset the links to the newly created table.
Mike Gagnon

 

[darrellblackhawk]

I believe you'll have to do it the hard way unfortunately.

And by the way, as a programmer, YOU have to know all the stuff! Read my signature line.

Save all the database and table(s) info, create a duplicate database, associated tables, relations, reload the data, delete the originals, and then rename the new ones.
(don't forget any stored procedures you may have)

You probably won't have to be so extreme if there is only one offending table, but you will have to re-establish all broken links. And by all means - make a backup!

Oh... and make sure to set the block size

As you know, changing the blocksize only affects the table's fpt size when it is created. (memo line width is affected by block size whatever the block size was when the table was created)

Darrell 'We all must do the hard bits so when we get bit we know where to bite'

 

[darrellblackhawk]

And as mgagnon says, do it the easy way.
'We all must do the hard bits so when we get bit we know where to bite'

 

[DSummZZZ]

Hmmm. It worked for me. Did you have your database open at the time? I set BLOCKSIZE to 0, copied my table to a new one, issued a CLOSE DATABASES, CLOSE ALL, then renamed the files. When I opened the database again, it appeared correct.

BE SURE TO WORK WITH BACKUPS!!!!
Dave S.

 

[gguidarelli]

(ugh...)
Thanks very much for the replies.

I must do this in code since the program has to be run at client sites.

I inherited this app from others who are long gone, so was hoping to write code that would be able to handle the things I don't know. I mean... even though I may not know personally what indexes a dbf has, I can write a program to properly recreate all the indexes for any dbf because foxpro gives me the tools to do that (TAG(), KEY(), etc.). So, I was looking for the same kind of thing here.

Greg

 

[gguidarelli]

Dave,

Ohhhhhh.... what I had tried first was including the indexes in the copy, but it didn't like that because the indexes were built with the long names.

So, I'll 1) save index info, 2) copy without indexes, 3) do as you said (close/rename), then 4) rebuild the indexes.

I'll let you know...

Thank you!

Greg

 

[ramani]

HI Gguidarelli,

SET BLOCKSIZE TO 0
SELECT * FROM myTable INTO DBF tMyTable FOR ! DELETED()
USE myTable EXCLUSIVE
ZAP
APPEND FROM tMyTable


SET BLOCKSIZE is scoped to the current data session. SO remember to set this in every data session.. otherwise teh default 64 will creep in again.

HOpe this helps you

Hope this helps you

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[danceman]

if everything works and
* BUT! all this does is add a new table to the database, but the one I want to replace is still there with the relations pointing to it...
if this is all that is wrong, then use the command
FREE TABLE tablename Attitude is Everything

 

[gguidarelli]

Below is the resolution I used (well, pretty much. a lot of error checking code was removed). Many thanks to Dave S (DSummZZZZ) for the basic premise:

PROCEDURE fixblksize
PARAMETER p_dbf

l_saveblock = SET("BLOCKSIZE&quot
SET BLOCKSIZE TO 0

l_savesele = SELECT()
IF NOT USED(p_dbf)
USE (p_dbf) IN 0
ENDIF
SELECT (p_dbf)

* only do this if blocksize is not 1
* note: use parameter instead of hard-coding 1!!
IF VAL(SYS(2012)) <> 1


SET BLOCKSIZE TO 0 && 1-byte block size
* create a copy -- this is where the new table will
* get the desired blocksize. But, it MUST BE added
* to the same database in order to retain any long
* field names it may have, like ours did!!
* note:
* -use parameter instead of hard-coding dbc name!
* -must first check for and delete _SAVE file if
* it already exists in the dbc!
* the name i give it is the name that will be used
* by the original when this is all done.
COPY TO (p_dbf+&quot;_save&quot WITH CDX DATABASE mydbc
* must close the database
CLOSE DATABASES ALL
* dele previous temp copies, if exist
IF FILE(p_dbf+&quot;_temp.dbf&quot
DELETE FILE (p_dbf+&quot;_temp.dbf&quot
ENDIF
IF FILE(p_dbf+&quot;_temp.cdx&quot
DELETE FILE (p_dbf+&quot;_temp.cdx&quot
ENDIF
IF FILE(p_dbf+&quot;_temp.fpt&quot
DELETE FILE (p_dbf+&quot;_temp.fpt&quot
ENDIF
* rename original file to temp name
RENAME (p_dbf+&quot;.dbf&quot TO (p_dbf+&quot;_temp.dbf&quot
RENAME (p_dbf+&quot;.cdx&quot TO (p_dbf+&quot;_temp.cdx&quot
RENAME (p_dbf+&quot;.fpt&quot TO (p_dbf+&quot;_temp.fpt&quot
* rename new file to original name
RENAME (p_dbf+&quot;_save.dbf&quot TO (p_dbf+&quot;.dbf&quot
RENAME (p_dbf+&quot;_save.cdx&quot TO (p_dbf+&quot;.cdx&quot
RENAME (p_dbf+&quot;_save.fpt&quot TO (p_dbf+&quot;.fpt&quot
* rename original files as _SAVE copies
RENAME (p_dbf+&quot;_temp.dbf&quot TO (p_dbf+&quot;_save.dbf&quot
RENAME (p_dbf+&quot;_temp.cdx&quot TO (p_dbf+&quot;_save.cdx&quot
RENAME (p_dbf+&quot;_temp.fpt&quot TO (p_dbf+&quot;_save.fpt&quot

ENDIF

SELECT (l_savesele)
SET BLOCKSIZE TO (l_saveblock)
RETURN


Dave's suggestion of setting the blocksize, copying the file, closing the database, and renaming the files would work fine if the table did not have long field names which you had to retain. Ours did, so had to put the copy in the database. The downside to all this is you have an extra table in the database. I don't want to delete it right away in case something wrong with the new one.

My apologies if I didn't explain things so well...
Thanks again to all.

Greg