Consultor Eletrônico

Status: Verified

GOAL:

How to have separate elements in an array field

FACT(s) (Environment):

Progress 9.1x

FIX:

Array fields are created using the Progress 4GL Dictionary. In Progress 9.1A the SQL-92 engine manages the arrays as NVARCHAR string, and the string representation is the concatenation of each array element with a semicolon ( ; ) separating each element.

The Progress SQL-92 engine provides three functions to handle the array fields: PRO_ELEMENT, PRO_ARR_ESCAPE, PRO_ARR_DESCAPE.

To access an array field, you would normally use the pro_element function as in the following example statement which accesses element 1 of the extent field monthquota:

- select pro_element(monthquota,1,1) from pub.salesrep

Sometimes you may want to have access to the array fields as separate elements. For example, you are working with a client tool where you just want to be able to specify the field name without using the pro_element function directly. The following example statement shows how a select statement can be used after running the program given later in this solution. A view called SQL-92.salesrep is created performing the calls to pro_element internally.

- select monthquota__1 from SQL92.salesrep

The 4GL program given below can be used to create view definitions for the SQL-92 engine so that client applications can access the array as separate fields.

Using these views, a client application does not need to separate the array elements on the client and it also minimizes the data sent to the client, because only the queried fields are sent.

The program can also be used to convert hyphens ( - ) to underscores ( _ ) so you can access the tables without needing to use double quotes. Note that double quotes are needed to escape hyphens because a hyphen is not a valid character for an identifier in standard SQL.

You can customize the program to fit your needs.

The generated SQL statements are SQL-92 and you can use them from Progress SQL Explorer or any other SQL frontend. If COMMIT WORK is not valid in your SQL frontend tool, perhaps, it has autocommit or a different way to commit, simply remove the COMMIT WORK statement or uncheck the "Add commit to script" option.

/*** PROGRAM ***/

/*
* Program that creates a script with view definitions
* to handle array fields as individual fields
* also it does convertion of hyphens to underscores
*
* Multiple tables can be selected.
*
* How to use it:
* - Simple run the program connected to a database (ex. SPORTS)
- Select a table with an array fields (or without) (ex. Salesrep)
* - Choose button "Create SQL Script"
* - Review the SQL statement to check out what was done
* - Run the generated SQL statement from any SQL-92 client
*
* Author: Edsel Garcia - Progress Technical Support
*/


DEF BUTTON b-read LABEL "Read crview.sql".
DEF VAR wlog AS LOGICAL.
DEF VAR wfields AS CHAR.
DEF VAR wcur-field AS CHAR.

DEF VAR wdata-type AS CHAR.
DEF STREAM out-stream.

DEF VAR select-1 AS CHAR
VIEW-AS SELECTION-LIST
TOOLTIP "Database tables - Multiselection allowed"
MULTIPLE INNER-CHARS 20 INNER-LINES 15
SCROLLBAR-VERTICAL LABEL "Tables".

DEF VAR wreplace_tables_hyphens_with AS CHAR INITIAL "_"
LABEL "Convert hyphens in tables to"
VIEW-AS FILL-IN
TOOLTIP
"Example: table order-line would be converted to order_line".
DEF VAR wreplace_fields_hyphens_with AS CHAR INITIAL "-"
LABEL "Convert hyphens in fields to"
VIEW-AS FILL-IN
TOOLTIP "Example: field cust-num would be converted to cust_num".
DEF VAR warray_delimiter AS CHAR INITIAL "__"
LABEL "Array Delimiter"
VIEW-AS FILL-IN
TOOLTIP
"Example: array element month-quota[1] would be month-quota__1".

DEF VAR wcommit AS LOGICAL INITIAL YES
LABEL "Add commit to script"
VIEW-AS TOGGLE-BOX
TOOLTIP "A commit statement will be added to the script".

DEF BUTTON b-create LABEL "Create SQL Script"
TOOLTIP &quot.;Generate SQL script".

DEF VAR weditor AS CHAR LABEL "Viewer"
VIEW-AS EDITOR TOOLTIP "SQL script viewer"
INNER-CHARS 63 INNER-LINES 15 SCROLLBAR-VERTICAL LARGE.

DEF VAR wuse-convert AS LOGICAL INITIAL YES
LABEL "Use convert function"
VIEW-AS TOGGLE-BOX
TOOLTIP
"The array fields would be converted to its defined data type".

&SCOPED-DEFINE OUT PUT STREAM out-stream UNFORMATTED

FORM
"Move the mouse over each widget to have a small description"
AT ROW 1 COL 2
"or example of its function" AT ROW 1.7 COL 2
select-1 AT ROW 3 COL 2
"Options:" VIEW-AS TEXT SIZE 45 BY 0.62
BGCOLOR 1 FGCOLOR 15 FONT 6 AT ROW 3 COL 34
wreplace_tables_hyphens_with AT ROW 4 COL 60 COLON-ALIGNED
wreplace_fields_hyphens_with AT ROW 5 COL 60 COLON-ALIGNED
warray_delimiter AT ROW 6 COL 60 COLON-ALIGNED
wuse-convert AT ROW 8 COL 55
wcommit AT ROW 9 COL 55
b-create AT ROW 11 COL 58
weditor AT ROW 13 COL 2
WITH FRAME FRAME-A SIDE-LABELS THREE-D WIDTH 80.

ON CHOOSE OF b-create DO:
DEF VAR wf AS INT.
DEF VAR wfiles AS CHAR.

wfiles = SELECT-1:SCREEN-VALUE.
ASSIGN wreplace_tables_hyphens_with
wreplace_fields_hyphens_with
warray_delimiter
wuse-convert wcommit.
IF wfiles = "" OR wfiles = ? THEN DO:
MESSAGE "Select the desired tables from the selection list."
VIEW-AS ALERT-BOX.
RETURN.
END.
OUTPUT STREAM out-stream TO crview.sql.
DO wf = 1 TO NUM-ENTRIES(wfiles).
RUN TableToView(ENTRY(wf, wfiles)).
END.
IF wcommit THEN
{&OUT} 'COMMIT WORK' '~;' SKIP.
OUTPUT STREAM out-stream CLOSE.
wlog = weditor:READ-FILE("crview.sql").
END.

FOR EACH _file WHERE _file-number > 0
AND _file-number < 32768 NO-LOCK BY _file-name:
select-1:ADD-LAST(_file-name).
END.

UPDATE
select-1
wreplace_tables_hyphens_with
wreplace_fields_hyphens_with
warray_delimiter
wuse-convert
wcommit
b-create
weditor
WITH FRAME FRAME-A.

PROCEDURE TableToView:
DEF INPUT PARAMETER ptable AS CHAR.
DEF VAR I AS INT.
DEF VAR J AS INT.
DEF VAR wtable_name AS CHAR.
DEF VAR wfield_name AS CHAR.

FIND _file WHERE _file-name = ptable
AND _file-number > 0 AND _file-number < 32768
NO-LOCK NO-ERROR.
IF NOT AVAILABLE _file THEN DO:
MESSAGE "Table: " ptable "is not available" VIEW-AS ALERT-BOX.
RETURN.
END.

wtable_name = REPLACE(_file-name,"-",
wreplace_tables_hyphens_with).
{&OUT} "DROP VIEW " + "sql92." + '"'
+ wtable_name + '"' + ";" SKIP.
{&OUT} "CREATE VIEW " + "sql92." + '"'
+ wtable_name + '"' SKIP.
{&OUT} "( " SKIP.
wfields = "".
FOR EACH _field OF _file NO-LOCK BREAK BY _order:
IF wfields <> "" THEN wfields = wfields + ",".
wfields = wfields + _field-name.
END.

DO J = 1 TO NUM-ENTRIES(wfields):
wcur-field = ENTRY(J, wfields).
FIND _field OF _file WHERE _field-name = wcur-field NO-LOCK.
wfield_name = REPLACE(_field-name,
"-",
wreplace_fields_hyphens_with).
IF _extent = 0
OR LOOKUP(_field._data-type,"RECID,RAW") > 0 THEN
{&OUT} '"' + wfield_name + '"'.
ELSE DO:
DO I = 1 TO _extent:
{&OUT} '"' + wfield_name + warray_delimiter
+ STRING(I) + '"'.
IF I <> _extent THEN
{&OUT} "," SKIP.
END.
END.
IF J = NUM-ENTRIES(wfields) THEN
{&am.p;OUT} SKIP.
ELSE
{&OUT} "," SKIP.
END.

{&OUT} ")" SKIP "AS SELECT " SKIP.

DO J = 1 TO NUM-ENTRIES(wfields):
wcur-field = ENTRY(J, wfields).
FIND _field OF _file WHERE _field-name = wcur-field NO-LOCK.
wdata-type = _field._data-type.
IF _extent = 0
OR LOOKUP(_field._data-type,"RECID,RAW") > 0 THEN
{&OUT} '"' + _field-name + '"'.
ELSE DO:
IF wuse-convert
AND LOOKUP(wdata-type,
"CHARACTER,INTEGER,DECIMAL,DATE,LOGICAL")
= 0 THEN DO:
IF wdata-type = "recid" THEN wdata-type = "character".
IF wdata-type = "raw" THEN wdata-type = "character".

MESSAGE "Field: " wcur-field
" is " CAPS(_field._data-type)
" data type, which has no corresponding SQL-92 data type,"
SKIP
"the field will be represented as "
+ CAPS(wdata-type) + "."
VIEW-AS ALERT-BOX.
END.
IF wdata-type = "logical" THEN wdata-type = "integer".
DO I = 1 TO _extent:
IF wuse-convert AND wdata-type = "CHARACTER" THEN
{&OUT} "pro_element (" + '"' + _field-name + '"'
+ " ," + STRING(I) + " ," + STRING(I) + ") ".
ELSE
{&OUT} "convert ('" + wdata-type + "', "
+ "nullif( pro_element (" + '"'
+ _field-name + '"' + " ," + STRING(I)
+ " ," + STRING(I) + "),'?') ) ".
IF I <> _extent THEN {&OUT} "," SKIP.
END.
END.
IF J = NUM-ENTRIES(wfields) THEN
{&OUT} SKIP.
ELSE
{&OUT} "," SKIP.
END.
{&OUT} "FROM " + 'pub."' + _file-name + '"' + ";" SKIP(1).

END.


/*** END OF PROGRAM ***/.