Hi there,

I was wondering whether anyone might be able to offer some help or
tips on a problem I'm having with creating Pervasive SQL definitions
for existing Btrieve files.

I have no problem defining the table's field structure. Where I'm
having a problems is getting Pervasive to recognise and match indexes
within the Btrieve file. The Btrieve files I'm using are generated by
a third party RAD. The RAD originally had its own backend, then later
an option was added to convert tables across to Btrieve. For
consistency with the RAD's builtin collation sequence the Btrieve
files are generated with two ACS files (one for case sensitive/one for
insensitive).

The problem i'm having is this: if a btrieve file contains two ACS
lists Pervasive refuses is unable to match indexes that reference the
second ACS list, despite the PervsasiveSQL index definition being
correct.

Here is some code to illustrate the problem.

(Note: The ACS UPPER2.ALT is simply a copy of the sample provided by
Pervasive, with the name changed. Make a copy of the sample, the open
it up in and change the name of the ACS in the first 9 bytes to UPPER2
as well)

// Step 1 - create basic table and indexes
CREATE TABLE MyTable
(
CaseYs CHAR(10) NOT NULL COLLATE 'C:\PVSW\Samples\upper.alt',
CaseNo CHAR(10) NOT NULL COLLATE 'C:\PVSW\Samples\upper2.alt'
)#
CREATE INDEX xCaseYs ON MyTable(CaseYs)#
CREATE INDEX xCaseNo ON MyTable(CaseNo)#


// Step 2 - drop the table definition in Pervasive, leave the btrieve
file (MyTable.MKD)
drop table MyTable in dictionary#


// Step 3 - recreate the table defintion in Pervasive, using the
previously created btrieve file
CREATE TABLE MyTable using 'MyTable.MKD'
(
CaseYs CHAR(10) NOT NULL COLLATE 'C:\PVSW\Samples\upper.alt',
CaseNo CHAR(10) NOT NULL COLLATE 'C:\PVSW\Samples\upper2.alt'
)#
CREATE INDEX xCaseYs IN DICTIONARY ON MyTable(CaseYs)#
CREATE INDEX xCaseNo IN DICTIONARY ON MyTable(CaseNo)#

The definitions for the indexes in the btrieve files should look
something like this (from butil -stat MyTable.mkd)

<snip>
Key Position Type Null Values*
ACS
Segment Length Flags Unique Values
0 1 1 10 String RMD --
0 0
1 1 11 10 String RMD --
0 1

Alternate Collating Sequence(ACS) List:
0 UPPER
1 UPPER2
</snip>

The index, xCaseNo, based over the second ACS file, upper2.alt, is not
matched by Pervasive when the table definition is added again in Step
3. The indications I'm using to come to this conclusion are:
- The Query Profile Viewer (w3sqlqpv.exe) shows that the index
xCaseNo is not used to optimise queries over the field CaseNo after
Step 3.
- The value of Xi$Number in the system table X$Index for xCaseNo does
not match the key number for the equivalent Btrieve index, discovered
by running "butil -stat MyTable.MKD"

This behaviour illustrated here exactly mirrors the problems I'm
having with getting Pervasive to match indexes in the Btrieve files
generated by the RAD I'm using.

I would be very grateful if anyone be able to offer any suggestions
for this problem. I've searched the usual locations (Pervasive KB,
help files, this newsgroup and Google), for tips on this without any
success. If, in the meanwhile, i discover what's happening (or make
further progress) i'll repost. If i've left any relevent information
out please let me know and i'll post it.

many thanks in advance

Andrew

Woops. Forgot to add version and OS details

I've tested the problem described previously on PervasiveSQL Server
edition 8.6 and 9.1 and the results are the same.
I'm currently running the server on WindowsXP, but have tested the
problem on Windows 2k as well.
If there is any configuration information that i've ommitted please
let me know and i'll post it

Andrew

Don't use the existing file in step 3!

Try it like this:
-- Step 3
CREATE TABLE MyTable USING 'temp.mkd' (...);
CREATE INDEX ...; -- NO "IN DICTIONARY"
CREATE INDEX ...;
-- At this point the value of Xi$Number in the system table
-- X$Index is correct.
butil -stat temp.mkd and butil -stat MyTable.mkd should be the same.

ALTER TABLE MyTable IN DICTIONARY USING 'MyTable.mkd';

del temp.mkd

Rokson

Hi there Rokson.

thank you very much that's solved the problem.

regards

Andrew