ODBC Driver for 1010data 2020

ODBC Driver for 1010data 2020

ODBC Driver for 1010data 2020 - Build 19.0.7333

Overview

The ODBC Driver for 1010data 2020 enables real-time access to 1010data data, directly from any applications that support ODBC connectivity, the most widely supported interface for connecting applications with data.

The driver wraps the complexity of accessing 1010data data in a standard ODBC driver compliant with ODBC 3.8. Applications can then access 1010data as a traditional database. The driver hides the complexity of accessing data and provides additional powerful security features, smart caching, batching, socket management, and more.

Key Features

  • Access, analyze, and report on data with your SQL-based tool of choice.
  • Supports 32-bit and 64-bit applications.
  • Supports the Unicode ODBC APIs and ODBC 3.8.
  • Drivers for Windows, Linux, and macOS/OS X.

Getting Started

See Getting Started to create an ODBC data source name (DSN) and connect to 1010data from your platform.

Using the ODBC Driver

See Using the ODBC Driver (from Tools) for information on using the ODBC driver in some of the popular applications and programming languages that connect via ODBC. See Using the ODBC Driver (from Code) to connect to 1010data from the following programming languages and for information on the supported interfaces:

See the 1010data integration guides for information on connecting from other applications.

Linked Server

You can use SQL Linked Server to connect to the CData SQL Gateway. SQL Server treats the CData SQL Gateway as a linked SQL Server instance, so you can write full SQL queries to query your 1010data data without losing performance as would happen connecting to an ODBC data source directly. The connection can be made locally or to the Gateway located on a separate machine.

See Create a Linked Server for more information.

SQL Gateway

See SQL Gateway to configure MySQL or SQL Server entry points for 1010data on your server, using the included CData SQL Gateway application. The SQL Gateway listens for incoming MySQL and SQL Server connections and brokers the SQL request to the ODBC data source. This enables any MySQL or SQL Server client to connect, from PHP applications, SQL Server linked servers, and so on.

SQL Compliance

See SQL Compliance for a syntax reference and code examples outlining the supported SQL.

Caching Data

See Caching Data to configure replication and caching for a range of scenarios common to remote data access. Configurations include:

  • Autocache: Automatically cache data to a lightweight database. Save data for later offline use or enable fast reporting from the cache.
  • Replication: Copy data to local and cloud data stores such as Oracle, SQL Server, Google Cloud SQL, and so on. The replication commands allow for intelligent incremental updates to cached data.
  • No caching: Work with remote data only. No local cache file is created.

Data Model

See Data Model for the available database objects. This section also provides more detailed information on querying specific 1010data entities.

Connection String Options

The Connection properties describe the various options that can be used to establish a connection.

ODBC Driver for 1010data 2020

Getting Started

The ODBC Driver for 1010data 2020 is a standards-based ODBC driver with editions for Windows and Unix-based operating systems, like macOS and Linux. The following sections show how to create an ODBC data source and query data.

Download the most recent version of the ODBC package from this link.

Creating a DSN on Your Platform

The following guides show how to use the standard tools to configure data source names (DSN).

1010data Version Support

The driver uses the 1010data ODBC API to retrieve data.

See Also

See the following sections to connect to the DSN from tools and from code:

ODBC Driver for 1010data 2020

Windows DSN Configuration

Using the Microsoft ODBC Data Source Administrator

You can use the Microsoft ODBC Data Source Administrator to edit the DSN configuration. Note that the DSN is created during the installation process.

Complete the following steps to edit the DSN configuration:

  1. Select Start > Search, and enter ODBC Data Sources in the Search box.
  2. Choose the version of the ODBC Administrator that corresponds to the bitness of your application (32-bit or 64-bit).
  3. Click the System DSN tab.
  4. Select the system data source and click Configure.
  5. Edit the information on the Connection tab and click OK.

Note: For .NET Framework 4.0, the driver distributes Microsoft Visual C++ 2017 Redistributable. For .NET Framework 3.5, the driver distributes Microsoft Visual C++ 2008 Redistributable.

Most tables require user authentication as well as application authentication. You can use the OAuth standard to authenticate.

ODBC Driver for 1010data 2020

macOS DSN Configuration

This section shows how to set up ODBC connectivity and configure DSNs on macOS.

Minimum macOS Version

The driver requires macOS Sierra (10.12) or above.

Licensing the Driver

In a terminal, run the following commands to license the driver. To activate a trial, omit the <key> input.

cd "/Applications/ODBC Driver for C1010/bin"
sudo ./install-license <key>

You'll be prompted for a name and password. These refer to your name and your machine's password.

Connecting through a Driver Manager

On macOS, the ODBC Driver for 1010data 2020 is preconfigured for use with the iODBC driver manager, as are many other products like Filemaker Pro, Microsoft Excel, and Tableau. You can find the latest version of iODBC on the iODBC site.

The driver installation registers the driver with iODBC and creates a system DSN, which you can use in any tools or applications that support ODBC connectivity.

The driver manager loads the driver and passes function calls from the application to the driver. The driver must be registered with the driver manager and DSNs are defined in the driver manager's configuration files.

Configuring DSNs

To configure a DSN, you can use the iODBC Administrator 64-bit, the GUI installed with iODBC. Note that the ODBC Manager must match the bitness of the ODBC driver. The most recent version of the ODBC Driver for 1010data 2020 is 64-bit only. Alternatively, you can edit the iODBC configuration files.

You can configure User or System DSNs. User data sources are restricted to a user account. System data sources can be accessed by all users.

Configuring a DSN with the iODBC Administrator

You can create user DSNs by opening the iODBC Administrator 64-bit from Launchpad.

To modify the system DSN installed by the driver or create a system DSN, open the iODBC Administrator 64-bit with elevated permissions. To do so, enter the following command into a terminal:

sudo /Applications/iODBC/iODBC\ Administrator64.app/Contents/MacOS/iODBC\ Administrator64
After opening the iODBC Administrator 64-bit, you will see the CData C1010 Source listed under the System tab. Select the DSN and click the Configure button to set connection properties as name-value pairs.

To create your own DSN, instead click Add on the User or System tab and then select the ODBC Driver for C1010 option.

Most tables require user authentication as well as application authentication. You can use the OAuth standard to authenticate.

Configuring a DSN in the iODBC INI Files

Configure DSNs in odbc.ini. Register ODBC drivers in odbcinst.ini.

odbc.ini

Define ODBC data sources in sections in the odbc.ini file. User data sources can only be accessed by the user account whose home folder the odbc.ini is located in. System data sources can be accessed by all users.

PrivilegesPath
User/Users/myuser/Library/ODBC/odbc.ini
System/Library/ODBC/odbc.ini

Modifying iODBC's system-wide settings requires elevated permissions; to do so, you can use the sudo command to open a text editor from the terminal. For example:

sudo nano /Library/ODBC/odbc.ini

In addition to the connection properties required to connect to your data source, the Driver property specifies either a driver definition in the odbcinst.ini file or the path to the driver library.

[CData C1010 Source]
Driver = ODBC Driver for C1010
User=username
Password=password

Additionally, in the ODBC Data Sources section, the DSN must be set to a driver defined in the odbcinst.ini file. For example, below is the entry for the DSN created during the driver install:

[ODBC Data Sources]
CData C1010 Source = ODBC Driver for C1010

odbcinst.ini

You may need to modify the installed driver definition if you change the path to the driver library.

To register an ODBC driver, modify the odbcinst.ini file. With iODBC, drivers can be available to only one user account or drivers can be available system wide.

PrivilegesPath
User/Users/myuser/Library/ODBC/odbcinst.ini
System/Library/ODBC/odbcinst.ini

Drivers are defined in sections in the odbcinst.ini file. The section name specifies the name of the driver. In this section, the Driver property specifies the path to the driver library. The driver library is the .dylib file located in the lib subfolder of the installation directory, by default in /Applications/ODBC Driver for C1010.

[ODBC Driver for C1010]
Driver = /Applications/ODBC Driver for C1010/lib/libc1010odbc.dylib

The ODBC Drivers section must also contain a property with the driver name, set to "Installed". For example:

[ODBC Drivers]
ODBC Driver for C1010 = Installed

Testing the Connection

You can use the iODBC Demo, available in most iODBC installations, to connect to 1010data and execute SQL queries.

iODBC Demo

Complete the following steps to connect from the iODBC Demo:

  • Open Launchpad and search for "iODBC".
  • If you need to connect to 1010data from an application that can use only the ANSI ODBC API, click iODBC Demo Ansi. Otherwise, click iODBC Demo Unicode.
  • In the Environment menu, click Open Connection.
  • Select the DSN on the corresponding tab and test the connection.
You can now execute SQL statements to 1010data by clicking Execute SQL in the SQL menu.

Set the Driver Encoding

The ODBC drivers need to specify which encoding to use with the ODBC Driver Manager. By default, the ODBC Drivers for Mac are configured to use UTF-32 which is compatible with iODBC, but other Driver Managers may require alternative encoding.

Alternatively, if you are using the ODBC driver from an application that uses the ANSI ODBC API it may be necessary to set the ANSI code page. For example, to import Japanese characters in an ANSI application, you can specify the code page in the config file '/Applications/ODBC Driver for C1010/lib/cdata.odbc.c1010.ini':

[Driver]
AnsiCodePage = 932

Uninstalling the Driver

The easiest way to uninstall the driver is to open a terminal and run the included uninstall.sh script, located in the installation directory. For example:

cd "/Applications/ODBC Driver for C1010"
sudo ./uninstall.sh

Note: The script needs to be run from the installation directory.

ODBC Driver for 1010data 2020

Linux DSN Configuration

This section describes how to set up ODBC connectivity and configure DSNs on several Linux distributions: Debian-based systems, like Ubuntu, and Red Hat Linux platforms, like Red Hat Enterprise Linux (RHEL), CentOS, and Fedora.

Minimum Linux Versions

Here are the minimum supported versions for Red Hat-based and Debian-based systems:

OSMin. Version
Ubuntu11.04
Debian7
RHEL6.9
CentOS6.9
Fedora13

Installing the Driver Dependencies

Run the following commands as root or with sudo to install the necessary dependencies:

  • Debian/Ubuntu:
    apt-get install libc6 libstdc++6 zlib1g libgcc1
  • RHEL/CentOS/Fedora:
    yum install glibc libstdc++ zlib libgcc

Here are the corresponding libraries required by the driver:

Debian/Ubuntu PackageRHEL/CentOS/Fedora PackageFile
libc6glibclinux-vdso.1
libc6glibclibm.so.6
libc6glibclibrt.so.1
libc6glibclibdl.so.2
libc6glibclibpthread.so.0
libc6glibclibc.so.6
libc6glibcld-linux-x86-64.so.2
libstdc++6libstdc++libstdc++.so.6
zlib1gzliblibz.so.1
libgcc1libgcclibgcc_s.so.1

Installing the Driver

You can use standard package management systems to install the driver.

On Debian-based systems, like Ubuntu, run the following command with root or sudo:

dpkg -i /path/to/driver/setup/C1010ODBCDriverforUnix.deb 

On systems that support the RPM package format, run the following command with root or sudo:

rpm -ivh /path/to/driver/C1010ODBCDriverforUnix.rpm 

Licensing the Driver

Run the following commands to license the driver. To activate a trial, omit the <key> input.

cd /opt/cdata/cdata-odbc-driver-for-c1010/bin/
sudo ./install-license.x64 <key>

Connecting through the Driver Manager

The driver manager loads the driver and passes function calls from the application to the driver. You need to register the driver with the driver manager and you define DSNs in the driver manager's configuration files.

The driver installation registers the driver with the unixODBC driver manager and creates a system DSN. The unixODBC driver manager can be used from Python and from many other applications. Your application may embed another driver manager.

Creating the DSN

See Using unixODBC to install unixODBC and configure DSNs. See Using the DataDirect Driver Manager to create a DSN to connect to OBIEE, Informatica, and SAS.

Most tables require user authentication as well as application authentication. You can use the OAuth standard to authenticate.

Set the Driver Encoding

The ODBC drivers need to specify which encoding to use with the ODBC Driver Manager. By default, the ODBC Drivers for Unix are configured to use UTF-16 which is compatible with unixODBC, but other Driver Managers may require alternative encoding.

Alternatively, if you are using the ODBC driver from an application that uses the ANSI ODBC API it may be necessary to set the ANSI code page. For example, to import Japanese characters in an ANSI application, you can specify the code page in the config file '/opt/cdata/cdata-odbc-driver-for-c1010/lib/cdata.odbc.c1010.ini':

[Driver]
AnsiCodePage = 932

ODBC Driver for 1010data 2020

Using unixODBC

In the following sections, you can find how-tos on installing the unixODBC driver manager, setting up the driver for unixODBC, and creating DSNs.

Installing unixODBC

Precompiled binaries for the unixODBC driver manager are available for many operating systems.

On Debian-based systems like Ubuntu, you can install unixODBC by running the following command as root or with sudo:

apt-get install unixodbc unixodbc-dev
On Red Hat Enterprise Linux, CentOS, and Fedora, you can install unixODBC with YUM or DNF. For example, run the following command as root or with sudo:
yum install unixODBC unixODBC-devel
If binaries are not available for your operating system, you will need to compile unixODBC yourself. Please refer to the unixODBC website for more information about obtaining binaries or compiling unixODBC on your operating system.

Using unixODBC Configuration Files

If your unixODBC installation does not include a graphical tool, you can set up connectivity to 1010data by editing the configuration files.

You can determine the location of the configuration files on your system by entering the following command into a terminal:

odbcinst -j
Below is an example of the output of this command:
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/myuser/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
Note: Modifying system-wide settings requires elevated permissions; to do so, you can use root or the sudo command.

Set the Driver Encoding

unixODBC communicates with applications using UTF-16 encoding. This is the default encoding in ODBC Drivers, but this can be set to alternative options if necessary, in the config file '/opt/cdata/cdata-odbc-driver-for-c1010/lib/cdata.odbc.c1010.ini'.

Register the Driver

Register the driver by adding a section to the odbcinst.ini file. You may need to modify the installed driver definition if you change the path to the driver library.

The sections of the odbcinst.ini file map a driver name to the driver library. The section begins with the driver name; the Driver property points to the path to the driver library. The driver library is the .so file located by default in /opt/cdata/cdata-odbc-driver-for-c1010/lib. A Description property can also be provided.

[ODBC Driver for C1010]
Driver=/opt/cdata/cdata-odbc-driver-for-c1010/lib/libc1010odbc.x64.so
Description=ODBC Driver for 1010data 2020

To check that the driver is registered, list the drivers installed on the system with the following command:

odbcinst -q -d

Define a DSN

Create a DSN by adding an entry to odbc.ini. Below is an example DSN entry in odbc.ini:

[CData C1010 Source]
Driver=/opt/cdata/cdata-odbc-driver-for-c1010/lib/libc1010odbc.x64.so
User=username
Password=password

Test the Connection

You can use the unixODBC test tool, isql, to execute SQL queries to 1010data from the command line. When testing the connection, use the -v flag to output any messages from the driver manager and the driver.

isql -v "CData C1010 Source" 

ODBC Driver for 1010data 2020

Using the DataDirect Driver Manager

Some ODBC client programs ship with their own ODBC driver managers, as opposed to the open-source unixODBC. For example, Informatica, OBIEE, and SAS use the DataDirect driver manager. See Using the ODBC Driver (from Tools) for application-specific configuration.

To avoid incompatibilities between unixODBC and your ODBC client application's ODBC driver manager, we would recommend uninstalling unixODBC before configuring the driver.

Set the Driver Encoding

The ODBC Driver also supports the ODBC environment attributes SQL_ATTR_APP_UNICODE_TYPE and SQL_ATTR_DRIVER_UNICODE_TYPE, which the DataDirect ODBC Driver Manager uses to automatically set the encoding depending on what the client applications expects. If those attributes are supported by the client application, character encoding is defined automatically. If those attribute aren't specified, the encoding can be set manually, in '/opt/cdata/cdata-odbc-driver-for-c1010/lib/cdata.odbc.c1010.ini'. By default, the DataDirect Driver Manager uses UTF-8:

[Driver]
DriverManagerEncoding=UTF-8

Register the Driver

In the driver configuration file, "odbcinst.ini", register the driver name. Note that this step is not required for SAS and Informatica.

The location of this file may depend on your installation. Some applications search environment variables for the ODBC configuration files, such as $ODBCHOME.

[ODBC Driver for C1010]
Driver=/opt/cdata/cdata-odbc-driver-for-c1010/lib/libc1010odbc.x64.so
Description=ODBC Driver for C1010 2017

Configure the DSN

In the DSN configuration file, "odbc.ini", make sure to specify the driver library by its full path, as opposed to the driver name like with unixODBC. You can then enter the other connection properties required by the specific data source.

The location of this file may depend on your installation. Some applications search environment variables for the ODBC configuration files, such as $ODBCHOME.

[CData C1010 Source]
Driver=/opt/cdata/cdata-odbc-driver-for-c1010/lib/libc1010odbc.x64.so
User=username
Password=password
See Linux DSN Configuration for a guide to connecting to 1010data.

ODBC Driver for 1010data 2020

Using the ODBC Driver (from Tools)

The driver provides the standard ODBC connection process in analytics tools and other applications.

ODBC Integration Quickstarts

The following sections show how to create and start querying 1010data ODBC data sources, walking through data access in ODBC from popular tools.

Complete List of 1010data Integration Quickstarts

See 1010data integration guides for information on connecting from other applications.

ODBC Driver for 1010data 2020

From Excel

This section describes how to use the driver with two Excel utilities, the Data Connection Wizard and Microsoft Query. If you are interested in using data from 1010data in Power Pivot, see the section: From PowerPivot.

macOS Configuration

On macOS, you need to set the AnsiCodePage configuration setting in the cdata.odbc.c1010.ini file, which is located in the folder containing the driver library, the lib subfolder under /Applications/ODBC Driver for C1010. Add the AnsiCodePage property in the Driver section.

You need to set the value of this property to the code page of your machine's current system locale. Here are several examples:

LanguageCode Page
English28591
Chinese936
Japanese932

Data Connection Wizard

Select From Other Sources > From Data Connection Wizard in the Data tab. Select ODBC DSN as your chosen data source type and click Next. Here, you can view all of the ODBC data sources on your machine. See "DSN Configuration" to configure a new ODBC data source if the CData 1010data data source is not listed. Once you select the data source, you will be presented with a list of tables. On this page, you can select the table and then import the data.

Microsoft Query in Windows

Select From Other Sources > From Microsoft Query from the Data tab. Here, you can view all of the ODBC data sources on your machine. See "DSN Configuration" to configure an ODBC data source if you do not see the CData 1010data data source. Once you select the correct data source, you will be guided through the following windows:

  • Choose Columns: Preview the data in any column and select the columns you want to include.
  • Filter Data: Add conditions that will filter data based on your criteria.
  • Sort Order: Select how the data should be sorted.
Click Finish to create the external data connection.

Microsoft Query in macOS

Select New Database Query > From Database from the Data tab. Here, you can view all of the ODBC data sources on your machine. See "DSN Configuration" to configure an ODBC data source if you do not see the CData 1010data data source.

Once you select the correct data source, you will need to build your query in the SQL Statement section. Clicking the table names will add the select statement that retrieves all data from that table. You will now have an external data connection.

Refreshing Data

Both the Data Connection Wizard and Microsoft Query create an external data connection in Excel. External data connections have options that control refreshing the data; you can control these settings from the WorkBook Connections window under the Data tab. There are options available to refresh the data each time the file is opened, or periodically at a specified interval.

ODBC Driver for 1010data 2020

From PowerPivot

Power Pivot is a popular business-intelligence add-in from Microsoft for Microsoft Excel. When PowerPivot is installed, the PowerPivot tab is added to the Excel ribbon. PowerPivot is freely available from Microsoft. You can follow this guide to import tables into PowerPivot.

macOS Configuration

On macOS, you need to set the AnsiCodePage configuration setting in the cdata.odbc.c1010.ini file, which is located in the folder containing the driver library, the lib subfolder under /Applications/ODBC Driver for C1010. Add the AnsiCodePage property in the Driver section.

You need to set the value of this property to the code page of your machine's current system locale. Here are several examples:

LanguageCode Page
English28591
Chinese936
Japanese932

Load 1010data Tables

Follow the steps below after creating a DSN and configuring the driver:
  1. Launch the PowerPivot window from the PowerPivot Window button on the PowerPivot tab of Excel.
  2. Click Get External Data > Other Data Sources to open the Table Import Wizard.
  3. Select Other/Advanced (OLEDB/ODBC) Relational Data Source and click Next. You will be presented with a dialog that allows you to define a user-friendly connection name for your connection and build a connection string for it.
  4. Click Build to open the Data Link Properties window.
  5. On the Provider tab, select Microsoft OLEDB Provider for ODBC Drivers.
  6. On the Connection tab of the Data Link Properties window, select Use data source name and select the CData 1010data Data Source. See "DSN Configuration" to configure an ODBC Data Source if you do not see the CData 1010data data source. Once you select the correct data source and exit the Data Link Properties dialog, a connection string will be built for you. It should resemble the following example:
    Provider=MSDASQL.1;Persist Security Info=False;DSN=MyC1010DSN
  7. Select the option to select from a list of tables for the data to import.
  8. Select the tables you want to import and click Finish.

If you want to import specific columns or only the results that match your criteria, you can also select the option to write a query.

ODBC Driver for 1010data 2020

From Access

You can use the ODBC Driver for 1010data 2020 to import data into an Access table and to create a linked table.

Import a Table into Access

To import a table into Access:

  1. Select External Data > ODBC Database from the ribbon. This opens the ODBC Database wizard. Select the option to import the source data into a new table in the current database.
  2. Select the CData 1010data data source from the Machine Data Source tab.
  3. Select the tables to import into Access.

Importing data into Access is a one-time import..

Link a table in Access

To link a table in Access to a table in 1010data:

  1. Select External Data > ODBC Data sources from the ribbon.
  2. Select the option to link to the data source by creating a linked table.
  3. On the next page of the wizard, select the CData 1010data data source in the Machine Data Source tab.

Once you select the correct data source, you will be presented with a list of tables. Here, you can select a table that you want as a linked table in Access. The linked table will always have fresh data.

ODBC Driver for 1010data 2020

From FileMaker Pro

You can connect to a DSN to load 1010data data into FileMaker Pro tables that can be refreshed on demand.

Building a Query to 1010data

FileMaker Pro provides a wizard to build the data import SQL.

  1. In your FileMaker Pro database, select File > Import Records > ODBC Data Source, and select the CData 1010data Source DSN.
  2. In the resulting SQL Query Builder wizard, select tables and columns and then click Insert into SQL Query. You can also edit this query directly.

    You can use the UI to build filters in the WHERE clause by clicking the WHERE tab.

  3. In the resulting Import Field Mapping wizard, you can define mappings from columns in the data source to the columns in a destination table. To create a new table for the query results, select New Table ("CData 1010data Source") from the Target box and click Import.

Refreshing Data

You can load any changes made to the external data into your FileMaker tables in Browse by selecting Records > Refresh Window. FileMaker Pro also executes a SELECT to 1010data when you run a search on the Fields tab of the relationships graph.

ODBC Driver for 1010data 2020

From OBIEE

This section describes how to create 1010data DSNs for use in Oracle Business Intelligence Enterprise Edition (OBIEE) installations on Linux.

Configure a DSN

Follow the procedure in Using the DataDirect Driver Manager to set the driver encoding, register the driver, and create a DSN. In OBIEE 12c, you can find the odbc.ini and odbcinst.ini configuration files in BI_DOMAIN/config/fmwconfig/bienv/core.

Test the Connection

To test the newly configured DSN 'CData C1010 Source', you can use the nqcmd OBIEE utility command.

ODBC Driver for 1010data 2020

From Informatica

This section describes how to create a DSN on the Informatica PowerCenter server. To connect to 1010data from Informatica PowerCenter, install the driver on the machine running Informatica Information Services and the machine you are connecting from. On both machines, you will then need to specify the connection properties in a DSN.

On Windows, you can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs. Linux editions of Informatica embed the DataDirect driver manager: the following steps show how to use this driver manager to configure a DSN for Informatica. You can then connect to the DSN from client tools like the Workflow Manager and Informatica.

Configure a DSN on a Linux Server

Before defining the DSN, complete these prerequisite steps:

  1. Set the ODBCSYSINI environment variable:
    >$ export ODBCSYSINI=/home/infar/Informatica/9.6.1/ODBC7.1
  2. Set the ODBCINI environment variable:
    >$ export ODBCINI=$ODBCSYSINI/odbc.ini
  3. Set the LD_LIBRARY_PATH environment variable:
    >$ export LD_LIBRARY_PATH=/home/infar/Informatica/9.6.1/ODBC7.1/lib:$LD_LIBRARY_PATH
You can then follow the procedure in Using the DataDirect Driver Manager to set the driver encoding and create the DSN in odbc.ini. For example, in /home/infar/Informatica/9.6.1/ODBC7.1/odbc.ini or /opt/informatica/10.1/ODBC7.1/odbc.ini. Note that the step to register the driver in odbcinst.ini is not required for Informatica.

Test the Connection

To test the newly configured DSN 'CData C1010 Source', you can use the utility ssgodbc:

  1. Change to the directory containing the tool in your Informatica installation. For example, enter the following command, changing the destination folder to match your operating system, as necessary:
    cd $INFA_HOME/tools/debugtools/ssgodbc/linux64
  2. Enter the following to test the DSN:
    ssgodbc -d "CData C1010 Source"-u username -p password -v

ODBC Driver for 1010data 2020

From SAS

This section describes how to create a DSN for use with SAS running on a Linux machine.

Configure a DSN

Before defining the DSN, complete the following the prerequisite steps:

  1. Set ODBCHOME env:
    >$ export ODBCHOME=/usr/local/SASHome/dfs/etc
  2. Set ODBCINI env:
    >$ export ODBCINI=$ODBCHOME/odbc.ini
  3. Set LD_LIBRARY_PATH env:
    >$ export LD_LIBRARY_PATH=/usr/local/SASHome/dfs/lib:$LD_LIBRARY_PATH
You can then follow the procedure in Using the DataDirect Driver Manager to set the driver encoding configure the DSN for SAS in the odbc.ini file. For example, in /usr/local/SASHome/dfs/etc/odbc.ini.

Note: The step to register the driver in odbcinst.ini is not required for SAS.

ODBC Driver for 1010data 2020

Using the ODBC Driver (from Code)

This section provides a reference for connecting to the driver using the ODBC libraries available in various languages.

ODBC Compliance

The driver is compliant with ODBC 3.8. The following sections list the supported ODBC APIs:

Language-Specific Help

The following sections describe using specific programming languages with the driver:

ODBC Driver for 1010data 2020

Supported Functions

Supported Functions

The ODBC Driver for 1010data 2020 supports the following ODBC API functions:

Function Conformance Level Status
SQLAllocConnect (Core) Supported
SQLAllocEnv (Core) Supported
SQLAllocStmt (Core) Supported
SQLBindCol (Core) Supported
SQLCancel (Core) Supported
SQLColAttributes (Core) Supported
SQLConnect (Core) Supported
SQLDescribeCol (Core) Supported
SQLDisconnect (Core) Supported
SQLError (Core) Supported
SQLExecDirect (Core) Supported
SQLExecute (Core) Supported
SQLFetch (Core) Supported
SQLFreeConnect (Core) Supported
SQLFreeEnv (Core) Supported
SQLFreeStmt (Core) Supported
SQLGetCursorName (Core) Not Supported
SQLNumResultCols (Core) Supported
SQLPrepare (Core) Supported
SQLRowCount (Core) Supported
SQLSetCursorName (Core) Not Supported
SQLSetParam (Core) Supported
SQLTransact (Core) Not Supported
SQLColumns (Core) Supported
SQLDriverConnect (Core) Supported
SQLGetConnectOption (Core) Supported
SQLGetData (Core) Supported
SQLGetFunctions (Core) Supported
SQLGetInfo (Core) Supported
SQLGetStmtOption (Core) Supported
SQLGetTypeInfo (Core) Supported
SQLParamData (Core) Supported
SQLPutData (Core) Supported
SQLSetConnectOption (Core) Supported
SQLSetStmtOption (Core) Supported
SQLSpecialColumns (Core) Supported
SQLStatistics (Core) Supported
SQLTables (Core) Supported
SQLBrowseConnect (Level 1) Supported
SQLColumnPrivileges (Level 2) Not Supported
SQLDataSources (Core) Supported
SQLDescribeParam (Level 2) Supported
SQLExtendedFetch (Level 2) Supported
SQLForeignKeys (Level 2) Supported
SQLMoreResults (Level 1) Not Supported
SQLNativeSql (Core) Supported
SQLNumParams (Core) Supported
SQLParamOptions (Core) Supported
SQLPrimaryKeys (Level 1) Supported
SQLProcedureColumns (Level 1) Supported
SQLProcedures (Level 1) Supported
SQLSetPos (Level 2) Not Supported
SQLSetScrollOptions (Level 2) Not Supported
SQLTablePrivileges (Level 2) Not Supported
SQLDrivers (Level 2) Supported
SQLBindParameter (Core) Supported
SQLAllocHandle (Core) Supported
SQLBindParam (Core) Supported
SQLCloseCursor (Core) Supported
SQLColAttribute (Core) Supported
SQLCopyDesc (Core) Supported
SQLEndTran (Core) Not Supported
SQLFetchScroll (Core) Supported
SQLFreeHandle (Core) Supported
SQLGetConnectAttr (Core) Supported
SQLGetDescField (Core) Supported
SQLGetDescRec (Core) Supported
SQLGetDiagField (Core) Supported
SQLGetDiagRec (Core) Supported
SQLGetEnvAttr (Core) Supported
SQLGetStmtAttr (Core) Supported
SQLSetConnectAttr (Core) Supported
SQLSetDescField (Core) Supported
SQLSetDescRec (Core) Supported
SQLSetEnvAttr (Core) Supported
SQLSetStmtAttr (Core) Supported
SQLBulkOperations (Level 1) Supported

ODBC Driver for 1010data 2020

Supported Data Types

Supported Types

The ODBC Driver for 1010data 2020 supports the following data types as column values:

SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
SQL_SMALLINT
SQL_INTEGER
SQL_BIGINT
SQL_REAL
SQL_DOUBLE
SQL_DECIMAL
SQL_BIT
SQL_TYPE_TIMESTAMP
SQL_TIMESTAMP
SQL_TYPE_TIME
SQL_TIME

ODBC Driver for 1010data 2020

From C/C++

This section provides a reference to ODBC compliance and a walk-through to writing ODBC data access code to 1010data in C/C++.

See Data Model for more information on the available API objects and any API limitations or requirements. See SQL Compliance for the SQL syntax.

Connecting from C/C++

See Getting Started for the prerequisite information you need to deploy the driver and configure the connection to 1010data. Connecting shows how to connect with ODBC functions.

Executing SQL

Use ODBC functions to execute SQL to 1010data:

  • See Querying Data to execute SELECT statements and iterate over the results.
  • See Parameterized Statements to execute parameterized statements. Parameterized statements provide a means to efficiently execute queries more than once and to mitigate SQL injection attacks.
  • See Discovering Schemas to obtain schema information, such as the available tables, columns, keys, and types. See Data Model to obtain the driver metadata by querying the available system tables.

Executing Stored Procedures

See Executing Stored Procedures to execute stored procedures as SQL statements.

ODBC Driver for 1010data 2020

Connecting

To create a connection, create an environment handle and a connection handle using SQLAllocHandle. You can then use SQLDriverConnect to provide a DSN or an ODBC connection string.

Connect to a DSN


  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); 
  SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
  SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 
  SQLDriverConnect(hdbc, 0, (SQLCHAR*)"Dsn=CData C1010 Source;", SQL_NTS, 0, 0, 0, 0);

Provide a Driver Connection String


  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); 
  SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
  SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 
  SQLDriverConnect(hdbc, 0, (SQLCHAR*)"DRIVER={ODBC Driver for C1010};User=user@company.com;Password=password;", SQL_NTS, 0, 0, 0, 0);

ODBC Driver for 1010data 2020

Querying Data

After Connecting, you can allocate a statement handle and execute the statement.

SELECT Procedure

Use SQLExecDirect to execute the statement and SQLFetch to fetch the records. You can use SQLBindCol to bind variables to columns in the result set. The numbering of the result set columns starts at 1.

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); 
SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 
SQLDriverConnect(hdbc, 0, (SQLCHAR*)"Dsn=CData C1010 Source", SQL_NTS, 0, 0, 0, 0);
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
SQLExecDirect(hstmt, (SQLCHAR*)"SELECT Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%'", SQL_NTS);  
SQLCHAR sFirstname[20] = {0};
SQLLEN cbFirstname = 0;
SQLBindCol(hstmt, 1, SQL_C_CHAR, sFirstname, 20, &cbFirstname);
SQLRETURN retcode = SQLFetch(hstmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
 ...
}

Iterating over the Results

You can use SQLGetData to get values while iterating through a cursor. To iterate over the results, use SQLFetch to fetch rows and SQLGetData to retrieve the column values.

  SQLHENV henv;
  SQLHDBC hdbc;
  SQLHSTMT hstmt;
  char slahmanid[255] = {0};
  SQLLEN cbId = 0;
  if (SQLAllocHandle(SQL_HANDLE_ENV, 0 ,&henv) == SQL_SUCCESS) {
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
    if (SQLAllocHandle(SQL_HANDLE_DBC, henv ,&hdbc) == SQL_SUCCESS) {
      if (SQLConnect(hdbc, "CData C1010 Source", SQL_NTS, 0, 0, 0, 0) == SQL_SUCCESS) {
        if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc ,&hstmt) == SQL_SUCCESS) {
          if (SQLExecDirect(hstmt, "SELECT lahmanid FROM pub.demo.baseball.master WHERE LastName = 'B%'", SQL_NTS) == SQL_SUCCESS) {
            while(SQLFetch(hstmt) == SQL_SUCCESS) {
              if (SQLGetData(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)slahmanid, 255, &cbId) == SQL_SUCCESS) {
                printf("lahmanid: %s\n", slahmanid);
              }
            }
          }
          SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        }
        SQLDisconnect(hdbc);
      }
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
  }

ODBC Driver for 1010data 2020

Parameterized Statements

The following code example shows how to bind parameters to create parameterized statements.

Binding Parameters

Use the SQLBindParameter function to bind the specified parameter position to the specified variable. Note that the parameter order starts at 1.

Example

The following example executes a parameterized SELECT and iterates over the results. You can use SQLExecDirect to execute any parameterized statement.

  SQLHENV henv;
  SQLHDBC hdbc;
  SQLHSTMT hstmt;
  char slahmanid[30] = {0};
  SQLLEN cbslahmanid = 0;
  char param[30] = {0};
  strcpy(param, "00190000007ABC");
  SQLLEN cbParam = SQL_NTS;
  if (SQLAllocHandle(SQL_HANDLE_ENV, 0 ,&henv) == SQL_SUCCESS) {
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
    if (SQLAllocHandle(SQL_HANDLE_DBC, henv ,&hdbc) == SQL_SUCCESS) {
      if (SQLConnect(hdbc, "CData C1010 Source", SQL_NTS, 0, 0, 0, 0) == SQL_SUCCESS) {
        if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc ,&hstmt) == SQL_SUCCESS) {
          SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 100, 0, (SQLPOINTER)param, 30, &cbParam);
          if (SQLExecDirect(hstmt, "SELECT lahmanid FROM pub.demo.baseball.master WHERE LastName = ?", SQL_NTS) == SQL_SUCCESS) {
            while(SQLFetch(hstmt) == SQL_SUCCESS) {
              if (SQLGetData(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)slahmanid, 255, &cbslahmanid) == SQL_SUCCESS) {
                printf("lahmanid: %s\n", slahmanid);
              }
            }
          }
          SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        }
        SQLDisconnect(hdbc);
      }
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
  }

ODBC Driver for 1010data 2020

Executing Stored Procedures

Procedure for Calling Stored Procedures

Use SQLExecDirect to call a stored procedure and SQLFetch and SQLGetData to iterate through its results. The following example shows the CALL syntax; you can also use the EXECUTE syntax, detailed in EXECUTE Statements.

Example


  SQLHENV henv;
  SQLHDBC hdbc;
  SQLLEN cbObjectName = SQL_NTS;
  if (SQLAllocHandle(SQL_HANDLE_ENV, 0 ,&henv) == SQL_SUCCESS) {
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0); 
    if (SQLAllocHandle(SQL_HANDLE_DBC, henv ,&hdbc) == SQL_SUCCESS) {
      if (SQLConnect(hdbc, "CData C1010 Source", SQL_NTS, 0, 0, 0, 0) == SQL_SUCCESS) {
        SQLHSTMT hstmt;
        SQLAllocHandle(SQL_HANDLE_STMT, hdbc ,&hstmt); 
        if (SQLExecDirect(hstmt, (SQLCHAR*)"{?=call CreateJob('Account', 'Insert')}", SQL_NTS) == SQL_SUCCESS) {
        if (SQLExecDirect(hstmt, "{?=call NA('NA')}", SQL_NTS) == SQL_SUCCESS) {
          char sCreatedById[255] = {0};
          SQLLEN cbsCreatedById = 0;
          while(SQLFetch(hstmt) == SQL_SUCCESS) {
            SQLGetData(hstmt, 2, SQL_C_CHAR, (SQLPOINTER)sCreatedById, 255, &cbsCreatedById);
            printf("CreatedById: %s\n", sCreatedById);
          }
        }
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        SQLDisconnect(hdbc);
      }
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
  }

ODBC Driver for 1010data 2020

Discovering Schemas

The following sections show how to obtain schema information in the ODBC API. See Data Model to obtain the driver metadata by querying the available system tables.

List Tables and Views

You can use the SQLTables function to list all available tables or views. The TableType parameter can be 'TABLE', 'VIEW', or both, and it is used to decide if tables or views are listed. After calling SQLTables, you can use SQLFetch and SQLBindCol to read the table listing. This is similar to reading columns from any SQL query, described in Querying Data.

SQLTables(hstmt, 0, 0, 0, 0, 0, 0, (SQLCHAR*)"'TABLE','VIEW'", SQL_NTS);

List Table Columns

You can use the SQLColumns function to list all columns of a specified table. After calling SQLColumns, you can use SQLFetch and SQLBindCol to read the column listing. This is similar to reading columns from any SQL query, described in Querying Data.

SQLColumns(hstmt, 0, 0, 0, 0, (SQLCHAR*)"DemoTable", SQL_NTS, 0, 0);

Get Column Information

You can use the SQLColAttribute function to get information about a column. The ColumnNumber parameter is the column index starting at 1. The FieldIdentifier parameter can be SQL_COLUMN_NAME, SQL_COLUMN_LENGTH, SQL_DESC_TYPE etc.

SQLCHAR columnName[30];
SQLSMALLINT cbColumnName;
SQLColAttribute(hstmt, 1, SQL_COLUMN_NAME, columnName, 30, &cbColumnName, NULL);

You can also use the SQLDescribeCol function to get the column name, type, size, decimal digits, and nullability of a column. The ColumnNumber parameter is the column index starting at 1.

SQLCHAR columnName[256];
SQLSMALLINT cbColumnName;
SQLSMALLINT dataType;
SQLULEN columnSize;
SQLSMALLINT decimalDigits;
SQLSMALLINT nullable;
SQLDescribeCol(hstmt, 1, columnName, 256, &cbColumnName, &dataType, &columnSize, &decimalDigits, &nullable);

List Primary Key Columns

You can use the SQLPrimaryKeys function to get the column name and the sequence number for the primary-key columns of a table. After calling SQLPrimaryKeys, use SQLFetch and SQLBindCol to read the table listing. This is similar to reading columns from any SQL query and is described in Querying Data.

SQLPrimaryKeys(hstmt, NULL, SQL_NTS, NULL, SQL_NTS, (SQLCHAR*)"DemoTable", SQL_NTS);

List Procedures

You can use the SQLProcedures function to list all available procedures. After calling SQLProcedures, you can use SQLFetch and SQLBindCol to read the table listing. This is similar to reading columns from any SQL query, described in Querying Data.

SQLProcedures(hstmt, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);

List Procedure Columns

You can use the SQLProcedureColumns function to list all columns of a specified procedure. After calling SQLProcedureColumns, you can use SQLFetch and SQLBindCol to read the table listing. This is similar to reading columns from any SQL query, described in Querying Data.

SQLProcedureColumns(hstmt, NULL, SQL_NTS, NULL, SQL_NTS, (SQLCHAR*)"DemoProcedure", SQL_NTS, NULL, SQL_NTS);

List Supported ODBC Functions

You can use the SQLGetFunctions function to determine whether a specific ODBC function is supported in the ODBC driver. The FunctionId (second parameter) can be SQL_API_ALL_FUNCTIONS or SQL_API_ODBC3_ALL_FUNCTIONS. The SupportedPtr (third parameter) should be a SQLUSMALLINT array of 100 elements. The call will set the SQLUSMALLINT element to true if the ODBC function is supported by the driver, and false otherwise.

SQLUSMALLINT functions[100];
SQLGetFunctions(hdbc, SQL_API_ALL_FUNCTIONS, functions);

Get Type Information

You can use the SQLGetTypeInfo function to return the information of the specified data type or all types. After calling SQLGetTypeInfo, use SQLFetch and SQLBindCol to read the information; see Querying Data for a code example.

SQLGetTypeInfo(hstmt, SQL_ALL_TYPES);

Get Number of Parameters

You can use the SQLNumParams function to get the parameter count of a query.

SQLSMALLINT paramCount;
SQLNumParams(hstmt, &paramCount);

Get Number of Columns

You can use the SQLNumResultCols function to get the column count of a table.

SQLSMALLINT columnCount;
SQLNumResultCols(hstmt, &columnCount);

Get Number of Rows

You can use the SQLRowCount function to get the row count of a table.

SQLLEN rowCount;
SQLRowCount(hstmt, &rowCount);

List Special Information for Columns

You can use the SQLSpecialColumns function to list all columns of a table with special information. The IdentifierType (second parameter) can be SQL_BEST_ROWID or SQL_ROWVER. The Scope (ninth parameter) can be SQL_SCOPE_CURROW, SQL_SCOPE_TRANSACTION or SQL_SCOPE_SESSION. After calling SQLSpecialColumns, use SQLFetch and SQLBindCol to read the column listing. This is similar to reading columns from any SQL query, described in Querying Data.

SQLSpecialColumns(hstmt, SQL_BEST_ROWID, NULL, 0, NULL,0, (SQLCHAR *)"DemoProcedure", SQL_NTS, SQL_SCOPE_SESSION, SQL_NULLABLE);

List Table Statistics Information

You can use the SQLStatistics function to list statistics and indices associated with a table. The IndexType (eighth parameter) can be SQL_INDEX_UNIQUE or SQL_INDEX_ALL. After calling SQLStatistics, you can use SQLFetch and SQLBindCol to read the table listing. This is similar to reading columns from any SQL query, described in Querying Data.

SQLStatistics(hstmt, NULL, 0, NULL, 0, (SQLCHAR*)"DemoProcedure", SQL_NTS, SQL_INDEX_UNIQUE, SQL_QUICK);

ODBC Driver for 1010data 2020

From Go

This section provides a walk-through to writing ODBC data access code to 1010data in Go, using the Go ODBC driver and the built-in sql package.

See Data Model for more information on the available API objects and any API limitations or requirements. See SQL Compliance for the SQL syntax.

Connecting from Go

See Getting Started for the prerequisite information you need to deploy the driver and configure the connection to 1010data.

Dependencies describes the necessary steps to install the Go ODBC driver. Once it is installed, refer to Connecting to connect to 1010data.

Executing SQL

Use SQL functions to execute SQL on 1010data:

  • See Querying Data to execute SELECT statements and iterate over the results.
  • See Parameterized Statements to execute parameterized statements. Parameterized statements provide a means to efficiently execute queries more than once and to mitigate SQL injection attacks.

Executing Stored Procedures

See Executing Stored Procedures to execute stored procedures as SQL statements.

ODBC Driver for 1010data 2020

Dependencies

This section describes the dependencies for using Go with the ODBC Driver for 1010data 2020.

Configure unixODBC (Linux and Mac Only)

Go ODBC uses unixOODBC on Linux and Mac, so make sure the driver is configured as described in Using unixODBC before continuing.

Install Go ODBC

Go ODBC can be installed through go get. You must also have Git installed for go get to download Go ODBC.

$ go get github.com/alexbrainman/odbc

Using Go ODBC

To use Go ODBC, you must import both the SQL package from the standard library as well as Go ODBC itself.

import (
        "database/sql"
        _ "github.com/alexbrainman/odbc"
)

ODBC Driver for 1010data 2020

Connecting

To connect to 1010data, you can use either a DSN or an ODBC connection string. Both of these methods use the sql package's Open function.

Connect to a DSN

To connect using a DSN:

db, _ := sql.Open("odbc", "DSN=CData C1010 Sys")
defer db.Close()

Provide a Driver Connection String

To connect using an ODBC connection string:

db, _ := sql.Open("odbc", "DRIVER={ODBC Driver for C1010};User=user@company.com;Password=password;;")
defer db.Close()

ODBC Driver for 1010data 2020

Querying Data

After Connecting, you can execute a SQL statement and retrieve the results.

SELECT Procedure

You can use Query to execute the statement, which will return a Row object you can use to fetch results.

rows, _ := db.Query("SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%'")
defer rows.Close()

Iterating over the Results

You can use Next to iterate through the rows in the resultset. To extract values from a row, use Scan to bind columns to local variables. The number of pointers given to Scan must match the number of columns in the resultset exactly, otherwise Scan will return an error.

for rows.Next() {
        var (
                lahmanid string
                Firstname string
        )

        rows.Scan(&lahmanid, &Firstname)
        fmt.Printf("lahmanid = %s, Firstname = %s\n", lahmanid, Firstname)
}

You may use types with Scan other than strings, as long as the data can be converted by the sql package. Please refer to the Scan function in the sql package documentation for supported types and type conversion rules.

ODBC Driver for 1010data 2020

Parameterized Statements

The following code example shows how to bind parameters to create parameterized statements.

Single-Use Statements

The Query and Exec functions both accept additional parameters for binding query parameters to values.

rows, _ := db.Query("SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = ?", "B%")
defer rows.Close()
for rows.Next() {
        var (
                lahmanid string
                Firstname string
        )

        rows.Scan(&lahmanid, &Firstname)
        fmt.Printf("lahmanid = %s, Firstname = %s\n", lahmanid, Firstname)
}

Reusable Statements

The Prepare function creates prepared Stmt objects, which can be re-used across multiple Query and Exec calls.

stmt, _ := db.Prepare("SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = ?")
defer stmt.Close()

rows, _ := stmt.Query("B% 1")
defer rows.Close()
for rows.Next() {
        var (
                lahmanid string
                Firstname string
        )

        rows1.Scan(&lahmanid, &Firstname)
        fmt.Printf("lahmanid = %s, Firstname = %s\n", lahmanid, Firstname)
}

rows, _ = stmt.Query("B% 2")
defer rows.Close()
for rows.Next() {
        var (
                lahmanid string
                Firstname string
        )

        rows2.Scan(&lahmanid, &Firstname)
        fmt.Printf("lahmanid = %s, Firstname = %s\n", lahmanid, Firstname)
}

ODBC Driver for 1010data 2020

Executing Stored Procedures

The following code example shows how to execute stored procedures and retrieve their results.

Procedure for Calling Stored Procedures

The Query function can be used to call a stored procedure with the EXECUTE syntax, as described in EXECUTE Statements. As with Querying Data, the results will be available through the Rows object.

rows, _ := db.Query("EXECUTE NA CreatedById = 'NA'")
defer rows.Close()

for rows.Next() {
        var (
                result string
        )

        rows.Scan(&result)
        fmt.Printf("result = %s\n", result)
}

ODBC Driver for 1010data 2020

Discovering Schemas

The following sections show how to obtain schema information.

List Tables and Views

Although the sql package does not provide a native interface to query tables and views, you can use the sys_tables and sys_views provided by the driver.

tables, _ := db.Query("SELECT CatalogName, SchemaName, TableName FROM sys_tables")
defer tables.Close()

for tables.Next() {
        var (
                catalog string
                schema string
                table string
        )

        rows.Scan(&catalog, &schema, &table)
        fmt.Printf("Catalog: %s, Schema: %s, Table: %s", catalog, schema, table)
}

views, _ := db.Query("SELECT CatalogName, SchemaName, TableName FROM sys_views")
defer views.Close()

for views.Next() {
        var (
                catalog string
                schema string
                view string
        )

        rows.Scan(&catalog, &schema, &view)
        fmt.Printf("Catalog: %s, Schema: %s, Table: %s", catalog, schema, table)
}

List Table Columns

You can use the sys_tablecolumns and sys_viewcolumns to get information about columns.

columns, _ := db.Query("SELECT ColumnName, DataType, Length, NumericPrecision, IsNullable FROM sys_tablecolumns WHERE TableName = 'pub.demo.baseball.master'")
defer columns.Close()

for columns.Next() {
        var (
                column string
                datatype int
                length int
                precision int
                nullable bool
        )

        rows.Scan(&column, &datatype, &length, &precision, &nullable)
        fmt.Printf("Name: %s, Type: %d, Length: %d, Precision: %d, Nullable: %t\n", column, datatype, length, precision, nullable)
}

ODBC Driver for 1010data 2020

From Node.js

This section provides a walk-through to writing ODBC data access code to 1010data in Node.js, using the node-odbc package.

See Data Model for more information on the available API objects and any API limitations or requirements. See SQL Compliance for the SQL syntax.

Connecting from Node.js

See Getting Started for the prerequisite information you need to deploy the driver and configure the connection to 1010data.

Dependencies describes the necessary steps to install the node-odbc package. Once it is installed, refer to Connecting to connect to 1010data.

Executing SQL

Use node-odbc functions to execute SQL on 1010data:

  • See Querying Data to execute SELECT statements and iterate over the results.
  • See Parameterized Statements to execute parameterized statements. Parameterized statements provide a means to efficiently execute queries more than once and to mitigate SQL injection attacks.

Executing Stored Procedures

See Executing Stored Procedures to execute stored procedures as SQL statements.

ODBC Driver for 1010data 2020

Dependencies

This section describes the dependencies for using Node.js with the ODBC Driver for 1010data 2020.

Configure unixODBC (Linux and Mac Only)

node-odbc uses unixODBC on Linux and Mac, so make sure the driver is configured as described in Using unixODBC before continuing.

Install node-odbc

You can install node-odbc using NPM.

npm install odbc

Using node-odbc

Before using node-odbc, you need to require it.

var Database = require("odbc").Database;

ODBC Driver for 1010data 2020

Connecting

To connect to 1010data, you can use either a DSN or an ODBC connection string. Both of these methods use the Database's Open method.

Connect to a DSN

To connect using a DSN:

var db = new Database();
db.open("DSN=CData C1010 Sys", (err) => {
    // Use the connection
});

Provide a Driver Connection String

To connect using an ODBC connection string:

var db = new Database();
db.open("DRIVER={ODBC Driver for C1010};User=user@company.com;Password=password;;", (err) => {
    // Use the connection
});

ODBC Driver for 1010data 2020

Querying Data

After Connecting, you can execute a SQL statement and retrieve the results.

SELECT Procedure

You can use the query method to execute the statement. It will split the query results into batches, and invoke a callback once for each batch. The rows are given to the callback as a list of objects, along with a flag indicating whether more rows are available.

db.open("...", (err) => {
    db.query("SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%'", (err, rows, moreRows) => {
        for (var i = 0; i < rows.length; i++) {
            var row = rows[i];
            console.log("lahmanid = " + row["lahmanid"]);
            console.log("Firstname = " + row["Firstname"]);
        }

        if (!moreRows) {
            console.log("All rows have been processed");
        }
    });
});

ODBC Driver for 1010data 2020

Parameterized Statements

The following code example shows how to bind parameters to create parameterized statements.

Single-Use Statements

The query method accepts an additional array parameter for binding query parameters to values.

db.open("...", (err) => {
     db.query(
        "SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = ?",
        ["B%"],
        (err, rows, moreRows) => {
        for (var i = 0; i < rows.length; i++) {
            console.log("lahmanid = " + rows["lahmanid"]);
            console.log("Firstname = " + rows["Firstname"]);
        }

        if (!moreRows) {
            console.log("All rows have been processed");
        }
    });
});

Reusable Statements

The prepare method creates prepared ODBCStatement objects, which can be re-used across multiple execute and executeNonQuery calls.

When you execute an ODBCStatement, your callback receives an ODBCResult object which is used to fetch results. It can be used to fetch all results at once into an array, or it can fetch rows one at a time.

db.open("...", (err) => {
    db.prepare(
        "SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = ?",
        (err, stmt) => {
        function printData(result, done) {
            result.fetch((err, row) => {
                if (row === null) done();

                console.log("lahmanid = " + row["lahmanid"]);
                console.log("Firstname = " + row["Firstname"]);
                printData(result);
            }
        }

        stmt.executeQuery("B% 1", (err, result) => {
            printData(result, () => {
                stmt.executeQuery("B% 2", (err, result) => {
                    printData(result);
                });
            });
        });
    });
});

ODBC Driver for 1010data 2020

Executing Stored Procedures

The following code example shows how to execute stored procedures and retrieve their results.

Procedure for Calling Stored Procedures

The query method can be used to call a stored procedure with the EXECUTE syntax, as described in EXECUTE Statements. As with Querying Data, the results will be given to a callback in batches.

db.open("...", (err) => {
    db.query("EXECUTE NA CreatedById = 'NA'", (err, rows, moreRows) => {
        for (var i = 0; i < rows.length; i++) {
            var row = rows[i];
            console.log("result = " + row["result"]);
        }

        if (!moreRows) {
            console.log("All rows have been processed");
        }
    });
});

ODBC Driver for 1010data 2020

Discovering Schemas

The following sections show how to obtain schema information.

List Tables and Views

You can use the tables method to discover what tables and views are available through the driver. This returns results through a callback like a query would.

db.tables(null, null, null, null, (err, tables_views)  => {
    for (var i = 0; i < tables_views.length; i++) {
        var row = tables_views[i];
        console.log("Catalog: " + row.TABLE_CAT +
                    ", Schema: " + row.TABLE_SCHEM +
                    ", Table: " + row.TABLE_NAME +
                    ", Type: " + row.TABLE_TYPE);
    }
});

You can use the columns method to discover what columns are available on a table or view.

db.columns(null, null, "pub.demo.baseball.master", null, (err, columns)  => {
    for (var i = 0; i < columns.length; i++) {
        var row = columns[i];
        console.log("Name: " + row.COLUMN_NAME +
                    ", Type: " + row.DATA_TYPE +
                    ", Length: " + row.COLUMNS_SIZE +
                    ", Precision: " + row.DECIMAL_DIGITS +
                    ", Nullable: " + row.IS_NULLABLE);
    }
});

ODBC Driver for 1010data 2020

From PHP

This section provides a walk-through to writing ODBC data access code to 1010data in PHP, using the build-in Unified ODBC API.

See Data Model for more information on the available API objects and any API limitations or requirements. See SQL Compliance for the SQL syntax.

Connecting from PHP

See Getting Started for the prerequisite information you need to deploy the driver and configure the connection to 1010data.

Dependencies describes the necessary steps to configure Unified ODBC. Once it is prepared, refer to Connecting to connect to 1010data.

Executing SQL

Use Unified ODBC functions to execute SQL on 1010data:

  • See Querying Data to execute SELECT statements and iterate over the results.
  • See Parameterized Statements to execute parameterized statements. Parameterized statements provide a means to efficiently execute queries more than once and to mitigate SQL injection attacks.

Executing Stored Procedures

See Executing Stored Procedures to execute stored procedures as SQL statements.

ODBC Driver for 1010data 2020

Dependencies

This section describes the dependencies for using PHP with the ODBC Driver for 1010data 2020.

Configure unixODBC (Linux Only)

PHP uses unixODBC on Linux, so make sure the driver is configured as described in Using unixODBC before continuing.

Install Unified ODBC

Linux

Most Linux distributions distribute ODBC support in their own packages apart from core PHP. Usually the package is named php-odbc.

# Debian, Ubuntu and related distributions
$ apt install php-odbc

# Red Hat, CentOS and related distributions
$ yum install php-odbc

Windows

On Windows, PHP is typically distributed with built-in ODBC support, so no extra packages are required.

Mac

On recent versions of Mac, the built-in PHP is not compiled with support for ODBC. Refer to the iODBC documentation for instructions on how to compile a version of PHP with iODBC support.

ODBC Driver for 1010data 2020

Connecting

To connect to 1010data, you can use either a DSN or an ODBC connection string. Both of these methods use the odbc_connect function.

Connect to a DSN

To connect using a DSN:

$cnx = odbc_connect("CData C1010 Sys", "", "");

Provide a Driver Connection String

To connect using an ODBC connection string:

$cnx = odbc_connect("DRIVER={ODBC Driver for C1010};User=user@company.com;Password=password;", "", "");

ODBC Driver for 1010data 2020

Querying Data

After Connecting, you can execute a SQL statement and retrieve the results.

SELECT Procedure

You can execute queries using odbc_exec. This will return a resource that you can use to read results.

$stmt = odbc_exec($cnx, "SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%'");

Iterating over the Results

You can use odbc_fetch_array to fetch the next row as an array.

while ($row = odbc_fetch_array($stmt)) {
    echo "lahmanid = ", $row["lahmanid"], "\n";
    echo "Firstname = ", $row["Firstname"], "\n";
}

ODBC Driver for 1010data 2020

Parameterized Statements

The following code example shows how to bind parameters to create parameterized statements.

Reusable Statements

The odbc_prepare function creates prepared statements, which can be re-used across multiple calls to odbc_execute. The statement object can be used to fetch results like a non-parameterized query.

$stmt = odbc_prepare($cnx, "SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = ?");

odbc_execute($stmt, array("B% 1"));
while ($row = odbc_fetch_array($stmt)) {
    echo "lahmanid = ", $row["lahmanid"], "\n";
    echo "Firstname = ", $row["Firstname"], "\n";
}

odbc_execute($stmt, array("B% 2"));
while ($row = odbc_fetch_array($stmt)) {
    echo "lahmanid = ", $row["lahmanid"], "\n";
    echo "Firstname = ", $row["Firstname"], "\n";
}

ODBC Driver for 1010data 2020

Executing Stored Procedures

The following code example shows how to execute stored procedures and retrieve their results.

Procedure for Calling Stored Procedures

The odbc_exec function called be used to call a stored procedure with the EXECUTE syntax, as described in EXECUTE Statements. As with Querying Data, the results can be obtained by iterating on the returned resource.

$stmt = odbc_exec($cnx, "EXECUTE NA CreatedById = 'NA'");
while ($row = odbc_fetch_array($stmt)) {
    echo "result = ", $row["result"], "\n";
}

ODBC Driver for 1010data 2020

Discovering Schemas

The following sections show how to obtain schema information.

List Tables and Views

You can use the odbc_tables function to discover what tables and views are available through the driver. This returns results through a resource like a query would.

$stmt = odbc_tables($cnx);
while ($row = odbc_fetch_array($stmt)) {
    echo "Catalog: ", $row["TABLE_CAT"], "\n";
    echo "Schema: ", $row["TABLE_SCHEM"], "\n";
    echo "Table: ", $row["TABLE_NAME"], "\n";
    echo "Type: ", $row["TABLE_TYPE"], "\n";
}

You can use the odbc_columns method to discover what columns are available on a table or view.

$stmt = odbc_columns($cnx);
while ($row = odbc_fetch_array($stmt)) {
    if ($row["TABLE_NAME"] == "pub.demo.baseball.master") {
        echo "Name: ", $row["COLUMN_NAME"], "\n";
        echo "Type: ", $row["DATA_TYPE"], "\n";
        echo "Length: ", $row["COLUMN_SIZE"], "\n";
        echo "Precision: ", $row["DECIMAL_DIGITS"], "\n";
        echo "Nullable: ", $row["IS_NULLABLE"], "\n";
    }
}

ODBC Driver for 1010data 2020

From Python

This section provides a walk-through to writing ODBC data access code to 1010data in Python, using the pyodbc module.

See Data Model for more information on the available API objects and any API limitations or requirements. See SQL Compliance for the SQL syntax.

Connecting from Python

See Getting Started for the prerequisite information you need to deploy the driver and configure the connection to 1010data.

Dependencies describes the necessary steps to install pyodbc. Once it is prepared, refer to Connecting to connect to 1010data.

Executing SQL

Use pyodbc functions to execute SQL on 1010data:

  • See Querying Data to execute SELECT statements and iterate over the results.
  • See Parameterized Statements to execute parameterized statements. Parameterized statements provide a means to efficiently execute queries more than once and to mitigate SQL injection attacks.

Executing Stored Procedures

See Executing Stored Procedures to execute stored procedures as SQL statements.

ODBC Driver for 1010data 2020

Dependencies

This section describes the dependencies for using Python with the ODBC Driver for 1010data 2020.

Configure unixODBC (Linux and Mac Only)

pyodbc uses unixODBC on Linux and Mac, so make sure your driver is configured as described in Using unixODBC before continuing.

Install pyodbc

You can use pip to install the module.

pip install pyodbc

Using pyodbc

To use pyodbc, you must import the pyodbc package.

import pyodbc

ODBC Driver for 1010data 2020

Connecting

To connect to 1010data, you can use either a DSN or an ODBC connection string. Both of these methods use the connect function.

Connect to a DSN

To connect using a DSN:

cnxn = pyodbc.connect("DSN=CData C1010 Sys;User=MyUser;Password=MyPassword")

Provide a Driver Connection String

To connect using an ODBC connection string:

cnxn = pyodbc.connect("DRIVER={ODBC Driver for C1010};User=user@company.com;Password=password;")

ODBC Driver for 1010data 2020

Querying Data

After Connecting, you can execute a SQL statement and retrieve the results.

SELECT Procedure

You will need to obtain a cursor before executing any SQL. Once you have one, you can use the cursor's execute method.

cursor = cnxn.cursor()
cursor.execute("SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%'")

Iterating over the Results

You can use a for loop to iterate over the rows of the resultset. Each row is presented as a tuple containing column values:

for (lahmanid, Firstname) in cursor:
    print("lahmanid = {}, Firstname = {}".format(lahmanid, Firstname))

ODBC Driver for 1010data 2020

Parameterized Statements

The following code example shows how to bind parameters to create parameterized statements.

Single-Use Statements

The execute method accepts an additional sequence for binding query parameters to values.

cursor.execute("SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = ?", ("B%",))
for (lahmanid, Firstname) in cursor:
    print("lahmanid = {}, Firstname = {}".format(lahmanid, Firstname))

Multi-Use Statements

The executemany method can be used to execute the same query repeatedly with different sets of parameters. Instead of a sequence of parameters, executemany accepts a nested sequence of parameters which are used for each execution.

executemany works only with INSERT, UPDATE and DELETE statements. It cannot be used with any operation that returns results, such as a SELECT or an EXECUTE.

params = [
  ("dana 1",),
  ("dana 2",),
  ("dana 3",),
]

cursor.executemany("INSERT INTO pub.demo.baseball.master(Firstname) VALUES (?)", params)

ODBC Driver for 1010data 2020

Executing Stored Procedures

The following code example shows how to execute stored procedures and retrieve their results.

Procedure for Calling Stored Procedures

The execute method can be used to call a stored procedure with the EXECUTE syntax, as described in EXECUTE Statements. As with Querying Data, the results will be available through the cursor.

cursor.execute("EXECUTE NA CreatedById = 'NA'")
for (result,) in cursor:
    print("result = ", result)

ODBC Driver for 1010data 2020

Discovering Schemas

The following sections show how to obtain schema information.

List Tables and Views

You can use the tables method to discover what tables and views are available through the driver. This returns results on the cursor like a query would.

cursor.tables()
for (catalog, schema, table, table_type, description) in cursor:
    print("Catalog: {}, Schema: {}, Table: {}, Type: {}".format(
        catalog, schema, table, table_type
    ))

You can use the columns method to discover what columns are available on a table or view.

cursor.columns("pub.demo.baseball.master")
for row in cursor:
    print("Name: {}, Type: {}, Length: {}, Precision: {}, Nullable: {}".format(
        row[3], row[4], row[6], row[8], row[17]
    ))

ODBC Driver for 1010data 2020

From R

This section provides a walk-through to writing ODBC data access code to 1010data in R, using the RODBC module.

See Data Model for more information on the available API objects and any API limitations or requirements. See SQL Compliance for the SQL syntax.

Connecting from R

See Getting Started for the prerequisite information you need to deploy the driver and configure the connection to 1010data.

Dependencies describes the necessary steps to install RODBC. Once it is prepared, refer to Connecting to connect to 1010data.

Executing SQL

Use RODBC functions to execute SQL on 1010data:

  • See Querying Data to execute SELECT statements and iterate over the results.
  • See Parameterized Statements to execute parameterized statements. Parameterized statements provide a means to efficiently execute queries more than once and to mitigate SQL injection attacks.

Executing Stored Procedures

See Executing Stored Procedures to execute stored procedures as SQL statements.

ODBC Driver for 1010data 2020

Dependencies

This section describes the dependencies for using R with the ODBC Driver for 1010data 2020.

Configure unixODBC (Linux and Mac Only)

RODBC uses unixODBC on Linux and Mac, so make sure your driver is configured as described in Using unixODBC before continuing.

Install RODBC

You can install the latest RODBC package from CRAN using install.packages.

install.packages("RODBC")

If you are interested in using parameterized queries, you should install RODBCext as well. RODBCext is an add-on library to RODBC that adds support for parameterized queries.

install.packages("RODBCext")

Using RODBC

To use RODBC, you must load the RODBC and RODBCext libraries.

library(RODBC)
library(RODBCext)

ODBC Driver for 1010data 2020

Connecting

To connect to 1010data, you can use either a DSN or an ODBC connection string. Both of these methods use the odbcConnect function.

Connect to a DSN

To connect using a DSN:

cnx <- odbcConnect("CData C1010 Sys")

Provide a Driver Connection String

To connect using an ODBC connection string:

cnx <- odbcDriverConnect(connection = "DRIVER={ODBC Driver for C1010};User=user@company.com;Password=password;")

ODBC Driver for 1010data 2020

Querying Data

After Connecting, you can execute a SQL statement and retrieve the results.

SELECT Procedure

You can use the sqlQuery function to execute queries. By default, it will return all the results as a single data frame. This works well for smaller result sets.

results <- sqlQuery(cnx, "SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%'")
for (row in 1:nrow(results)) {
    cat(paste("lahmanid = ", results[row,]$lahmanid, "\n"))
    cat(paste("Firstname = ", results[row,]$Firstname, "\n"))
}

Iterating over the Results

For larger result sets, sqlQuery can return results in batches instead of storing the entire result set in memory. When there are no results left, or an error occurs, sqlQuery and sqlGetResults will return an integer error code instead of a data frame.

results <- sqlQuery(cnx, "SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%'", max = 1000)
while (is.data.frame(results)) {
    for (row in 1:nrow(results)) {
        cat(paste("lahmanid = ", results[row,]$lahmanid))
        cat(paste("Firstname = ", results[row,]$Firstname))
    }
    rows <- sqlGetResults(cnx, max = 1000)
}

ODBC Driver for 1010data 2020

Parameterized Statements

The following code example shows how to bind parameters to create parameterized statements.

Single-Use Statements

The sqlExecute function from RODBCext accepts an additional data frame for binding query parameters to values.

sqlExecute(
    cnx,
    query = "SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = ?",
    data = data.frame("B%")
)

results <- sqlGetResults(cnx, max = 1000)
while (is.data.frame(results)) {
    for (row in 1:nrow(results)) {
        cat(paste("lahmanid = ", results[row,]$lahmanid, "\n"))
        cat(paste("Firstname = ", results[row,]$Firstname, "\n"))
    }
    results <- sqlGetResults(cnx, max = 1000)
}

ODBC Driver for 1010data 2020

Executing Stored Procedures

The following code example shows how to execute stored procedures and retrieve their results.

Procedure for Calling Stored Procedures

The sqlQuery function can be used to call a stored procedure with the EXECUTE syntax, as described in EXECUTE Statements. As with Querying Data, the results will be returned as a data frame directly from sqlQuery.

results <- sqlQuery("EXECUTE NA CreatedById = 'NA'")
for (row in 1:nrow(results)) {
    cat(paste("result = ", results[row,]$result, "\n"))
}

ODBC Driver for 1010data 2020

Discovering Schemas

The following sections show how to obtain schema information.

List Tables and Views

You can use the sqlTables function to discover what tables and views are available through the driver. This returns results in a data frame like a query would.

results <- sqlTables(cnx)
for (row in 1:nrow(results)) {
    cat(paste("Catalog: ", results[row,]$TABLE_CAT,
              ", Schema: ", results[row,]$TABLE_SCHEM,
              ", Table: ", results[row,]$TABLE_NAME,
              ", Type: ", results[row,]$TABLE_TYPE, "\n"))
}

You can use the sqlColumns function to discover what columns are available on a table or view.

results <- sqlColumns(cnx, "pub.demo.baseball.master")
for (row in 1:nrow(results)) {
    cat(paste("Name: ", results[row,]$COLUMN_NAME,
              ", Type: ", results[row,]$DATA_TYPE,
              ", Length: ", results[row,]$COLUMN_SIZE,
              ", Precision: ", results[row,]$DECIMAL_DIGITS,
              ", Nullable: ", results[row,]$IS_NULLABLE, "\n")
}

ODBC Driver for 1010data 2020

Linked Server

CData supports creating a linked server in SQL Server Management Studio with our ODBC drivers. This section describes how to create a linked server and then execute queries from that linked server.

Create a Linked Server

See Create a Linked Server for information on creating a linked server in SQL Server Management Studio.

Linked server support is enabled through the ODBC remoting feature. The ODBC remoting feature allows you to connect to the ODBC driver from remote machines. You connect to each ODBC data source as a virtual MySQL or SQL Server database, communicating through the MySQL or TDS (SQL Server) protocols. ODBC remoting is managed through the CData SQL Gateway application. The remoting feature is only available in the Windows edition.

Execute Queries on a Linked Server

See Execute Queries on a Linked Server for information on executing queries to the 1010data linked server from any tool that can connect to SQL Server.

Connect from MySQL Workbench

See Connect from MySQL Workbench for information on configuring ODBC remoting and accessing 1010data as a virtual MySQL database in MySQL Workbench.

ODBC Driver for 1010data 2020

Create a Linked Server

This section describes how to configure and create a linked SQL Server instance.

Linked Server Benefits

You can use SQL Linked Server to connect to the CData SQL Gateway. SQL Server treats the CData SQL Gateway as a linked SQL Server instance, so you can write full SQL queries to query your 1010data data without losing performance as would happen connecting to an ODBC data source directly. The connection can be made locally or to the Gateway located on a separate machine.

Configure the Linked Server

Configuring remote access to an ODBC data source in the SQL Gateway administration tool consists of the following steps:

  1. Configure the proxying service on the Services tab.
  2. Configure database users on the Users tab.
  3. Configure TLS/SSL on the Other tab.
  4. Access services hosted behind a firewall by configuring the Tunneling.

Create a Linked Server from the UI

To create a linked server from the UI in SQL Server Management Studio:

  1. Open SQL Server Management Studio and connect to an instance of SQL Server.
  2. Expand the Server Objects node, right-click Linked Servers, and click New Linked Server. The New Linked Server dialog is displayed.
  3. In the General section, select Other Data Source and enter the following information after naming the linked server:
    • Provider: Select the SQL Server Native Client Provider that corresponds to your version of SQL Server. For example, SQL Server Native Client 11.0.
    • Data Source: Enter the host and port the service is running on, separated by a comma. For example, "localhost,1434". If you are connecting off-network through the SSH Tunnel enter the host and port of your SSH host. Note that a value of "localhost" in the Data Source input refers to the machine where SQL Server is running, so be careful when creating a linked server in Management Studio when not running on the same machine as SQL Server.
    • Catalog: Enter the system DSN as the catalog. For example, "CData C1010 Sys".
  4. In the Security section, select Be Made Using this Security Context and enter the username and password of a user you authorized on the SSH Tunnel tab in the SQL Gateway administration console.

Create a Linked Server Programmatically

In addition to using the SQL Server Management Studio UI to create a linked server, you can use stored procedures. The following inputs are required to create the linked server:

  • Server: The linked server name.
  • Provider: Enter "SQLNCLI", for the SQL Server Native Client Provider.
  • Datasrc: The host and port the service is running on, separated by a comma. Note that a value of "localhost" in the datasrc input refers to the machine where SQL Server is running, so be careful when creating a linked server in Management Studio when not running on the same machine as SQL Server.
  • Catalog: Enter the system DSN configured for the service.
  • Srvproduct: Enter the product name of the data source; this can be an arbitrary value, such as "CData SQL Gateway" or an empty string.

To create the linked server and configure authentication to the SQL Gateway:

  1. Call sp_addlinkedserver to create the linked server:
    EXEC sp_addlinkedserver @server='C1010',
      @srvproduct = '',
      @provider='SQLNCLI',
      @datasrc='<Machine IP address>,1434',
      @catalog='CData C1010 Sys';
    GO
  2. Call sp_addlinkedsrvlogin to allow SQL Server users to connect with the credentials of an authorized user of the service. Note that the credentials you use to connect to the service must map to a user you defined on the Users tab in the SQL Gateway administration tool.
    EXEC sp_addlinkedsrvlogin @rmtsrvname='C1010',
      @rmtuser='admin',
      @rmtpassword='test',
      @useself='FALSE',
      @locallogin=NULL;
    GO

Connect from SQL Server Management Studio

SQL Server Management Studio uses the SQL Server Client OLE DB provider, which requires the ODBC driver to be used inprocess. You must enable the Allow inprocess option for the SQL Server Client Provider in Management Studio to query the linked server from SQL Server Management Studio. To do this, open the properties for the provider you are using under Server Objects > Linked Servers > Providers.

ODBC Driver for 1010data 2020

Execute Queries on a Linked Server

This section describes how to execute queries to the 1010data linked server from any tool that can connect to SQL Server.

When executing queries from a linked server, you must use a four-part name format, as shown below.

[<Linked Server Name>].[<DSN Name>].[<Schema Name (normally but not always the service name)>].[<Table Name>]

Where:

  • Linked Server Name is the name of the linked server that you created.
  • DSN Name is the name of the data source.
  • Schema Name is the name of the system schema or user-defined schema.
  • Table Name is the name of the table you are copying data from.

SELECT Statements

To create new records:

SELECT * FROM [Linked Server Name].[CData C1010 Sys].[C1010].[pub.demo.baseball.master] WHERE LastName = 'B%'

ODBC Driver for 1010data 2020

Connect from MySQL Workbench

The ODBC Driver for 1010data 2020 integrates with MySQL clients through MySQL virtualization managed through the CData SQL Gateway application. The SQL Gateway enables you to create MySQL interfaces for ODBC data sources. Clients like MySQL Workbench can connect to 1010data ODBC data sources as virtual MySQL databases.

This section describes how to configure ODBC remoting and access 1010data as a virtual MySQL database in MySQL Workbench.

Configure ODBC Remoting

Creating a virtual MySQL database in the SQL Gateway administration tool consists of the following steps:

  1. Configure and start the proxying service on the Services tab.
  2. Configure database users on the Users tab.
  3. If you need to connect over TLS/SSL, configure it on the Other tab.
  4. Access services hosted behind a firewall by configuring Tunneling.

Connect to the Database

Click Database > Connect to Database in the main menu. Name the connection and enter the following information in the resulting dialog:

Hostname and PortEnter the hostname and port of the service or, if you are connecting from off network, enter the location of the Tunneling.
UsernameEnter the username of a user account you created in the SQL Gateway.

When you click Test Connection, you are prompted for the password and also to accept the SQL Gateway's digital certificate, if you enabled SSL when you configured the service. You can configure the certificate on the Other tab.

Query the Database

After connecting to the database, you can browse metadata in the Schemas section of the Navigator pane. You can also visually build queries. For example, right-click a table and select Select Rows - Limit 1000.

ODBC Driver for 1010data 2020

SQL Gateway

The CData SQL Gateway enables remote access to ODBC data sources as virtual SQL Server or MySQL databases. The SQL Gateway application listens on a configured port for requests made in the MySQL or TDS (SQL Server) wire protocols and responds to those by reading from and writing to the ODBC data source. This allows you to connect to any ODBC DSN from remote machines, using any tool or application that can work with either MySQL or SQL Server.

In addition to brokering the SQL request to ODBC DSNs, the SQL Gateway also makes it easy to securely connect to data from across the firewall. You can configure the SQL Gateway to create an SSH reverse tunnel to a machine that is outside the firewall with just a few simple settings. Any connections made to this external machine are then relayed securely to the SQL Gateway instance within the firewall. Connections are secured via TLS/SSL and standard access control mechanisms available in MySQL and SQL Server. SSH reverse tunneling support is especially useful in allowing cloud services to access on-premises data.

Configuring Remoting Services

To enable remote access, you create a service that listens for database queries over the MySQL or TDS (SQL Server) protocols and then proxies them to the ODBC driver tied to the DSN. You can create remoting services for any ODBC driver.

The MySQL and TDS remoting services you create can be managed on the Services tab in the CData SQL Gateway administration tool. Define access control lists on the Users tab to manage access to your created services.

Hosting Services Behind the Firewall

The Gateway feature enables you to build an SSH reverse tunnel to an SSH host outside the firewall. The Gateway can also manage the reverse tunnel connection, reconnecting if the connection is lost or interrupted, without any need for a third-party tool.

Configure the SSH reverse tunnel on the Tunneling tab.

ODBC Driver for 1010data 2020

Services

On the Services tab, you configure services to proxy MySQL or SQL Server connections to ODBC drivers. To run services as Windows services, select "Run as a Windows Service" on the Status tab.

For additional connection settings, such as configuring TLS/SSL, see Other.

Service Settings

The following settings configure the proxy service. Note that you must have Windows administrator access, as the SQL Gateway connects through system DSNs.

  • Service Name: Specify the name of the database.
  • SQL Server or MySQL: Select the database to emulate.
  • Data Source: Select the system DSN for the ODBC data source you want to access as a SQL Server or MySQL database.

  • SSL: Select this option to use TLS/SSL to encrypt client connections. You must configure TLS/SSL for SQL Server databases. See Other to configure the server certificate.
  • Port: Select the port the service will listen on.
  • Expose on Gateway: Enable this option to allow remote access to firewalled databases through an SSH server. You need to first configure the SSH Tunnel feature on the Tunneling tab. If the SSH server is running, the SQL Gateway will automatically select a port on the server: When connecting to the SSH server, specify the Remote Port value.

Startup Settings

Windows services have the following startup options:

  • Automatic: The service automatically starts at boot.

  • Manual: Services configured for manual startup can be started manually or from an application, such as the SQL Gateway or a script.
  • Disabled: The service can only be started manually from the Services snap-in to the Microsoft Management Console or the Task Manager.
You can start Windows services from the SQL Gateway's Services tab or from the Services snap-in to the Microsoft Management Console. To load the snap-in, enter "run" in the Start menu and then enter "services.msc".

Logging Options

Basic information about the service, such as startup and shutdown, is displayed on the Status tab.

To retrieve more verbose service-specific logs, select the Log Mode option and enable the Write Log to a File option on the service configuration dialog accessed on the Services tab.

The logging options on the Other tab will log to a file when the configured services start and stop and also any errors encountered in the SQL Gateway app itself.

Managing Incoming Connections

Advanced database connection settings, such as the idle connection timeout, are available on the Other tab.

ODBC Driver for 1010data 2020

Users

On the Users tab, you can control access to services through privileged user accounts. The following options are available to add or edit a user:

  • User: Enter the username.
  • Password: Enter the password for the user.
  • Privilege Settings: Specify whether the user is an administrator of the SQL Gateway. This setting applies to all services. Or, select an access scope for each service you want to allow the adminstrator access to: full, read-only, or none.

ODBC Driver for 1010data 2020

Status

The Status tab reports basic information about each service. On this tab you can also configure whether SQL Gateway services run as Windows services.

To obtain database logs, see Services. To obtain SQL Gateway logs, see Other.

ODBC Driver for 1010data 2020

Tunneling

The Gateway simplifies hosting services behind a firewall: It enables you to tunnel connections through an SSH server.

Configuring Local Database Access

The connection from the SQL Gateway to the SSH server is a reverse SSH tunnel; after authenticating the SQL Gateway, the server automatically forwards remote connections.

Along with the Host and Port in the Remote Server section, you need to specify the credentials needed to perform SSH authentication. You also need to have already created a service and selected the "Enable on Gateway" option on the Services tab.

Authenticating to the Server

Select one of the following options in the Auth Mode menu in the Remote Server section and then click Test SSH Connection to initiate a login to the SSH server.

Password Authentication

In this authentication method, the SQL Gateway logs in with the username and password of a user account on the machine running the SSH server. The password is securely sent through the encrypted SSH tunnel.

Public Key Authentication

You will need a key pair to perform this authentication method. Specify a digital certificate or create a certificate to obtain a key pair: Click the button in the Certificate box.

  • Private Key: Select the private key from the Windows certificate stores, from a .pfx file, or from a .pem file.

  • Public Key: Click Export Public Key to save the public key as an OpenSSH public key, an SSH2 public key, or an X.509 public key certificate.

The public key is configured on the server and used to encrypt data that can only be decrypted by the private key. The SQL Gateway uses the private key to decrypt data packets from the server and to authenticate.

Managing SSH

In the Advanced Settings section, you can configure SSH connection behavior:

  • Timeout: Configure the seconds the connection is allowed to idle.
  • Auto Reconnect: Select whether the SQL Gateway will attempt to reconnect when the SSH connection is lost.
  • Reconnect Count: Enter the number of reconnection attempts the SQL Gateway will make when the connection is lost. A value of -1 signifies that the SQL Gateway will attempt to reconnect indefinitely.
  • Reconnect Interval: Select the retry interval, in seconds.

ODBC Driver for 1010data 2020

Other

On the Other tab, you can configure TLS/SSL and more advanced connection settings. You can also configure logging for the SQL Gateway.

Securing Connections with TLS/SSL

You must configure SSL for SQL Server connections. The certificate specified on this tab will also be used for SSL-enabled MySQL connections.

A default certificate is shipped for testing purposes; this certificate is not suitable for production.

To secure database connections with SSL, specify a digital certificate by clicking the button in the SSL Cert box. Configure the SQL Gateway with the private key in the resulting dialog.

You can select a private key from the available certificate stores. Or, click Create New Certificate on the System Store tab to create a new key pair. Select an existing private key from the system store, the user store, a PFX file, or a PEM file.

After configuring the private key in the SQL Gateway, clients need to be able to trust the public key. To trust the key, clients validate the certificate's chain of trust.

A self-signed certificate, such as a certificate you create with the SQL Gateway, is the only certificate in the chain. To trust a self-signed certificate, you must manually validate and accept the certificate.

One way to accept the certificate is to add it to the trusted system store. Your application may require a different store.

Logging Application Info

To record SQL Gateway activity, enable the Write Log to a File option and specify the logfile, verbosity, and log rotation interval.

ODBC Driver for 1010data 2020

SQL Compliance

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 driver'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').

ODBC Driver for 1010data 2020

SELECT Statements

A SELECT statement can consist of the following basic clauses.

  • 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 driver:

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%'
    

ODBC Driver for 1010data 2020

Aggregate Functions

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%'

ODBC Driver for 1010data 2020

JOIN Queries

This section provides information about the features and restrictions that are specific to how the ODBC Driver for 1010data 2020 supports joins. The ODBC Driver 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

ODBC Driver for 1010data 2020

SELECT INTO Statements

You can use the SELECT INTO statement to export formatted data to a file.

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

ODBC Driver for 1010data 2020

CACHE Statements

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 driver 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.

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 driver 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 driver 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 driver 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 driver creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA

If this option is set, the driver 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

ODBC Driver for 1010data 2020

EXECUTE Statements

To execute stored procedures, you can use EXECUTE or EXEC statements.

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; 

ODBC Driver for 1010data 2020

Caching Data

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 driver'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 driver 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.

ODBC Driver for 1010data 2020

Configuring the Cache Connection

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 driver 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.

ODBC Driver for 1010data 2020

Automatically Caching Data

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 driver automatically initializes and builds a cache in the background. When AutoCache = true, the driver 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%'

SQLHSTMT hstmt;
SQLTCHAR connectString[1024];
SQLSMALLINT cbconnectString;
SQLDriverConnect(hdbc, 0, (SQLTCHAR*)"DSN=CData C1010 Source;AutoCache=true;Cache Location=C:\\cache.db", SQL_NTS, connectString, 1024, &cbconnectString, SQL_DRIVER_COMPLETE);
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
SQLExecDirect(hstmt, (SQLCHAR*)"SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%'", SQL_NTS);
while(SQLFetch(hstmt) == SQL_SUCCESS) {}
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

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.

ODBC Driver for 1010data 2020

Explicitly Caching 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 driver.

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%'

SQLCHAR *cmd = (SQLCHAR*)"CACHE SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%'";
SQLExecDirect(hstmt, cmd, SQL_NTS);

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%'
  

SQLCHAR *cmd = (SQLCHAR*)"CACHE WITH TRUNCATE SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%'";
SQLExecDirect(hstmt, cmd, SQL_NTS);

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 driver 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.

ODBC Driver for 1010data 2020

Data Type Mapping

The driver 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

ODBC Driver for 1010data 2020

Data Model

The ODBC Driver for 1010data 2020 models 1010data entities as relational Tables, Views, and Stored Procedures.

ODBC Driver for 1010data 2020

System Tables

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:

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 driver 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.

ODBC Driver for 1010data 2020

sys_catalogs

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.

ODBC Driver for 1010data 2020

sys_schemas

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.

ODBC Driver for 1010data 2020

sys_tables

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.

ODBC Driver for 1010data 2020

sys_tablecolumns

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.

ODBC Driver for 1010data 2020

sys_views

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.

ODBC Driver for 1010data 2020

sys_viewcolumns

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.

ODBC Driver for 1010data 2020

sys_procedures

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.

ODBC Driver for 1010data 2020

sys_procedureparameters

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.

ODBC Driver for 1010data 2020

sys_keycolumns

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.

ODBC Driver for 1010data 2020

sys_indexes

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.

ODBC Driver for 1010data 2020

sys_connection_props

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.

ODBC Driver for 1010data 2020

sys_sqlinfo

Describes the SELECT query processing that the driver 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.

NamePossible Values
AGGREGATE_FUNCTIONSAVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNTYES
SUPPORTED_OPERATORS =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BYNO_RELATION
OUTER_JOINSYES
OJ_CAPABILITIESNESTED, LEFT, RIGHT, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
SUBQUERIESCOMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONSASCII,CHAR,CONCAT,LEFT,LTRIM,REPLACE,RIGHT,RTRIM,SOUNDEX,SPACE,SUBSTRING
NUMERIC_FUNCTIONSABS,ACOS,ASIN,ATAN,CEILING,COS,COT,DEGREES,EXP,FLOOR,LOG,LOG10,PI,POWER,RADIANS,RAND,ROUND,SIGN,SIN,SQRT,TAN
TIMEDATE_FUNCTIONSCURRENT_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.

ODBC Driver for 1010data 2020

sys_identity

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.

ODBC Driver for 1010data 2020

Connection String Options

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 CacheAutomatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider .
Cache ConnectionThe 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 LocationSpecifies the path to the cache when caching to a file.
Cache MetadataThis property determines whether or not to cache the table metadata to a file store.
Cache ProviderThe name of the provider to be used to cache data.
Cache SessionThis option changes whether the provider stores the current session when you log in.
Cache ToleranceThe tolerance for stale data in the cache specified in seconds when using AutoCache .
Default DomainThis property is used for the Oracle Database Gateway for ODBC.
Enable Foreign Key DetectionWhether to detect the foreign keys in ODBC.
Firewall PasswordA password used to authenticate to a proxy-based firewall.
Firewall PortThe TCP port for a proxy-based firewall.
Firewall ServerThe name or IP address of a proxy-based firewall.
Firewall TypeThe protocol used by a proxy-based firewall.
Firewall UserThe user name to use to authenticate with a proxy-based firewall.
Include Dual TableSet this property to mock the Oracle DUAL table for better compatibility with Oracle database.
Kill SessionWhether to kill the any existing sessions under your account.
Limit Key SizeThe maximum length of a primary key column.
LocationA path to the directory that contains the schema files defining tables, views, and stored procedures.
LogfileA path to the log file.
Login URLThe URL of your 1010data instance.
Map To IntThis property controls whether or not the long type maps to SQL_INTEGER instead of SQL_BIGINT. This property is false by default.
Map To Long VarcharThis property controls whether or not a column is returned as SQL_LONGVARCHAR.
Map To WVarcharThis property controls whether or not string types map to SQL_WVARCHAR instead of SQL_VARCHAR. This property is set by default.
Maximum Column SizeThe maximum column size.
Max Log File CountA 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 SizeA 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 RowsLimits 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.
OfflineUse offline mode to get the data from the cache instead of the live source.
OtherThese hidden properties are used only in specific use cases.
Page SizeThe number of results to return per request to 1010data.
PasswordYour 1010data password.
Proxy Auth SchemeThe authentication type to use to authenticate to the ProxyServer proxy.
Proxy Auto DetectThis 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 ExceptionsA semicolon separated list of hosts or IPs that are exempt from connecting through the ProxyServer .
Proxy PasswordA password to be used to authenticate to the ProxyServer proxy.
Proxy PortThe TCP port the ProxyServer proxy is running on.
Proxy ServerThe hostname or IP address of a proxy to route HTTP traffic through.
Proxy SSL TypeThe SSL type to use when connecting to the ProxyServer proxy.
Proxy UserA user name to be used to authenticate to the ProxyServer proxy.
Pseudo ColumnsThis property indicates whether or not to include pseudo columns as columns to the table.
Query PassthroughThis option passes the query to the 1010data server as is.
RTKThe runtime key used for licensing.
SSL Server CertThe certificate to be accepted from the server when connecting using TLS/SSL.
TablesThis property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
TimeoutThe value in seconds until the timeout error is thrown, canceling the operation.
Upper Case IdentifiersThis property reports all identifiers in uppercase. This is the default for Oracle databases and thus allows better integration with Oracle tools such as the Oracle Database Gateway.
UserYour 1010data login name.
VerbosityThe verbosity level that determines the amount of detail included in the log file.
ViewsRestricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.
ODBC Driver for 1010data 2020

Auto Cache

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 driver automatically maintains a cache of your table's data in the database of your choice.

Setting the Caching Database

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

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.

ODBC Driver for 1010data 2020

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.

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 driver 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.

ODBC Driver for 1010data 2020

Cache Location

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 driver 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.

ODBC Driver for 1010data 2020

Cache Metadata

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 driver 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.

ODBC Driver for 1010data 2020

Cache Provider

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 driver 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;

ODBC Driver for 1010data 2020

Cache Session

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 driver 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 driver 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 driver 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.

ODBC Driver for 1010data 2020

Cache Tolerance

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 driver checks with the data source for newer records after the tolerance interval has expired. Otherwise, it returns the data directly from the cache.

ODBC Driver for 1010data 2020

Default Domain

This property is used for the Oracle Database Gateway for ODBC.

Data Type

string

Default Value

""

Remarks

The Oracle Database Gateway will always truncate the username from the "@" character. When DefaultDomain is specified, an "@" character and the domain will be appended to the username before authenticating.

ODBC Driver for 1010data 2020

Enable Foreign Key Detection

Whether to detect the foreign keys in ODBC.

Data Type

bool

Default Value

false

Remarks

Whether to detect the foreign keys in ODBC.

ODBC Driver for 1010data 2020

Firewall Password

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.

ODBC Driver for 1010data 2020

Firewall Port

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.

ODBC Driver for 1010data 2020

Firewall Server

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 driver uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

ODBC Driver for 1010data 2020

Firewall Type

The protocol used by a proxy-based firewall.

Data Type

string

Default Value

"NONE"

Remarks

This property specifies the protocol that the driver will use to tunnel traffic through the FirewallServer proxy. Note that by default, the driver 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 driver opens a connection to 1010data and traffic flows back and forth through the proxy.
SOCKS4 1080 When this is set, the driver 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 driver 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.

ODBC Driver for 1010data 2020

Firewall User

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.

ODBC Driver for 1010data 2020

Include Dual Table

Set this property to mock the Oracle DUAL table for better compatibility with Oracle database.

Data Type

bool

Default Value

false

Remarks

Set this property to mock the Oracle DUAL table. This table is used by Oracle database in a few special cases. This property facilitates connectivity when accessing 1010data as a remote Oracle database through the ODBC Gateway. For example, in SQL Developer, this table is queried to test the connection.

When using the ODBC Gateway, set the following properties, as well:

MapToWVarchar=False Set this property to map string data types to SQL_VARCHAR instead of SQL_WVARCHAR. By default, the driver uses SQL_WVARCHAR to accommodate various international character sets. You can use this property to avoid the ORA-28528 Heterogeneous Services data type conversion error when the Unicode type is returned.
MaximumColumnSize=4000 Set this property to restrict the maximum column size to 4000 characters.
UpperCaseIdentifiers=True Set this property to avoid quoting identifiers. Use this property to report uppercase identifiers to Oracle. By default, Oracle stores identifiers in uppercase, so uppercase identifiers do not need to be escaped with quotes.

ODBC Driver for 1010data 2020

Kill Session

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 driver can share a session, the driver cannot share sessions with other tools. This option controls what happens when the driver logs in and another session is active.

By default this property is false, and the driver 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.

ODBC Driver for 1010data 2020

Limit Key Size

The maximum length of a primary key column.

Data Type

string

Default Value

"255"

Remarks

In some ODBC tools, for example, Microsoft Access, the length of the primary key column cannot be larger than a specific value. This property makes the ODBC Driver override the reported length of all the primary key columns. It is especially useful when using the ODBC Driver as a Microsoft Access Linked Data Source.

Setting the LimitKeySize to zero will make the key length revert to the original length.

This property can also be set using the SQLSetConnectAttr method, as shown in the following example:

SQLSetConnectAttr(hdbc, 20001, (SQLPOINTER)128, 0);

ODBC Driver for 1010data 2020

Location

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 driver (.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

ODBC Driver for 1010data 2020

Logfile

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.

ODBC Driver for 1010data 2020

Login URL

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.

ODBC Driver for 1010data 2020

Map To Int

This property controls whether or not the long type maps to SQL_INTEGER instead of SQL_BIGINT. This property is false by default.

Data Type

bool

Default Value

false

Remarks

SQL_BIGINT type columns have to be mapped to SQL_INTEGER to accommodate certain tools such as Access. MapToInt is set to false by default. You may set it to true to use SQL_INTEGER instead.

ODBC Driver for 1010data 2020

Map To Long Varchar

This property controls whether or not a column is returned as SQL_LONGVARCHAR.

Data Type

string

Default Value

"-1"

Remarks

Some applications require all text data larger than a certain number of characters to be reported as SQL_LONGVARCHAR. Use MapToLongVarchar to map any column larger than the specified size so they are reported as SQL_LONGVARCHAR instead of SQL_VARCHAR.

ODBC Driver for 1010data 2020

Map To WVarchar

This property controls whether or not string types map to SQL_WVARCHAR instead of SQL_VARCHAR. This property is set by default.

Data Type

bool

Default Value

true

Remarks

String columns must be mapped to SQL_WVARCHAR to accommodate various international character sets, so MapToWVarchar is set to true by default. You may set it to false to use SQL_VARCHAR instead.

ODBC Driver for 1010data 2020

Maximum Column Size

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 driver 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.

ODBC Driver for 1010data 2020

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.

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.

ODBC Driver for 1010data 2020

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.

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.

ODBC Driver for 1010data 2020

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.

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.

ODBC Driver for 1010data 2020

Offline

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.

ODBC Driver for 1010data 2020

Other

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=TrueCaches only a subset of columns, which you can specify in your query.
QueryPassthrough=TruePasses the specified query to the cache database instead of using the SQL parser of the driver.

Integration and Formatting

SupportAccessLinkedModeIn Access' linked mode, it is generally a good idea to always use a cache as most data sources do not support multiple Id queries. However if you want to use the driver in Access but not in linked mode, this property must be set to False to avoid using a cache of a SELECT * query for the given table.
DefaultColumnSizeSets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMTDetermines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filenameRecords the underlying socket data transfer to the specified file.

ODBC Driver for 1010data 2020

Page Size

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

Data Type

string

Default Value

"10000"

Remarks

The driver 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.

ODBC Driver for 1010data 2020

Password

Your 1010data password.

Data Type

string

Default Value

""

Remarks

Your 1010data password.

ODBC Driver for 1010data 2020

Proxy Auth Scheme

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 driver 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 driver performs HTTP BASIC authentication.
  • DIGEST: The driver performs HTTP DIGEST authentication.
  • NEGOTIATE: The driver retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
  • PROPRIETARY: The driver 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.

ODBC Driver for 1010data 2020

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.

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.

On Mac, proxy settings are read from the system-configured CFNetwork settings.

On Linux, this property is unsupported, and is set to False by default.

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

ODBC Driver for 1010data 2020

Proxy Exceptions

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 driver 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.

ODBC Driver for 1010data 2020

Proxy Password

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 driver uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

ODBC Driver for 1010data 2020

Proxy Port

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.

ODBC Driver for 1010data 2020

Proxy Server

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 driver 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 driver uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

ODBC Driver for 1010data 2020

Proxy SSL Type

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:

AUTODefault setting. If the URL is an HTTPS URL, the driver will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYSThe connection is always SSL enabled.
NEVERThe connection is not SSL enabled.
TUNNELThe 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.

ODBC Driver for 1010data 2020

Proxy User

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

ODBC Driver for 1010data 2020

Pseudo Columns

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, "*=*".

ODBC Driver for 1010data 2020

Query Passthrough

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

Data Type

bool

Default Value

true

Remarks

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

ODBC Driver for 1010data 2020

RTK

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.

ODBC Driver for 1010data 2020

SSL Server Cert

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.

ODBC Driver for 1010data 2020

Tables

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 driver.

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.

ODBC Driver for 1010data 2020

Timeout

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 driver throws an exception.

ODBC Driver for 1010data 2020

Upper Case Identifiers

This property reports all identifiers in uppercase. This is the default for Oracle databases and thus allows better integration with Oracle tools such as the Oracle Database Gateway.

Data Type

bool

Default Value

false

Remarks

Set this property to report all identifiers, including table and column names, in uppercase. This is the default for Oracle databases and thus allows better integration with Oracle tools such as the Oracle Database Gateway. For example, you can use this property to avoid quoting identifiers.

ODBC Driver for 1010data 2020

User

Your 1010data login name.

Data Type

string

Default Value

""

Remarks

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

ODBC Driver for 1010data 2020

Verbosity

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 driver reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described in the following list:

1Setting 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.
2Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and additional information about the request, if applicable, such as HTTP headers.
3Setting Verbosity to 3 will additionally log the body of the request and the response.
4Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
5Setting 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.

ODBC Driver for 1010data 2020

Views

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 driver.

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.

Copyright (c) 1010data - All rights reserved.
Build 19.0.7333.0