TXBowse+SqlQuery error when using arrays (solved)

[ellano]

I use the following code to define and fill-up a TXBrowse control:

Code: Select all  Expand view

oBrwD:=TXBrowse():New(oFld:aDialogs[5])
oBrwD:SetArray(aResult)
oBrwD:cHeaders:= {"#","Name","_", "Born"}
oBrwD:CreateFromResource(101)
where the array aResult come from the following function:
sCondic:="SELECT num, name, _, dateborn WHERE " + ;
               IIF( oDbf:_="M","FATHER=","MOTHER=") + CHR(34) + oDbf:num + CHR(34) + ;
              " ORDER BY num"
TRY
  aResult := oDbf:SqlQuery( sCondic )
  cText[8]:="# of Descendants:" + STR(LEN(aResult))
CATCH oError
  aResult:={ "", "", "", ""}
  cText[8]:="No descendants"
END

The 1st time out the TxBrowse is displayed everything is OK. The problem comes when I skip through my records, then the array gets all messed up. When I get new record to fill the TXBrowse array with:

Code: Select all  Expand view

DBSKIP(-1)
*Same code as before to fill the array
….
  oBrwD:aArrayData := aResult //here we have the new descendants in the array
  oBrwD:SetFocus()
  oBrwD:REFRESH()
  oBrwD:GoTop()

Here I have a strange case since some registers are correctly displayed with all descendants correctly positioned in every column, 1 line for each register, while some are displayed with my 4 fields (number, name, _, born; in those cases in which there is 1 descendent) in one column. The number of descendants is multiplied by four, which are always piled together and displayed in the 1st column). Example: If the register has two descendants, it indicates eight descendants (corresponding to the 4 fields by 2 registers). When skipping back and forth through the registers at one time or another everything comes back to normal. Seems that this situation only occurs when there is only one descendant in the condition ("SELECT num, name, _, dateborn WHERE " + IIF( oDbf:_="M","FATHER=","MOTHER=") + CHR(34) + oDbf:num + CHR(34) + " ORDER BY num)
I tried the code in Windows 7 64 bits, Windows XP 32 bits, Windows Vista 32 bits all with the same result:

The code was working correctly on FWH 13.05, not working on FWh 13.06, 13.07 nor 13.08 Bug, error?

[nageswaragunupudi]

Mr Ellano

#1) I understand you are using the method SqlQuery() of TDatabase. Please confirm.

#2) Glad to see atleast one programmer using this method.

#3) Yes. We implemented a change in the way the value is returned. We thought this would be helpful, but I now realize that this change is really causing problems.

Please kindly make this change in the \fwh\source\classes\database.prg.

In the database.prg, please locate the method SqlQuery and go to the end of the method
You will find these lines.

Code: Select all  Expand view

            if Len( uRet ) == 1
               uRet  := uRet[ 1 ]
               if Len( uRet ) == 1
                  uRet  := uRet[ 1 ]
               endif
            endif
 

Please remove or comment out all these lines from "if Len( uRet ) == 1" to the corresponding "endif"

Please let us know the result.

We shall also make this change and will be available from fwh13.09 onwards.

We regret the inconvenience.

[ellano]

Mr. Rao:

Thank you for your quick answer.

Yes, I’m using (extensibility) the method SQLQuery of TDatabase, very powerful and simple way to do queries.
But since I found this error I just restrain myself.

Found the lines and correct them. Now working like a charm.

Will I have right to v13.09 with no charge?

Thanks

[nageswaragunupudi]

Please do not use TRY/CATCH, because TRY/CATCH is used inside the function while opening the recordset. If the function fails it returns an empty array ( {} ).

If the function fails or there are no records in the recordset the return value is an empty array ( {} ).

Are you using the method on DBFs on local drive only or have you tried on DBFs on network drives?

[ellano]

The reason to use try catch is(was) because the function was not working properly returning an error so I have to role my own empty array.

In this precise case the function is used in local DBfs.

[ellano]

This is what happens if I delete the try-catch:


Time from start: 0 hours 0 mins 12 secs
Error occurred at: 19/09/2013, 09:00:12
Error description: (DOS Error -2147352567) WINOLE/1007 Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. (0x800A0BCD): ADODB.Recordset

[nageswaragunupudi]

Please provide the error.log

[ellano]

There you go:

Application
===========
Path and name: c:\emili\pedigree4\Pedigree.exe (32 bits)
Size: 3,477,504 bytes
Compiler version: Harbour 3.2.0dev (r1306132159)
FiveWin Version: FWH 13.07
Windows version: 6.1, Build 7601 Service Pack 1

Time from start: 0 hours 0 mins 10 secs
Error occurred at: 19/09/2013, 09:04:15
Error description: (DOS Error -2147352567) WINOLE/1007 Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. (0x800A0BCD): ADODB.Recordset

[nageswaragunupudi]

If you are getting this error in line 1217, then you will find the code

Code: Select all  Expand view

If oRs != nil

in the previous line (1216).

Please change this line as

Code: Select all  Expand view

If oRs != nil .and. oRs:RecordCount() > 0

[ellano]

With the following (proposed by you) modifications to database.prg

Code: Select all  Expand view

*        if oRs != nil
         if oRs != nil  .and. oRs:RecordCount() > 0
            uRet := oRs:GetRows()
            oRs:MoveFirst()
            oRs:Close()
*            if Len( uRet ) == 1
*               uRet  := uRet[ 1 ]
*               if Len( uRet ) == 1
*                  uRet  := uRet[ 1 ]
*               endif
*            endif
         endif
      endif

now it is working correctly with out the try-catch structure and without piling the array in the 1st column when the DB returns only 1 register

[nageswaragunupudi]

Please finally adopt this code

Code: Select all  Expand view

        oRs      := FW_OpenRecordSet( ::oCn, cSql )
         if oRs != nil
            if oRs:RecordCount() > 0
               uRet := oRs:GetRows()
               oRs:MoveFirst()
            endif
            oRs:Close()
         endif
      endif
   endif

return uRet
 

[ellano]

Changed. Still working without problems.

[nageswaragunupudi]

Are you working with DBFs located on local drives only?
Have you tested with DBFs on server or other computers on a network?

[ellano]

Working on a local drives + from a network without problem.

[nageswaragunupudi]

Working on a local drives + from a network without problem.

Thanks for the information.
Are you using DBF on net work like this?
USE \\OTHERPC\C\MYFOLDER\MYDBF.DBF ?
Is SqlQuery() working in such cases?