ADO RDD xHarbour

[byte-one]

Antonio, line 6233 lReval -> lRetval

[AHF]

Gunther,

Thanks Done!

[AHF]

New build adordd ver 1.0 at https://github.com/AHFERREIRA/adordd.git

New:

1) SET ADODBF INDEX LIST FIELDTYPE NUMBER TO {{ "Table",{ "numfiled", nlen },{"numfield", nLen } } }
adordd need a precise indication of the len of numeric fields used in index expressions.
This is only needed for numeric fields where the SQL type its without field len definition such as:
AUTOINC, MONEY, DOUBLE, INT, SMALLINT, etc.

Corrected Bugs:

1) ADOBEGINTRANS( nWA ) : without work are it starts a transaction in default adordd connection otherwise
it starts in the connection of the current work area.

2) ADOROLLBACKTRANS( nWA ) : without any work area it rolls back all transactions in all open connections
otherwise it rolls back transaction in same connection as that work area.

3) ado_create was adding an extra position in field len of numeric fields
now ADO_FIELDSTRUCT takes that position returning the correct len of a field type "N"
This could lead to side effects in INDEXKEY where the numeric field was part of the expression.
No tables structure corrections are necessary.

4) ADPSEUDOSEEK works now ok with fields type "T"

5) ADODUFINDEX if result of the index expression its numeric its considered as it was a UDF expression
because we cant extract field len to build a find, filter or query expression because the eval result
its the sum of all the numeric fields in the expression.

6) ORDKEYGOTO was not full implemented.

[AHF]

New build adordd ver 1.0 at https://github.com/AHFERREIRA/adordd.git

Corrected new Bugs due to changes previous build:

1) ADO_SQLSTRU with firebird numeric fields precision fieldlen was wrong

2) ADO_FIELDSTRUCT integers doesnt take any extra position.

3) ADO_PUTVALUE trim spaces to check data width error with numbers.

[AHF]

New AdoRdd Version 1.070317 at https://github.com/AHFERREIRA/adordd.git

Improvements and corrected bugs:

ADO_ORDCREATE after creating index wasnt opening it.

ADO_SEEK key with multiple fields with softseek on was going to eof instead of last key.

ADOFILE if connection not valid returns now .F. instead of HB_FAILURE.

ADOOPENCONNECT if engine its not supported or wrongly set alerts the user.

ADOBEGINTRANS if workarea not specified opens a transactions for each opened connection
ADOCOMMITTRANS and ADOROLLBACKTRANS the same as ADOBEGINTRANS

New function ADONESTEDTRANS( nWA ) returns nr of nested transactions

NEW ERROR CODE 10600 - connection not available cant find transactions

ADO_ALREADYOPEN Test if the open recordset has same Sql query statement.

ADORECCOUNT Table name was not being converted to get fieldrecno if any defined
for that table always returned that default one.

ADOPREOPENTHRESHOLD Faster counting records. Doesn't cache any tables with WHERE clause.

All ADO cursors changed in the code from adopendynamic to adopenstatic, although it always
started as adopenstatic.


New features:

New option PORT in SET ADO DEFAULT DATABASE ...

ADO_ORDINFO ordwildseek implemented

Multibag orders support with new SET:
SET ADODBF MULTIBAG INDEX LIST TO { { "INDEX NAME", {TAGORDER 1","TAGORDER2"} } }
This allows multibag index file support exactly the same as any other DBf rdd.
ATTENTION
Now when SET AUTOOPEN ON doesn't open all index files automatically in SET ADODBF INDEX LIST instead
only opens the INDEX NAME present in SET ADODBF MULTIBAG INDEX LIST TO that match the table name.

Faster opening recordsets:

SET RECORDSET OPEN WHERE CLAUSE TO { { "TABLENAME", "SQL WHERE EXPRESSION" } }

ADOWHERECLAUSE ADORDD function allowing us to change the recordset WHERE clause on the fly keeping all
workarea information as Filters, Scopes, Indexes, etc.

Ex

SET RECORDSET OPEN WHERE CLAUSE TO { { "table", "FIELDANEMYEAR = '2017'" } }

USE table
BROWSE() //only records that meet "where" clause
//we want to see some historic data
cOldQry := ADOWHERECLAUSE( SELECT(), "FIELDANEMYEAR = '2012'" )
BROWSE()
//Get back to current year
ADOWHERECLAUSE( SELECT(), cOldQry )
BROWSE()

[AHF]

New AdoRdd at https://github.com/AHFERREIRA/adordd.git

[AHF]

New AdoRdd at https://github.com/AHFERREIRA/adordd.git

[AHF]

New AdoRdd at https://github.com/AHFERREIRA/adordd.git

Performance enhancement opening tables.

SET RECORDSET OPEN WHERE CLAUSE TO {{"MYTABLE","HBRECNO < 51000" },;
{"MYTABLE2","HBRECNO BETWEEN 1 AND 50000" }}

This SET together ADOWHERECLAUSE( nWa, cNewSql ) enable us to open tables using the set of data we really need without altering any code logic.
This SET is used as default query to open for each table present here.

The ADOWHERECLAUSE( nWa, cNewSql ) can be placed in strategic places within our app code to change or to avoid to bring all the records to the set.
Tables till 100K dont need to worry.
Usually its slower to build the set than to read data from the database.
Recordset s with more than 100 columns will start to take long time to build.
I´ve seen recordset s with 800 columns! This takes long time even with a couple of thousands of records!

Tables with a couple of millions of records if we dont use any WHERE clause will exhaust memory very fast or we even dont have enough memory to run our app.

Code: Select all  Expand view

....
//Ex
USE "MYTABLE"  // 10.000.000 RECS with SET RECORDSET OPEN WHERE CLAUSE TO will load only 51.000 recs
....
// Here we will need other set of data
//we have to be sure that with this new query our app code can run all necessary routines
nRecno := RECNO()
cOldSql := ADOWHERECLAUSE( SELECT(), "INVOICEDATE > '2016/04/01'" )
//working with the new set
//all workarea data, indexes etc continue exactly the same
//finish working get back to previous set
ADOWHERECLAUSE( SELECT(), cOldSql  )
GO nRecno
....
 

Caching the tables (recordsets):

Code: Select all  Expand view

SET ADO PRE OPEN THRESHOLD TO 0 MASK {"MYTABLE","MYTABLE2"}
 

This enable to cache the tables to avoid read the data again every time we open these tables
with the same query.
This SET uses the queries defined with SET RECORDSET OPEN WHERE CLAUSE TO.

Setting TO 0 adordd will not cache any table based on nr of records.
In this case adordd will only load the tables in MASK.
In databases with tables of millions of records if we choose to cache tables for ex > 50k records we must have a defined query for those tables otherwise adordd will cache all the records in the table.
If there are 10.000k program most probably will crash!

We need also to pay some attention to MASK to avoid loading tables we dont need.
Suppose that we have loaded, when we import our data on our database, our tables with paths:

These will be the table names in the database.
CURRENTPATH_MYTABLE
HISTORICOATH_MYTABLE

With above SET adordd will cache both.
So in this case we can do:

SET ADO PRE OPEN THRESHOLD TO 0 MASK {"CURRENTPATH\MYTABLE","CURRENTPATH\MYTABLE2"}

To avoid cache for ex other historic tables with same name.

Please remember that even we dont use this SET every time a table is opened it will be cached.
Again if any of those tables have millions of records if we dont have any query to limit the nr of records returned adordd will load all those millions!

ATTENTION
By default adordd caches all tables with more than 6000 recs when opening it.
Please adjust it in adordd to another value of your choice and we should have a query to limit records
for the tables with more than 500k.

[AHF]

New AdoRdd at https://github.com/AHFERREIRA/adordd.git

Improved speed DBEval
Cache tables and recordsets enhancement

[AHF]

New AdoRdd and tryadordd at https://github.com/AHFERREIRA/adordd.git

[AHF]

My main work now with AdoRdd its database with tables between 5.000 and 10.000.000 records building recordsets between 5.000 and 100.000 records and 70 columns in average.
The work its on a 1Gb LAN.

In the bigger tables if the server has indexes for the WHERE clause its pretty fast < 3 Secs building the recordset.

The memory consumption gets to max 600Mb.
I can have > 50 recordset opened at same time.
With the cached recordsets memory consumption will not increase.

AdoRdd can cache the recordsets in the beginning of the application with:

SET ADO PRE OPEN THRESHOLD TO

In this case app waits a little more to start but then all table opening its lightning fast.
Normally to display a window with > 20 tables its less < 1sec.

If we choose not to use this SET AdoRdd will use as cache already opened tables.
Thus the second time it will be also very fast.

With tables of this dimension we absolutely need a default open WHERE clause otherwise application would crawl and crash. use SET RECORDSET OPEN WHERE CLAUSE TO.
Please pay attention to these WHERE clauses otherwise you might not be able to see new additions as soon as AdoRdd requery.

Punctuality we might need to reduce the recordset for ex for sum SUM operations.
This is where we use:

cOldSql := adowhereclause( Select(), "where clause" )
SUM ...
adowhereclause( Select(), cOldSql )

Hope this might help you.

[rodrigoppt]

Necesito ayuda, cuando estoy usando dos aplicaciones, cuando incluyo una, en la otra aplicación no aparece.

[hmpaquito]

¿ Quien no aparece ?

[rodrigoppt]

no aparece el elemento que agregué en el otro sistema,
Ej: dejo ambos sistemas en la pantalla de registro de grupo con xbrowse, al incluir en un sistema, en el otro si voy a incluir el mismo código porque no actualizo el registro, y en xbrowse tampoco aparece .
Ahora cuando hago un cambio, cambia normal en ambos

[rodrigoppt]

o estoy usando asi

Code: Select all  Expand view

REQUEST DBFCDX
REQUEST ORDKEYNO
REQUEST ORDKEYCOUNT
REQUEST ORDKEYGOTO
REQUEST ADORDD, ADOVERSION, RECSIZE

FUNCTION MAIN()

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

   SET DATE TO BRIT  
   SET CENTURY ON
   SET EPOCH TO (YEAR(DATE())-50)
   SET DELETED ON
   SET SOFTSEEK OFF

   IF RDDSETDEFAULT() == "ADORDD"

      SET ADODBF TABLES INDEX LIST TO {{"SLGRUP",{"SLGRUP1","STRZERO(GRU_COD,3)"},{"SLGRUP2","GRU_DES"}},;
                                       {"SLSGRU",{"SLSGRU1","STRZERO(SGR_COD,3)"},{"SLSGRU2","SGR_DES"}} }

      SET ADODBF MULTIBAG INDEX LIST TO {{"SLGRUP",{"SLGRUP1","SLGRUP2"}},;
                                         {"SLSGRU",{"SLSGRU1","SLSGRU2"}} }

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

      SET ADO INDEX UDFS TO {"IF","&","SUBSTR","==","DESCEND","MYFUNC","PAD"}

      SET ADODBF INDEX LIST FIELDTYPE NUMBER TO { {"SLGRUP",{"GRU_COD",3}} }

      //SET ADO TABLES DECIMAL FIELDS LIST TO {}

      //SET ADO TABLES LOGICAL FIELDS LIST TO {};

      SET ADO DEFAULT RECNO FIELD TO "ID"
      //SET ADO FIELDRECNO TABLES LIST TO {{"SLGRUP","ID"}}      
           
      SET ADO DEFAULT DELETED FIELD TO "HBDELETE"
      //SET ADO FIELDDELETED TABLES LIST TO {{"SLGRUP","HBDELETE"}}

      SET ADO FORCE LOCK ON
      SET ADO LOCK CONTROL SHAREPATH TO HB_DIRBASE()+"TEMP" RDD TO "DBFCDX"

      SET ADO DEFAULT DATABASE TO "dbfnew" SERVER TO "localhost" ENGINE TO "MYSQL" USER TO "new" PASSWORD TO "123"
     
      // with thsi set the path becomes part of the table name.
      SET ADO TABLENAME WITH PATH OFF

      // uploaded with one path working now with another
      SET ADO ROOT PATH TO HB_DIRBASE()+"DADOS" INSTEAD OF HB_DIRBASE()
       
      SET ADO CACHESIZE TO 100 ASYNC ON ASYNCNOWAIT ON

      SET ADO PRE OPEN THRESHOLD TO 5000 //MASK { "TABLE" }

      SET EXCLUSIVE OFF
      SET AUTORDER TO 1
      SET AUTOPEN ON

      hb_AdoUpload( HB_DIRBASE()+"DADOS\" , "DBFCDX" , "MYSQL", .f. )

   ENDIF

   USE SLGRUP ALIAS "SLGRUP"  VIA "ADORDD" NEW    
   USE SLSGRU ALIAS "SLSGRU" VIA "ADORDD" NEW

RETURN .T.