SMS

A component for sending and receiving SMS messages.

A valid Customer Account must be registered with the SMS service provider.

SQL

SQL Call: Get Actions (mandatory)

Statement to extract actions to perform.

May modify database: No

Parameters

@Action string
Will be set to NULL

Resultset: Username and password (optional)

Will override the default credentials from the system settings. You should only use this table during debugging.
Table count: repeated zero or one time
Row count: exactly one row
Columns
Password mandatory string
Password as service provider
Username mandatory string
Username at service provider

Resultset: SendMessage (optional)

A list of message to be sent.
Table count: repeated zero or more times
Row count: zero or more rows
Columns
Action mandatory string
Must be set to "SendMessage".
OutgoingMessageId mandatory string
A string identifying the message uniquely among all messages sent using the same Customer Account.
OriginatorType mandatory string
Specifes the originator type used.
Possible value Description
Alpha A maximum of 11 alphanumeric characters. Only the characters A-Z, a-z, 0-9 and åäöÅÄÖ are supported.

The recipient can not respond to a message sent using this originator type.
Numeric A numeric value representing a phone number on international format without leading "00".

Example: The swedish phone number 0700-123123 should be written as 46700123123.
Originator mandatory string
Sets the string indicating the origin of the message. See the OriginatorType-column for details.
Destination mandatory string
The phone number of the message recipient. The number should be on the form <Country Code><Area Code><Subscriber Number> with only digits and no spacing.

Example: The Swedish phone number 0700-123123 should be written as 46700123123.

For legacy reasons you may optionally prefix the phone number with 00 (for example 0046700123123) but this is not required.
Content mandatory string
Sets the contents of the message.

The length of a single SMS message is limited to 140 bytes, corresponding to 160 characters using GSM-encoding (7 bits/character) or 70 characters using UCS-2-encoding (16 bits/character).

The component will automatically choose the most efficient encoding based on the content of the message.

Messages with content lengths exceeding these limits are referred to as "concatenated SMS" and are transmitted over the phone networks split into several single SMS messages (referred to as "segments") that are reassembled at the receiving terminal. Each such segment includes a short header to aid in reassembly, thus limiting each segment in a concatenated SMS to 153 characters using GSM-encoding, or 67 characters using UCS-2-encoding.

The maximum number of segments supported, and thus the maximum message length allowed, depends on which SMS supplier is used and may vary based on your subscription.

Resultset: GetOutgoingMessageStatus

Fetches the current status information of the specified sent messages.
Table count: repeated one or more times
Row count: zero or more rows
Columns
Action mandatory string
Must be set to "GetOutgoingMessageStatus".
OutgoingMessageId mandatory string
Your primary key. This id value will identify your message in the temporary table with the results.
SentMessageReference mandatory binary
The message reference you received while sending the message.

SQL Call: SendMessage result

Supplies the result of a "SendMessage" action.

May modify database: Yes

Parameters

@Action string
Will be set to "SendMessage"

Resultset: #OutgoingMessageResult

A temporary table containing one row for each message that was supposed to be sent.
Table count:
Row count:
Columns
OutgoingMessageId mandatory string
The value of the "OutgoingMessageId" column in the resultset that supplied the messages to be sent.
ErrorCode mandatory int
Is set to 0 when all went fine. Any other value indicates a problem.
Possible value Description
0 ErrorMessage: NoError
1 ErrorMessage: ActionValidationError
Not a valid Action, no request to SMS supplier
5 ErrorMessage: ErrorMessageFromSupplier
SMS supplier returned with error
6 ErrorMessage: ExceptionDuringCommunicationWithSupplier
Error during Communication with SMS supplier or when decoding message.
7 ErrorMessage: MessageReferenceNotFound
Reference sent to SMS supplier not found at supplier or in Softadmins Admin tables.
9 ErrorMessage: InternalErrorCode
Not handled error
ErrorMessage mandatory string
May contain an error message with additional details.
SentMessageReference mandatory binary
A message reference that you can use with the GetOutgoingMessageStatus action to determine the delivery status of the message.

SQL Call: GetOutgoingMessageStatus result

Supplies the result of a "GetOutgoingMessageStatus" action.

May modify database: Yes

Parameters

@Action string
Will be set to "GetOutgoingMessageStatus".

Resultset: #OutgoingMessageStatusResult

A temporary table containing one row for each valid OutgoingMessageId inquired for.
Table count:
Row count:
Columns
OutgoingMessageId mandatory string
The OutgoingMessageId inquired for.
Status mandatory string
The current status of the message.
Possible value Description
Buffered The message has been delivered to the recipient's phone operator.
Delivered The message has been delivered to the destination terminal. Most delivered messages never reach this status (because of operator limitations) and remain as "Buffered".
Failed Message delivery to the phone operator failed.
null Unable to fetch status. See ErrorCode and ErrorMessage for more details.
Queued The message has been received by the SMS supplier and is awaiting transfer to the recipient's phone operator. You should ask again later to get its final status.
Unknown The message was sent but it is unknown if it was delivered.
ErrorCode mandatory int
Is set to 0 when all went fine. Any other value indicates a problem.
Possible value Description
0 No error.
1 The action was rejected by the component.
5 Error from supplier.
6 Communication error. Check the system's error log.
7 Invalid message reference.
9 Internal error. Check the system's error log.
ErrorMessage mandatory string
Contains an error message if a problem was encountered.

SQL Call: Validate parameters

Allows you to validate the parameters supplied by the user before any other SQL is run in the component. This call is only made if the component has visible parameters, the SQL is a stored procedure, and Validate parameters is checked.

May modify database: No

Parameters

@Force bit
Set to 1 if the last call to validate parameters used admin_force and the user clicked OK in the OK/Cancel dialog.
@ValidateParams bit
Set to 1 when this call is made.

Resultset: Messages (optional)

Table count: repeated zero or one time
Row count: zero or one row
Columns
ADMIN_Force optional string
Message asking the end user to confirm their parameters.
ADMIN_Message optional string
Message explaining why the parameters are rejected.

Custom access control and logging

SQL Call: Custom access control and logging

Use this call to restrict which entries a user is allowed to view and edit, and to log which entries a user views.

Access to a menu item is normally controlled through functions and roles alone but some entities need more fine grained control. For example, a user may have access to the View Member menu item for normal members but not for members with a protected identity.

The menu items a user visits are always logged (in ADMINLogMenuItem) but for sensitive data you may need to log exactly what entries are viewed. Do the logging in this call as the common ways of viewing data (grid and InfoSQL) are not allowed to modify the database.

If you bind a scalar function instead of a stored procedure to this call then its name must end with '_GrantAccess'.

May modify database: Yes

Resultset: Access permissions

Return whether the user is allowed to visit the menu item with the current parameters.
Table count: repeated exactly once
Row count: exactly one row
Columns
GrantAccess mandatory bit
1 if the user is allowed to view the menu item, 0 if the user should not be allowed to view the menu item.

If 0 then an error will be logged as the user should not have been able to reach the menu item with the given parameters in the first place.

Examples

Sending messages from a queue

CREATE SCHEMA SmsOutgoing AUTHORIZATION dbo;
GO

CREATE TABLE SmsOutgoing.SmsQueue
(
	SmsQueueId           BIGINT IDENTITY CONSTRAINT PK_SmsQueue PRIMARY KEY,
	Destination          varchar(30) not null,
	Content              nvarchar(1600) not null,
	QueueDatetimeUtc     datetime2(2) not null,
	SentDatetimeUtc      datetime2(2) null,
	SentMessageReference varbinary(1000) null,
	DeliveryStatus       varchar(30) null,
	IsFinalStatus        bit not null CONSTRAINT DF_SmsQueue_IsFinalStatus DEFAULT(0),
	ErrorCode            int null,
	ErrorMessage         varchar(max) null
);

GO

CREATE OR ALTER PROCEDURE SmsOutgoing.SmsQueue_Send
	@Action varchar(50) = NULL
AS
BEGIN
	DECLARE @Originator varchar(11) = 'Multisoft'; -- Originator should be hardcoded or fetched from a setting.
	
	IF @Action IS NULL
	BEGIN
		SELECT TOP (5000)
			'SendMessage'    AS Action,
			Q.SmsQueueId     AS OutgoingMessageId,
			'Alpha'          AS OriginatorType,
			@Originator      AS Originator,
			Q.Destination    AS Destination,
			Q.Content        AS Content
		FROM
			SmsOutgoing.SmsQueue Q
		WHERE
			Q.SentDatetimeUtc IS NULL
		ORDER BY
			Q.SmsQueueId;

		RETURN;
	END;

	IF @Action = 'SendMessage'
	BEGIN
		UPDATE SmsOutgoing.SmsQueue SET
			SentDatetimeUtc      = SYSUTCDATETIME(),
			SentMessageReference = R.SentMessageReference,
			ErrorCode            = R.ErrorCode,
			ErrorMessage         = R.ErrorMessage,
			IsFinalStatus        = IIF(R.ErrorCode <> 0, 1, 0)
		FROM
			SmsOutgoing.SmsQueue Q
			JOIN #OutgoingMessageResult R ON
				R.OutgoingMessageId = Q.SmsQueueId;

		RETURN;
	END;

	RAISERROR('Unknown action %s', 16, 1, @Action);
END;

Getting the status of sent messages

Uses the queue from the previous example (Sending messages from a queue) to fetch the status of sent messages. While sending should be done continuously you should only fetch delivery reports every few minutes.

CREATE OR ALTER PROCEDURE SmsOutgoing.SmsQueue_GetDeliveryStatus
	@Action varchar(50) = NULL
AS
BEGIN
	IF @Action IS NULL
	BEGIN
		SELECT
			'GetOutgoingMessageStatus' AS Action,
			Q.SmsQueueId               AS OutgoingMessageId,
			Q.SentMessageReference     AS SentMessageReference
		FROM
			SmsOutgoing.SmsQueue Q
		WHERE
			Q.SentDatetimeUtc IS NOT NULL AND
			Q.IsFinalStatus = 0 AND
			Q.SentMessageReference IS NOT NULL;

		RETURN;
	END;

	IF @Action = 'GetOutgoingMessageStatus'
	BEGIN
		DECLARE @StopPollingAfter datetime2(2) = DATEADD(day, 3, SYSUTCDATETIME());

		UPDATE SmsOutgoing.SmsQueue SET
			DeliveryStatus = R.Status,
			ErrorCode      = R.ErrorCode,
			ErrorMessage   = R.ErrorMessage,
			IsFinalStatus  = CASE
				WHEN
					R.Status IN ('Buffered', 'Queued') AND
					Q.SentDatetimeUtc > @StopPollingAfter THEN 0
				ELSE 1
			END
		FROM
			SmsOutgoing.SmsQueue Q
			JOIN #OutgoingMessageStatusResult R ON
				R.OutgoingMessageId = Q.SmsQueueId;

		RETURN;
	END;

	RAISERROR('Unknown action %s', 16, 1, @Action);
END;