Run SQL statements from the command line
Here's a quick way to evaluate information, such as mathematical formulas or file content, etc.
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.