Crystal XI not showing BLOBN field

[JimmyEdwards]

Hi All,

Db - SQL 2005
Crystal XI (11.0.0.1282) Developer edition
Connecting via an ODBC connection using the SQL native driver.

I have a BLOB field in my database containing a bmp image. When I try to insert this into a crystal report it shows as a blank field (i.e. does not show the image). Here's what I've tried:-

1) Replaced the bmp with a jpg - made no difference.
2) Set the field to can grow (in case of blank space). The field size doesn't change at all, still no image displayed.
3) Tried placing the image directly in the report - the image does display properly, but I need the report to pick up the image from the database, as there is more than one possibility.
4) Tried an old version of crystal. We used to use Crystal version 4.6. I tried it in this, and it works perfectly! The image displays exactly as it should.
5) Tried printing and exporting in the hope that it just wasn't showing the image in the crystal preview screen - it doesn't show on a print or a pdf export either.

Does anyone have any idea why it won't work in crystal 11? Are there any settings relating specifically to BLOB fields hidden away somewhere? THe image in the database is obviously OK because the old version of crystal displays it OK.

Cheers

James

 

[hilfy]

IIRC, blob images won't work through an ODBC connection. You would have to connect to SQL Server through an OLE DB (ADO) connection.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[JimmyEdwards]

Hi hilfy,

thanks for the reply. I tried connecting to the database using an ADO connection, but unfortunatly I got the same result.

Any other advice from anyone?

Cheers

James

 

[TucsonOgre]

It took a lot of searching, but with the help of another programmer, I found out how to do this.

In order to get text contained in a BLOB to show up in Crystal Reports XI, you need to write the entire report in SQL (using Add Command in the Database Expert) and use this piece of code to convert the BLOB to text:

UTL_RAW.CAST_TO_VARCHAR2("MEMOS"."THEMEMO")

where "MEMOS"."THEMEMO" is the name of the table (MEMOS) and BLOB field (THEMEMO).

Here's a sample SQL statement with this in use:

SELECT MEMOS.RECID, UTL_RAW.CAST_TO_VARCHAR2(MEMOS.THEMEMO), GSP_STAFFBLOCK.STARTDATE
FROM GSP_STAFF
INNER JOIN GSP_STAFFBLOCK ON GSP_STAFF.RECID=GSP_STAFFBLOCK.STAFFID
INNER JOIN MEMOS ON MEMOS.RECID=GSP_STAFFBLOCK.NOTEID

Hope this helps.

 

[TucsonOgre]

My apologies. I got interrupted 3 times and pulled up the wrong thread. My previous reply does not apply to your problem. Sorry.