forstie/send_sms.sql

## send_sms.sql
--
-- 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 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;
	--
	-- 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 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;