Tom, your reply reminds me of a serious "flaw" (in my opinion) with the Progress design: 4GL code is 4GL code; There is no distinction between 4GL code in the context of screens and local processes compared to 4GL code that does nothing but access the database.
Over the past 16 years I have become so accustomed to sending a request to a database engine that performs database processing within itself and returns to me any results. Consider (as I'm sure you did with your last post) the pure inefficiency of moving a large table across network bandwidth simply to obtain a row count.
We all can't be working locally on the box with the database.
Joey, maybe I understand what you are saying and maybe I don't.
I think what you are saying is that an application can be written to provide this type of information from the database, and that application would be run under the appserver on the same box as the database.
If that's what you are saying then my response is simply that a pure database engine is that program, and I don't have to reinvent that wheel.
If that's not what you are saying then please help my old head to understand better.
Thanks.
So, one can dream about a function that will count entries in the index without reading the records or about the database maintaining such a count for you dynamically and one can make enhancement requests and hope that they are eventually implemented, but in the short run it isn't there, so you should focus on what you are going to do about it.
Does it need to be exact and up to the last second? Might a proutil run each night loaded into a table be good enough. Possibly a server side process?
Time to think about what is possible, not about what isn't there.
Tom,
The SQL-92 approach works fine. However, it is not especially fast. I am simply looking for a way to obtain the count programatically in < 30 seconds per table regardless of row count.
The ideal method would have been reading the record count information from the system tables, similar to other databases like MySQL, PostgreSQL.
I checked the _File and some other "hidden" tables in Progress, but it seems there is no such information, although I am convinced there should be some place where Progress writes the current sizes of the tables...
So, I believe the only method is counting, FOR EACH or the SQL COUNT.
SELECT SYSTABLES.TBL, SYSTBLSTAT.VALUE
FROM SYSTABLES, SYSTBLSTAT
WHERE SYSTBLSTAT.TBLID = SYSTABLES.ID
AND SYSTBLSTAT.PROPERTY = 4 ;
Output to "/data/export/micos/ContractRegel_xas4711i.csv".
For Each ContractRegel No-lock:
Export Delimiter "~t" ContractRegel.
End.
Output Close.
Hello, avdberg.
Simply add counter inside your for-each cicle:
DEFINE STREAM fLog.
DEFINE VARIABLE lCount AS INTEGER NO-UNDO.
lCount = 0.
For Each ContractRegel No-lock:
Export Delimiter "~t" ContractRegel.
lCount = lCount + 1.
End.
OUTPUT STREAM fLog TO VALUE("export.log") APPEND.
PUT STREAM fLog UNFORMATTED lCount "~tContractRegel" SKIP.
OUTPUT STREAM fLog CLOSE.
And, if You want, You could add date and time of finish dumping each table:
PUT STREAM fLog UNFORMATTED
TODAY " " STRING(TIME, "HH:MM:SS") " "
lCount "~tContractRegel" SKIP.
About delimiter - You using tab-delimiter (~t).
Quotes used to contain text data inside it.
Avdberg, from the example posted by Serj use two streams:-
DEFINE STREAM ftables.
DEFINE STREAM fLog.
DEFINE VARIABLE lCount AS INTEGER NO-UNDO.
OUTPUT STREAM ftables TO "/data/export/micos/ContractRegel_xas4711i.csv".
OUTPUT STREAM fLog TO VALUE("export.log") APPEND.
lCount = 0.
For Each ContractRegel No-lock:
Export STREAM ftables Delimiter "~t" ContractRegel.
lCount = lCount + 1.
End.
PUT STREAM fLog UNFORMATTED lCount "~tContractRegel" SKIP.
OUTPUT STREAM ftables CLOSE.
OUTPUT STREAM fLog CLOSE.
For Delimiter I normally use either of the following:-
EXPORT DELIMITER "," or EXPORT DELIMITER ";".
Hope this helps.
Yes this helps me very much, now i want to do this voor for example 10 tables. Now I i copy the above code 10 times and change the table name. Is it possible to use a array where i put in the table name and the ftables output file and that i can use one time the above code?