Connect to Ocient
Connect via ODBC

Open Database Connectivity (ODBC) is a common way to connect to databases across many different programming languages and operating systems. ODBC can be used directly from a program to access the database or can be operated in a Command Line Interface (CLI) mode. This example will describe how to connect using the CLI mode and then provide an example of connecting with ODBC in . For details on using the ODBC driver in code, please see the unixODBC page. 
Step 1: Setup the ODBC Tools and Files
To run this example, unixodbc is required. If unixodbc is not installed on your system, the setup instructions in the ODBC Manual﻿ cover detailed options available to setup the ODBC driver on your system.
On a Mac, unixodbc can be installed using brew install unixodbc
On most Linux distributions, unixodbc can be installed with apt install unixodbc-dev
Check the location of the default odbcinst.ini and ocbc.ini files:
Shell
$ odbcinst -j

unixODBC 2.3.9
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/example_user/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
$ odbcinst -j

unixODBC 2.3.9
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/example_user/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
﻿
Step 2: Setup ODBC Files and Paths
Create a directory for this example:
Shell
cd ~
mkdir odbc-example
cd ~
mkdir odbc-example
﻿
Obtain the latest version of the ODBC driver from  Support and save it to your client machine in this example folder at ~/odbc-example/libocient_odbc.so.
Using the file path listed for DRIVERS and SYSTEM DATA SOURCES in the prior step, copy these files to the example directory:
Shell
cp /usr/local/etc/odbcinst.ini ~/odbc-example/
cp /usr/local/etc/odbc.ini ~/odbc-example/
cp /usr/local/etc/odbcinst.ini ~/odbc-example/
cp /usr/local/etc/odbc.ini ~/odbc-example/
﻿
Set environment variables so the ODBC driver uses your example file for settings:
Shell
export ODBCSYSINI=~/odbc-example
export ODBCINI=~/odbc-example/odbc.ini
export ODBCSYSINI=~/odbc-example
export ODBCINI=~/odbc-example/odbc.ini
﻿
If you run odbcinst -j again, you should see your updated settings in place for the file locations.
Step 3: Configure ODBC for Ocient
Next, edit the ODBC ini files to configure how ODBC will connect to Ocient.
This example assumes:
TLS=UNVERIFIED
IP Address 10.10.1.1 for the SQL Node
ODBC Driver and files are in ~/odbc-example
Standard SQL Port 4050
SQL Nodes are installed by default with a self-signed TLS certificate for encryption by default, but a custom certificate can be present. Depending on the TLS configuration, tls=unverified (default) or tls=on can be the required setting.
Add the following lines to the ~/odbc-example/odbcinst.ini file. This sets up a driver named "OcientDW" for use on your system and defines where ODBC should locate the driver file:
Shell
[OcientDW]
Description = Ocient
Driver = ~/odbc-example/libocient_odbc.so
FileUsage = 1
[OcientDW]
Description = Ocient
Driver = ~/odbc-example/libocient_odbc.so
FileUsage = 1
﻿
Add the following lines to the ~/odbc-example/odbc.ini file. These configure the Data Set named "Ocient" for use in ODBC based programs:
Shell
[Ocient]
Driver = OcientDW
HOST = 10.10.1.1
PORT = 4050
DATABASE = system
TLS = UNVERIFIED
[Ocient]
Driver = OcientDW
HOST = 10.10.1.1
PORT = 4050
DATABASE = system
TLS = UNVERIFIED
﻿
Step 4: Test a Connection
You can test an ODBC driver connection from the command line or a Python script.
Test a connection using the command line
Run the following command with the isql utility that ships with unixodbc to test the ODBC connection replacing the username and password with your configured user details:
Shell
 isql -v Ocient <example_user@system> example_password
 isql -v Ocient <example_user@system> example_password
﻿
Then you will be greeted with a SQL prompt.
Example Response:
Text
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
﻿
Success! Now you are connected and can execute queries based on your user’s permissions. Try running: select * from sys.tables to see what tables are defined.
Test a connection using Python
You can connect to Ocient databases with Python by using the pyodbc module. This module requires Python version 3.7 or later.
Step 5: Run a Python Program
To connect using Python, the pyodbc module can be used.
Shell
$ pip3 install pyodbc
$ pip3 install pyodbc
﻿
With Python, run the following program to connect and run a simple loop over a table, replacing the username and password with your credentials:
Python
import pyodbc

username = 'example_user@system'
password = 'example_password'

conn = pyodbc.connect(f'DSN=Ocient;UID={username};PWD={password}')

''' Create a cursor from the connection '''
cursor = conn.cursor()

cursor.execute("select * from sys.tables")

row = cursor.fetchone()
while row != None:
    for i in range(len(row)):
        print(row[i])
    row = cursor.fetchone()
import pyodbc

username = 'example_user@system'
password = 'example_password'

conn = pyodbc.connect(f'DSN=Ocient;UID={username};PWD={password}')

''' Create a cursor from the connection '''
cursor = conn.cursor()

cursor.execute("select * from sys.tables")

row = cursor.fetchone()
while row != None:
    for i in range(len(row)):
        print(row[i])
    row = cursor.fetchone()
﻿
Example Response:
Text
86e906f2-168e-49f0-a402-ec18bb63f0a1
bird
public
0e1d06d8-c180-4313-a12b-2663aa657a49
6c94896b-ad76-40fa-bb11-5e6ba2120178
4
None
None
ffc9866f-d83e-4c90-a927-d7e1ac5d232b
flock
public
0e1d06d8-c180-4313-a12b-2663aa657a49
6c94896b-ad76-40fa-bb11-5e6ba2120178
4
None
None
86e906f2-168e-49f0-a402-ec18bb63f0a1
bird
public
0e1d06d8-c180-4313-a12b-2663aa657a49
6c94896b-ad76-40fa-bb11-5e6ba2120178
4
None
None
ffc9866f-d83e-4c90-a927-d7e1ac5d232b
flock
public
0e1d06d8-c180-4313-a12b-2663aa657a49
6c94896b-ad76-40fa-bb11-5e6ba2120178
4
None
None
﻿
For more details on using the ODBC driver and the CLI program, see the ODBC Manual﻿.
Updated 17 Oct 2023
Did this page help you?
Connect via JDBC
Connect via pyocient