Kbase P148042: 4GL/SQL: How to build a java program to execute an SQL query by merging the query script file with a
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
8/19/2009 |
|
Status: Verified
GOAL:
4GL/SQL: How to build a java program to execute an SQL query by merging the query script file with a java template using 4GL/ABL?
GOAL:
How to batch execute SQL queries from 4GL/ABL without using the character mode SQL Explorer Tool?
GOAL:
How to problematically merge an SQL query script file into an existing java template to produce a java program to execute the query either from the generated java program or from 4GL?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
OpenEdge Category: Language (4GL/ABL)
OpenEdge Category: SQL
FIX:
The following ABL/4GL procedure performs the following tasks:
1. Merges an SQL query script file Query.sql with an existing Template.java file to generate a Program.java which may be used to execute query.
2. Compiles the resulting Program.java to produce the Program.class.
3. Executes the Program.class which dumps the result set as a pipe "|" delimited into the ResultSet.txt file.
Note: Sample Template.java, Query.sql, Program.java and ResultSet.txt files are in the notes below. The Template.java assumes an OpenEdge 10.1x JDBC driver and uses the OpenEdge 10.1x database URL syntax. Modify the database name, host name, port number, user name, password, driver name and the database URL in the Template.java file to reflect your actual version and configuration:
/* ExecuteSQLQueryUsingJavaProgramFrom4GL.p */
DEFINE VARIABLE csqlQuery AS CHARACTER NO-UNDO.
DEFINE VARIABLE cJavaTemplate AS CHARACTER NO-UNDO.
DEFINE VARIABLE cJavaProgram AS CHARACTER NO-UNDO.
DEFINE VARIABLE cCompileCmd AS CHARACTER NO-UNDO.
DEFINE VARIABLE cExecuteQuery AS CHARACTER NO-UNDO.
DEFINE VARIABLE cLine AS CHARACTER NO-UNDO.
DEFINE STREAM iTemplate.
DEFINE STREAM iQuery.
DEFINE STREAM oProgram.
ASSIGN
csqlQuery = "Query.sql"
cJavaTemplate = "Template.java"
cJavaProgram = "Program.java"
cCompileCmd = "javac Program.java"
cExecuteQuery = "java Program".
/* Generate Program.java from the Template.java and the Query */
INPUT STREAM iTemplate FROM VALUE ( cJavaTemplate ).
OUTPUT STREAM oProgram TO VALUE ( cJavaProgram ).
REPEAT:
IMPORT STREAM iTemplate UNFORMATTED cLine.
IF INDEX (cLine, "Template") > 0 THEN
cLine = REPLACE ( cLine , "Template" , "Program" ).
IF INDEX (cLine, "private static final String SQLQRY =") > 0 THEN DO:
PUT STREAM oProgram UNFORMATTED cLine SKIP.
RUN InsertQuery.
NEXT.
END.
PUT STREAM oProgram UNFORMATTED cLine SKIP.
END.
INPUT STREAM iTemplate CLOSE.
OUTPUT STREAM oProgram CLOSE.
/* Compile and Execute the generated java program */
OS-COMMAND SILENT VALUE(cCompileCmd).
OS-COMMAND SILENT VALUE(cExecuteQuery).
PROCEDURE InsertQuery:
DEFINE VARIABLE cLineBreak AS CHARACTER INITIAL ' + ~"~\n~" +'.
DEFINE VARIABLE cIndent AS CHARACTER INITIAL ' '.
DEFINE VARIABLE cSpace AS CHARACTER INITIAL ' '.
DEFINE VARIABLE cFirstLine AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSecondLine AS CHARACTER NO-UNDO.
INPUT STREAM iQuery FROM VALUE ( cSqlQuery ).
IMPORT STREAM iQuery UNFORMATTED cFirstLine.
REPEAT:
IMPORT STREAM iQuery UNFORMATTED cSecondLine.
ASSIGN
. cLine = cFirstLine
cFirstLine = cSecondLine
cLine = TRIM(cLine)
cLine = cIndent + QUOTER(cLine + cSpace) + cLineBreak.
PUT STREAM oProgram UNFORMATTED cLine SKIP.
END.
ASSIGN
cLine = cFirstLine
cLine = TRIM(cLine)
cLine = cIndent + QUOTER(cLine + cSpace) + ";".
PUT STREAM oProgram UNFORMATTED cLine SKIP.
END PROCEDURE..