2

I get a database table name at run time(let us suppose from user). I need to query the table and return few fields(which I know). How to do this?

"FOR EACH" wont accept a variable name in it. So, I cant use it.

I have gone through dynamic queries, especially SET-BUFFERS() method. Even with this, I need to know the table name before.

I need something like:

DEF VAR tablename AS CHAR. 
tablename = somename.
FOR EACH tablename WHERE ....:
     ...
     ...
END.

Can someone please point me to right direction?

Austin
  • 1,237
  • 1
  • 11
  • 22
  • @ScubaManDan Progress database itself – Austin May 19 '14 at 08:34
  • If you don't know the name of the table it seems pretty hard to get any information from it. You can display all table names in the db by using the hidden table _file, simply do `FOR EACH _file NO-LOCK: DISPLAY _file. END.` – Jensd May 19 '14 at 08:44
  • @Jensd Thank you! But, I know the table name at run time here :(. I want to use that name in queries like "FOR EACH" (or any other thing that serves the purpose) – Austin May 19 '14 at 08:47

1 Answers1

5

You can do a dynamic query with a dynamic buffer. Simply replace the value of cTableName variable in this example:

/* Replace _file with whatever field you're after */
DEFINE VARIABLE cTableName AS CHARACTER   NO-UNDO INIT "_file".

DEFINE VARIABLE hQuery  AS HANDLE      NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE      NO-UNDO.

CREATE BUFFER hBuffer FOR TABLE cTableName.
CREATE QUERY hQuery.

hQuery:SET-BUFFERS(hBuffer).

hQuery:QUERY-PREPARE("FOR EACH " + cTableName).
hQuery:QUERY-OPEN().

REPEAT:
    hQuery:GET-NEXT().
    IF hQuery:QUERY-OFF-END THEN LEAVE.

    DISPLAY hBuffer:BUFFER-FIELD(1):BUFFER-VALUE.

    /* If you know the name of the field you can do: */
    /* DISPLAY hBuffer:BUFFER-FIELD("nameoffield"):BUFFER-VALUE. */

END.


/* Clean up */
hQuery:QUERY-CLOSE().
hBuffer:BUFFER-RELEASE().
DELETE OBJECT hBuffer.
DELETE OBJECT hQuery.
Jensd
  • 7,886
  • 2
  • 28
  • 37
  • @Jensd Ah! This is what I am looking for. Thank you very much! I should have extended my knowledge to dynamic buffers before. – Austin May 19 '14 at 11:08