ADO RDD xHarbour

[Armando]

James:

Maybe it's an Access problem.

Why not use ADO only

Regards

[James Bott]

Armando,

Thanks. I am using both ADO and ADORDD. I am posting the problems I come across so hopefully we can fix them in the ADORDD. If the ADORDD is going to be a drop-in replacement like other RDDs, then everything needs to be working. Otherwise, we need a bunch of IF/THENs in our source code so we can use DBFs or SQL.

And yes, getting the DBF data in the SQL database is a separate issue from using existing apps with an SQL database. So we can use whatever means necessary to get the data into the SQL database.

James

[James Bott]

I am trying to figure out how to use indexes. Can someone post a simple working example preferably using the Northwind.mdb? This would be very helpful, since I have had no luck getting an index to work.

Below is what I have been trying to get working. I am trying to index on COUNTRY, but it is not working.

James

Code: Select all  Expand view  RUN

/*
Purpose: Simple ADORDD test of indexes
Author : James Bott
Date   : 6/30/2015 5:02:35 PM

*/

#include "fivewin.ch"
#include "adordd.ch"

REQUEST ADORDD, ADOVERSION

FUNCTION Main()

   RddRegister("ADORDD",1)
   RddSetDefault("ADORDD")
   
   SET ADO TABLES INDEX LIST TO { {"CUSTOMERS",{"CUSTOMERID","CUSTOMERID"} },;
      {"CUSTOMERS",{"COUNTRY","COUNTRY"} } }
   
   //SET ADO TEMPORARY NAMES INDEX LIST TO {"TMP","TEMP", "TEMPORARY" }

   //SET ADO DEFAULT RECNO FIELD TO "HBRECNO"

   SET AUTOPEN ON

   SET ADO FORCE LOCK OFF   // required

   SET ADO DEFAULT DATABASE TO "northwind.mdb" ;
       SERVER TO "Microsoft.Jet.OLEDB.4.0"       ;
       ENGINE TO "ACCESS"

   USE customers
   //INDEX ON FIELD->CUSTOMERID TO CUSTOMERID TAG CUSTID
   //index on field->country to country TAG COUNTRY
   //set index to country
   
   set order to "country"
   go top
   msgInfo(country)
   //BROWSE()
   xbrowser fastedit

Return nil

// EOF

[AHF]

James,

SET ADO TABLES INDEX LIST TO = expressions used to build queries with ORDER BY clause. Here you can even use sql expressions like ASC DESC CONVERT etc

SET ADODBF TABLES INDEX LIST TO = standard clipper like expressions to enable functions like Indexkey() to work. These are the actual indexes you re using in your app.

You need to have both defined and in the same order.

Indexes defined in these array do not need to be created!

Just use SET INDEX TO or ORDLISTADD to "open" them and then SET ORDER TO or ORDSETFOCUS to activate them.

Please remember that indexes files dont really exist they are only expressions to be used by adordd in SELECT.. ORDER BY clause.

Please check the readme.pdf file

Indexes created with INDEX ON are temporary in the sense that when the app is closed they need to be created again as they arent in the SET .. INDEX array.

Try take out the TAG I never tried because I dont use it. TAGs are not needed as files dont really exist.

Code: Select all  Expand view  RUN

USE customers
INDEX ON CUSTOMERID TO temp1
index on country to temp2
ordsetfocus("temp2")
browse()
ordsetfocus("temp1")
browse()
ordsetfocus("CUSTOMERID")
browse()
 

Did it work?

[Kleyber]

Kleyber,

That may be an option. I downloaded it and will take a look.

I did see this in another message thread, "It imports DBFs into MySQL or PostgreSQL databases" and since for testing I am using ACCESS right now it won't be helpful.

I do think the ADORDD issue can be solved. I expect maybe it is doing a COMMIT after adding each record. From my previous experience, this will REALLY slow things down.

Regards,
James

That's right. DBF2SQL has the option of how many records must be sent before a commit. The default is 250 but it can be changed. The best way to export data from dbf to an sql database is (IMHO) using transactions with a commit after them.

[James Bott]

AHF,

OK I tried what I thought you were saying and it still doesn't work. That's why I was asking for a working example, because obviously I still have something wrong. It seems we have to make lots of changes with indexes to use SQL.

Below is my modified code that is not working.

James

Code: Select all  Expand view  RUN

/*
Purpose: Simple ADORDD test of indexes
Author : James Bott
Date   : 6/30/2015 5:02:35 PM

*/

#include "fivewin.ch"
#include "adordd.ch"

REQUEST ADORDD, ADOVERSION

FUNCTION Main()

   RddRegister("ADORDD",1)
   RddSetDefault("ADORDD")
/*  
SET ADO TABLES INDEX LIST TO = expressions used to build queries with ORDER BY clause. Here you can even use sql expressions like ASC DESC CONVERT etc

SET ADODBF TABLES INDEX LIST TO = standard clipper like expressions to enable functions like Indexkey() to work. These are the actual indexes you re using in your app  
*/  
   SET ADO TABLES INDEX LIST TO { {"CUSTOMERS",{"CUSTOMERID","CUSTOMERID"} },;
      {"CUSTOMERS",{"COUNTRY","COUNTRY"} } }
   SET ADODBF TABLES INDEX LIST TO  { {"CUSTOMERS",{"CUSTOMERID","CUSTOMERID"} },;
      {"CUSTOMERS",{"COUNTRY","COUNTRY"} } }      
   
   //SET ADO TEMPORARY NAMES INDEX LIST TO {"TMP","TEMP", "TEMPORARY" }

   //SET ADO DEFAULT RECNO FIELD TO "HBRECNO"

   SET AUTOPEN ON

   SET ADO FORCE LOCK OFF   // required

   SET ADO DEFAULT DATABASE TO "northwind.mdb" ;
       SERVER TO "Microsoft.Jet.OLEDB.4.0"       ;
       ENGINE TO "ACCESS"

   //USE customers
   //INDEX ON FIELD->CUSTOMERID TO CUSTOMERID TAG CUSTID
   //index on field->country to country TAG COUNTRY
   //set index to country
   
USE customers
INDEX ON CUSTOMERID TO temp1
index on country to temp2
ordsetfocus("temp2")
browse()
ordsetfocus("temp1")
browse()
ordsetfocus("CUSTOMERID")
browse()  
/*  
   set order to "country"
   go top
   msgInfo(country)
   //BROWSE()
   xbrowser fastedit
*/
Return nil

// EOF

[AHF]

James,

Try this:

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "adordd.ch"

REQUEST ADORDD, ADOVERSION

FUNCTION Main()

   RddRegister("ADORDD",1)
   RddSetDefault("ADORDD")
/*
SET ADO TABLES INDEX LIST TO = expressions used to build queries with ORDER BY clause. Here you can even use sql expressions like ASC DESC CONVERT etc

SET ADODBF TABLES INDEX LIST TO = standard clipper like expressions to enable functions like Indexkey() to work. These are the actual indexes you re using in your app

array spec :

{ {"Table Name", {"tag name","index expression"},;
                 {"2nd tag name","index expression"} },;
  next table...       }
*/
   //change tag name to custid as your previous post
  // as in clipper you cannot have repeated index (tag) names.
  // you can try {"COUNTRY","COUNTRY DESC"} to check behaviour
   SET ADO TABLES INDEX LIST TO { {"CUSTOMERS", {"CUSTID","CUSTOMERID"},;
                                                                                       {"COUNTRY","COUNTRY"} } }

   SET ADODBF TABLES INDEX LIST TO { {"CUSTOMERS", {"CUSTID","CUSTOMERID"},;
                                                                                             {"COUNTRY","COUNTRY"} } }

   SET ADO TEMPORARY NAMES INDEX LIST TO {"TMP","TEMP", "TEMPORARY" }

   SET ADO DEFAULT RECNO FIELD TO "HBRECNO" //needed always to be indicated and exist on the tables!

   SET AUTOPEN ON //your choice!

   SET ADO FORCE LOCK OFF   // required ahf why?

   //isnt the path to mdb needed?
   SET ADO DEFAULT DATABASE TO "northwind.mdb" ;
       SERVER TO "ACCESS"       ;
       ENGINE TO "ACCESS"


USE customers
dbgotop()
SET ORDER TO 1
xbrowser fastedit

SET ORDER TO 2
dbgotop()
xbrowser fastedit

INDEX ON SOMEFIELD.... TO temp1
SET INDEX TO TEMP1

xbrowser fastedit

//try also this
sele 0
USE "INFORMATION_SCHEMA.TABLES"
xbrowser fastedit


Return nil

 

It seems we have to make lots of changes with indexes to use SQL.

No James the beauty of it is that is very easy to work with indexes maintaining exactly the same way of working as with any other rdd.
The only thing we need to do is to indicate these SETs to ADORDD we dont change anything else in the app code.

I didnt test this myself because I dot have ACCESS right now. Im only working with MySql.

Once more please note that the indexes are not being physically built in SQL engine these are only used to build ORDER BY clauses when opening the tables (recordsets) with SELECT....

We should not interfere in the database logic that is for DB ADMIN to do.
If later the DB ADMIN gets to the conclusion that many selects are using a certain ORDER BY clause may be it would be worth to build a true index at the server to serve faster those requests but this should never be done at the app level.

Please let me know if its ok now.

[James Bott]

AHF,

Thanks for the reply.

I didnt test this myself because I dot have ACCESS right now

Hmm, you don't need the ACCESS program as the ADO drivers are part of Windows. You can just use the Northwind.mdb (commonly available) or you can use the ADO functions to create an empty ACCESS database. I don't have the ACCESS program either.

I will try your example.

James

[James Bott]

AHF,

I tried your example and with a few changes got it working except for the temp index. Any ideas?

James

Code: Select all  Expand view  RUN

// This is not working
// data is natural order
INDEX ON FIELD->CITY TO temp1
SET INDEX TO TEMP1
dbgotop()
xbrowser fastedit

[AHF]

James,

Glad to know thats working.

Place this in your code and call it after open indexes to check area status.

Code: Select all  Expand view  RUN

FUNCTION areainfo()
LOCAL cerrorlog := "",n,j,ntarget,x

   for n = 1 to 255
      if ! Empty( Alias( n ) )
         cErrorLog += CRLF + Str( n, 3 ) + ": " + If( Select() == n,"=> ", "   " ) + ;
                      PadR( Alias( n ), 15 ) + Space( 20 ) + "NomeRDD: " + ;
                      ( Alias( n ) )->( RddName() ) + CRLF
         cErrorLog += "     ==============================" + CRLF
         cErrorLog += "     RecNo    RecCount    BOF   EOF" + CRLF
         cErrorLog += "    " + Transform( ( Alias( n ) )->( RecNo() ), "99999" ) + ;
                      "      " + Transform( ( Alias( n ) )->( RecCount() ), "99999" ) + ;
                      "      " + cValToChar( ( Alias( n ) )->( BoF() ) ) + ;
                      "   " + cValToChar( ( Alias( n ) )->( EoF() ) ) + CRLF + CRLF
         cErrorLog += "     Índices em uso " + Space( 23 ) + "TagName" + CRLF

         for j = 1 to 15
            if ! Empty( ( Alias( n ) )->( IndexKey( j ) ) )
               cErrorLog += Space( 8 ) + ;
                            If( ( Alias( n ) )->( IndexOrd() ) == j, "=> ", "   " ) + ;
                            PadR( ( Alias( n ) )->( IndexKey( j ) ), 35 ) + ;
                            ( Alias( n ) )->( OrdName( j ) ) + ;
                            CRLF
            endif
         next

         cErrorLog += CRLF + "     Relations " + CRLF
         for j = 1 to 8
            if ! Empty( ( nTarget := ( Alias( n ) )->( DbRSelect( j ) ) ) )
               cErrorLog += Space( 8 ) + Str( j ) + ": " + ;
                            "TO " + ( Alias( n ) )->( DbRelation( j ) ) + ;
                            " INTO " + Alias( nTarget ) + CRLF
               // uValue = ( Alias( n ) )->( DbRelation( j ) )
               // cErrorLog += cValToChar( &( uValue ) ) + CRLF
            endif
         next
      x :=  (alias(n))->(dbrlocklist())
      cErrorLog += Space( 8 ) +"Records locked "+CRLF
      for j = 1 to len(x)
          cErrorLog += Space( 8 ) + "Recno nr "+str(x[j])+CRLF
      next

      endif
   next
   SHOWMEMO(cerrorlog)

RETURN .t.
 

What do you have after set index ?

[AHF]

James,

Code: Select all  Expand view  RUN

// This is not working
// data is natural order
INDEX ON FIELD->CITY TO temp1
SET INDEX TO TEMP1
dbgotop()
Browse()
 

This is working here with ACCESS adordd version 1/250615

[James Bott]

AHF,

Below is the code I am using where the temp index isn't working.

James

Code: Select all  Expand view  RUN

/*
Purpose  : Test ADORDD indexing
Author   : James Bott
Date     : 8/4/2015
Company  : Intellitech
Language : Fivewin/xHarbour
Updated  :
Notes    :

*/

#include "fivewin.ch"
#include "adordd.ch"

REQUEST ADORDD, ADOVERSION, DBFCDX

FUNCTION Main()

   RddRegister("ADORDD",1)
   RddSetDefault("ADORDD")
/*
SET ADO TABLES INDEX LIST TO = expressions used to build queries with ORDER BY clause. Here you can even use sql expressions like ASC DESC CONVERT etc

SET ADODBF TABLES INDEX LIST TO = standard clipper like expressions to enable functions like Indexkey() to work. These are the actual indexes you re using in your app

array spec :

{ {"Table Name", {"tag name","index expression"},;
                 {"2nd tag name","index expression"} },;
  next table...       }
*/
 
msgInfo( ADOVERSION(), "ADO Version" )
 
   //change tag name to custid as your previous post
  // as in clipper you cannot have repeated index (tag) names.
  // you can try {"COUNTRY","COUNTRY DESC"} to check behaviour
   SET ADO TABLES INDEX LIST TO { {"CUSTOMERS", {"CUSTID","CUSTOMERID"},;
                                     {"COUNTRY","COUNTRY DESC"} } }

   SET ADODBF TABLES INDEX LIST TO { {"CUSTOMERS", {"CUSTID","CUSTOMERID"},;
                                      {"COUNTRY","COUNTRY DESC"} } }

   SET ADO TEMPORAY NAMES INDEX LIST TO {"TMP","TEMP", "TEMPORARY" }

   SET ADO DEFAULT RECNO FIELD TO "HBRECNO" //needed always to be indicated and exist on the tables!

   SET AUTOPEN ON //your choice!

   SET ADO FORCE LOCK OFF   // required ahf why?

   //isnt the path to mdb needed?
   SET ADO DEFAULT DATABASE TO "northwind.mdb" ;
       SERVER TO "Microsoft.Jet.OLEDB.4.0"     ;
       ENGINE TO "ACCESS"


USE customers
dbgotop()

SET ORDER TO 1
xbrowser fastedit

SET ORDER TO 2
dbgotop()
xbrowser fastedit

// This is not working
// data is natural order
INDEX ON FIELD->CITY TO temp1
SET INDEX TO TEMP1
dbgotop()
xbrowser fastedit

//try also this
//select 0
//USE "INFORMATION_SCHEMA.TABLES" //MySQL only?
//xbrowser fastedit

Return nil

[James Bott]

AHF,

Here is the data you wanted regarding the temp index.
I am using the same ADORDD version as you are.

James


1: => CUSTOMERS NomeRDD: ADORDD
==============================
RecNo RecCount BOF EOF
1 45 .F. .F.

Índices em uso TagName
CUSTOMERID CUSTID
=> COUNTRY DESC COUNTRY

Relations
Records locked

[James Bott]

AHF,

The thought just came to me that the customers table in the ACCESS Northwind.mdb does not have the HBRECNO field (because it is a default file). Could that be the reason that the temp indexes aren't working?

James

[AHF]

James,

Without some autoinc field defined as recno results are unpredictable.
It might be it.