ODBC/JDBC Connector Guide¶

The ODBC/JDBC Client and Server will enable clients to connect to GPUdb via ODBC/JDBC and issue SQL commands.

Installation & Configuration Instructions¶

Server¶

Installation and configuration of the server portion is a simple three step process:

Install the distribution (install the RPM or deb, or unpack the tgz).
To ensure that the ODBC Server starts when gpudb is started, edit the /opt/gpudb/core/etc/gpudb.conf file and set enable_odbc_connector = true.
The default install will listen on all IP addresses of the machine on port 9292. To change it, edit the /opt/gpudb/connectors/odbcserver/bin/GISFederal.GPUdbODBC.ini file and update the ListenAddress and ListenPort settings. ListenAddress can be "0.0.0.0" to instruct the ODBC server to listen on all available IP addresses.

Clients¶

Client connections are configured in two files (odbc.ini and odbcinst.ini) located in the /opt/gpudb/connectors/odbcserver/client/etc folder.

The odbcinst.ini file contains information about the ODBC driver itself, namely, its location. It is unlikely that any of the settings in this file would need to be changed, unless the location of the libSimbaClient.so file is relocated, in which case, the "Driver" setting would need to be updated.

The odbc.ini file contains information about ODBC connections. This file comes preconfigured with a connection to GPUdb running on the local machine on port 9191 and an ODBC Server running on the local machine on port 9292. The parameters in this file are as follows:

Driver: The file name and path of the libSimbaClient.so
SSLCertFile: The certificate (.pem) file to use when UseSsl is enabled.
UseSsl: 0 (off) or 1 (on) - whether or not to use SSL to encrypt communications.
ServerList: The ODBC Server host and port separated by a space.
ParentSet: The name of a collection to restrict client access to.
URL: the url of the GPUdb head node (including port number).

Testing the ODBC setup¶

To test that everything has been properly configured, run the isql tool:

/opt/gpudb/connectors/odbcserver/client/bin/isql -v GPUDBDSN U P

If it succeeds, you should see a message like:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

At this point, everything is properly configured and you can exit the isql tool by typing 'quit' and hitting enter/return.

The common errors are:

[S1000][unixODBC][GIS Federal][GPUdb] (1045) Failed connection test! connect: Connection refused

which means that the client was able to connect to the ODBC server, but the ODBC server was unable to connect to GPUdb. Ensure that GPUdb is started and running on the same host and port that is configured in the odbc.ini file.

[08001][unixODBC][Simba][SimbaClient] (62) Client cannot establish a connection to the server.

which means that the client was unable to connect to the ODBC server. Ensure that it is started and running on the same host and port that is configured in the odbc.ini file.

[01000][unixODBC][Driver Manager]Can't open lib '/some/path/here/libSimbaClient.so' : file not found

which means that the system was unable to locate the libSimbaClient.so library. Check the odbc.ini and odbcinst.ini files in the odbcserver/client/etc folder to ensure that they contain the proper path and filename.

How can I use the ODBC client in my application?¶

In general, you should treat the ODBC client like any other ODBC connection and use the standard tools for your language, referencing the connection by ODBC DSN name.

For Unix variants, the client libraries folder (/opt/gpudb/connectors/odbcserver/client/lib) should be in your LD_LIBRARY_PATH.

How can I use the JDBC client in my Java application?¶

1. Optionally, copy the JDBC driver .jar file into your project.

mkdir -p ./lib
cp /opt/gpudb/connectors/odbcserver/client/lib/SimbaJDBCClient4.jar ./lib

2. Create a JDBC connection

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
.
.
.
Class.forName("com.simba.client.core.jdbc4.SCJDBC4Driver");
Properties p = new Properties();
p.setProperty("UID", "u");
p.setProperty("PWD", "p");
String odbcServerIpAndPort = "127.0.0.1:9292"
String gpudbServerIpAndPort = "127.0.0.1:9191"
String parentSet = "Optional.ParentCollection"
String url = "jdbc:simba://" + odbcServerIpAndPort + ";URL=" + gpudbServerIpAndPort + ";ParentSet=" + parentSet;
Connection = DriverManager.getConnection(url, p)

3. Include the SimbaJDBCClient jar in your classpath.

For Unix variants:

java -cp .:./lib/SimbaJDBCClient4.jar ...

Table Of Contents