Num records in table

avdberg

avdberg

New Member
Osborne said:
There may be other/better ways to do this as the only idea I have is to use query, buffer and field handles. E.g. something like this:-

DEFINE VARIABLE fCount AS INTEGER.
DEFINE VARIABLE lCount AS INTEGER.
DEFINE VARIABLE tCount AS INTEGER.
DEFINE VARIABLE qh AS HANDLE.
DEFINE VARIABLE bh AS HANDLE.
DEFINE VARIABLE fh AS HANDLE.
DEFINE VARIABLE expTables AS CHARACTER EXTENT 10.

DEFINE STREAM ftables.
DEFINE STREAM fLog.

expTables[1] = "ContractRegel".

OUTPUT STREAM ftables TO "/data/export/micos/ContractRegel_xas4711i.csv".
OUTPUT STREAM fLog TO VALUE("aaexport.log") APPEND.

DO tCount = 1 TO 10:
IF expTables[tCount] <> "" THEN DO:
CREATE BUFFER bh FOR TABLE expTables[tCount].
CREATE QUERY qh.
qh:SET-BUFFERS(bh).
qh:QUERY-PREPARE("FOR EACH " + expTables[tCount]).
qh:QUERY-OPEN.
lCount = 0.
REPEAT:
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
REPEAT fCount = 1 TO bh:NUM-FIELDS:
fh = bh:BUFFER-FIELD(fCount).
IF fh:dATA-TYPE = "CHARACTER" THEN
PUT STREAM ftables UNFORMATTED QUOTER(fh:BUFFER-VALUE).
ELSE
PUT STREAM ftables UNFORMATTED fh:BUFFER-VALUE.
IF fCount = bh:NUM-FIELDS THEN
PUT STREAM ftables SKIP.
ELSE
PUT STREAM ftables ",".
END.
lCount = lCount + 1.
END.
PUT STREAM fLog UNFORMATTED lCount SPACE(1) bh:NAME SKIP.
qh:QUERY-CLOSE().
DELETE OBJECT fh NO-ERROR.
DELETE OBJECT bh NO-ERROR.
DELETE OBJECT qh NO-ERROR.
END.
END.
OUTPUT STREAM ftables CLOSE.
OUTPUT STREAM fLog CLOSE.
Click to expand...

Thank you for the information and help. Here a what i want to do.

I have the following tables in the progres DB

Contact
Contract
Project
Offerte

And want Contract exported to a seperate export file so

Contact to Contact_xoc1010i.csv
Contract to Contract_xom4010i.csv
Project to Project_xci4010.csv
Offerte to Offerte_cma1010i.csv

And for every table count the number of records and put this in a log file with information about number of records, time, date and table name.
 
O

Osborne

Active Member
avdberg said:
Thank you for the information and help. Here a what i want to do.

I have the following tables in the progres DB

Contact
Contract
Project
Offerte

And want Contract exported to a seperate export file so

Contact to Contact_xoc1010i.csv
Contract to Contract_xom4010i.csv
Project to Project_xci4010.csv
Offerte to Offerte_cma1010i.csv

And for every table count the number of records and put this in a log file with information about number of records, time, date and table name.
Click to expand...

You could always put these values in array variables as well. Extra code required would be:-

fileName[1] = "Contact_xoc1010i".

DO tCount = 1 TO 10: /* As before */
exportFile = "/data/export/micos/" + fileName[tCount] + ".csv".
OUTPUT STREAM ftables TO VALUE(exportFile).
/* Export the data */
OUTPUT STREAM ftables CLOSE.
END.
 
avdberg

avdberg

New Member
Osborne said:
You could always put these values in array variables as well. Extra code required would be:-

fileName[1] = "Contact_xoc1010i".

DO tCount = 1 TO 10: /* As before */
exportFile = "/data/export/micos/" + fileName[tCount] + ".csv".
OUTPUT STREAM ftables TO VALUE(exportFile).
/* Export the data */
OUTPUT STREAM ftables CLOSE.
END.
Click to expand...

why is the Do tCount = 1 to 10 for has this to do with the fact that i did earlier say that i want to do 10 tables?
 
avdberg

avdberg

New Member
Osborne said:
You could always put these values in array variables as well. Extra code required would be:-

fileName[1] = "Contact_xoc1010i".

DO tCount = 1 TO 10: /* As before */
exportFile = "/data/export/micos/" + fileName[tCount] + ".csv".
OUTPUT STREAM ftables TO VALUE(exportFile).
/* Export the data */
OUTPUT STREAM ftables CLOSE.
END.
Click to expand...


How does the complete code look like then, sorry for my many request.
 
avdberg

avdberg

New Member
Osborne said:
There may be other/better ways to do this as the only idea I have is to use query, buffer and field handles. E.g. something like this:-

DEFINE VARIABLE fCount AS INTEGER.
DEFINE VARIABLE lCount AS INTEGER.
DEFINE VARIABLE tCount AS INTEGER.
DEFINE VARIABLE qh AS HANDLE.
DEFINE VARIABLE bh AS HANDLE.
DEFINE VARIABLE fh AS HANDLE.
DEFINE VARIABLE expTables AS CHARACTER EXTENT 10.

DEFINE STREAM ftables.
DEFINE STREAM fLog.

expTables[1] = "ContractRegel".

OUTPUT STREAM ftables TO "/data/export/micos/ContractRegel_xas4711i.csv".
OUTPUT STREAM fLog TO VALUE("aaexport.log") APPEND.

DO tCount = 1 TO 10:
IF expTables[tCount] <> "" THEN DO:
CREATE BUFFER bh FOR TABLE expTables[tCount].
CREATE QUERY qh.
qh:SET-BUFFERS(bh).
qh:QUERY-PREPARE("FOR EACH " + expTables[tCount]).
qh:QUERY-OPEN.
lCount = 0.
REPEAT:
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
REPEAT fCount = 1 TO bh:NUM-FIELDS:
fh = bh:BUFFER-FIELD(fCount).
IF fh:dATA-TYPE = "CHARACTER" THEN
PUT STREAM ftables UNFORMATTED QUOTER(fh:BUFFER-VALUE).
ELSE
PUT STREAM ftables UNFORMATTED fh:BUFFER-VALUE.
IF fCount = bh:NUM-FIELDS THEN
PUT STREAM ftables SKIP.
ELSE
PUT STREAM ftables ",".
END.
lCount = lCount + 1.
END.
PUT STREAM fLog UNFORMATTED lCount SPACE(1) bh:NAME SKIP.
qh:QUERY-CLOSE().
DELETE OBJECT fh NO-ERROR.
DELETE OBJECT bh NO-ERROR.
DELETE OBJECT qh NO-ERROR.
END.
END.
OUTPUT STREAM ftables CLOSE.
OUTPUT STREAM fLog CLOSE.
Click to expand...

I get the following error:
Unknown attribute QUERY-OFF-END used in widget:attribute phrase. (3406)
** Could not understand line 48. (198)
Click to expand...

We are using Progress 8.3B on SCO OpenServer 5.0.7.
 
O

Osborne

Active Member
avdberg said:
How does the complete code look like then, sorry for my many request.
Click to expand...

The complete code would look as follows:-

DEFINE VARIABLE fCount AS INTEGER.
DEFINE VARIABLE lCount AS INTEGER.
DEFINE VARIABLE tCount AS INTEGER.
DEFINE VARIABLE qh AS HANDLE.
DEFINE VARIABLE bh AS HANDLE.
DEFINE VARIABLE fh AS HANDLE.
DEFINE VARIABLE exportFile AS CHARACTER.
DEFINE VARIABLE expTables AS CHARACTER EXTENT 4.
DEFINE VARIABLE fileNames AS CHARACTER EXTENT 4.
DEFINE STREAM ftables.
DEFINE STREAM fLog.

ASSIGN expTables[1] = "ContractRegel"
expTables[2] = "Table2"
expTables[3] = "Table3"
expTables[4] = "Table4"
fileNames[1] = "Contact_xoc1010i.csv"
fileNames[2] = "Contract_xom4010i.csv"
fileNames[3] = "Project_xci4010.csv"
fileNames[4] = "Offerte_cma1010i.csv".

OUTPUT STREAM fLog TO VALUE("aaexport.log").
DO tCount = 1 TO 4:
IF expTables[tCount] <> "" THEN DO:
exportFile = "/data/export/micos/" + fileNames[tCount].
OUTPUT STREAM ftables TO VALUE(exportFile).
CREATE BUFFER bh FOR TABLE expTables[tCount].
CREATE QUERY qh.
qh:SET-BUFFERS(bh).
qh:QUERY-PREPARE("FOR EACH " + expTables[tCount]).
qh:QUERY-OPEN.
lCount = 0.
REPEAT:
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
REPEAT fCount = 1 TO bh:NUM-FIELDS:
fh = bh:BUFFER-FIELD(fCount).
IF fh:dATA-TYPE = "CHARACTER" THEN
PUT STREAM ftables UNFORMATTED QUOTER(fh:BUFFER-VALUE).
ELSE
PUT STREAM ftables UNFORMATTED fh:BUFFER-VALUE.
IF fCount = bh:NUM-FIELDS THEN
PUT STREAM ftables SKIP.
ELSE
PUT STREAM ftables ",".
END.
lCount = lCount + 1.
END.
OUTPUT STREAM ftables CLOSE.
DISP STREAM fLog bh:NAME LABEL "Table Name" FORMAT "X(20)"
lCount LABEL "No. Of Recs"
TODAY LABEL "Date"
STRING(TIME, "HH:MM:SS") LABEL "Time" SKIP.
qh:QUERY-CLOSE().
DELETE OBJECT fh NO-ERROR.
DELETE OBJECT bh NO-ERROR.
DELETE OBJECT qh NO-ERROR.
END.
END.
OUTPUT STREAM fLog CLOSE.

However, DevTeam is correct that dynamic queries have been available only since Progress v9 so would be no use under 8.3. Unfortunately I don't know of any other way to do it.
 
D

DevTeam

Member
Try something like that (based on Osbourne's code) :


Program 1 :
Code: 
DEFINE STREAM ftables.
DEFINE STREAM fLog.

DEFINE VARIABLE lCount AS INTEGER NO-UNDO.
DEFINE VARIABLE myPath AS CHAR NO-UNDO.

OUTPUT STREAM fLog TO VALUE("export.log") APPEND.

{my_include.i table1}
{my_include.i table2}
{my_include.i table3}

OUTPUT STREAM fLog CLOSE.
Include (named "my_include.i"):
Code: 
myPath = "/tmp/" + "{1}" + ".csv".
OUTPUT STREAM ftables TO VALUE(myPath).

lCount = 0.

For Each {1} No-lock:
  Export STREAM ftables Delimiter "~t" {1}.
  lCount = lCount + 1.
End.

PUT STREAM fLog UNFORMATTED lCount "~t" "{1}" SKIP.

OUTPUT STREAM ftables CLOSE.
To be crash-tested !
 
avdberg

avdberg

New Member
DevTeam said:
Try something like that (based on Osbourne's code) :


Program 1 :
Code: 
DEFINE STREAM ftables.
DEFINE STREAM fLog.
 
DEFINE VARIABLE lCount AS INTEGER NO-UNDO.
DEFINE VARIABLE myPath AS CHAR NO-UNDO.
 
OUTPUT STREAM fLog TO VALUE("export.log") APPEND.
 
{my_include.i table1}
{my_include.i table2}
{my_include.i table3}
 
OUTPUT STREAM fLog CLOSE.
Include (named "my_include.i"):
Code: 
myPath = "/tmp/" + "{1}" + ".csv".
OUTPUT STREAM ftables TO VALUE(myPath).
 
lCount = 0.
 
For Each {1} No-lock:
  Export STREAM ftables Delimiter "~t" {1}.
  lCount = lCount + 1.
End.
 
PUT STREAM fLog UNFORMATTED lCount "~t" "{1}" SKIP.
 
OUTPUT STREAM ftables CLOSE.
To be crash-tested !
Click to expand...

Thank you for the help it's working now how we want it.

DEFINE STREAM ftables.
DEFINE STREAM fLog.
DEFINE VARIABLE lCount AS INTEGER NO-UNDO.
DEFINE VARIABLE myPath AS CHAR NO-UNDO.
OUTPUT STREAM fLog TO VALUE("/data/export/micos/dbtocsv.log") APPEND.
{dbtocsv.i Afdeling xma2031i}
{dbtocsv.i Afleveradres xoc1120i}
{dbtocsv.i Bankrekening xma2014i}
{dbtocsv.i ContactPers xma2013}
{dbtocsv.i Contract xas4710i}
{dbtocsv.i ContractRegel xas4711i}
{dbtocsv.i Crediteur xas1050i}
{dbtocsv.i Debiteur xas1040i}
{dbtocsv.i Inkoopartikel xpu1012i}
{dbtocsv.i Klant xoc1110i}
{dbtocsv.i LevArtikel xpu2010i}
{dbtocsv.i Leverancier xpu1110i}
{dbtocsv.i Lot xma1021i}
{dbtocsv.i Lotdefinitie xma1020}
{dbtocsv.i Meerkeuze xma1071i}
{dbtocsv.i MeerkeuzeTaal xma1014i}
{dbtocsv.i Offerte xmc4110i}
{dbtocsv.i Prodprijs xic2010}
{dbtocsv.i Produkt xma1010i}
{dbtocsv.i Project xoc1041i}
{dbtocsv.i Relatie xma2010i}
{dbtocsv.i ServiceMat xsc4111}
{dbtocsv.i ServiceMatTrans xsc4111z}
{dbtocsv.i ServiceOrder xsc4110i}
{dbtocsv.i Specfact xas4112}
{dbtocsv.i Tabelkenmerk}
{dbtocsv.i Verkoopartikel xoc1010i}
{dbtocsv.i Verkoopprijs xoc1021}
{dbtocsv.i Voorraadartikel xic1080}
{dbtocsv.i Voorraadpunt}
{dbtocsv.i Vragenlijst xma1075}
{dbtocsv.i XmDoc}
OUTPUT STREAM fLog CLOSE.
Click to expand...

myPath = "/data/export/micos/" + "{1}" + "_" + "{2}" + ".csv".
OUTPUT STREAM ftables TO VALUE(myPath).
lCount = 0.
For Each {1} No-lock:
Export STREAM ftables Delimiter "~t" {1}.
lCount = lCount + 1.
End.
PUT STREAM fLog UNFORMATTED "Tabelnaam:" "~t" "{1}" SKIP
"Aantal Record(a):" "~t" lCount SKIP
"Bestandsnaam:" "~t" "{1}" + "_" + "{2}" + ".csv" SKIP
"----------------------------------------------------" SKIP.
OUTPUT STREAM ftables CLOSE.
Click to expand...

Now only one other question i have one table with a field in it that has 32k in it and in the field are quote's so when i import these in SQL 2005 with SSIS it can't handle the quote's. how to use an other text qualifier?
 
P

palthe

Member
To get back on-topic:
with 4GL I believe the fastest way to get your table scan is:
- first you get the fields from the primary index of the table
- then you do a query-prepare("for each <tablename> fields (<primary unique index fields>)").

I tested this (and of course it's not as fast as a select count(*) on an sql environment but still... it's faster than select count(*) on a 4GL environment! And please don't look at the actual results, but at the relative results, 'cause i'm running this on a laptop...).

1st example:

select count(*) from <tablename>:
Number of records in current resultset: 28209, Time used for counting: 3,016 seconds

for each <tablename>:
Number of records in current resultset: 28209, Time used for counting: 3,437 seconds

for each <tablename> fields (<PU index-fields>):
Number of records in current resultset: 28209, Time used for counting: 0,953 seconds

That's 3 times faster than select count(*) !

Second example (more records):

select count(*) from <tablename>:
Number of records in current resultset: 1101044, Time used for counting: 171,928 seconds

for each <tablename>:
Number of records in current resultset: 1101044, Time used for counting: 178,913 seconds

for each <tablename> fields (<PU index-fields>):
Number of records in current resultset: 1101044, Time used for counting: 41,954 seconds

4x faster!

Again: don't look at the actual timing, but at the relative timing between the table scans.
 
You must log in or register to reply here.

Similar threads

B
Question Disconnect Crystal Report Connexion
Replies
4
Views
855
BobyIsProgress
B
H
Understanding Temp-Table Records
Replies
7
Views
1K
TomBascom
TomBascom
T
Smartbrowse loses link to smartviewer
Replies
4
Views
580
Tarby777
T
M
Resolved Using "Local Variables" in QAD Browse Maintenance
Replies
1
Views
884
sgs.prasad
S
R
  • Locked
[Stackoverflow] [Progress OpenEdge ABL] How xml-node-type = 'hidden' play a role in a loop
Replies
0
Views
125
Rajesh khanna
R
Top