Dynamic query with external tables

[-Zigo-]

Hi everyone

I have read a number of threads on here which have been very useful but I'm still having trouble and wondering if anyone can help. (I'm using Progress 9.1E and ADM1 btw).

I have 2 tables, JobHdr and JobDet (job header and job details respectively), of which there may be multiple JobDet records per JobHdr (which are linked by the field JobId). The maintenance of these records is in a window which uses a SmartFolder and 2 pages (1 for each of JobHdr and JobDet). Each window has a SmartBrowser and a SmartViewer.

The JobDet SmartBrowser has the query "FOR EACH JobDet WHERE JobDet.JobId = JobHdr.JobId NO-LOCK" and has JobHdr as an external table so it knows which JobId to use.

I'm trying to write a generic include file for all my browsers (smart or non-smart) that will allow column sorting (ascending and descending), which I'm putting in the START-SEARCH code for the browser. The idea is to change the query so that for example it reads like "FOR EACH JobDet WHERE JobDet.JobId = JobHdr.JobId NO-LOCK BY Quantity" (if I've clicked the JobDet.Quantity column).

Everything works fine...except when the browser uses external tables. If I try to use the QUERY-PREPARE method then it complains with the error "JobHdr JobId must be a quoted constant or an unabbreviated, unambiguous buffer/field reference for buffers known to query br_table"

The problem is definitely to do with the external table reference, because if I hard-code the query to be "FOR EACH JobDet WHERE JobDet.JobId = 1 NO-LOCK", for example, then all works fine.

I tried to create a dynamic buffer and dynamic query to obtain the value of the current JobHdr.JobId record (using BUFFER-VALUE) so that I could substitute the actual value into the QUERY-PREPARE method, but it is proving more difficult than I thought.

Does anyone know how to solve the final part of this puzzle or know of an easier way to do it? If you require any more information, eg. the full code listing from the include file, please ask. Thanks

 

[jongpau]

first of all you have to add the buffer of the header table to the query (if you have not done so):
hQuery:ADD-BUFFER(<handle to header>).

Next you will have to make sure the header record actually is present in the buffer, so you may have to do some extra work to accomplish that.

You can either use the buffer that was used in the header browse, or you can create a new buffer and then (for instance) use the rowid from the header browse in the query of the detail browse. How you can best work that out depends on your code to be honest (I am personally not a fan of using any ADM - 1 or 2)

 

[-Zigo-]

The problem I have is in substituting the part of the query which reads "JobHdr.JobId" with its actual value. I now don't think I need to create a dynamic query to do this - I should be able to use the FIND-BY-ROWID() method of the buffer, then substitute bhField:BUFFER-VALUE into the query instead of JobHdr.JobId

This is what I'm trying to achieve now:
(assume cFieldname = "JobId" in this instance - it's too long-winded to show how I parse {&QUERY-STRING-{&BROWSE-NAME}} to obtain this)

DEFINE VARIABLE iLoop AS INTEGER NO-UNDO.
DEFINE VARIABLE bhTable AS HANDLE NO-UNDO.
DEFINE VARIABLE bhField AS HANDLE NO-UNDO.
DEFINE VARIABLE rId AS ROWID NO-UNDO.
DEFINE VARIABLE cFieldName AS CHARACTER NO-UNDO.

DO iLoop = 1 TO NUM-ENTRIES("{&EXTERNAL-TABLES}"):
CREATE BUFFER bhTable FOR TABLE ENTRY(iLoop, "{&EXTERNAL-TABLES}").
bhField = bhTable:BUFFER-FIELD(cFieldname).
rId = ROWID(ENTRY(iLoop, "{&EXTERNAL-TABLES}")).
bhTable:FIND-BY-ROWID(rId).

/* use bhField:BUFFER-VALUE */
END.

All of the above works fine except when trying to set rId - I have to explicitly name the table on which I want to perform the ROWID() function, eg. in this instance it would be rId = ROWID(JobHdr).

I can't do something like ENTRY(iLoop, "{&EXTERNAL-TABLES}"):ROWID either because at I only know the name of the table as a string. If I create a new buffer then I can only find records using that buffer, and I can't see a way of finding out what's in the 'original' record buffer.

I've tried using an include file but if you pass a reference like ENTRY(...) then it uses that as the literal text, even when I've not put it in quotes.

Does anyone have any ideas?

 

[-Zigo-]

How to dynamically find value of field?

I put this issue to the side for a while and got on with other stuff, but now I have come back to this. As I put originally, I'm trying to dynamically setup a query using the QUERY-PREPARE method, but it doesn't work with external tables. Given that Progress can evaluate QUERY-PREPARE at run time means that I can piece it together dynamically.

(Although I have hardcoded JobHdr and JobDet here, I'm just using this as an example. In any statement that I can build an expression string, I can substitute any table/field name)

The original query statement on the JobDet browse reads:
"FOR EACH JobDet WHERE JobDet.JobId = JobHdr.JobId".

I want to change this to:
"FOR EACH JobDet WHERE JobDet.JobId = <insert actual value of JobHdr.JobId here>"

If I create a buffer to JobHdr, the current values of JobHdr aren't copied with it, so I have to re-find on the buffer. The following test code works (assume that a valid JobHdr record is already in the default buffer):

DEFINE VARIABLE bhTable AS HANDLE NO-UNDO.
DEFINE VARIABLE bhField AS HANDLE NO-UNDO.
 
bhTable = BUFFER JobHdr:HANDLE.
bhField = bhTable:BUFFER-FIELD("JobId").
bhTable:FIND-FIRST("WHERE bhTable.JobId = JobHdr.JobId") NO-ERROR.
 
/* now I can reference bhField:BUFFER-VALUE */

However, I can't use the statement "bhTable = BUFFER JobHdr:HANDLE" because JobHdr is hardcoded (ie. I can't build it using an expression).

The natural choice is to replace that line of code with "CREATE BUFFER bhTable FOR TABLE "JobHdr"." which looks to me as if it should do exactly the same thing. But if I try it, I get the error "** No record is available. (91)"

If I take the NO-ERROR condition out of the FIND-FIRST method, it actually gives me errors even on the working code. First off it says:
"bhTable JobId must be a quoted constant or an unabbreviated, unambiguous buffer/field reference for buffers known to query or FIND. (7328)"
...then:
"Invalid WHERE clause in FIND method for buffer JobHdr (10041)"

...but yet it gives me the correct result anyway.

Can you see what I'm trying to do? I just want to dynamically find the value of a field, and substitute it into the browse's query using the QUERY-PREPARE method. I've tried to simplify this by using JobHdr and JobDet as an example but I hope I've not confused anyone reading this.

If I'm missing something really obvious then please let me know - I'd never heard of Progress before this year...

 

[jongpau]

Yes I see what you are trying to do and I know what you mean.

But, doing a CREATE BUFFER just does that, it creates a buffer, but does not have a record in it, so you will actually have to find it in that buffer (so using buffers you can have multiple records from the same table available to a program at the same time). Apart from that, when you have two programs that reference table xyz, both programs have their own buffer to that table with their own copy of records in it.

What you can do (and this is just one option) is create some logic that allows you to get the buffer handle from window (=program) A in window (=program) B. You could accomplish this for instance by running an internal procedure (in the program that has the JobHdr record) that returns the default buffer handle as an output parameter. If you then run that internal procedure from within the program that you create your dynamic query in, you will have not just the handle to the default buffer, but that buffer will also contain the record.

You can then use that buffer handle (and the buffer-values of the fields) to actually replace JobHdr.JobId with the value of that field in the query you are trying to run. So instead of having "for each JobDet where JobDet.JobId = JobHdr.JobId" you would get "for each JobDet where JobDet.JobId = 10565" (so that imaginary number I typed would be the value of the JobHdr.JobId field)

--- Darn these things are hard to explain (and I am sure there are more ways than this one to get to what you want to do, especially since you say you use ADM1; which probably means there are some standard ways of getting the rowid of a record in another container etc)

 

[-Zigo-]

Thanks for your reply Paul. I am not using different procedures or windows - I am trying to put this in a generic include file, which is referenced from the START-SEARCH trigger of a browse.

You mention the default buffer handle - how do you obtain this? If I could get that, then I can use BUFFER-VALUE to extract the value I want. However I have not been able to find a way to get the handle to the 'default' buffer like this, given that the program only knows the name of the table in the form of a string.

 

[jongpau]

hDefaultBuffer = BUFFER JobHdr:HANDLE.

Problem is that you will have to "hard-code" the table name when you do it like this. However, if you are using an include file you could change it to:

hDefaultBuffer = BUFFER {1}:HANDLE.

and your include can then be:

{myinclude.i JobHdr}

 

[-Zigo-]

I see, so it's just how I had it in my example code above.

However, now that you mention using {1} I think I can do it. It's possible that a browse could have many external tables, so I have to loop through these within the include file to replace any reference to external table/field names with their current values. If I reference the include like {myinclude.i {&EXTERNAL-TABLES}} then that should work.

I've not tried this yet but I imagine I'll have to set up a CASE statement within the include to determine which external table to work with. I'll have to find entries from "{&EXTERNAL-TABLES}" within "{&QUERY-STRING-{&BROWSE-NAME}}" and substitute the name with the value. It doesn't appear possible to do something like:

(semi-pseudo code)

DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE h AS HANDLE NO-UNDO.
REPEAT i = 1 TO NUM-ENTRIES("{&EXTERNAL-TABLES}"):
  find reference to ENTRY(i, "{&EXTERNAL-TABLES}") in "{&QUERY-STRING-{&BROWSE-NAME}}" 
  h = BUFFER {i}:HANDLE.
END.

Still, like I said, I can use a CASE statement - I mean, in all the browses in our software none of them have more than 1 external table anyway, so I reckon 3 cases should suffice. Thank you

 

[-Zigo-]

It works

 

[jongpau]

Good stuff :biggrin:

 

[shanthimarie79]

Hi,

I couldn't find any logic for what i wanted and this post seemed to be the closest till I hit my brick wall.

I am using OE10.2A on a windows platform.

We're upgrading schema and plan to implement new unique index.
I need to write a code to input a CSV text file of tablesname (1st column), the list fields we plan to applpy unique on (column 2 -space delimited) and also a primary key (I think this is necessary). The output will be the list of records for the table that breaks the new unique index.

My current logic is to :
1. import the text file into a temp-table
2. for each tablename in the temp-table, I :
a. loop through the table (accessed via a dynamic query).
b. Next loop thorugh a buffer of the main table(also via dynamic query) and find matching fields where the primarykey value (usually a decimal type field) does not equal.

My current road bloack is trying to define a buffer on the same table for step 2b.

I'm no sure if this makes sense, do let me know if more info is required.
I hope someone has some thoughts on how to go about this.
Thanks!!

 

[Stefan]

You can use the BUFFER-NAME option of the CREATE BUFFER statement as follows:

DEFINE TEMP-TABLE tt NO-UNDO
   FIELD cc AS CHAR.

CREATE tt. tt.cc = "A".
CREATE tt. tt.cc = "B".
CREATE tt. tt.cc = "B".
CREATE tt. tt.cc = "C".

DEF VAR hq AS HANDLE NO-UNDO.
DEF VAR hb AS HANDLE NO-UNDO EXTENT 2.

CREATE BUFFER hb[1] FOR TABLE "tt":U BUFFER-NAME "tt1":U.
CREATE BUFFER hb[2] FOR TABLE "tt":U BUFFER-NAME "tt2":U.

CREATE QUERY hq.
hq:SET-BUFFERS( hb[1], hb[2] ).
hq:QUERY-PREPARE( "FOR EACH tt1, FIRST tt2 WHERE tt2.cc = tt1.cc AND ROWID( tt1 ) <> ROWID( tt2 )":U ).
hq:QUERY-OPEN().
DO WHILE hq:GET-NEXT():

   MESSAGE hb[1]::cc VIEW-AS ALERT-BOX.

END.
hq:QUERY-CLOSE().

DELETE OBJECT hq.
DELETE OBJECT hb[1].
DELETE OBJECT hb[2].

 

[shanthimarie79]

Hi Stefan,
Thanks so much...you've helped me 'above and beyond'
Didn't expect the code written out like that - it's much appreciated.

Warm regards,
shanthi