Binary file store to Oracle BLOB column got extra 2 bytes

General discussion forum about all databases other than SqlBase.
gatekeeper
Hong Kong
Posts: 3
Joined: 19 May 2020, 07:20
Location: Hong Kong

Binary file store to Oracle BLOB column got extra 2 bytes

Post by gatekeeper » 20 May 2011, 10:48

I'm trying to store a binary file to BLOB column using Oracle 10g, after I insert to database,
i find that DBMS_LOB.GETLENGTH(blob_column) is 2 bytes more than the file size.

I used the following code to test:
If VisFileOpen(hFile, psSrcFile, OF_Read|OF_Binary) = VTERR_Ok
Call VisFileReadBinary(hFile, sImage, VisFileGetSize(psSrcFile))
Call VisFileClose(hFile)
If SqlPrepare(phSql, "Insert Into PT_FORM_IMAGE (FILE_NAME, IMAGE)
Values (:psSrcFile, :sImage)")
Call SqlSetLongBindDatatype(3, 23)
If SqlExecute(phSql)
Call SqlCommit(phSql)

I set a breakpoint to trace the code, i found that VisFileGetSize(psSrcFile) for example is 1000,
then after calling VisFileReadBinary function, i got SalGetBufferLength(sImage) = 1002

How come a binary variable need to append 2 bytes of end of string char?

It is weird if I read back the BLOB column from database to a file, then the file size will differ from the original file.

Jeff Luther

Re: Binary file store to Oracle BLOB column got extra 2 bytes

Post by Jeff Luther » 25 May 2011, 23:28

How come...
Those 2 bytes are the end-of-string character. And, yes, you read 1000 bytes, but the buffer into which they are read has a length of 1000+2, as SalGetBufferLength() tells you. That's how TD knows where the end of the string/buffer is. This should not affect you storing or retrieving that binary file in Oracle, though.

gatekeeper
Hong Kong
Posts: 3
Joined: 19 May 2020, 07:20
Location: Hong Kong

Re: Binary file store to Oracle BLOB column got extra 2 bytes

Post by gatekeeper » 07 Jun 2011, 08:50

I'm concerning if i upload and download the file repeatly from database, the file size will be increased extra 4 bytes for 1 cycle.
(i.e. uploading file give extra 2 bytes and downloading the file back will be another 2 bytes).

Jeff Luther

Re: Binary file store to Oracle BLOB column got extra 2 bytes

Post by Jeff Luther » 07 Jun 2011, 17:25

"I'm concerning if i upload and download the file repeatedly..." -- I suggest testing this because I don't think this will happen. It should not, unless your code somehow is recalculating this extra size each time, causing TD to think that file each time is another 2 bytes longer each time with a call to SalSetBufferLength(), for example.

I have never heard of this before and the 2 EOS bytes has been in since v5.2. If you test this with a small test case -- and I recommend this -- and you do find a problem, let us know what it is and you already have a test case so zip/rar that and attach it here.

In effect, the definition for a buffer size is:
Useable/actual size: X bytes
Buffer length in TD: X+2 bytes

gatekeeper
Hong Kong
Posts: 3
Joined: 19 May 2020, 07:20
Location: Hong Kong

Re: Binary file store to Oracle BLOB column got extra 2 bytes

Post by gatekeeper » 08 Jun 2011, 03:31

Attachment is the testcase, the database is Oracle 10g and the table definition in the case is
CREATE TABLE "REP"."PT_FORM_IMAGE" ("FILE_NAME" VARCHAR2(20 byte)
NOT NULL, "IMAGE" BLOB)
TABLESPACE "PWLG" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 500K NEXT 500K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING
MONITORING LOB("IMAGE") STORE AS ( TABLESPACE "PWLG"
STORAGE ( INITIAL 500K NEXT 500K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
ENABLE
STORAGE IN ROW
NOCACHE CHUNK 4096 PCTVERSION 10) ;
You do not have the required permissions to view the files attached to this post.

Jeff Luther

Re: Binary file store to Oracle BLOB column got extra 2 bytes

Post by Jeff Luther » 09 Jun 2011, 00:20

Thanks for the test case. I ran a modified version against SQLBase, see the same thing, and asked the TD internal tech guys about this. Feedback:

This +2 bytes for buffers variables in TD (String, Long String, Binary) is something that will need to be accounted for and handled in your code. Earlier you asked:
How come a binary variable need to append 2 bytes of end of string char?
As I wrote, because TD needs to know where that end-of-string/buffer is.

In my changing your test a bit, I see that:
Set nFileSize = VisFileGetSize(sFile)

returns the 'correct' size for your PDF file. And if that nFileSize is a value that you need to keep and/or maintain there are a couple ways I'm sure to code in this value that you need to preserve:
* Your code would account for that in the SalFileWrite() call and substract -2 on when writing:
After the SqlFetchNext() call...
Set nBufferSize = SalGetBufferLength(sImage)
Call SalFileWrite(hFile, sImage, nBufferSize-2 )

* You could have another DB column in that table, like BUFF_LEN and on INSERT/UPDATE keep the value of nFileSize, gotten above in the VisFileGetSize() call.

Because this is how TD is coded internally to manage strings/buffers, this +2 isn't considered a bug. Instead, SAL code needs to handle and remove that as necessary in the app's code.

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests