Dynamic query

The following variant of your code, which runs against the sports2000 database, works just fine.  I don't know what is wrong with your code.  Perhaps if you posted the full code (yours is missing, at a minimum, several variable definitions) and told us the version of OpenEdge you are using we could be of more help.

DEFINE VARIABLE ip_Buffers     AS CHARACTER NO-UNDO INITIAL "customer,order".
DEFINE VARIABLE lv_Buffers     AS HANDLE    NO-UNDO.
DEFINE VARIABLE retrn          AS LOGICAL   NO-UNDO.
DEFINE VARIABLE qry_Address    AS HANDLE    NO-UNDO.
DEFINE VARIABLE lv_x           AS INTEGER   NO-UNDO.
DEFINE VARIABLE ip_querystring AS CHARACTER NO-UNDO INITIAL "for each customer, each order of customer".

CREATE QUERY qry_Address.
DO lv_x = 1 TO NUM-ENTRIES(ip_Buffers):
    CREATE BUFFER lv_buffers FOR TABLE ENTRY(lv_x, ip_Buffers).
    qry_Address:ADD-BUFFER(lv_Buffers:HANDLE).
END.

qry_Address:SET-BUFFERS(BUFFER customer:HANDle , BUFFER order:HANDLE). 
qry_Address:QUERY-PREPARE (ip_QueryString ).
retrn = qry_Address:QUERY-OPEN ().
IF NOT retrn THEN
    DO:
        MESSAGE "No records founds" VIEW-AS ALERT-BOX.
        RETURN.
    END.

Hi Brian,

Thanks for the quick response. Just one thing;

Why do you use the ADD-BUFFER & SET-BUFFERs at the same time.

I just want to use only - the ADD-BUFFER. Because my number of table is dynamic. It depends of the user input.

Thanks,

No the ADD-BUFFER does not work. The SET-BUFFERS works fine, but it's of no use for me at this moment.

Then I was confused.  There are two commented lines in your code.

The following still compiles and runs just fine.  Perhaps if you provided us with more details (i.e. errors that are returned, etc)?

DEFINE VARIABLE ip_Buffers     AS CHARACTER NO-UNDO INITIAL "customer,order".
DEFINE VARIABLE lv_Buffers     AS HANDLE    NO-UNDO.
DEFINE VARIABLE retrn          AS LOGICAL   NO-UNDO.
DEFINE VARIABLE qry_Address    AS HANDLE    NO-UNDO.
DEFINE VARIABLE lv_x           AS INTEGER   NO-UNDO.
DEFINE VARIABLE ip_querystring AS CHARACTER NO-UNDO INITIAL "for each customer, each order of customer".
 
CREATE QUERY qry_Address.
DO lv_x = 1 TO NUM-ENTRIES(ip_Buffers):
    CREATE BUFFER lv_buffers FOR TABLE ENTRY(lv_x, ip_Buffers).
    qry_Address:ADD-BUFFER(lv_Buffers:HANDLE).
END.
 
// qry_Address:SET-BUFFERS(BUFFER customer:HANDle , BUFFER order:HANDLE).
qry_Address:QUERY-PREPARE (ip_QueryString ).
retrn = qry_Address:QUERY-OPEN ().
IF NOT retrn THEN
    DO:
        MESSAGE "No records founds" VIEW-AS ALERT-BOX.
        RETURN.
    END.

One thought, does your ip_Buffers variable contain the values as (for example)...

"customer,order"

or

"customer, order"

??

The second variant will cause this -> Could not create buffer object for table  order. (7334) <-- note the two spaces before order.

Brian,

I have tried the below code and it's having problems with the first table "addrtype".

The error I'm getting from the syntax check is "missing FOR, FIND or CREATE for table with addrtype in current block"

I'm trying to figure out why. Why with the ADD-BUFFER. And not with the SET-BUFFERS.

CREATE QUERY qry_Address.  

/*DO lv_x = 1 TO NUM-ENTRIES (ip_Buffers):                      */

/*    // MESSAGE ENTRY(lv_x, ip_Buffers) VIEW-AS ALERT-BOX.     */

/*   CREATE BUFFER lv_buffers FOR TABLE ENTRY(lv_x, ip_Buffers).*/

/*   qry_Address:ADD-BUFFER(lv_Buffers:HANDLE).                 */

/*END.                                                          */

CREATE BUFFER lv_buffers FOR TABLE addrtype.

  qry_Address:ADD-BUFFER(lv_Buffers:HANDLE).

  CREATE BUFFER lv_buffers FOR TABLE address.

  qry_Address:ADD-BUFFER(lv_Buffers:HANDLE).

The error is caused because the address buffer is not in scope.  There is no reference to this table earlier in the code.  If you want to access this table dynamically without a prior reference then I would refer you to the doc:

FOR TABLE table-name|table-handle|buffer-handle A character expression (table-name) that evaluates to a unique database table name or static temp-table name, a temp-table handle (table-handle), or to an existing buffer object handle (buffer-handle), each of which can specify the record source for which to create the buffer object.

If table-name is ambiguous, you must qualify the database table name with a database name or rename the temp-table. Otherwise, if the database table exists in multiple connected databases, the AVM creates the buffer in the first connected database

So...

// note the double quotes

CREATE BUFFER lv_buffers FOR TABLE "address".

The key part of the doc I posted is this -> A character expression

Thanks Brian,

I'm really confused. It must be something of progress that I can't figure out.

When I use both tables "addrtype" and "address" in the query SET-BUFFERS method, it works fine.

But when I use both tables in the query ADD-BUFFER, the system has problems with the "addrtype" table. Strange to me.

I have tried all variations of syntax, nothing seems to give me the result I need.

Rg,

Ab

CREATE BUFFER lv_buffers FOR TABLE "addrtype".

qry_Address:ADD-BUFFER(lv_Buffers:HANDLE).

CREATE BUFFER lv_buffers FOR TABLE "address".

qry_Address:ADD-BUFFER(lv_Buffers:HANDLE).

Thanks Brian,

I have tried that, but it doesn't work. My problem lies in the fact that the system can not reference my "addrtype" table.

Because I have a browser, which displays only fields from the "address" table. The table "addrtype" is only used in the query string to narrow down the address records by the selected address type.

I need to make some reference to the table "addrtype", before the system can use it.

Ab,

No you don't.  The code I posted works for me when pasted in to a Procedure Editor and executed.  Obviously I replaced the table names with some from the sports2000 database but I have no previous reference to the tables either.

There is something else wrong and you need to one of the following:

1) Provide the full and complete source code.

2) Open a support case with Technical Support (of course, we will want the full and complete source code too).

Brian

Hi Brian,

Issue solved.

I have removed the creation of the buffer and now it works.

I thought ip_Buffers contains my tables, why should I create an additional buffer.

CREATE QUERY qry_Address.  

DO lv_x = 1 TO NUM-ENTRIES (ip_Buffers):                      */

/*   CREATE BUFFER lv_buffers FOR TABLE ENTRY(lv_x, ip_Buffers).*/

qry_Address:ADD-BUFFER( ENTRY(lv_x, ip_Buffers)).                 */

END.  

Thanks for you help.