Kdb+ server for ODBC

In Windows only, you can use ODBC to connect to a q database from a non-q client.
Cookbook/ODBC

Installation

To install:

Ensure your q process has loaded the SQL interpreter.
KxSystems/kdb/s.k

Windows 2003 hotfix KB948459

If using Windows 2003, before installing ensure you have hotfix KB948459 applied.

Troubleshooting

If everything appears to be set up properly but you cannot connect to, or retrieve data from q then try rebooting.

Method

  • if the user or password are not needed for the connection, then you can use the Windows ODBC Data Source Administrator to create a q DSN of host:port, for example localhost:5001
  • if the user or password has to be given, you need to connect with a statement of the form:
DRIVER=kdb+;DBQ=host:port;UID=usr;PWD=pwd;

Start q with the given port number, and load a database:

q)\p 5001
q)\l sp.q

You can now use ODBC from a non-q client to access the database in q. For example (depending on your client application):

h=: ddcon 'dsn=localhost:5001'
ddread h;'select * from s'
+--+-----+--+------+
|s1|smith|20|london|
|s2|jones|10|paris |
|s3|blake|30|paris |
|s4|clark|20|london|
|s5|adams|30|athens|
+--+-----+--+------+

The default language for ODBC is SQL. To use q, prefix with q):

ddread h;'q)select from s'

Connection syntax

The actual connection syntax depends on your ODBC client. Some examples:

From Excel 2003 with Microsoft Query installed, you can use menu Data>Import External Data>New Database Query and select the localhost:5001 data source to import into the current worksheet.

From Excel that supports sql.request:

 =SQL.REQUEST("DRIVER=kdb+;DBQ=localhost:5001;UID=usr;PWD=pwd;",,,"select * from s")

From Visual Basic Add-ins>Visual Data Manager>File>Opendatabase>ODBC:

r=new adodb.recordset
r.Open "select * from s","DRIVER=kdb+;DBQ=localhost:5001;UID=usr;PWD=pwd;"

or

connQ.Open"Provider=MSDASQL.1;DRIVER=kdb+;DBQ=localhost:5001;UID=usr;PWD=pwd;"

From SQL Server via a linked server:

EXEC sp_addlinkedserver @server = N'kdb', @srvproduct = N'',@provider = N'MSDASQL.1', @provstr = 'Provider=MSDASQL.1;DRIVER=kdb+;DBQ=localhost:5001';
EXEC sp_addlinkedsrvlogin 'kdb', 'false', NULL, 'usr', 'pwd'
GO
select * from openquery(kdb, 'q)([]a:til 10)')