Problem:
Using "CAST xxxx AS BLOB" removes null bytes from within the value that gets passed to the blob field.


Example-1:
The following SQL works correctly...
    UPDATE  MyTable
           SET  my_blob = CAST( 'ABC' + #13 + 'XYZ'  AS BLOB )
     WHERE  my_id = 1;

...the blob field ends up with 8 bytes:  3 letters + carriage return + 3 more letters + an ending Null byte
       "A", "B", "C", [CR], "X", "Y", "Z", #0


Example-2:
However, the following SQL does not work correctly...
    UPDATE  MyTable
           SET  my_blob = CAST( 'ABC' + #0 + 'XYZ'  AS BLOB )
     WHERE  my_id = 1;

...the blob ends up with 8 characters, but the null byte (#0) gets removed from the middle.  
       "A", "B", "C", "X", "Y", "Z", #0, #0

(I'm guessing the extra #0 at the end is just null padding due to the length of the blob being correct.)


Question:
How can I prevent null byte(s) from being removed when using CAST xxxx AS BLOB ?

(BTW, I am familiar with using parameters but they are not able to be used in this specific scenario.)

Thanks,
-Peter

Should I re-phrase the question?

TIA,
-Peter





Peter M. wrote:

Problem:
Using "CAST xxxx AS BLOB" removes null bytes from within the value that gets passed to the blob field.


Example-1:
The following SQL works correctly...
    UPDATE  MyTable
           SET  my_blob = CAST( 'ABC' + #13 + 'XYZ'  AS BLOB )
     WHERE  my_id = 1;

...the blob field ends up with 8 bytes:  3 letters + carriage return + 3 more letters + an ending Null byte
       "A", "B", "C", [CR], "X", "Y", "Z", #0


Example-2:
However, the following SQL does not work correctly...
    UPDATE  MyTable
           SET  my_blob = CAST( 'ABC' + #0 + 'XYZ'  AS BLOB )
     WHERE  my_id = 1;

...the blob ends up with 8 characters, but the null byte (#0) gets removed from the middle.  
       "A", "B", "C", "X", "Y", "Z", #0, #0

(I'm guessing the extra #0 at the end is just null padding due to the length of the blob being correct.)


Question:
How can I prevent null byte(s) from being removed when using CAST xxxx AS BLOB ?

(BTW, I am familiar with using parameters but they are not able to be used in this specific scenario.)

Thanks,
-Peter

Peter


>Should I re-phrase the question?

I think its more that we can't answer it

All I can suggest is email Tim directly.

Roy Lambert [Team Elevate]

Peter,

I also don't know the answer, just want to add that the #0 is trimmed out from the string constant before casting, no matter you are going to cast it as blob or not.
Apart from using streams to write to the blob instead of SQL, which is kind of a smart ass answer I also can't think of any solution.

--
Fernando Dias
[Team Elevate]

Peter,

<< Using "CAST xxxx AS BLOB" removes null bytes from within the value that
gets passed to the blob field. >>

Due to the textual nature of SQL, you can't do what you want without using a
parameter.  Fernando is correct, the concatenation of the NULL byte to the
string causes the string to be terminated there *before* it is converted
into a BLOB.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Thanks to everyone for their responses...

Since I'm storing JPG images in the BLOB fields, and since I cannot use Parameters in this scenario,
I'll either convert each JPG to a Hex string or a JSON string and store them in a Memo field (vs. BLOB).

(I realize this will require more bytes and bandwidth to store the data but that is not an issue in this scenario.)

Thanks again,
-Peter



"Tim Young [Elevate Software]" wrote:

Peter,

<< Using "CAST xxxx AS BLOB" removes null bytes from within the value that
gets passed to the blob field. >>

Due to the textual nature of SQL, you can't do what you want without using a
parameter.  Fernando is correct, the concatenation of the NULL byte to the
string causes the string to be terminated there *before* it is converted
into a BLOB.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com