ADO RDD xHarbour

[AHF]

Pieter,

What adordd version are you using?

I would not take out ADS from your prgs I would use instead around the code:

Code: Select all  Expand view  RUN

IF RDDSETDEFAUL() == "ADORDD"
  //adordd code chages
ELSE
  //your actual code
ENDIF
 

and link all rdds.
Then you only have to change RDDSETDEFAULT and your app will work with any of them.

By the way the next version of adordd has big changes and it seems that will be 100% compatible with any clipper code. Just link and go.

Changes:
Data from server only read once.
Better underlying data synchronization (deletions and new additions by others).
Big big improvements in speed ( seeks, relations, record movement, filters, etc) browses even with several relations are extremely fast.
Indexes are not anymore queries with order by but :Sort instead.
Finally Indexes with any clipper expressions including any UDFs. Thanks to Mr Rao!
Code in general cleaner and much reduced.

I hope to have it ready between 21-26.09 because we are testing every change.
All our trials are done with MySql / ACCESS and recordsets between 50.000 and 200.000 recs.

[pieter]

Antonio F,

Thank you:D. How can I see which version of ADORDD I have in the application? Something like MsgInfo(ADORDDVERSION()) maybe?

I am looking forward to the new adordd.

Pieter

[AHF]

Pieter,

ADOVERSION()

[pieter]

MsgInfo(ADOVERSION()) gives: AdoRdd Version 1.170815/1

[AHF]

Pieter,

There is a problem with open indexes but I dont now what.

Do you have the tables and indexes opened in your error log? Can you post it ?

If you dont you can use this code to check it.

Code: Select all  Expand view  RUN

FUNCTION expalias()
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 + "     Relações em uso " + 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 ) +"Registos fechados "+CRLF
      for j = 1 to len(x)
          cErrorLog += Space( 8 ) + "Registo nr "+str(x[j])+CRLF
      next

      endif
   next
   SHOWMEMO(cerrorlog)

RETURN .t.
 

[pieter]

Antonio F,
I have used your code of FUNCTION expalias() (I used memowrit() instead of SHOWMEMO())

Code: Select all  Expand view  RUN

  1:    USERS                              NomeRDD: ADORDD
     ==============================
     RecNo    RecCount    BOF   EOF
        1          3      .F.   .F.

     Índices em uso                        TagName

     Relações em uso
        Registos fechados

  2:    ID                                 NomeRDD: ADORDD
     ==============================
     RecNo    RecCount    BOF   EOF
        1          1      .F.   .F.

     Índices em uso                        TagName

     Relações em uso
        Registos fechados

  3: => BASIS                              NomeRDD: ADORDD
     ==============================
     RecNo    RecCount    BOF   EOF
        1          2      .F.   .F.

     Índices em uso                        TagName

     Relações em uso
        Registos fechados
 

Here is more code of my program.

Code: Select all  Expand view  RUN

IF ! GetInlogdata(1)
    RETURN nil
ENDIF
LoginOke( .F.,.T. )

aOpenDbf := { "basis","id","users" }
IF ! DBFopen( aOpenDbf,"S",1,1 )
    RETURN nil
ENDIF

expalias()

aBasis( nil,.T.) //Error happens in this function.

 

I can send you more code or information if needed.

[AHF]

Pieter,

I would say that you are seeking without any index opened.

What does aBasis?

[pieter]

Antonio F,

Code: Select all  Expand view  RUN

   Error description: Error BASE/1132  Bound error: array access
   Error id 1: -
   Error id 2: -
   Current opened dbf: BASIS - 1
   Last menu action: <onbekend>
   Args:
      [   1] = A    (L=0)
      [   2] = N            1

Stack Calls
===========
   Called from ADOPSEUDOSEEK(3749)
   Called from ADOSEEKCLIFIND(3412)
   Called from ADO_SEEK(3371)
   Called from DBSEEK(0)
   Called from DBFSEEK(1479)
   Called from DBFSELECT(1465)
   Called from ABASIS(2912)
   Called from UBTEST(45)

 

In function aBasis, the comment translated in english is: Base - load data in object
In this function aBasis, there is a function DBFselect() which is used a lot in the program. In this error the code looks like this: DBFselect( "basis",1, etc. ). The error happens in the DBFselect function. Comment which is written by DBFselect function is: "Select a DBF (and open it if not allready opened)". I think it is a kind of USE or DbUseArea, but then customized for our program.

Pieter

[AHF]

Pieter,


Called from DBFSEEK(1479) before adordd.

Its not USE.
You are seeking the table and no indexes are opened.

The error occurs in

Code: Select all  Expand view  RUN

STATIC FUNCTION ADOPSEUDOSEEK(nWA,cKey,aWAData,lSoftSeek,lBetween,cKeybottom)

 LOCAL nOrder := aWAData[WA_INDEXACTIVE]
 LOCAL cExpression := aWAData[WA_INDEXEXP][nOrder] //here the error the array is empty
 

Should error "table not indexed" but it seems not.

Place expalias() after use table and indexes and check it.

[pieter]

Antonio F,

Oké, I think I have done what you suggested (see also my email). It looks like it is working well until Dbseek().

Pieter

[James Bott]

RE: ADORDD and Memory Issues

For this discussion I am talking about standard FWH code that was written for DBF files, since this is the reason for having an ADORDD.

I assume that whenever a database is opened via ADORDD, that a recordset is created containing the entire database. Is that correct?

I know that we have to live with the designs of SQL client/server, but this can create a big issue with memory. If we open several databases with hundreds of thousands, or even millions of records, then we are going to quickly run out of RAM.

In my apps, I often may have several copies of the same database open in different objects. Memory is not an issue with this when using DBFs, since they often only load one record at a time, or maybe a screen full with a browse. But with SQL we are going to load the entire database just to get access to one record. So if I have two objects that need one record each from a database, I will have two recordsets containing the entire database in memory. Is that correct?

Of course it also means that we have to load the entire database across the network--multiple times.

I know that some memory can be paged out to disk, but this comes at the cost of speed and also garbage collection I am guessing. So the speed of the same app running with a DBF RDD would be much faster than with the ADORDD (regardless of the fact that it is client/server).

It seems like this is going to be a real issue with all but very small files.

Yes, I know this can be solved by rewriting our apps to take advantage of SQL's query capabilities, but that is another entire project. We would pretty much have to rewrite our entire apps.

Thoughts?

James

[AHF]

James,

adordd is in big changes and will only read from table once when it opens it.
You can also use with a query to limit the range of the set.
After loading the set it only re read the table again when new additions by others are made.

Next week Ill post a new version with big changes, much much faster, UDFs in indexes etc.

Im now converting another app to SQL and this app does not have historic data files as should be.
All the records for the last 20 years are in the daily working files!
I had to add a new routine to have a indication of the working period and then USE the table with a WHERE clause for datefield = working date.
This reduced the working load from > 1000.000 recs to less than 50.000!
This app can have many tables opened at same time because it works with MDIs windows each one with their own tables, relations etc saved as each window objects.
Did nt change any code logic and app is running very fast. Still on trials.

But note that also with dbfs leaving tables grow so much would be a cause of many indexes problems, data corruption etc.

You will see adordd power in LAN or even better in a WAN with many users.
Because sets are worked locally performance degradation does not happen and the speed you have with one user it is same with 40.
This is the maximum I have tried but I believe that this will also be true for 200 since it will only depends of the server capacity of serving all the requests that with ADO are very few so even the server overload will not occur.

Next version of adordd will be available 21-16.09.
I will change the name to openrdd (Ill explain why when post it)

[James Bott]

Antonio F.

I look forward to all the new features and modifications. However, you are confirming my concerns.

// with DBF RDD
use customer // loads one record

// with ADORDD
use customer // loads the entire database

So opening 20 databases with a DBF RDD loads only 20 records into memory. Opening 20 databases with ADORDD loads 20 entire databases into memory.

Yes modifying your code to limit the range of the records loaded is the best solution and that is a viable option if you have a program written for a single customer. However, I am working on a commercial program that is on hundreds of sites. Most are fine with DBFs but some sites are larger with more users and more data and they would benefit from SQL. However, this means it is not practical to just use the same program and link in different RDDs for different database types.

So we have two options, fork the program into two versions (twice the updates). Or, put in lots of IF/THENs--still lots more work.

I'm just confirming that only in some situations are you going to be able to just link in an SQL RDD without issues--some of which may be show-stoppers.

James

[AHF]

James,

So opening 20 databases with a DBF RDD loads only 20 records into memory. Opening 20 databases with ADORDD loads 20 entire databases into memory.

Today memory its not a problem. Its cheap! 20 tables its not so much even to ADO memory management.
But for sure for our kind of ISAM coding our main problem resides here!

So we have two options, fork the program into two versions (twice the updates). Or, put in lots of IF/THENs--still lots more work.

May be not so much work if you can use a kind of replacement #command or may be the app has a open function that take care of all USE.

I'm just confirming that only in some situations are you going to be able to just link in an SQL RDD without issues--some of which may be show-stoppers.

Stay tuned we might not be confined to Ms ADO!

[pieter]

Hello Antonio F,

I am making progress with my main program with ADORDD. But I got a problem with ' (apostrof)

Do you think there is still a way to use ' (apostrof) in a program?

Example testprogram
USE CUSTOMER
APPEND BLANK
REPLACE FIRST WITH "Test'Pieter"


Errorlog:

Application
===========
Path and name: C:\Pieter\LeerOmgevingXharbour\learn-database.exe (32 bits)
Size: 2,781,696 bytes
Compiler version: xHarbour 1.2.3 Intl. (SimpLex) (Build 20150213)
FiveWin Version: FWHX 15.01
Windows version: 6.2, Build 9200

Time from start: 0 hours 0 mins 6 secs
Error occurred at: 09/24/15, 13:46:41
Error description: Error ADODB.Connection/0 S_OK: EXECUTE
Args:
[ 1] = C UPDATE CUSTOMER SET FIRST = 'Test'Pieter' WHERE HBRECNO = 516

Stack Calls
===========
Called from: => TOLEAUTO:EXECUTE( 0 )
Called from: adordd.prg => ADO_PUTVALUE( 1532 )
Called from: learn-database.prg => LEARNADORDD2( 55 )
Called from: learn-database.prg => MAIN( 22 )

System
======
CPU type: Intel(R) Pentium(R) CPU N3540 @ 2.16GHz 2166 Mhz
Hardware memory: 3500 megs

Free System resources: 90 %
GDI resources: 90 %
User resources: 90 %

Windows total applications running: 4
1 ,
2 , C:\Windows\WinSxS\x86_microsoft.windows.common-controls_6595b64144ccf1df_6.0.9600.17810_none_a9edf0
3 DDE Server Window, C:\Windows\SYSTEM32\OLE32.DLL
4 Tussen taken schakelen, C:\Pieter\LeerOmgevingXharbour\learn-database.exe

Variables in use
================
Procedure Type Value
==========================
TOLEAUTO:EXECUTE
Param 1: C "UPDATE CUSTOMER SET FIRST = 'Test'Pieter' WHERE HBRECNO = 516"
Local 1: U
Local 2: C "ADODB.Connection"
ADO_PUTVALUE
Param 1: N 1
Param 2: N 2
Param 3: C "'Test'Pieter'"
Local 1: A Len: 38
Local 2: O Class: TOLEAUTO
Local 3: N 516
Local 4: A Len: 6
Local 5: U
Local 6: U
Local 7: A Len: 7
LEARNADORDD2
Local 1: C "Test'Pieter"
MAIN

Linked RDDs
===========
DBF
DBFFPT
DBFBLOB
DBFCDX
DBFNTX
ADORDD

DataBases in use
================

1: => CUSTOMER RddName: ADORDD
==============================
RecNo RecCount BOF EOF
516 516 .F. .F.

Indexes in use TagName

Relations in use

Classes in use:
===============
1 ERROR
2 HASHENTRY
3 HBCLASS
4 HBOBJECT
5 TFONT
6 TWINDOW
7 TDIALOG
8 TBRUSH
9 TCONTROL
10 TXBROWSE
11 TXBRWCOLUMN
12 TBTNBMP
13 TSCROLLBAR
14 TRECT
15 TOLEAUTO
16 VTWRAPPER
17 TREG32

Memory Analysis
===============
256 Static variables

Dynamic memory consume:
Actual Value: 0 bytes
Highest Value: 0 bytes