ADO RDD xHarbour

[elvira]

Antonio,

Thank you but it does not:

Code: Select all  Expand view

REQUEST ADORDD, DBFCDX

function Main()

 local cPath := hb_dirbase()


  RddRegister("ADORDD",1)
  RddSetDefault("ADORDD")


SET ADODBF TABLES INDEX LIST TO {{ "TABLE1", {"TEST1", "FIRST"} ,;
{"TEST2", "LAST"} ,;
{"TEST3", "AGE"} }}

SET ADO TABLES INDEX LIST TO {{ "TABLE1", {"TEST1", "FIRST"} ,;
{"TEST2", "LAST"} ,;
{"TEST3", "AGE"} }}



SET ADO DEFAULT RECNO FIELD TO "ID"

SET AUTOPEN ON //might be OFF if you wish
SET AUTORDER TO 1 // first index opened can be other


SET ADO DEFAULT DATABASE TO CPATH+"TEST2.mdb" ENGINE TO "ACCESS" USER TO "" PASSWORD TO ""

SET ADO LOCK CONTROL SHAREPATH TO "C:" RDD TO "DBFCDX"



IF !FILE( "test2.mdb" )

DbCreate( "test2.mdb;table1", { { "FIRST", "C", 30, 0 },;
{ "LAST", "C", 30, 0 },;
{ "AGE", "N", 8, 0 },;
{ "ID" ,"+",10, 0}  }, "ADORDD" )

ENDIF



     USE table1 ALIAS "TEST2" NEW
     xbrowse()
 

Time from start: 0 hours 0 mins 0 secs
Error occurred at: 05/05/15, 16:27:09
Error description: (DOS Error -2147352567) WINOLE/1007 The database engine Microsoft Jet can not find the input table or query 'TABLE1'. Make sure it exists and that its name is spelled correctly. (0x80040E37): Microsoft JET Database Engine

Args:
[ 1] = C SELECT * FROM TABLE1
[ 2] = O ADODB.Connection

Stack Calls
===========
Called from: => TOLEAUTO:OPEN( 0 )
Called from: elvira.prg => ADO_OPEN( 295 )
Called from: => DBUSEAREA( 0 )
Called from: elvira.prg => MAIN( 58 )

Thanks .

[AHF]

Elvira,

Its a bug !

Take out test2.mdb; from dbcreate.

DbCreate( "table1", { { "FIRST", "C", 30, 0 },;
{ "LAST", "C", 30, 0 },;
{ "AGE", "N", 8, 0 },;
{ "ID" ,"+",10, 0} }, "ADORDD" )

Im correcting it but like this its working here.

Let me know if it worked.

Thanks.

[AHF]

Elvira,

If you want keep things the same way please replace in adordd in function ado_create

IF( ALLTRIM( cDataBase ) == "" ,cDataBase:= t_cDataSource, cDataBase )
IF( ALLTRIM( cTable ) == "" , cTable := aOpenInfo[ UR_OI_NAME ] ,cTable)
IF( ALLTRIM( cDbEngine ) == "" ,cDbEngine:= t_cEngine, cDbEngine )
IF( ALLTRIM( cServer ) == "" , cServer:= t_cServer, cServer )
IF( ALLTRIM( cUserName ) == "" , cUserName:= t_cUserName, cUserName )
IF( ALLTRIM( cPassword ) == "" , cPassword:= t_cPassword, cPassword )

Please note difference = to ==

That s it.

[elvira]

Antonio,

Now I get:

Error description: Error ADORDD/1022 Lock required: TABLE1

Stack Calls
===========
Called from: => UR_SUPER_ERROR( 0 )
Called from: elvira.prg => ADO_PUTVALUE( 1276 )
Called from: elvira.prg => MAIN( 76 )

I also tried with the EXCLUSIVE at USE:

USE table1 ALIAS "TEST2" NEW EXCLUSIVE

Also, how do I check if the table "table1" exists in the Access database?.

Thanks

[AHF]

Elvira,

ADOVERSION() Returns adordd version
hb_adoRddGetConnection( nWorkArea ) Returns the connection for the workarea
hb_adoRddGetRecordSet( nWorkArea ) Returns the recordset for the nWorkArea
hb_adoRddGetTableName( nWorkArea ) Returns tabe name for the nWorkArea
hb_adoRddExistsTable( oCon,cTable, cIndex ) Returns .t. if table or table and index exist on the DB
hb_adoRddDrop( oCon, cTable, cIndex, DBEngine ) Drops (delete) table or index in the DB
hb_GetAdoConnection() Returns ado default connection

Exclusive its only working if you try to open the table in another instance of app.
Updating records its still not verifying it if table its exclusive and since it does not find a lock record throws error.

All this only working next post tomorrow.

[elvira]

Antonio,

I can´t add, modify or delete records.

I get that errors.

I open the table like this:

Code: Select all  Expand view

USE table1 ALIAS "TEST2" NEW
 

And:

Code: Select all  Expand view

USE table1 ALIAS "TEST2" NEW EXCLUSIVE
 

Both cases I get errors.

Sorry, but the ADORDD is not ready yet.

[AHF]

Elvira,

Here tryadordd and variants are working perfectly.

I can add modify and delete records with browse() or with append and replace .

I dont work with xBrowse

Did you tried with browse()
Can you navigate well with xbrowse ?

Can you send to my email your trial prg?

[elvira]

Antonio,

Yes, I tested with Browse() and it fails.

Here is my full sample:

Code: Select all  Expand view

#include "fivewin.ch"

#include "adordd.ch"
#include "adordd.prg"



REQUEST ADORDD, DBFCDX

function Main()

 local cPath := hb_dirbase()


  RddRegister("ADORDD",1)
  RddSetDefault("ADORDD")


  SET ADODBF TABLES INDEX LIST TO {{ "TABLE1", {"TEST1", "FIRST"} ,;
                                               {"TEST2", "LAST"} ,;
                                               {"TEST3", "AGE"} }}

  SET ADO TABLES INDEX LIST TO {{ "TABLE1", {"TEST1", "FIRST"} ,;
                                            {"TEST2", "LAST"} ,;
                                            {"TEST3", "AGE"} }}



  SET ADO DEFAULT RECNO FIELD TO "ID"

  SET AUTOPEN ON //might be OFF if you wish
  SET AUTORDER TO 1 // first index opened can be other


  SET ADO DEFAULT DATABASE TO CPATH+"TEST2.mdb" ENGINE TO "ACCESS" USER TO "" PASSWORD TO ""

  SET ADO LOCK CONTROL SHAREPATH TO "C:" RDD TO "DBFCDX"



//IF !FILE( "test2.mdb" )
/*
DbCreate( "table1", { { "FIRST", "C", 30, 0 },;
{ "LAST", "C", 30, 0 },;
{ "AGE", "N", 8, 0 },;
{ "ID" ,"+",10, 0} }, "ADORDD" )
*/
//ENDIF



     USE table1 ALIAS "TEST2" NEW
BROWSE()


      TEST2->(  OrdSetFocus("TEST2")  )
  msginfo(ordsetfocus())




   XBROWSER FASTEDIT


   APPEND BLANK
   test2->First   := "HOMER si no Homer"
   test2->Last    := "Simpson"
   test2->Age     := 45

   APPEND BLANK
   test2->First   := cValToChar( nRandom() ) + " aaa Lara"
   test2->Last    := "Croft si no"
   test2->Age     := 32


   GO TOP

   xBrowse()
   DbCloseAll()




return nil



 

[AHF]

Antonio,

Please confirm then meaning of each method

aLockInfo[ UR_LI_METHOD ]

DBLM_EXCLUSIVE
DBLM_MULTIPLE
DBLM_FILE

[elvira]

Antonio,

Have you checked my sample?.

Thank you.

[AHF]

Elvira,

Yes and it runs ok here.

I ve found out that the problem is with cursorlocation because with ACCESS aduseclient you can not :update()
so I've change it to adUseServer but with adOpenKeyset because with adOpenDynamic ACCESS also does not work correctly.
In my tests with MYSql everything its ok.

Also since locks are enforced using Browse() you need to pass the deleteblock otherwise adorddd will raise "lock required" error as Browse() deletes without call dbrlock().

Exclusive use is still under development because it wasnt work correctly.
Locks have been readjusted and now seem to work ok.

When creating views please remember that you need to have also a autoinc field to be used as recno so you can include it in the "as select" statement.

Please send me your email and Ill send you now last adordd.

Did you managed to navigate correctly with xbrowse?

[AHF]

Mr Rao, Lucas,

How can I use adUseServer cursor with MySql?

I can only work with adUseClient.

[AHF]

New version adordd.prg posted https://github.com/AHFERREIRA/adordd.git

Its very imortant that you indicate in adordd.prg in the several places with the cursorlocation the type of cursor you need to your DB.
Not choosing the right cursor the browses become irregular.
Please remember that all browse positioning is based on absoluteposition thus a cursor that can not support it doesnt work.
Also you need a cursor that supports update() and requery().
The cursors defined in adordd support:

ACCESS
MYSQL
ORACLE

Changes:
Locks now work as it should.
Exclusive use in progress not validated

Using Browse() you must pass delete block because it does not lock the record and adorddd raises a lock required error.
Also when you change the index key value the browse() does not re-position immediately the grid. Click right or left arrows.

adordd has been working ok with :

ACCESS
MYSQL
ORACLE

both in internal network and internet and its ok.
Internet a little slow but not crawling with tables 30.000 recs.
You must find which are the best parameters for your case and adjust it in ADO_OPEN

Look for:

Code: Select all  Expand view

  //PROPERIES AFFECTING PERFORMANCE TRY
   //oRecordSet:MaxRecords := 60
   //oRecordSet:CacheSize := 50 //records increase performance set zero returns error set great server parameters max open rows error
   //oRecordset:PageSize = 10
   //oRecordSet:MaxRecords := 15
   //oRecordset:Properties("Maximum Open Rows") := 110  //MIN TWICE THE SIZE OF CACHESIZE
 

Please post your comments and findings.

[AHF]

Lucas,

How can I set value for "Maximum Open Rows" with MySql?

Is it possible with this property that the server only returns blocks of rows as they are requested?

Can this increase performance?

Thanks

[AHF]

Mr Rao, Lucas,

We found a performance problem with adordd trials.

The issue is that ADO_RECCOUNT (reccount() or lastrec()) sends a sql SELECT MAX(FIELD USED AS RECNO)
This is delaying quite a bit all browses mainly on WAN because gets called many times.
Taking this out the browses are quite fast.

Is there a way to question the DB if table has been updated and only then adordd sends the sql SELECT?
Or may be a faster way to question the DB ?

Im trying it with MySql over LAN and WAN and the difference in browses performance its huge.