ADO RDD xHarbour

[AHF]

Lucas,

DO_PUTVALUE( 1503 ) -> oRecordSet:Update()

oRecordSet:Fields( nField - 1 ):Value := xValue
oRecordSet:Update()


This is my code:

REPLACE FACTURAS->NOMBRE WITH CLIENTES->NOMBRE
.... and so one


The real cause:

Field lengh for FACTURAS nombre is 34, but in CLIENTES is 50.

This works here.
adordd automatically truncates :

C values to the len of the field
N values decimals round to the number of decimals in the field left part of the number it throws a error data_with.

Did you try this after copy to to build this table?
Your addition on ado_append to insert field recno cant be used because with "copy to" all fields will be out of order between the source table and destination table
We must use aadd() field.

Field dates in "copy to" when the source table was opened via ex"dbfcdx" continues to error.

Filters we will need some help because I dont have now the time to worry to much about it as I dont need it for this project.
Ill try to do the best I can.

[lucasdebeltran]

Antonio,

I don´t understand what you say about dates.

Your xHarbour version has a bug, but recent Harbour and xHarbour must have an emtpy date, and it´s achieved by using AdoNull().

This code at ADO_APPEND() works fine and have been tested:

Code: Select all  Expand view  RUN

           AADD( aVals, HB_DECODE( aStruct[ n, 2 ], 'C', Space( aStruct[ n, 3 ] ), 'D', AdoNull(), 'L', .f., ;
                 'M', "", 'm', "", '+', 0, ;
                 'N', If( aStruct[ n, 3 ] == 0, 0, Val( "0." + Replicate( '0', aStruct[ n, 3 ] ) ) ), ;
                 'T', AdoNull(), '' ) )
 
 

No, I have not use COPY TO or APPEND TO. I see your point about the fix for HBRECNO.

But your code at ADO_OPEN did´t work: the SQL sinxtax at ALTER TABLE and the condition to execute both fail.

FW provides SQL ALTER TABLE <tbl> <cop:ADD,MODIFY,ALTER> [COLUMN] <acol> => FW_AdoAddModiColSQL( <(tbl)>, <acol>, <"cop"> )

I asked Harbour and xHarbour developers help about Filters.

ADORDD is close to be ready.

Thank you.

[AHF]

Lucas,

Filters without any change in the app code. (I dont know about performance)

Code: Select all  Expand view  RUN

STATIC FUNCTION ADO_SETFILTER( nWA, aFilterInfo )

   LOCAL oRecordSet := USRRDD_AREADATA( nWA )[ WA_RECORDSET ]
   LOCAL aWAData := USRRDD_AREADATA( nWA )
   LOCAL oError, nRecNo, aBookMarks := {},nDecimals := SET( _SET_DECIMALS)
   
    IF VALTYPE(aFilterInfo) = "A"
     
       aWAData[WA_FILTERACTIVE] := aFilterInfo[ UR_FRI_BEXPR ] //SAVE ACTIVE FILTER EXPRESION
         
    ELSE //CHECKING ACTVE FILTER IF ONE
     
       IF EMPTY(aWAData[WA_FILTERACTIVE])
          RETURN HB_SUCCESS  //NONE CONTINUE WITH CURRENT TASK
       ENDIF
         
    ENDIF
     
    ADO_RECID( nWA, @nRecNo )
     
    IF oRecordSet:Supports(adBookmark)
       SET( _SET_DECIMALS, 0 ) //IF BOOKMARK NUMERIC IT COMES WITH DEFINED DECIMALS MUST SET IT TO 0
     
       oRecordSet:MoveFirst()
     
       DO WHILE ! oRecordSet:Eof()
          IF EVAL( aFilterInfo[ UR_FRI_BEXPR ] )
             AADD( aBookMarks, oRecordSet:BookMark )
          ENDIF
         
          oRecordSet:MoveNext()
         
       ENDDO
       SET( _SET_DECIMALS, nDecimals )
       
       oRecordSet:Filter := aBookMarks   //ARRAY OF BOOKMARKS
       
    ELSE
       TRY
          oRecordSet:Filter := SqlTranslate(aFilterInfo[ UR_FRI_CEXPR ])

       CATCH //SHOULD RAISE AN ERROR
          IF VALTYPE(aFilterInfo[ UR_FRI_CEXPR ]) = "C"
             MSGINFO("Expression not allowed! " +SqlTranslate(aFilterInfo[ UR_FRI_CEXPR ]))
         
          ENDIF
       
       END
       
    ENDIF  

    ADO_GOTOID( nWA, nRecNo )
    IF oRecordSet:Eof() //does not have this rec in filter lets gotop
       ADO_GOTOP(nWa)
    ENDIF
     
   RETURN HB_SUCCESS


   
STATIC FUNCTION ADO_CLEARFILTER( nWA )

   LOCAL aWAData := USRRDD_AREADATA( nWA )
   LOCAL oRecordSet := USRRDD_AREADATA( nWA )[ WA_RECORDSET ]
   LOCAL aOrderInfo := ARRAY(UR_ORI_SIZE)
 
   aWAData[WA_FILTERACTIVE] := NIL //NO FILTER
   
   IF !EMPTY(oRecordSet:Filter )
      oRecordSet:Filter := ""
     
   ENDIF
   
   RETURN HB_SUCCESS
 

Please try it and let me know results.

[lucasdebeltran]

Antonio,

Thank you.

ADO_CLEARFILTER() should be as follows:

Code: Select all  Expand view  RUN

STATIC FUNCTION ADO_CLEARFILTER( nWA )

   LOCAL aWAData    := USRRDD_AREADATA( nWA )
   LOCAL oRecordSet := USRRDD_AREADATA( nWA )[ WA_RECORDSET ]
   LOCAL aOrderInfo := ARRAY(UR_ORI_SIZE)

   aWAData[WA_FILTERACTIVE] := NIL //NO FILTER

   // fix Lucas de Beltrán 24.05.2015
   IF ValType( oRecordSet ) == "O"

      IF !EMPTY(oRecordSet:Filter )
         oRecordSet:Filter := ""
      ENDIF

   ENDIF


   RETURN HB_SUCCESS

 

Also, I am getting 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

at ADO_SETFILTER(), ado_gotop(), at the very end:

Code: Select all  Expand view  RUN

   IF oRecordSet:Eof() //does not have this rec in filter lets gotop
       ADO_GOTOP(nWa)
     ENDIF
 

Also, a fix for empty tables at ADO_SETFILTER():

Code: Select all  Expand view  RUN

    IF oRecordSet:Supports(adBookmark)
       SET( _SET_DECIMALS, 0 ) //IF BOOKMARK NUMERIC IT COMES WITH DEFINED DECIMALS MUST SET IT TO 0


       // fix Lucas de Beltran 24.05.2015 for empty oRecordSet
       if ! ( oRecordSet:Eof .and. oRecordSet:Bof )
          oRecordSet:MoveFirst()
       endif

 

Thank you.

[AHF]

Lucas,

Did it work ?

I only can test it here today with ADS LOCAL and although all get all bookmarks it only displays one in browse().

[lucasdebeltran]

Antonio,

No it didn´t.

aBookMarks almost always get all the values matching the filter.

But many times after SET FILTER command, the dbf is empty!!.

Any possible clue?.

Maybe, at oRecordSet:Filter instead of BookMarks use HHBRECNO numbers?.

[AHF]

Lucas,

I assume that you placed a msgselect to inspect aBookmarks.
Please note that aBookmarks order change accordingly to the index order.
Here I always get all matches in aBookmarks.

The problem is that only shows one in browse and does not navigate through all.

I think it might have something to do with the var type variant of :bookmark.
How can we set a variant type var?

Please check this http://www.java2s.com/Code/VBA-Excel-Ac ... ecords.htm

[lucasdebeltran]

Antonio,

Yes, I inspected aBookmarks and I saw that all matches were in aBookmarks.

However, most of times I get no records. Please use MySQL instead of ADS.

As far as I know, :Bookmark is managed by ADO itself:
https://msdn.microsoft.com/es-es/librar ... 62(v=vs.85).aspx

So the alternative is to call the Filter with HBRECNO numbers. I beleive it may be safer, as Bookmarks sometimes provide wrong results as Mr. Nages pointed out.

[AHF]

Lucas,

Acc to documentation with aduseclient bookmarks are always assured by ado.

I cant test it today with Mysql only tomorrow.

ado its taking care of bookmarks and yes sometimes they might have strange values because they are "variant" variable. But they are always correct in ado sense.

We cant send hbrecno as bookmarks as bookmark is a internal variant ado var.

I think the problem has to do with the corresponding "variant" type var between ado and (x)Harbour.

Can you check with Nages?

[AHF]

Lucas,

Please check this http://en.wikipedia.org/wiki/Variant_type

[lucasdebeltran]

Antonio,

I did this test:

Code: Select all  Expand view  RUN

       DO WHILE ! oRecordSet:Eof()
          IF EVAL( aFilterInfo[ UR_FRI_BEXPR ] )
             
             AADD( aBookMarks, oRecordSet:BookMark )

             aadd ( aRegistros, alltrim(  cValtochar(oRecordSet:Fields(ADODEFLDRECNO()):Value)  ) )

          ENDIF

          oRecordSet:MoveNext()

       ENDDO
       SET( _SET_DECIMALS, nDecimals )


   xbrowse(aregistros)

 for i := 1 to len( aRegistros )
     cFiltro += ADODEFLDRECNO() + " = " + aregistros[i]

     if i != len( aRegistros )
        cFiltro += " AND "
     endif
  next

  MSGINFO(cfiltro)


       oRecordSet:Filter := cFiltro //aBookMarks   //ARRAY OF BOOKMARKS

 

I am still getting no records, despite cFiltro is ok.

Maybe something with cursors?.

[AHF]

Lucas,

Try set filter to fieldrecn = nrecno and fieldrecn =nrecno

Does it works?

[AHF]

Lucas,

Comment this in ado_setfilter

* ADO_GOTOID( nWA, nRecNo )

IF oRecordSet:Eof() //does not have this rec in filter lets gotop
ADO_GOTOP(nWa)
ENDIF
*/

Did it work?

[lucasdebeltran]

Antonio,


Yes!!, now commenting out such lines oRecordSet:Filter := aBookMarks works perfect!!!.

Congratulations.

I am going to send you an email with minor bugs.

Thank you.

[AHF]

Lucas,

I assume then you are using this:

Code: Select all  Expand view  RUN

 DO WHILE ! oRecordSet:Eof()
          IF EVAL( aFilterInfo[ UR_FRI_BEXPR ] )
             
             AADD( aBookMarks, oRecordSet:BookMark )

             aadd ( aRegistros, alltrim(  cValtochar(oRecordSet:Fields(ADODEFLDRECNO()):Value)  ) )

          ENDIF

          oRecordSet:MoveNext()

       ENDDO
       SET( _SET_DECIMALS, nDecimals )


   xbrowse(aregistros)

 for i := 1 to len( aRegistros )
     cFiltro += ADODEFLDRECNO() + " = " + aregistros[i]

     if i != len( aRegistros )
        cFiltro += " AND "
     endif
  next

  MSGINFO(cfiltro)


       oRecordSet:Filter := cFiltro //aBookMarks   //ARRAY OF BOOKMARKS

 

Its great that work but it doesnt seem a correct solution.
If there are a couple thousand records will :Filter work with such long cFiltro? I doubt.