ADO RDD xHarbour

[Antonio Linares]

Antonio,

I'm converting a big app and till now in browses didn't need to change a single line of code!

Wonderful!

[russimicro]

with translate.google.com

great initiative

1. They give the ultimate solution database engine ... update with trend data management
2. For those who have SQLRDD of xharbour.com ... could go with fwh + harbor + ADORDD
3. Invite colleagues who have some kind of rdd ( mysql , dhs, etc ) ,,, to join efforts towards common product and very comprehensive
4. Would give an identity to products: Fivewin SQL , fiveTouch and fiveWeb .. excellent working tool
5. We could think of a contribution to encourage this development , much like the QT project or EasyReport

Greetings ...

Johnson Russi
Colombia

[AHF]

New version of adordd at https://github.com/AHFERREIRA/adordd.git

Please help trying it.

[elvira]

Hello,

Sorry, but it does not work under Harbour. Many warnings and errors when compiling...

[AHF]

Elvira,

Place this at top adordd.prg:
This has not been tested by me because I only use xHarbor.

[code][#ifndef __XHARBOUR__

#xcommand TRY => BEGIN SEQUENCE WITH {| oErr | Break( oErr ) }
#xcommand CATCH [<!oErr!>] => RECOVER [USING <oErr>] <-oErr->
#xcommand FINALLY => ALWAYS

#include "fivewin.ch" // as Harbour does not have TRY / CATCH IF YOU DONT HAVE COMENT THIS LINE
#define UR_FI_FLAGS 6
#define UR_FI_STEP 7
#define UR_FI_SIZE 5 // by Lucas for Harbour

//13.04.15 functions given by thefull to compile with Harbour WITHOUT FIVEWIN
function cValToChar( u ); return CStr( u )
function MsgInfo( u ) ; return Alert( u )
function MsgAlert( u ); return Alert( u )

function cFilePath( cPathMask ) // returns path of a filename

local n := RAt( "\", cPathMask ), cDisk

return If( n > 0, Upper( Left( cPathMask, n ) ),;
( cDisk := cFileDisc( cPathMask ) ) + If( ! Empty( cDisk ), "\", "" ) )

function cFileNoPath( cPathMask )

local n := RAt( "\", cPathMask )

return If( n > 0 .and. n < Len( cPathMask ),;
Right( cPathMask, Len( cPathMask ) - n ),;
If( ( n := At( ":", cPathMask ) ) > 0,;
Right( cPathMask, Len( cPathMask ) - n ),;
cPathMask ) )

function cFileNoExt( cPathMask ) // returns the filename without ext

local cName := AllTrim( cFileNoPath( cPathMask ) )
local n := RAt( ".", cName )

return AllTrim( If( n > 0, Left( cName, n - 1 ), cName ) )

function cFileDisc( cPathMask ) // returns drive of the path

return If( At( ":", cPathMask ) == 2, ;
Upper( Left( cPathMask, 2 ) ), "" )

#pragma BEGINDUMP
#include <hbapi.h>

HB_FUNC( LAND )
{
hb_retl( ( hb_parnl( 1 ) & hb_parnl( 2 ) ) != 0 );
}

#pragma ENDDUMP

#endif
/code]

[AHF]

Antonio, Enrico,Mr Rao,

We have to decide for a way to go but with a few trials and small tables (30.000 records) its difficult to take an option.
May be you have some experience with it.
The problem is when we make a seek with more than on field in the seek expression.(locate will be the same):

1) Today's way in adordd
Opens another record set with where clause for that seek expression
This becomes the working record set with only the records matching the seek expression.
When we need to go to a record outside of this scope adordd has to "guess" it and return the previous
working record set.
This tends to be cumbersome.
Pros : Very fast.
One operation only- open new recordset
Cons: adordd has to do some guessing

2) Another way is to have a new record set with the records matching the seek expression.
Get the record number (defined autoinc field ) from the new record set accordingly to lsoftseek, lfindlast
Close the new record set.
:Find the record number from the new record set in the actual working record set
Return to normal operation.
Pros: adordd has nothing to "guess", same behavior as dbfs
In Oracle seems to be very fast in others DB dont know!
Cons: number of operations :
Open a new recordset
Close new recordset
:Find record in actual working record set
It will not work without some autoinc field to be used as recno because bookmarks
are not guarantee to be the same between difference record sets

EX.

Code: Select all  Expand view  RUN

nrec := recno()
if seek "whatever whatever2" // 2 fields in seek expression  // 1) new recordset 2) the same
  do while seek expression = .t.
       ....
      skip
  enddo
endif
go to nrec // 1) out of the scope needs to revert to previous record set 2) the same record set :find that record
 

Personally I like more option 2 but....

Please give me your ideas.

[Enrico Maria Giordano]

Antonio,

Cons: adordd has to do some guessing

Please explain this point.

EMG

[AHF]

Antonio,

Cons: adordd has to do some guessing

Please explain this point.

EMG

Enrico,

In option 1) adordd creates a new recordset but the nrec saved before the seek it will not exist in the new recordset that becomes the working one.
Thus adordd tries to find that record when go to nrec gets called, if it doesnt reverts to the previous recordset (without where clause seek expression).
So adordd guesses if the record its not in the current recordset its in the previous one and restart it.

In option 2) that problem doesnt exists because the working recordset never changes.
The new open recordset with where clause matching the seek expression its only used to extract the record number of the first match and then find it in the working recordset.
The working recordset never changes.

The best way for you to understand is test it:

Code: Select all  Expand view  RUN

nrec :=recno()
seek expression with 2 fields
browse()
go to nrec //error
set index to
go to nrec  //ok
browse()
 

Can you see the difference?

The main issue here is performance.

Can you test this with MySql with tables bigger than 100.0000 recs and with cursor adUseClient and adUseServer ?

[hmpaquito]

neither the first nor the second option, but both


my proposal:

basic programming: compatibility mode: 2nd option

advanced programming: 1st option. New command ADORDD_SEEK

[AHF]

neither the first nor the second option, but both


my proposal:

basic programming: compatibility mode: 2nd option

advanced programming: 1st option. New command ADORDD_SEEK

hmpaquito,

Its a good proposal.

Then we would have :
ADO_SEEK(cExpression) = seek build new recordset
ADO_SEEK() reset to previous recordset

And its to the programmer to decide where to pace each.

[AHF]

The question remains is :find quick enough to work with in average size tables ( 100.0000 records) in every DB?

[AHF]

Antonio

I use the ADO Class and methods to create recordsets to manage SQL.. and creating separate recordsets based on the primary key and the foreign key or a single recordset based on inner and outer joins.. not necessarily like we used to with .Dbf.

I am watching this thread with interest.

Rick Lipkin

Rick,

Could you share code for creating separate recordsets based on the primary key and the foreign key ?

[Rick Lipkin]

Here are an example .. lets open an Invoice table for [InvoiceNumber] = 354 then open the InvoiceDetail for the foreign key also named [InvoiceNumber]

Code: Select all  Expand view  RUN

Func _Invoice(nRepairNumber)  // 354

Local cSql,oRsInvoice,oRsInvoiceDetail,oErr

...
...
..

cSql := "Select * from [Invoice] where [Invoice Number] = "+ltrim(str(nRepairNumber))

oRsInvoice := TOleAuto():New( "ADODB.Recordset" )
oRsInvoice:CursorType     := 1        // opendkeyset
oRsInvoice:CursorLocation := 3        // local cache
oRsInvoice:LockType       := 3        // lockoportunistic

TRY
   oRsInvoice:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening INVOICE table" )
   RETURN(.F.)
END TRY

If oRsInvoice:Eof
   Msginfo( "Could not find Invoice "+trim(str(nRepairNumber)) )
   oRsInvoice:CLose()
   oRsInvoice := nil
   Return(.f.)
Endif

// open detail table

cSql := "Select * from [InvoiceDetail] where [Invoice Number] = "+ltrim(str(nRepairNumber))

oRsInvoiceDetail := TOleAuto():New( "ADODB.Recordset" )
oRsInvoiceDetail:CursorType     := 1        // opendkeyset
oRsInvoiceDetail:CursorLocation := 3        // local cache
oRsInvoiceDetail:LockType       := 3        // lockoportunistic

TRY
   oRsInvoiceDetail:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening INVOICEDETAIL table" )
   oRsInvoice:CLose()
   oRsInvoice := nil
   RETURN(.F.)
END TRY
 

This opens two recordsets you can display anyway you want ..

Rick Lipkin

[AHF]

Rick,

Thanks for sharing it.
Now adordd do exactly that with set relation to...

Do you have and performance experience with :find?

[Rick Lipkin]

Antonio

Using :Find you MUST :MoveFirst() before you issue the :Find() .. you might also use :Filter

Code: Select all  Expand view  RUN

oRsInvoice:MoveFirst()
oRsInvoice:Find( "[InvoiceNumber] = "+ltrim+str(nRepairNumber)) )

or

oRsInvoice:Filter := "[InvoiceNumber] = "+ltrim+str(nRepairNumber))

 

Rick Lipkin