VisFileReadBinary with BLOB in MySQL

General discussion forum about all databases other than SqlBase.
olvin

VisFileReadBinary with BLOB in MySQL

Post by olvin » 28 Jun 2010, 15:49

Hello,

When I read a binary file and store it in a medium blob column in MySQL, I have 2 bytes more than I have in the file.

Here is a sample.

Olvin
You do not have the required permissions to view the files attached to this post.

Jeff Luther

Re: VisFileReadBinary with BLOB in MySQL

Post by Jeff Luther » 30 Jun 2010, 00:42

Have you checked string length before inserting into the DB? I don't have MySql for testing but "2 bytes" sounds like it could be end-of-file chars.

Well, maybe not, because I see you're reading in an INI file but storing as binary -- SqlSetLongBindDatatype( 1, 23 ) -- in a BLOB DB column type, looks like. Plus reading into a binary var. Not sure about that. INI is text so I'm not sure what the side-effects are and in fact the 2 extra chars. might be the end-of-string 2 bytes since you really do have a string. (The INI is text, right?)

olvin

Re: VisFileReadBinary with BLOB in MySQL

Post by olvin » 30 Jun 2010, 08:28

Hi,

Yes, I have checked binary length :
- When I use VisFileReadBinary, I sent correct binary length
- But after reading, I have two more bytes to the length of the binary variable

And, yes ini is a text file, but it's just a sample. I have the same with a jpeg file.

Olvin

tlpub

Re: VisFileReadBinary with BLOB in MySQL

Post by tlpub » 16 May 2011, 17:15

Hello,

during investigations on ways to store/retrieve Binary data in Oracle I found this thread. While it's almost one year old it seems to me that the issue described still exists ( at least in CTD52 PTF2)

I need this to check in/check out files into BLOB columns from an Oracle Table and thus the 2 extra bytes found just after VisFileReadBinary are problematic.

Did someone at Unify had a chance to find a solution for the described problem.

Thanks in advance for any help.
Thierry.

Jeff Luther

Re: VisFileReadBinary with BLOB in MySQL

Post by Jeff Luther » 16 May 2011, 18:08

Old thread indeed. Sounds like this '+2' bytes is to be expected for TD v5.2. That 2 bytes is the end-of-string character. That's why when you call this function in TD to set some pre-defined buffer length you will pass to an ext. function:

call SalSetBufferLength( sBuffer, nSomeLength+2 )
Call someExtFunct( sBuffer, nSomeLength, ... )

you set for that EOS character, but pass the 'base buffer length' to the ext. function.

Yes, I just tested this:

Code: Select all

On SAM_AppStartup
   Set s = 'this is a test'
   Set n = SalGetBufferLength( s )
   Set n = n
'n' = 30 bytes, for a string whose length (in chars.) = 14. So, 30 = (14*2) + 2 for EOS char.

It seems your code will need to take into account this 2-byte different between 'real' buffer length that that returned in a SAL function because it includes these extra 2 bytes.

tlpub

Re: VisFileReadBinary with BLOB in MySQL

Post by tlpub » 17 May 2011, 08:23

Thank you Jeff for this quick answer. I'll stick to that method.

BTW, still during my BLOB usage experiments I'm now facing another annoying issue which is that I never have a chance to store more than 65536 bytes of data in my BLOB field. A file smaller than 65336 bytes will be correctly stored/retrieved, but anything above this will be truncated.

I played with the longbuffer setting in the oragtwy section of sql.ini without any visible effect (changed from default 32768 to 1000000).

Found this thread : https://support.guptatechnologies.com/supportforum/viewtopic.ph ... fer#p20041

which looks similar but does not help me ...

Thanks for any hint.
Thierry.

Jeff Luther

Re: VisFileReadBinary with BLOB in MySQL

Post by Jeff Luther » 17 May 2011, 21:18

65536 -- odd number, 2^16. Something's going on since a BLOB can be much larger than that:
http://www.orafaq.com/wiki/BLOB

I'd need a test case. Let's see:
[oragtwy]
longbuffer=1000000

You are calling SqlSetLongBindDatatype( ..., 23 ) right, between SqlPrepare and SqlExecute for INSERT/UPDATE?
And calling it after prepexe but before SqlFetchNext?

What's the size of the var. containing the value before the INSERT? Use SalGetBufferLength() to find out. How are you reading it in? You better be using SalFileRead.

BTW, here's another fine example of where a test case can be of tremendous help! Lots easier than trying to guess what's in the 'black box' :D

tlpub

Re: VisFileReadBinary with BLOB in MySQL

Post by tlpub » 18 May 2011, 07:54

ooops ... I checked again and found that I was not playing with the correct sql.ini

Once having longbuffer correctly set in the correct sql.ini, everything goes fine.

Thank you again a lot for your help.

Thierry

Jeff Luther

Re: VisFileReadBinary with BLOB in MySQL

Post by Jeff Luther » 19 May 2011, 00:13

Thanks for the update. Glad you found it!
I was not playing with the correct sql.ini
Yes indeed, a BIG "GOTCHA" that has plagued users (including me!) for nearly 23 years. (SQLWindows v1.0, called the "Developers' Release", was shipped SEPT, 1988, FYI.)

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests