Prev: help with select
Next: Restoring German DB to US Server - Known Issues?
From: Iain Sharp on 8 Jun 2010 07:13

The application language that I use prepends # (0x25) on the front of
image data before storing it in SQL server. It handles this natively,
and can display the images 'directly' from the entity, internally.

However, I'd like to make use of them in Crystal reports.

I have tried casting the image column to varbinary(max) then
varchar(max) then using the substring to drop the first character and
recasting back to image.

select cast(
cast(
substring(stringimage,2,datalength(stringimage)-1)
as varbinary(max))
as image) as select_image
from (select
cast(
cast(foo.image as varbinary(max))
as varchar(max)) as stringimage
from foo) as data

(obviously, I have difficulty posting sample data in a non-binary
newsgroup.... )

This seemed to work O.K. for one test image, but when trying it on
others, the image stops after a while. I think the casting is
stripping 00 (NULL) characters from the image, and that's breaking the
encoding.

Is it possible to select the 2nd byte of an image or varbinary
onwards? (Rather than using string manipulation). Then I can create a
view which I can use in Crystal.

Iain
From: Dan Guzman on 8 Jun 2010 07:48
> select cast(
> cast(
> substring(stringimage,2,datalength(stringimage)-1)
> as varbinary(max))
> as image) as select_image
> from (select
> cast(
> cast(foo.image as varbinary(max))
> as varchar(max)) as stringimage
> from foo) as data

I don't see the need to convert to varchar here. You might try a CAST to
varbinary(MAX) directly:

SELECT
SUBSTRING(CAST(image AS varbinary(MAX)), 2, DATALENGTH(image)-1)
FROM dbo.foo;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Iain Sharp" <iains(a)pciltd.co.uk> wrote in message
news:ve7s069te2kff1g2suovael9uevelbs9ou(a)4ax.com...
>
> The application language that I use prepends # (0x25) on the front of
> image data before storing it in SQL server. It handles this natively,
> and can display the images 'directly' from the entity, internally.
>
> However, I'd like to make use of them in Crystal reports.
>
> I have tried casting the image column to varbinary(max) then
> varchar(max) then using the substring to drop the first character and
> recasting back to image.
>
> select cast(
> cast(
> substring(stringimage,2,datalength(stringimage)-1)
> as varbinary(max))
> as image) as select_image
> from (select
> cast(
> cast(foo.image as varbinary(max))
> as varchar(max)) as stringimage
> from foo) as data
>
> (obviously, I have difficulty posting sample data in a non-binary
> newsgroup.... )
>
> This seemed to work O.K. for one test image, but when trying it on
> others, the image stops after a while. I think the casting is
> stripping 00 (NULL) characters from the image, and that's breaking the
> encoding.
>
> Is it possible to select the 2nd byte of an image or varbinary
> onwards? (Rather than using string manipulation). Then I can create a
> view which I can use in Crystal.
>
> Iain

From: Iain Sharp on 8 Jun 2010 08:39
On Tue, 8 Jun 2010 06:48:14 -0500, "Dan Guzman"
<guzmanda(a)nospam-online.sbcglobal.net> wrote:

>> select cast(
>> cast(
>> substring(stringimage,2,datalength(stringimage)-1)
>> as varbinary(max))
>> as image) as select_image
>> from (select
>> cast(
>> cast(foo.image as varbinary(max))
>> as varchar(max)) as stringimage
>> from foo) as data
>
>I don't see the need to convert to varchar here. You might try a CAST to
>varbinary(MAX) directly:
>
>SELECT
> SUBSTRING(CAST(image AS varbinary(MAX)), 2, DATALENGTH(image)-1)
>FROM dbo.foo;

No apparent difference, either I am mistaking the root cause of the
problem, or the substring (and I tried right()) are doing an implicit
conversion to varchar or something...

I am going to try and have the application save the images to file
(which strips the #) and reload as binary data, then see if this data
displays correctly in Crystal. If it does, it's the conversion,
otherwise, it's a problem in Crystal.

Iain
From: Dan Guzman on 8 Jun 2010 08:52
> No apparent difference, either I am mistaking the root cause of the
> problem, or the substring (and I tried right()) are doing an implicit
> conversion to varchar or something...

No conversion should occur here. I ran the script below with a 10K hex
value and it ran as expected.

CREATE TABLE dbo.foo([image] image)
INSERT INTO dbo.foo VALUES(0x2500000000000000) --use a longer value to test

SELECT
SUBSTRING(CAST([image] AS varbinary(MAX)), 2, DATALENGTH(image)-1)
FROM dbo.foo;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/



From: Iain Sharp on 8 Jun 2010 08:56
On Tue, 08 Jun 2010 13:39:02 +0100, Iain Sharp <iains(a)pciltd.co.uk>
wrote:

>On Tue, 8 Jun 2010 06:48:14 -0500, "Dan Guzman"
><guzmanda(a)nospam-online.sbcglobal.net> wrote:
>
>>> select cast(
>>> cast(
>>> substring(stringimage,2,datalength(stringimage)-1)
>>> as varbinary(max))
>>> as image) as select_image
>>> from (select
>>> cast(
>>> cast(foo.image as varbinary(max))
>>> as varchar(max)) as stringimage
>>> from foo) as data
>>
>>I don't see the need to convert to varchar here. You might try a CAST to
>>varbinary(MAX) directly:
>>
>>SELECT
>> SUBSTRING(CAST(image AS varbinary(MAX)), 2, DATALENGTH(image)-1)
>>FROM dbo.foo;
>
>No apparent difference, either I am mistaking the root cause of the
>problem, or the substring (and I tried right()) are doing an implicit
>conversion to varchar or something...
>
>I am going to try and have the application save the images to file
>(which strips the #) and reload as binary data, then see if this data
>displays correctly in Crystal. If it does, it's the conversion,
>otherwise, it's a problem in Crystal.
>
>Iain

Okay, dumping the data to a file from the application and reloading it
without the # character allows Crystal to display the image correctly.
So, unless the application is doing something else to the data (and
the manual says,

/image�reads the raw data from FileName, assuming that this
data is an image. An initial hash character (#) is added to
the data before copying the data to Target. (The hash
character is an indicator to show that image data follows.) No
further conversion is performed on the data.
/raw�behaves similarly to the /image switch, except that the
data in FileName is assumed not to be an image; an initial
hash character (#) is not added. No further conversion is
performed on the data.

Data stored /image is what I have, data stored /raw is working
correctly. So I presume it's the substring() or right() that is
corrupting it....

Regards,

Iain
 |  Next  |  Last
Pages: 1 2
Prev: help with select
Next: Restoring German DB to US Server - Known Issues?