Table size?

I have a table with a lot of Varchar fields.

Like: VARCHAR (10000)

But it seems that I am running out of space on this table.

When a try to add a new varchar field (10000), I get this error:



"fmCompile.trCompile:
This operation is not defined for system tables.
Unsucceddfull metadata update.
New record size of 69412 bytes is too long.
TABLE MYTABLE"


Any help?

There is a row width limit of 64k BYTES. ie Unicode chars will be half, etc.

Easy fix, just use BLOBS - each field uses 10 bytes ( it points to the real data)

see this:

URL

cheers - dave

You can use BLOBs for larger text columns, however, they cannot be indexed, easily searched and should be updated/inserted in different ways.

A more important and logical question would be:
Why on earth do you need VARCHAR(10000) columns?

Hi upscene & wagga:


Thanks for your reply.


I need the big varchar fields to store some graphics settings in.
I don't generete the settings, I just need to store them.

I will try the Blob field later.

Thanks for now.

In that case, you really should use BLOBs. But are these settings this large?

Well, good luck.

Hi upscene:

Are there a special way to handle BLOB fields when update/insert?


This:
UPDATE myTABLE SET settings = "TEST"

dont work.


Error:
Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements.
feature is not supported.
BLOB and array data types are not supported for move operation

First of all, don't use double quotes ("value") but single quotes for values ('value'). With double quotes, Firebird thinks you're using a column name.

Second, BLOBs can be updated/inserted directly if you're using text blobs (BLOB SUB_TYPE TEXT), but others require you to use prepared statements:

INSERT INTO ... (MYBLOB) VALUES (:MYBLOB)

Prepare the statement.

(In Delphi)
Use something like:
ParamByName('MYBLOB').LoadFromStream(etc)

Hi upscene:

Again thanks.


I use Database workbench as database editor.
Really great product.

I am quit new to Firebird, and I want to optimize my speed/mem usage on my database.
Maybe you can help me explain some of these terms:


- Page size
- Allocated pages
- Page buffers
- Sweep interval


Thanks

Regards
Thomas

Hi Thomas,

>>I use Database workbench as database editor.
>>Really great product.

Thank you for your kind comments.

I advise you to download the InterBase 6 documentation (from www.ibphoenix.com) - most of it is still valid for Firebird.

Short explanations:
>> Page size

Each database is build from "pages". Blocks of database-data with a certain size. Depending on your average record length, one page might be better than another. Most people use 8192 bytes as their page size.

>> Allocated pages

The number of pages used for your database.

>> Page buffers

The number of pages Firebird will allocate as cache memory when someone connects to this database.

>> Sweep interval

Check this elsewhere, I'm unable to explain this quickly.