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
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.
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:
Method Parameters:
Below is a sample response format – You should get response like this.
[
{
"TableName": "Product"
}
]
Method: GetAllItems
Parameters:
Method Parameters:
Sample Response:
[
{
"Productid": "1",
"Name": "Smart Watch",
"Price": "100",
},
{
"Productid": "2",
"Price": "50",
"Name": "Product A"
}
]
Method: GetItem
Parameters:
Method Parameters:
Sample Response:
{
"Productid": "1",
"Name": "Smart Watch",
"Price": "100",
}
Method: GetItems
Parameters:
Method Parameters:
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:
Sample Response:
{
"Productid": 1,
"Name": "Smart Watch",
"Price": 100
}
Method: PutItem
Parameters:
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:
Response:
{"response": "Delete Successful"}
Method: RunSelectQuery
Parameters:
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.
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
Create Inputs
Create Input corresponding to the Intent
Create Publish and Publish to Channel
On the Publish tab, click on Add App publishing and fill following details:
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