top of page
Get a Demo
Get a Free Quote

Server stored procedure that integrates with the OneSignal API for push notifications

Feb 17, 2024

2 min read

0

1

0

Let's craft a SQL Server stored procedure that integrates with the OneSignal API for push notifications. Here's a tailored solution, along with essential explanations and considerations:



CREATE PROCEDURE sp_SendOneSignalNotification @Title NVARCHAR(100), @Content NVARCHAR(255), @TargetSegments NVARCHAR(MAX) = NULL, -- Comma-separated segment names @IncludedPlayerIds NVARCHAR(MAX) = NULL -- Comma-separated player IDs AS BEGIN DECLARE @API_URL NVARCHAR(255) = 'https://onesignal.com/api/v1/notifications'; DECLARE @API_KEY NVARCHAR(100) = 'YOUR_ONESIGNAL_REST_API_KEY'; -- Replace with your key DECLARE @JSON NVARCHAR(MAX); -- Construct JSON payload (Refer to OneSignal API for more options) SET @JSON = N'{ "app_id": "YOUR_ONESIGNAL_APP_ID", -- Replace with your app ID "headings": {"en": @Title}, "contents": {"en": @Content} }'; -- Targeting Logic IF @TargetSegments IS NOT NULL SET @JSON = @JSON + N',"included_segments": [' + @TargetSegments + ']'; IF @IncludedPlayerIds IS NOT NULL SET @JSON = @JSON + N',"include_player_ids": [' + @IncludedPlayerIds + ']'; -- HTTP Request using OLE Automation DECLARE @Object INT; DECLARE @ResponseText NVARCHAR(MAX); EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT; EXEC sp_OAMethod @Object, 'open', NULL, 'POST', @API_URL, 'false'; EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type', 'application/json;charset=UTF-8'; EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Authorization', 'Basic ' + @API_KEY; EXEC sp_OAMethod @Object, 'send', NULL, @JSON; EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT; -- Handle response for potential error logging (Customize as needed) SELECT @ResponseText AS Response; EXEC sp_OADestroy @Object; END

Key Points

  1. Dependencies: The stored procedure depends on OLE Automation procedures (sp_OACreate, sp_OAMethod, sp_OADestroy). It's essential to ensure these are enabled on your SQL Server instance.

  2. Security:

  • Never store your OneSignal API key directly in the stored procedure. Instead, explore secure means like:

  • SQL Server's built-in encryption mechanisms

  • Azure Key Vault integration

  • Environment variables (if applicable)

  1. API Customization: The JSON payload is a basic structure. Tailor it to your needs based on the rich options provided by the OneSignal REST API (https://documentation.onesignal.com/reference/create-notification).

  2. Error Handling:  The sample provides a simple response but implement robust error handling,  logging, and potential retries on failures.


Usage Example



EXEC sp_SendOneSignalNotification @Title = 'Important Announcement', @Content = 'Stay updated with the latest news!', @TargetSegments = 'Active Users, Registered Members';

Caution

  • OLE Automation within SQL Server can have performance and security implications. Evaluate alternative approaches carefully (such as external scripts or services) if you have high-volume or security-sensitive notification requirements.


Comments

Share Your ThoughtsBe the first to write a comment.
bg.8b803c47.png

READ OUR LATEST ARTICLES

Post

Welcome to the Intertoons Blog! Discover expert insights, the latest trends, and valuable tips on eCommerce, web development, and digital solutions. Stay informed and ahead in the digital world with our in-depth articles and guides!

5/5 based on 63 reviews | GDPR Compliant

bottom of page