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?

How to batch execute SQL queries from 4GL/ABL without using the character mode SQL Explorer Tool?

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?

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.

.     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..

The Template.java file:

import java.io.*;
import java.sql.*;
public class Template
{
    private static final String DRIVER = "com.ddtek.jdbc.openedge.OpenEdgeDriver";
    private static final String DBURL  = "jdbc:datadirect:openedge://localhost:23456;databaseName=sports2000";
    private static final String DBUSER = "yshanshi";
    private static final String DBPASS = "progress";
    private static final String SQLQRY =
    public static FileOutputStream Output;
    public static PrintStream file;
    public static void main (String args [])
    {
        try
        {
            Output = new FileOutputStream("ResultSet.txt");
            file   = new PrintStream(Output);
            Class.forName (DRIVER);
            Connection con = DriverManager.getConnection(DBURL,DBUSER,DBPASS);
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(SQLQRY);
            ResultSetMetaData rsmd     = rs.getMetaData();
            int               rsmdCols  = rsmd.getColumnCount();
            int       i     = 0;
            while (rs.next())
            {
                for ( i=1; i<=rsmdCols; i++ )
                {
                    if (i == rsmdCols)
                    {
                        file.println(rs.getString(i) + "\n");
                    }
                    else
                    {
                        file.print(rs.getString(i) + "|");
                    }
                }
            }
            rs.close();
            stmt.close();
            con.close();
        }
     catch (Exception x)
        {
            x.printStackTrace();
        }
    }
}

The generated Program.java file:

import java.io.*;
import java.sql.*;
public class Program
{
    private static final String DRIVER = "com.ddtek.jdbc.openedge.OpenEdgeDriver";
    private static final String DBURL  = "jdbc:datadirect:openedge://localhost:23456;databaseName=sports2000";
    private static final String DBUSER = "yshanshi";
    private static final String DBPASS = "progress";
    private static final String SQLQRY =
        "SELECT " + "\n" +
        "CustNum, Name, City, Country, Balance " + "\n" +
        "FROM " + "\n" +
        "PUB.Customer ";
    public static FileOutputStream Output;
    public static PrintStream file;
    public static void main (String args [])
    {
        try
        {
            Output = new FileOutputStream("ResultSet.txt");
            file   = new PrintStream(Output);
            Class.forName (DRIVER);
            Connection con = DriverManager.getConnection(DBURL,DBUSER,DBPASS);
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(SQLQRY);
            ResultSetMetaData rsmd     = rs.getMetaData();
            int               rsmdCols  = rsmd.getColumnCount();
            int       i     = 0;
            while (rs.next())
            {
                for ( i=1; i<=rsmdCols; i++ )
                {
                    if (i == rsmdCols)
                    {
                        file.println(rs.getString(i) + "\n");
                    }
                    else
                    {
                        file.print(rs.getString(i) + "|");
                    }
                }
            }
     &nb.sp;      rs.close();
            stmt.close();
            con.close();
        }
        catch (Exception x)
        {
            x.printStackTrace();
        }
    }
}

.sp;      rs.close();
            stmt.close();
            con.close();
        }
        catch (Exception x)
        {
            x.printStackTrace();
        }
    }
}.