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 in different versions such as Enterprise, Standard, Express, and Developer. iX Hello support all editions.

The iX Hello 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.

  • iX Hello IP should be whitelisted so a connection can be established from iX Hello 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.

The screenshot below shows the settings:

Configuring Microsoft SQL server with iX Hello

To configure Microsoft SQL Server in the iX Hello platform, browse to: Integration > Connect System > Add New Connection > Connect External System and select Microsoft SQL Server under Choose Connection:

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 the SQL Server administrator for these values.

Configuration
Info

Service Instance Name

You can give any name here. This name will appear as Instance Name in iX Hello platform.

Host

Hostname or IP Address of SQL Server. If you are not using the default port (1443), include the new value. E.g. sqlserverhost:portnumber

Username

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 the 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

Create a data source for SQL Server by navigating to Data Source tab on the same screen. Make sure to select the integration configuration set up in previous step under Integration Configuration.

Create Methods

Now you can now create methods for the SQL Server Instance.

As of the publication of this document, iX Hello supports the 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

This method returns a list of all the available instances.

Parameter
Info

Method Name

GetDatabases

Request Type

GetDatabases

This is the method that you are using

Parameters

See configuration table above

Result Type

List of Records

Indicates that the method can return a list rather than a single record

Record Limit

10

Record JsonPath

Each method returns data in JSON format. To pick any specific information from the JSON, we need to specify the Record JsonPath so that the iX Hello platform will pick that data in consideration while displaying results.

Example: $.[*]

This selects all the records that are 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 is 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 is case-sensitive and derived from the result obtained from the method.

It represents a 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 the 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

The method you are using

Parameters

See configuration table above

Result Type

List of Records

It indicates that the method can return a list of records rather than a single record

Record Limit

10

Record JsonPath

Each method returns data in JSON format. To extract any specific information from the JSON, we need to specify the Record JsonPath so that the iX Hello platform will choose that data while displaying results.

Example: $.[*]

This selects all the records 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 is case-sensitive. {{Input.DatabaseName}} represents that it is replaced by whatever value is provided by user input. For example, if the input parameter name was providerName, the field should be set as {{Input.providerName}}.

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 is case-sensitive and derived from the result obtained from the method.

This represents a single record from 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

The method used.

Parameters

See configuration table above.

Result Type

List of Records

It indicates that the method can a list of records rather than a single record.

Record Limit

10

Record JsonPath

Each method returns data in JSON format. To pick any specific information from the JSON, we need to specify the Record JsonPath so that the iX Hello platform will pick that data when displaying results.

Example: $.[*]

This selects all the records 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 is case-sensitive. {{Input.TableName}} represents that it is replaced by whatever value is provided by user input. For example, if the input parameter name was providerName, it should be entered as {{Input.providerName}}.

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 items in the list.

Record Template

{{FirstName}}

Note:

Anything written in between {{ }} is represented as a JSON field and is case-sensitive and derived from the result obtained from the method. It should 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

The method used

Parameters

See configuration table above

Result Type

Record

Indicates that the method returns a single record rather than a list of records.

Record JsonPath

Each method returns data in JSON format. To extract any specific information from the JSON, we need to specify the Record JsonPath so that the iX Hello platform will pick that data 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 is 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 providerName, it shall have been used as {{Input.providerName}}.

Result Template

Here are the details found {{Name}}. How else can I help you?

{{Records}} represents that the record template is going to loop through 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 records, you will 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

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 a trigger to invoke the intent.

Utterance for GetTables

Details:

Value: get all tables

Custom App: SQL Demo

Intent: GetTablesIntent

Create Inputs

Create Inputs 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 will 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 a more descriptive step by step guide for app creation and publishing in iX Hello.

PreviousMicrosoft Office365 AADNextTwilio

Last updated

Was this helpful?