Table Data Records

P

Progress_Learner

Member
Hello Guys, hope you all are doing well.

Could you please suggest how to do WRITE-JSON from a dynamic temp-table?

Code: 
CREATE TEMP-TABLE hTemp.
  hTemp:ADD-FIELDS-FROM(hBuffer,_field._field-name).
  hTemp:TEMP-TABLE-PREPARE("ttDelete").
  hDefault = hTemp:DEFAULT-BUFFER-HANDLE.
  
  hDefault:BUFFER-CREATE.
  hDefault:BUFFER-VALUE = vBufferValue.
  
  ASSIGN
    vTargetType = "file"
    vFile             = "ttDelete.json"
    lformatted   = TRUE
    lretok           = TEMP-TABLE ttDelete:WRITE-JSON(vTargetType, vFIle, lformatted).

Getting error as Unknown or Ambiguous table ttDelete.

Regards,
Learner
 
O

Osborne

Active Member
With a dynamic temp-table it is the variable handle you use:
Code: 
hTemp:WRITE-JSON(vTargetType, vFIle, lformatted).
 
P

Progress_Learner

Member
Hello Osborne/All,

Thanks for your reply!

I am able to create dynamic temp-table in an iteration and write data to JSON File but values are getting overlapped, I need to append the data to JSON file.

Could you please share how to append values to JSON file like we do with Output to statement.

Code: 
ASSIGN
    vTargetType = "file"
    vFile       = "C:\ttDelete.json"
    lformatted  = TRUE
    lretok      = hTemp:WRITE-JSON(vTargetType, vFIle, lformatted).

Regards,
 
Last edited:
Stefan

Stefan

Well-Known Member
Since JSON is not a line based format, you cannot simply append like with the output statement, so you need to:
  1. read-json file into temp-table
  2. create temp-table records
  3. write-json temp-table to file
 
P

Progress_Learner

Member
Thanks for your reply Stefan but I found problem is with Dynamic temp-table.

Code: 
  hDefault:BUFFER-FIELD(_field._field-name):BUFFER-VALUE = vBufferValue.
   
END.

If I am displaying temp-table after END statement then only last record is getting displaying and same is coming in JSON file. Could you please share your thoughts on this.

Regards,
 
Last edited:
Stefan

Stefan

Well-Known Member
You are creating the dynamic buffer, query and temp-table per index field of the unique index on table 'test'. So you are:
  1. not getting what you need
  2. leaking memory since you are repeatedly creating dynamic objects over each other
 
P

Progress_Learner

Member
Yes Stefan, you got it right.

I need to create Dynamic temp-table with Unique Index fields and values and then write that to JSON file. If I display dynamic Temp-Table before END statement then I am getting all unique indexes/values in the temp-table but after END only last one is available (seems some leakage issue as you mentioned).

Please suggest how to fix this problem.

Regards,
 
Stefan

Stefan

Well-Known Member
Progress_Learner said:
I need to create Dynamic temp-table with Unique Index fields and values and then write that to JSON file. If I display dynamic Temp-Table before END statement then I am getting all unique indexes/values in the temp-table but after END only last one is available (seems some leakage issue as you mentioned).
Click to expand...
So you need to not do that and only create what you need when you need it. If you want a temp-table with multiple records, you need to create the temp-table once and then create multiple records in that temp-table.
 
P

Progress_Learner

Member
Yes Stefan, agreed. I am new to dynamic temp-tables. I need to create temp-table once so I moved statement "CREATE TEMP-TABLE hTemp." before initial FOR FIRST loop so that table gets created only once and records created multiple times based on unique indexes but getting below error:

1652777697926.png

Regards,
 
Stefan

Stefan

Well-Known Member
I cannot make heads nor tails of your code.

// first you need to prepare your temp-table based on the index

create ht.
for each _file...
ht:add-like-field.
end.
ht:temp-table-prepare().

// then you get records and populate temp-table

create hq.
hq:query-prepare().
do while hq:get-next:
hb:buffer-create().
hb:buffer-field:buffer-value =
end.

// finally you write json

ht:write-json()
 
O

Osborne

Active Member
As Stefan has posted it is really not easy to make heads nor tails of your code.

Are you trying to create a single JSON file of all the tables or separate JSON files for each table? If the former then a bit of thought is required because as Stefan posted JSON is not a line based format, you cannot simply append like with the output statement. You will have to have multiple dynamic temp tables and a dataset for the temp tables and write the dataset to JSON.

This is based on your original code and it will produce separate JSON files for each table and hopefully gets you started:

Code: 
FOR FIRST _file NO-LOCK
    WHERE _file._file-name = "test"
    ,FIRST _index OF _file NO-LOCK
     WHERE _unique = TRUE
    ,EACH _index-field OF _index NO-LOCK
  ,FIRST _field OF _index-field NO-LOCK:  
 
   fld = _field._field-name.
 
   CREATE buffer bh for table vTable.

   /* Temp-Table Create */
   CREATE TEMP-TABLE hTemp.
   hTemp:ADD-LIKE-FIELD(fh:NAME,fh).
   hTemp:TEMP-TABLE-PREPARE("ttDelete").
   hDefault = hTemp:DEFAULT-BUFFER-HANDLE.

   /* Create a Dynamic Query for the database records */
   CREATE QUERY  qh.
   qh:SET-BUFFERS(bh).
   qh:QUERY-PREPARE( "for each " + vTable + " no-lock" ).
   qh:QUERY-OPEN().
   qh:GET-FIRST().
   DO WHILE NOT qh:QUERY-OFF-END:
      /* Create a dynamic temp-table record for each database record */
      ASSIGN fh = bh:BUFFER-FIELD( _field._field-name )
             vBufferValue = fh:BUFFER-VALUE() NO-ERROR.
      hDefault:BUFFER-CREATE().
      hDefault:BUFFER-FIELD(_field._field-name):BUFFER-VALUE = vBufferValue.
      hDefault:BUFFER-RELEASE().
      qh:GET-NEXT().
   END.
   qh:QUERY-CLOSE().

   /* All database records read so now write to the JSON file */
   ASSIGN
      vTargetType = "file"
      vFile       = vTable + "_ttDelete.json"
      lformatted  = TRUE
      lretok      = hTemp:WRITE-JSON(vTargetType, vFIle, lformatted).

   /* Clean up all the dynamic objects so that we don't produce a memory leak */
   DELETE OBJECT qh.
   DELETE OBJECT bh.
   DELETE OBJECT hTemp.
   ASSIGN qh = ?
          bh = ?
          hTemp = ?.
END.
 
P

Progress_Learner

Member
Thanks a lot Osborne for your help but getting error (included all handle and other variables) while running this code:

1652782354251.png

Trying to resolve this error.

Regards,
 
P

Progress_Learner

Member
Yes, I am trying to create Separate JSON file for each table having information of ONLY Unique index of a table.

For example:

I have one table "test" with one unique index having five fields then I need to capture all these five fields in JSON file like below:

{"ttDelete": [
{
"one" : "test1"
"two" : 231424,
"three" : yes,
"four" : 2314324.68,
"five" : 02/01/22,
}
]}

Regards,
 
P

Progress_Learner

Member
Osborne said:
As Stefan has posted it is really not easy to make heads nor tails of your code.

Are you trying to create a single JSON file of all the tables or separate JSON files for each table? If the former then a bit of thought is required because as Stefan posted JSON is not a line based format, you cannot simply append like with the output statement. You will have to have multiple dynamic temp tables and a dataset for the temp tables and write the dataset to JSON.

This is based on your original code and it will produce separate JSON files for each table and hopefully gets you started:

Code: 
FOR FIRST _file NO-LOCK
    WHERE _file._file-name = "test"
    ,FIRST _index OF _file NO-LOCK
     WHERE _unique = TRUE
    ,EACH _index-field OF _index NO-LOCK
  ,FIRST _field OF _index-field NO-LOCK: 
 
   fld = _field._field-name.
 
   CREATE buffer bh for table vTable.

   /* Temp-Table Create */
   CREATE TEMP-TABLE hTemp.
   hTemp:ADD-LIKE-FIELD(fh:NAME,fh).
   hTemp:TEMP-TABLE-PREPARE("ttDelete").
   hDefault = hTemp:DEFAULT-BUFFER-HANDLE.

   /* Create a Dynamic Query for the database records */
   CREATE QUERY  qh.
   qh:SET-BUFFERS(bh).
   qh:QUERY-PREPARE( "for each " + vTable + " no-lock" ).
   qh:QUERY-OPEN().
   qh:GET-FIRST().
   DO WHILE NOT qh:QUERY-OFF-END:
      /* Create a dynamic temp-table record for each database record */
      ASSIGN fh = bh:BUFFER-FIELD( _field._field-name )
             vBufferValue = fh:BUFFER-VALUE() NO-ERROR.
      hDefault:BUFFER-CREATE().
      hDefault:BUFFER-FIELD(_field._field-name):BUFFER-VALUE = vBufferValue.
      hDefault:BUFFER-RELEASE().
      qh:GET-NEXT().
   END.
   qh:QUERY-CLOSE().

   /* All database records read so now write to the JSON file */
   ASSIGN
      vTargetType = "file"
      vFile       = vTable + "_ttDelete.json"
      lformatted  = TRUE
      lretok      = hTemp:WRITE-JSON(vTargetType, vFIle, lformatted).

   /* Clean up all the dynamic objects so that we don't produce a memory leak */
   DELETE OBJECT qh.
   DELETE OBJECT bh.
   DELETE OBJECT hTemp.
   ASSIGN qh = ?
          bh = ?
          hTemp = ?.
END.
Click to expand...
Hi Osborne, JSON produced by this code is also having only last Index field (same like my code). Seems the same issue that Stefan mentioned above, creating dynamic temp-table inside for each causing memory leak. :- CREATE TEMP-TABLE hTemp.
 
You must log in or register to reply here.

Similar threads

B
Problem with dynamic temp-table. Exceeding temp-table limit 32762
Replies
6
Views
2K
Stefan
Stefan
H
  • Locked
[Progress Communities] [Progress OpenEdge ABL] Forum Post: updating records of a dynamic temp-table
Replies
0
Views
417
hutcj
H
M
  • Locked
[Progress Communities] [Progress OpenEdge ABL] Forum Post: Can not create record in kay10 table via rest api using POST method in POSTMAN.
Replies
0
Views
657
memoxoz
M
Ramesh Chand Rebari
How to create a table and populate data by using .txt file .
Replies
12
Views
3K
Cecil
Cecil
B
  • Locked
[Progress Communities] [Progress OpenEdge ABL] Forum Post: READ-JSON from LongChar back into DB
Replies
0
Views
1K
bkozak
B
Top