Image built from varchar pieces -- Help please!
sauly
I have a problem that I have been working on most of the afternoon and
I was hoping someone here could point me in the right direction. In a
nutshell, I have a stored procedure that is receiving data from a
PocketPC device. Due to limitations of the sync, the binary image data
is broken on the handheld into smaller pieces and transmitted to the
SQL 2000 database in parts. This string is a text representation of
the hex as shown below (the numbers don't make a real JPG, but imagine
more parts and bigger strings)
@Pic_Part = '0xFFD8FFE000104A46494600010101004800480000F', @Part=1
then
@Pic_Part = '0xF4AEB7C39CE9706070381F8122ADEC44BA22C4', @Part=2
..
So I'm trying to write the stored procedure that builds the image
field "Pic" in the table "Picture" from these string parts. This is
where I am stuck. I can get it to work (as in, it inserts/appends),
but reading back the values it hasn't used the hex values. I'm not
exactly sure what it (I?) have done wrong!
Here is a snippet of the code I'm using to try and insert the second
part (the first part code is similar)
...
-- There is probably no way I could have worked any of this out
without the help of the article here --
-- http://msdn.microsoft.com/en-us/library/aa175795(SQL.80).aspx
Declare @ptrval as binary(16)
Declare @i as int
-- Get the pointer to the Pic
SELECT @ptrval = TEXTPTR(Pic)
FROM [dbo].[Picture]
WHERE Pic_ID = @Existing_Pic_ID
-- Get the current length of the PIC
set @i = ( SELECT Datalength(Pic)
FROM [dbo].[Picture]
WHERE Pic_ID = @Existing_Pic_ID)
-- Now, at last, add the part to the end of the pic
UPDATETEXT [dbo].[Picture].[Pic] @ptrval @i 0 @Pic_Part
When I read it back using ...
READTEXT [dbo].[Picture].[Pic] @ptrval 0 @i
I get ..
0x30784634414542374333394345....
and not the ..
0xFFD8FFE000104A46494600010....
I was hoping for.
Sooooo.... please, please, please. If you can see what I'm doing wrong
or can suggest a different technique I'd be very grateful. I don't use
these BLOB types much and they are doing my head in. I have tried
making @Pic_Part varchar, nvarchar and text. I have tried with, and
without, the "0x" at the beginning. I have tried converting it to
binary and back to a string (it truncates it, no good). I'm running
out of ideas. Perhaps it's impossible, although I find that somewhat
hard to believe.
Erland Sommarskog
> I have a problem that I have been working on most of the afternoon and
> I was hoping someone here could point me in the right direction. In a
> nutshell, I have a stored procedure that is receiving data from a
> PocketPC device. Due to limitations of the sync, the binary image data
> is broken on the handheld into smaller pieces and transmitted to the
> SQL 2000 database in parts. This string is a text representation of
> the hex as shown below (the numbers don't make a real JPG, but imagine
> more parts and bigger strings)
>
> @Pic_Part = '0xFFD8FFE000104A46494600010101004800480000F', @Part=1
> then
> @Pic_Part = '0xF4AEB7C39CE9706070381F8122ADEC44BA22C4', @Part=2
> ..
>
>
> READTEXT [dbo].[Picture].[Pic] @ptrval 0 @i
>
> I get ..
>
> 0x30784634414542374333394345....
>
> and not the ..
>
> 0xFFD8FFE000104A46494600010....
If you do
select convert(varchar, 0x30784634414542374333394345)
you will get a revelation. Apparently Picture is of datatype Image. But
your input is not binary data, but hexstrings. So you first need to convert
the hexstring to binary.
Unfortunately, there is no quick way to do this in SQL 2000. There is an
undocumented system-shipped user-defined function to it one direction,
but I don't recall which. But "SELECT name FROM master.dbo.sysobjects
WHERE type = 'TF'" may reveal something interesting.
Overall, working with the image type is difficult and labourously. If you
are able to upgrade to SQL 2008, you could use varbinary(MAX), and also
use built-in conversion of hexstrings.
If you cannot move on from SQL 2000, I would suggest that doing this
client-side in a regular programming language is a lot better.
Of course, one could ask if there are limitations. why the binary parts
are converted to hexstrings in the first place...
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Plamen Ratchev
> Unfortunately, there is no quick way to do this in SQL 2000. There is an
> undocumented system-shipped user-defined function to it one direction,
> but I don't recall which. But "SELECT name FROM master.dbo.sysobjects
> WHERE type = 'TF'" may reveal something interesting.
>
It is master.dbo.fn_varbintohexstr to convert binary to hex string...
Here are a couple documented methods, very easy especially on SQL Server 2008:
http://blogs.msdn.com/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx
--
Plamen Ratchev
http://www.SQLStudio.com
sauly
Firstly, thank you both for taking the time to respond to my question.
I bow to you.
Reading your replies, and the excellent links you provided, I have
come to conclusion that I need to supply the Image field with binary
data and not hex data, and that converting it in SQL 2000 is more than
a bit of a pain. I will go back to the handheld and see if I can get
the image there in binary rather than hex strings. I'm not sure if the
first version of the compact framework, which is what I am working
with there, has such a function but I'll go looking. That is not
really a question for this forum.
Thanks again and regards,
Saul