gshan
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
I hope this all will help you.
Janos
ASKER
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?
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?
Hi gshan,
yes, you have right. But you overlooked my 3rd comment about the illegal value of 64536.
Janos
yes, you have right. But you overlooked my 3rd comment about the illegal value of 64536.
Janos
if you try with segment size = 64535 would be good I mean
ASKER
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?
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?
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
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
Hi,
Can you now able to store file upto 2 MB in interbase database?
Please share your approach with example.
Thanks
Can you now able to store file upto 2 MB in interbase database?
Please share your approach with example.
Thanks
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
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
The BLOB size is limited by op system
or by the used file system
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