topic-30266

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: ADO RDD xHarbour
Posted: Sat May 23, 2015 08:49 PM

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 (fw): Select all Collapse

           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.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

AHF

Posts: 838

Joined: Fri Feb 10, 2006 12:14 PM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 09:04 AM

Lucas,

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

Code (fw): Select all Collapse

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.

Regards

Antonio H Ferreira

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 12:24 PM

Antonio,

Thank you.

ADO_CLEARFILTER() should be as follows:

Code (fw): Select all Collapse

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 (fw): Select all Collapse

    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 (fw): Select all Collapse

    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.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

AHF

Posts: 838

Joined: Fri Feb 10, 2006 12:14 PM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 01:31 PM

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().

Regards

Antonio H Ferreira

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 01:51 PM

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?.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

AHF

Posts: 838

Joined: Fri Feb 10, 2006 12:14 PM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 02:02 PM

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

Regards

Antonio H Ferreira

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 02:14 PM

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.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

AHF

Posts: 838

Joined: Fri Feb 10, 2006 12:14 PM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 02:29 PM

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?

Regards

Antonio H Ferreira

AHF

Posts: 838

Joined: Fri Feb 10, 2006 12:14 PM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 02:33 PM

Lucas,

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

Regards

Antonio H Ferreira

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 02:35 PM

Antonio,

I did this test:

Code (fw): Select all Collapse

       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?.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

AHF

Posts: 838

Joined: Fri Feb 10, 2006 12:14 PM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 03:09 PM

Lucas,

Try set filter to fieldrecn = nrecno and fieldrecn =nrecno

Does it works?

Regards

Antonio H Ferreira

AHF

Posts: 838

Joined: Fri Feb 10, 2006 12:14 PM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 03:22 PM

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?

Regards

Antonio H Ferreira

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 03:48 PM

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.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

AHF

Posts: 838

Joined: Fri Feb 10, 2006 12:14 PM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 03:58 PM

Lucas,

I assume then you are using this:

Code (fw): Select all Collapse

 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.

Regards

Antonio H Ferreira

lucasdebeltran

Posts: 1303

Joined: Tue Jul 21, 2009 08:12 AM

Re: ADO RDD xHarbour
Posted: Sun May 24, 2015 04:04 PM

Antonio,

No, I am using AADD( aBookMarks, oRecordSet:BookMark )

I think your approach is the same as SQLRDD.

And also, we have Scopes, faster that Filters.

Muchas gracias. Many thanks.

Un saludo, Best regards,

Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]

Implementando MSVC 2010, FWH64 y ADO.

Abandonando uso xHarbour y SQLRDD.

FiveTech Support Forums