convert binary to text for a Crystal Report 2

[lpa]

I am using a database that has a field defined as image. The data in the field is binary.

Practically speaking, the only data that could be entered into the field is text (in the application the field actually opens an RTF editor). For what it is worth, I have spoken to the vendor about why then did this, and received no useful information.

I want to put the data on a Crystal Report (9.0 - another vendor limitation...) From what I read in the CR forums, it does not appear that I can do it there, at least with this version (possibly with purchase of 3rd party system).

So I am trying to accomplish it in the SQL database (SQL 2000). I reviewed post: 183-1213505 which describes converting the binary data to an array using dotnet and then re-building, but I'm stuck w/o dotnet, and don't have the experience or resources.

I've tried the following, but this really provided no useful output (just the 1's and 0's). I just put it in to try and illustrate the data:

SELECT dbo.drlicense.dr_nbr, dbo.drlicense.lic_dcmnt, CAST(CAST(CAST(dbo.drlicense.lic_dcmnt AS binary) AS bit) AS varchar) AS Expr1, dbo.doctor.dr_mnc

FROM dbo.drlicense INNER JOIN dbo.doctor ON dbo.drlicense.dr_nbr = dbo.doctor.dr_nbr

Any thoughts? Is the dotnet mechanism the only way?

Thank you -

 

[SQLDenis]

image is not the same as binary

declare @d varbinary(10)
select @d =convert(varbinary(10),'abc')

select @d,convert(varchar(10),@d)

output
0x616263 abc

yes you will have to read that (in chunks) by using ado.net or some other data provider


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[gmmastros]

If you start off with binary data, why are you converting it to binary. Worse yet, you then convert it to bit (which will return a 1 or 0) and then convert it to varchar (without specify the size). I recommend you try...

[COLOR=blue]SELECT[/color] dbo.drlicense.dr_nbr, 
       dbo.drlicense.lic_dcmnt, 
       [COLOR=#FF00FF]CAST[/color](dbo.drlicense.lic_dcmnt [COLOR=blue]AS[/color] [COLOR=blue]varchar[/color](8000)) [COLOR=blue]AS[/color] Expr1, 
       dbo.doctor.dr_mnc
[COLOR=blue]FROM[/color]   dbo.drlicense 
       [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] dbo.doctor 
         [COLOR=blue]ON[/color] dbo.drlicense.dr_nbr = dbo.doctor.dr_nbr

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

>>I am using a database that has a field defined as image. The data in the field is binary.

George he is using an Image datatype with data stored as binary (basically written as a binary stream to the db)

create table blaImage (test image)

insert blaImage 
select convert(varbinary(400),'abcd')

select convert(varchar(400),convert(varbinary(400),test)),* from blaImage

this will fail

select convert(varchar(400),test),* from blaImage

Server: Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type image to varchar is not allowed.

he is storing documents which are stored in binary format, there is no point converting them to varchar since they have to be streamed back over ado.net/ado




Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[gmmastros]

OK. But what about converting to binary first. Do you think it would work then? Using your example...

select Convert(VarChar(8000), convert(varbinary,test)),* from blaImage

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

remember he is storing documents, which word document can you find on your PC that is less than 8K? Smallest one I have is 18K (damn formatting)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[lpa]

Thank you both SQLDenis and gmmastros -

I tried this as well:

SELECT     dbo.drlicense.dr_nbr, dbo.drlicense.lic_dcmnt, CONVERT(varchar(8000), CONVERT(varbinary, dbo.drlicense.lic_dcmnt)) AS Expr1, 
                      dbo.doctor.dr_mnc
FROM         dbo.drlicense INNER JOIN
                      dbo.doctor ON dbo.drlicense.dr_nbr = dbo.doctor.dr_nbr

and the data returned was:

{\rtf1\ansi\deff0
{\fonttbl{\

My sense based on what I see being returned and the info you both have provided is that I need to be looking at ado.net or other provider (or going after the vendor to fix this....)

Apprecaite your insight.

lpa

 

[gmmastros]

Try specifying a size for the verbinary conversion. As Denis stated, if your document is larger than 8000 bytes, it will get truncated.

[COLOR=blue]SELECT[/color] dbo.drlicense.dr_nbr, 
       dbo.drlicense.lic_dcmnt, 
       [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]varchar[/color](8000), [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]varbinary[/color][!](8000)[/!], dbo.drlicense.lic_dcmnt)) AS Expr1, 
       dbo.doctor.dr_mnc
[COLOR=blue]FROM[/color]   dbo.drlicense 
       [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] dbo.doctor 
         [COLOR=blue]ON[/color] dbo.drlicense.dr_nbr = dbo.doctor.dr_nbr

The output you are seeing is RTF codes.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lpa]

mmmmm - I like this. The data users enter in here will probably be short enough that I will be ok with this as a solution.

Good way to end a Friday -

Thanks VERY much!

 

[gmmastros]

The problem with this solution is that the document will sliently truncate itself, and your users will never know. I don't know about you, but I'd never allow this to happen. At a minimum, I recommend you use this code...

[COLOR=blue]Select[/color] [COLOR=#FF00FF]DataLength[/color](Test), * [COLOR=blue]from[/color] BlaImage

[COLOR=blue]SELECT[/color] dbo.drlicense.dr_nbr, 
       dbo.drlicense.lic_dcmnt, 
       [COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]DataLength[/color](dbo.drlicense.lic_dcmnt) > 8000
            [COLOR=blue]Then[/color] [COLOR=red]'Document too large to display'[/color]
            [COLOR=blue]Else[/color] [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]varchar[/color](8000), [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]varbinary[/color](8000), dbo.drlicense.lic_dcmnt)) 
            [COLOR=blue]End[/color] [COLOR=blue]AS[/color] Expr1, 
       dbo.doctor.dr_mnc
[COLOR=blue]FROM[/color]   dbo.drlicense 
       [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] dbo.doctor 
         [COLOR=blue]ON[/color] dbo.drlicense.dr_nbr = dbo.doctor.dr_nbr

Notice the Case statement. If the length of the data exceeds 8000 bytes, then a hard-coded message is returned. Otherwise, you will see your document.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lpa]

gmmastros - This is good info as a reminder

In my case, I spoke with the user and we have a high degree of confidence that she will never enter more than a couple lines.

To suppress the rtf control codes, I created a number of fields in CR (6) (probably could have done this in SQL too, but I am more comfortable in CR) to display the text after the "\dn0" string.

I used the default in CR to only display 1 line of text, and sized it so that it would display a max line size.

In case the user entered more than 6 lines of text (and to gmmastros point concerning truncation), the last line tells the user to go back to the application directly for additional data, alerting that the report is not displaying all there is...It could still truncate in SQL before the report evaluates, but the leading control characters seemed to be very consistent, so I think I will be ok there.

Ugly, but I think it will work for this application.

Thanks all for your assistance.

lpa