FiveTech Software tech support forums

www.FiveTechSoft.com
https://forums.fivetechsupport.com/

ADO RDD xHarbour

https://forums.fivetechsupport.com/viewtopic.php?f=3&t=30266

Page 10 of 70

Re: ADO RDD xHarbour

PostPosted: Wed Mar 25, 2015 4:23 pm
by 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

Re: ADO RDD xHarbour

PostPosted: Wed Mar 25, 2015 6:15 pm
by 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!

Re: ADO RDD xHarbour

PostPosted: Wed Mar 25, 2015 6:25 pm
by Antonio Linares
Antonio,

Many thanks for your great feedback :-)

Re: ADO RDD xHarbour

PostPosted: Thu Mar 26, 2015 11:32 am
by 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


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:(

Re: ADO RDD xHarbour

PostPosted: Thu Mar 26, 2015 3:57 pm
by 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

Re: ADO RDD xHarbour

PostPosted: Thu Mar 26, 2015 4:02 pm
by Enrico Maria Giordano
Antonio,

AHF wrote: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

Re: ADO RDD xHarbour

PostPosted: Thu Mar 26, 2015 4:39 pm
by AHF
Enrico,

Enrico Maria Giordano wrote:Antonio,

AHF wrote: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.

Re: ADO RDD xHarbour

PostPosted: Thu Mar 26, 2015 4:49 pm
by AHF
Antonio,

Antonio Linares wrote: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.

Re: ADO RDD xHarbour

PostPosted: Thu Mar 26, 2015 5:29 pm
by Enrico Maria Giordano
Antonio,

AHF wrote:What DB you use?


MDB, MSSQL and MySQL.

AHF wrote: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.

AHF wrote: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

Re: ADO RDD xHarbour

PostPosted: Thu Mar 26, 2015 5:57 pm
by AHF
Enrico Maria Giordano wrote:
AHF wrote: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.

Re: ADO RDD xHarbour

PostPosted: Thu Mar 26, 2015 6:02 pm
by Enrico Maria Giordano
Antonio

AHF wrote: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

Re: ADO RDD xHarbour

PostPosted: Thu Mar 26, 2015 6:10 pm
by AHF
Enrico Maria Giordano wrote:Antonio

AHF wrote: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.

Re: ADO RDD xHarbour

PostPosted: Thu Mar 26, 2015 6:28 pm
by Enrico Maria Giordano
Antonio,

AHF wrote: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

Re: ADO RDD xHarbour

PostPosted: Thu Mar 26, 2015 6:58 pm
by AHF
Antonio,

Can you find rdd function for close all ?

Re: ADO RDD xHarbour

PostPosted: Fri Mar 27, 2015 5:41 am
by Antonio Linares
Do you mean DbCloseAll() ?

I think you have to implement UR_CLOSE
All times are UTC
Page 10 of 70
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/