Component: SMS

 Show all Hide all

A component designed to send and receive SMS messages.

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

SQL

SQL Call: Get Actions (mandatory)

Statement to extract actions to perform.
Supports custom errors: No
May modify database: No

Parameters:

@Action mandatory string
Will be set to NULL

Resultset: Username and password

Will override the password and username set in the settings SmsCustomerAccountName and SmsCustomerAccountAccessKey.
Repeat mode: 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

A list of message to be sent.
Repeat mode:
Row count:

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.

Note that a receiver 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. The receiving terminal will then add a leading "+"-character and thus provide the ability to reply to the message.
ShortCode This format is used for initiating a premium message and only swedish short codes using the format 72nnn are supported.

Note: Before using a short code, ensure that you have registered a proper premium prefix to use.
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. This number must be supplied in international format.

Example: The swedish phone number 0700-123123 should be written as 0046700123123.
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).

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, is a configurable property of the Customer Account used.
EncodeAsUCS2 optional bit
Set to 1 if the message content should be encoded using UCS-2 instead of the standard GSM-encoding otherwise used.

GSM-encoding supports most western european characters (including åäö). UCS-2 supports a much wider characters set, including arabic and other non latin letters. See other sources for further details.

Resultset: SendPremiumReply

A list of premium replies to be sent.
Repeat mode:
Row count:

Columns:

Action mandatory string
Must be set to "SendPremiumReply".
Content mandatory string
Sets the contents of the message. Concatenated SMS is not supported and thus the maximum lenght allowed is 160 characters using GSM-encoding or 70 characters using UCS-2-encoding.
PremiumPrice mandatory int
The tariff (or amount of money in SEK) that the recipient of the message is to be charged.
SentMessageReference mandatory string
The SentMessageReference of the message that should receive a premium reply.

Resultset: GetIncomingMessages

Fetches all incoming message received in a given time span.
Repeat mode:
Row count:

Columns:

Action mandatory string
Must be set to "GetIncomingMessages".
FromDatetimeUtc mandatory datetime
The lower limit of the selection time span.
ToDatetimeUtc optional datetime
The upper limit of the selection time span. Omit this column, or set to NULL, for no upper limit.
Type optional string
Standard or premium SMS.
Standard
Possible value Description
Premium
Standard

Resultset: GetOutgoingMessageStatus

Fetches the current status information of the specified sent messages.
Repeat mode:
Row count:

Columns:

Action mandatory string
Must be set to "GetOutgoingMessageStatus".
SentMessageReference mandatory int
The SentMessageReference that identifies the message of inquiry.

SQL Call: SendMessage

Supplies the result of a "SendMessage" action.
Supports custom errors: No
May modify database: Yes

Parameters:

@Action mandatory string
Will be set to "SendMessage"

Resultset: #OutgoingMessageResult

A temporary table containing one row for each message that was supposed to sent.
Repeat mode:
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
Referens sent to SMS supplier not found at supplier or in Softadmins Admin tables.
9 ErrorMessage: InternalErrorCode
Not handled error
ErrorMessage mandatory string
Contains an error message if a problem was encountered.
SentMessageReference mandatory int
Used to retrieve outgoingMessageStatus.

SQL Call: SendPremiumReply

Supplies the result of a "SendPremiumReply" action.
Supports custom errors: No
May modify database: Yes

Parameters:

@Action mandatory string
Will be set to "SendPremiumReply"

Resultset: #OutgoingMessageResult

A temporary table containing one row for each premium reply that was supposed to sent.
Repeat mode:
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
Referens sent to SMS supplier not found at supplier or in Softadmins Admin tables.
9 ErrorMessage: InternalErrorCode
Not handled error
ErrorMessage mandatory string
Contains a error message if a problem was encountered.

SQL Call: GetIncomingMessages

Supplies the result of a "GetIncomingMessages" action.
Supports custom errors: No
May modify database: Yes

Parameters:

@Action mandatory string
Will be set to "GetIncomingMessages".

Resultset: #IncomingMessageResult

A temporary table containing all messages (if any) received between the specified points in time.
Repeat mode:
Row count:

Columns:

IncomingMessageId mandatory string
A guaranteed unique string identifying each incoming message.
Type mandatory string
Specifies the type of the message.
Possible value Description
Premium This message was sent to a Short Code with a premium prefix associated with the current Customer Account.
Standard This message was sent to a virtual phone number associated with the current Customer Account.
ReceivedDatetimeUtc mandatory datetime
The point in time when the message was received by SMS Gateway.
Originator mandatory string
The originator of the message, i.e. which phone number it was sent from.
Destination mandatory string
The destination of the message, i.e. which phone number or short code it was sent to.
Content optional string
The content of the message.

SQL Call: GetOutgoingMessageStatus

Supplies the result of a "GetOutgoingMessageStatus" action.
Supports custom errors: No
May modify database: Yes

Parameters:

@Action mandatory string
Will be set to "GetOutgoingMessageStatus".

Resultset: #OutgoingMessageStatusResult

A temporary table containing one row for each valid OutgoingMessageId inquired for.
Repeat mode:
Row count:

Columns:

OutgoingMessageId mandatory string
The OutgoingMessageId inquired for.
Type mandatory string
Specifies the type of the message.
Possible value Description
Premium Premium SMS
Standard Standard SMS
SentDateTimeUtc mandatory datetime
The point in time that SMS Gateway successfully relayed the message to the operator.
Status mandatory string
The current status of the message.
Possible value Description
Buffered The message has been delivered to the 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.
Queued The message has been received by SMS Gateway and is awaiting further delivery to phone operator.
NumberOfMessageSegments mandatory int
The number of individual SMS messages used to transmit the complete message.
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
Referens sent to SMS supplier not found at supplier or in Softadmins Admin tables.
9 ErrorMessage: InternalErrorCode
Not handled error
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.
Supports custom errors: No
May modify database: No

Parameters:

@force optional bit
@validateparams mandatory bit
This parameter is set to 1 by Softadmin® when this call is made.

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'.
Supports custom errors: No
May modify database: Yes

Resultset: Access permissions

Return whether the user is allowed to visit the menu item with the current parameters.
Repeat mode: 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

Send messages

CREATE PROCEDURE [SMS].[SendMessages]
	@Action varchar(50) = null

AS
BEGIN

	IF @Action IS null
	BEGIN
		SELECT 
			'SendMessage' AS [Action], 
			OM.OutgoingMessageId,
			OM.OriginatorType,
			OM.Originator,
			OM.Destination,
			OM.Content,
			OM.EncodeAsUCS2
		FROM 
			SMS.OutgoingMessage OM
		WHERE 
			OM.DatetimeSent IS null
		ORDER BY
			OM.DatetimeInsert asc
	END

	ELSE IF @Action = 'SendMessage'
	BEGIN
		UPDATE
			OM
		SET
			OM.ErrorCode = OMR.ErrorCode,
			OM.ErrorMessage = OMR.ErrorMessage,
			OM.DatetimeSent = 
				CASE 
					WHEN OMR.ErrorCode = 0 THEN sysdatetime()
					ELSE null
				END
		FROM
			#OutgoingMessageResult OMR
			JOIN SMS.OutgoingMessage OM ON
				OM.OutgoingMessageId = OMR.OutgoingMessageId
	END

END