Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
CAST #0 AS BLOB - Null Bytes Are Removed |
Fri, Jul 13 2012 2:42 PM | Permanent Link |
Peter M. | 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 |
Thu, Jul 19 2012 2:47 AM | Permanent Link |
Peter M. | 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 |
Mon, Jul 23 2012 3:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Jul 24 2012 3:10 PM | Permanent Link |
Fernando Dias 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] |
Thu, Aug 2 2012 3:07 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Aug 6 2012 12:30 PM | Permanent Link |
Peter M. | 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 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |