Need Advise on Dynamic For Each Statement.

C

cluc

New Member
Hi Everyone,

You are probably thinking, what the hell is this Noob talking about Dynamic For Each Statement!

Ok, let me explain what I'm trying to achieve.
My current working database has over 100s of tables, for my self and other developers, it' ok, because we have the knowledge of the 'For Each' and 'Find' statements. But for the Business Analysts, when they perform testing, they wouldn't know how to write up a simple for each statement, even if they new the table name, if they wanted to look up on some records.

So I thought, ok, if the BAs knew what tables and fields to look for, I can just write up a simple program, to take the table name and the field queries.
It wasn't so simple, as a matter of fact, I got stuck.

I've got a simple .i file which can be seen as the body, if you wish.

/* Heres my file called dynquery.i */

&IF "{1}" <> "" &THEN

FOR EACH {1} WHERE {1}.{2} = "{3}" NO-LOCK:

DISPLAY {1}.{4}.

END.

&ENDIF
/* End of file called dynquery.i */


And here's the simple .p file:

/* Heres my file called TESTING.i */

DEF VAR tempaaa AS CHAR NO-UNDO.
DEF VAR tname AS CHAR NO-UNDO.
DEF VAR tfield AS CHAR NO-UNDO.
DEF VAR qfield AS CHAR NO-UNDO.
DEF VAR dfield AS CHAR NO-UNDO.

tname = "TABLE-A".
tfield = "FIELD-A".
qfield = "QFIELD-A".
dfield = "FIELD-B".

{DYNQUERY.i tname tfield qfield dfield}.
/* End of file called TESTING.i */

The error I am getting is Unknown or Ambiguous tname table, although I have assigned tname to TABLE-A, but Progress seems to be using "tname" instead of what the variable is.
In the .i file, if I comment out the 'for each' statement and just to display '{1}', I will actually get the name TABLE-A.

Is there a work around?
Or is there some Progress already defined code?

Thanks in advance.
Calv.
 
Hi there,

If your BA's have licenses that allow session compiles (which judging by your previous example I'm assuming they have) , e.g. 4GL, PROVision, etc. then try the following (courtesy Progress Web Site) :-

If they don't have the correct licenses then they will get an error whinging about not able to compile blah blah ...

/* proc1.p */
DEF VAR a AS CHAR FORMAT "X(20)".
DEF VAR b AS CHAR FORMAT "X(20)".
DEF VAR c as CHAR FORMAT "X(20)".
SET a HELP "Enter file name to query" LABEL "File"
b HELP "Enter selection criteria" LABEL "Where"
c HELP "Enter index sort to use" LABEL "Index".
RUN proc1a.p value(a) value(b) value(c) "'AD HOC QUERY'" "2 COLUMNS".

/* proc1a.p */
FOR EACH {1} WHERE {2} BY {3}:
DISPLAY {1} WITH TITLE {4} {5}.
END.

However, the even better way is to use Dynamic queries and by combining this with the system tables of Progress you can provide a simple utiity for dynamically querying the database.

Dynamic queries are even better in that the user doesn't need a development license so in theory you could write a utility to give to end users which would let them browse the entire database and construct their own queries - not a good idea as it requires an in-depth understanding of the database schema.

Let me know if this doesn't resolve your problem and I'll knock a dynamic query example up and post it on here later, but, I'd need to know are you using Character or GUI and what version please as earlier versions of Progress don't support dynamic queries.

Have fun.
 
Hi Dave,

I've just tried it out, seems to be what I was looking for. Thanks!
If I just apply it to a GUI, it should still be pretty similar right?

I never knew you can pass in values when saying RUN PROC.P value value value.
I thought the only way to pass in something was RUN PROC.P (INPUT...).
And yes, I'm still very new to Progress.
That just opened up a lot of doors for me. :-)

Now, the dynamic query sounds pretty good...
I guess with the sample you given me, would always be hardcoded, for the amount of fields to validate against in the WHERE clause.
And I'm thinking that the dynamic quries could remove hardcoding part?

Would it be ok if you write up a simple example?

Thanks again and it was what I was looking for.

Cheers,
Calv
 
Ok, just found a table called _file, it is an actual Progress hidden table withing the Progress Database.
It has a list of all the table names, fields, formats of each fields, etc.

So, I guess, the dynamic coding can start from that side?
 
Hi there,

Yes, _File is the starting point. There is also _db (each connected database) and _ field, so, you can see how we can have something for selecting databases, files and fields (database is a slightly tricky one though).

Do you wish the BA's to have access to multiple DB's in there one session ?

Like I said though, the BA's must have an in-depth knowledge of the database structure.

I'm a little snowed under today, but, I'll first post on the code for simply browsing the system tables and at some other point I'll add the dynamic stuff.

Watch this space ...

Thanks.
 
Hi,

Probably just want the BA's to have access to one DB.
I've managed to find some existing code from older programs which loads up all the table names into the one combo box.

I was applying the example you provided and with the _file, it could lead me to the dynamic side of things.

thanks
 
I think what your looking for is something like this (keep in mind that this only works in versions 9.1 and above I think...)

DEFINE TEMP-TABLE aTable NO-UNDO
FIELD Field1 AS INTEGER
FIELD Field2 AS CHARACTER.

DEFINE VARIABLE BLQHand AS HANDLE NO-UNDO.
DEFINE VARIABLE BLHand AS HANDLE NO-UNDO.
DEFINE VARIABLE Finished AS LOGICAL NO-UNDO.
DEFINE VARIABLE aInt AS INTEGER NO-UNDO.
DEFINE VARIABLE aChar AS CHARACTER NO-UNDO.
DEFINE VARIABLE TempStr AS CHARACTER INITIAL "" NO-UNDO.
CREATE QUERY BLQHand.

BLHand = BUFFER aTable:HANDLE.
BLQHand:SET-BUFFERS(BLHand).

TempStr = "FOR EACH aTable WHERE Field1 = ".
TempStr = TempStr + STRING(RANDOM(1,4)).
IF LOGICAL(RANDOM(0,3)) THEN TempStr = TempStr + " AND Field2 = " + QUOTER("omg").

BLQHand:QUERY-PREPARE(TempStr).
BLQHand:QUERY-OPEN().
Finished = NOT BLQHand:GET-FIRST(NO-LOCK).

DO WHILE NOT Finished:
aInt = BLHand:BUFFER-FIELD("Field1"):BUFFER-VALUE.
aChar = BLHand:BUFFER-FIELD("Field2"):BUFFER-VALUE.
Finished = NOT BLQHand:GET-NEXT(NO-LOCK).
END.

BLQHand:QUERY-CLOSE().
DELETE OBJECT BLQHand.
DELETE OBJECT BLHand.
 
You must log in or register to reply here.
Back
Top