Status: Verified
GOAL:
4GL/SQL: How to generate SQL script to create SQL VIEWs that split Array fields into individual fields using 4GL?
GOAL:
How to programmatically generate SQL script files to create SQL VIEWs for all the tables of the connected database using 4GL?
GOAL:
How to access the Array fields of a table as individual fields using SQL VIEWs?
GOAL:
How to use the PRO_ELEMENT function to access individual elements of array fields?
GOAL:
How to enable Microsoft Access to to read the individual elements of the array field rather than the whole field as a semi-colon separated list.
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.1x
OpenEdge Category: Language (4GL/ABL)
FIX:
The following GenerateViewsForAllTables_JDBC91X.p procedure generates an SQL script file to create an SQL VIEW for each table of the connected database.
If a table has Array fields, its SQL VIEW will split each Array field into individual fields using the PRO_ELEMENT function. For example, if the the field myArray is an array of three elements, the the VIEW will split this field into the fields myArray1, myArray2 and myArray3. This enables Microsoft Access to read the individual elements of the array field rather than reading the whole array as a semi-colon separated list.
To generate these SQL VIEW creating script files, simply connect to the database and run the GenerateViewsForAllTables_JDBC91X.p proecedure. Notice that for each user data table in the database, an SQL script file named CreateViewFor<TableName>.sql is created. Thus, the SQL script file CreateViewForCustomer.sql is created for the Customer table, and the CreateViewForSalerep.sql is created for the Salesrep table, etc.
To create the SQL VIEW for any table, execute the SQL script generated for it by this 4GL/ABL procedure. Notice that executing the CreateViewForCustomer.sql script creates an SQL VIEW named "OwnerName"."CustomerView" and executing the CreateViewForSalerep.sql script creates an SQL VIEW named "OwnerName"."SalerepView", etc. where OwnerName is the VIEW owner as set in the 4GL/ABL procedure.
The resulting SQL script files may be executed using any JDBC client like the Progress SQL Explorer Tool. To generate SQL script syntax to run using the WinSQL ODBC client, run the GenerateViewsForAllTables_WinSQL91X.p version in the note below:
/***************GenerateViewsForAllTables_JDBC91X.p************/
DEFINE VARIABLE cSQLViewOwnerName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSQLViewName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cViewColumnList AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSelectColumnList AS CHARACTER NO-UNDO.
DEFINE VARIABLE hTableBufferHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE iTableFieldCounter AS INTEGER NO-UNDO.
DEFINE VARIABLE hCurrentTableFieldHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE iFieldExtentCounter AS INTEGER NO-UNDO.
ASSIGN
cSQLViewOwnerName = "yshanshi".
/********Loop through all tables********/
FOR EACH _File NO-LOCK WHERE _Tbl-Type = "T":
/*** Construct the SQL VIEW name for the curret table ***/
ASSIGN
cTableName = _File-Name
cViewColumnList = ""
cSelectColumnList = ""
cSQLViewName = QUOTER (cSQLViewOwnerName) + "." + QUOTER(cTableName + "View").
/*** .Construct the current SELECT list and VIEW column list ***/
CREATE BUFFER hTableBufferHandle FOR TABLE cTableName.
DO iTableFieldCounter = 1 TO hTableBufferHandle:NUM-FIELDS:
hCurrentTableFieldHandle = hTableBufferHandle:BUFFER-FIELD(iTableFieldCounter).
IF hCurrentTableFieldHandle:EXTENT = 0 THEN
ASSIGN
cSelectColumnList = cSelectColumnList + CHR(1) + QUOTER(hCurrentTableFieldHandle:NAME)
cViewColumnList = cViewColumnList + CHR(1) + QUOTER(hCurrentTableFieldHandle:NAME).
ELSE DO iFieldExtentCounter = 1 TO hCurrentTableFieldHandle:EXTENT:
ASSIGN
cSelectColumnList = cSelectColumnList + CHR(1) + "PRO_ELEMENT(" + QUOTER(hCurrentTableFieldHandle:NAME) + "," + STRING (iFieldExtentCounter) + "," + "1)".
cViewColumnList = cViewColumnList + CHR(1) + QUOTER(hCurrentTableFieldHandle:NAME + STRING(iFieldExtentCounter)).
END.
END.
ASSIGN
cViewColumnList = LEFT-TRIM(cViewColumnList, CHR(1))
cSelectColumnList = LEFT-TRIM(cSelectColumnList, CHR(1)).
/* Build the SQL View for the current table */
RUN BuildSQLView(INPUT cViewColumnList, INPUT cSelectColumnList).
END.
PROCEDURE BuildSQLView:
DEFINE INPUT PARAMETER ipcViewColumnList AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER ipcSelectColumnList AS CHARACTER NO-UNDO.
/*** Generate SQL script for the DROP and CREATE VIEW statements ***/
OUTPUT TO VALUE("CreateViewFor" + cTableName + ".sql").
PUT UNFORMATTED "DROP VIEW " cSQLViewName ";" SKIP.
PUT UNFORMATTED "COMMIT WORK;" SKIP.
PUT UNFORMATTED "CREATE VIEW " cSQLViewName SKIP.
/*** Generate SQL script for the VIEW column list ***/
PUT UNFORMATTED " (" SKIP.
DO iTableFieldCounter = 1 TO NUM-ENTRIES(ipcViewColumnList, CHR(1)) - 1:
PUT UNFORMATTED " " ENTRY(iTableFieldCounter,ipcViewColumnList, CHR(1)) "," SKIP.
END.
PUT UNFORMATTED " " ENTRY(NUM-ENTRIES(ipcViewColumnList, CHR(1)),ipcViewColumnList, CHR(1)) SKIP.
PUT UNFORMATTED " )" SKIP.
/*** Generate SQL script for the SELECT column list ***/
PUT UNFORMATTED "AS SELECT" SKIP.
DO iTableFieldCounter = 1 TO NUM-ENTRIES(ipcSelectColumnList, CHR(1)) - 1:
PUT UNFORMATTED " " ENTRY(iTableFieldCounter,ipcSelectColumnList, CHR(1)) "," SKIP.
END.
PUT UNFORMATTED " " ENTRY(NUM-ENTRIES(ipcSelectColumnList, CHR(1)),ipcSelectColumnList, CHR(1)) SKIP.
/*** Generate SQL script for the FROM phrase ***/
PUT UNFORMATTED "FROM" SKIP.
PUT UNFORMATTED "PUB." QUOTER(cTableName) ".;" SKIP.
PUT UNFORMATTED "COMMIT WORK;".
OUTPUT CLOSE.
END..