Limitation of BLOB size?

hi,
I have a BLOB field in one of my table. Below is the script I use to create the DB:
CREATE TABLE FILES (
FILEID INTEGER NOT NULL,
BIN_DATA BLOB SUB_TYPE 0 SEGMENT SIZE 65536,
FILENAME VARCHAR(50),
FILETYPE VARCHAR(50),
FILESIZE VARCHAR(10),
PRIMARY KEY (FILEID));

with my program interface, I am able to upload a file which is below 64KB. Else, the system will give me an error message:
Dynamic SQL Error SQL error code = -104 Unexpected end of command

Is the size of the blob field only can handle file size not more than 64K? If yes, how to increase the blob size? Else, what is the segment size??

thanks in advance.
gshanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kacorretiredCommented:
From the IB handbooks:

Rather than storing BLOB data directly, a BLOB column stores a BLOB ID. A BLOB ID is a unique numeric value that references BLOB data. The BLOB data is stored elsewhere in the database, in a series of BLOB segments, which are units of BLOB data that are read and written in chunks. InterBase writes data to a BLOB one segment at a time. Similarly, it reads a BLOB one segment at a time.

The BLOB data referenced by the BLOB ID.

When a BLOB column is defined in a table, the BLOB definition can specify the expected size of BLOB segments that are written to the column. Actually, for SELECT, INSERT, and UPDATE operations, BLOB segments can be of varying length. For example, during insertion, a BLOB might be read in as three segments, the first segment having length 30, the second having length 300, and the third having length 3.

The default segment length is 80. Normally, an application should not attempt to write segments larger than the segment length defined in the table; doing so overflows the internal segment buffer, corrupting memory in the process.

It is very important:
The segment length setting does not affect InterBase system performance!!
Choose the segment length most convenient for the specific application. The largest possible segment length is 32 kilobytes (32,767 bytes) when using gpre.

But: in the examples the segment sizes are  mostly between the default 80 and 512. My recommendation is to leave this value at max 512. Try it.

wbr Janos
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kacorretiredCommented:
A Blob is a dynamically sizable datatype that has no specified size.
InterBase uses the segment length setting to determine the size of an internal buffer to which it writes Blob segment data.
My recommendation is to leave this value at max 512 undependent whether the expectable BLOB-size is 1K or 2MB.

For more information about Blob handling, see the Embedded SQL Guide.

wbr
Janos
0
kacorretiredCommented:
If I remember well the value 64K covers the range 0...64535 therefore the above mentioned value of 64536 is illegal. The needed higher size will be reached by using of n segments (the segments haven't necessarily the same size!! - as I told earlier). The number of segments will be determined automatically for each BLOB-field and can differ for the same column record for record (because of it's dynamical size).

I hope this all will help you.

Janos
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gshanAuthor Commented:
Hi kacor,
From my understanding after I read the Interbase Embedded SQL Guide, BLOB fields does not have size limitation. Because Interbase itself know how to split the input file into appropriate segment and create a blob id in the blob field, am i right?

Then, the problem I face is not because of Blob size, isn't it?
0
kacorretiredCommented:
Hi gshan,

yes, you have right. But you overlooked my 3rd comment about the illegal value of 64536.

Janos
0
kacorretiredCommented:
if you try with segment size = 64535 would be good I mean
0
gshanAuthor Commented:
Kacor,
I think the cover range of 64K should be 0....65536, right? (64KB = 64 X 1024 = 65536)

If the setting of DB is correct, why I still get the error message when I insert a file more than 64KB into it? That's the reason I suspect the segment size that I define for the BLOB type restrict my database to handle file more than 64KB.

Back to your first comment, "an application should not attempt to write segments larger than the segment length defined in the table", could you explain it in more detail?

In my application, I only know the file size I try to save into the DB. How could I know the segment size will be use by my application?

0
kacorretiredCommented:
again,

You think wrong. The range of numbers includs every number in this range:
the first is 0,
the second is 1
the 3rd is 2
...
...
the 65535th is 65534
the 65536th is 65535      this is the last element of a range which contains the 0 too. This is very important. The algebraic number systems have everytime the 0 therefore for example in the decimal system the first element is the zero (0) and the one-millionth element is the number 999 999 and not the 1 000 000. This would be the 1 000 001th element.

try the followings:
in hexadecimal system you can cover 65536 statement included the zero (H0000) and go up to HFFFF.
which can be the highest number. The position values are as follows:
the smallest   has 1
1 step left: 16
2 step left: 256
3 steps left 4096

in hexadecimal system F is equal 15. therefore the decimal value of the hexadecimal HFFFF is

         15 by 1                      =              15
         15 by 16                      =           240
         15 by 256                      =       3840
         15 by 4096                      =    61440
================================
                                                      65535

But somewhere in the documentation (#12706775) is wrtitten that " The largest possible segment length is 32 kilobytes (32,767 bytes) ".  This is one of the contredictions of InterBase. Try not step over this value and I am sure you'll get a good result.

You asked you wanted to know ther segment size in your application. It is not needed I mean. The system makes it automatically and it's size can vary between the min and max value.

waiting you kind answer.

Janos
0
nitinmahajan80Commented:
Hi,
Can you now able to store file upto 2 MB in interbase database?
Please share your approach with example.

Thanks
0
kacorretiredCommented:
Hi nitinmahajan80,

BLOB datatype in interbase is a dynamically sizeable data type which has not predefined size and coding.
With the following command is created a table with 4 BLOB fields:

CREATE TABLE BlobTest
          (
          BLOBField1 BLOB sub_type -1 segment size 32767,
          BLOBField2 BLOB sub_type 1 segment size 80,
          BLOBField3 BLOB sub_type 0,
          BLOBField4 BLOB
          );

Field 1 is a user defined -1 BLOB field with 32767 bytes segment size.
Field 2 is text type BLOB field with 80 bytes segment size.
Field 3 an4 are BLOB subtypes 0 fields without structure with default 80 bytes segment size.

wbr Janos
0
kacorretiredCommented:
The BLOB size is limited by op system
0
kacorretiredCommented:
or by the used file system
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.