ADO enhansed TwBrowse ?

[Rick Lipkin]

To All

Working with a local access table and FWH Twbrowse seems to work with decent responce in painting the listbox .. BUT .. connecting to a SQL server using ADO and FW Twbrowse is very slow ..

The ( fwh ) Browse will start and you will see a corner in the left open and sit there till Twbrowse can get it's 'bearings'. I realize that Twbrowse has to figure out it's parameters. top, bottom, recno?? but it is painfully slow.

Even worse is Hernan's TwBrowse replacement class .. it never opens the recordset... and looking at the code .. it doesn't even take into consideration ADO .. just rdd and I am not using an rdd to calc the recordset.

Is there a way to modify either FW TWbrowse or Hernan's Twbrowse to give any better performance with ADO.

Thanks
Rick Lipkin

[Antonio Linares]

Rick,

Have you already tested the free ADORDD that we have published ?

Browses work very fine with it

[Rick Lipkin]

Antonio

To be honest with you .. no, I have not tried the adordd. Would you be so kind as to send it to my private e-mail .. I will give it a good test..

I have been following your development .. does the download include a brief readme on the syntax .. for the connection strings ?? I guess the .ch would tell me a great deal as well ..

Be anxious to see how it works.

Rick Lipkin
lipkinrm29063@yahoo.com

[Antonio Linares]

Rick,

Already sent the email

[R.F.]

Rick:

Where are your placing the RecordSet Cursor ?

For browsing my best advice is to place the cursor on the client size because in the server size it takes ALOT of time on retrieving data.

Have you modified the gotop, gobottom, bskip and blogiclen blocks ?

[Rick Lipkin]

Rene

I am using the 'standard' code .. I presume the recordset is being cached to the local workstation ?

Here is the code:

// access.prg

#include "Fivewin.ch"

Static lOK,oWND1,oBRW,oBROW,oBROW2
Static oSAY, cSAY, oSAY1, cSAY1
//-------------------------------------------
Func Access()

LOCAL oErr, SAYING, cFILENUM, oCN, cNAME, nLEN, nPOS, oRs, oDLG
LOCAL lOK

SET CENTURY on
SET EPOCH to 1990
SET DELETED on


oRs := CREATEOBJECT( "ADODB.Recordset" )

TRY
oRS:Open( "SELECT * FROM CERT_DATA order by Name", "Provider=SQLOLEDB;Data Source=WEBDB02;Initial Catalog=DRUG_CONTROL;User Id=drugcontroluser;Password=r2x6j3q4", 1,3 )
CATCH oErr
MsgInfo( "Error in Opening CERT_DATA table" )
RETURN NIL
END TRY

* oRs:MoveFirst()

_CertBrow(oRs )

Return(nil)


//--------------------------------------------------------
STATIC FUNCTION _CertBrow( oRs )

LOCAL nRec, oDLG

lOK := .F.

DEFINE WINDOW oWnd1 ;
FROM 3,1 to 30,100 ;
Menu Buildmenu(oRS);
TITLE "REGISTRATION Registration Information Browse"

@ 0,0 LISTBOX oBrw FIELDS ;
oRs:Fields( "name" ):Value,;
oRs:Fields( "addr1" ):Value,;
oRs:Fields( "addr2" ):Value,;
oRs:Fields( "addr3" ):Value,;
oRs:Fields( "city" ):Value,;
oRs:Fields( "state" ):Value,;
oRs:Fields( "zip" ):Value,;
oRs:Fields( "zip4" ):Value,;
oRs:Fields( "schd" ):Value,;
dtoc( oRs:Fields( "expir_date" ):Value),;
dtoc( oRs:Fields( "init_date" ):Value );
SIZES 300,100,100,100,80,80,80,80,80;
HEADERS "Name",;
"Address1 ",;
"Address2 ",;
"Address3 ",;
"City ",;
"State",;
"Zip ",;
"Zip4 ",;
"Schd ",;
"Expire Date",;
"Init_date ";
ON DBLCLICK( MsgInfo( "Mark Record to Keep" ) );
UPDATE

oBrw:bLogicLen := { || oRs:RecordCount }
oBrw:bGoTop := { || oRs:MoveFirst() }
oBrw:bGoBottom := { || oRs:MoveLast() }
oBrw:bSkip := { | nSkip | Skipper( oRs, nSkip ) }
oBrw:cAlias := "ARRAY"

oWND1:oClient := oBRW

ACTIVATE WINDOW oWND1 ;
VALID ( IIF( !lOK, _LwmfsClose(.T., oRs), .F. ))

RETURN NIL

//------------------------
Static FUNCTION _LwmfsClose( lCLEAN, oRS )

IF lCLEAN = .T.
lOK := .T.
oBRW:cALIAS := nil
oRs:Close()
ENDIF

RETURN(.T.)


//--------------------------------------
Static FUNCTION BuildMenu( oRS )

LOCAL oMenu1

MENU oMenu1

MENUITEM "&Find ..." // ;
* ACTION ( _DocFind( oRS ), ;
* oBRW:Refresh() ) ;
* MESSAGE "Find a Site"


MENUITEM "&Quit" ;
ACTION oWnd1:END()
ENDMENU

RETURN( oMenu1 )

//-------------------------------
STATIC FUNCTION SKIPPER( oRsx, nSkip )

LOCAL nRec := oRsx:AbsolutePosition

oRsx:Move( nSkip )

IF oRsx:EOF; oRsx:MoveLast(); ENDIF
IF oRsx:BOF; oRsx:MoveFirst(); ENDIF

RETURN( oRsx:AbsolutePosition - nRec )


//----------------------
Static Func _Docfind(oRS)

LOCAL cITEM, oDLG,olbx

cITEM := "File Number"

DEFINE DIALOG oDlg ;
FROM 1, 3 to 18, 35 ;
TITLE "Site Locate Routine"

@ 0,0 LISTBOX oLBX var cITEM ;
ITEMS { "File Number" } ;
on LEFT DBLCLICK _FINDENTR(cITEM,oDLG,oRS) ;
of oDlg SIZE 120,100 ;
MESSAGE "Please select an Item"

@ 6,4 BUTTON "&Ok" ;
SIZE 25,10 of oDLG ;
ACTION( _FINDENTR(cITEM, oDLG, oRS), ;
oDLG:END(), cITEM := NIL ) ;
DEFAULT

@ 6,11 BUTTON "&Quit" ;
SIZE 25,10 of oDLG ;
ACTION oDlg:END()

ACTIVATE DIALOG oDlg

oDLG:END()


RETURN( NIL )

//-----------------------
Static FUNC _FINDENTR( cITEM,oDLG,oRS )

LOCAL cFIND, cTITLE, oDLG2

cFIND := "BOGUS"

DO CASE
CASE cITEM = "File Number"
cFIND := SPACE(10)
cTITLE := "Enter a File Number"

ENDCASE

DEFINE DIALOG oDlg1 ;
FROM 14,10 to 21,44 ;
TITLE cTITLE

@ 1,1 GET cFIND of oDLG1 PICTURE "@!"

@ 2,6 BUTTON "&Ok" ;
SIZE 25,10 of oDLG1 ;
ACTION ( GoGetum( cFIND, oRS, cITEM),;
oDlg1:END(), oDLG:END() ) ;
DEFAULT

@ 2,13 BUTTON "&Quit" ;
SIZE 25,10 of oDLG1 ;
ACTION ( oDlg1:END(), oDLG:END() )

ACTIVATE DIALOG oDlg1

oDLG1:END()
RETURN( NIL )

//---------------------------
Static FUNC GoGetum( cFIND,oRS, cITEM )

LOCAL SAYING, nRECNO, oBTN1,oBTN2
LOCAL lOK

cFIND := ALLTRIM( cFIND )

DO CASE
CASE cITEM = "File Number"

oRs:Find("file_num = '"+cFIND+"'" )

IF oRs:eof
oRs:MoveFirst()
oRs:Find("file_num = '"+cFIND+"'" )

IF oRs:eof()
Msginfo( "File Number "+cFind+" can not bre found" )
oRs:MoveFirst()
ENDIF
ENDIF

RETURN(.T.)

ENDCASE

RETURN(NIL)

// -- END

[Antonio Linares]

Rick,

Check the source code of the ADORDD to see how we do it:

oAdo := TOleAuto():New( "ADODB.Recordset" )
oAdo:CursorType = adOpenDynamic
oAdo:CursorLocation = adUseClient
oAdo:LockType = adLockPessimistic

[Rick Lipkin]

Antonio and Fernando

I am looking at the adordd.prg and I see how you are creating the recordset ..

oADO := TOleAuto():New( "ADODB.Recordset" )
oAdo:CursorType = adOpenDynamic
oAdo:CursorLocation = adUseClient
oAdo:LockType = adLockPessimistic
oAdo:Open( s_cQuery + s_cTableName, s_aConnections[ nWA ] )

//--- my code

oRs := CREATEOBJECT( "ADODB.Recordset" )

TRY
oRS:Open( "SELECT * FROM CERT_DATA order by Name", "Provider=SQLOLEDB;Data Source=WEBDB02;Initial Catalog=DRUG_CONTROL;User Id=drugcontroluser;Password=r2x6j3q4", 1,3 )
CATCH oErr
MsgInfo( "Error in Opening CERT_DATA table" )
RETURN NIL
END TRY

Please excuse the dumb question .. what is the difference in :
oADO := TOleAuto():New( "ADODB.Recordset" )
and
oRs := CREATEOBJECT( "ADODB.Recordset" )

I take it that this is what you were driving at with a client cursor vs a server cursor ??

If that is correct .. does the last two parameters 1,3 .. still allow the client recordset update visability back and forth from other network clients ??

Being rdd .. how do I define field vars and how do I write them back .. can I do something like :

Select 1
USE (AllTrim(cDbf)) VIA "ADORDD" TABLE AllTrim(cTable) SQL ;
FROM AllTrim(cFrom) USER AllTrim(cUser) PASSWORD AllTrim(cPsw)

then reference my vars with cWhatever := a->whatever and then write them back like a->whatever := cWhatever ?

I take it record locks are still not relevant here since the database handles the oportunistic locking ?? 1,3 )

I am looking forward to seeing how this works .. I will let you all know sometime tomorrow ..

Many thanks
Rick Lipkin

[R.F.]

Rick:

I reviewed your code all seems to look all rigth, I mean, the movement blocks are correct but, you have to "prepare" your RecordSet BEFORE open the query.

To "prepare" the cursor, you have to follow Antonio's instructions and use the ::CursorType, ::CursorLocation, and ::locktype properties before opening the recordset, if you don't prepare the recordset, the cursortype is Forward Only, the cursor location is on server size and the Locktype is Optimistic (default values), I guess that's why the browsing is slow.

[Antonio Linares]

Rick,

>
Select 1
USE (AllTrim(cDbf)) VIA "ADORDD" TABLE AllTrim(cTable) SQL ;
FROM AllTrim(cFrom) USER AllTrim(cUser) PASSWORD AllTrim(cPsw)
>

Yes

>
then reference my vars with cWhatever := a->whatever and then write them back like a->whatever := cWhatever ?
>

Right

[Rick Lipkin]

Antonio

I got a working browse with my code and YES .. the listbox is MUCH better when the recordset is cached locally.

Still takes a while to 'fetch' the recordset .. but that is based on the connection to the server .. which is ( in my case here ) a considerable distance from my location.

I have not tried multiple workstations updating records and the visability of those changes .. any thoughts there ??

What about indexes .. or TAGS .. generally speaking I was creating the ORDER by in the SQL statement as to how I want to see the records and on the relational tables .. opening a recordset with the sql clause .. WHERE ASSOCIATED_FILE_NUM ='"+cFILENUM+"'"+" order by date_" .. not even using an index.

In traditional dbfcdx rdd .. there is a set scope .. or filter .. I see in the adordd code:

STATIC FUNCTION ADO_SETFILTER( nWA, aFilterInfo )

local oADO := USRRDD_AREADATA( nWA )[ 1 ]

oADO:Filter = SQLTranslate( aFilterInfo[ UR_FRI_CEXPR ] )

RETURN SUCCESS

STATIC FUNCTION ADO_CLEARFILTER( nWA )

local oADO := USRRDD_AREADATA( nWA )[ 1 ]

TRY
oADO:Filter = ""
#ifndef __XHARBOUR__
CATCH
FINALLY
#else
CATCH oError
Alert( oError:Operation )
END
#endif

RETURN SUCCESS

How is that translated into rdd ?? ... set index to... set order to tag ... LOCATE ??

#command LOCATE [FOR <for>] [WHILE <while>] [NEXT <next>] ;
[RECORD <rec>] [<rest:REST>] [ALL] => ;
[ HB_AdoSetLocateFor( <(for)> ); ] ;
__dbLocate( <{for}>, <{while}>, <next>, <rec>, <.rest.> )


I know this is still a work in progress .. MARVELOUS WORK so far !!

Rick Lipkin

[Antonio Linares]

Rick,

>
I got a working browse with my code and YES .. the listbox is MUCH better when the recordset is cached locally.

Still takes a while to 'fetch' the recordset .. but that is based on the connection to the server .. which is ( in my case here ) a considerable distance from my location.
>

You may try setting the recordset CacheSize value. We have not implemented it yet, but we plan to do it:

oAdo:CacheSize := 50

>
I have not tried multiple workstations updating records and the visability of those changes .. any thoughts there ??
>

ADORDD uses oAdo:CursorType = adOpenDynamic so the changes should become visible from all workstations

> What about indexes .. or TAGS

We have not implemented them yet. Probably we will do it along this next weekend

You can use SET FILTER TO <expression> and SET FILTER TO, to clear the filter. Same as Clipper. For SQL expressions, write them between quotation marks

We are glad that you are enjoying the ADORDD

[Rick Lipkin]

Antonio

oAdo:CacheSize had no effect at any value .... but I am not complaining. This is a table with about 25k records .. and I have tested it at this location ( on the 'outer rim' ) and close to the core router. The closer to the core router .. the faster the 'fetch'

Just thought I would let you know about the cachesize

Rick Lipkin

[Antonio Linares]

Rick

Thanks for the feedback