Record size exceeds page size

[dfbosse]

I'm trying to create a large table (12000 bytes), but keep getting the error "record size exceeds page size". It appears the maximum page size (I'm using 2000i SP4) is 4096 bytes. Is there any way to create a table greater than 4096 bytes. Thanks!

 

[mirtheil]

Are you using SQL or Btrieve API to create the file? Either way, you're going to need to specify the file as COMPRESSED to get a fixed length record larger than about 4088.


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[dfbosse]

I'm new to Pervasive. I believe Btrieve API. When I go into the table designer for my file (I can create it using a script), under Statistics, Data Commpression is set to Yes. Is this what you are referring to?

Thanks for the response!

 

[mirtheil]

If Data Compression is already turned on, then the file should've been created.
WHen you say you "can create it using a script", are you talking about a CREATE TABLE statement?
If so, can you post the script?


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[dfbosse]

Yes, I am using a CREATE TABLE statement like this:

CREATE TABLE SO_RELEASE
(SO_ID INTEGER NOT NULL,
ITEM_NO INTEGER NOT NULL,
...(many more fields)...
MODIFIED_DATE TIMESTAMP NULL)

The table is created when I use (post) this statement. However, when I try to open the table (from my vb class), it fails horribly. If I attempt to add an index, for example, I get the error "record size exceeds page size". When I enter 15 columns, all varchars, length 254, I get the same error.

 

[mirtheil]

What you'll need to do is:
CREATE TABLE SO_RELEASE DCOMPRESS
(SO_ID INTEGER NOT NULL,
ITEM_NO INTEGER NOT NULL,
...(many more fields)...
MODIFIED_DATE TIMESTAMP NULL)
For more information about DCOMPRESS, see

http://www.pervasive.com/library/794/whatsnew/osandsql6.html#95766

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[dfbosse]

Unfortunately, that didn't help. The problem is still there. The table is created, as it did when I didn't use DCOMPRESS. I still get the same error when I try to add an index from the table designer, or open the file from my vb class.

 

[mirtheil]

Using the following statements, I was able to add an index using Table Designer (or in script):

create table compressed (
f1 char(254),
f2 char(254),
f3 char(254),
f4 char(254),
f5 char(254),
f6 char(254),
f7 char(254),
f8 char(254),
f9 char(254),
f10 char(254),
f11 char(254),
f12 char(254),
f13 char(254),
f14 char(254),
f15 char(254))
#
create index idxF1 on compressed (f1)

What's the data type of the field you are trying to add the index on? Where in the file is the field? Are you in Linked or Unlinked mode? What happens if you issue a Create Index statement instead?
Can you post the whole Create Table statement?
What do you mean by "when I try to open the table (from my vb class), it fails horribly"? What the actual error?


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[dfbosse]

Please add 3 more fields (get up to f18) to your create table statement. This will get the record length over 4096. Then, from Edit Table Design, add an index. When you save it, you should (at least I do) get the error "Record size exceeds page length".

My index field in an integer, but any data type fails. This is also true of the location of the field in the file. Every field fails when I try to use it to create an index.

I don't know. How can I tell if I'm Linked or Unlinked?

Create Index, like Create Table, works from Table Designer (without error).

Yes, I can post the whole create table statement. The statement executes successfully. If I were to create the table in Edit Table Design, using the sample you suggested, I get "Record size exceeds page length".

When I try to open the table from my vb class, vb shuts down entirely. I do not know the actual command used to open the table as we use a database engine developed internally. Once I realized the "Record size exceeds page length", I assume this is causing the vb shut down.

Thank you!

 

[mirtheil]

With 20 fields, I see the same behavior you are seeing on adding an index with Table Designer. You might want to report this to Pervasive.
As far as the VB program, if VB shuts down, it sounds like there's no error handling. What happens if you run the application within the VB IDE? What operation fails?


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[dfbosse]

Thanks for the advice. I reported this to Pervasive. They confirmed that the record lenth cannot exceed the 4k page size.

One suggestion was to use "longvarchar" field types instead of varchar(). This works great from the pervasive point of view. However, in my application, I cannot insert data into longvarchar type fields. I can insert/update to these fields from table designer, so I know it has something to do with my database engine.

Thank you for all your help!