Page 58 of 70
Re: ADO RDD xHarbour
Posted: Tue Sep 08, 2015 3:01 pm
by 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
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.
Re: ADO RDD xHarbour
Posted: Tue Sep 08, 2015 3:19 pm
by 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
Re: ADO RDD xHarbour
Posted: Tue Sep 08, 2015 3:23 pm
by AHF
Pieter,
ADOVERSION()
Re: ADO RDD xHarbour
Posted: Wed Sep 09, 2015 6:47 am
by pieter
MsgInfo(ADOVERSION()) gives: AdoRdd Version 1.170815/1
Re: ADO RDD xHarbour
Posted: Wed Sep 09, 2015 7:39 am
by 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
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.
Re: ADO RDD xHarbour
Posted: Wed Sep 09, 2015 8:37 am
by pieter
Antonio F,
I have used your code of FUNCTION expalias() (I used memowrit() instead of SHOWMEMO())
Code: Select all | Expand
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
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.
Re: ADO RDD xHarbour
Posted: Wed Sep 09, 2015 8:48 am
by AHF
Pieter,
I would say that you are seeking without any index opened.
What does aBasis?
Re: ADO RDD xHarbour
Posted: Wed Sep 09, 2015 9:18 am
by pieter
Antonio F,
Code: Select all | Expand
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
Re: ADO RDD xHarbour
Posted: Wed Sep 09, 2015 9:39 am
by 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
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.
Re: ADO RDD xHarbour
Posted: Wed Sep 09, 2015 1:59 pm
by 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
Re: ADO RDD xHarbour
Posted: Thu Sep 10, 2015 6:49 pm
by 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
Re: ADO RDD xHarbour
Posted: Fri Sep 11, 2015 4:31 pm
by 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)
Re: ADO RDD xHarbour
Posted: Fri Sep 11, 2015 11:26 pm
by 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
Re: ADO RDD xHarbour
Posted: Sat Sep 12, 2015 11:05 am
by 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!
Re: ADO RDD xHarbour
Posted: Thu Sep 24, 2015 12:00 pm
by 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