Database images not showing

How should I store an image into my SQL database in order to be able to read
it using Reporting Services Image control? I have a table with an image
field (datatype of the field is 'Image').
I have tried several ways and the report is always rendered with the image
showing a red cross inside it (image broken). However if I read the table
using MS Access ADP and double click on the image field, MS Paint opens and
shows up the image (this is a bmp image). I have also set the MIMEType of
the rs image field to 'image/bmp' (filling this field this is compulsory
with database images).
It seems that, even though the database field contents ('Image' field) are
properly stored, Reporting Services does not know how to handle it. May I
need to do a CONVERT(Binary, MyImageField) or the database field be of
another type? How should I store the image inside the database so that RS
could read it?
Regards.
PS: This is RS SP2.This sounds like the images are stored as OLE images in the database (e.g.
Access would convert images into OLE images). You can try the following
expression to get rid of the OLE chunk:
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
105))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
> How should I store an image into my SQL database in order to be able to
> read it using Reporting Services Image control? I have a table with an
> image field (datatype of the field is 'Image').
> I have tried several ways and the report is always rendered with the image
> showing a red cross inside it (image broken). However if I read the table
> using MS Access ADP and double click on the image field, MS Paint opens
> and shows up the image (this is a bmp image). I have also set the MIMEType
> of the rs image field to 'image/bmp' (filling this field this is
> compulsory with database images).
> It seems that, even though the database field contents ('Image' field) are
> properly stored, Reporting Services does not know how to handle it. May I
> need to do a CONVERT(Binary, MyImageField) or the database field be of
> another type? How should I store the image inside the database so that RS
> could read it?
> Regards.
> PS: This is RS SP2.
>|||Thanks for your reply but it seems that there should be another reason. Your
explanation is on the good road, but
I have tried your expression, with that 105 varying from 100 to 110 with the
same results. The red cross is still there.
Any other suggestion? Regards.
PS: Just for further debugging, I have an output of the first 950 bytes
generated by the expression:
=System.Convert.ToBase64String(Fields!Picture.Value)
Here they follow:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
> This sounds like the images are stored as OLE images in the database (e.g.
> Access would convert images into OLE images). You can try the following
> expression to get rid of the OLE chunk:
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
> 105))
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read the
>> table using MS Access ADP and double click on the image field, MS Paint
>> opens and shows up the image (this is a bmp image). I have also set the
>> MIMEType of the rs image field to 'image/bmp' (filling this field this is
>> compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field be
>> of another type? How should I store the image inside the database so that
>> RS could read it?
>> Regards.
>> PS: This is RS SP2.
>|||More information on the subject:
I have been doing more tests with this sample bitmap. I have sent it to
myself via email just to read the source code of the message and extract the
base64 of the bitmap. The header of attachment and some hundreds the
begining bytes are here:
--_=_NextPart_001_01C55532.6E34D77F
Content-Type: image/bmp;
name="PHOTO.BMP"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="PHOTO.BMP"
Qk1gSAAAAAAAAHYAAAAoAAAAzwAAALEAAAABAAQAAAAAAAAAAAAQFwAAEBcAAAAAAAAAAAAAAAAA
AP///wD6+voA8/PzAOfn5wDb29sAzMzMALm5uQCjo6MAi4uLAHNzcwBZWVkAQkJCAC8vLwAfHx8A
Dw8PABERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
EREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERARERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
EREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERER
ERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
etc...
If I copy this base64 chunk of data and set the Reporting Services image
field to:
=System.Convert.FromBase64String("the chunk")
The image is shown perfecly on the report.
However, if I compare some bytes of the begining of this chunk (which is
shown to be correct) and try to find it somewhere on the string returned by:
=System.Convert.ToBase64String(Fields!Picture.Value)
which is:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
I can't find it anywhere. There is no 'Qk1gS' substring in it. Now my
question are: Are there multiple ways of converting a file into a base64
string? How can I remove the OLE header of the database if I cannot find the
matching of the image REAL data using this 'comparing' approach?
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:%23AtcAySVFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply but it seems that there should be another reason.
> Your explanation is on the good road, but
> I have tried your expression, with that 105 varying from 100 to 110 with
> the same results. The red cross is still there.
> Any other suggestion? Regards.
> PS: Just for further debugging, I have an output of the first 950 bytes
> generated by the expression:
> =System.Convert.ToBase64String(Fields!Picture.Value)
> Here they follow:
> FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
> mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
>> This sounds like the images are stored as OLE images in the database
>> (e.g. Access would convert images into OLE images). You can try the
>> following expression to get rid of the OLE chunk:
>> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
>> 105))
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
>> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read
>> the table using MS Access ADP and double click on the image field, MS
>> Paint opens and shows up the image (this is a bmp image). I have also
>> set the MIMEType of the rs image field to 'image/bmp' (filling this
>> field this is compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field
>> be of another type? How should I store the image inside the database so
>> that RS could read it?
>> Regards.
>> PS: This is RS SP2.
>>
>|||Instead of doing tests with the final image, I have created a 5x5 pix bitmap
to work/test with.
The Base64 encoding of it (grabbed from an email sourcecode is):
Qk2GAAAAAAAAADYAAAAoAAAABQAAAAUAAAABABgAAAAAAFAAAAAAAAAAAAAAAAAAAAAAAAAA////////////////////AP///wAA/////////wAA/wD///////////////////8A////////AAD/////AAD/AAAA/////////////////wA=
It works perfectly if I set the value property of the rs image to:
=System.Convert.FromBase64String("the former base64 string")
Now, the database version of the same file: I set a texbox in the RS report
and set its value to =System.Convert.ToBase64String(Fields!Picture.Value) so
that I could retrieve the complete base64 of the image stored in the
database. Here it is:
FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAoAAAAEJNhgAAAAAAAAA2AAAAKAAAAAUAAAAFAAAAAQAYAAAAAABQAAAAAAAAAAAAAAAAAAAAAAAAAP///////////////////wD///8AAP////////8AAP8A////////////////////AP///////wAA/////wAA/wAAAP////////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABBQAAAAAAAIqtBf4=
By other means, I have been able to revert this base64 string back to binary
and here are the results:
9 + ÿÿÿÿImagen de mapa de bits Paint.Picture PBrush
BM? 6 ( P ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿ
ÿÿÿÿÿÿÿ ÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿ ÿÿÿÿ ÿ ÿÿÿÿÿÿÿÿÿÿÿÿÿ
S­ þ
Of course, I have some rubbish here. But not everything is lost: Now I can
see that a localized language string is included in the OLE chunk before the
'BM' indicating the begining of the bitmap file. And now the final question:
Should I use other string than
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
105))
when the locale of the systems/servers/software is not english? In my case
I'm using spanish and since the string 'Imagen de mapa de bits' is not as
long as 'Bitmap image file', I think that 105 is not valid/correct when
other languages are used.
After some calculations I think that the OLE chunk size for spanish (which
is my case) is 121 instead of 105. However the red cross is still there!!!!!
:(((
Another aproach, instead of letting RS cropping the image ole data, is to
retrieve the image from the database without it. Is it feasible to retrieve
just a 'substring' of the image?
Please help. I really need it. Thaks.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:%23AtcAySVFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply but it seems that there should be another reason.
> Your explanation is on the good road, but
> I have tried your expression, with that 105 varying from 100 to 110 with
> the same results. The red cross is still there.
> Any other suggestion? Regards.
> PS: Just for further debugging, I have an output of the first 950 bytes
> generated by the expression:
> =System.Convert.ToBase64String(Fields!Picture.Value)
> Here they follow:
> FRw5AAIAAAAXAA4AFAArAP////9JbWFnZW4gZGUgbWFwYSBkZSBiaXRzAFBhaW50LlBpY3R1cmUAAQUAAAIAAAAHAAAAUEJydXNoAAAAAAAAAAAAYEgAAEJNYEgAAAAAAAB2AAAAKAAAAM8AAACxAAAAAQAEAAAAAAAAAAAAEBcAABAXAAAAAAAAAAAAAAAAAAD///8A+vr6APPz8wDn5+cA29vbAMzMzAC5ubkAo6OjAIuLiwBzc3MAWVlZAEJCQgAvLy8AHx8fAA8PDwAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBEREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREQERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERAREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREREBERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERERER
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> escribió en el
> mensaje news:eL5pK2LVFHA.612@.TK2MSFTNGP12.phx.gbl...
>> This sounds like the images are stored as OLE images in the database
>> (e.g. Access would convert images into OLE images). You can try the
>> following expression to get rid of the OLE chunk:
>> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),
>> 105))
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
>> news:e5gQDOJVFHA.584@.TK2MSFTNGP15.phx.gbl...
>> How should I store an image into my SQL database in order to be able to
>> read it using Reporting Services Image control? I have a table with an
>> image field (datatype of the field is 'Image').
>> I have tried several ways and the report is always rendered with the
>> image showing a red cross inside it (image broken). However if I read
>> the table using MS Access ADP and double click on the image field, MS
>> Paint opens and shows up the image (this is a bmp image). I have also
>> set the MIMEType of the rs image field to 'image/bmp' (filling this
>> field this is compulsory with database images).
>> It seems that, even though the database field contents ('Image' field)
>> are properly stored, Reporting Services does not know how to handle it.
>> May I need to do a CONVERT(Binary, MyImageField) or the database field
>> be of another type? How should I store the image inside the database so
>> that RS could read it?
>> Regards.
>> PS: This is RS SP2.
>>
>

Database getting slower for every BLOB object

Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:

>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
>

Database getting slower for every BLOB object

Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.
How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:

>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.
|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
[vbcol=seagreen]
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:

Database getting slower for every BLOB object

Hi,
I have a table in my database storing BLOBS (images), and I have
noticed something strange. For every record that goes into the table,
the next insert takes longer to perfrom. It used to be that inserting
a small blob used to take about .25 of a second, but now its upto a
second.
I create a test database (copy of my live) and created a test
application that inserted the same images over and over again and this
is my results:
(starting from 1750 images):
Inserting next 250 images = 267 seconds
Inserting next 250 images = 284 seconds
Inserting next 250 images = 296 seconds
Inserting next 250 images = 307 seconds
Inserting next 250 images = 319 seconds
and so on.
Is this typical of SQL Server (2005)? On Oracle, after the first 250
items were added, it kind of settles down and the insert time repains
fairly constant, no matter how many images I insert.How are you adding the BLOBs? Try chunking the updates--that usually
helps speed things up.
--Mary
On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
wrote:
>Hi,
>I have a table in my database storing BLOBS (images), and I have
>noticed something strange. For every record that goes into the table,
>the next insert takes longer to perfrom. It used to be that inserting
>a small blob used to take about .25 of a second, but now its upto a
>second.
>I create a test database (copy of my live) and created a test
>application that inserted the same images over and over again and this
>is my results:
>
>(starting from 1750 images):
>Inserting next 250 images = 267 seconds
>Inserting next 250 images = 284 seconds
>Inserting next 250 images = 296 seconds
>Inserting next 250 images = 307 seconds
>Inserting next 250 images = 319 seconds
>and so on.
>Is this typical of SQL Server (2005)? On Oracle, after the first 250
>items were added, it kind of settles down and the insert time repains
>fairly constant, no matter how many images I insert.|||Hi Mary,
I actually created a test application to insert 3000 blobs, clear the
database, and do it again. The 1st run was with no chunk size (just
appendchunk as one massive block), then the next test was large chunks
(32k), the next was at 16kb, and the next at 4kb. There was not a
large difference between them in terms of the time taken by SQL to add
the blob row (16kb was the fastest, but only saved about 2% in relation
to the massive delays from SQL Server).
Mary Chipman [MSFT] wrote:
> How are you adding the BLOBs? Try chunking the updates--that usually
> helps speed things up.
> --Mary
> On 10 Jan 2007 08:34:10 -0800, "JaffaB" <jaffa_brown@.yahoo.co.uk>
> wrote:
> >Hi,
> >
> >I have a table in my database storing BLOBS (images), and I have
> >noticed something strange. For every record that goes into the table,
> >the next insert takes longer to perfrom. It used to be that inserting
> >a small blob used to take about .25 of a second, but now its upto a
> >second.
> >
> >I create a test database (copy of my live) and created a test
> >application that inserted the same images over and over again and this
> >is my results:
> >
> >
> >(starting from 1750 images):
> >
> >Inserting next 250 images = 267 seconds
> >Inserting next 250 images = 284 seconds
> >Inserting next 250 images = 296 seconds
> >Inserting next 250 images = 307 seconds
> >Inserting next 250 images = 319 seconds
> >and so on.
> >
> >Is this typical of SQL Server (2005)? On Oracle, after the first 250
> >items were added, it kind of settles down and the insert time repains
> >fairly constant, no matter how many images I insert.

Database driven images not showing in non-english systems

I'd like to share with the community the results of 3 days of testings and
headaches. I finally found the reason for the problem and a way to solve it:
As stated in
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/migratereports.mspx
dealing with 'How to Migrate Access Reports to Reporting Services', when
talking about database-stored images, the writer (Adam Cogan) says:
[...]
3. Replace the default value in the image Value field with the following
expression as shown in Figure 16:
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),105))
[...]
However this piece of code does not work under certain conditions. I have
seen several forum pages of people posting they are having the same problem
(see
http://forums.devarticles.com/archive/t-11844/displaying-image-fields-in-reporting-services )
. The former code takes the assumption that the images stored in the
database were saved by a English/American localised application. If you are
using an English version of MS Access, do copy a bitmap file and paste it
into an image field of SQL table (using ADP Access project), MS Access adds
an OLE header and trailer to the image you are storing. In the English case,
the header is 78 bytes and trailer 38 bytes. However, this header is not of
that size if you are using another language. In my case, we use spanish
applications and the OLE header for bitmap files is 88 bytes instead of 78
(the trailer is also 38 bytes but you can forget about it).
If you use the former piece of code in your reports and you are not using an
English OS and applications you will see red crosses on your reports (this
is an assumption of my own because at least in the Spanish case the header
size differs from English; I suppose the same might happen in German,
French, etc.).
The reason of this behaviour is because when converting those 78 bytes of
english header into a Base64 string, it turns to be exactly 104 Base64
chars:
78 bytes * 8 bits each = 624 bits
If you convert that stream of 624 bits using Base64 (6 bits each char):
624 bits / 6 bits = 104 Base64 chars
So the next Base64 char (105) belongs completely to the bitmap file data and
you don't experience any problem if you use the Mid function to cut the
string by 105th char and then FromBase64String to convert the Base64 string
back to a byte() (byte array).
However if you use Spanish applications, the OLE header is 88 bytes (704
bits). If you try to convert that stream of bits into a Base64 string:
740 bits / 6 bits each Base64 char = 123,333... chars
What does it mean? It means that it will use 123 chars and 2 bits of the
next char. The other 4 bits of the 124th Base64 char will have data
belonging to the bitmap file. And here is the tricky part: You CANNOT remove
the OLE header without turning the whole string into rubbish because of
this. Thats why you always see red crosses on your reports no matter the
number you use to remove the OLE header from the Base64 string. No matter if
you use 105, 104, 103, or 123, 124, etc. It will always break the Base64
string and when you convert it back to get the image you will get just
rubish.
THE SOLUTION:
Instead of using Base64 strings to cut the OLE header and try to rebuild the
image, you should use this piece of code in your Image RS components:
=System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))
Where XXX is the size of the localised OLE header + 1.
For Spanish use XXX = 89
For English use XXX = 79
For other languages you will have to guess (you can do some trial/error
until you find out).
Why is there this difference of sizes in the OLE header? The header contains
the type description of the file (the type column when you see the file
using explorer) using your local language. In Spanish, the default value for
a bitmap file is 'Imagen de mapa de bits' (22 bytes). However in English the
type description for that kind of files (I suppose) is 'Bitmap image' (12
bytes). Hence:
22 - 12 = 10
The OLE header is 10 bytes larger for spanish applications (in the case of
.bmp files). For other type of files the size probably differs (I have not
tested it).
Instead of trial/error to find the XXX value for your local languae you can
try to do this little calculation:
78 + ( length(bitmap type description for your language) - 12) + 1
That's all. Just use:
=System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))
being XXX the value you have just calculated and forget about the original
Adam's code.
I hope this could help to people experiencing this problem and save their
valuable time. I also hope that Adam Cogan revises his article to replace
his version of the code and explains the restrictions of this alternative
also. People tend to consider documentation under microsoft.com domain to be
fully trustable, but in this case the information was totally wrong for
users that are not using English localised applications.
Regards.Thanks for sharing your findings!
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> wrote in message
news:uyB2AeiVFHA.228@.TK2MSFTNGP12.phx.gbl...
> I'd like to share with the community the results of 3 days of testings and
> headaches. I finally found the reason for the problem and a way to solve
> it:
> As stated in
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/migratereports.mspx
> dealing with 'How to Migrate Access Reports to Reporting Services', when
> talking about database-stored images, the writer (Adam Cogan) says:
> [...]
> 3. Replace the default value in the image Value field with the following
> expression as shown in Figure 16:
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),105))
> [...]
> However this piece of code does not work under certain conditions. I have
> seen several forum pages of people posting they are having the same
> problem (see
> http://forums.devarticles.com/archive/t-11844/displaying-image-fields-in-reporting-services )
> . The former code takes the assumption that the images stored in the
> database were saved by a English/American localised application. If you
> are using an English version of MS Access, do copy a bitmap file and paste
> it into an image field of SQL table (using ADP Access project), MS Access
> adds an OLE header and trailer to the image you are storing. In the
> English case, the header is 78 bytes and trailer 38 bytes. However, this
> header is not of that size if you are using another language. In my case,
> we use spanish applications and the OLE header for bitmap files is 88
> bytes instead of 78 (the trailer is also 38 bytes but you can forget about
> it).
> If you use the former piece of code in your reports and you are not using
> an English OS and applications you will see red crosses on your reports
> (this is an assumption of my own because at least in the Spanish case the
> header size differs from English; I suppose the same might happen in
> German, French, etc.).
> The reason of this behaviour is because when converting those 78 bytes of
> english header into a Base64 string, it turns to be exactly 104 Base64
> chars:
> 78 bytes * 8 bits each = 624 bits
> If you convert that stream of 624 bits using Base64 (6 bits each char):
> 624 bits / 6 bits = 104 Base64 chars
> So the next Base64 char (105) belongs completely to the bitmap file data
> and you don't experience any problem if you use the Mid function to cut
> the string by 105th char and then FromBase64String to convert the Base64
> string back to a byte() (byte array).
> However if you use Spanish applications, the OLE header is 88 bytes (704
> bits). If you try to convert that stream of bits into a Base64 string:
> 740 bits / 6 bits each Base64 char = 123,333... chars
> What does it mean? It means that it will use 123 chars and 2 bits of the
> next char. The other 4 bits of the 124th Base64 char will have data
> belonging to the bitmap file. And here is the tricky part: You CANNOT
> remove the OLE header without turning the whole string into rubbish
> because of this. Thats why you always see red crosses on your reports no
> matter the number you use to remove the OLE header from the Base64 string.
> No matter if you use 105, 104, 103, or 123, 124, etc. It will always break
> the Base64 string and when you convert it back to get the image you will
> get just rubish.
>
> THE SOLUTION:
> Instead of using Base64 strings to cut the OLE header and try to rebuild
> the image, you should use this piece of code in your Image RS components:
> =System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))
> Where XXX is the size of the localised OLE header + 1.
> For Spanish use XXX = 89
> For English use XXX = 79
> For other languages you will have to guess (you can do some trial/error
> until you find out).
> Why is there this difference of sizes in the OLE header? The header
> contains the type description of the file (the type column when you see
> the file using explorer) using your local language. In Spanish, the
> default value for a bitmap file is 'Imagen de mapa de bits' (22 bytes).
> However in English the type description for that kind of files (I suppose)
> is 'Bitmap image' (12 bytes). Hence:
> 22 - 12 = 10
> The OLE header is 10 bytes larger for spanish applications (in the case of
> .bmp files). For other type of files the size probably differs (I have not
> tested it).
> Instead of trial/error to find the XXX value for your local languae you
> can try to do this little calculation:
> 78 + ( length(bitmap type description for your language) - 12) + 1
> That's all. Just use:
> =System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))
> being XXX the value you have just calculated and forget about the original
> Adam's code.
> I hope this could help to people experiencing this problem and save their
> valuable time. I also hope that Adam Cogan revises his article to replace
> his version of the code and explains the restrictions of this alternative
> also. People tend to consider documentation under microsoft.com domain to
> be fully trustable, but in this case the information was totally wrong for
> users that are not using English localised applications.
> Regards.
>