Dynamic temp table to a static temptable

[jmac12]

Hi I'm using open edge 10.2b

I have a program thats got a browse that displays a temp table ttRecs. In this instance it’s defined like a database table grades. I want to use a dynamic query/temp table to get all the grades records (to start with) and then display them in a browse. Now the reason im using a temp table in my browse is that the column labiling and widths etc aren’t brilliant straight from the database. So thus I do a display in my browse and name my columns etc.

I’m trying to use dynamic query/table within a template to try and make my code more genric so my idea is that I have my ttRecs in my browse and do I dynamic query/table then past the results to ttRecs (so the display is correct). But I haven’t worked out how to do it and I was wondering if anyone knew?

Heres what I’ve got so far

define variable hTempTable          as handle no-undo.
DEFINE VARIABLE qh           AS HANDLE NO-UNDO.
/*chrMyTable = grades in this case*/
 
CREATE BUFFER hTempTable FOR TABLE chrMyTable.
CREATE QUERY qh.
qh:SET-BUFFERS(hTempTable).
qh:QUERY-PREPARE("FOR EACH " + chrMyTable + " NO-LOCK").
qh:QUERY-OPEN().
qh:GET-FIRST().

REPEAT:
     qh:GET-NEXT().   
     IF qh:QUERY-OFF-END THEN LEAVE.
     buffer-copy hTempTableto ttRecs.  /*how can i put what ive got in the handle into the temp table*/
end.

so any suggests would be good really i'd like a dynamic temp table so i can query later in the code if i want rather than building it all up again.

 

[MaximMonin]

For web apps i used following method:

define Temp-Table CollumnData NO-UNDO
  field IdField as integer
  field fLabel as character
  field fName as character
  field fWidth as integer
  field fType as character
  field fAlign as character
  index i0 IdField asc.

define input-output parameter ContextId as character.
define input-output parameter InfoType as character.
define input parameter RidDoc as integer.
define output parameter TABLE for CollumnData.
define output parameter TABLE-HANDLE DocumentData.

.....

if InfoType = "PARAM" then
do:
  create temp-table DocumentData.

  DocumentData:ADD-NEW-FIELD ("IdParam",    "integer"). 
  DocumentData:ADD-NEW-FIELD ("NameParam",  "character"). 
  DocumentData:ADD-NEW-FIELD ("Value",      "character"). 

  /* default sort */
  DocumentData:ADD-NEW-INDEX("i0", false, true).
  DocumentData:ADD-INDEX-FIELD("i0","IdParam").

  create CollumnData. i = 1.
  assign IdField = i fLabel = "Number" fName = "IdParam" fWidth = 6 fType = "Text" fAlign = "right".
  create CollumnData. i = i + 1.
  assign IdField = i fLabel = "Param Name" fName = "NameParam" fWidth = 30 fType = "Text" fAlign = "left".
  create CollumnData. i = i + 1.
  assign IdField = i fLabel = "Param Value" fName = "Value" fWidth = 50 fType = "Text" fAlign = "left".

  DocumentData:TEMP-TABLE-PREPARE ("DocumentData").
  CREATE BUFFER hBuffer FOR TABLE DocumentData:DEFAULT-BUFFER-HANDLE.
  CREATE QUERY hQuery.
  hQuery:SET-BUFFERS(hBuffer).

  FOR EACH system.doc-param-val WHERE system.doc-param-val.rid-document = RidDoc NO-LOCK,
      EACH system.doc-param of system.doc-param-val NO-LOCK:

    hBuffer:BUFFER-CREATE().
    hBuffer:BUFFER-FIELD("IdParam"):BUFFER-VALUE = system.doc-param.id-param.
    hBuffer:BUFFER-FIELD("NameParam"):BUFFER-VALUE = system.doc-param.name.
    hBuffer:BUFFER-FIELD("Value"):BUFFER-VALUE = system.doc-param-val.val.
  end.
END.
....

I returned static temp-table with attributes for my browse and second dynamic table
with data.

Client renders browser according static temp-table CollumnData and displays then dynamic table inside browse.
It is possible to write universal client module by using openedge 10 to display any table/info.

 

[jmac12]

just trying to understand ur code? dont see how the dynamic table is passing anything to the static one?

[/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma]  create temp-table DocumentData. /*create the temp table fine*/ [/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma]I could do it like this: [/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma]hTableLike = BUFFER ttRecs:HANDLE.[/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma]create temp-table hTempTable.[/FONT]
[FONT=Tahoma]hTempTable:CREATE-LIKE(hTableLike). /*make this temp table like my ttRecs (which is in my browse)[/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma]hTempTable:TEMP-TABLE-PREPARE("ttNew"). [/FONT]
[FONT=Tahoma]hBuffer = hTempTable:DEFAULT-BUFFER-HANDLE.[/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma]/*creating ur temp table here? */[/FONT]
[FONT=Tahoma]  create CollumnData. i = 1.[/FONT]
[FONT=Tahoma]  assign IdField = i [/FONT]
[FONT=Tahoma]            fLabel = "Number"[/FONT]
[FONT=Tahoma]            fName = "IdParam" [/FONT]
[FONT=Tahoma]            fWidth = 6 [/FONT]
[FONT=Tahoma]            fType = "Text" [/FONT]
[FONT=Tahoma]            fAlign = "right".[/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma]  DocumentData:TEMP-TABLE-PREPARE ("DocumentData").[/FONT]
[FONT=Tahoma]  CREATE BUFFER hBuffer FOR TABLE DocumentData:DEFAULT-BUFFER-HANDLE.[/FONT]
[FONT=Tahoma]  CREATE QUERY hQuery.[/FONT]
[FONT=Tahoma]  hQuery:SET-BUFFERS(hBuffer).[/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma]  FOR EACH system.doc-param-val WHERE system.doc-param-val.rid-document = RidDoc NO-LOCK,[/FONT]
[FONT=Tahoma]      EACH system.doc-param of system.doc-param-val NO-LOCK:[/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma]    hBuffer:BUFFER-CREATE().[/FONT]
[FONT=Tahoma]    hBuffer:BUFFER-FIELD("IdParam"):BUFFER-VALUE = system.doc-param.id-param.[/FONT]
[FONT=Tahoma]    hBuffer:BUFFER-FIELD("NameParam"):BUFFER-VALUE = system.doc-param.name.[/FONT]
[FONT=Tahoma]    hBuffer:BUFFER-FIELD("Value"):BUFFER-VALUE = system.doc-param-val.val.[/FONT]
[FONT=Tahoma]  end.[/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma]/*isn’t there a  more gengric way than above? I was thinking of doing query e.g. in ur example system.doc-param-val. Then but the values into my ttrecs?[/FONT]
[FONT=Tahoma]Is this not just creating a static temp table then putting values into ur dynamic table? In the above code your having to say each field rather than counting through them? And code in the table name aswell[/FONT]
[FONT=Tahoma]END.[/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma] [/FONT]
[FONT=Tahoma]

 

[FrancoisL]

Here is a simpler way to do it (STATIC TO DYNAMIC) :

DEF VAR mptr AS MEMPTR.
DEF TEMP-TABLE ttTest 
    FIELD fld1 AS CHAR
    FIELD fld2 AS INT
    INDEX ttTest IS PRIMARY fld1.

DEF VAR hTable AS HANDLE.

CREATE TEMP-TABLE hTable.

hTable:CREATE-LIKE(TEMP-TABLE ttTest:HANDLE).

CREATE ttTest.
ASSIGN ttTest.fld1 = "TEST" ttTest.fld2 = 2.
CREATE ttTest.
ASSIGN ttTest.fld1 = "TEST2" ttTest.fld2 = 7.

hTable:TEMP-TABLE-PREPARE("ttTest").
TEMP-TABLE ttTest:WRITE-XML("MEMPTR", mptr).

hTable:READ-XML("MEMPTR", mptr, "EMPTY", ?, FALSE).
SET-SIZE(mptr) = 0.

 

[FrancoisL]

And here is from DYNAMIC TO STATIC.

DEF VAR mptr AS MEMPTR.

DEF TEMP-TABLE ttTest 
    FIELD fld1 AS CHAR
    FIELD fld2 AS INT
    INDEX ttTest IS PRIMARY fld1.

DEF VAR hTable AS HANDLE.
DEF VAR hBuffer AS HANDLE.

CREATE TEMP-TABLE hTable.
hTable:CREATE-LIKE(TEMP-TABLE ttTest:HANDLE).
hTable:TEMP-TABLE-PREPARE("ttTest").

hBuffer = hTable:DEFAULT-BUFFER-HANDLE.

hBuffer:BUFFER-CREATE().
hBuffer::fld1 = "TEST".
hBuffer::fld2 = 4.

hBuffer:BUFFER-CREATE().
hBuffer::fld1 = "TEST4".
hBuffer::fld2 = 111.

hTable:WRITE-XML("MEMPTR", mptr).

TEMP-TABLE ttTest:READ-XML("MEMPTR", mptr, "EMPTY", ?, FALSE).
SET-SIZE(mptr) = 0.

 

[MaximMonin]

jmac12:

If you have to reuse some code, you should thinking about splitting code on levels:
db-level, logic level, presenation level.
It is possible to create fully dynamic browser and display dynamic temp-table in it. You dont have to to use static temp-table instead.

In example above i used static link db-level and logic level.
Instead you can use dynamic one with

CREATE BUFFER hTempTable FOR TABLE chrMyTable.
CREATE QUERY qh.
qh:SET-BUFFERS(hTempTable).
qh:QUERY-PREPARE("FOR EACH " + chrMyTable + " NO-LOCK").
qh:QUERY-OPEN().

REPEAT:
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
buffer-copy hTempTableto hBuffer. /*how can i put what ive got in the handle into the temp table*/
end.

 

[jmac12]

cheers for the help but im still struggling with it.

maximMonin what im trying to do is have some code that just does a query on table X thus the chrMytable so this can be generic. The reason I’m using a static temp table in a browse is that the labels are incorrect from the database so need something to do the labelling. So all I want to the programmer to do is set up the lables a via temp table then I can say heres the data display in browse.

So its: Get data via dynamic query
Display it in the browse but with the labels I want. I know I could do if name= “grade” then column-label = “LABEL” but then that would really miss the point. So that’s why im thinking if its all set up in the browse I can pass it the data and the labels will be correct

 

[MaximMonin]

As i understood you are trying to set column labels in temp-table description. But data columns are the same to db table.

define input-output temp-table ttDBTable.
define input parameter TableName as character.

DEFINE variable hQuery           AS HANDLE NO-UNDO.
DEFINE variable hBuffer          AS HANDLE NO-UNDO.
DEFINE VARIABLE hBufferField     AS HANDLE NO-UNDO.
DEFINE variable hQueryData       AS HANDLE NO-UNDO.
DEFINE variable hBufferData      AS HANDLE NO-UNDO.
DEFINE VARIABLE hBufferFieldData AS HANDLE NO-UNDO.

define variable sqlquery as character.
sqlquery = "FOR EACH " + TableName + " NO-LOCK".

CREATE QUERY hQuery.
hBuffer = ttDBTable:BUFFER-HANDLE.
hQuery:SET-BUFFERS(hBuffer).
fieldcount = hBuffer:NUM-FIELDS.


CREATE QUERY hQueryData.
CREATE BUFFER hBufferData FOR TABLE TableName.
hQueryData:SET-BUFFERS(hBufferData).
hQueryData:QUERY-PREPARE(sqlquery).
hQueryData:QUERY-OPEN.
hQueryData:GET-FIRST ().
repeat:
  if hQueryData:QUERY-OFF-END then leave.

  hBuffer:BUFFER-CREATE().
  DO j = 1 TO fieldcount:
    hBufferField = hBuffer:BUFFER-FIELD(j).
    hBufferFieldData = hBufferData:BUFFER-FIELD(j).
    hBufferField:BUFFER-VALUE = hBufferFieldData:BUFFER-VALUE.
  END.

end.

Something like this.

But anyway i think it isnt good way to solve this task.
It is better to return dbfield name/or field order and label you wanted to display in static temptable and write client code to change browse label dynamically.

 

[jmac12]

yeah ur prob right mate... I guess i could just go round colums of the browse which is better than my idea..and then i guess i wouldnt need the static table after all.. cause just apply the query to browse. the thing i was struggling with was getting the data ive got in the buffer out into the browse in a good format. But i'll try what you've said and let you know how i get on thanks for the help

 

[jmac12]

I've done the following:

chrQuery = "FOR EACH " + chrMyTable.
                                       
do with frame {&frame-name}:
 
    create query hQuery.
    hQuery:set-buffers(hTempTable).
    hQuery:query-prepare(chrQuery).
    hQuery:query-open().
    
    hQuery:get-first().
    brDetail:query = hQuery.
    
    brDetail:add-columns-from(hTempTable).

end.

though this seems slower than a for each is there a quicker way of adding to the browse rather than doing add-columns-from? as ive already set up my columns in a Previous Procedure.