Use Variable As Table Name In Find First

Rob Case · Oct 2, 2015

Hi all,

I'm curious if you can use a variable in a table lookup. I have 6 tables (3 production and 3 history). I want to search for a value in the database but would like to keep the coding as short as possible.

<code>

def var wk_tablename as char extent 6 format 'x(10)'.
def var fulltablename as char format 'x(10)'.
def var x as int init 0.

assign wk_tablename[1] = 'tab1_task'
wk_tablename[2] = 'tab2_task'
wk_tablename[3] = 'tabl3_task'
wk_tablename[4] = 'tab1_hist'
wk_tablename[5] = 'tab2_hist'
wk_tablename[6] = 'tabl3_hist'.

repeat:
x = x + 1.
fulltablename = wk_tablename[x].
find first fulltablename.
if available fulltablename no-lock then display fulltablename.
if x = 6 then leave.
end.

</code>

The coding I provided is not complete but I was unsure if you can lookup a database table name if that table name would be provided from a variable.

Thanks ,
Rob

TheMadDBA · Oct 2, 2015

Look up dynamic queries (CREATE BUFFER, CREATE QUERY and the buffer methods like FIND-FIRST,FIND-UNIQUE,etc)

RealHeavyDude · Oct 5, 2015

Just like the MadDBA said - the only way to achieve that is to use dynamic buffers ( and probably query objects ).

This is just a code example:

Code:

define variable bufferObject  as handle  no-undo.
define variable tableName  as character  no-undo.
/* Create the dynamic objects */
create buffer bufferObject for table "myTable".
bufferObject:find-first ( 'where true', no-lock ).
if bufferObject:available then do:
  /* Your stuff */
end.
/* Very important: Clean up - delete resources not used anymore to prevent a memory leak */
if valid-handle ( bufferObject ) then
  delete object bufferObject.

Heavy Regards, RealHeavyDude.

ForEachInvoiceDelete · Oct 5, 2015

You could go all hard core.

Code:

def var ctable as char.
def var cfield as char init "customercode". /* YOUR FIELD HERE*/
def var cvalue as char init "turncarl". /* Your value here */
def var cdir  as char init "C:/newfolder/". /* Some empty folder path here */
def var cFile as char.
def var cpath as char.

ctable = "Customer". /* YOUR TABLE NAME HERE */

Output to value("C:/NewFolder/" + ctable + ".p").

put unformatted "Find first " ctable " Where " ctable "." cfield " = " "'" cvalue "'" " no-lock no-error." SKIP.
put unformatted "if avail(" ctable ") then do:" SKIP.
put unformatted "Display " ctable "." cfield " with 1 col scrollable." SKIP.
put unformatted "End." SKIP.

output close.

input from os-dir(cdir) echo.

repeat:

    import cfile.

    assign cpath = cdir + cfile.

end.

run value(cpath).

os-delete value(cpath).

Quick disclaimer.

I wouldn't ever recommend using this code. Use dynamic queries and build a query string.

kolonuk · Nov 17, 2015

ForEachInvoiceDelete: we do just that for a HUGE statistics report program - it was before we found out about dynamic queries, and we just haven't got round to changing it yet... Works quite well, except when things go wrong! Luckily it's just a report and not actually changing any data!

TomBascom · Nov 17, 2015

Back in the good old days that sort of thing was the only option we had and we were glad to have it!

We also had to carry bits out in a bucket and dump them behind the woodshed...

Dynamic queries are a much, much better way to go. Spend a few hours getting comfortable with them. You will be glad that you did.

andre42 · Nov 25, 2015

ForEachInvoiceDelete said:
You could go all hard core.

...

Quick disclaimer.

I wouldn't ever recommend using this code. Use dynamic queries and build a query string.

If you rely on on-the-fly compile, you could just as well use compile time arguments. Much easier to read, and no temporary files.
(I only use this for some special-purpose stuff where I have to go over all tables and which doesn't have to run in production.)

Use Variable As Table Name In Find First

Rob Case

New Member

TheMadDBA

Active Member

RealHeavyDude

Well-Known Member

ForEachInvoiceDelete

Active Member

kolonuk

Member

TomBascom

Curmudgeon

andre42

Member

Similar threads