GridDB Advanced Edition ODBC driver guide

Revision: 2185

Table of Contents

1 Introduction

1.1 Aim & configuration of this manual

This manual is written for application developers running the GridDB Advanced Edition via the ODBC (Open Database Connectivity) driver.

Knowledge of the following is required to read this manual.

  • Basic knowledge of GridDB Advanced Edition
  • General knowledge of Windows
  • Basic knowledge regarding ODBC-compatible application development tools (VisualStudio, etc.)

The functions described in this manual can be used exclusively by users with GridDB Advanced Edition license.

1.2 About the GridDB Advanced Edition

An interface that can access GridDB data using SQL is provided in the GridDB Advanced Edition. This manual explains the details of the C API (ODBC) used to access a database supported by the GridDB Advanced Edition (hereinafter referred to as GridDB AE).

1.3 Operating environment

The ODBC driver used in GridDB AE can be used in the following OS.

  • Windows Server 2012 R2, Windows Server 2008 R2
  • Windows 10, Windows 8, Windows 7

2 Overview of application development

This chapter provides an overview of application development using the ODBC driver for GridDB AE.

2.1 Overview of ODBC

ODBC (Open Database Connectivity) is a standard interface for accessing database management system (DBMS) from Windows-compatible application advocated by Microsoft.

Relationship between ODBC driver and GridDB AE cluster

Relationship between ODBC driver and GridDB AE cluster

  • ODBC-compatible application (Windows)
    • In order to issue SQL statements and retrieve the results, ODBC functions are processed and invoked by user-made applications and BI tools.
  • ODBC driver manager (Windows)
    • For ODBC functions invoked from an ODBC-compatible application, the OS will control the connection to a specific database of the specified ODBC data source. In addition, management of the data source is also carried out.
  • ODBC driver for GridDB AE (Windows)
    • This is a unique driver that can be used to connect to GridDB AE.
    • A 32-bit version and a 64-bit version are available and should be chosen carefully based on the application. For example, a 32-bit and 64-bit application should use 32-bit and 64-bit driver respectively.
  • GridDB cluster (Linux)
    • This is a GridDB AE cluster operating in Linux OS on another machine.

2.2 Installation method

The ODBC driver for GridDB AE is included in the “\Windows\ODBC” directory of the installation media.

The installation method is as follows.

When installing a 64-bit driver

  1. Select the “GridStoreODBC_64bit_setup.bat”, right-click, and select Run as administrator.
  2. Check that GridStoreODBC64.dll has been copied to the C:\Program Files\TOSHIBA\GridStore\bin directory.

When installing a 32-bit driver

  1. Select the “GridStoreODBC_32bit_setup.bat”, right-click, and select Run as administrator.
  2. Check that GridStoreODBC32.dll has been copied to the C:\Program Files\TOSHIBA\GridStore\bin directory.

[Memo]

  • Both the 32-bit ODBC driver and 64-bit ODBC driver can be installed.

2.3 Registration of ODBC data source

In order to access the GridDB AE database using GridDB ODBC, it is necessary to register the ODBC data source in advance. Registration of the ODBC data source is performed with the following procedure.

For 64-bit

  1. For Windows 7/Windows 2008, start [Data source (ODBC)] from the [Management tools]. For Windows 8/Windows 2012, start [ODBC data source (64-bit)] from the [Management tools].
  2. Create a system data source (system DSN). Select “GridStore ODBC (x64)” when specifying the driver.
    System DSN creation
  3. Set up the connection data to the GridDB cluster when the GridDB ODBC setup screen appears.
    Setup screen

[Input items]

  • Data Source
    • Specify the data source name to be registered to the ODBC driver manager.
    • The application will specify the data source name specified here to connect to the GridDB cluster.
  • Cluster Configuration "Multicast" "Fixed List" "Provider"
    • Select the method to connect a GridDB cluster. It needs to be matched with the cluster configuration on a server.
  • Destination
    • In the case that "Multicast" is selected, the values of /sql/notificationAddress and /sql/notificationPort in the cluster definition file (gs_cluster.json) of a GridDB node need to be specified.
      • Default: 239.0.0.1:41999
    • In the case that "Fixed List" is selected, combine the value of /cluster/notificationMember/transaction/address and /cluster/notificationMember/transaction/port in the cluster definition file (gs_cluster.json) with a ":" and specify the value of each node by separating them with a comma.
      • e.g. (3 nodes): 192.168.10.11:20001,192.168.10.12:20001,192.168.10.13:20001
    • In the case that "Provider" is selected, the value of /cluster/clusterName in the cluster definition file (gs_cluster.json) of a GridDB node needs to be specified.
  • Cluster Name
    • Specify the cluster name of GridDB cluster.
    • Value of /cluster/clusterName in the cluster definition file (gs_cluster.json) of the GridDB node needs to be specified.
  • Database
    • Specify the database in the cluster.
    • Database is a logical data management unit created in a cluster database. Default value is "public" (default database).
  • User Name
    • Specify the user name connected to GridDB cluster.
  • Password
    • Specify the password of the user mentioned above.

[Buttons]

  • Connect Test
    • Check the connection to the GridDB cluster.
  • Save
    • Save the configurations to the registry and close the dialog.
  • Cancel
    • Discard any changes made.

For 32-bit

  1. For Windows 7/Windows 2008, start the command prompt *as administrator,* and run the 32-bit version of ODBC administrator (odbcad32.exe).
    • <Storage location>: %windir%\SysWOW64\odbcad32.exe
    • <Example>: C:\WINDOWS\SysWOW64\odbcad32.exe

    For Windows 8/Windows 2012, start [ODBC data source (32-bit)] from the [Management tools].

  2. Create a system data source (system DSN). Select “GridStore ODBC (x86)” when specifying the driver.
  3. Set up the connection data to the GridDB server in the GridStore ODBC setup screen. The configuration method is the same as the 64-bit version.

[Points to note]

  • If [Data source (ODBC)] is started from the [Management tools] in a 64-bit OS, the 64-bit ODBC administrator will be started and it will not be possible to configure the 32-bit settings.
  • Only GridDB cluster configured with multicast method can be connected.

2.4 Connection method

If a BI/ETL tool is used, please specify the data source name set in the “GridStore ODBC setup screen”.

If ODBC API is used, specify the data source name set in the “GridStore ODBC setup screen” as well.

[Example]

  • Specify in the second argument for SQLConnect functions.
    SQLConnect(hdbc, (SQLTCHAR *)TEXT("GridStoreODBC-db1"), SQL_NTS, (SQLTCHAR *)TEXT(""), SQL_NTS, (SQLTCHAR *)TEXT(""), SQL_NTS);
    

3 Supported APIs

This section describes the APIs supported by the GridDB ODBC driver. All ODBC APIs version 3 and some of the APIs version 2 are supported by the GridDB ODBC driver.

3.1 List of supported APIs

API support status is shown below.

Function nameSupport status
SQLBindCol
SQLBindParameter
SQLBrowseConnect
SQLCancel×
SQLCloseCursor
SQLColAttribute
SQLColumnPrivileges
SQLColumns
SQLConnect
SQLDescribeCol
SQLDescribeParam
SQLDisconnect
SQLDriverConnect
SQLEndTran
SQLError
SQLExecDirect
SQLExecute
SQLFetch
SQLFetchScroll
SQLForeignKeys
SQLFreeHandle
SQLFreeStmt
SQLGetConnectAttr
SQLGetCursorName
SQLGetData
SQLGetDescField
SQLGetDescRec
SQLGetDiagFieldOnly return SQL_SUCCESS
SQLGetDiagRec
SQLGetEnvAttr
SQLGetFunctions
SQLGetInfo
SQLGetStmtAttr
SQLGetTypeInfo
SQLMoreResults
SQLNativeSql
SQLNumResultCols
SQLParamData
SQLPrepare
SQLPrimaryKeys
SQLProcedureColumns
SQLProcedures
SQLPutData
SQLRowCount
SQLSetDescField
SQLSetDescRec
SQLSetEnvAttrOnly return SQL_SUCCESS
SQLSetStmtAttr
SQLSpecialColumns
SQLStatistics
SQLTablePrivileges
SQLTables
  • ✓: Supported.
  • ✗: Not supported.

See below for the API specifications.

4 Samples

Sample programs in the C language using ODBC API and VisualStudio project files are included in the file “gridstore-odbc-sample.zip”. (These are stored under \Windows in the installation media.)

The samples are written and tested (operation check) in VisualStudio 2005 SP1.

  • GridStoreODBC-sample.sln
    • VisualStudio solution file
  • ODBC-sample\GridStoreODBC-sample.cpp
    • Sample source
  • GridStoreODBC-sample.vcproj
    • Project file

Try to perform table creation, data registration, and search. The project settings are configured for 64-bit and 32-bit use.

The following ODBC data source settings are required to execute a program.

For 64-bit

Data source name: GridStoreODBC-test64bit

For 32-bit

Data source name: GridStoreODBC-test32bit