MS SQL Server

Managed Connection – Microsoft SQL Server

What is SQL Server?

SQL SERVER is a relational database management system (RDBMS) developed by Microsoft. SQL Server comes with different versions and editions like Enterprise, Standard, Express and Developer edition. All of these editions are supported by the ixHello platform.

The ixHello platform allows for the rapid integration of SQL Server to various voice channels such as, Alexa, Lex, Google Assistant and more.

Prerequisites

• SQL Server should support remote connection.

• ixHello IP should be whitelisted so connection can be established from ixHello platform to the Microsoft SQL Server RDS instance.

Enabling Remote connections to the SQL server:

Option 1: Using Transact-SQL

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

EXEC sp_configure 'remote access', 0 ;
GO
RECONFIGURE ;
GO

Option 2: Using SQL Server Management Studio

To configure the remote access option

1. In Object Explorer, right-click a server and select Properties.

2. Click the Connections node.

3. Under Remote server connections, select or clear the Allow remote connections to this server check box.

Below screenshot shows above settings explaining this.

Configure Microsoft SQL server with ixHello Platform

To Configure Microsoft SQL Server, browse to: Integration > Connect System > Add NewConnection>Connect External System in the ixHello platform and select options as shown below.

On the configuration screen, provide the required information as shown below.

You should be able to get these configuration settings from the SQL Server connectivity and security settings. You may need to contact SQL Server administrator for this

Configuration	Info
Service Instance Name	You can give any name here. This name will appear as Instance Name in ixHello platform.
Host	Hostname or IP Address of SQL Server. If you are not using default port, include that. E.g. sqlserverhost:portnumber
User Name	SQL Server Username
Password	Database Password for the username
Database Name	Name of the Database that you are going to use.

Create Custom App

Once you have saved settings, browse to: Custom Apps > Manage Apps and create a new Custom App by clicking on “Create New Custom App” button. Fill out the details as shown below:

Create Data Source

Now, create a Data Source for the Microsoft SQL Server by navigating to Data Source tab in the same screen. Make sure to select the integration config we set up in previous step as Integration Configuration.

Create Methods

Now you can create methods for the SQL Server Instance.

As of the publication of this document, the ixHello platform supports following Microsoft SQL Server (MSSQL) methods,

1. Get Databases

2. Get Tables

3. Get All Items

4. Get Item

5. Get Items

6. Post Item

7. Put Item

8. Post Item without Primary Key

9. Delete Item

10. Run Select Query

Method: GetDatabases

Parameters: Not required

It returns a list of all the instances available.

Parameter	Info
Method Name	GetDatabases
Request Type	GetDatabases This is the method that you are using
Parameters	As explained in table above
Result Type	List of Records It indicates that the method can a list of records
Record Limit	10
Record JsonPath	Each method returns data in json format and to pick any specific information from the json, we need to specify the Record JsonPath so ixHello platform will pick that data in consideration while displaying results. Example: $.[*] This selects all the records that is returned.
Empty Result Template	Sorry, no database found in the given server. How else can I help you? Note: Anything written in between {{ }} is represented as a json field and it’s case-sensitive.
Result Template	Here are the databases found {{Records}}. How else can I help you? {{Records}} represents that the record template is going to loop around all the items in the list.
Record Template	{{DatabaseName}} Note: Anything written in between {{ }} is represented as a json field and it’s case-sensitive and is derived from the result obtained from the method. It represents the single record of the list.

Below is a sample response format – You should get response like this.

[
{"DatabaseName":"database1"},{"DatabaseName":"Instance2"},
]

You can test whether the method has been created correctly by clicking on Test on the action menu. The object screen shall display all the values that are being retrieved from the method while Result tab presents how the output will be presented to the user.

Method: GetTables

Parameters:

Parameter	Sample Value	Info
DatabaseName	Db1	Name of the database instance selected from the dropdown.

Method Parameters:

Parameter	Info
Method Name	GetTables
Request Type	GetTables This is the method that you are using
Parameters	As explained in table above
Result Type	List of Records It indicates that the method can a list of records
Record Limit	10
Record JsonPath	Each method returns data in json format and to pick any specific information from the json, we need to specify the Record JsonPath so ixHello platform will pick that data in consideration while displaying results. Example: $.[*] This selects all the records that is returned.
Empty Result Template	No tables created for {{Input,DatabaseName}}. How else can I help you? Note: Anything written in between {{ }} is represented as a json field and it’s case-sensitive. {{Input.DatabaseName}} represents that it is replaced by whatever value is provided by the user in input. For example if the input parameter name was poviderName, it shall have been used as {{Input.providerName}} etc.
Result Template	Here are the tables found {{Records}}. How else can I help you? {{Records}} represents that the record template is going to loop around all the items in the list.
Record Template	{{TableName}} Note: Anything written in between {{ }} is represented as a json field and it’s case-sensitive and is derived from the result obtained from the method. It represents the single record of the list.

Below is a sample response format – You should get response like this.

[
{
"TableName": "Product"
}
]

Method: GetAllItems

Parameters:

Parameter	Sample Value	Info
TableName	Product	SQL Server database table name.

Method Parameters:

Parameter	Info
Method Name	GetAllItems
Request Type	GetAllItems This is the method that you are using
Parameters	As explained in table above
Result Type	List of Records It indicates that the method can a list of records
Record Limit	10
Record JsonPath	Each method returns data in json format and to pick any specific information from the json, we need to specify the Record JsonPath so ixHello platform will pick that data in consideration while displaying results. Example: $.[*] This selects all the records that is returned.
Empty Result Template	No tables created for {{Input,TableName}}. How else can I help you? Note: Anything written in between {{ }} is represented as a json field and it’s case-sensitive. {{Input.TableName}} represents that it is replaced by whatever value is provided by the user in input. For example if the input parameter name was poviderName, it shall have been used as {{Input.providerName}} etc.
Result Template	Here are the details found {{Records}}. How else can I help you? {{Records}} represents that the record template is going to loop around all the items in the list.
Record Template	{{FirstName}} Note: Anything written in between {{ }} is represented as a json field and it’s case-sensitive and is derived from the result obtained from the method. It shall be the field name in the table. It represents the single record of the list.

Sample Response:

[
{
"Productid": "1",
"Name": "Smart Watch",
"Price": "100",
},
{
"Productid": "2",
"Price": "50",
"Name": "Product A"
}
]

Method: GetItem

Parameters:

Parameter	Sample Value	Info
TableName	Product	SQL Server Database Table name.
PrimaryKeyName	Productid	Name of the primary Key for the provided table
PrimaryKeyValue	1	Value for the Partition Key

Method Parameters:

Parameter	Info
Method Name	GetItem
Request Type	GetItem This is the method that you are using
Parameters	As explained in table above
Result Type	Record It indicates that the method returns a single record.
Record JsonPath	Each method returns data in json format and to pick any specific information from the json, we need to specify the Record JsonPath so ixHello platform will pick that data in consideration while displaying results. Example: $.[*] This selects all the records that is returned.
Empty Result Template	No data found for {{Input,TableName}}. How else can I help you? Note: Anything written in between {{ }} is represented as a json field and it’s case-sensitive. {{Input.TableName}} represents that it is replaced by whatever value is provided by the user in input. For example if the input parameter name was poviderName, it shall have been used as {{Input.providerName}} etc.
Result Template	Here are the details found {{Name}}. How else can I help you? {{Records}} represents that the record template is going to loop around all the items in the list.

Sample Response:

{
"Productid": "1",
"Name": "Smart Watch",
"Price": "100",
}

Here there is only one matching record, If there are multiple, you should get multiple items in the json array.

Method: PostItem

Parameters:

Parameter	Sample Value	Info
TableName	Product	SQL Server database table name.
PartitionKeyName	Productid	Name of the Partition Key
PartitionKeyValue	1	Value for the Partition Key
ItemData	{"Name": "Smart Watch","Price": 100}	Item data in json format

Sample Response:

{
"Productid": 1,
"Name": "Smart Watch",
"Price": 100
}

Method: PostItemWithoutPrimaryKey

Parameters:

Parameter	Sample Value	Info
TableName	Product	SQL Server database table name.
ItemData	{"Name": "Smart Watch","Price": 100}	Item data in json format

Sample Response:

Method: PutItem

Parameters:

Parameter	Sample Value	Info
TableName	Product	SQL Server database table name.
PartitionKeyName	Productid	Name of the Partition Key
PartitionKeyValue	1	Value for the Partition Key
ItemData	{"Name": "Smart Watch 2","Price": 111}	Item data in json format For example

Sample Response:

This is same as Post Item. The only difference is, Put Item updates the record and Post Item inserts the record.

{
"Productid": 1,
"Name": "Smart Watch 2",
"Price": 111
}

Method : DeleteItem

Parameters:

Parameter	Sample Value	Info
TableName	Product	SQL Server database table name.
PartitionKeyName	Productid	Name of the Partition Key
PartitionKeyValue	1	Value for the Partition Key

Response:

Method: RunSelectQuery

Method : RunSelectQuery

Parameters:

Parameter	Sample Value	Info
SelectStatement	select ProductId,Name from Product	A valid SQL select statement.

Sample Response:

[
{
"ProductId": 1,
"Name": "Smart Watch"
},
{
"ProductId": 2,
"Name": "Product A"
}
]

Create Intents

Create Intents corresponding to each method.

GetTablesIntent

Details:

Name: GetTablesIntent

IntentType: Search

Intent Action: Execute Method

Primary Method Data Source: SQL DS

Data source Method: GetTables

Create Slots

Create intents corresponding to every method. For every parameter used in the method, you need to create a corresponding slot. Slot name shall match the parameter name.

Details:
Name: DatabaseName
Slot Order: 0
Reprompt Message: “what’s the database name”
Type: Text
Custom App: SQL Demo
Intent: GetTablesIntent

Create Utterances

To invoke the intent, you need to create utterances. Utterances are short text that act as trigger to invoke the intent.

Utterance for GetTables

Details:
Value: get all tables
Custom App: SQL Demo
Intent: GetTablesIntent

Create Inputs

Create Input corresponding to the Intent

Details:
Name: GetTablesInput
Custom App: SQL Demo
Custom Intent: GetTablesIntent
DataSource : SQL DS
Data Source Method: GetTables

Create Publish and Publish to Channel

On the Publish tab, click on Add App publishing and fill following details:

External App Name: SQL Demo
App Type: Survey
Channel: Alexa
Skill Invocation: database demo
Messages: Fill with your suitable prompts.

After creating the publish, you’ll be able to publish the app to the channel selected and test the app as well.

On the developer console, once the skill builds successfully, you can invoke the intents and test the app. Here is a sample one,

You can refer to this document for more descriptive step by step guide for app creation and publishing in ixHello.