Populating Temp-Tables

[KMoody]

I want to populate a reference temp table with hard-coded information:

CREATE glConverter.
glConverter.gl-num = "40101".
glConverter.prod-code = "30101".
RELEASE glConverter.

CREATE glConverter.
glConverter.gl-num = "40102".
glConverter.prod-code = "30102".
RELEASE glConverter.

CREATE glConverter.
glConverter.gl-num = "40212".
glConverter.prod-code = "30212".
RELEASE glConverter.

CREATE glConverter.
glConverter.gl-num = "40228".
glConverter.prod-code = "30228".
RELEASE glConverter.
/*etc...............*/

(At the moment, we cannot add a new table to the database with this information.)

Is there a more elegant way to do this?

 

[TomBascom]

Put the data in "data.dat" (space delimited in this example):

abc 123
456 789
def ghi

and then use something like:

define temp-table tt_x
  field f1 as character
  field f2 as character
.

input from "data.dat".
repeat:
  create tt_x.
  import tt_x.
end.
input close.

for each tt_x:
  display tt_x.
end.

 

[PhilW]

put the static data in a delimited txt file and load it up using something like the code below.

def stream ip.

def var v-reccnt as int no-undo.
def var v-data as char no-undo.

input stream ip from value(yourfilename here.txt) no-echo.

repeat:
v-reccnt = v-reccnt + 1.
import stream ip unformatted v-data.
create glConverter.
assign
glConverter.gl-num = int(entry(1,v-data,'yourefielddelimeter'))
glConverter.prod-code = int(entry(2,v-data,'yourefielddelimeter')).
end.
end.
input stream ip close.
message v-reccnt ' records loaded' view-as alert-box.

 

[PhilW]

ha tom beat me to it!

 

[KMoody]

Thanks. I'd prefer not to use an external text file, though. Can I treat the value of a character variable just like the contents of a file?

 

[TomBascom]

You cannot IMPORT from a character variable.

 

[PhilW]

you can do a loop with entry(x,var) etc and create a new temp table rec for each iteration.
def var v-data as char no-undo init '1,one:2,two:3,three'.
def var v-fdata as char no-undo.
def var x as int no-undo.
do x = 1 to num-entries(v-data,':'):
v-fdata = entry(x,v-data,':').
create glConverter.
assign
glConverter.gl-num = int(entry(1,v-data))
glConverter.prod-code = int(entry(2,v-data)).
end.

 

[JamesBowen]

Just chucking in my two cents and mixing thing up. You could store the temp-table in the form of an XML or even JSON string format within your source code and simple use the READ-XML/READ-JSON method to populate the temp-table. The XML/JSON string could even be stored in an include file. However I don't promote this kind of coding.

XML VERSION:

DEFINE TEMP-TABLE glConverter NO-UNDO
FIELD gl-num    AS INTEGER
FIELD prod-code AS INTEGER XML-NODE-TYPE 'attribute'.

DEFINE VARIABLE lcXMLDefaults AS LONGCHAR     NO-UNDO.

ASSIGN
    lcXMLDefaults = '<?xml version="1.0"?>'
    lcXMLDefaults = lcXMLDefaults + '<glConverter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'
    /** Copy and paste the following line which represents each record of the temp-table**/
    lcXMLDefaults = lcXMLDefaults + '<glConverterRow prod-code="23423"><gl-num>234234</gl-num></glConverterRow>'
    lcXMLDefaults = lcXMLDefaults + '</glConverter>'.

/** Load the string (longchar into a temp-table) **/
TEMP-TABLE glConverter:READ-XML('LONGCHAR', lcXMLDefaults,'EMPTY',?,?).


FOR EACH glConverter:
    DISPLAY glConverter.
END.

JSON VERSION:

DEFINE TEMP-TABLE glConverter NO-UNDO
FIELD gl-num    AS INTEGER
FIELD prod-code AS INTEGER.

DEFINE VARIABLE lcXMLDefaults AS LONGCHAR     NO-UNDO.

ASSIGN
    lcXMLDefaults = '~{"glConverter":['
    /** Copy and paste the following line which represents each record of the temp-table separated by a comer**/
    lcXMLDefaults = lcXMLDefaults + '~{"gl-num":123,"prod-code":3453},'
    lcXMLDefaults = lcXMLDefaults + '~{"gl-num":234,"prod-code":4567}'
    lcXMLDefaults = lcXMLDefaults + ']}'.

/** Load the string (longchar into a temp-table) **/
TEMP-TABLE glConverter:READ-JSON('LONGCHAR', lcXMLDefaults,'EMPTY').


FOR EACH glConverter:
    DISPLAY glConverter.
END.

 

[tamhas]

To chime on with the XML/JSON version, one technique I have used in the past is to simply write a program which includes all the manual assignment to the temp-table and then does a WRITE-XML to create the external file. This makes it very easy to update as changes occur. Then, the actual application does a READ-XML to populate the working temp-table.