Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
Fun with Embedded SQL
has no ratings.
Published: 02 Nov 2012
Revised: 23 Jan 2013 - 1703 days ago
Last viewed on: 21 Sep 2017 (3359 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Fun with Embedded SQL Published by: Bob Cozzi on 02 Nov 2012 view comments(3)

Here's some potpourri of using Embedded SQL in RPG IV that I've put together.

 

 /free                                                               
    exec SQL set option commit = *none;                              
                                                                     
     // Convert 1 or more characters to hex                          
         exec SQL SET :myHex = hex( :Symbol );                       
     // Get end-of-month date                                        
         exec SQL SET :endOfMonth = last_day( :today);               
     // Get the name of the month                                    
         exec SQL set :szMonth = monthName(:today);                  
     // Encrypt using RC2                                            
         exec SQL SET ENCRYPTION PASSWORD = :pwd;                    
         name = 'Robert Cozzi, Jr.';                                 
         exec SQL set :enc_name = encrypt_rc2( :name );              
     // Decrypt using RC2                                            
         exec SQL set :decrypted_name = DECRYPT_CHAR( :enc_Name);    
     // Find next Friday's date                                      
         exec SQL set :nextFriday = next_day(:today ,'FRIDAY');      
     // Convert all blanks in szURL with plus signs.                 
         szURL = 'www.midrangeNews.com/find?kwd=IBM i';
         exec SQL set :szURL = replace(:szURL , ' ' , '+');  

 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: Fun with Embedded SQL
Posted: 4 years 10 months 5 days 17 hours 21 minutes ago

Here's some I use commonly:

SELECT   SYSTEM_USER        AS USER_GROUP
        ,SESSION_USER       AS USER_ID
        ,CURRENT SERVER     AS SERVER
        ,CURRENT SCHEMA     AS CUR_LIB
        ,CURRENT PATH       AS LIB_LIST
        ,CURRENT TIMEZONE   AS UTC_DIFF
        ,CURRENT DATE                                 AS TODAY
        ,YEAR(CURRENT DATE)                           AS YEAR
        ,MONTH(CURRENT DATE)                          AS MONTH
        ,MONTHNAME(CURRENT DATE)                      AS MONTH_NAME
        ,WEEK(CURRENT DATE)                           AS WEEK
        ,DAYOFYEAR(CURRENT DATE)                      AS DAY_OF_YEAR
        ,DAYOFWEEK(CURRENT DATE)                      AS DAY_OF_WEEK
        ,DAYNAME  (CURRENT DATE)                      AS DAY_NAME
        ,LAST_DAY (CURRENT DATE - 1 MONTH) + 1 DAY    AS MONTH_BEG
        ,LAST_DAY (CURRENT DATE)                      AS MONTH_END
        ,DATE(NEXT_DAY(CURRENT DATE - 8 DAYS, 'FRI')) AS LAST_FRI
        ,DATE(NEXT_DAY(CURRENT DATE - 8 DAYS, 'SUN')) AS LAST_SUN
        ,DATE(NEXT_DAY(CURRENT DATE, 'SAT'))          AS NEXT_SAT
        ,DATE(CURRENT DATE) + 60 DAYS                 AS PLUS_60
        ,MONTHS_BETWEEN('2011-07-27',CURRENT DATE)    AS MONTH_BETW  -- added in 6.1
FROM     SYSIBM.SYSDUMMY1

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Fun with Embedded SQL
Posted: 4 years 10 months 5 days 12 hours 31 minutes ago

Cool Steve. I think you can use the SQL SET syntax I showed for any of the things you specified, so you avoid the SELECT statement.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Fun with Embedded SQL
Posted: 4 years 10 months 2 days 23 hours 16 minutes ago

I probably would have used the SELECT from SYSDUMMY1 too, though I like the idea of SET -- simpler.

You could extend either method to gain access to other scalar functions, like the trig functions and logarithms. Not that this would be better than using MI built-ins or C libraries, just another way to do it.