Dynamic Queries and buffers

jdpjamesp

jdpjamesp

ProgressTalk.com Moderator
Staff member
When building a dynamic query, I quite often have the situation where the tables are referenced in a different order depending on the data the user has provided for the query. As a result, making sure the buffers in the dynamic query are referenced in the right order is quite a tricky business. I'd much rather have a situation where I could dynamically add the buffers in the right order too. In my last job we could do this by generating a list of the tables referenced and going through this list and using hQuery:ADD-BUFFER(BufferName). This worked fine. But here we have to prefix our table references with db.TableName due to table names duplicated across dbs connected. Progress doesn't like it when the name of the buffer you're trying to add is prefixed with the db name and errors saying it can't add the buffer.

My question is, has anyone worked out a way of adding buffers to a query dynamically that will work in this situation?
 
I always add the buffers with handles and not their names to the query ...

Would defining static buffers help?
DEFINE BUFFER myBuffer FOR myDB.myTable.
hQuery:ADD-BUFFER ( "myBuffer" ).
Click to expand...

Heavy Regards, RealHeavyDude.
 
Are you using BufferName as a character or a handle? If you create dynamic buffers you can use the database prefix in the name:

Code: 
DEF VAR hb1 AS HANDLE .
DEF VAR hb2 AS HANDLE .
DEF VAR hq AS HANDLE .


CREATE BUFFER hb1 FOR TABLE "sports1.customer".
CREATE BUFFER hb2 FOR TABLE "sports2.customer".


CREATE QUERY hq.


hq:ADD-BUFFER( hb1 ).
hq:ADD-BUFFER( hb2 ).


hq:QUERY-PREPARE( "for each sports1.customer, each sports2.customer by sports1.customer.cust-num by sports2.customer.cust-num descending" ).
hq:QUERY-OPEN().


hq:GET-FIRST( NO-LOCK ).


MESSAGE hb1::cust-num SKIP hb2::cust-num VIEW-AS ALERT-BOX.
 
Thanks for the answers.
I have a function that parses a query string and pulls out the table names into a comma delimited list, so "db.table1,db.table2,db.table3". What I was able to do in my last job was just do lvi = 1 to num entries ADD-BUFFER(entry(lvi,lvtables)). But that doesn't work with the db prefix.
I'm aware of being able to define buffers as you both suggest, but that would still mean that I'd have to have some logic to statically add those buffers in the right order or else the query will fall over. What I want to be able to do is to analysis a query string as above and automatically add the right buffers in the right order.
 
You still have to ensure that the right buffers are added in the right order. I'm essentially trying to write some code that I can throw any query at and it would add the correct buffers in the correct order. Adding by name worked fine without the db prefix. I can't see how your code would work for this?
 
You mentioned that when you did not need the database prefix you had it all worked out, so I am going to assume that you do not need the logic to put the tables in the correct order. Assuming you have your list of tables with database prefix you can add them like so:

Code: 
DEF VAR ctables AS CHAR.
DEF VAR hq AS HANDLE.
DEF VAR hb AS HANDLE EXTENT.
DEF VAR ii AS INT.

EXTENT( hb ) = NUM-ENTRIES( ctables ).

DO ii = 1 TO NUM-ENTRIES( ctables ):
   CREATE BUFFER hb[ii] FOR TABLE ENTRY( ii, ctables ).
   hq:ADD-BUFFER( hb[ii] ).
END.
 
You must log in or register to reply here.
Back
Top