Last active
December 26, 2023 20:15
-
-
Save forstie/49bff70e5bc82864d04eaa2471960f31 to your computer and use it in GitHub Desktop.
The idea... open up SQL to sending text (SMS) messages. Surely this idea is well within our grasp. To capture the idea fully implies that the complexity needs to be encapsulated.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- Subject: The idea... open up SQL to sending text (SMS) messages. Surely this idea is well within our grasp. To capture the idea fully implies that the complexity needs to be encapsulated. | |
-- Author: Scott Forstie | |
-- Date : May, 2022 | |
-- Features Used : This Gist uses QSYS2.HTTP_GET, QSYS2.HTTP_POST, SQL PL, and wrap | |
-- | |
-- Step 1: | |
-- Decide on an SMS provider service to use. | |
-- This example is based upon Twilio, but there are many other services (TextMagic, Vonage, and others) that provide similar support. | |
-- | |
-- Note that Twilio support for Short Message Service (SMS), the text length maximum is 1600. | |
-- The recommendation is to use texts with length of 320 or less, for the best delivery experience. | |
-- | |
-- Step 2: | |
-- Confirm that you're using IBM i 7.3 or higher | |
-- | |
-- Step 3: | |
-- Confirm that the Db2 for i PTF Group level is at a reasonable level. | |
-- | |
-- Step 4: | |
-- Establish a Trust Store. | |
-- IBM i has provided many detailed instructions and even an example script to setup a Trust Store. | |
-- https://www.ibm.com/docs/en/i/7.5?topic=programming-http-functions-overview | |
-- | |
stop; | |
-- | |
-- Step 5: | |
-- Confirm that the Trust Store works | |
-- | |
values QSYS2.http_get( | |
URL => 'https://www.ibm.com/support/pages/sites/default/files/inline-files/xmldoc.xml', | |
OPTIONS => '{"sslCertificateStoreFile":"/home/javaTrustStore/fromJava.KDB"}'); | |
-- Note: replace the IFS path as needed if you use a different path | |
stop; | |
-- | |
-- Step 6: | |
-- Constuct an awesome scalar function | |
-- | |
-- The design approach for this function is that one account will service several to many "texters". | |
-- Therefore, the UDF does a lookup on who is the intended receiver of the text message, and uses the corresponding cell phone. | |
-- If someone attempts to call this service and they're not registered as an expected user to receive a text, the caller receives a failure. | |
-- | |
-- | |
-- Note: This script has been adjusted to not reveal Scott and Tim's cell phone numbers (sorry!). | |
-- Also, Tim's Twilio account details are safely hidden. | |
-- Adjust the script to include your cell phone and account details. | |
-- | |
create or replace function coolstuff.send_sms ( | |
text varchar(1600) ccsid 1208, | |
to_cell varchar(10) for sbcs data default user | |
) | |
returns clob(2m) ccsid 1208 | |
not deterministic | |
no external action | |
modifies sql data | |
not fenced | |
set option COMMIT = *NONE | |
begin | |
declare LOCAL_SQLCODE integer; | |
declare LOCAL_SQLSTATE char(5) for sbcs data; | |
declare not_found condition for '02000'; | |
declare at_end integer default 0; | |
declare v_message_text varchar(500) for sbcs data; | |
declare result_value clob(2m) ccsid 1208; | |
declare to_cell_number varchar(11) ccsid 1208; | |
set to_cell_number = | |
case to_cell | |
when 'SCOTTF' then '15072699999' | |
when 'TIMMR' then '15072509999' | |
else null | |
end; | |
if (to_cell_number is null) then | |
set v_message_text = 'coolstuff.send_sms() failed because user: ' concat to_cell concat | |
'''s cell phone is not registered within this function'; | |
signal sqlstate 'QSMS1' | |
set message_text = v_message_text; | |
return -1; | |
end if; | |
values QSYS2.HTTP_POST( | |
'https://api.twilio.com/2010-04-01/Accounts/AC803409999999999999999999999999/Messages.json', | |
'To=+' concat to_cell_number concat | |
'&From=+18124585123&MessagingServiceSid=MGd999999999999999999999999999999&Body=' concat | |
text, | |
'{"basicAuth":"AC803409999999999999999999999999,399999999999999999999999999999","header":"content-type,application/x-www-form-urlencoded","sslCertificateStoreFile":"/home/javaTrustStore/fromJava.KDB"}' | |
) into result_value; | |
return result_value; | |
end; | |
stop; | |
-- | |
-- Step 7: | |
-- Confirm that the send_sms service is working | |
-- | |
values coolstuff.send_sms('Twilio suggests that SMS text length be <= 320 for the most reliable and timely delivery'); | |
stop; | |
-- | |
-- Step 8: | |
-- Confirm that the send_sms service will fail when the target cell is unknown | |
-- | |
values coolstuff.send_sms(TEXT => 'Twilio suggests that SMS text length be <= 320 for the most reliable and timely delivery', TO_CELL => 'JOEUSER'); | |
stop; | |
-- | |
-- | |
-- Step 9: | |
-- Bonus step... protect your Twilio identity and authentication detail, and all the cell phone numbers, from discovery via Db2 for i obfuscation. | |
-- The WRAP built-in function and CREATE_WRAPPED procedure will obfuscate the SQL PL. | |
-- | |
-- Insider's tip: Once the SQL is fully formed and working, goto a different window/session/etc and global replace the single quotes with two single quotes | |
-- | |
values wrap('create or replace function coolstuff.send_sms ( | |
text varchar(1600) ccsid 1208, | |
to_cell varchar(10) for sbcs data default user | |
) | |
returns clob(2m) ccsid 1208 | |
not deterministic | |
no external action | |
modifies sql data | |
not fenced | |
set option COMMIT = *NONE | |
begin | |
declare LOCAL_SQLCODE integer; | |
declare LOCAL_SQLSTATE char(5) for sbcs data; | |
declare not_found condition for ''02000''; | |
declare at_end integer default 0; | |
declare v_message_text varchar(500) for sbcs data; | |
declare result_value clob(2m) ccsid 1208; | |
declare to_cell_number varchar(11) ccsid 1208; | |
set to_cell_number = | |
case to_cell | |
when ''SCOTTF'' then ''15076666666'' | |
when ''TIMMR'' then ''15076666666'' | |
else null | |
end; | |
if (to_cell_number is null) then | |
set v_message_text = ''coolstuff.send_sms() failed because user: '' concat to_cell concat | |
''''''s cell phone is not registered within this function''; | |
signal sqlstate ''QSMS1'' | |
set message_text = v_message_text; | |
return -1; | |
end if; | |
values QSYS2.HTTP_POST( | |
''https://api.twilio.com/2010-04-01/Accounts/AC803409999999999999999999999999/Messages.json'', | |
''To=+'' concat to_cell_number concat | |
''&From=+18124585123&MessagingServiceSid=MGd999999999999999999999999999999&Body='' concat | |
text, | |
''{"basicAuth":"AC803409999999999999999999999999,399999999999999999999999999999","header":"content-type,application/x-www-form-urlencoded","sslCertificateStoreFile":"/home/javaTrustStore/fromJava.KDB"}'' | |
) into result_value; | |
return result_value; | |
end'); | |
stop; | |
-- | |
-- Produces code that is obfuscated, but does exactly the same thing | |
-- Note: copy and paste the SQL statement | |
-- | |
CREATE OR REPLACE FUNCTION COOLSTUFF . SEND_SMS ( | |
TEXT VARCHAR ( 1600 ) CCSID 1208 , | |
TO_CELL VARCHAR ( 10 ) FOR SBCS DATA DEFAULT USER | |
) | |
WRAPPED QSQ07040 aacx99999999999999999999999999e2_1_F3uve4ANO6ZkXFtWbIcjCVsEsLCHseLJvywxuaIrIcgsPnFqIoiclXn5Y_NAVnsCmHt7NTQGml:_6SY5FfM2x84eCNOSWz19c0JwM7oVlldk9:dYu29zb5FhJnfcaONd4Tqc2oiiJJzJK6:MrOcsW6M8:YfXB3VA1r5Iyb_Nt0_0:r4JiNlWEnNTkB2zpR8SDV9z1CwADMWi82zzLNFaAX1tGj5DdIXf6_cu5srT7uVbwb0vaGXO4_ECUYi_wFV0xx3guxmYjYPN4Up:rLCBseeaw46x2FMZLy0OBl4b0gu3IITnYjzercV5PYNUllst:lhQX26kzW9uNKoDyjTUYg3qmo2Pfz80zNLoKgklnkSa0_RzI32onK8eZToo3y2NGGA9wf55M1a8slCT1spNI:b7bCS0UGu6CNmF1VyeMc5FG7hVnmoEA9CQE6OeZNvJJT90oDjuAzCLafouY4y7asfiE:dtUnHgZSgqyAjdO3dobOfnr4PpsNoIMQ6NylVZWQ9fqcZVctlzrzoguaPJsEtNGyaGPZLOl7GMWuTpXKqxwsbtQtJayNB5jlA0ppTLdp4uNCDNQpWIfnzZ447kLAx5h9xygWgD:g78f5g4AAsj8cIQ_acSb3ZVx:kpgMU06rhmns2vx:NYGG0jzmpMwmhIM6K0eI3G8tAcqrI7:SM60tGz9aHLe5BUOmzsRNhnTh8WhPogZoAGHZ5RMQrFRddTHUZ5dXYo7Mp51Mr5nd1dwco:dxATmfeXAnkZ8iVVcDFbhvlPFRPE1DbzMU:_JJs5TsYXlkcj3bHyKcSaVcnfj3noc0PMXxD7Xea332lxuRnvqK4ebY8BI8JF8lUogRrsxvP3HeZBm8q0YDCTlrkPux0d043kOWr6IIQMoT4cVq:W51diPiMQOKiB1aJClKdLsw7JBwzr9EbyJSH4OJc6c8tDp9onDqKgxJxR3UUed8yikFDHOzzhb6hwXCOAWO8Y3YbT83kacaZS:wMwjxSz9_n99ITDgAmrVTXe8KeWLF_ABItqNLYjin45RGR2nsunp:nLuWTsqa3Y9eM9vc1cVKZAe:0C_bvcCs:dOdFhqSLj:c1jVlYUuBP6C7hS6PX4F2mkm3Rpkx4h_XhrZHFlyN3nvuyNyFf:SSjthOdv9ZLKlJDoi79PMucn2TM:RXxHovQtf9yWYv_4HHxKGolOyaQaLCkcM8tgeZLsuZYCUZCV5AKOWZht1IViic5eyFXKkAi5T2F2kF1MNyswtlYKHH76J0Fvjo8Xq7crFYqWrTKyT8wA0OPTLqWFNM7mc:3gs_L4xZb97xDEJse6fjvlH84TKV:uTt2EvZtkzMiN8wKulN2slvxWYB9ieh3COf_LsIt327iAbPV7oo8mVBzv173SFEqwatnGL6o64WomTAUE2u1pUKn7Ej2l8rOeoCY2:B05nzZL2yCSzEaTTs7lVyoUDOPIWRM6KtrkpfTKxWPCvp9uxAOFQ1I91kEKQkVujvGTvTyPhUG0m201yw3OLtyK1GBdQCNrymLQEAya:hSY7F5gyTI7N_IBTheEIwVeFG:9xtES4NwflV7uj1t2Olb:eGr2Amcj78TjdYrKvJmW3Rs0uRBKE4jNnGodx0R_f9ORBZ:vrKVopj0uwt4xgRI:iWCv_umxShGiXv07IROd2V9eLb45jAMp_y_UdxeMs8YIWKz_C8F3CGrZxu6jvdfy2W8AN2DPYxUIkA8Z:KTUD8fTxo_c2Gaa; | |
stop; | |
Hi
I am on 7.3
But level should be okay
ons. 20. dec. 2023 kl. 14.29 skrev Scott Forstie ***@***.***>:
… ***@***.**** commented on this gist.
------------------------------
Hi Morten.
What IBM i operating system level are you using?
—
Reply to this email directly, view it on GitHub
<https://gist.github.com/forstie/49bff70e5bc82864d04eaa2471960f31#gistcomment-4801562>
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BE2EGNJ56RUXXRYQNYTSXRDYKLR35BFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFQKSXMYLMOVS2I5DSOVS2I3TBNVS3W5DIOJSWCZC7OBQXE5DJMNUXAYLOORPWCY3UNF3GS5DZVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVEYTCNRVGY4DONZVU52HE2LHM5SXFJTDOJSWC5DF>
.
You are receiving this email because you commented on the thread.
Triage notifications on the go with GitHub Mobile for iOS
<https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675>
or Android
<https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>
.
Use this command to see the level of the database PTF Group that is INSTALLED:
WRKPTFGRP PTFGRP(SF99703)
Hi Scott
My machine is on level 33
Regards
Morten
ons. 20. dec. 2023 kl. 17.14 skrev Scott Forstie ***@***.***>:
… ***@***.**** commented on this gist.
------------------------------
Use this command to see the level of the database PTF Group that is
INSTALLED:
WRKPTFGRP PTFGRP(SF99703)
—
Reply to this email directly, view it on GitHub
<https://gist.github.com/forstie/49bff70e5bc82864d04eaa2471960f31#gistcomment-4801772>
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/BE2EGNK7EWH66XRL3V25IYTYKMFG3BFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFQKSXMYLMOVS2I5DSOVS2I3TBNVS3W5DIOJSWCZC7OBQXE5DJMNUXAYLOORPWCY3UNF3GS5DZVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVEYTCNRVGY4DONZVU52HE2LHM5SXFJTDOJSWC5DF>
.
You are receiving this email because you commented on the thread.
Triage notifications on the go with GitHub Mobile for iOS
<https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675>
or Android
<https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>
.
Perhaps QSYS2 is not in your library list.
Either add QSYS2 to your library list, or precede the calls with QSYS2.
values QSYS2.http_get(
URL => 'https://www.ibm.com/support/pages/sites/default/files/inline-files/xmldoc.xml',
OPTIONS => '{"sslCertificateStoreFile":"/home/javaTrustStore/fromJava.KDB"}');
<!--
/* Font Definitions */
@font-face
{font-family:Courier;
panose-1:2 7 4 9 2 2 5 2 4 4;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:3.0cm 2.0cm 3.0cm 2.0cm;}
div.WordSection1
{page:WordSection1;}
-->Hi Scott My statement is library-spcific. And the error I get is that HTTP.GET is not found in QSYS2. My Holidays har started now. So I would like to get back to you next year😁😊🤣 Happy Holidays Regards Morten Fra: Scott ForstieSendt: 21. december 2023 16:03Til: forstieCc: CommentEmne: Re: ***@***.*** commented on this gist.Perhaps QSYS2 is not in your library list.Either add QSYS2 to your library list, or precede the calls with QSYS2.values QSYS2.http_get(URL => 'https://www.ibm.com/support/pages/sites/default/files/inline-files/xmldoc.xml',OPTIONS => '{"sslCertificateStoreFile":"/home/javaTrustStore/fromJava.KDB"}');—Reply to this email directly, view it on GitHub or unsubscribe.You are receiving this email because you commented on the thread.Triage notifications on the go with GitHub Mobile for iOS or Android.
Enjoy your holiday!
I think that you should open an IBM i Support ticket. They'll be able to diagnose what's wrong.
BR... Scott
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi Morten.
What IBM i operating system level are you using?
New HTTP functions based in QSYS2 were added in 2021 to IBM i 7.3. 7.4, and 7.5.
https://www.ibm.com/support/pages/node/6486889
IBM i 7.5 - Base
IBM i 7.4 - SF99704 Level 15
IBM i 7.3 - SF99703 Level 26