xBrowse & Recordset related errors in FWH 9.06

[anserkk]

I am using FWH 9.06. I have found the following errors in Xbrowse when associated with Recordsets.

1) In line no.5971, Method ToCalc() is trying to make a call to CopyFromRecordSet(). I understand that OpenOffice Calc does not support this function. This function is only supported by Excel and there is no equivalent function in OpenOffice Calc. XBrowse errors out at this line. This function is called only when the xBrowse condition "Len( aCols ) == ::oRs:Fields:Count()" is evaluated as .T., otherwise xbrowse uses the regular ClipBoard copy/paste method to transfer the xBrowse contents to Calc/Excel and it may work if the recordset does not contain a numeric column.

I tried to by pass this error by adding an extra column to the recordset, so that the condition "Len( aCols ) == ::oRs:Fields:Count()" is evaluated to .F. and xBrowse tries to use ClipBoard copy/paste method. But then another problem rises ie, if the recordset contains numeric column, it errors out at line 9405 ie

RetVal := AllTrim( Eval( ::bStrData ) ) // Alltrim will generate error.

I found that the same problem exists in the Method To Excel() when the CopyFromRecordSet() is not utilised to transfer xBrowse data contents to Excel. XBrowse will not use CopyFromRecordSet() when the xBrowse displayed columns and RecordSet FieldCount() differs

For testing purpose you can recreate the error by following the below given steps

1. Create a record set containing a numeric column
For eg. Name, Salary, Address. Salary should be a numeric column
2. Make xBrowse to display only column 1 and 2 ie Name & Salary.
3. Try to call ToExcel()

I understand that xBrowse expects the bStrData to be of character type and it tries to do Alltrim() (Line 9405) and StrTran() in the Method ClpRow() Line No. 5155 and errors out when the bStrData type is not of a character type

Any help is appreciated.

Regards
Anser

[nageswaragunupudi]

Mr Anser

This function is only supported by Excel and there is no equivalent function in OpenOffice Calc. XBrowse errors out at this line.

You are right. This seems to be an inadvertent copy of ToExcel code to ToCalc method when the ToCalc method was introduced. This line should be removed from ToCalc method. Hope this will be fixed in version 9.08

About bStrData:

Actually XBrowse expects bStrData to return Character value only. We are not supposed to assign a codeblock returning non-character value to bStrData.
In fact direct use of bStrData is deprecated long time back.

Please read this extract from Whatsnew.txt version 8.03 ( March 2008 )

Code: Select all  Expand view

* TXBrowse Enhancements: (please review samples\TestXBr3.prg)

  a) bStrData: Deprecated, Users are advised to assign a codeblock returning the data in
     its native type ( 'N', "C", "D", "L" ) and optionally assigining the picture to
     oCol:cEditPicture. Browse class will automatically derive the bStrData from the
     bEditValue and cEditPicture, if specified,  and in addition the Browse can
     appropriately handle the values for alignment, formatting, copy / export to
     excel and reports.

 

We are advised to use bEditValue only. Codeblocks returning any datatype ( numeric, date, or whatever ) can be assigned to bEditValue. XBrowse internally constructs bStrData codeblock from bEditValue and cEditPicture ( if assigned ).

It is also desirable to use xbrowse command syntax than direct assignment to the data in oops way. If we use command syntax, xbrowse internally handles many dependant codeblocks automatically. Particularly for recordsets, it is all the more advantageous to depend on xbrowse's automatically generated codeblocks rather than our own codeblocks.

In your case:

For testing purpose you can recreate the error by following the below given steps

1. Create a record set containing a numeric column
For eg. Name, Salary, Address. Salary should be a numeric column
2. Make xBrowse to display only column 1 and 2 ie Name & Salary.
3. Try to call ToExcel()

Your code should be :

Code: Select all  Expand view

oCol:bEditValue := { || oRs:Fields( "Salary" ):Value }
oCol:cEditPicture := '999,999.99'  // This is optional
 

You will not get any error.
In fact it is all the more easier and advantageous to use command syntax

Code: Select all  Expand view

@ 0,0 XBROWSE oBrw OF oWnd COLUMNS 'Name', 'Salary' RECORDSET oRs

In this case xbrowse generates codeblocks which are empty recorset tolerant, assigns appropriate picture clause etc. To write all the code directly in our program would take atleast 4 or 5 lines of code for each column

[anserkk]

Dear Mr.Rao,

Thank you for the effort. You are right.

This did the trick

Code: Select all  Expand view

oCol:bEditValue := { || oRs:Fields( "Salary" ):Value }
oCol:cEditPicture := '999,999.99'  // This is optional

(Below given code) This was how I used to write the code while using RecordSets with xBrowse. Now I understand that I was wrong

Code: Select all  Expand view

oBrw:=TxBrowse():New(oDlg)

oCol:=oBrw:AddCol()
oCol:bStrData := { || IF(oRecSet:BOF() .AND. oRecSet:EOF(),SPACE(1),oRecSet:Fields("EmpName"):Value)}
oCol:cHeader := "Employee Name"
oCol:nHeadStrAlign := AL_LEFT
oCol:nDataStrAlign := AL_LEFT
oCol:nWidth := 216

// Numeric Column
oCol:=oBrw:AddCol()
oCol:bStrData := { || IF(oRecSet:BOF() .AND. oRecSet:EOF(),SPACE(1),oRecSet:Fields("Salary"):Value)}
oCol:cHeader := "Salary"
oCol:nHeadStrAlign := AL_LEFT
oCol:nDataStrAlign := AL_LEFT
oCol:nWidth := 216

xBrwAdoSettings(oBrw,oRecSet)  // ADO Settings for xBrowse
oBrw:CreateFromCode()

ACTIVATE DIALOG oDlg CENTERED ;
   ON INIT (oBrw:nHeight:= oDlg:nHeight-35,oBrw:nWidth:=oBrw:nWidth+110)

*-----------------------------------*
Function xBrwAdoSettings(oBrw,oRecSet)
*-----------------------------------*
oBrw:bGoTop    := { || IF( ! oRecSet:BOF() .AND. ! oRecSet:EOF(), oRecSet:MoveFirst(), ) }
oBrw:bGoBottom := { || IF( ! oRecSet:BOF() .AND. ! oRecSet:EOF(), oRecSet:MoveLast(), ) }
oBrw:bBof      := { || oRecSet:BOF() }
oBrw:bEof      := { || oRecSet:EOF() }
oBrw:bBookMark := { |uBm| IF( oRecSet:BOF() .AND. oRecSet:EOF(), 0, IF( uBm == NIL, oRecSet:BookMark, oRecSet:BookMark := uBm ) ) }
oBrw:bKeyNo := { |n| IF( oRecSet:BOF() .AND. oRecSet:EOF(), 0, IF( n == NIL, oRecSet:AbsolutePosition, oRecSet:AbsolutePosition := n ) ) }
oBrw:bKeyCount := { || oRecSet:RecordCount() }
oBrw:SetAdo(oRecSet)
Return NIL
 

Once again thank you for pointing me towards the right direction

Regards
Anser

[nageswaragunupudi]

I would advise you to use the command syntax suggested by me above. You will notice that 3 or 4 lines of code is equivalent to 100s of lines of code written in the oops way. In addition the xbrowse internally generates well tested codeblocks and does lot more work than we can do. For example the above single line code also generates picture clauses (comma delimited where needed), posteditblocks, ready for edit and a lot more.

testxbr3.prg in the samples folder gives examples of building xbrowse from ADO recordsets. If we write the same code in oops way ourselves we would have to write several pages of code to achieve the same result