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 Voiceworx platform.
The VoiceWorx 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.
Voiceworx IP should be whitelisted so connection can be established from voiceworx platform to the Microsoft SQL Server RDS instance.
Enabling Remote connections to the SQL server:
Option 1: Using Transact-SQL
Connect to the Database Engine.
From the Standard bar, click New Query.
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
In Object Explorer, right-click a server and select Properties.
Click the Connections node.
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 VoiceWorx platform:
To Configure Microsoft SQL Server, browse to: Integration > Connect System > Connect External System in the VoiceWorx 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 VoiceWorx 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. |
Once you have saved settings, browse to: Custom Apps > Manage Apps and create a new Custom Skill by clicking on “Create New Custom App” button. Fill out the details as shown below:
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.
Now you can create methods for the SQL Server Instance.
As of the publication of this document, the VoiceWorx platform supports following Microsoft SQL Server (MSSQL) methods,
Get Databases
Get Tables
Get All Items
Get Item
Get Items
Post Item
Put Item
Post Item without Primary Key
Delete Item
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 Voice Worx 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 Voice Worx 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 Voice Worx 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 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 | {{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 Voice Worx 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",
}
Method: GetItems
Parameters:
Parameter | Sample Value | Info |
TableName | Product | SQL Server database table name. |
QueryParameters | {"Price":"100"} | Key Value pair in a valid json format. |
Method Parameters:
Parameter | Info |
Method Name | GetItems |
Request Type | GetItems This is the method that you are using |
Parameters | As explained in table above |
Result Type | List of Records It indicates that the method returns a list of records. |
Record Limit | 10 Maximum number of items returned from the method. |
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 Voice Worx 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 {{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 | {{Name}} 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",
}
]
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: 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:
{"response": "Delete Successful"}
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 VoiceWorx.
Last updated