In response to our request, one of our vendors has recently
provided us with DDFs for an old btrieve database/application.
The rep tells us that he is using a proprietary tool for generating
DDFs. Unfortunately, his tool doesn't seem to generate an
INDEX.DDF file and we're going to have to live with it.
Nevertheless, the FIELD.DDF and FILE.DDF seem to work fine
and I can see the tables and their fields.

My thoughts are that I can probably create the INDEX.DDF by
using the Create table utility to examine the indexes in the
btrieve files, then do a CREATE INDEX for each index on each
table using the IN DICTIONARY option so as not to overwrite the
btrieve files. Will this work? Will the index get used properly?

Thanks,

Bill Ehrreich
Hollywood, FL

Sent via www.billmiami.com

You are correct -- creating a CREATE INDEX ... IN DICTIONARY statement
should work well. As with anything, you should always have a backup of
your DDF's and data files before starting this task. Better yet, work
on a test server or at least a private directory.

You should create th indices in the same order in which they appear at
the Btrieve level for best results. When done, run the Check Database
Wizard to check your work.

I wrote a utility a while back that would create the proper index.ddf
records based on the Btrieve files, for a similar situation with DDF's
built from a COBOL application for the insurance industry.

It MIGHT work for your situation, too, but can be rather complex to run,
and I've not used it for years. Perhaps you can send me the DDF's and a
few (preferably empty) data files and I'll see if I can get it to work
again...
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: March, 2004: See our web site for details!


Bill Ehrreich wrote:

Thanks for the reply Bill.

By the way, I have one of your primers on Pervasive on my
desk from a training session that a co-worker attended. That
and the Pervasive documentation have been very helpful to me.

work on a test server or at least a private directory<<

Absolutely. In fact, when I added indexes to the dictionary for
one table, I checked the btrieve file and I thought it was
changed, even though it shouldn't have been. Therefore, I'm not
sure that I can trust the IN DICTIONARY clause to leave the
underlying btrieve file untouched.

situation with DDF's built from a COBOL application for the
insurance industry.

It MIGHT work for your situation, too, but can be rather complex
to run, and I've not used it for years. Perhaps you can send me
the DDF's and a few (preferably empty) data files and I'll see if I
can get it to work again...<<

Absolutely. It's certainly worth a try, because there are many
files and I certainly don't want to spend days creating indexes. I
suppose that I can send you empty datafiles by Running a
DELETE FROM <table> but will the remaining file be adequate
for your needs?

One more question, by the way. Is it possible for the presence
of certain other non btrieve files in the same folder as the btrieve
files to cause the SQL engine to have difficulty interpreting the
database? It would seem the me that the FILE.DDF should be
able to tell the engine which files comprised the database and
which didn't but I seem to remember a few hiccups until I
cleared a number of miscellaneous files from the folder.

Bill

Sent via www.billmiami.com

Bill Ehrreich wrote:

Great! From what company, if you can say?

Actually, no, since it will leave the tables very large. Instead, use
the command:
BUTIL -CLONE TEMP\filename filename
Supply your own filenames (with extensions), obviously, and make sure
that the TEMP directory exists first.

Highly doubtful. The database doesn't even look at a file until the
application
tries to open it. There'd be no need to open up non-Btrieve files, so
it should
have no impact. I'd suspect either a real neat coincidence, or perhaps
there
was corruption within the directory entry table that was messing with
it.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: March, 2004: See our web site for details!

Jorge B. is the person. You have a couple of his utilities posted
on your site.

BUTIL -CLONE TEMP\filename filename
Supply your own filenames (with extensions), obviously, and
make sure that the TEMP directory exists first.<<

Why would deleting all the records leave the tables very large?

I haven't looked at all of these btreive functions. Perhaps I
should.

Bill

Sent via www.billmiami.com

When Btrieve deletes records, it marks the freed pages as empty, and
will re-use them the next time it needs space. As such, deleting all
records from a file does NOT shrink the file in any way, and it doesn't
save any disk space. If you delete a lot of records (i.e. purge data),
you should rebuild the files if you wish to reclaim physical disk
space. (The database will eventually re-use the space, but you can cut
backup times and free up resources in the meantime.)
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: March, 2004: See our web site for details!


Bill Ehrreich wrote:

In reading a bit more, I see that space formerly used for deleted
records is not classified as "free space".

In fact, I should have said TRUNCATE TABLE rather than
DELETE FROM to ensure that the space is reused, but in
looking at the documentation, I don't see TRUNCATE to be a
valid command in Pervasive SQL.

Bill

Sent via www.billmiami.com

Sorry. I meant that it IS classified as free space.




Sent via www.billmiami.com

Nope -- there's no way to truncate a Pervasive.SQL data table froim the
SQL side, short of dropping & re-creating the table.

You CAN do this from the Btrieve level with a BUTIL -CLONE and some file
renames & deletes, but this is not often worthwhile. You can also use
the Rebuild utility to reclaim this deleted space, but only with PSQL7
and higher.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: March, 2004: See our web site for details!


Bill Ehrreich wrote:

Bill,

As it turns out, the DDFs that the vendor sent to me were useful
after all, in spite of your initial assessment.

I was able to identify the three or four tables that I needed. I
then created IN DICTIONARY indexes that did indeed make use
of the existing indexes in the btrieve files. This had to be done
very carefully using the correct names for the indexes and by
specifying the index exactly as it was in the btrieve file.
Otherwise, the index would not get used and the SQL engine
would do table scans. I used the Create Table wizard to expose
the existing indexes and my list of field names to indentify the
fields on which they were based.

I was able to write stored procedures to access the files. I must
say that writing these was rather clumsy. For example, I was
not able to modify a stored procedure once I had created it. I
would get a btrieve error 4 each time. Instead, I had to drop the
procedure entirely and then recreate it with my revised
definition. I had similar problems with views although I didn't use
them much.

Using MS Access, I created a user interface to the btrieve data.
I used pass-through queries and ADODB to access the data.
Everything worked fine and data was retrieved reasonably
quickly on about 500,000 records or more. I also tried linking
directly to tables via ODBC links and using MS Access bound
forms. This seemed to work fine although I decided not to use
this approach in the end.

One thing to note is that I am not attempting to write to the
existing btrieve files--I write only to an additional table/file that I
created. Perhaps if I did try to write to the files, the overlapping
and incorrectly defined fields in my DDF would begin to cause
problems. I haven't experimented with it so I can't say at this
moment.

Bill Ehrreich

Sent via www.billmiami.com