Common ODBC connector based on ANSI SQL

We have provided support to connect to different databases through their ODBC drivers. More specifically, database solutions providing ODBC drivers based on ANSI SQL standard can establish connection with our application.

When it comes to ANSI SQL supportive ODBC, not all functions are supported by vendors but still most of the functions and keywords used would be common and based on the standards. As long as the query that we issue from our Syncfusion Dashboard gets executed in the respective database, we may stay in the live query mode. If any function or keyword is not supported, we would recommend the user to switch to extracted mode and continue the dashboard design as usual further. This is a generic way to approach this scenario.

Live Query Mode : Issuing a query directly to the database via ODBC and retrieving the data as a resultant. This happens when the functions and keywords in the query are accepted by the database via its ODBC driver.

Extracted Mode : When the functions or keywords in a query is not accepted by the database via its ODBC driver, user is provided with an option to extract and move their data to an embedded (internal) data source. Further, we continue issuing queries to the embedded (internal) data source and retrieve the data as a resultant. Following are few of the options which does not have direct or alternate functions under ANSI SQL standards and they remain as a limitation with our Syncfusion Dashboard. But if you still prefer to work on them, then we recommend you switching to the extracted mode.

      •    Standard Deviation and Variance under Aggregation functions

      •    Relative Date Filters

      •    Changing data types dynamically

To start with, first download and install the ODBC driver provided by the respective database vendor. In this example, we have downloaded and configured Teradata ODBC driver available for Windows OS. Once the installation is over, launch our dashboard designer application.

Navigate to the Add Data Source section under Data Sources and you may now find a new connection wizard”. Change the connection type as Others (ANSI SQL) and select the driver which you have installed.

ODBC ANSI SQL connection type

ODBC ANSI SQL Driver list

Once the driver is selected, go to Connect Driver option where database server information needs to be entered in-order to generate an appropriate connection string and establish a connection.

Teradata ODBC driver creadential

ODBC driver credential

After entering the database server information, you can check your connection using Test Connection option.

Test connection succedded

On successful connection, now you are allowed to access the respective database. Further, database schema is obtained from which table information are extracted and populated in the designer canvas. Finally you are now ready to design a dashboard with tables or views available. If you need more knowledge about designing our dashboard, just click here.

Data preview grid window

Data Connection Drivers

The compliant databases whose connectivity through Syncfusion Dashboard Designer was officially confirmed with ANSI SQL standard compliant ODBC drivers are listed below.

Database Driver Name Driver Details
IBM DB2 IBM DB2 ODBC DRIVER – DB2 COPY1 (DB2 ODBC Driver install during the DB2 server installation)
Advantage Database Server (ADS) Advantage StreamlineSQL ODBC (ADS ODBC Driver install during the ADS server installation)
Firebird Firebird/InterBase[r] driver
Google BigQuery Simba ODBC Driver for Google BigQuery
Sybase ASE Adaptive Server Enterprise (Sybase ASE ODBC Driver install during the Sybase ASE server installation)
Sybase IQ Sybase IQ ODBC Driver (Sybase IQ ODBC Driver install during the Sybase IQ server installation)
Teradata Teradata Database ODBC Driver
EXASolution EXASolution Driver
Splunk Splunk ODBC Driver
Aster Database Aster ODBC Driver
Actian Vector Ingres ODBC Driver
IBM PDA (Netezza) NetezzaSQL
Sap SQL Anywhere SQL Anywhere (SAP SQL Anywhere ODBC Driver install during the SQL Anywhere server installation)
MarkLogic MarkLogic SQL ODBC Driver
MongoDB Simba MongoDB ODBC Driver
Apache Cassandra Simba Cassandra ODBC Driver
Amazon DynamoDB CData DynamoDB ODBC Driver