Create Table Perv 8.7 Error 5

[OhioCowboy]

I am just starting to get into our company Pervasive DB. I've got VB6 experience, but my SQL knowledge is very basic.
The following SQL code worked 6 months ago when I had to add a couple of tables to our DB app after we did an update.


sql = "CREATE TABLE SalesShipInfo USING 'SalesShipInfo.DAT' "
sql = sql & "(PBID IDENTITY, "
sql = sql & "CustID Char(20) Not NULL, "
sql = sql & "CustContact Char(30), "
sql = sql & "CustPhone Char(20)) "

Now I get an error code 5. If I remove the Identity constraint, the build works, but my data is out of sync. (the first 2 characters of each field are missing)
There are actually 20 field to the table, but the error occurs at the first field (PBID)
Any ideas?

 

[mirtheil]

Most likely, the SalesShipInfo.DAT file has a duplicate value in the PBID field. Identity fields cannot have duplicate. Post a BUTIL -STAT of the SalesShipInfo.DAT file and we should be able to confirm that.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[OhioCowboy]

The table does not exist yet. I'm creating the table now. The problem arises from the MRP database program. Every time they release an update, they issue new DFF files, which do not include the tables I have added for our addon programs we have written.
So I rebuild the DFF's to include my tables
1. create the empty tables
2. replace the newly created tables with the pre-update tables that contain our data.

I get the error message at the first line of the SQL - CREATE TABLE.

I hope this explains my situation better.

 

[OhioCowboy]

I found a method create the table.
1. create table with PBID as INTEGER
2. delete table
3. create table with PBID as IDENTITY

Can anyone explain the reason.

When the MRP was updated, is it possible they left info in the index and field tables pointing to my added tables but did not have any reference in the file DDF?

Since my tables were not in the DDF file, I could not delete them, but when I tried to create new tables there were still references to them in the index and field tables and generated errors about duplicate fields?

 

[mirtheil]

Is the MRP program updating most of the DDFs (FILE.DDF, FIELD.DDF, and INDEX.DDF, at least)?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[OhioCowboy]

I am 100 % sure they updated the files.ddf.
That is the only file I checked before I tried to add the tables.

I do not know about the FIELD.DDF or INDEX.DDF

I will have to put the original DDFs back and check to see. I know my add-on program could not find any of the tables after they completed the update.

 

[mirtheil]

FILE.DDF stores the link between the table name and file name. FIELD.DDF stores the fields for each table in FILE.DDF.
INDEX.DDF stores the indexes on the fields in FIELD.DDF.

THe DDFs are a matched set. If only one of the files was updated, the error you saw would be expected. By creating and dropping the table, you cleared out all of the entries.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[OhioCowboy]

Thank you Mirtheil.

It has been frustrating trying to restore my links.
I would get the error sometimes and other times I would not.
I am changing my rebuild program to create a plain Jane "dummy" table with one field, delete the table to let Pervasive remove all the extra entries, then create the "real" table.

Thanks again,
Paul