Bug 7642

Issue with large binary/text database blob object... 16 January, 2022

Andreas Mykonios
13 December, 2021
Product: PowerBuilder Category: DataWindow
Version: 2019 R3 Build: 2670
Classification: Issue Publishing: Public
Priority: P2
Status: Closed Reason: RESOLVED
Ken Guo @Appeon 16 January, 2022
#21
Hi Andreas,

Thanks for your feedback.
We will close this ticket first and please feel free to open a new one if you encounter any problems.

My best wishes for the new year.

Regards,
Ken
Andreas Mykonios 14 January, 2022
#20
Hi Ken.
My best wishes for the new year.
As this isn't condidered as a bug you can proceed and close that case. The only disadvantage I see in this way of handling docx it's that we have to retrieve two times the datawindow. We made some changes to better handle that...

Thank you for your help.
Ken Guo @Appeon 20 December, 2021
#19
Hi Andreas,

The docx format may have its specificity so OLE cannot auto-match it. This is OLE’s own behavior which PB cannot control. SAP PB 12.6 has the same result too.

So I suggest you use the code I previously provided to specify oleclass to resolve this issue. In this way, it’s more reliable and can support documents of all formats.

Regards,
Ken
Andreas Mykonios 15 December, 2021
#18
Hi Ken.

The following is from the help file.

======================>
When the server does not match the OLE blob data
If you specify a server that does not match the OLE blob object or if your database contains objects belonging to different servers, the OLE mechanism can usually handle the situation. It looks for the server specified in the object and starts it instead of the server you specified.
<======================

Of course it's stated than it "can usually handle the situation". What I understand is that this mechanism fails for docx (maybe with all newer office format files).

Andreas.
Ken Guo @Appeon 15 December, 2021
#17
Hi Andreas,

This is not a bug. It’s a coincidence that you can open the doc file with Excel OLE, or it’s a special behavior of Excel OLE itself which PB cannot control.

Normally, you need to set up the corresponding OLE Class to open different types of files.  

Regards,
Ken
Andreas Mykonios 15 December, 2021
#16
Hi Ken.
Thank you for yor answer.
I moved the setting of oleclass to rowfocuschange of the grid datawindow (which doesn't have the blob).
There I set the oleclass of the second datawindow to the appropriate value (before retrieve as you said). That way it works.
But I'm still wondering, if there is a bug, as this problem happens only with docx format. For other formats it works even if I don't set the oleclass at all. So I wonder if this is a workaround. I can live with it right now, but there is a different behaviour which is strange.

Andreas.
Ken Guo @Appeon 15 December, 2021
#15
Hi Andreas,

You can’t set the blob object oleclass in the doubleclicked event, you need to set the oleclass before dw_view_docs.retrieve().
So please add code in the rowfocuschanged event in dw_docs_list to change oleclass, below for example,

double ld_doc_ver_no
string ls_doc_id
if currentrow < 1 then return
ls_doc_id = dw_docs_list.object.doc_id[currentrow]
ld_doc_ver_no = dw_docs_list.object.doc_ver_no[currentrow]

//Added these to fix the issue. Need set the oleclass before dw_view_docs retrieve.
String ls_doctype
ls_doctype = dw_docs_list.object.doc_type[currentrow] 
If ls_doctype = "doc" or ls_doctype = "docx" Then
                dw_view_docs.object.blob_1.OleClass = "Word.Document.12"
ElseIf ls_doctype = "xls" or ls_doctype = "xlsx" Then
                dw_view_docs.object.blob_1.OleClass = "Excel.Sheet.12"
End If

dw_view_docs.post retrieve(ls_doc_id, ld_doc_ver_no)


Regards,
Ken
Andreas Mykonios 14 December, 2021
#14
Finally what I mension in #12, happens when DB is access. If DB is SQL Anywhere, then with the additional code in dw_view_docs doubleclicked event it always shows a blank document!
Andreas Mykonios 14 December, 2021
#13
Also we tested our sample application in PB 2021 (EBF 1311) and it has the same behaviour as PB 2019 R3 (EBF 2670).
Andreas Mykonios 14 December, 2021
#12
Hi Chris.
I was thinking that docx should be opened using Word.Document.12. In the same way xlsx are working with Excel.Sheet.12.
Also, I want to mention that if I add the following code in dw_view_docs doubleclicked event:
string ls_doc_type

if row < 1 then return

if dwo.name = "blob_1" then
	ls_doc_type = this.object.doc_type[row]
	if ls_doc_type = 'xls' or ls_doc_type = 'xlsx' then
		this.object.blob_1.OleClass = "Excel.Sheet.12"
	elseif ls_doc_type = 'doc' or ls_doc_type = 'docx' then
		this.object.blob_1.OleClass = "Word.Document.12"
	end if
end if

the behavior changes. In that case, if I try to open the docx when I first launch the application, or right after I’ve opened an .xls or .xlsx file, word will open showing a blank document. But if I first open the doc file, and right after I try to open the docx, it will show!
So I believe there must be a bug somewhere.
Andreas.
Chris Pollach @Appeon 13 December, 2021
#11
Hi Andreas;

  Yes, looking at your OLE Blob DWO I see that in the Blob Definition's "OLE Class Type" that the newer MS-Office DOCX (Word version) does not seem to be listed as an option in the DDLB. I suspect that this feature does not comply with the newer MS-Word format (MS-office 365 that I am using or this could be a genuine DW OLE Blob bug). My guess though is that this might require a DW OLE enhancement.

  I will now transfer this ticket over to the main Support / Engineering team for their review & feedback on this DOCX issue.

Regards ... Chris
PS: PB2021 has the same issue as well
Chris Pollach @Appeon 13 December, 2021
#10
Hi Andreas;

  I am looking at your example now more in depth. Yes, yYou are using an OLE BLob vs the way I was testing the MS-Word DOCX with a SelectBlob. That is why I said earlier that I think that this could be an OLE issue.

  I will give a further update soon.

Regards ... Chris
Andreas Mykonios 13 December, 2021
#9
Hi Chris.
Sorry but your example has nothing to do with the issue I'm having.
You do all the work with sql commands, while we are retrieving the blob using a datawindow.
If you don't mind take a look to the sample I attached in my first post.
Thanks.
Chris Pollach @Appeon 13 December, 2021
#8
Hi Andreas;

  The PBMaxBlobSize  default for SQL Server is 1024000 but typically for ODBC 32K.

Regards ... Chris
Chris Pollach @Appeon 13 December, 2021
#7
Hi Andreas;

  I wrote a simple test case to use your DOCX file into and back out of an SA DBMS Table Blob (see attached). The document was retrieved OK and the output file was resurrected OK as MS-Word was able to read it and the original and output files were the same length.

  So at this point, I cannot recreate your BLOB issue.

BTW: Here is the SA Table syntax ...

CREATE TABLE "dba"."TICKET7642" ("id" integer NOT NULL DEFAULT autoincrement, "doc_name" varchar(100) NOT NULL DEFAULT NULL, "doc_data" long binary DEFAULT NULL , PRIMARY KEY ("id")) ;


Regards ... Chris
PS: Your problem might be OLE related.
Chris Pollach @Appeon 13 December, 2021
#6
Test Case (By Chris) - Output MS-Word
Chris Pollach @Appeon 13 December, 2021
#5
Test Case (By Chris)
Andreas Mykonios 13 December, 2021
#4
By the way, in help file it is mentioned that PBMaxBlobSize default value is 1024000.
Andreas Mykonios 13 December, 2021
#3
Hi Chris.
In the documents I included in the zip file you can see the following information:
-January.doc (399.872 bytes) -> Works fine in the sample application.
-January.docx (111.448 bytes) -> Doesn't work!
-Seasonal illustrated any year calendar1.xls (3.150.336 bytes) -> Works fine in the sample application.
-Seasonal illustrated any year calendar1.xlsx (818.671 bytes) -> Works fine in the sample application.

As the file I'm facing the issue is the smaller one, I don't believe that this is a problem related to the properties you mentioned.
(But I will try those settings).

Andreas.
Chris Pollach @Appeon 13 December, 2021
#2
Hi Andreas;

  We have 1k's of PB customers that are using BLOB columns in various DBMS that include new MS-Office file formats.

  The only thing with any DBMS where you use the ODBC driver is that the application *needs* to set the maximum data length otherwise, ODBC defaults to 32K. 

For example:

SQLCA.DBParm="PBMaxBlobSize=200000"

Note: Also ...

SQLCA.DBParm="PBMaxTextSize='48000'

- OR - 

  You can add the PBMaxBlobSize and PBMaxTextSize parameters in the corresponding PBODBXXX.INI file

  Please consult the PB HELP file for more information on the above.


HTH
Regards ... Chris
Andreas Mykonios 13 December, 2021
#1
WordDWIssue.zip (8294KB)

*Phenomenon:
Hi.
We are having problem with blob in a datawindow. We do use this kind of column to edit documents and spreadsheets with word and excel and have changes made to those files saved back to our database. Our production db is sql anywhere, but for the purpose of this case we send an access db. We have reproduced the exact same problem using access. The blob (longbinary) is updated using updateblob command. We attach a test application, and four files (one .doc, one .docx, one .xls and an .xlsx). We do load all of them to the database (using add content button), but when retrieving data from database, we can only view the doc, xlsx and xls files. The docx doesn't open!

I will mention that the test application is based on an implementation that is currently used in production by a Client. The issue wasn't detected before, because they were using doc and not docx files. But now there is an internal requirement to use docx format for new documents, but they cannot view them...

*Reproduce Steps:
In the attached applications the third row cannot show the document... It contains a docx. Other rows work fine.
Example docs we are using are in a subfolder named docs.

Remarks:
Attached a sample application.
OS:
Windows 10
Platform:
All
Database Type:
SAP SQL Anywhere
Database Version:
17.0.10.6315