Tableau Connector for 1010data 2020

Tableau Connector for 1010data 2020

Tableau Connector for 1010data 2020 - Build 19.0.7333

Overview

The JDBC Driver for 1010data 2020 offers the most natural way to connect to 1010data data from Java-based applications and developer technologies. The driver wraps the complexity of accessing 1010data data in an easy-to-integrate, 100%-Java JDBC driver. 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

  • Deploy a single JAR that does not rely on client-side libraries.
  • Write SQL to retrieve data.
  • Compliant with JDBC 3.0 and JDBC 4.0.
  • Codeless integration with popular BI, reporting, and ETL tools.

Getting Started

See Getting Started for A-Z guides on authenticating and connecting to 1010data data. See the 1010data integration guides for information on connecting from other applications.

Using the JDBC Driver

See Using JDBC for examples of using standard JDBC classes like DataSource, Connection, Statement, ResultSet, and others, to work with 1010data data. Using JDBC (from Tools) walks through the steps of integration with JDBC tools, using several popular database tools as examples.

Schema Discovery

See Schema Discovery to access schema information through the standard JDBC interfaces. Query the System Tables to access additional metadata, such as data source capabilities.

JDBC Remoting

See JDBC Remoting to configure remote access to the JDBC data source. The JDBC remoting feature allows hosting the JDBC connection on a server to enable connections from virtually anywhere -- various clients on any platform (Java, .NET, C++, PHP, Python, and so on) and using any standards-based technology (ODBC, JDBC, and so on). JDBC remoting is enabled using the popular MySQL wire protocol server.

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 information on the available entities and how to query them.

Connection String Options

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

Tableau Connector for 1010data 2020

Getting Started

Connecting to 1010data

Installing the Connector shows how to authenticate to 1010data and configure any necessary connection properties in a JDBC URL. You can also configure driver capabilities through the available Connection properties, from data modeling to firewall traversal. The Advanced Settings section shows how to set up more advanced configurations and troubleshoot connection errors.

Connecting to JDBC Data Sources

The JDBC Driver for 1010data 2020 provides full support for integration into Java applications, including Eclipse, NetBeans, IntelliJ IDEA, and many other Integrated Development Environments, as well as J2EE applications running on a Java server such as Tomcat. You can find JSP, console, and swing demos in the installation folder.

Java Version Support

To deploy the driver JAR file, you must have Java Development Kit (JDK) 1.6 or higher installed on your system.

1010data Version Support

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

See Also

Tableau Connector for 1010data 2020

Installing the Connector

Prerequisites

  • Contact your account representative to enable SQL connection features on your account
  • Before you start, you must have installed Tableau 2019.4 or greater.

Installing the Connector

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

  1. Download the most recent version of the JDBC Connector from this link
  2. Install the JDBC Connector by running setup.jar
  3. Download the 1010.taco file from the website
  4. Move the 1010.taco file into C:\Users\[Windows User]\Documents\My Tableau Repository\Connectors
  5. When you run Tableau click on the "more" options under "connect to a server" and select 1010data from the list of options

    Connect to Server

  6. You will need to provide your 1010data Username and Password, as well as the LoginURL of your 1010data instance. The LoginURL should contain both the domain as well as the API version: for example, https://my.domain.1010data.com/beta-latest

    Provide Credentials

  7. You have successfully connected to Edge, select your table and get started with building your workbook!

Tableau Connector for 1010data 2020

Using JDBC

This section provides a walk-through of writing data access code to 1010data in JDBC.

See Data Model for more information on the available API objects and how to query them with SQL. See SQL Compliance for the SQL syntax.

Connecting from Code

See Installing the Connector for the prerequisite information you need to deploy the driver and configure the connection to 1010data. Connecting from Code shows how to connect with the DriverManager or C1010dataSource classes.

Executing SQL

Use the Statement and PreparedStatement classes to execute SQL to 1010data:

  • See Executing Statements to execute Statements and iterate over the returned ResultSets.
  • See Using Prepared Statements to execute parameterized statements. The PreparedStatement class provides a means to efficiently execute queries more than once and to mitigate SQL injection attacks.

Executing Stored Procedures

You can execute stored procedures as parameterized statements (with the CallableStatement class) or SQL statements (with the EXECUTE syntax): see Calling Stored Procedures.

Connection Pooling

Instantiate pooled connections with C1010dataSource objects: see Connection Pooling to create and configure the pool.

Tableau Connector for 1010data 2020

Installed Files

The JDBC Driver for 1010data 2020 ships the following files, located in the lib subfolder of the installation directory:

  • cdata.jdbc.c1010.jar: Pure Java Type 4/5 JDBC Driver, compiled with JDK 1.6.
  • cdata.jdbc.c1010.remoting.ini: This is the configuration file for JDBC Remoting.

Tableau Connector for 1010data 2020

Connecting from Code

This section describes how to connect with the JDBC DriverManager or C1010dataSource interfaces.

Connecting with the DriverManager

When connecting with the DriverManager class, the JDBC Driver for 1010data 2020 follows the JDBC convention: First, load the C1010 driver class. Then, make a connection.

Load the Driver

The following step is optional per the JDBC 4.0 specification.
Class.forName("cdata.jdbc.c1010.C1010Driver");

Establish a Connection

Provide the connection string with the getConnection method of the static DriverManager class. Start the connection string with "jdbc:c1010:". A typical connection string is the following:

Connection conn = DriverManager.getConnection("jdbc:c1010:User=user@company.com;Password=password;");
Alternatively, you can prepare the connection options using a Properties object. Pass the Properties object to the DriverManager.
Properties prop = new Properties();
prop.setProperty("User","user@company.com");
prop.setProperty("Password","password");

Connection conn = DriverManager.getConnection("jdbc:c1010:",prop);

Connecting with the C1010dataSource Class

You can use the C1010dataSource class to create pooled connections, as shown in the following example. See Connection Pooling for more information.

The following example instantiates a pooled Connection object:

C1010dataSource ds = new C1010dataSource("cdata.jdbc.c1010.C1010Driver", "jdbc:c1010:UseConnectionPooling=true;User=user@company.com;Password=password;"); 
Connection conn = ds.getConnection();

Tableau Connector for 1010data 2020

Executing Statements

After Connecting from Code, you can execute SQL statements with the Statement class. See Using Prepared Statements to execute parameterized statements.

Select

To execute SQL statements that return data, use the Statement class' generic execute method or the executeQuery method. To return the results of a query, call the getResultSet method of the Statement.

The following example calls the execute method and iterates over the results returned:

Statement stat = conn.createStatement();
boolean ret = stat.execute("SELECT lahmanid, Firstname FROM pub.demo.baseball.master");
if (ret) {
  ResultSet rs=stat.getResultSet();
  while(rs.next()) {
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnName(i) +"="+rs.getString(i));
    }
  }
}

Tableau Connector for 1010data 2020

Using Prepared Statements

The PreparedStatement object represents a precompiled SQL statement. A PreparedStatement can be used multiple times and mitigates SQL injection attacks. A PreparedStatement can be a SELECT, INSERT, UPDATE, or DELETE statement.

To execute a prepared statement, you can use the generic execute method of the Statement class. This section describes how to execute a prepared statement.

  1. Instantiate a PreparedStatement object with the prepareStatement method of the Connection class.

    See Connecting from Code to create the connection.

  2. Declare parameters by calling the PreparedStatement's corresponding setter method. Note that the parameter indices start from one.
  3. Call the PreparedStatement's execute method to execute the statement.
  4. Call the PreparedStatement's getResultSet method to pull the results into a ResultSet object.
  5. Call ResultSet.next to iterate over the result set. Use the ResultSetMetaData class to obtain column information about the result set. To instantiate a ResultSetMetaData object, call the ResultSet's getMetaData method.

Select

The following example shows how to execute a SELECT prepared statement:

String query = "SELECT * FROM pub.demo.baseball.master WHERE lahmanid=? AND Firstname=?";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, "XXX");
pstmt.setString(2, "YYY");
boolean ret = pstmt.execute();
if (ret) {
  ResultSet rs=pstmt.getResultSet();
  while(rs.next()) {
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnName(i) +"="+rs.getString(i));
    }
  }
} 

Tableau Connector for 1010data 2020

Connection Pooling

The driver implements a standard JDBC connection pool. Set UseConnectionPooling to enable the pool. The following sections show how to configure and use them.

Working with Pooled Connections

Just as you would interact with a non-pooled connection, you use standard JDBC objects to get and close connections. But, in this case, the Connection object retrieved is a handle for the physical connection owned by the connection pool. When the connection is closed, instead of the connection being destroyed, the handle is returned to the pool, where it is available for the next connection request.

You must explicitly close the connection for it to be returned to the pool.

Creating Pooled Connections

To retrieve a Connection object from the pool, you must use an instance of C1010dataSource and set the UseConnectionPooling connection property to true.

Configuring the Connection Pool

In addition to UseConnectionPooling, set the following connection properties to control the connection pool:

  • PoolMaxSize: Define the maximum number of connections that can be open at any given time.
  • PoolIdleTimeout: Set a limit to how long connections can remain open and idle. If this limit is exceeded, the connection is returned to the pool.
  • PoolWaitTime: Set a limit to how long new connection requests should wait for a connection to become available. If this limit is exceeded, the request throws an error. By default, connection requests wait forever for a connection to become available.

C1010dataSource Example

To start pooling connections, instantiate the C1010dataSource with UseConnectionPooling:

C1010dataSource c1010dataSource = new C1010dataSource("cdata.jdbc.c1010.C1010Driver", "jdbc:c1010:UseConnectionPooling=true;User=user@company.com;Password=password;");

JNDI Example

The following example shows how to use a connection pool with JNDI.

The following code initializes the JNDI File System Service Provider. To run the example, you need to add the fscontext.jar and providerutil.jar files to your classpath. You can download these files from the Oracle Java Archive: Under the Java SE section, select Java Platform Technologies > Java Naming and Directory Interface.

 
Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,
    "com.sun.jndi.fscontext.RefFSContextFactory");
env.put(Context.PROVIDER_URL, "file:///tmp");

Context ctx = new InitialContext(env); 
DataSource ds = null;
Connection conn = null;
The following code registers the C1010dataSource with the JNDI naming service, gets an instance of the DataSource from the service, and creates pooled connections from that instance.
try {
  C1010dataSource c1010dataSource = new C1010dataSource("cdata.jdbc.c1010.C1010Driver", "jdbc:c1010:UseConnectionPooling=true;User=user@company.com;Password=password;");
  ctx.bind("jdbc/c1010", c1010dataSource);
  ds = (DataSource) ctx.lookup("jdbc/c1010");

  conn = ds.getConnection();
  Statement stat = conn.createStatement();
  boolean ret = stat.execute("SELECT 1");
  ResultSet rs=stat.getResultSet(); 
} catch(Exception ex) { } finally {
  if(conn != null) conn.close();
}

Closing the Connection Pool

On JRE 1.3 or higher, the connection pool itself automatically closes when the application stops running. You can manually close the connection pool by invoking the close method of the DataSource object.

Tableau Connector for 1010data 2020

Calling Stored Procedures

Use CallableStatement objects to execute parameterized stored procedure calls. Use Statement objects to execute stored procedures as SQL statements with the EXEC syntax.

Using Callable Statement Objects

You can use the generic execute method of the CallableStatement class to execute any stored procedure as a parameterized query.

To return the stored procedure's results, call getResultSet. To return a count of updated rows, call getUpdateCount.

The following example shows how to execute the NA stored procedure:

CallableStatement cstmt = conn.prepareCall("NA");
cstmt.setString("NA", "NA");
boolean ret = cstmt.execute();   
if (!ret) {
  int count=cstmt.getUpdateCount();
  if (count!=-1) {
    System.out.println("Affected rows: "+count);
  }
}
else {
  ResultSet rs=cstmt.getResultSet();
  while(rs.next()){
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnName(i) +"="+rs.getString(i));
    }
  }
}

Using Statement Objects

You can use the execute method of the Statement class to execute any stored procedure as an SQL statement.

To return the stored procedure's results, call getResultSet. To return a count of updated rows, call getUpdateCount.

The following example shows how to execute the NA stored procedure: (See EXECUTE Statements for more on the syntax.)

Statement stmt = conn.createStatement();
boolean ret = stmt.execute("EXEC NA NA = 'NA'");

if (!ret) {
  int count=stmt.getUpdateCount();
  if (count!=-1) {
    System.out.println("Affected rows: "+count);
  }
}
else {
  ResultSet rs=stmt.getResultSet();
  while(rs.next()) {
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnName(i) +"="+rs.getString(i));
    }
  }
}

Tableau Connector for 1010data 2020

Using JDBC (from Tools)

The JDBC Driver for 1010data 2020 provides the standard JDBC connection process in analytics tools and other applications.

JDBC Integration Quickstarts

The JDBC Driver for 1010data 2020 provides the standard JDBC connection process in applications ranging from business intelligence tools to IDEs. The following sections show how to create and start querying 1010data JDBC data sources, walking through data access in JDBC from several popular database tools.

Complete List of 1010data Integration Quickstarts

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

Tableau Connector for 1010data 2020

DbVisualizer

This section shows how to establish a connection to data in DbVisualizer, use the table editor to edit and save data, and execute SQL.

Add the JDBC Driver for 1010data

Complete the following steps to add the driver .jar file in a driver definition:

  1. In DbVisualizer, select Tools > Driver Manager and then select Driver > Create Driver.
  2. Enter a name for the driver.
  3. Enter the following in the URL Format box:
    jdbc:c1010:
  4. In the Driver JAR Files section, click the folder icon and browse to the driver .jar file, cdata.jdbc.c1010.jar. By default, this is located in the lib subfolder of the installation directory.
  5. In the Driver Class menu, select the C1010Driver class, cdata.jdbc.c1010.C1010Driver.

Create a Database Connection for 1010data

Complete the following steps to select the 1010data driver and build the JDBC URL to create the JDBC data source:

  1. In the main DbVisualizer window, select Tools > Connection Wizard.

  2. Enter an alias for the connection.
  3. Select the driver definition you created.
  4. In the following fields, enter the JDBC URL and provide authentication.

    See Installing the Connector for a guide.

  5. Database URL: Enter the full JDBC URL. The syntax of the JDBC URL is jdbc:c1010: followed by the connection properties in a semicolon-separated list of name-value pairs.

    A typical connection string is below:

    jdbc:c1010:User=user@company.com;Password=password;

  6. Database UserId: Enter the username for authentication to 1010data, if needed.
  7. Database Password: Enter the password for authentication to 1010data, if needed.
Note: After you successfully create a database connection for 1010data you should set the "Database Type" property to "Generic".

Discover Schemas and Query 1010data Data

In the main DbVisualizer window, click Connect on the Connection tab for the newly created connection. You can then browse data and execute SQL in the following ways:

  • To access the available tables, from the Databases tab, expand the nodes for the connection, database, schema, and table or view.
  • To browse through table data and metadata, right-click a table and click Open in New Tab.
  • To execute SQL queries, select SQL Commander > New SQL Commander. Select the Database Connection, Database, and Schema from the available menus.

See Data Model for information on querying specific tables. See SQL Compliance for more information on the SQL syntax.

Tableau Connector for 1010data 2020

DBeaver

This section describes how to connect to 1010data following the standard JDBC connection process in DBeaver: Add the driver JAR, provide the driver class name, and provide any 1010data-specific parameters in the JDBC URL.

Add the JDBC Driver for 1010data

Complete the following steps to add the driver .jar file:

  1. Open the DBeaver application and, in the Database menu, select the Driver Manager option. Click New to open the Create New Driver form.
  2. In the Driver Name box, enter a user-friendly name for the driver.
  3. To add the .jar file, click Add File on the Libraries tab. Select the cdata.jdbc.c1010.jar file, located in the lib subfolder of the installation directory.

  4. Click Find Class, and in the list select, "cdata.jdbc.c1010.C1010Driver".
  5. In the URL Template field, enter jdbc:c1010:.

Create the JDBC Data Source

Complete the following steps to select the 1010data driver and build the JDBC URL to create the JDBC data source:

  1. In the main DBeaver window, click Database > New Connection.
  2. Select the driver definition you created in the dialog that is displayed.
  3. On the next page of the wizard, click the Driver Properties tab.
  4. Enter any connection properties required to connect to 1010data.

    Here is a typical connection string:

    jdbc:c1010:User=user@company.com;Password=password;
    See Installing the Connector for a connection and authentication guide.

  5. Finish creating the connection with the default settings or configure advanced network settings -- proxy, firewall, and SSH.

Discover Schemas and Query 1010data Data

Complete the following steps to query information from the tables exposed by the connection:

  1. Expand the node for the connection to access the database metadata.
  2. Browse the table metadata and edit the table data by right-clicking a Table and then clicking Edit Table.

To execute an SQL query, select SQL Editor > New SQL Editor and select the 1010data connection you created. You can then enter queries using code completion.

Tableau Connector for 1010data 2020

SQuirreL SQL

This section describes how to create a JDBC data source for 1010data data and execute queries.

Add the JDBC Driver for 1010data

Complete the following steps to add the driver .jar file:

  1. In the Drivers pane, click the plus icon to open the Add Driver wizard.
  2. In the Name box, enter a user-friendly name for the driver; for example, JDBC Driver for 1010data.
  3. In the Example URL box, enter jdbc:c1010:
  4. In the Extra Class Path tab, click Add.
  5. In the file explorer that opens, select the .jar file for the driver, located in the lib subfolder of the installation directory.
  6. Click List Drivers to populate the Class Name menu with the class name for the driver, cdata.jdbc.c1010.C1010Driver.

Create the JDBC Data Source

Complete the following steps to select the 1010data driver you created and build the JDBC URL to create the JDBC data source:

  1. In the Aliases pane, click the plus icon.
  2. In the Add Alias wizard that opens, provide values for the following fields:

    • Name: Enter a name for the alias; for example, 1010data Source.
    • Driver: Select the driver definition you created.
    • URL: Enter jdbc:c1010:
    • User Name: If needed, enter a user name for authentication, which is added to the JDBC URL.
    • Password: If needed, enter a password for authentication, which is added to the JDBC URL.

  3. If you want to define any additional properties, add them to the JDBC URL in a semicolon-separated list.

    The following is a typical connection string:

    jdbc:c1010:User=user@company.com;Password=password;
    See Installing the Connector for a connection and authentication guide.

Or, follow these steps to add the properties in the Driver Properties dialog:
  1. Select the Use Driver Properties check box.
  2. In the Specify column, select the check boxes for the required connection properties and specify the corresponding values.
  3. In the dialog that appears after you click OK, click Connect to test the connection.

Discover Schemas and Query 1010data Data

To connect to the data source, right-click the alias on the Aliases pane and then click Connect. After the metadata has loaded, a new tab for the 1010data data source is displayed. On the Objects subtab, you can discover schema information, such as the available tables and views.

To view table data and metadata, select the table on the Objects tab. Access the table data on the Content tab.

To execute an SQL query, enter the query on the SQL tab and then click Run SQL (the runner icon).

Tableau Connector for 1010data 2020

Tableau

This section describes how to connect and start querying data from Tableau.

Add the JDBC Driver for 1010data 2020

Note: Before starting Tableau on Windows, make sure that you have placed the .jar file in the C:\Program Files\Tableau\Drivers folder. Before starting Tableau on macOS, make sure that you have placed the .jar file in the ~/Library/Tableau/Drivers folder.

To add the driver .jar file:

  1. Start Tableau.
  2. Under To a Server, select More.
  3. Select Other Databases (JDBC).
  4. Enter the JDBC connection string in the URL field. See Building the JDBC URL for 1010data below for more information.
  5. Select Sign in.

Building the JDBC URL for 1010data

Connection strings provide information about a data source and how to connect to that data source. The driver comes with a connection string builder that makes it easier to create and manage the contents of connection strings.

After downloading and installing the driver, double-click the .jar file in the lib folder. You can also manually run the .jar file, as shown in the following examples.

From Windows:

java -jar 'C:\Program Files\CData\JDBC Driver for 1010data 2020\lib\cdata.jdbc.c1010.jar'

From macOS:

java cdata.jdbc.c1010.jar

Running the .jar file opens the Connection Properties dialog box. You can use this dialog box to build and test a connection string. Click Test Connection to test and validate the entered connection properties. Click Copy to Clipboard to copy the connection string for use within the application where the JDBC driver is being used. See the help documentation for more information about the connection string options.

Discover Schemas and Query Data

To query data:

  1. Select CData from the Database pull-down menu.
  2. Select Odata from the Schema pull-down menu.
  3. Drag the table onto the join area. You can include multiple tables.
  4. Select Update Now or Automatically Update. Update Now lets you preview the first 10,000 rows of the data source (or enter the number of rows you want to see in the Rows text box). Automatically Update automatically reflects the changes in the preview area.
  5. In the Connection menu, select the Live option, so that you skip loading a copy of the data into Tableau and instead work on real-time data.
  6. Click the tab for your worksheet. Columns are listed as Dimensions and Measures, depending on the data type.

Tableau Connector for 1010data 2020

JDBC Remoting

The JDBC remoting feature allows you to connect to the JDBC driver from remote machines. The remoting feature is a daemon process that listens for database queries and responds to them. The driver supports the MySQL protocol and the TDS (SQL Server) protocol for remoting.

JDBC remoting will allow any MySQL or SQL Server client (ODBC drivers, programming languages like PHP, Perl, Python, tools like MySQL workbench, and many other applications) to easily connect to 1010data.

Remoting can be configured through CLI Options or a Configuration File.

Tableau Connector for 1010data 2020

CLI Options

You can start the MySQL and TDS daemons from the command line, as shown below:

java -jar cdata.jdbc.c1010.jar [ options ]
The following command-line options are available:

OptionDescription
-h, --helpDisplay help for available options and exit.
-f, --config-fileThe configuration file for the daemon.
-u, --userThe user allowed to connect. Use a configuration file to configure multiple users. If a user is specified on the command line, then only that user is given access.
-p, --passwordThe password for the user specified with the user option. If both user and password are set on the command line, the users section in the config file is ignored.
-d, --databaseThe database that clients will use to connect. If multiple databases are specified in the config file, connections are allowed to only the database specified on the command line.
-c, --connectionThe connection string used to connect to the data source being surfaced. If no connection string is specified on the command line, the connection string is read from the config file.
-P, --portThe port number to use to listen for TCP/IP connections. The default port is 3306.
-m, --max-connectionsThe maximum number of allowed TCP/IP connections. The default value is 25 connections.
--session-timeoutThe session timeout time in seconds. The default timeout is 20 seconds.
-t, --protocolThe protocol used for remoting. The options are MySQL or TDS. The default value is MySQL.
-g, --logfileThe full path of the log file.
-F, --logrotationschemeThe interval at which to truncate the logs. The options are 1 (daily in the format [MyFileName]_2016_3_21.txt), 2 (weekly in the format [MyFileName]_Week_5.txt, where 5 is the fifth week in the year), and 3 (monthly in the format [MyFileName]_2016_3_21.txt).
-v, --verbosityThe verbosity of the log. 1 is informational. Levels up to 5 add the following subsequent details: (2) HTTP headers, (3) the HTTP body, (4) transport-level communication including SSL, and (5) interface commands and other data source communication.
--testThe database to test the connection with. If this property is not specified, the default database is used.
--ssl-certThe path to the SSL certificate.
--ssl-subjectThe subject of the SSL certificate.
--ssl-passwordThe password of the SSL certificate.
-n, --nodeidDisplays the NodeId of this machine.
-l, --licenseInstalls the license on this machine. This option will prompt you for the type of license and other details.

Options specified on the command line take precedence over options specified in the config file. You can pass in command-line options to specify a restricted subset of the options allowed in the Configuration File.

Tableau Connector for 1010data 2020

Configuration File

You can save configuration settings for MySQL and TDS remoting in a config file. The file must be structured in the INI file format. Specify this file with the -f command-line option. The config file can have the following sections:

[mysqld]

In the mysqld section, use the following properties to configure the MySQL daemon:

NameDescription
portThe port number to use to listen for TCP/IP connections. The default port is 3306.
max-connectionsThe maximum number of allowed TCP/IP connections. 25 is the default.
session-timeoutThe session timeout time in seconds. The default timeout is 20 seconds.
logfileThe full path of the log file.
verbosityThe verbosity of the log. 1 is informational. Levels up to 5 add the following subsequent details: (2) HTTP headers, (3) the HTTP body, (4) transport-level communication including SSL, and (5) interface commands and other data source communication.
logrotationschemeThe interval at which to truncate the logs. The options are 1 (daily in the format [MyFileName]_2016_3_21.txt), 2 (weekly in the format [MyFileName]_Week_5.txt, where 5 is the fifth week in the year), and 3 (monthly in the format [MyFileName]_2016_3_21.txt).

The default is 2.

ssl-certThe path to the SSL certificate.
ssl-subjectThe subject of the SSL certificate.
ssl-passwordThe password of the SSL certificate.

[tdsd]

In the tdsd section, use the following properties to configure the TDS daemon:

NameDescription
portThe port number to use to listen for TCP/IP connections. The default port is 3306.
max-connectionsThe maximum number of allowed TCP/IP connections. 25 is the default.
session-timeoutThe session timeout time in seconds. The default timeout is 20 seconds.
logfileThe full path of the log file.
verbosityThe verbosity of the log. 1 is informational. Levels up to 5 add the following subsequent details: (2) HTTP headers, (3) the HTTP body, (4) transport-level communication including SSL, and (5) interface commands and other data source communication.
logrotationschemeThe interval at which to truncate the logs. The options are 1 (daily in the format [MyFileName]_2016_3_21.txt), 2 (weekly in the format [MyFileName]_Week_5.txt, where 5 is the fifth week in the year), and 3 (monthly in the format [MyFileName]_2016_3_21.txt).
ssl-certThe path to the SSL certificate.
ssl-subjectThe subject of the SSL certificate.
ssl-passwordThe password of the SSL certificate.

[databases]

In the databases section, define keys that map the MySQL database or the SQL Server catalog to 1010data connection strings. Clients connect to the MySQL database or the SQL Server catalog defined here. To connect to 1010data, the driver uses the connection string that corresponds to this key.

[databases]
C1010 = "User=user@company.com;Password=password;"

[users]

In the users section, define the usernames and passwords of the users of the server. If the acl section is not defined, all users have access to all databases.

[mysql_vars]

In the mysql_vars section, define system variables for the MySQL server. The standard variables are supported. Below are several examples:

NameDescription
version_commentThis value is hard-coded as CData JDBC Driver for 1010data (MySQL Remoting).
character_set_clientThe character_set used in statements sent by the client.

[acl]

In the acl section, allow users to access 1010data databases. Databases must be defined in the databases section. Users must be defined in the users section. Use commas to separate users authorized to access the specified database.

Example Config File

Below is an example config file. The example includes all properties required to configure the server. It also shows how to configure access control for several users and 1010data instances.

[mysqld]
port = 3306
max-connections = 25
session-timeout = 20
logfile = C1010RemotingLog.txt
verbosity = 2
ssl-cert = "CData.JDBC.C1010.Remoting.pfx"
ssl-subject = <subject>
ssl-password = <password>

[tdsd]
port = 1433
maxc = 20
session-timeout = 20
logfile = C1010RemotingLog.txt
verbosity = 2
ssl-cert = "CData.JDBC.C1010.Remoting.pfx"
ssl-subject = <subject>
ssl-password = <password>

[databases]
C1010 = "User=user@company.com;Password=password;"
C1010_ReadOnly = "User=user@company.com;Password=password;ReadOnly=True"

[users]
root = <password>
test = <password>

[mysql_vars]
version_comment =  "JDBC Driver for 1010data (MySQL Remoting)"

[acl]
C1010 = root
C1010_ReadOnly = root, test

You can further restrict the allowed options by passing in options on the command line. See CLI Options for more information.

Tableau Connector for 1010data 2020

Schema Discovery

The driver supports schema discovery using JDBC classes or using SQL queries to the available system tables. The JDBC classes enable access to schema information, connection property information, and information on the columns returned.

Through SQL queries to the available System Tables, you can access schema and connection property information as well as information on data source functionality and statistics on update operations.

Using JDBC Interfaces

You can use JDBC interfaces to access schema information, connection property metadata, and result set metadata. The driver implements the standard interfaces as defined in the JDBC 4.0 specification.

Retrieving Schema Information

The DatabaseMetaData class provides information on the following:

Retrieving Connection Property Information

The Driver class, returned by DriverManager, provides information about Connection Properties.

Retrieving Result Set Column Information

The ResultSetMetaData class provides information about the columns returned in Result Sets.

Using SQL

You can query the System Tables to access any metadata surfaced through the driver.

Tableau Connector for 1010data 2020

Tables

You can use the getTables method of the DatabaseMetaData interface to retrieve a list of tables:

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

Connection conn = DriverManager.getConnection(connectionString);
DatabaseMetaData table_meta = conn.getMetaData();
ResultSet rs=table_meta.getTables(null, null, "%", null);  
while(rs.next()){
  System.out.println(rs.getString("TABLE_NAME"));
}
The getTables method returns the following columns:

Column NameData TypeDescription
TABLE_CATStringThe table catalog.
TABLE_SCHEMStringThe table schema.
TABLE_NAMEStringThe table name.
TABLE_TYPEStringThe table type.
REMARKSStringThe table description.

Tableau Connector for 1010data 2020

Columns

You can use the getColumns method of the DatabaseMetaData interface to retrieve column information. You can restrict the results by the table name. The code example below retrieves the column names for the pub.demo.baseball.master table:

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

Connection conn = DriverManager.getConnection(connectionString);
DatabaseMetaData table_meta = conn.getMetaData();
ResultSet rs = table_meta.getColumns(null, null, "pub.demo.baseball.master", null);
while(rs.next()){
  System.out.println(rs.getString("COLUMN_NAME")); 
}
The getColumns method returns the following columns:

Column NameData TypeDescription
TABLE_CATStringThe database name.
TABLE_SCHEMStringThe table schema.
TABLE_NAMEStringThe table name.
COLUMN_NAMEStringThe column name.
DATA_TYPEintThe data type identified by the value of a constant defined in java.sql.Types.
TYPE_NAMEStringThe data type name used by the driver.
COLUMN_SIZEintThe length in characters of the column or the numeric precision.
BUFFER_LENGTHintThe buffer length.
DECIMAL_DIGITSintThe column scale or number of digits to the right of the decimal point.
NUM_PREC_RADIXintThe radix, or base.
NULLABLEintWhether the column can contain null as defined by the following JDBC DatabaseMetaData constants: columnNoNulls (0) or columnNullable (1).
REMARKSStringThe column description.
COLUMN_DEFStringThe default value for the column.
SQL_DATA_TYPEintReserved by the specification.
SQL_DATETIME_SUBintReserved by the specification.
CHAR_OCTET_LENGTHintThe maximum length of binary and character-based columns.
ORDINAL_POSITIONintThe column index, starting at 1.
IS_NULLABLEStringWhether a null value is allowed: YES or NO.
SCOPE_CATALOGStringThe table catalog that is the scope of a reference attribute.
SCOPE_SCHEMAStringThe table schema that is the scope of a reference attribute.
SCOPE_TABLEStringThe table name that is the scope of a reference attribute.
SOURCE_DATA_TYPEintThe source type of a distinct type. Or, a user-generated Ref type. If DATA_TYPE is not DISTINCT, this value is null. If a user-generated Ref, this value is null.
IS_AUTOINCREMENTStringWhether the column value is assigned by 1010data in fixed increments.
IS_GENERATEDCOLUMNStringWhether the column is generated: YES or NO.
ISREADONLYbooleanWhether the column is read-only.
ISKEYbooleanWhether the column is a key.

Tableau Connector for 1010data 2020

Procedures

You can use the DatabaseMetaData interface to retrieve stored procedure information. The getProcedures method returns descriptions of the available stored procedures.

The following code retrieves the names of the available stored procedures:

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

Connection conn = DriverManager.getConnection(connectionString);
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getProcedures(null, null, "%");
while(rs.next()){
  System.out.println(rs.getString("PROCEDURE_NAME"));
}
The getProcedures method returns the following columns:

Column NameData TypeDescription
PROCEDURE_CATStringThe catalog the procedure belongs to.
PROCEDURE_SCHEMStringThe schema the procedure belongs to.
PROCEDURE_NAMEStringThe stored procedure name.
REMARKSStringThe description of the stored procedure.
PROCEDURE_TYPEshortReturns 2 if the procedure returns a result. Returns 1 if the procedure does not return a result. Returns 0 if unknown.
SPECIFIC_NAMEStringThe name that uniquely identifies the stored procedure within its schema.

Tableau Connector for 1010data 2020

Procedure Parameters

You can use the DatabaseMetaData interface to retrieve stored procedure information. The getProcedureColumns method returns descriptions of stored procedure parameters. You can restrict the results by the stored procedure name.

The following code example outputs information about the parameters of the NA stored procedure:

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

Connection conn = DriverManager.getConnection(connectionString);
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs=meta.getProcedureColumns(null, null, "NA", null);  
while(rs.next()) {   
  for(int i=1;i<=rs.getMetaData().getColumnCount();i++)  {
    System.out.println(rs.getMetaData().getColumnName(i) +"="+rs.getString(i));
  }
}
The getProcedureColumns method returns the following columns:

Column NameData TypeDescription
PROCEDURE_CATStringThe catalog that the procedure belongs to.
PROCEDURE_SCHEMStringThe schema that the procedure belongs to.
PROCEDURE_NAMEStringThe name of the stored procedure.
COLUMN_NAMEStringThe name of the procedure column.
COLUMN_TYPEStringThe type of procedure column as defined by the following DatabaseMetaData constants: procedureColumnIn (1), procedureColumnInOut (2), procedureColumnResult (3), procedureColumnOut (4), and procedureColumnReturn (5).
DATA_TYPEintThe data type name as defined in java.sql.Types.
TYPE_NAMEStringThe driver-defined data type name.
PRECISIONintThe number of digits allowed for numeric data.
LENGTHintThe number of characters allowed for character data. The number of digits allowed for numeric data.
SCALEshortThe number of digits to the right of the decimal point in numeric data.
RADIXshortThe radix, or base.
NULLABLEshortWhether the parameter can contain null as defined by the following DatabaseMetaData constants: parameterNoNulls (0), parameterNullable (1), and parameterNullableUnknown (2).
REMARKSStringThe description of the parameter.
COLUMN_DEFStringThe default value for the parameter.
SQL_DATA_TYPEintReserved in the specification.
SQL_DATETIME_SUBintReserved in the specification.
CHAR_OCTET_LENGTHintThe maximum length of binary-based and character-based columns. Null for other data types.
ORDINAL_POSITIONintThe index of the output parameter.
IS_NULLABLEStringWhether the column can include null: YES or NO.
SPECIFIC_NAMEStringThe name that uniquely identifies the stored procedure within its schema.

Tableau Connector for 1010data 2020

Primary Keys

You can use the getPrimaryKeys method to return information about the primary keys for 1010data tables. You can restrict the results by the table name.

The following code example outputs the column or columns composing the primary key for the pub.demo.baseball.master table:

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

Connection conn = DriverManager.getConnection(connectionString);
ResultSet rs = conn.getMetaData().getPrimaryKeys(null,null,"pub.demo.baseball.master");
while(rs.next()){
  System.out.println(rs.getString("COLUMN_NAME"));
}

The getPrimaryKeys method returns the following columns:

Column NameData TypeDescription
TABLE_CATStringThe table catalog
TABLE_SCHEMStringThe table schema.
TABLE_NAMEStringThe table name.
COLUMN_NAMEStringThe column name.
KEY_SEQshortThe sequence number, or column index starting from 1, within the foreign key.
PK_NAMEStringThe primary key name.

Tableau Connector for 1010data 2020

Connection Properties

The available connection properties can be retrieved with the getPropertyInfo method of the Driver class. This method returns an array with elements of type DriverPropertyInfo.

String connectionString = "jdbc:c1010:";

Driver driver = DriverManager.getDriver(connectionString);
Properties info = new Properties();
DriverPropertyInfo[] attr = driver.getPropertyInfo(connectionString,info);
for(int i=0;i<attr.length;i++){
  System.out.println(attr[i].name);
  System.out.println(attr[i].description);
  System.out.println(attr[i].required);
  System.out.println(attr[i].value);
  String[] c = attr[i].choices;
  if(c != null) {
    for(String s: c)
      System.out.println(s);
  }
}

The DriverPropertyInfo class has the following properties:

Property NameData TypeDescription
NameStringThe name of the connection property.
DescriptionStringThe description for the connection property.
RequiredbooleanWhether the connection property must be set to connect to 1010data.
ChoicesString[]An array of the allowed values for the connection property.
ValueStringThe current value of the connection property or the default value if one is not set by the user.

Tableau Connector for 1010data 2020

Result Sets

You can use ResultSetMetaData to retrieve metadata about the results of a query.

The query can contain any of the following:

  • Joins
  • Aggregates
  • Aliases
  • Fully qualified names
  • Generated columns

You can instantiate a ResultSetMetaData object by invoking the getMetaData method of the Statement class. A ResultSetMetaData instance is populated with data after the statement has been executed. The following query prints out the columns in the result of the query:

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

Connection conn = DriverManager.getConnection(connectionString);
PreparedStatement pstmt = conn.prepareStatement("SELECT lahmanid, Firstname AS My_Firstname, GETDATE() FROM pub.demo.baseball.master WHERE LastName = 'B%'");
pstmt.executeQuery();
ResultSetMetaData rs = pstmt.getMetaData();
for(int i=1;i<=rs.getColumnCount();i++) {
  System.out.println(rs.getColumnName(i));
}

Tableau Connector 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').

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

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

Tableau Connector for 1010data 2020

JOIN Queries

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

Tableau Connector 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):

boolean ret = stat.execute("SELECT lahmanid, Firstname INTO [csv://c:/pub.demo.baseball.master.txt] FROM [pub.demo.baseball.master] WHERE LastName = 'B%'");
System.out.println(stat.getUpdateCount()+" rows affected");
You can specify other file formats in the URI. The following example exports tab-separated values:
Statement stat = conn.createStatement();
boolean ret = stat.execute("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%'");
System.out.println(stat.getUpdateCount()+" rows affected");

Tableau Connector 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

Tableau Connector 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; 

Tableau Connector 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.

Tableau Connector 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.

CacheDriver

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

Tableau Connector 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.

String connectionString = "jdbc:c1010:Cache Location=C:\\cache.db;" +
                          "AutoCache=true;" +
                          "User=user@company.com;Password=password;";
Connection connection = DriverManager.getConnection(connectionString);
Statement stat = connection.createStatement();
boolean ret = stat.execute("SELECT lahmanid, Firstname FROM pub.demo.baseball.master WHERE LastName = 'B%'");
ResultSet rs=stat.getResultSet();
while(rs.next()){
  System.out.println("Read and cached the row with Id "+rs.getString("Id"));
}
connection.close();

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.

Tableau Connector 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.

String cmd = "CACHE SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%'", connection";
stat.execute(cmd);
connection.close();

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.

String cmd = "CACHE WITH TRUNCATE SELECT * FROM pub.demo.baseball.master WHERE LastName = 'B%'";
stat.execute(cmd);
connection.close();

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.

Connection connection = DriverManager.getConnection("jdbc:c1010:User=user@company.com;Password=password;Offline=true;Cache Location=C:\\cache.db;");
Statement stat = connection.createStatement();
String query = "SELECT * FROM pub.demo.baseball.master WHERE LastName='B%' ORDER BY Firstname ASC";
stat.execute(query);
connection.close();

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.

Tableau Connector 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

Tableau Connector for 1010data 2020

Data Model

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

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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 installing, see Installing the Connector.
Auto CacheAutomatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider .
Batch SizeThe maximum size of each batch operation to submit.
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 DriverThe database driver to be used to cache 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 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 .
Connection Life TimeThe maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed.
Connect On OpenThis property species whether to connect to the 1010data when the connection is opened.
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.
Kill SessionWhether to kill the any existing sessions under your account.
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.
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.
Pool Idle TimeoutThe allowed idle time for a connection before it is closed.
Pool Max SizeThe maximum connections in the pool.
Pool Min SizeThe minimum number of connections in the pool.
Pool Wait TimeThe max seconds to wait for an available connection.
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.
Use Connection PoolingThis property enables connection pooling.
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.
Tableau Connector 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.

Tableau Connector for 1010data 2020

Batch Size

The maximum size of each batch operation to submit.

Data Type

int

Default Value

0

Remarks

When BatchSize is set to a value greater than 0, the batch operation will split the entire batch into separate batches of size BatchSize. The split batches will then be submitted to the server individually. This is useful when the server has limitations on the size of the request that can be submitted.

Setting BatchSize to 0 will submit the entire batch as specified.

Tableau Connector 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 CacheDriver 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 CacheDriver.

The connection string specified in the CacheConnection property is passed directly to the underlying CacheDriver. Consult the documentation for the specific JDBC driver for more information on the available properties. Make sure to include the JDBC driver in your application's classpath.

Derby and Java DB

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

Alternatively, you can configure the connection to Derby manually using CacheProvider and CacheConnection. The following is the Derby JDBC URL syntax:

jdbc:derby:[subsubprotocol:][databaseName][;attribute=value[;attribute=value] ... ]
For example, to cache to an in-memory database, use the following:
jdbc:derby:memory

SQLite

To cache to SQLite, you can use the SQLite JDBC driver. The following is the syntax of the JDBC URL:

jdbc:sqlite:dataSource
  • Data Source: The path to an SQLite database file. Or, use a value of :memory to cache in memory.

MySQL

The installation includes the JDBC Driver for MySQL. The following is an example JDBC URL:

jdbc:mysql:User=root;Password=root;Server=localhost;Port=3306;Database=cache
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

The JDBC URL for the Microsoft JDBC Driver for SQL Server has the following syntax:

jdbc:sqlserver://[serverName[\instance][:port]][;database=databaseName][;property=value[;property=value] ... ]
For example:
jdbc:sqlserver://localhost\sqlexpress:1433;integratedSecurity=true
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.

    To use integrated security, you will also need to add sqljdbc_auth.dll to a folder on the Windows system path. This file is located in the auth subfolder of the Microsoft JDBC Driver for SQL Server installation. The bitness of the assembly must match the bitness of your JVM.

  • 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

The following is the conventional JDBC URL syntax for the Oracle JDBC Thin driver:

jdbc:oracle:thin:[userId/password]@[//]host[[:port][:sid]]
For example:
jdbc:oracle:thin:scott/tiger@myhost:1521:orcl
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

The following is the JDBC URL syntax for the official PostgreSQL JDBC driver:

jdbc:postgresql:[//[host[:port]]/]database[[?option=value][[&option=value][&option=value] ... ]]
For example, the following connection string connects to a database on the default host (localhost) and port (5432):
jdbc:postgresql:postgres
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. You can specify this in the JDBC URL with the "user" parameter.
  • Password: The password provided for authentication with the PostgreSQL database.

Tableau Connector for 1010data 2020

Cache Driver

The database driver to be used to cache data.

Data Type

string

Default Value

""

Remarks

You can cache to any database for which you have a JDBC driver, including 1010data JDBC drivers.

The cache database is determined based on the CacheDriver and CacheConnection properties. The CacheDriver is the name of the JDBC driver class that you want to use to cache data.

Note that you must also add the CacheDriver JAR file to the classpath.

The following examples show how to cache to several major databases. Refer to CacheConnection for more information on the JDBC URL syntax and typical connection properties.

Derby and Java DB

The driver simplifies Derby configuration. Java DB is the Oracle distribution of Derby. The JAR file is shipped in the JDK. You can find the JAR file, derby.jar, in the db subfolder of the JDK installation. In most caching scenarios, you need to specify only the following, after adding derby.jar to the classpath:

jdbc:c1010:CacheLocation='c:/Temp/cachedir';User=user@company.com;Password=password;
To customize the Derby JDBC URL, use CacheDriver and CacheConnection. For example, to cache to an in-memory database, use a JDBC URL like the following:
jdbc:c1010:CacheDriver=org.apache.derby.jdbc.EmbeddedDriver;CacheConnection='jdbc:derby:memory';User=user@company.com;Password=password;

SQLite

The following is a JDBC URL for the SQLite JDBC driver:

jdbc:c1010:CacheDriver=org.sqlite.JDBC;CacheConnection='jdbc:sqlite:C:/Temp/sqlite.db';User=user@company.com;Password=password;

MySQL

The following is a JDBC URL for the included 1010data JDBC Driver for MySQL:

  jdbc:c1010:Cache Driver=cdata.jdbc.mysql.MySQLDriver;Cache Connection='jdbc:mysql:Server=localhost;Port=3306;Database=cache;User=root;Password=123456';User=user@company.com;Password=password;
  
The 1010data JDBC Driver for MySQL is located in the lib subfolder of the JDBC Driver for 1010data 2020 installation directory.

SQL Server

The following JDBC URL uses the Microsoft JDBC Driver for SQL Server:

jdbc:c1010:Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost\sqlexpress:7437;user=sa;password=123456;databaseName=Cache';User=user@company.com;Password=password;

Oracle

The following is a JDBC URL for the Oracle Thin Client:

jdbc:c1010:Cache Driver=oracle.jdbc.OracleDriver;CacheConnection='jdbc:oracle:thin:scott/tiger@localhost:1521:orcldb';User=user@company.com;Password=password;
NOTE: If using a version of Oracle older than 9i, the cache driver will instead be oracle.jdbc.driver.OracleDriver .

PostgreSQL

The following JDBC URL uses the official PostgreSQL JDBC driver:

jdbc:c1010:CacheDriver=org.postgresql.Driver;CacheConnection='jdbc:postgresql://localhost:5433/postgres?user=postgres&password=admin';User=user@company.com;Password=password;

Tableau Connector 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 Java DB, Oracle's distribution of the Derby database. To cache to Java DB, you will need to add the Java DB JAR file to the classpath. The JAR file, derby.jar, is shipped in the JDK and located in the db subfolder of the JDK installation.

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 ~/Library/Application Support
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.

Tableau Connector 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 CacheDriver and CacheConnection properties, use AutoCache to cache implicitly. Or, use CACHE Statements to cache explicitly.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector for 1010data 2020

Connection Life Time

The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed.

Data Type

string

Default Value

"0"

Remarks

The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed. The default is 0 which indicates there is no limit to the connection lifetime.

Tableau Connector for 1010data 2020

Connect On Open

This property species whether to connect to the 1010data when the connection is opened.

Data Type

bool

Default Value

false

Remarks

When set to true, a connection will be made to 1010data when the connection is opened. This property enables the Test Connection feature available in various database tools.

This feature acts as a NOOP command as it is used to verify a connection can be made to 1010data and nothing from this initial connection is maintained.

Setting this property to false may provide performance improvements (depending upon the number of times a connection is opened).

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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 ~/Library/Application Support
Linux ~/.config

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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

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.

Tableau Connector 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.

Tableau Connector for 1010data 2020

Password

Your 1010data password.

Data Type

string

Default Value

""

Remarks

Your 1010data password.

Tableau Connector for 1010data 2020

Pool Idle Timeout

The allowed idle time for a connection before it is closed.

Data Type

string

Default Value

""

Remarks

The allowed idle time a connection can remain in the pool until the connection is closed. The default is 60 seconds.

Tableau Connector for 1010data 2020

Pool Max Size

The maximum connections in the pool.

Data Type

string

Default Value

"100"

Remarks

The maximum connections in the pool. The default is 100. To disable this property, set the property value to 0 or less.

Tableau Connector for 1010data 2020

Pool Min Size

The minimum number of connections in the pool.

Data Type

string

Default Value

"1"

Remarks

The minimum number of connections in the pool. The default is 1.

Tableau Connector for 1010data 2020

Pool Wait Time

The max seconds to wait for an available connection.

Data Type

string

Default Value

""

Remarks

The max seconds to wait for a connection to become available. If a new connection request is waiting for an available connection and exceeds this time, an error is thrown. By default, new requests wait forever for an available connection.

Tableau Connector 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.

Tableau Connector 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

false

Remarks

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

NOTE: When this property is set to True, the proxy used is determined as follows:

  • A search from the JVM properties (http.proxy, https.proxy, socksProxy, etc.) is performed.
  • In the case that the JVM properties don't exist, a search from java.home/lib/net.properties is performed.
  • In the case that java.net.useSystemProxies is set to True, a search from the SystemProxy is performed.
  • In Windows only, an attempt is made to retrieve these properties from the Internet Options in the registry.

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

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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

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

Tableau Connector 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.

Tableau Connector 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. See the included licensing file to see how to set this property. The runtime key is only available if you purchased an OEM license.

Tableau Connector 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.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

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

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector for 1010data 2020

Use Connection Pooling

This property enables connection pooling.

Data Type

bool

Default Value

false

Remarks

This property enables connection pooling. The default is false. See Connection Pooling for information on using connection pools.

Tableau Connector 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.

Tableau Connector 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.

Tableau Connector 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) 2020 1010data - All rights reserved.
Build 19.0.7333.0