Interesting Problem

[Ross27]

Imagine a table in Microsoft Sql Server 2000 named Pictures with three
fields. A primary key ID(int), a Name(nvarchar) and a Picture(image)
field.

Lets put some records into the table. The first two fields as expected
would take an int and a string. The third field is of type image.
Instead of putting a bitmap in this field lets place an xml document
that has been streamed into a byte array. The xml document would
describe the Picture using say lines. If the picture was that of a
square we would have four lines in the xml document. You could think of
the xml document as something similar to vector graphics but the
details are not relevant. The important fact is that the contents of
the image field is NOT a bitmap but a binary stream of an xml document.

Now imagine we have a reporting tool like Crystal Reports that can be
used to report on this database table. Imagine we create a report by
using the three fields mentioned above. As far as Crystal is concerned
the first field is an int, the second a string and the third an image.
If our table had ten records and we preview the report we like to see
10 entries each consisting of an ID, Name and a Picture.

This can only happen if the image field contains a Bitmap, but as
mentioned above the field contains an xml document.

Now my question...

Can we write something in SQL Server 2000 (not 2005) to sit between the
table and Crystal Reports so to convert the XML document to a bitmap.
The restriction is that we cannot use anything but sql server itself.
The client in the above case has been Crystal Reports but it could be
anything.

I know SQL Server 2005 supports C# with access to the .NET framework
within the database. Unfortunately, I am not using SQL server 2005.

Some people have suggested the use of User Defined Functions and TSQL.
I like to know from the more experienced SQL Server people if what I am
trying to achieve is possible. Maybe it has not been done but is it
possible?

Any suggestions would be greatly appreciated...

Many Regards

[Simon Hayes]

Translating an XML byte stream to a bitmap dynamically sounds like
something which is probably beyond pure TSQL. Even if it is possible
somehow, I suspect that the code would be complex and slow - TSQL isn't
really a general purpose language, and its support for manipulating
binary data is limited.

Having said that, there are a couple of ways you might be able to
approach this - extended stored procedures, and COM support. An
extended proc is an external DLL which can be called from TSQL, rather
like a more basic version of the .NET support in 2005. Alternatively,
you can use the sp_OA% procs to instantiate COM objects, so if your
logic can be written as a COM object, then you can use it from TSQL.
Check Books Online for more details on both these options.

Although I don't have much experience with extended procs, the COM
support is probably not a good solution, because of security and
performance issues. The best approach is almost certainly to do this in
client code, rather than the database - perhaps you can look at
embedding something in Crystal, instead of in the database?

Simon

[David Portas]

Perhaps it would be possible to write an extended stored proc and call
that from a function. That of course assumes that Crystal Reports
provides some method of rendering a bitmap returned from a query.

I can't think of a good reason to do this in SQL Server. It's obviously
better suited for the client or middle-tier.

--
David Portas
SQL Server MVP
--

[Ross27]

Hi Simon

Thanks for your suggestions. I think I like the Extented Stored
Procedure(xp) route more and I can certainly create a dll in C++ to
convert an XML document containing the description of the square to a
bitmap of the square (say 100x100 pixels by default). Once I add this
xp to sql server I need to attach it to my xml field in some way so
that when Crystal requests the content of the image field in the
Pictures table instead of it returning the binary array representing
the xml document it returns the on-the-fly generated bitmap.

Is there a way I could intervene in what sql sends to crystal in
respect to the Picture field using the xp? Do I need to use triggers?
Basically I am trying to fool crystal that the Picture field contains a
Bitmap. This needs to happen when the Crystal does a select I
suppose...

Probably its worth mentioning why I am trying to do this at all....

We have a drawing package that allows users to draw shapes. We can
store these as an XML document and restore them. Whats more we have a
control that can render these documents directly. Thus in order to
preview the XML document all that is needed is the control which is
self contained. The control also scales the preview of the shape.

We therefore can store the XML for this document in the database and
not worry about also storing a preview bitmap of the shape in the same
row. This means there is less danger of the preview bitmap field going
out of syn with the xml document and also avoids data redundancy. Whats
more preview bitmaps are fixed in size and dont scale well. This
approach solves all these problems.
However, as databases are often reported on and that Crystal Reports is
a leading reporting tool I like our database to work well with Crystal
when wanting to create reports that need the picture field (that
contains the xml document).

This is exactly why I need to the conversion at SQL as our customers
could do reporting directly from the database using Crystal Reports.
Embedding within Crystal is also not an option as the reporting tool
could change.

Many Thanks in Advance

[Ross27]

Thanks Dave

Please look at my reply to Simon for reasons why I am trying to do
this...
Problem now is how to fool Crystal to get the Bitmap from the xp as
oppose the xml from the field...

Any comments would be appreciated...

Regards..

[David Portas]

> Is there a way I could intervene in what sql sends to crystal in

respect to the Picture field using the xp? Do I need to use triggers?

You could call your XP from a user-defined function and return the
result as a binary column. Put the UDF in a view and query the view
from Crystal. There are no triggers on SELECT so this is the only
method I can think of.

--
David Portas
SQL Server MVP
--