Skip to content

Q client for ODBC

In Windows and Linux, you can use ODBC to connect to a non-kdb+ database from q.

Installation

To install, download

q q/l32 q/l64 q/w32 q/w64
≥V3.0 odbc.so odbc.so odbc.dll odbc.dll
≤V2.8 odbc.so odbc.so odbc.dll odbc.dll

Mixed versions

If you mix up the library versions, you’ll likely observe a type error when opening the connection.

Start kdb+ and load odbc.k – this populates the .odbc context.

Unix systems

Ensure you have unixODBC installed, and that LD_LIBRARY_PATH includes the path to the odbc.so, e.g. for 64-bit Linux 


$ export LD_LIBRARY_PATH=\(LD_LIBRARY_PATH:\)QHOME/l64
unixODBC configuration guide

Method

First open an ODBC connection to a database. To do so, define a DSN (database source name), and then connect to the DSN using .odbc.open. This returns a connection handle, which is used for subsequent ODBC calls:

q)\l odbc.k
q)h:.odbc.open "dsn=northwind"          / use DSN to connect northwind database
q).odbc.tables h                        / list tables
`Categories`Customers`Employees`Order Details`Orders`Products..
q).odbc.eval[h;"select * from Orders"]  / run a select statement
OrderID CustomerID EmployeeID OrderDate  RequiredDate..
-----------------------------------------------------..
10248   WILMK      5          1996.07.04 1996.08.01  ..
10249   TRADH      6          1996.07.05 1996.08.16  ..
10250   HANAR      4          1996.07.08 1996.08.05  ..
..
Alternatively, use .odbc.load to load the entire database into q: 
q)\l odbc.k
q).odbc.load "dsn=northwind"           / load northwind database
q)Orders
OrderID| CustomerID EmployeeID OrderDate  RequiredDate ..
-------| ----------------------------------------------..
10248  | WILMK      5          1996.07.04 1996.08.01   ..
10249  | TRADH      6          1996.07.05 1996.08.16   ..
10250  | HANAR      4          1996.07.08 1996.08.05   ..
..

ODBC functions

Functions defined in the .odbc context:

close

Closes an ODBC connection handle:

.odbc.close x

Where x is the connection value returned from .odbc.open.

eval

Evaluate a SQL expression:

.odbc.eval[x;y]

Where

  • x is either
    • the connection value returned from .odbc.open.
    • a 2 item list containing the connection value returned from .odbc.open, and a timeout (long).
  • y is the statement to execute on the data source.
q)sel:"select CompanyName,Phone from Customers where City='London'"
q)b:.odbc.eval[h;sel]
q)b
CompanyName             Phone
----------------------------------------
"Around the Horn"       "(171) 555-7788"
"B's Beverages"         "(171) 555-1212"
"Consolidated Holdings" "(171) 555-2282"
"Eastern Connection"    "(171) 555-0297"
"North/South"           "(171) 555-7733"
"Seven Seas Imports"    "(171) 555-1717"
q)select from b where Phone like "*1?1?"
CompanyName          Phone
-------------------------------------
"B's Beverages"      "(171) 555-1212"
"Seven Seas Imports" "(171) 555-1717"
q)b:.odbc.eval[(h;5);sel)       / same query with 5 second timeout

load

Loads an entire database into the session:

.odbc.load x

Where x is the same parameter definition as that passed to .odbc.open.

q).odbc.load "dsn=northwind"
q)\a
`Categories`Customers`Employees`OrderDetails`Orders`Products`Shippers`Supplie..
q)Shippers
ShipperID| CompanyName        Phone
---------| -----------------------------------
1        | "Speedy Express"   "(503) 555-9831"
2        | "United Package"   "(503) 555-3199"
3        | "Federal Shipping" "(503) 555-9931"

open

Open a connection to a database.

.odbc.open x

Where x is a

  • string representing an ODBC connection string. Can include DSN and various driver/vendor defined values. For example: 
    q)h:.odbc.open "dsn=kdb"                     
q)h:.odbc.open "driver=Microsoft Access Driver (*.mdb, *.accdb);dbq=C:\\CDCollection.mdb"
q)h:.odbc.open "dsn=kdb;uid=my_username;pwd=my_password"
  • mixed list of connection string and timeout (long). For example: 
    q)h:.odbc.open ("dsn=kdb;";60)
  • symbol representing a DSN. The symbol value may end with the following supported values for shortcut operations:
    • .dsn is a shortcut for file DSN. For example: 
      h:.odbc.open `test.dsn                   / uses C:\Program Files\Common Files\odbc/data source\test.dsn on windows
                                         / and /etc/ODBCDataSources/test.dsn on linux
    • .mdb is a shortcut for the Microsoft Access driver. For example: 
      q)h:.odbc.open `$"C:\\CDCollection.mdb"  / resolves to "driver=Microsoft Access Driver (*.mdb);dbq=C:\\CDCollection"
      Note that the driver name above must match the driver installed. If the driver name differs, an alternative is to the use a string value rather than this shortcut.
    • .mdf is a shortcut for the SQL Server driver. For example: 
      q)h:.odbc.open `my_db.mdf                / resolves to "driver=sql server;server=(local);trusted_connection=yes;database=my_db"
      Note that the driver name above must match the driver installed. If the driver name differs, an alternative is to the use a string value rather than this shortcut.
  • list of three symbols. First symbol represents the DSN, the second is the username, and the third symbol is for password.

Returns an ODBC connection handle.

tables

List tables in database:

.odbc.tables x

Where x is the connection value returned from .odbc.open.

q).odbc.tables h
`Categories`Customers`Employees`Order Details`Orders`Products...

views

List views in database:

.odbc.views x

Where x is the connection value returned from .odbc.open.

q).odbc.views h
`Alphabetical List of Products`Category Sales for 1997`Current...

Tracing

ODBC has the capability to trace the ODBC API calls to a log file; sometimes this can be helpful in resolving unusual or erroneous behavior. On Unix, you can activate the tracing by adding

[ODBC]
Trace         = 1
TraceFile     =/tmp/odbc.log
to the odbcinst.ini file, which can typically be found in /etc or /usr/local/etc.

MSDN.aspx) for tracing on Windows