The Power BI Connector for 1010data 2020 is built on top of an ODBC driver. This section discusses how to install the connector, create a DSN, and connect from Power BI.

Installing the Connector

Installing the Connector describes how to install the Power BI Connector for 1010data 2020.

Connecting from Power BI

Getting Data shows how to connect to the DSN and build a query to 1010Data from the Get Data window.

1010Data Version Support

The connector uses the 1010Data ODBC API to retrieve data.

The Power BI Connector for 1010data 2020 includes comprehensive high-performance data access, real-time integration, extensive metadata discovery, and robust SQL-92 support.

Install the Connector

Complete the following steps to install the connector:

1. Contact your account representative to enable SQL connection features on your account
2. Ensure PowerBI is installed and is up to date
3. Download the most recent version of the ODBC package from this link
4. Install the appropriate ODBC package to your local machine
5. Use the prompt to Finish and configure the PowerBI Connector. If the prompt doesn't appear you can access it by finding the application "ConfigureODBC.exe" in the folder where you installed the application
   
6. In the configuration screen perform the following changes:
   • Under Authentication:
     • Data Source Name: Name your connection (you will need to remember this for later)
     • User: Enter your username
     • Password: Enter your password
     • Login Server: Enter the URL of the 1010data environment you are accessing including the version (such as /beta-latest) as shown. The PowerBI connector is only compatible for Edge V15 and above
   
   
7. Download the connector.DB1010data.pqx file
8. Place the .pqx file in the folder This PC > Documents > Power BI Desktop > Custom Connectors
9. Open Microsoft PowerBI
   
10. Select GetData
11. Select the 1010data connector from the list
    
12. Enter your DSN name from Step 5 and select the "DirectQuery" option from the radial in order to run queries in the Edgedb
    
13. You will be prompted to enter your credentials
    
14. Finally, select your table(s) on the next modal window and you should be connected and able to analyze your data in PowerBI
    

The installation process creates a data source name (DSN) called CData Power BI C1010. A DSN is the name that applications use to request a connection to a data source.

The Power BI Connector for 1010data 2020 hooks into Power BI's two modes for Querying Data:

• DirectQuery: Visualizing data in real time by connecting directly to the data at the source.
• Import: Embedding data in a report, which can be refreshed on demand. This is the most common way to get data. Importing data takes advantage of the Power BI query engine.

You select the data connectivity mode when Getting Data. The following sections describe the basics of Visualizing Data, which are defining filters, aggregating data, and joining tables when working with remote data.

Select a data connectivity mode when you create the connection to 1010Data in the Get Data window. The connector fully integrates 1010Data connectivity into the two data connectivity modes in Power BI: DirectQuery and data import.

Using DirectQuery

Use DirectQuery mode to work with the remote data in real time, rather than a local copy. As you define filters, aggregate fields, or join tables, the connector executes the underlying queries to 1010Data.

Note: DirectQuery mode is limited by the DirectQueryLimit connection property.

Using Data Import

Use data import mode to save a copy of the data in your report. As you make changes to your report, Power BI executes the underlying queries to the local cache, independent of the connector.

To synchronize your report with any changes in the remote data, click Refresh from the Home menu on the ribbon.

Advanced Connection Properties (optional)

This field allows you to specify properties for the connection. For example, PropertyA=Value1;PropertyB=Value2;

Advanced Options (optional)

This field allows you to provide a SQL statement that specifies what data to return. To configure this option, expand the Advanced Options area and then, in the SQL statement field, type or paste the SQL statement. Note that SQL statements are not supported in DirectQuery mode.

You can use the following types of SQL statements:

• SELECT Statements extract data from a database. For example:
  

  SELECT * FROM Account

• EXECUTE Statements call procedures that are stored in a database. For example:
  

  EXECUTE my_proc @second = 2, @first = 1, @third = 3;

After Getting Data, you can create data visualizations in the Report view by dragging fields from the Fields pane onto the canvas. This section describes how to use visualizations to display insights that have been discovered in the data.

Creating and Working with Data Visualizations

The following example shows how to create and work with data visualizations, using a pie chart as an example. See Data Model for more specifics on querying 1010Data tables.

1. Select a pie chart icon in the Visualizations pane.
2. Select a dimension in the Fields pane.
3. Select a measure in the Fields pane.

You can change sort options by clicking the ellipsis (...) button for the chart. Options to select the sort column and change the sort order are displayed.

Highlighting and Filtering Data

Highlighting and filtering change the focus on the data. Filtering removes unfocused data from visualizations; highlighting does not remove data, but instead highlights a subset of the visible data; the unhighlighted data remains visible but dimmed.

Highlight fields by clicking them. You can apply filters at the page level or at the report level. To create a filter, drag fields onto the Filters pane. Select the filter type and filter options in the Filters pane.

Creating Real-Time Visualizations

If you selected DirectQuery data connectivity mode when you created the connection, the connector builds a new SELECT WHERE clause as you change the filter.

SELECT Statements

See SELECT Statements for a syntax reference and examples.

See Data Model for information on the capabilities of the 1010Data API.

CACHE Statements

CACHE statements allow granular control over the connector's caching functionality. For a syntax reference and examples, see CACHE Statements.

For more information on the caching feature, see Caching Data.

EXECUTE Statements

Use EXECUTE or EXEC statements to execute stored procedures. See EXECUTE Statements for a syntax reference and examples.

Names and Quoting

• Table and column names are considered identifier names; as such, they are restricted to the following characters: [A-Z, a-z, 0-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 can optionally start with the @ symbol (e.g., @p1 or @CustomerName) and cannot be quoted.
• Strings must be quoted using single quotes (e.g., 'John Doe').

A SELECT statement can consist of the following basic clauses. This statement can be accessed using the Odbc.Query function in the M formula language.

• SELECT
• INTO
• FROM
• JOIN
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY
• LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the connector:

SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  { 
    * 
    | { 
        <expression> [ [ AS ] <column_reference> ] 
        | { <table_name> | <correlation_name> } .* 
      } [ , ... ] 
  }
  [ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
  { 
    FROM <table_reference> [ [ AS ] <identifier> ] 
  } [ , ... ]
  [ [  
      INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } 
    ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ] 
  ] [ ... ] 
  [ WHERE <search_condition> ]
  [ GROUP BY <column_reference> [ , ... ]
  [ HAVING <search_condition> ]
  [ UNION [ ALL ] <select_statement> ]
  [ 
    ORDER BY 
    <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
  ]
  [ 
    LIMIT <expression>
    [ 
      { OFFSET | , }
      <expression> 
    ]
  ] 
}

<expression> ::=
  | <column_reference>
  | @ <parameter> 
  | ?
  | COUNT( * | { [ DISTINCT ] <expression> } )
  | { AVG | MAX | MIN | SUM | COUNT } ( <expression> ) 
  | NULLIF ( <expression> , <expression> ) 
  | COALESCE ( <expression> , ... ) 
  | CASE <expression>
      WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
    [ ELSE { <expression> | NULL } ]
    END 
  | <literal>
  | <sql_function> 

<search_condition> ::= 
  {
    <expression> { = | > | < | >= | <= | <> | != | LIKE | NOT_LIKE | IN | NOT_IN | IS_NULL | IS_NOT_NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
  } [ { AND | OR } ... ] 

Examples

1. Return all columns:
   

   SELECT * FROM pub.demo.baseball.master

2. Rename a column:
   

   SELECT [Firstname] AS MY_Firstname FROM pub.demo.baseball.master

3. Cast a column's data as a different data type:
   

   SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM pub.demo.baseball.master

4. Search data:
   

   SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%';

5. The 1010Data APIs support the following operators in the WHERE clause: =, !=, <>, >=, <=, >, <, LIKE, NOT LIKE, IS NULL, IS NOT NULL, AND, OR, NOT, IN.
   

   SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%';

6. Return the number of items matching the query criteria:
   

   SELECT COUNT(*) AS MyCount FROM pub.demo.baseball.master 

7. Return the unique items matching the query criteria:
   

   SELECT DISTINCT Firstname FROM pub.demo.baseball.master 

8. Summarize data:
   

   SELECT Firstname, MAX(AnnualRevenue) FROM pub.demo.baseball.master  GROUP BY Firstname

   See Aggregate Functions for details.
9. Retrieve data from multiple tables.
   

   SELECT B.ab, B.h, B.year, B.firstname, P.era FROM "pub.demo.baseball.batting" AS B INNER JOIN "pub.demo.baseball.pitching" As P ON B.firstname = P.firstname

   See JOIN Queries for details.
10. Sort a result set in ascending order:
    

    SELECT lahmanid, Firstname FROM pub.demo.baseball.master  ORDER BY Firstname ASC

11. Restrict a result set to the specified number of rows:
    

    SELECT lahmanid, Firstname FROM pub.demo.baseball.master LIMIT 10 

12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
    

    SELECT * FROM pub.demo.baseball.master WHERE LastName = @param

See Explicitly Caching Data for information on using the SELECT statement in offline mode.

Pseudo Columns

Some input-only fields are available in SELECT statements. These fields, called pseudo columns, do not appear as regular columns in the results, yet may be specified as part of the WHERE clause. You can use pseudo columns to access additional features from 1010Data.

    SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%'
    

Examples of Aggregate Functions

Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM pub.demo.baseball.master WHERE LastName = 'B%'

AVG

Returns the average of the column values.

SELECT Firstname, AVG(AnnualRevenue) FROM pub.demo.baseball.master WHERE LastName = 'B%' GROUP BY Firstname

MIN

Returns the minimum column value.

SELECT MIN(AnnualRevenue), Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%' GROUP BY Firstname

MAX

Returns the maximum column value.

SELECT Firstname, MAX(AnnualRevenue) FROM pub.demo.baseball.master WHERE LastName = 'B%' GROUP BY Firstname

SUM

Returns the total sum of the column values.

SELECT SUM(AnnualRevenue) FROM pub.demo.baseball.master WHERE LastName = 'B%'

This section provides information about the features and restrictions that are specific to how the Power BI Connector for 1010data 2020 supports joins. The Power BI Connector for 1010data 2020 supports join queries between any tables in 1010Data. Tables in 1010Data do not contain relationship information. Instead, data can be joined between any two tables, but with some limitations. The only joins available are INNER and LEFT. All other join types will fail.

SELECT B.ab, B.h, B.year, B.firstname, P.era FROM "pub.demo.baseball.batting" AS B INNER JOIN "pub.demo.baseball.pitching" As P ON B.firstname = P.firstname

You can use the SELECT INTO statement to export formatted data to a file. This statement can be accessed using the Odbc.Query function in the M formula language.

Data Export with an SQL Query

The following query exports data into a file formatted in comma-separated values (CSV):

SELECT lahmanid, Firstname INTO [csv://pub.demo.baseball.master.txt] FROM [pub.demo.baseball.master] WHERE LastName = 'B%'

You can specify other formats in the file URI. The possible delimiters are tab, semicolon, and comma with the default being a comma. The following example exports tab-separated values:

SELECT lahmanid, Firstname INTO [csv://pub.demo.baseball.master.txt;delimiter=tab] FROM [pub.demo.baseball.master] WHERE LastName = 'B%'

C#

String connectionString = "User=user@company.com;Password=password;";

using (C1010Connection connection = new C1010Connection(connectionString)) {
  C1010Command cmd = new C1010Command("SELECT lahmanid, Firstname INTO [csv://pub.demo.baseball.master.txt] FROM [pub.demo.baseball.master] WHERE LastName = 'B%' ", connection);
  int rowsAffected = cmd.ExecuteNonQuery();
} 

VB.NET

Dim connectionString As [String] = "User=user@company.com;Password=password;" 

Using connection As New C1010Connection(connectionString)
  Dim cmd As New C1010Command("SELECT lahmanid, Firstname INTO [csv://pub.demo.baseball.master.txt] FROM [pub.demo.baseball.master] WHERE LastName = 'B%'", connection)
  Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
End Using

You can specify other file formats in the URI. The following example exports tab-separated values:

C#

String connectionString = "User=user@company.com;Password=password;";

using (C1010Connection connection = new C1010Connection(connectionString)) {
  C1010Command cmd = new C1010Command("SELECT * INTO [pub.demo.baseball.master] IN [csv://filename=c:/pub.demo.baseball.master.csv;delimiter=tab] FROM [pub.demo.baseball.master] WHERE LastName = 'B%'", connection);
  int rowsAffected = cmd.ExecuteNonQuery();
} 

VB.NET

Dim connectionString As [String] = "User=user@company.com;Password=password;" 

Using connection As New C1010Connection(connectionString)
  Dim cmd As New C1010Command("SELECT * INTO [pub.demo.baseball.master] IN [csv://filename=c:/pub.demo.baseball.master.csv;delimiter=tab] FROM [pub.demo.baseball.master] WHERE LastName = 'B%'", connection)
  Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
End Using

When caching is enabled, CACHE statements provide complete control over the data that is cached and the table to which it is cached. The CACHE statement executes the SELECT statement specified and caches its results to a table with the same name in the cache database or to table specified in <cached_table_name>. The connector 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.

This statement can be accessed using the Odbc.Query function in the M formula language.

See Caching Data for more information on different caching strategies.

CACHE Statement Syntax

The cache statement may include the following options that alter its behavior:

CACHE [ <cached_table_name> ] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] <select_statement> 

WITH TRUNCATE

If this option is set, the connector 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 connector commits 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 connector 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 connector creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA

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

Common Queries

Use the following cache statement to cache all rows of a table:

CACHE SELECT * FROM pub.demo.baseball.master

Use the following cache statement to cache all rows of a table into the cache table Cachedpub.demo.baseball.master:

CACHE Cachedpub.demo.baseball.master SELECT * FROM pub.demo.baseball.master

Use the following cache statement for incremental caching. The DateModified column may not exist in all tables. The cache statement shows how incremental caching would work if there were such a column. Also, notice that, in this case, the WITH TRUNCATE and DROP EXISTING options are specifically omitted, which would have deleted all existing rows.

CACHE Cachedpub.demo.baseball.master SELECT * FROM pub.demo.baseball.master WHERE DateModified > '2013-04-04'

Use the following cache statements to create a table with all available columns that will then cache only a few of them. The sequence of statements cache only lahmanid and Firstname even though the cache table Cachedpub.demo.baseball.master has all the columns in pub.demo.baseball.master.

CACHE Cachedpub.demo.baseball.master SCHEMA ONLY SELECT * FROM pub.demo.baseball.master
CACHE Cachedpub.demo.baseball.master SELECT lahmanid, Firstname FROM pub.demo.baseball.master

To execute stored procedures, you can use EXECUTE or EXEC statements. This statement can be accessed using the Odbc.Query function in the M formula language.

EXEC and EXECUTE assign stored procedure inputs, referenced by name, to values or parameter names.

Stored Procedure Syntax

To execute a stored procedure as an SQL statement, use the following syntax:

 
{ EXECUTE | EXEC } <stored_proc_name> 
{
  [ @ ] <input_name> = <expression>
} [ , ... ]

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

Example Statements

Reference stored procedure inputs by name:

EXECUTE my_proc @second = 2, @first = 1, @third = 3;

Execute a parameterized stored procedure statement:

EXECUTE my_proc second = @p1, first = @p2, third = @p3; 

Caching Data

Caching data provides several benefits, including faster access to data and reducing the number of API calls, which improve performance. The connector supports a simple caching model where multiple connections can also share the cache over time. You can enable and configure caching features by setting the necessary connection properties.

Contents

The sections in this chapter detail the connector's caching functionality and link to the corresponding connection properties, as well as SQL statements.

Configuring the Cache Connection

Configuring the Cache Connection describes the properties that you can set when configuring the cache database.

Caching Metadata

Caching Metadata describes the CacheMetadata property. This property determines whether or not to cache the table metadata to a file store.

Automatically Caching Data

Automatically Caching Data describes how the connector automatically refreshes the cache when the AutoCache property is set.

Explicitly Caching Data

Explicitly Caching Data describes how you can decide what data is stored in the cache and when it is updated.

Data Type Mapping

Data Type Mapping shows the mappings between the data types configured in the schema and the data types in the database.

Configuring the Caching Database

This section describes the properties for caching data to the persistent store of your choice.

CacheLocation

The CacheLocation property species the path to a file-system-based database. When caching is enabled, a file-system-based database is used by default. If CacheLocation is not specified, this database is stored at the path in Location. If neither of these connection properties are specified, the connector uses a platform-dependent default location.

CacheConnection

The CacheConnection property specifies a database driver and the connection string to the caching database.

CacheProvider

The CacheProvider property specifies a database driver and the connection string to the caching database.

Automatically caching data is useful when you do not want to rebuild the cache for each query. When you query data for the first time, the connector automatically initializes and builds a cache in the background. When AutoCache = true, the connector uses the cache for subsequent query executions, resulting in faster response times.

Configuring Automatic Caching

Caching the pub.demo.baseball.master Table

The following example caches the pub.demo.baseball.master table in the file specified by the CacheLocation property of the connection string.

SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%'

Common Use Case

A common use for automatically caching data is to improve driver performance when making repeated requests to a live data source, such as building a report or creating a visualization. With auto caching enabled, repeated requests to the same data may be executed in a short period of time, but within an allowable tolerance (CacheTolerance) of what is considered "live" data.

With explicit caching (AutoCache = false), you decide exactly what data is cached and when to query the cache instead of the live data. Explicit caching gives you full control over the cache contents by using CACHE Statements. This section describes some strategies to use the caching features offered by the connector.

Creating the Cache

To load data in the cache, issue the following statement.

CACHE SELECT * FROM tableName WHERE ...

Once the statement is issued, any matching data in tableName is loaded into the corresponding table.

Updating the Cache

This section describes two ways to update the cache.

Updating with the SELECT Statement

The following example shows a statement that can update modified rows and add missing rows in the cached table. However, this statement does not delete extra rows that are already in the cache. This statement only merges the new rows or updates the existing rows.

CACHE SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%'

Updating with the TRUNCATE Statement

The following example shows a statement that can update modified rows and add missing rows in the cached table. This statement can also delete rows in the cache table that are not present in the live data source.

  CACHE WITH TRUNCATE SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%'
  

Query the Data in Online or Offline Mode

This section describes how to query the data in online or offline mode.

Online: Select Cached Tables

You can use the tableName#CACHE syntax to explicitly execute queries to the cache while still online, as shown in the following example.

SELECT * FROM pub.demo.baseball.master#CACHE

Offline: Select Cached Tables

With Offline = true, SELECT statements always execute against the local cache database, regardless of whether you explicitly specify the cached table or not. Modification of the cache is disabled in Offline mode to prevent accidentally updating only the cached data. Executing a DELETE/UPDATE/INSERT statement while in Offline mode results in an exception.

The following example selects from the local cache but not the live data source because Offline = true.

SELECT * FROM pub.demo.baseball.master WHERE LastName='B%' ORDER BY Firstname ASC

Delete Data from the Cache

You can delete data from the cache by building a direct connection to the database. Note that the connector does not support manually deleting data from the cache.

Common Use Case

A common use for caching is to have an application always query the cached data and only update the cache at set intervals, such as once every day or every two hours. There are two ways in which this can be implemented:

• AutoCache = false and Offline = false. All queries issued by the application explicitly reference the tableName#CACHE table. When the cache needs to be updated, the application executes a tableName#CACHE ... statement to bring the cached data up to date.
• Offline = true. Caching is transparent to the application. All queries are executed against the table as normal, so most application code does not need to be aware that caching is done. To update the cached data, simply create a separate connection with Offline = false and execute a tableName#CACHE ... statement.

The connector maps types from the data source to the corresponding data type available in the chosen cache database. The following table shows the mappings between the data types configured in the schema and the data types in the database.

Data Type Mapping

Note: String columns can map to different data types depending on their length.

Schema	.NET	JDBC	SQL Server	Derby	MySQL	Oracle	SQLite	Access
int	Int32	int	int	INTEGER	INT	NUMBER	integer	LONG
double	Double	double	float	DOUBLE	DOUBLE	NUMBER	double	DOUBLE
date	DateTime	java.sql.Date	date	DATE	DATE	DATE	date	DATETIME
datetime	DateTime	java.sql.Date	datetime	TIMESTAMP	DATETIME	TIMESTAMP	datetime	DATETIME
time	TimeSpan	java.sql.Time	time	TIME	TIME	TIMESTAMP	datetime	DATETIME
string	String	java.lang.String	If length > 4000: nvarchar(max), Otherwise: nvarchar(length)	If length > 32672: LONG VARCHAR, Otherwise VARCHAR(length)	If length > 255: LONGTEXT, Otherwise: VARCHAR(length)	If length > 4000: CLOB, Otherwise: VARCHAR2(length)	nvarchar(length)	If length > 255: LONGTEXT, Otherwise: VARCHAR(length)
long	Int64	long	bigint	BIGINT	BIGINT	NUMBER	bigint	LONG
boolean	Boolean	boolean	tinyint	SMALLINT	BIT	NUMBER	tinyint	BIT
decimal	Decimal	java.math.BigDecimal	decimal	DECIMAL	DECIMAL	DECIMAL	decimal	CURRENCY

The Power BI Connector for 1010data 2020 models 1010Data entities as relational Tables, Views, and Stored Procedures.

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for 1010Data:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables.
• sys_tablecolumns: Describes the columns of the available tables.
• sys_views: Lists the available views.
• sys_viewcolumns: Describes the columns of available views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

• sys_identity: Returns information about batch operations or single updates.

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Name	Type	Description
CatalogName	String	The database name.

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

Name	Type	Description
CatalogName	String	The database name.
SchemaName	String	The schema name.

Lists the available tables.

The following query retrieves the available tables:

          SELECT * FROM sys_tables
          

Columns

Name	Type	Description
CatalogName	String	The database containing the table.
SchemaName	String	The schema containing the table.
TableName	String	The name of the table.
TableType	String	The table type.
Description	String	A description of the table.

Describes the columns of the available tables.

The following query returns the columns and data types for the pub.demo.baseball.master table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='pub.demo.baseball.master'

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the table.
SchemaName	String	The schema containing the table.
TableName	String	The name of the table containing the column.
ColumnName	String	The column name.
DataTypeName	String	The data type name.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The length in characters of the column or the numeric precision.
NumericPrecision	Int32	The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The column scale or number of digits to the right of the decimal point.
IsNullable	Boolean	Whether the column can contain null.
Description	String	A brief description of the column.
Ordinal	Int32	The sequence number of the column.
IsAutoIncrement	String	Whether the column value is assigned in fixed increments.
IsGeneratedColumn	String	Whether the column is generated.
IsReadOnly	Boolean	Whether the column is read-only.
IsKey	Boolean	Whether the column is a primary key.
IsHidden	Boolean	Whether the column is hidden.

Lists the available views.

The following query retrieves the available views:

          SELECT TableName FROM sys_views 
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the view.
SchemaName	String	The name of the schema containing the view.
TableName	String	The name of the view.
TableType	String	The type of the view.
Description	String	A description of the view.

Describes the columns of the available views.

The following query returns the columns and data types for a specified view:

SELECT ColumnName, DataTypeName FROM sys_viewcolumns WHERE TableName='MyView'

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the view.
SchemaName	String	The name of the schema containing the view.
TableName	String	The name of the view.
ColumnName	String	The name of the column.
DataTypeName	String	The name of the data type.
DataType	Int32	An integer indicating the data type of the column. This value is determined at run time based on the environment.
Length	Int32	The length in characters of the column or the numeric precision.
NumericPrecision	Int32	The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The column scale or number of digits to the right of the decimal point.
IsNullable	Boolean	Whether the column can contain null.
Description	String	The column description.
Ordinal	Int32	The sequence number of the column.
IsAutoIncrement	String	Whether the column value is assigned in fixed increments.
IsGeneratedColumn	String	Whether the column is generated.
IsReadOnly	Boolean	Whether the column is read-only.
IsKey	Boolean	Whether the column is a primary key.
IsHidden	Boolean	Whether the column is hidden.

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

Name	Type	Description
CatalogName	String	The database containing the stored procedure.
SchemaName	String	The schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure.
Description	String	A description of the stored procedure.

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the NA stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='NA' AND Direction=1 OR Direction=2

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the stored procedure.
SchemaName	String	The name of the schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure containing the parameter.
ColumnName	String	The name of the stored procedure parameter.
Direction	Int32	An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName	String	The name of the data type.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
NumbericScale	Int32	The number of digits to the right of the decimal point in numeric data.
IsNullable	Boolean	Whether the parameter can contain null.
Description	String	The description of the parameter.
Ordinal	Int32	The index of the parameter.

Describes the primary and foreign keys.

The following query retrieves the primary key for the pub.demo.baseball.master table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='pub.demo.baseball.master'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
IsKey	Boolean	Whether the column is a primary key in the table referenced in the TableName table.
IsForeignKey	Boolean	Whether the column is a foreign key referenced in the TableName table.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the index.
SchemaName	String	The name of the schema containing the index.
TableName	String	The name of the table containing the index.
IndexName	String	The index name.
ColumnName	String	The name of the column associated with the index.
IsUnique	Boolean	True if the index is unique. False otherwise.
IsPrimary	Boolean	True if the index is a primary key. False otherwise.
Type	Int16	An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder	String	The sort order: A for ascending or D for descending.
OrdinalPosition	Int16	The sequence number of the column in the index.

Returns information on the available connection properties and those set in the connection string.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Name	Type	Description
Name	String	The name of the connection property.
ShortDescription	String	A brief description.
Type	String	The data type of the connection property.
Default	String	The default value if one is not explicitly set.
Values	String	A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value	String	The value you set or a preconfigured default.
Required	Boolean	Whether the property is required to connect.
Category	String	The category of the connection property.
IsSessionProperty	String	Whether the property is a session property, used to save information about the current connection.

Describes the SELECT query processing that the connector can offload to the data source.

Collaborative Query Processing

When working with data sources that do not support SQL-92, you can query the sys_sqlinfo view to determine the query capabilities of the underlying APIs, expressed in SQL syntax. See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name	Possible Values
AGGREGATE_FUNCTIONS	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	YES
SUPPORTED_OPERATORS	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	NO_RELATION
OUTER_JOINS	YES
OJ_CAPABILITIES	NESTED, LEFT, RIGHT, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
SUBQUERIES	COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONS	ASCII,CHAR,CONCAT,LEFT,LTRIM,REPLACE,RIGHT,RTRIM,SOUNDEX,SPACE,SUBSTRING
NUMERIC_FUNCTIONS	ABS,ACOS,ASIN,ATAN,CEILING,COS,COT,DEGREES,EXP,FLOOR,LOG,LOG10,PI,POWER,RADIANS,RAND,ROUND,SIGN,SIN,SQRT,TAN
TIMEDATE_FUNCTIONS	CURRENT_DATE,CURRENT_TIMESTAMP,MONTH,YEAR
IDENTIFIER_QUOTE_OPEN_CHAR	[
IDENTIFIER_QUOTE_CLOSE_CHAR	]

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name='SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns

Name	Type	Description
NAME	String	A component of SQL syntax, or a capability that can be processed on the server.
VALUE	String	Detail on the supported SQL or SQL syntax.

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

         SELECT * FROM sys_identity
          

Columns

Name	Type	Description
Id	String	The database-generated Id returned from a data modification operation.
Batch	String	An identifier for the batch. 1 for a single operation.
Operation	String	The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message	String	SUCCESS or an error message if the update in the batch failed.

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Getting Started.

---

Auto Cache	Automatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider .
Cache Connection	The connection string for the cache database. This property is always used in conjunction with CacheProvider . Setting both properties will override the value set for CacheLocation for caching data.
Cache Location	Specifies the path to the cache when caching to a file.
Cache Metadata	This property determines whether or not to cache the table metadata to a file store.
Cache Provider	The name of the provider to be used to cache data.
Cache Session	This option changes whether the provider stores the current session when you log in.
Cache Tolerance	The tolerance for stale data in the cache specified in seconds when using AutoCache .
Direct Query Limit	Limits the number of rows when using the DirectQuery mode. This helps avoid performance issues at design time.
Enable Foreign Key Detection	Whether to detect the foreign keys in ODBC.
Firewall Password	A password used to authenticate to a proxy-based firewall.
Firewall Port	The TCP port for a proxy-based firewall.
Firewall Server	The name or IP address of a proxy-based firewall.
Firewall Type	The protocol used by a proxy-based firewall.
Firewall User	The user name to use to authenticate with a proxy-based firewall.
Kill Session	Whether to kill the any existing sessions under your account.
Location	A path to the directory that contains the schema files defining tables, views, and stored procedures.
Logfile	A path to the log file.
Login URL	The URL of your 1010Data instance.
Maximum Column Size	The maximum column size.
Max Log File Count	A string specifying the maximum file count of log files. When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted.
Max Log File Size	A string specifying the maximum size in bytes for a log file (for example, 10 MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end.
Max Rows	Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Offline	Use offline mode to get the data from the cache instead of the live source.
Other	These hidden properties are used only in specific use cases.
Page Size	The number of results to return per request to 1010Data.
Password	Your 1010Data password.
Proxy Auth Scheme	The authentication type to use to authenticate to the ProxyServer proxy.
Proxy Auto Detect	This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
Proxy Exceptions	A semicolon separated list of hosts or IPs that are exempt from connecting through the ProxyServer .
Proxy Password	A password to be used to authenticate to the ProxyServer proxy.
Proxy Port	The TCP port the ProxyServer proxy is running on.
Proxy Server	The hostname or IP address of a proxy to route HTTP traffic through.
Proxy SSL Type	The SSL type to use when connecting to the ProxyServer proxy.
Proxy User	A user name to be used to authenticate to the ProxyServer proxy.
Pseudo Columns	This property indicates whether or not to include pseudo columns as columns to the table.
Query Passthrough	This option passes the query to the 1010Data server as is.
RTK	The runtime key used for licensing.
SSL Server Cert	The certificate to be accepted from the server when connecting using TLS/SSL.
Tables	This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
User	Your 1010Data login name.
Verbosity	The verbosity level that determines the amount of detail included in the log file.
Views	Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Automatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider .

Data Type

bool

Default Value

false

Remarks

When AutoCache = true, the connector automatically maintains a cache of your table's data in the database of your choice.

Setting the Caching Database

When AutoCache = true, the connector caches to a simple, file-based cache. You can configure its location or cache to a different database with the following properties:

• CacheLocation: Specifies the path to the file store.
• CacheProvider and CacheConnection: Specifies a driver to a database and the connection string.

See Also

• CacheMetadata: This property reduces the amount of metadata that crosses the network by persisting table schemas retrieved from the 1010Data metadata. Metadata then needs to be retrieved only once instead of every connection.
• Explicitly Caching Data: This section provides more examples of using AutoCache in Offline mode.
• CACHE Statements: You can use the CACHE statement to persist any SELECT query, as well as manage the cache; for example, refreshing schemas.

The connection string for the cache database. This property is always used in conjunction with CacheProvider . Setting both properties will override the value set for CacheLocation for caching data.

Data Type

string

Default Value

""

Remarks

The cache database is determined based on the CacheProvider and CacheConnection properties. Both properties are required to use the cache database. Examples of common cache database settings can be found below. For more information on setting the caching database's driver, refer to CacheProvider.

The connection string specified in the CacheConnection property is passed directly to the underlying CacheProvider. Consult the documentation for the specific ADO.NET provider for more information on the available properties.

SQLite

The connector simplifies caching to SQLite, only requiring you to set the CacheLocation property to make a basic connection.

MySQL

The installation includes the CData ADO.NET Provider for MySQL. The following are typical connection properties:

• Server: The IP address or domain name of the server you want to connect to.
• Port: The port that the server is running on.
• User: The user name provided for authentication to the database.
• Password: The password provided for authentication to the database.
• Database: The name of the database.

SQL Server

To cache to SQL Server, you can use the .NET Framework Provider for SQL Server, included in the .NET Framework. The following are typical SQL Server connection properties:

• Server: The name or network address of the computer running SQL Server. To connect to a named instance instead of the default instance, this property can be used to specify the host name and the instance, separated by a backslash.
• Port: The port SQL Server is running on.
• Database: The name of the SQL Server database.
• Integrated Security: Set this option to true to use the current Windows account for authentication. Set this option to false if you are setting the User and Password in the connection.
• User ID: The user name provided for authentication with SQL Server. This property is only needed if you are not using integrated security.
• Password: The password provided for authentication with SQL Server. This property is only needed if you are not using integrated security.

Oracle

To cache to Oracle, you can use the Oracle Data Provider for .NET. The following are typical connection properties:

• Data Source: The connect descriptor that identifies the Oracle database. This can be a TNS connect descriptor, an Oracle Net Services name that resolves to a connect descriptor, or, after version 11g, an Easy Connect naming (the host name of the Oracle server with an optional port and service name).

• Password: The password provided for authentication with the Oracle database.
• User Id: The user Id provided for authentication with the Oracle database.

PostgreSQL

To cache to PostgreSQL, you can use the Npgsql ADO.NET provider. The following are typical connection properties:

• Host: The address of the server hosting the PostgreSQL database.
• Port: The port used to connect to the server hosting the PostgreSQL database.
• Database: The name of the database.
• User name: The user Id provided for authentication with the PostgreSQL database.
• Password: The password provided for authentication with the PostgreSQL database.

Specifies the path to the cache when caching to a file.

Data Type

string

Default Value

"%APPDATA%\\CData\\C1010 Data Provider"

Remarks

The CacheLocation is a simple, file-based cache. The connector uses SQLite.

If left unspecified, the default location is "%APPDATA%\\CData\\C1010 Data Provider" with %APPDATA% being set to the user's configuration directory:

Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Mac	~/.config
Linux	~/.config

See Also

• AutoCache: Set to implicitly create and maintain a cache for later offline use.
• CacheMetadata: Set to persist the 1010Data catalog in CacheLocation.

This property determines whether or not to cache the table metadata to a file store.

Data Type

bool

Default Value

false

Remarks

As you execute queries with this property set, table metadata in the 1010Data catalog are cached to the file store specified by CacheLocation if set or the user's home directory otherwise. A table's metadata will be retrieved only once, when the table is queried for the first time.

When to Use CacheMetadata

The connector automatically persists metadata in memory for up to two hours when you first discover the metadata for a table or view and therefore, CacheMetadata is generally not required. CacheMetadata becomes useful when metadata operations are expensive such as when you are working with large amounts of metadata or when you have many short-lived connections.

When Not to Use CacheMetadata

• When you are working with volatile metadata: Metadata for a table is only retrieved the first time the connection to the table is made. To pick up new, changed, or deleted columns, you would need to delete and rebuild the metadata cache. Therefore, it is best to rely on the in-memory caching for cases where metadata changes often.
• When you are caching to a database: CacheMetadata can only be used with CacheLocation. If you are caching to another database with the CacheProvider and CacheConnection properties, use AutoCache to cache implicitly. Or, use CACHE Statements to cache explicitly.

The name of the provider to be used to cache data.

Data Type

string

Default Value

""

Remarks

You can cache to any database for which you have an ADO.NET provider . The caching database is determined based on the CacheProvider and CacheConnection properties.

The following sections show connection examples and address other requirements for several popular database drivers. Refer to CacheConnection for more information on typical connection properties.

Loading the Caching Database's Driver

The CacheProvider property specifies the name of the DbProviderFactory used to cache data. You can find the provider factories available to you in your machine.config.

SQLite

The connector simplifies SQLite configuration; in most caching scenarios, you need to specify only the CacheLocation.

CacheLocation=C:\\Users\\Public\\cache.db;User=user@company.com;Password=password;

MySQL

To cache to MySQL, you can use the included CData ADO.NET Provider for MySQL:

Cache Provider=System.Data.CData.MySQL;Cache Connection='Server=localhost;Port=3306;Database=cache;User=root;Password=123456';User=myUser;Password=myPassword;Security Token=myToken;

SQL Server

You can use the Microsoft .NET Framework Provider for SQL Server, included in the .NET Framework, to cache to SQL Server:

Cache Provider=System.Data.SqlClient;Cache Connection="Server=MyMACHINE\MyInstance;Database=SQLCACHE;User ID=root;Password=admin";User=user@company.com;Password=password;

Oracle

To cache to Oracle, you can use the Oracle Data Provider for .NET, as shown in the following example:

Cache Provider=Oracle.DataAccess.Client;Cache Connection='User Id=scott;Password=tiger;Data Source=ORCL';User=user@company.com;Password=password;

The Oracle Data Provider for .NET also requires the Oracle Database Client. When you download the Oracle Database Client, ensure that the bitness matches the the bitness of your machine. When you install select either the Runtime or Administrator installation type;, the Instant Client is not sufficient.

PostgreSQL

You can use the Npgsql ADO.NET provider to cache to PostgreSQL, as shown in the following example:

CacheProvider=Npgsql;CacheConnection="Host=myserver;Username=mylogin;Password=mypass;Database=mydatabase";User=user@company.com;Password=password;

This option changes whether the provider stores the current session when you log in.

Data Type

bool

Default Value

true

Remarks

When using the 1010Data API, the connector must get a session before it can execute queries. Each login can have only one active session and acquiring a new session automatically removes all others.

By default this property is true, which means that the connector can share sessions between multiple processes. It does this by writing a file in your temporary directory and storing your session token in it.

If this property is false, the connector will only share sessions within a single process and will not write session information to a file. This will prevent multiple instances of the driver from running at one time.

If you are using an application like Excel for Mac that is sandboxed, you should set this property to false to avoid permissions issues.

The tolerance for stale data in the cache specified in seconds when using AutoCache .

Data Type

string

Default Value

"600"

Remarks

The tolerance for stale data in the cache specified in seconds. This only applies when AutoCache is used. The connector checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.

Limits the number of rows when using the DirectQuery mode. This helps avoid performance issues at design time.

Data Type

string

Default Value

"10000"

Remarks

Limits the number of rows returned when using the DirectQuery Mode. This limit only applies when aggregation is not being used. Queries with SUM, MIN, MAX, GROUP BY, and so on will not be limited.

Whether to detect the foreign keys in ODBC.

Data Type

bool

Default Value

false

Remarks

Whether to detect the foreign keys in ODBC.

A password used to authenticate to a proxy-based firewall.

Data Type

string

Default Value

""

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

The TCP port for a proxy-based firewall.

Data Type

string

Default Value

""

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

The name or IP address of a proxy-based firewall.

Data Type

string

Default Value

""

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the connector uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

The protocol used by a proxy-based firewall.

Data Type

string

Default Value

"NONE"

Remarks

This property specifies the protocol that the connector will use to tunnel traffic through the FirewallServer proxy. Note that by default, the connector connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

Type	Default Port	Description
TUNNEL	80	When this is set, the connector opens a connection to 1010Data and traffic flows back and forth through the proxy.
SOCKS4	1080	When this is set, the connector sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5	1080	When this is set, the connector sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

The user name to use to authenticate with a proxy-based firewall.

Data Type

string

Default Value

""

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

Whether to kill the any existing sessions under your account.

Data Type

bool

Default Value

false

Remarks

Each connection to 1010Data requires its own session. While multiple connections made with the connector can share a session, the connector cannot share sessions with other tools. This option controls what happens when the connector logs in and another session is active.

By default this property is false, and the connector will attempt to take ownership over an existing session. However, this will not work if the session is not responding due to a long-running query or other issues. In those cases this property should be set to true, which will destroy the existing session and any queries which might be active on it.

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

"%APPDATA%\\CData\\C1010 Data Provider\\Schema"

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\\CData\\C1010 Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:

Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Mac	~/.config
Linux	~/.config

A path to the log file.

Data Type

string

Default Value

""

Remarks

For more control over what is written to the log file, you can adjust its Verbosity.

The URL of your 1010Data instance.

Data Type

string

Default Value

""

Remarks

This is the domain assigned to your 1010Data instance. For example, https://mycompany.edge.1010data.com/odbc.

The maximum column size.

Data Type

string

Default Value

"16000"

Remarks

Some tools restrain the largest size of a column or the total size of all the columns selected. You can set the MaximumColumnSize to overcome these schema-based restrictions. The connector will not report any column to be larger than the MaximumColumnSize.

Set a MaximumColumnSize of zero to eliminate limits on column size, as shown in the following example:

SQLSetConnectAttr(hdbc, 20002, (SQLPOINTER)2048, 0);

The following are a few examples of how you can use this property to avoid compatibility issues with several tools:

• Oracle ODBC Gateway: Set MaximumColumnSize=4000 to avoid the ORA-28562 data truncation error. Note that Oracle ODBC Gateway additionally requires that you set the MapToWVarchar connection property to false.
• Microsoft Access: Set MaximumColumnSize=255 to report string fields as TEXT instead of MEMO in Access. MEMO fields have no length limit but have restrictions on joins and filters. TEXT fields have a fixed length but support more functionality in Access tables.

A string specifying the maximum file count of log files. When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted.

Data Type

string

Default Value

""

Remarks

A string specifying the maximum file count of log files. When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted. The minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.

A string specifying the maximum size in bytes for a log file (for example, 10 MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end.

Data Type

string

Default Value

"100MB"

Remarks

A string specifying the maximum size in bytes for a log file (for example, 10 MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 100 MB. Values lower than 100 kB will use 100 kB as the value instead.

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Data Type

string

Default Value

"-1"

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Use offline mode to get the data from the cache instead of the live source.

Data Type

bool

Default Value

false

Remarks

When Offline = true, all queries execute against the cache as opposed to the live data source. In this mode, certain queries like INSERT, UPDATE, DELETE, and CACHE are not allowed.

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Caching Configuration

CachePartial=True	Caches only a subset of columns, which you can specify in your query.
QueryPassthrough=True	Passes the specified query to the cache database instead of using the SQL parser of the connector.

Integration and Formatting

DefaultColumnSize	Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT	Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename	Records the underlying socket data transfer to the specified file.

The number of results to return per request to 1010Data.

Data Type

string

Default Value

"10000"

Remarks

The connector fetches results from 1010Data in batches to avoid long delays when reading large result sets. This option controls how many rows are fetched per request.

Your 1010Data password.

Data Type

string

Default Value

""

Remarks

Your 1010Data password.

The authentication type to use to authenticate to the ProxyServer proxy.

Data Type

string

Default Value

"BASIC"

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the connector will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

• BASIC: The connector performs HTTP BASIC authentication.
• DIGEST: The connector performs HTTP DIGEST authentication.
• NEGOTIATE: The connector retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
• PROPRIETARY: The connector does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

Data Type

bool

Default Value

true

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

On Windows, the proxy settings are read from the Internet Options in the registry.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

A semicolon separated list of hosts or IPs that are exempt from connecting through the ProxyServer .

Data Type

string

Default Value

""

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the connector uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

A password to be used to authenticate to the ProxyServer proxy.

Data Type

string

Default Value

""

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the connector uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

The TCP port the ProxyServer proxy is running on.

Data Type

string

Default Value

"80"

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

The hostname or IP address of a proxy to route HTTP traffic through.

Data Type

string

Default Value

""

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The connector can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the connector uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

The SSL type to use when connecting to the ProxyServer proxy.

Data Type

string

Default Value

"AUTO"

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:

AUTO	Default setting. If the URL is an HTTPS URL, the connector will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYS	The connection is always SSL enabled.
NEVER	The connection is not SSL enabled.
TUNNEL	The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

A user name to be used to authenticate to the ProxyServer proxy.

Data Type

string

Default Value

""

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

This option passes the query to the 1010Data server as is.

Data Type

bool

Default Value

false

Remarks

When this is set, queries are passed through directly to 1010Data.

The runtime key used for licensing.

Data Type

string

Default Value

""

Remarks

The RTK property may be used to license a build. Only use this property in environments that do not support the .NET licensing scheme. In most circumstances, this property is not necessary. The value of this property overwrites all licensing information, so use caution to ensure that the value is correct.

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. For example, Tables=TableA,TableB,TableC.

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

string

Default Value

"60"

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector throws an exception.

Your 1010Data login name.

Data Type

string

Default Value

""

Remarks

This can be either an email address or a classic 1010Data username.

The verbosity level that determines the amount of detail included in the log file.

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the connector reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described in the following list:

1	Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.
2	Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and additional information about the request, if applicable, such as HTTP headers.
3	Setting Verbosity to 3 will additionally log the body of the request and the response.
4	Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
5	Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. For example, Views=ViewA,ViewB,ViewC.