PB 12.6 classic failures when exporting to XLSX

[Former Member]

I have an application that has been upgraded from PB8 to PB11.5 and most recently to PB 12.6 Classic Build 3506.

A lot of this application is outputting data to Excel for the user.  It has all worked great except for when we went to saving as XLSX.  The application runs and saves a file and thinks it succeeds.  You cannot open the file because it is corrupted.

We have a DW on page.  The DataObject is a DW that is basically a template.  The problem was with the template DataWindow that got assigned.  I did an 'edit source' on it to see the code.

Question #1.  It says release 8; at the top, which I assume was the original version that it was developed under.  I can change it to 12.6, but is there something that should automatically change it?

Inside the file, the table was being built,

table(column=(type=char(60) updatewhereclause=yes name=title dbname="TITLE" )

        column=(type=char(8) updatewhereclause=no name=osd_code dbname="OSD Code" )

        .....

        .....   all the way to completion

The 'data' string looked like this(similar):

data("DIRECT" null null null null null null, null null "", null  "OSD Code".......

All the blanks cells in the template were represented throughout the data string 3 different ways. 

#1 null

#2 null,

#3 "",

Question #2.  I did not develop this, so I am unsure what happened here.  Why is a blank represented 3 different ways?

I got this to work when I modified the 'data' string to all use "",.  Using all 'null' or all 'null,' did not work.

So, I moved on to the next place that outputs an XLSX file that was failing with the same error.

When I would 'edit' the DW, it had both string and number's defined.

I got this one to work by going thru the 'data' string and figuring out where it was going to be representing strings.  I replaced those 'null' with "", and it worked correctly.

I can output these files using EXCEL8! fine without any modification, but the XLSX bombs b/c of the XML.  I have other issues I would like to discuss, but this is a good start. 

All suggestions are apprecitated.

[Former Member]

I had also issues with saving large files (blobs) after migrating to PB12.6 and open a case at SAP support.

And I got help from a nice support guy!!!

In my case it was a new System option -  "Use 1-byte structure member alignment in external function"  - that I had not checked.

My be this helps you, too.

Good luck!

Ludwin

[Former Member]

thx for the suggestion.  I tried this, but the problem is still there.

[Former Member]

Chris,

Have you deployed PBDWExcel12Interop126.dll and Sybase.PowerBuilder.DataWindow.Excel12.dll?  This was a new required file in later releases.

Chris Craft

[Former Member]

Yes.  I deployed those 2 dll's.  XLSX works if I just dump data from a DW into a spreadsheet.  It seems to have trouble handling a structure.

[Former Member]

This brings me to my next problem that I have not been able to overcome.  We are reading in a template into a DW.  It is basically a row at the top, all strings and about 10 or so of them.  Not really relevant I don't think?

  Anyway, the code sets the template and then it begins to insert rows using SetItem.  Before inserting, it does a dw.SetItem(0) to make space for the row.  The issue here is that this portion is in a loop.  If it only loops thru one time, it will output correctly to XLSX.  But if it has to output more than one time, the XLSX file is created but will not open b/c it is corrupted.  Each iteration thru the code is setting the same number of rows.

   I can run the code thru the debugger and after the first iteration, I make it jump the rest and output the XLSX.  It works great.  I run again and after the first iteration, I let it try and set one single column with a text value, then skip the rest and output the data to XLSX.  It outputs, but it will not open as it is corrupt.

Before each row is added using SetItem, a new row is inserted so I do not know why that is causing it to fail.  If I output using EXCEL8, it works great.  Here is a code snippet of where the data is being put into the DW.  I cannot find a reason why the restriction is there to only allow one iteration.  I have looked over the DW definition, but I do not see anything.

[Former Member]

  FWIW: There were fixes to Excel & XLS processing in build 4088 and related fixes in earlier EBF's. The current build of PB 12.6 is 4098.    

[Former Member]

That is what I'm looking at.  Can I get the latest 4098 or do I need to install each one in progression from the build that I have.

[Former Member]

I am now at 4098 and still have the same issues

[Former Member]

Hi Chris,

about your question #2.

I've tried in 12.1 classic:

If you only insert a row in the data view in datawindow painter without editing, all values are null (without comma).

If you edit a value for a column and the save the datawindow, the value for that column is not null. I don't know a way to return it to null in the datawindow painter. If you clean the field the data entry will be "", (with comma).

I don't know how entries may become null,

Maybe older versions had saved a litte be different.

However, do you think this is the reason for export problems?

[Former Member]

Hi Rene;

Q: If you edit a value for a column and the save the datawindow, the value for that column is not null. I don't know a way to return it to null in the datawindow painter

A:  Use the "Empty String is NULL" option on any column. 

Regards ... Chris

[Former Member]

Hi Chris,

yes, i know that. But it seems not to work in the data view of the datawindow painter.

[Former Member]

Did you check the ShareData option ?

If not, the EmptyStringIsNull settings will not be reflected in the data view !

Sometimes, it is necessary to close and reopen the datawindow and even more to restart PB IDE to see the ShareData option effect in both preview and data views !

[Former Member]

When a column is defined to be a string, if the code behind was using null for that column, the XLSX file that was output would not open.  It was corrupted.  I would edit the 'data' string in the source to make sure that each column that was string would be '  "", ', then it would open and display correctly.  If the column is defined as a number, the null would work correctly.

[Former Member]

Hi Chris;

  Just edit the DW source and change the 8 to a 12.6 - then save the DW source. That will complete the DWO migration to version 12.6. The migration assistant in PB never migrates any DWO source properly (IMHO). I always check all my DWO's source "release" setting & edit the release number accordingly as part of my standard migration procedure.

Regards ... Chris

[Former Member]

Hi Chris & Chris,

I think this is not a good way to change the release version of a datawindow. It assumes that nothing other changes. But nobody knows what backward compatibility issues are in the powerbuilder runtime implemented.

PowerBuilder datawindows are (until now) backward compatible (as far as I know), so you don't need to change the datawindow release number.

If you want to change it the best way is to open the datawindow in datawindow painter, do some litte change and save it. Powerbuilder saves it with new release information. If you compare the old and new datawindow you will see that also new properties are now included.

[Former Member]

Hi Rene;

   FWIW: I have been doing this since PB version 3/4 where I then noticed (and even in recent releases) that weird anomalies happen if the DWO source code is not migrated to the current PB release. Its a bug in PB (IMHO) that has been there since version 2.0 when the migration assistant was first written. I have documented the problem with not completing the proper migration to PowerSoft & Sybase and even documented this many times in various PB beta's with Sybase where the issue was reproducible.

  Hopefully, we'll finally get this migration issue fixed with Appeon now steering the PB ship.  

Regards ... Chris

[Former Member]

I wouldn't call it a bug, it is a design choice that you don't agree with.

The intent is that the DataWindow engine is able to run them from any prior release and therefore a version migration is not needed. If you have a DataWindow from a prior release that works differently than a copy of the same DataWindow that has been version migrated, that is a bug in the DataWindow engine that should be reported.

[Former Member]

  I would call it a bug when I can detect an error and fix it by just updating the DWO source code to current levels via my little release # change. I must have proved that to the Sybase Engineer's in over a dozen beta's.