ADO RDD xHarbour

[Antonio Linares]

Antonio,

I am checking the properties and methods of the ADO Connection object:

https://msdn.microsoft.com/en-us/library/windows/desktop/ms681546(v=vs.85).aspx

Please do a MsgInfo( oADODB:State ) before opening the recordset

[AHF]

Antonio,

We just found out that :
RecordCount isnt always accurate:

with adUseServer (cursor supported by the provider) always return the total nr of records in table even if there is a where clause in the select like Reccount()

with adUseClient (MS ccursor ) always return the true nr of rows in the recordset

Thus the OrdKeyCount mainly needed for scrollbars will not function properly with adUseServer cursor!

[Antonio Linares]

Antonio,

Many thanks for your great feedback

[AHF]

Antonio,

OrdKeyCount and RECORDCOUNT might be a problem!

The only way to solve this is to:

If we use always adUseClient because ADO knows all rows and there ist any problem but performance decreases a lot. Not advisable!
IF we use adUseServer then with a WHERE clause ADO returns wrong RECORDCOUNT and OrdKeyCount!

The only solution we for this is :

If there is a WHERE clause we have to query the table with SELECT COUNT(*) FROM ctable WHERE ....
Thus in each RECORDCOUNT and OrdKeyCount values are correct.
We replace RECORDCOUNT by the following code:

Code: Select all  Expand view  RUN

STATIC FUNCTION ADORECCOUNT(nWA,oRecordSet) //AHF
   LOCAL aAWData := USRRDD_AREADATA( nWA )
   LOCAL oCon := aAWData[WA_CONNECTION]
   LOCAL nCount := 0, cSql:=""

   IF oRecordSet:CursorLocation == adUseClient
      nCount :=  oRecordSet:RecordCount
   ELSE
      IF LEN(aAWData[WA_INDEXES]) > 0 .AND. aAWData[WA_INDEXACTIVE] > 0
         oRecordSet:close()
         //Making it lightning faster
         oRecordSet:CursorLocation := adUseServer
         oRecordSet:CursorType := adOpenForwardOnly
         oRsecordSetLockType := adLockReadOnly
         //LAST PARAMTER INSERTS cSql COUNT(*) MUST BE ALL FIELDS BECAUSE IF THERE IS A NULL        
                 //FIELD COUNTS RETURNS WRONG
         cSql := IndexBuildExp(aAWData[WA_INDEXACTIVE],aAWData,.T.) //INCLUDE COUNT
         msginfo("adoreccount 2 "+CSQL)
         //LETS COUNT IT
         oRecordSet:open(cSql,oCon)
         nCount := oRecordSet:Fields( 0 ):Value
         msginfo("adoreccount 3 NRREC "+CVALTOCHAR(NCOUNT))
         oRecordSet:close()
         //RETURNING TO DEFAULT DATA
         oRecordSet:CursorType := adOpenDynamic
         oRecordSet:CursorLocation := adUseServer //adUseClient never use ths very slow!
         oRecordSet:LockType := adLockPessimistic
         cSql := IndexBuildExp(aAWData[WA_INDEXACTIVE],aAWData,.F.)  //.F. DONT INCLUDE COUNT
          msginfo("adoreccount 4 "+CSQL)
         oRecordSet:open(cSql,oCon)
      ELSE
         nCount :=  oRecordSet:RecordCount
      ENDIF
   ENDIF  
   
   RETURN nCount  

 

Do you have any other alternative? This function its called a lot and I dont know what will be effect in the overall performance. In our small trials it seems ok but .....

I miss good old DBF or ADS:(

[Antonio Linares]

Antonio,

As far as I know oRs:RecordCount() returns the number of records of the recordset
so if you use a WHERE clause then you get less records.

Lets ask Mr. Rao

[Enrico Maria Giordano]

Antonio,

If we use always adUseClient because ADO knows all rows and there ist any problem but performance decreases a lot.

Using adUseClient the performance is better than adUseServer!

EMG

[AHF]

Enrico,

Antonio,

If we use always adUseClient because ADO knows all rows and there ist any problem but performance decreases a lot.

Using adUseClient the performance is better than adUseServer!

EMG

What DB you use?

adUseClient would be much easier for adordd (bookmarks etc) but everything I read covering this indicates that adUseClient its much slower and should be avoid.

Please note that the I'm using SELECT * FROM ... in adordd thats its really not good SQL practice but compatibility much be achieved.

[AHF]

Antonio,

Antonio,

As far as I know oRs:RecordCount() returns the number of records of the recordset
so if you use a WHERE clause then you get less records.

Lets ask Mr. Rao

Opening same dbf in 2 dif areas

One with normal index the RECORDCOUNT = nr of records dbf = ORDKEYCOUNT
Second with conditional index SELECT WHERE ... the RECORDCOUNT = nr of records dbf <> ORDKEYCOUNT that is = nr of records within the WHERE clause.
Moving through the recordset will soon or later get an error.
Please note that the recordset has in fact only the records within the condition although RECORDCOUNT reports all records.

This is using serverside cursor.

[Enrico Maria Giordano]

Antonio,

What DB you use?

MDB, MSSQL and MySQL.

adUseClient would be much easier for adordd (bookmarks etc) but everything I read covering this indicates that adUseClient its much slower and should be avoid.

adUseClient is faster in my experience.

Please note that the I'm using SELECT * FROM ... in adordd thats its really not good SQL practice but compatibility much be achieved.

???

SELECT * FROM is the base of all SQL queries. What else would you want to use?

EMG

[AHF]

Please note that the I'm using SELECT * FROM ... in adordd thats its really not good SQL practice but compatibility much be achieved.

???

SELECT * FROM is the base of all SQL queries. What else would you want to use?

EMG

I mean avoid loading all the fields instead of just the ones we need for the task.

[Enrico Maria Giordano]

Antonio

I mean avoid loading all the fields instead of just the ones we need for the task.

Ok, but what if I need all the fields?

EMG

[AHF]

Antonio

I mean avoid loading all the fields instead of just the ones we need for the task.

Ok, but what if I need all the fields?

EMG

Then SELECT colname FROM table unfortunatly in adordd its always all the fields! Its the price for no code change.

[Enrico Maria Giordano]

Antonio,

Then SELECT colname FROM table unfortunatly in adordd its always all the fields! Its the price for no code change.

Ok, I see, you're right.

EMG

[AHF]

Antonio,

Can you find rdd function for close all ?

[Antonio Linares]

Do you mean DbCloseAll() ?

I think you have to implement UR_CLOSE