How to add a BLOB field in a report?

[breixo]

Hi all,

I'm trying to include a BLOB field in one of my reports but the field appears as empty (although I know the blob isn't) when I execute the report. CR says that BLOB fields can be inserted into reports as any other data type but doesnt seem to be working for me.

Many thanks

 

[IanWaterman]

You need to be more specific, what are you using
Crystal Version
Database
Connection method
What type of data is in the BLOB, is it RTF or HTML formatted.

As you say CR should display the BLOB, have you set its format to grow? You may have a lot of leading space.

Ian

 

[breixo]

Crystal Reports Professional v.10
Database: Remote MySQL database
Connection method: ODBC conn
Type of data in the BLOB: I dont know and the DBA is not telling me
I dont know how to set its format to grow.

So, basically I establish an odbc conn between CR and the database, choose one table, fields, apply one filter and run the report. Field in question is empty.

 

[IanWaterman]

To set field to grow, right click field then format field. On Common tab, check box on left hand side(bottom of a group of 3).

What have you done to upset your DBA. Never heard of that response before.

What makes you so sure there is anything thing in the BLOB if the DBA will not tell you anything about it?

Ian

 

[synapsevampire]

Blobs can contain various types of things, including embedded objects.

You need to clarify what you're expecting it to return, it could be an image, data, an embedded file, etc.

Your question is the equivalent of I have a file and when I open it it doesn't show me what is in there, although I'm certain it has something I've no idea what.

You say that you know it isn't empty, so state HOW you know this and WHAT is in there, it doesn't take much more time to be specific.

-k

 

[breixo]

well, the BLOB field in question contains strings (no images or files) which is what I expect Crystal to return but it doesnt. The reason why I know there is a string is because if a pull a query from the database using MySQL Command Line Client I see a string on the column containing the blob field but in crystal this column appears empty

 

[synapsevampire]

I would guess that there's an implicit conversion occuring within MySQL.

I would use a CAST/CONVERT (whatever MySQL supports, can't recall) in a SQL Expression to convert it to a known data type (such as varchar).

Then you can use the SQL Expression in the report as a normal field.

SQL Expressions are extremely useful and fit this bill nicely.

-k

 

[IanWaterman]

K

I have seen you recommend SQL expressions a lot, but I must be doing something wrong with them as I can only bring back totals or specific data as I cannot seem to get them to relate to the data I am drawing.

Take this example I would write my SQL expression as

(Select BlobField from Table)

If I then try to use this I get an error message on the lines SQL expression must return single record.

How would you write the SQL Ex so that it can be used to bring back a specific Blob for a specific record.

Thanks

Ian

 

[synapsevampire]

I don't know the syntax for MySQL, but in SQL Server something like:

cast(table.field as varchar(20))

Using a select within one is more complex, just consider that a SQL Expression is passed as a separate item in the SELECT of the existing query of the report and you should be fine.

So if the report had:

select field1, field2 from table under database->Show SQl Query, the result of the above would be:

select field1, field2, cast(table.field as varchar(20)) from table

Some databases and drivers require special considerations, but in general they're easy to use and very powerful for this sort of thing, plus the database does the work.

-k