Status: Verified
GOAL:
4GL/SQL: How to generate SQL script to create SQL VIEWs that splits Array fields into multiple 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 enable Microsoft Access to access the individual elements of Array fields in the OpenEdge database?
GOAL:
How to access the Array fields of a table as individual fields using SQL VIEWs?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.1x
FIX:
The following GenerateViewsForAllTables_JDBC.p procedure generates an SQL script file to create an SQL VIEW for each table of the connected database. If the table has Array fields, its SQL VIEW will split each Array field into individual fields. 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 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 following 4GL/ABL code. 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, simply 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 an ODBC client like WinSQL, run the GenerateViewsForAllTables_WinSQL.p version in the note below:
/***************GenerateViewsForAllTables_JDBC.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 ***/
&n.bsp; 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 + "," + QUOTER(hCurrentTableFieldHandle:NAME)
cViewColumnList = cViewColumnList + "," + QUOTER(hCurrentTableFieldHandle:NAME).
ELSE DO iFieldExtentCounter = 1 TO hCurrentTableFieldHandle:EXTENT:
ASSIGN
cSelectColumnList = cSelectColumnList + "," + QUOTER(hCurrentTableFieldHandle:NAME) + "[" + STRING(iFieldExtentCounter) + "]"
cViewColumnList = cViewColumnList + "," + QUOTER(hCurrentTableFieldHandle:NAME + STRING(iFieldExtentCounter)).
END.
END.
ASSIGN
cViewColumnList = LEFT-TRIM(cViewColumnList, ",")
cSelectColumnList = LEFT-TRIM(cSelectColumnList, ",").
/* 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) - 1:
PUT UNFORMATTED " " ENTRY(iTableFieldCounter,ipcViewColumnList) "," SKIP.
END.
PUT UNFORMATTED " " ENTRY(NUM-ENTRIES(ipcViewColumnList),ipcViewColumnList) SKIP.
PUT UNFORMATTED " )" SKIP.
/*** Generate SQL script for the SELECT column list ***/
PUT UNFORMATTED "AS SELECT" SKIP.
DO iTableFieldCounter = 1 TO NUM-ENTRIES(ipcSelectColumnList) - 1:
PUT UNFORMATTED " " ENTRY(iTableFieldCounter,ipcSelectColumnList) "," SKIP.
END.
PUT UNFORMATTED " " ENTRY(NUM-ENTRIES(ipcSelectColumnList),ipcSelectColumnList) 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..