Page 16 of 70
Re: ADO RDD xHarbour
Posted: Tue Mar 31, 2015 5:29 pm
by 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
Re: ADO RDD xHarbour
Posted: Tue Mar 31, 2015 5:35 pm
by 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?
Re: ADO RDD xHarbour
Posted: Tue Mar 31, 2015 5:53 pm
by 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
Re: ADO RDD xHarbour
Posted: Tue Mar 31, 2015 6:03 pm
by lucasdebeltran
Hello,
First of all, to compile adordd.prg with Harbour, you need to add before ANNOUNCE ADORDD:
Code: Select all | Expand
#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 | Expand
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.
Re: ADO RDD xHarbour
Posted: Tue Mar 31, 2015 6:18 pm
by 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
Re: ADO RDD xHarbour
Posted: Tue Mar 31, 2015 6:20 pm
by 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
Re: ADO RDD xHarbour
Posted: Tue Mar 31, 2015 6:25 pm
by 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.
Re: ADO RDD xHarbour
Posted: Tue Mar 31, 2015 7:17 pm
by 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.
Re: ADO RDD xHarbour
Posted: Wed Apr 01, 2015 6:20 am
by 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
Re: ADO RDD xHarbour
Posted: Wed Apr 01, 2015 7:08 am
by 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?
Re: ADO RDD xHarbour
Posted: Wed Apr 01, 2015 7:12 am
by AHF
Antonio,
What Rdd function DbCommit() and DbCommitAll() are calling ?
I need it to design the transactions.
Re: ADO RDD xHarbour
Posted: Wed Apr 01, 2015 7:29 am
by Antonio Linares
Antonio,
From Harbour/src/rdd/dbcmd.c
Code: Select all | Expand
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();
}
Re: ADO RDD xHarbour
Posted: Wed Apr 01, 2015 7:55 am
by 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 | Expand
//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.
Re: ADO RDD xHarbour
Posted: Wed Apr 01, 2015 9:25 am
by Antonio Linares
Antonio,
The same but for each workarea:
Code: Select all | Expand
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 );
}
Re: ADO RDD xHarbour
Posted: Wed Apr 01, 2015 10:06 am
by 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 | Expand
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()