IVR: Supported SQL Language for Generic Data Providers

Expand / Collapse
 

IVR: Supported SQL Language for Generic Data Providers


Article ID: 52371 - Last Review: December 4, 2015

GENERAL INFORMATION

In MiContact Center Version 8 we have added support for a new Generic Data Provider in workflow query activities. These were implemented in order to enhance our compatibility with SAP, Salesforce, and Dynamics CRM. This data provider type has some limitations on the SQL queries you can run, as detailed below.



SAP 2015

Batching of SQL statements is not supported. To execute multiple commands, please create multiple instances and execute each separately.


SELECT

SELECT statements generally follow the standard SQL syntax.

  • Selecting all columns with a * is supported (eg: SELECT * FROM tblConfig_Employee )
  • Column aliases are supported (eg: SELECT Reporting AS Employee FROM tblConfig_Employee )
  • Quoted column names are supported (eg: SELECT [Reporting] FROM tblConfig_Employee )
  • WHERE clauses =, !=, >, <, >=, <=, LIKE are supported (eg: SELECT * FROM tblConfig_Employee WHERE Reporting = '1000' )
  • COUNT is supported (eg: SELECT COUNT(LastName) FROM tblConfig_Employee )
  • LIMIT and ORDER BY are supported (eg: SELECT Reporting, LastName FROM tblConfig_Employee ORDER BY Reporting ASC LIMIT 10 )
  • Parameterized SELECT statements are supported (eg: SELECT * FROM tblConfig_Employee WHERE Reporting = @param )
  • Pseudo columns are supported. These do not appear as regular columns in the results but may be specified as part of the WHERE clause (eg: SELECT * FROM tblConfig_Employee WHERE PseudoName = 'Mitel' )


CACHE

CACHE statements allow you to have direct control over the cached data. The CacheLocation must be specified in order to use cache statements.

The syntax for CACHE statements is: CACHE [CACHE_TABLE_NAME] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] SELECT statement

CACHE statements allow for complete control over the data that is cached and the table to which it is cached. The cache statement executes the SELECT statement and caches its results to a similarly named table in the cache database or to the CACHE_TABLE_NAME, if it is specified. The provider updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns. The cache statement may include options that alter its behavior; currently the following options are supported:

WITH TRUNCATE If this option is set, the provider removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.

AUTOCOMMIT If this option is set, the provider will commit each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.

DROP EXISTING If this option is set, the provider drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.

SCHEMA ONLY If this option is set, the provider creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA If this option is set, the provider alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option will result in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.


Names and Quoting

  • Table and column names are considered identifier names; as such, they are restricted to the following characters: [A-Za-z0-9_:@].
  • To use a table or column name with characters not listed above, the name must be quoted using square brackets ([name]) in any SQL statement.
  • Parameter names start with the @ symbol (e.g., @p1 or @CustomerName) and cannot be quoted.
  • Strings should be quoted using single quotes (e.g., 'John Doe').


Transactions

Transactions are not currently supported.



SALESFORCE 2015

Batching of SQL statements is not supported. To execute multiple commands, please create multiple instances and execute each separately.


SELECT

SELECT statements generally follow the standard SQL syntax.

  • Selecting all columns with a * is supported (eg: SELECT * FROM tblConfig_Employee )
  • Column aliases are supported (eg: SELECT Reporting AS Employee FROM tblConfig_Employee )
  • Quoted column names are supported (eg: SELECT [Reporting] FROM tblConfig_Employee )
  • WHERE clauses =, !=, >, <, >=, <=, LIKE are supported (eg: SELECT * FROM tblConfig_Employee WHERE Reporting = '1000' )
  • COUNT is supported (eg: SELECT COUNT(LastName) FROM tblConfig_Employee )
  • LIMIT and ORDER BY are supported (eg: SELECT Reporting, LastName FROM tblConfig_Employee ORDER BY Reporting ASC LIMIT 10 )
  • Parameterized SELECT statements are supported (eg: SELECT * FROM tblConfig_Employee WHERE Reporting = @param )
  • Pseudo columns are supported. These do not appear as regular columns in the results but may be specified as part of the WHERE clause (eg: SELECT * FROM tblConfig_Employee WHERE PseudoName = 'Mitel' )


UPDATE

To update a table, use the standard UPDATE SQL syntax. The provider supports updating only one row at a time. Hence, the primary key Id is always required.


INSERT

To insert into a table, use the standard INSERT SQL syntax.

In order to retrieve the Id of the last inserted record, use the SCOPE_IDENTITY function.


DELETE

To delete from a table, use the standard DELETE SQL syntax. The provider supports deleting only one row at a time. Hence, the primary key, Id, is always required.


CACHE

CACHE statements allow you to have direct control over the cached data. The CacheLocation must be specified in order to use cache statements.

The syntax for CACHE statements is: CACHE [CACHE_TABLE_NAME] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] SELECT statement

CACHE statements allow for complete control over the data that is cached and the table to which it is cached. The cache statement executes the SELECT statement and caches its results to a similarly named table in the cache database or to the CACHE_TABLE_NAME, if it is specified. The provider updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns. The cache statement may include options that alter its behavior; currently the following options are supported:

WITH TRUNCATE If this option is set, the provider removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.

AUTOCOMMIT If this option is set, the provider will commit each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.

DROP EXISTING If this option is set, the provider drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.

SCHEMA ONLY If this option is set, the provider creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA If this option is set, the provider alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option will result in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.


Names and Quoting

  • Table and column names are considered identifier names; as such, they are restricted to the following characters: [A-Za-z0-9_:@].
  • To use a table or column name with characters not listed above, the name must be quoted using square brackets ([name]) in any SQL statement.
  • Parameter names start with the @ symbol (e.g., @p1 or @CustomerName) and cannot be quoted.
  • Strings should be quoted using single quotes (e.g., 'John Doe').


Transactions

Transactions are not currently supported.



DYNAMICS CRM

Batching of SQL statements is not supported. To execute multiple commands, please create multiple instances and execute each separately.


SELECT

SELECT statements generally follow the standard SQL syntax.

  • Selecting all columns with a * is supported (eg: SELECT * FROM tblConfig_Employee )
  • Column aliases are supported (eg: SELECT Reporting AS Employee FROM tblConfig_Employee )
  • Quoted column names are supported (eg: SELECT [Reporting] FROM tblConfig_Employee )
  • WHERE clauses =, !=, >, <, >=, <=, LIKE are supported (eg: SELECT * FROM tblConfig_Employee WHERE Reporting = '1000' )
  • COUNT is supported (eg: SELECT COUNT(LastName) FROM tblConfig_Employee )
  • LIMIT and ORDER BY are supported (eg: SELECT Reporting, LastName FROM tblConfig_Employee ORDER BY Reporting ASC LIMIT 10 )
  • Parameterized SELECT statements are supported (eg: SELECT * FROM tblConfig_Employee WHERE Reporting = @param )
  • Pseudo columns are supported. These do not appear as regular columns in the results but may be specified as part of the WHERE clause (eg: SELECT * FROM tblConfig_Employee WHERE PseudoName = 'Mitel' )


UPDATE

To update a table, use the standard UPDATE SQL syntax. The provider supports updating only one row at a time. Hence, the primary key Id is always required.


INSERT

To insert into a table, use the standard INSERT SQL syntax.

In order to retrieve the Id of the last inserted record, use the SCOPE_IDENTITY function.


DELETE

To delete from a table, use the standard DELETE SQL syntax. The provider supports deleting only one row at a time. Hence, the primary key, Id, is always required.


CACHE

CACHE statements allow you to have direct control over the cached data. The CacheLocation must be specified in order to use cache statements.

The syntax for CACHE statements is: CACHE [CACHE_TABLE_NAME] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] SELECT statement

CACHE statements allow for complete control over the data that is cached and the table to which it is cached. The cache statement executes the SELECT statement and caches its results to a similarly named table in the cache database or to the CACHE_TABLE_NAME, if it is specified. The provider updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns. The cache statement may include options that alter its behavior; currently the following options are supported:

WITH TRUNCATE If this option is set, the provider removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.

AUTOCOMMIT If this option is set, the provider will commit each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.

DROP EXISTING If this option is set, the provider drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.

SCHEMA ONLY If this option is set, the provider creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA If this option is set, the provider alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option will result in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.


Names and Quoting

  • Table and column names are considered identifier names; as such, they are restricted to the following characters: [A-Za-z0-9_:@].
  • To use a table or column name with characters not listed above, the name must be quoted using square brackets ([name]) in any SQL statement.
  • Parameter names start with the @ symbol (e.g., @p1 or @CustomerName) and cannot be quoted.
  • Strings should be quoted using single quotes (e.g., 'John Doe').


Transactions

Transactions are not currently supported. CRM Online Office 365, CRM 2013, and CRM 2015 support bulk operations. The provider abstracts the Dynamics CRM bulk API into SQL. The following sections describe the SQL you can use to execute bulk operations to Dynamics CRM.



APPLIES TO

MiCC Version 8.0 and newer

Keywords: generic data provider providers sap salesforce dynamics crm query sql language syntax format



Rate this Article:
     

Add Your Comments


Name: *
Email Address:
Web Address:
Verification Code:
*
 

Details
Last Modified:Thursday, December 03, 2015
Last Modified By: AndrewM
Type: INFO
Article not rated yet.
Article has been viewed 14,754 times.
Options