[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Limitation of BLOB size?

Posted on 2004-11-30
14
Medium Priority
?
15,082 Views
Last Modified: 2013-12-09
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.
0
Comment
Question by:gshan
  • 9
  • 2
12 Comments
 
LVL 10

Accepted Solution

by:
kacor earned 200 total points
ID: 12706775
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
 
LVL 10

Expert Comment

by:kacor
ID: 12707515
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
 
LVL 10

Expert Comment

by:kacor
ID: 12709677
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:gshan
ID: 12713134
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
 
LVL 10

Expert Comment

by:kacor
ID: 12713925
Hi gshan,

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

Janos
0
 
LVL 10

Expert Comment

by:kacor
ID: 12714998
if you try with segment size = 64535 would be good I mean
0
 

Author Comment

by:gshan
ID: 12722868
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
 
LVL 10

Expert Comment

by:kacor
ID: 12784649
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
 

Expert Comment

by:nitinmahajan80
ID: 32796451
Hi,
Can you now able to store file upto 2 MB in interbase database?
Please share your approach with example.

Thanks
0
 
LVL 10

Expert Comment

by:kacor
ID: 32807738
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
 
LVL 10

Expert Comment

by:kacor
ID: 32807748
The BLOB size is limited by op system
0
 
LVL 10

Expert Comment

by:kacor
ID: 32808510
or by the used file system
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question