Run SQL statements from the command line

• Eitan Rosenberg
• Published: 23 Dec 2003

Some times you want to have a quick way to evaluate information, such as mathematical formulas or file content. If you are familiar with SQL, you probably know that you can use it. But did you know that to use SQL you need to start SQL Interactive Session? The utility I wrote (which is very basic) demonstrates a way to get a single field result from an SQL query from the command line.

Usage:
Enter the SQL statement. If the result expected is numeric, you'll need to warp it with char() or digits(). For example:

- SELECT digits(COUNT(*)) FROM PGMREF
- SELECT CHAR(COUNT(*)) FROM PGMREF
- SELECT CHAR(sin(10)) FROM <some single record file>
- SELECT CHAR(10 + 20) FROM <some single record file>

The program evaluates the SQL statement and present the result in a pop-up window.

The result shown is the first field in the first line of the result.

If the SQL statement does not show any result check the job log.

 
SQL Eval command
---------------
CMD        PROMPT('SQL calculator & evaluator')
PARM       KWD(QRYSTR) TYPE(*CHAR) LEN(256) PROMPT('SQL +
            Statement')
            
SQL Eval command processing program
----------------------------------
      * CRTSQLRPGI parameters
      *PARMS OBJTYPE(*PGM) DLYPRP(*YES) COMMIT(*NONE)
      *PARMS CLOSQLCSR(*ENDMOD) DBGVIEW(*SOURCE)
      *
     H indent('| ')
     H option(*srcstmt:*nodebugio)
      *
     D QryStr          s            256a
     D BUFFER          s            512a
      *
     D MessageTxt      s           1024a
     D MessageLen      s             10i 0
      *
      *=====================================================================
      * Exception message feed back
     D ApiErrDs        ds                   inz
00000D  EXMbpr                       10i 0 inz(%len(ApiErrDs))                    byte prvd
00000D  EXMbav                       10i 0                                        byte avbl
00000D  EXMsid                        7a                                          excpt id
00000D  EXMrsv                        1a                                          rsrvd
00000D  EXMdta                      256a                                          excpt data
      *=====================================================================
      * Display Long Text API
     DQUILNGTX         pr                  extpgm('QUILNGTX')
     D MessageTxt                   512a   const
     D MessageLen                    10i 0 const
     D MessageIde                     7a   const
     D MessageFil                    20a   const
     D ApiErr                              like(ApiErrDs)
      *=====================================================================
      *
     C     *entry        plist
     C                   parm                    QryStr
      *
      * Prepare and execute the SQL Statement
      *
     C/EXEC SQL
     C+ SET TRANSACTION ISOLATION LEVEL NO COMMIT
     C/END-EXEC
      *
     C/EXEC SQL
     C+ DECLARE CRSR02 SCROLL CURSOR FOR STMT02
     C/END-EXEC
      *
     C/EXEC SQL
     C+ CLOSE CRSR02
     C/END-EXEC
      *
     C/EXEC SQL
     C+ PREPARE STMT02 FROM :QryStr
     C/END-EXEC
      *
     C/EXEC SQL
     C+ OPEN CRSR02
     C/END-EXEC
      *
  01 C/EXEC SQL FETCH NEXT FROM CRSR02 INTO :BUFFER
  01 C/END-EXEC
  01  *
     C/EXEC SQL
     C+ CLOSE CRSR02
     C/END-EXEC
      *
     C                   eval      *inlr = *on
      *
      * Edit and show the result
      *
     C                   Eval      MessageTxt =
     C                               'The result of ' +
     C                               %trim(QryStr) + ' is ' +
     C                               %trim(BUFFER) + '.'
      *
     C                   Eval      MessageLen = %len(%trim(MessageTxt))
      *
     C                   callp     QUILNGTX(
     C                               MessageTxt :
     C                               MessageLen :
     C                               *blank     :
     C                               *blank     :
     C                               ApiErrDs)
      *
      *=====================================================================

==================================
MORE INFORMATION ON THIS TOPIC
==================================

The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.