Dynamic-Tables and static queries

[Latika6]

I have to replace static temp-table with a dynamic temp-table in my application because the table will now exist in the database. My question is- Do I now need to replace all the static queries with dynamic queries. I mostly work with static queries.

 

[Stefan]

Yes, a dynamic temp-table can only be queried dynamically. A static temp-table can be queried statically or dynamically.

You can also pass a dynamic temp-table as table-handle to a static receiver (I think).

But why not just rename the temp-table and put it in its own namespace?

define temp-table ttfoo ... // was foo

All use of the temp-table should be referencing buffers on the temp-table, so you only need to refactor the buffer definitions:

define bufer bufoo for ttfoo. // was foo

 

[Latika6]

Yes, a dynamic temp-table can only be queried dynamically. A static temp-table can be queried statically or dynamically.

You can also pass a dynamic temp-table as table-handle to a static receiver (I think).

But why not just rename the temp-table and put it in its own namespace?

define temp-table ttfoo ... // was foo

All use of the temp-table should be referencing buffers on the temp-table, so you only need to refactor the buffer definitions:

define bufer bufoo for ttfoo. // was foo

Hello Stefan, could you please tell me what you mean by "why not just rename the temp-table and put it in its own namespace?" At the moment, I have a define temp-table in my program and all queries use the temp-table. Now the temp-table is in the database like any other table.

 

[Rob Fitzpatrick]

I have to replace static temp-table with a dynamic temp-table in my application because the table will now exist in the database.

Now the temp-table is in the database like any other table.

"The temp-table is in the database" doesn't make sense to me. Database tables are in the database. Temp-tables are client-side.

Do you mean that data that you previously wrote to and read from a static temp-table will now reside in a database table? And that you are going to cache that table's data in a temp-table?

Or do you mean something else?

 

[Latika6]

"The temp-table is in the database" doesn't make sense to me. Database tables are in the database. Temp-tables are client-side.

Do you mean that data that you previously wrote to and read from a static temp-table will now reside in a database table? And that you are going to cache that table's data in a temp-table?

Or do you mean something else?

I mean that temp-table is being removed. Instead, a table to database is added having the same fields as temp-table. my screen (used for display) and its queries, still use the temp-table as defined earlier. Now, I intend to remove the temp-table and have it as a database table.

 

[Rob Fitzpatrick]

You rephrased it slightly without clearly explaining what is going on, which doesn't help my comprehension.

that temp-table is a database table now.

Present tense.

I intend to remove the temp-table and have it as a database table.

Future tense.

I can't tell from this what your application looks like now or what you intend it to look like in the future.

Maybe if you actually showed some code and schema, instead of speaking in general terms, it would be more clear.

 

[Latika6]

An example is-

define temp-table ttQuote no-undo
    field quote-date       as date
    field risk                  as character
    field client-name           as character
    field address               as character
    field product-code          as character
    field risk-num              as integer
    field risk-version          as integer
    field risk-det-num          as integer
    field state                 as character.

This temp-table has the fields that I want to display on my dashboard. If I need extra fields in my report, the user will have to add fields on this temp-table.
Every time an extra field is needed, the temp-table gets edited. If this was to be replaced with a database table then the users can add or remove fields from the maintenance screen. Also, the dynamic temp-table will pick the additional fields.

 

[TomBascom]

Wouldn’t the users then have to add the fields on the database table? And isn’t that rather more work than updating a temp table?

Something about what you are trying to do isn’t making sense to us. Maybe a bit of sample code would go a long way. Or a more complete description of the functionality of your dashboard. Or both.

 

[Latika6]

Hello @TomBascom, the reason is that when the user says I would like the see 'new field' on the dashboard, we do not need a programmer to add this to the temp table, we can just create a new record and the dashboard routine will pull the data in. Therefore, we do not need to wait for a release in order to supply the information to the user. I am not sure, how I can implement this. At the moment, this is existing in the form of static queries and a temp-table that has got the fields of the dashboard

 

[TomBascom]

As I said - a little bit of sample code would go a long ways.

Or a diagram on a white board.

Trying to explain "it" with a few short sentences isn't getting anywhere. My mind reading skills are exceptionally poor and I still have no idea what you are trying to do.

 

[Latika6]

I think, I can explain the problem better having worked on it for the last two weeks. If the dashboard shows number, name, date, create-date, price, they go as fields on the temp-table as shown in one of my messages(#7) on this post. So, if an extra column is required on the dashboard, that will be an extra field on the temp-table. This means, for every extra column on the dashboard we need an extra field on the temp-table and hence a code change.

Instead, if we have a database table with the following fields-
Column-name | Table-name | field-name

and we can keep adding records on the table, for the columns that we need on our report. Hence, the need for dynamic table. I am still trying to find information on dynamic queries.

 

[TomBascom]

Here is an example of using a dynamic query to get arbitrary data from a database:

/* getdyn.p
 *
 * run ./getdyn.p ( "customer", "where custNum = 1", "name,custNum,state,xyzzy", output resultList ).
 *
 */

define input  parameter tableName   as character no-undo.
define input  parameter whereClause as character no-undo.
define input  parameter fieldList   as character no-undo.

define output parameter resultList  as character no-undo.

define variable b   as handle no-undo.
define variable q   as handle no-undo.

define variable qs  as character no-undo.
define variable fv  as character no-undo.
define variable i   as integer   no-undo.

resultList = ?.

qs = substitute( "for each &1 &2", tableName, whereClause ).

/* message qs. */

create query  q.
create buffer b for table tableName.

q:set-buffers( b ).

q:query-prepare( qs ).
q:query-open.

if q:get-next( no-lock ) = false then
  do:
    message "record not found: " qs.
    return.
  end.

do i = 1 to num-entries( fieldList ):
  resultList = resultList + ( if i > 1 then "," else "" ).
  assign
    fv = ?
    fv = b:buffer-field( entry( i, fieldList )):buffer-value()
  no-error.
  resultList = resultList + quoter( fv ).
end.

return.

 

[Latika6]

@TomBascom , Thank you for the above query - Dynamic query explained in an easy to understand manner. I think, I am going beyond the scope of this post, I am tempted to ask another question here. I have to implement this with the help of dataset. and I do not clearly understand the role of a data-source, If I have Dataset, Temp-table and Queries.

 

[TomBascom]

I suggest that you open a new post in order to ask a new question.

It makes it easier when people are searching for answers.