Printing issue with MS SQL Server BLOB (PNG) Data Fields

[Former Member]

Hi Support,

I had opened a thread long time ago related to this topic:

https://archive.sap.com/discussions/message/16743585#16743585

The issue was corrected in SP18.

Recently, I found that only JPG format is working, PNG format still facing this issue.

Can you please check it with PNG and let me know why the PNG format are not printed?

Regards,

[former_member11696 · Accepted solution]

Hi Gevorg,

That works for me. Here's what I did:

I added a VARBINARY(MAX) field to my DB then ran the below SQL:

INSERT INTO xtreme.dbo.Credit(MyPNG)
SELECT * FROM OPENROWSET(BULK N'D:\BIInstaller.png', SINGLE_BLOB) rs

I then created the report off my Xtreme database and it reads the PNG just fine using MS SQL Native 11 OLE DB provider:

So reading PNG Images directly without using a SP works for me so I assume it's reading the CRC bits.

Curious as to why they are breaking the images into parts and not storing them as a complete blob? Possibly so people can't read them without knowing?

Thanks again

Don

[Former Member · Accepted solution]

Hi Don,
Thank you very much for sharing the testing results. Can you please confirm that it is also working with ODBC driver, because the 3rd party application only supports ODBC driver?

Thanks,

Gevorg

[former_member11696 · Accepted solution]

I gave you the code on how to import an image into your DB, you coudl have tested it yourself.

Yes it works using ODBC also.

[Former Member · Accepted solution]

Hi Don,

Thank you very much for your great support and great help with the sample that I used to find out what was wrong on my SP. I fixed the issue, thanks once more time,

Gevorg

[former_member11696]

Hi Gevorg,

3. Everything is OK, file content is imported and parsed properly by SP, but besides this we need to add aditional data to BLOB to make it readable by Crystal?

You/they need to add the CRC bits according to the W3 Standards when importing the image into SQL Server. Because the Standard requires the CRC bits in PNG's CR requires them, we don't want to remove this check. The SP appears to be reading all of the data.

Thanks again

Don

[Former Member]

Hi Don,

Sorry if I'm asking additional questions, but I would like to make everything clear to me.

Is CRC bits not part of PNG file content, so that we need to add it manually?
Is it possibility to create PNG file which has already proper CRC bits in its content?

Which kind of graphic tools I need to use?

Regards,

[former_member11696]

Hi Gevorg,

CRC is part of the png file.

Check this link for more info about CRC:

http://www.w3.org/TR/PNG-CRCAppendix.html

All I know is CR Developers expect it to be there. It may be the way you are importing the image into parts that is stripping the CRC info off...

Don

[Former Member]

Hi Don,

In your last reply you have suspected that the issue might be in the Importing part, right? As I mentioned before the Import part is working well. It does not matter in which format is the imported file: JPG, PNG or other. The file content is imported without an issue. The confirmation on this is the Export part, which exports file from the database. I've checked the exported file, and it was not corrupted. If the import was done NOT properly, it is obvious that the export will fail also.

One thing that I would ask you to check with your developer, just ignore my SP and my DB. Please create your own test DB, with one sample table, where you have some PNG file imported as a BLOB yourself, and try to place this field in the Crystal Reports. If you have success on this, that will mean the issue is 100% on my side. Otherwise, you can check what is wrong on your side.

If it is not take too much time for you to make this test, I'll appreciate it a lot. Because, I still cannot find a solution for this issue.

There is another confusing thing also: As you know, you can insert picture into Crystal Reports directly from the file. When I insert the same PNG (with no CRC that you have reported previously), I don't have any issue. It seams the Crystal Reports does not check CRC for this case, the confirmation is the image shown .

I want to understand the following:

If the Crystal Reports does not support PNG files without CRC, why this limitation applies for BLOB fields only?

Thanks in advance,

Gevorg

[former_member11696]

Hi Gevorg,

Our developer checked the case and discovered there is always an error when reading the CRC bytes from the data source via the SP.

But odd that the test app can read it, I'll send it back to him to have a closer look at it...

Don

[Former Member]

OK, thank you Don, please note that SP does not separate the case of PNG from the JPG, that the same logic works to convert bytes into BLOB. If the issue was with SP, JPG also will fail.

[former_member11696]

Hi Gevorg,

Was the CRC included in the JPG when imported into SQL Server?

Chris never indicated if we used it in the JPG fix or not so just trying to get info.

Thanks again

[Former Member]

Hi Don,

Sorry, I don't understand what is CRC. Actually the files are imported to SQL by their binary content, doesn't matter which file is it JPG, PNG or any other files like .DOC, ..etc. As well as when these files are exported from the SQL to the storage, it works (the files are properly restored, they are functioning as were before importing). Also you know that the sample application that uses Picture Box to show an image it works. I'm sure if the file was corrupted, even with one byte discrepancy, the Picture box could not fix it to show the image.

Regards,

[former_member11696]

Hi Gevorg,

According to the PNG standards it is required:

http://www.w3.org/TR/PNG-Structure.html

And the reason why JPG works is because there is no requirement for CRC to be included with JPG's.

From our Developer:

I’ve check the format of PNG and JPG.

The CRC bytes only exist in the last chunk(IEND chunk) of PNG file, it is required in PNG file and JPG file has no such data chunk. Now we can’t get the CRC bytes in the current data, which is the cause that our product can’t support data of PNG by using Store Procedure. I think it’s better to take a check that why there is no CRC bytes in the data and make a fix for it.

If we want to support the current data without CRC bytes, we only can handle the data without any check for CRC, of course there is the risk that there maybe the error for the chunk data.

What he is saying is we don't want to support PNG's without CRC check, there is the possibility it may cause an error.

Why .NET shows the image is likely a bug in MS's code not doing the World Standards CRC check.

So check your import routine to get the PNG into SQL Server and add the CRC value and then Crystal should work as it does for PNG's.

Thanks again

Don

[Former Member]

Hi Don,

Thanks for findings, can you please let me know what exactly is missing in the SP:

1. Do you mean that the file content is not properly imported (some bytes is missing)? In other words the file size doesn't match to the imported data size in SQL,

2. The SP does not parse imported data properly so that some data is currupted,

3. Everything is OK, file content is imported and parsed properly by SP, but besides this we need to add aditional data to BLOB to make it readable by Crystal?

I want to inderstand what exactly I have to fix in SP.

Thanks,

Gevorg

[former_member11696]

There were more updates in SP 22 for the viewer, try it:

https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

Don

[Former Member]

Hi Don,

Let me thank you for your quick response. How are you? It's long time that we have communicated each other related to the similar issue with JPG data files. And I'm not sure that you remember the details. Now, I'm facing the same issue with PNG data file (before I used only JPG).

I've checked with SP22 but the issue still exists. Can you please ask to your Developer Team to double check the same issue with PNG format?

Thanks in advance,

Gevorg

[former_member11696]

Can you send me your report and image so I can test it and verify?

Thanks again

[Former Member]

Hi Don,

I've prepared the attachment in zip format, which contains Database files and the Report for your test. Please note that item "A11030" has PNG file attached, which shows blank in crystal reports preview, the other items are JPG and they are shown properly.

Please note that the attachment size is about 19 MB, Let me know where I should upload it, (Dropbox or somewhere else).

Thanks,

Gevorg

[former_member11696]

If you can attach the png file to this post I can add it to a test report otherwise send me a link to dropbox...

Thanks

Don

[Former Member]

https://www.dropbox.com/s/zt4gxzocdviwehy/Sample%20Data%20%26%20Report.zip?dl=0

[former_member11696]

Go them, what version of SQL Server is it so I don't have to try them all?

Don

[Former Member]

SQL Server 2014

[former_member11696]

Figures, the one version I don't have an image for... I'll have to find one or create one...

[Former Member]

Thank you Don for your effort to help me. If you have Live account you can download the image from here:

https://my.visualstudio.com/Downloads?q=SQL%20Server%202014&pgroup=

Please let me know if anything else you need.

[former_member11696]

Sorry, got busy with SP 23 release and cases. I'll have to get back to this on monday hopefully....

[Former Member]

No Worries. Thank you Don.

[Former Member]

Hi Don,

Do you have any updates?

Thanks,

[former_member11696]

Still trying to get SQL 2014 image running. We have very few for testing so I had to create one I had permissions on.

Now I just need to get connected to it from CRD and test....

Hoping to get to it tomorrow...

Don

[former_member11696]

I have it running now and I can connect, I had to enable both NT and SQL Auth...

I don't see the image in CR Designer either so this is a Report issue and possibly a .NET issue.

I asked Vitaly who is the Nexus for CRD to look into it...

Thanks again

Don

[vitaly_izmaylov]

I found the image is coming from Stored Procedure, not from the DB table.

Is there any reason why?

How do you store the image in the Database? (format type)

and what is the role of Store Procedure?

[Former Member]

Hi Vitaly,

Yes, your findings is correct. The image is converted to the BLOB using stored procedure. Because, the image is saved into database by the 3-rd party application, which splits image content into bytes(255) fields array. If you check the old post

https://archive.sap.com/discussions/message/16743585#16743585

The logic was the same, the issue is only with PNG file, I guess the issue is related to crdb_odbc which is not properly load image of type PNG from the BLOB field.

Regards,

[Former Member]

Thank you very much Don. I've been in touch with Vitaly.

[vitaly_izmaylov]

I've tested with SQL Server OLEDB Provider and got the same results - displays .jpg but not .png, so I do not think it is related to crdb_odbc.

Have you tested the Stored Procedure results - the BLOB file for .png image outside of Crystal Reports?

[Former Member]

I'm also getting the same results with oledb, just tested now. So the issue might be in CR Viewer.

If you are not sure that the stored procedure is working properly, you can cover your suspicions by creating a .Net Window Form App, which shows image in the picture box from the Blob field returned by the stored procedure. Let me know if you need more details.

I've tested the stored procedure without Crystal Reports, it works.

[Former Member]

Please find uploaded test app, which uses the stored procedure to show PNG Image (without Crystal Reports):

https://www.dropbox.com/s/cljk8jox8xxni4w/Test.zip?dl=0

[vitaly_izmaylov]

Forwarded the question to Crystal Reports Developers.

Let's see what they have to say about the issue.

Will keep you posted.

[Former Member]

OK, noted and thanks.

[Former Member]

Hi Vitaly,

Do you have any updates?

Regards,
Gevorg

[vitaly_izmaylov]

There is a case (320680/2018) opened for the issue and assigned to Developer at June 26th.

No futher updates.