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.
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 , ' ' , '+');
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
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.
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.