ADO RDD xHarbour

[AHF]

Antonio, Mr Rao,

The incremental search in xbrowse doesnt emulate propably the lSoftseek and since it works with filter can take an eternity.

Im trying this alternative and I would like to have your opinions:

oRecordSet.MoveLast

oRecordSet:Find( "NAME = 'whatever' ", , adSearchBackward)

oRecordSet:Move(1) == lSoftSeek

The remaining problem is when the cKey in dbseek corresponds to multiple fields but we dont have this case in our app

[AHF]

Antonio,

Thanks

Our trials are running quite good although its still to soon to say how it will work and with what degree of compatibility with existing code.

I hope I can solve all upcoming problems that Im sure will be many.

Im going into reations do you have any news about my previous questions on this matter?

[Antonio Linares]

Antonio,

Very good to know that it is working fine on your tests, excellent

I don't have any specific idea regarding relationships. I would say that they could be simulated but I don't have the expertise to talk about it.

We need Rao advice

[lucasdebeltran]

Hello,

First of all, to compile adordd.prg with Harbour, you need to add before ANNOUNCE ADORDD:

Code: Select all

#ifndef __XHARBOUR__

   #include "fivewin.ch"        // as Harbour does not have TRY / CATCH
   #define UR_FI_FLAGS           6
   #define UR_FI_STEP            7
   #define UR_FI_SIZE            7 //eram 5 ahf

#endif

 

Next, I have to adapt ADO_OPEN to allow Access files, as you are using DBFS with ADS:

Code: Select all

STATIC FUNCTION ADO_OPEN( nWA, aOpenInfo )

   LOCAL aWAData := USRRDD_AREADATA( nWA )
   LOCAL cName, aField, oError, nResult
   LOCAL oRecordSet, nTotalFields, n
   LOCAL nCountIndexes // see below

   /* When there is no ALIAS we will create new one using file name */
   IF Empty( aOpenInfo[ UR_OI_ALIAS ] )
      hb_FNameSplit( aOpenInfo[ UR_OI_NAME ],, @cName )
      aOpenInfo[ UR_OI_ALIAS ] := cName
   ENDIF



/*
  //aOpenInfo[ UR_OI_NAME ] += ".dbf"

   hb_adoSetTable( aOpenInfo[ UR_OI_NAME ] )
   hb_adoSetEngine( "")
   hb_adoSetServer( "")
   hb_adoSetQuery( )
   hb_adoSetUser( "")
   hb_adoSetPassword( "" )
*/


   IF Empty( aOpenInfo[ UR_OI_CONNECT ] )
      IF EMPTY(oConnection)
          aWAData[ WA_CONNECTION ] :=  TOleAuto():New( "ADODB.Connection" )
          oConnection := aWAData[ WA_CONNECTION ]
          aWAData[ WA_TABLENAME ] := t_cTableName
          aWAData[ WA_QUERY ] := t_cQuery
          aWAData[ WA_USERNAME ] := t_cUserName
          aWAData[ WA_PASSWORD ] := t_cPassword
          aWAData[ WA_SERVER ] := t_cServer
          aWAData[ WA_ENGINE ] := t_cEngine
          aWAData[ WA_CONNOPEN ] := .T.
          aWAData[ WA_OPENSHARED ] := aOpenInfo[ UR_OI_SHARED ]



          DO CASE
          CASE Lower( Right( aOpenInfo[ UR_OI_NAME ], 4 ) ) == ".mdb"
             IF Empty( aWAData[ WA_PASSWORD ] )
                aWAData[ WA_CONNECTION ]:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + aOpenInfo[ UR_OI_NAME ] )
             ELSE
                aWAData[ WA_CONNECTION ]:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + aOpenInfo[ UR_OI_NAME ] + ";Jet OLEDB:Database Password=" + AllTrim( aWAData[ WA_PASSWORD ] ) )
             ENDIF
          CASE Lower( Right( aOpenInfo[ UR_OI_NAME ], 4 ) ) == ".xls"
             aWAData[ WA_CONNECTION ]:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + aOpenInfo[ UR_OI_NAME ] + ";Extended Properties='Excel 8.0;HDR=YES';Persist Security Info=False" )

          CASE Lower( Right( aOpenInfo[ UR_OI_NAME ], 4 ) ) == ".dbf"
             cStr :=   CFILEPATH(aOpenInfo[ UR_OI_NAME ])
             cStr := SUBSTR(cStr,1,LEN(Cstr)-1)
             cStr := "d:\followup-testes\tESTES FOLLOWUP.add"
//aWAData[ WA_CONNECTION ]:Open(adoconnect()) //trials
             aWAData[ WA_CONNECTION ]:Open("Provider=Advantage OLE DB Provider;User ID=adssys;Data Source="+cStr+";TableType=ADS_CDX;"+;
                "Advantage Server Type=ADS_LOCAL_SERVER;")

//OTHE PROVIDERS FOR DBF FOR TRIALS
// aWAData[ WA_CONNECTION ]:Open("Provider=vfpoledb.1;Data Source="+cStr+";Collating Sequence=machine;")
// aWAData[ WA_CONNECTION ]:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + cstr + ";Extended Properties=dBASE IV;User ID=Admin;Password=;" )

          CASE Lower( Right( aOpenInfo[ UR_OI_NAME ], 3 ) ) == ".db"
             aWAData[ WA_CONNECTION ]:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + aOpenInfo[ UR_OI_NAME ] + ";Extended Properties='Paradox 3.x';" )

          CASE aWAData[ WA_ENGINE ] == "MYSQL"
             aWAData[ WA_CONNECTION ]:Open( "DRIVER={MySQL ODBC 3.51 Driver};" + ;
                "server=" + aWAData[ WA_SERVER ] + ;
                ";database=" + aOpenInfo[ UR_OI_NAME ] + ;
                ";uid=" + aWAData[ WA_USERNAME ] + ;
                ";pwd=" + aWAData[ WA_PASSWORD ] )

          CASE aWAData[ WA_ENGINE ] == "SQL"
             aWAData[ WA_CONNECTION ]:Open( "Provider=SQLOLEDB;" + ;
                "server=" + aWAData[ WA_SERVER ] + ;
                ";database=" + aOpenInfo[ UR_OI_NAME ] + ;
                ";uid=" + aWAData[ WA_USERNAME ] + ;
                ";pwd=" + aWAData[ WA_PASSWORD ] )

          CASE aWAData[ WA_ENGINE ] == "ORACLE"
             aWAData[ WA_CONNECTION ]:Open( "Provider=MSDAORA.1;" + ;
                "Persist Security Info=False" + ;
                iif( Empty( aWAData[ WA_SERVER ] ), ;
                "", ";Data source=" + aWAData[ WA_SERVER ] ) + ;
                ";User ID=" + aWAData[ WA_USERNAME ] + ;
                ";Password=" + aWAData[ WA_PASSWORD ] )

          CASE aWAData[ WA_ENGINE ] == "FIREBIRD"
             aWAData[ WA_CONNECTION ]:Open( "Driver=Firebird/InterBase(r) driver;" + ;
                "Persist Security Info=False" + ;
                ";Uid=" + aWAData[ WA_USERNAME ] + ;
                ";Pwd=" + aWAData[ WA_PASSWORD ] + ;
                ";DbName=" + aOpenInfo[ UR_OI_NAME ] )
          ENDCASE
      ELSE
         // ITS ALREDY OPE THE ADODB CONN USE THE SAME WE WANT TRANSACTIONS WITHIN THE CONNECTION
          aWAData[ WA_CONNECTION ] :=  oConnection
          aWAData[ WA_TABLENAME ] := t_cTableName
          aWAData[ WA_QUERY ] := t_cQuery
          aWAData[ WA_USERNAME ] := t_cUserName
          aWAData[ WA_PASSWORD ] := t_cPassword
          aWAData[ WA_SERVER ] := t_cServer
          aWAData[ WA_ENGINE ] := t_cEngine
          aWAData[ WA_CONNOPEN ] := .T.
          aWAData[ WA_OPENSHARED ] := aOpenInfo[ UR_OI_SHARED ]
      ENDIF
   ELSE
      // here we dont save oconnection for the next one because
      // we assume that is not application defult conn but a temporary conn
      //to other db system.
      aWAData[ WA_CONNECTION ] := TOleAuto():New("ADODB.Connection")
      aWAData[ WA_CONNECTION ]:Open( aOpenInfo[ UR_OI_CONNECT ] )
      aWAData[ WA_TABLENAME ] := t_cTableName
      aWAData[ WA_QUERY ] := t_cQuery
      aWAData[ WA_USERNAME ] := t_cUserName
      aWAData[ WA_PASSWORD ] := t_cPassword
      aWAData[ WA_SERVER ] := t_cServer
      aWAData[ WA_ENGINE ] := t_cEngine
      aWAData[ WA_CONNOPEN ] := .F.
   ENDIF
   /* will be initilized */
   t_cQuery := ""

   IF Empty( aWAData[ WA_QUERY ] )
      aWAData[ WA_QUERY ] := "SELECT * FROM "
   ENDIF

   oRecordSet :=  TOleAuto():New( "ADODB.Recordset" )

   IF oRecordSet == NIL
      oError := ErrorNew()
      oError:GenCode := EG_OPEN
      oError:SubCode := 1001
      oError:Description := hb_langErrMsg( EG_OPEN )
      oError:FileName := aOpenInfo[ UR_OI_NAME ]
      oError:OsCode := 0 /* TODO */
      oError:CanDefault := .T.

      UR_SUPER_ERROR( nWA, oError )
      RETURN HB_FAILURE
   ENDIF

   oRecordSet:CursorType := adOpenDynamic
   oRecordSet:CursorLocation := adUseServer  // adUseClient its slower but has avntages such always bookmaks
   oRecordSet:LockType := adLockPessimistic


   // LUCAS
   //aWAData[ WA_TABLENAME ] := SUBSTR(CFILENOPATH(aWAData[ WA_TABLENAME ] ),1,LEN(CFILENOPATH(aWAData[ WA_TABLENAME ] ))-4)

   IF aWAData[ WA_QUERY ] == "SELECT * FROM "

      ALERT(aWAData[ WA_TABLENAME ])
      oRecordSet:Open( aWAData[ WA_QUERY ] + aWAData[ WA_TABLENAME ], aWAData[ WA_CONNECTION ])
      //this should allow prop index and seek  adCmdTableDirect
   ELSE
      oRecordSet:Open( aWAData[ WA_QUERY ], aWAData[ WA_CONNECTION ],,,adCmdTableDirect )
   ENDIF

   aWAData[ WA_RECORDSET ] := oRecordSet
   aWAData[ WA_BOF ] := aWAData[ WA_EOF ] := .F.

   UR_SUPER_SETFIELDEXTENT( nWA, nTotalFields := oRecordSet:Fields:Count )

   FOR n := 1 TO nTotalFields

      aField := Array( UR_FI_SIZE )
      aField[ UR_FI_NAME ] := oRecordSet:Fields( n - 1 ):Name
      aField[ UR_FI_TYPE ]    := ADO_FIELDSTRUCT( oRecordSet, n-1 )[7]
      aField[ UR_FI_TYPEEXT ] := 0
      aField[ UR_FI_LEN ]     := ADO_FIELDSTRUCT( oRecordSet, n-1 )[3]
      aField[ UR_FI_DEC ]     := ADO_FIELDSTRUCT( oRecordSet, n-1 )[4]
      aField[ UR_FI_FLAGS ] := 0  // xHarbour expecs this field
      aField[ UR_FI_STEP ] := 0 // xHarbour expecs this field


      alert(nwa)
      xbrowser @afield FASTEDIT
      
// here error happens:
UR_SUPER_ADDFIELD( nWA, aField )
   NEXT

   nResult := UR_SUPER_OPEN( nWA, aOpenInfo )

   IF nResult == HB_SUCCESS
      ADO_GOTOP( nWA )
   ENDIF

   RETURN nResult


 

But, in UR_SUPER_ADDFIELD( nWA, aField ) i get:

Error description: Error ADORDD/1003 Argument error

Stack Calls
===========
Called from: => UR_SUPER_ADDFIELD(0)

But xbrowse shows values, so the Access database is opened.

Something wrong with aField structure?.

Thank you.

[AHF]

Antonio,

I can discover only 2 possible ways:

a) 2 recordsets the parent look with find in the child. With server cursor should be very fast.

b) 1 recordset parent and multiple recordsets as child when parent looks by corresponding match.

I remember a read somewere that there was subrecordset but I dont remember if it was with ado.net


Meantime I post most recent version adordd.prg adordd.ch with seeks working like in dbfs https://github.com/AHFERREIRA/adordd.git

[Antonio Linares]

Antonio,

b) 1 recordset parent and multiple recordsets as child when parent looks by corresponding match.

Yes, thats my idea unless there is a better way to do it using ADO features.

We need Rao advice, or other ADO advanced users advice

[AHF]

Lucas,

You re right Ill apply your changes.

Comment these and check what is the UR_FI_SIZE in hbusrdd.ch

aField[ UR_FI_FLAGS ] := 0 // xHarbour expecs this field
aField[ UR_FI_STEP ] := 0 // xHarbour expecs this field

I think Harbour ony accepts 5 ele.

[lucasdebeltran]

Hello,

In hbusrrdd.ch, there is:

#define UR_FI_SIZE 5

Yes, before posting I comment out with no luck:

// aField[ UR_FI_FLAGS ] := 0 // xHarbour expecs this field
// aField[ UR_FI_STEP ] := 0 // xHarbour expecs this field

Antonio, maybe as Antonio´s developing a great efford he could enjoy latest FWH?.

Having ADORDD for the community will be a goal.

Thank you.

[AHF]

Lucas,

I appreciate your proposal but I have no time now to install new versions of FWH or Harbour or xHarbour.

The alternatives in hbusrdd.ch are (one of wich shoul work) :

a) #define UR_FI_SIZE 5
comment in ado_open
// aField[ UR_FI_FLAGS ] := 0 // xHarbour expecs this field
// aField[ UR_FI_STEP ] := 0 // xHarbour expecs this field

b) #define UR_FI_SIZE 7
add in hbusrrd.ch
#define UR_FI_FLAGS 6
# define UR_FI_STEP 7
uncomment comment in ado_open
aField[ UR_FI_FLAGS ] := 0 // xHarbour expecs this field
aField[ UR_FI_STEP ] := 0 // xHarbour expecs this field

[AHF]

Antonio, Enrico, Mr Rao,

It seems that in ADO datarelations are not available like in ADO.NET so we have to do everything ourselves from scratch.

Ive found this article:

One of the biggest differences between traditional ADO and ADO.NET is that the rowsets stored within ADO.NET can be truly relational. For example, a DataSet can store one DataTable containing customers and another DataTable containing the customers' orders. These DataTable objects can then be related to one another within ADO.NET, thus recreating the relationship that exists within the relational database. In ADO.NET once you retrieve two rowsets of data (in other words, parents and children) and relate them to each other, you can then retrieve all children rows for a given parent, display any one DataTable in a grid at a time, or modify several tiers of DataTable objects, and send the changes to the database all in one batch update. DataRelation objects, which are integral to ADO.NET applications, enable these features to function.

Do you have any ideas on the approach?

[AHF]

Antonio,

What Rdd function DbCommit() and DbCommitAll() are calling ?

I need it to design the transactions.

[Antonio Linares]

Antonio,

From Harbour/src/rdd/dbcmd.c

Code: Select all

HB_FUNC( DBCOMMIT )
{
   AREAP pArea = ( AREAP ) hb_rddGetCurrentWorkAreaPointer();

   if( pArea )
      SELF_FLUSH( pArea );
   else
      hb_errRT_DBCMD( EG_NOTABLE, EDBCMD_NOTABLE, NULL, HB_ERR_FUNCNAME );
}

HB_FUNC( DBCOMMITALL )
{
   hb_rddFlushAll();
}

[AHF]

Antonio,

Im trying this code to control if a table can be opened exclusive.

The probem is I dont know what to return to the open to be failled.

Code: Select all

   //OPEN EXCLUSIVE MUST BE WITHIN A TRANSACTION!
   IF !aWAData[ WA_OPENSHARED ] 
      //THERE IS ANOTHER TOP LEVEL TRANSACTION OPEN EXCLUSIVE
       IF aWAData[ WA_CONNECTION ]:BeginTrans() > 1  
            aWAData[ WA_CONNECTION ]:CommitTrans() // CLOSE TRANSACT THERE IS NOTHING TO COMMITT
         nResult := HB_SUCCESS 
       ELSE  //TABLE EXCLUSIVE NOT WITHIN TRANSACTION FAIL TO OPEN
           NETERR(.F.)
          nResult := HB_FAILURE
       ENDIF
   ENDIF     

Dbcommit it go always to UR_FLUSH that goes to ADO_FLUSH right?
But how can I trap hb_rddFlushAll()? This one isnt going to the rdd.

[Antonio Linares]

Antonio,

The same but for each workarea:

Code: Select all

void hb_rddFlushAll( void )
{
   PHB_STACKRDD pRddInfo = hb_stackRDD();
   HB_USHORT uiArea = ( HB_AREANO ) hb_rddGetCurrentWorkAreaNumber(), uiIndex;

   for( uiIndex = 1; uiIndex < pRddInfo->uiWaMax; ++uiIndex )
   {
      hb_rddSelectWorkAreaNumber( ( ( AREAP ) pRddInfo->waList[ uiIndex ] )->uiArea );
      SELF_FLUSH( ( AREAP ) pRddInfo->pCurrArea );
   }
   hb_rddSelectWorkAreaNumber( uiArea );
}

[AHF]

Antonio,

Regarding concurrent access I choose to initiate a transaction as soon as the first lock is called and end it as soon a dbcommit is called.
I assume that the code of apps its written in this mode, dbcommit its only called after all changes otherwise the result migh be unpredictable.
We could also do it with unlock. What is your opinion?

Do you think this code works?

Code: Select all

STATIC FUNCTION ADO_LOCK( nWA, aLockInfo )

   LOCAL aWdata := USRRDD_AREADATA( nWA ),B,AA:= {}
   
   HB_SYMBOL_UNUSED( nWA )
   
   IF EMPTY(aLockInfo[ UR_LI_RECORD ]) .OR. aLockInfo[ UR_LI_METHOD ] = DBLM_EXCLUSIVE
      ADO_UNLOCK( nWA, aLockInfo[ UR_LI_RECORD ] )
   ENDIF   

   /*
   UR_LI_METHOD VALUES CONSTANTS
   DBLM_EXCLUSIVE 1 RELEASE ALL AND LOCK CURRENT 
   DBLM_MULTIPLE 2 LOCK CURRENT AND ADD TO LOCKLIST
   DBLM_FILE 3 RELEASE ALL LOCKS AND FILE LOCK 
   */
   
   IF aLockInfo[UR_LI_METHOD] = DBLM_FILE
      aLockInfo[ UR_LI_RESULT ] := .T.
      aWdata[ WA_FILELOCK ] := .T.
   ELSE
      aLockInfo[ UR_LI_RECORD ] := RecNo()
      aLockInfo[ UR_LI_RESULT ] := .T.
      AADD(aWdata[ WA_LOCKLIST ],aLockInfo[ UR_LI_RECORD ])
   ENDIF  
   
    /* TRANSACTION CONCURRENT ACCESS THIS COULD BE MADE BY A QUERY TO THE DB BUT DIFFERENT
        SYNTAXES ARE USED BETWEEN EACH DB*/
    IF aWdata[ WA_CONNECTION ]:BeginTrans() > 1 //already protected by  a existing transaction do nothing
       aWdata[ WA_CONNECTION ]:RollbackTrans()  //started above theres nothing to rollback only to cancel it
    ENDIF
    
   RETURN HB_SUCCESS

   
STATIC FUNCTION ADO_UNLOCK( nWA, xRecID )

   LOCAL aWdata := USRRDD_AREADATA( nWA ),n

   HB_SYMBOL_UNUSED( xRecId )
   HB_SYMBOL_UNUSED( nWA )
   
   IF !EMPTY(xRecID)
      n := ASCAN(aWdata[ WA_LOCKLIST ],xRecID)
      IF n > 0
         ADEL(aWdata[ WA_LOCKLIST ],n,.T.)
      ENDIF
   ELSE
      aWdata[ WA_LOCKLIST ] := {}
      aWdata[ WA_FILELOCK ] := .F.
   ENDIF   
   
   RETURN HB_SUCCESS

 STATIC FUNCTION ADO_FLUSH( nWA )

   LOCAL oRecordSet := USRRDD_AREADATA( nWA )[ WA_RECORDSET ]

   TRY
   
      oRecordSet:Update()
      
      /* TRANSACTION CONCURRENT ACCESS THIS COULD BE MADE BY A QUERY TO THE DB BUT DIFFERENT 
           SYNTAXES ARE USED BETWEEN EACH DB*/
      IF aWdata[ WA_CONNECTION ]:BeginTrans() > 1 //already protected by  a existing transaction do othing
         aWdata[ WA_CONNECTION ]:CommitTrans()  //started above theres nothing to commit only to cancel it
      ENDIF
          //now we commit current transaction
      aWdata[ WA_CONNECTION ]:CommitTrans()
      
   CATCH
   
      IF aWdata[ WA_CONNECTION ]:BeginTrans() > 1 //already protected by  a existing transaction do othing
         aWdata[ WA_CONNECTION ]:CommitTrans()  //started above theres nothing to commit only to cancel it
      ENDIF
          //now we roll back current transaction
      aWdata[ WA_CONNECTION ]:RollbackTrans()
      
   END

   RETURN HB_SUCCESS

 

How can I do to return a failure use in ado_open()