Harbour OLE/ADO Backward Compatibility Issue

Harbour OLE/ADO Backward Compatibility Issue

Postby nageswaragunupudi » Tue Jan 30, 2018 3:39 am

I was using Harbour 3.2.0dev (r1703231115) till recently and just now upgraded to the latest build Harbour 3.2.0dev (r1801051438). I noticed a major change in the manner array data is retrieved from Ole sources by TOleAuto, which may have impact on all libraries and software relying on this feature. In particular, this change affects

ADO RecordSet GetRows() --> aData
OLE Excel Range:Value() --> aData


xHarbour is consistent in its behavior from the time TOleAuto class was created till today. The behavior of Harbour's TOleAuto was also identical till recently. In other words, present Harbour version differs from xHarbour and all previous versions of Harbour.

GetRows()

Syntax: oRecordSet:GetRows( [rows], [start], [fields] ) --> aData

This is the fastest and simplest way to read data from a recordset into an array. Many existing software might be using this method and FWH library also uses this method.

Let us see the change using this sample program:
Code: Select all  Expand view

#include "fivewin.ch"

function Main()

   local oCn, oRs, aRows

   oCn   := FW_OpenAdoConnection( "c:\fwh\samples\xbrtest.mdb" )
   oRs   := FW_OpenRecordSet( oCn, "SELECT ID,FIRST,CITY FROM CUSTOMER WHERE ID < 6" )
   XBROWSER oRs TITLE "RecordSet"

   oRs:MoveFirst()
   aRows := oRs:GetRows()
   oRs:MoveFirst()

   XBROWSER aRows TITLE "oRs:GetRows() " + Version()

   oRs:Close()
   oCn:Close()

return nil
 

RecordSet:
Image

Array from GetRows() with all versions of xHarbour and all previous versions of Harbour:
Image

Array from GetRows() with the latest version of Harbour. (Different from xHarbour and all previous versions of Harbour)
Image

aData := oRange:Value // Excel

For testing this sample, first please open Excel. Please fill data in top 3 rows and 2 columns in this way:
Image

Range("A1:C3") is the used range.
In Visual Basic, if we assign Range.Value to an array the values of array are exactly like the values in the sheet, i.e.,
{ { "A1", "B1" },
{ "A2", "B2" },
{ "A3", "B3" } }
However (x)Harbour behaved differently from the beginning of TOleAuto class and was returning an array with transposed rows and columns like this:
{ { "A1", "A2", "A3" },
{ "B1", "B2", "B3 } }
For this reason, in all our (x)Harbour programs we transform the array after assining oRange:Value like this:
Code: Select all  Expand view

aData := oRange:Value
aData := ArrTranspose( aData )
 


Now let us see the change. Do not close Excel and keep it open on the desktop. Now execute this program:
Code: Select all  Expand view

#include "fivewin.ch"

function Main()

   local oRange, aData, n

   oRange   := GetExcelRange()
   if oRange == nil
      ? "Empty"
   else
      aData := oRange:Value
      xbrowser aData TITLE "oRange:Value " + Version()
      aData := ArrTranspose( aData )
      xbrowser aData TITLE "Transposed"
   endif

return nil
 


After "aData := oRange:Value"

All versions of xHarbour and all previous versions of Harbour:
Image

Recent version of Harbour:
Image
Obviously, Harbour fixed the transposed array issue in all previous versions, ignoring other side-effects and backward compatibility issues.


After "aData := ArrTranspose( aData )"

All versions of xHarbour and all previous versions of Harbour:
Image

Recent version of Harbour:
Image

Implications:
Only those who use Harbour and use ADO/Excel Ole, directly or indirectly using these methods, need to be concerned about this issue. Upgrading Harbour will produce erroneous results unless the application code is carefully modified.

Obviously, FWH team is working on this issue of making the library compatible with xHarbour and Harbour both old and revised versions.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 5563
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Harbour OLE/ADO Backward Compatibility Issue

Postby hua » Tue Jan 30, 2018 9:38 am

Possibly related to what's discussed here
FWH 11.08
xHarbour 1.2.1 (Rev 6406) + BCC 5.82
Harbour 3.1 (Rev 17062) + BCC 5.82
hua
 
Posts: 719
Joined: Fri Oct 28, 2005 2:27 am

Re: Harbour OLE/ADO Backward Compatibility Issue

Postby AntoninoP » Tue Jan 30, 2018 10:07 am

can It influense the xls creation? One reason we migrated on libxlsx is that on some pc the creation did not works...

Code: Select all  Expand view
Error BASE/1005  Message not found: ARRAY:_MERGECELLS

=> __ERRRT_SBASE(0)
../../../tobject.prg => ARRAY:ERROR(0)
../../../tobject.prg => (b)HBOBJECT(0)
../../../tobject.prg => ARRAY:MSGNOTFOUND(0)
../../../tobject.prg => ARRAY:_MERGECELLS(0)
report.prg => TOEXCEL(2202)
 
AntoninoP
 
Posts: 184
Joined: Tue Feb 10, 2015 9:48 am
Location: Albenga, Italy

Re: Harbour OLE/ADO Backward Compatibility Issue

Postby nageswaragunupudi » Tue Jan 30, 2018 10:20 am

Mr AntonioP

I am still studying the impact.
Can you please let me know the version of Harbour you are using?
If you do the tests I posted above you will know if your version has the new behaviour or old.

In any case, if you can give me a sample where you are getting errors while creating xls, that would help me a lot in fixing any issues with our library.

Code: Select all  Expand view

WITH OBJECT ( oRng := oSheet:Range( oRange:Cells( nRow, 1 ), oRange:Cells( nRow, nMergeCols ) ) )
   :MergeCells := .t.
 

This is the line you are referring.
I will check and get back.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 5563
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Harbour OLE/ADO Backward Compatibility Issue

Postby AntoninoP » Tue Jan 30, 2018 11:41 am

Hello,
we are using Harbour 3.2.0dev (r1708131853)
This error happened on a Windows Server 2008 64bit Service Pack 2 TS
I thought it depended on wich version of office is installed, in some computer it does not happen...
AntoninoP
 
Posts: 184
Joined: Tue Feb 10, 2015 9:48 am
Location: Albenga, Italy

Re: Harbour OLE/ADO Backward Compatibility Issue

Postby Antonio Mart. » Tue Jan 30, 2018 12:38 pm

My doubt,

Is this a relationed issue ?
Antonio Mart.
 
Posts: 154
Joined: Sat Feb 23, 2013 10:04 am

Re: Harbour OLE/ADO Backward Compatibility Issue

Postby nageswaragunupudi » Mon Feb 12, 2018 11:25 pm

In FWH 1801, we added 2 new functions

xlRangeValue( oRange ) --> aValue
RsGetRows( oRs ) --> aRows


These functions check the version of Harbour and decide on use of array transpose depending on the version.

aValue := xlRangeValue( oRange )
instead of
aValue := oRange:Value
aValue := ArrTranspose( aValue )

and

aData := RsGetRows( oRs )
may be used instead of
aData := oRs:GetRows()
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 5563
Joined: Sun Nov 19, 2006 5:22 am
Location: India


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 7 guests