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.
- in W32, download KxSystems/kdb/w32/odbc.zip and run it to install the q ODBC driver
- in W64, download KxSystems/kdb/w64/odbc.zip and extract it to a temporary directory. Run
d0.exe
to install the q ODBC driver.
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 examplelocalhost: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)')