OpenLink MCP Server for ODBC

1# OpenLink MCP Server for ODBC
2 
3This document covers the set up and use of a generic ODBC server for the Model Context Protocol (MCP), referred to as an `mcp-odbc` server. It has been developed to provide Large Language Models with transparent access to ODBC-accessible data sources via a Data Source Name configured for a specific ODBC Connector (also called an ODBC Driver).
4 
5![mcp-client-and-servers|648x499](https://www.openlinksw.com/data/gifs/mcp-client-and-servers.gif)
6 
7## Server Implementation
8 
9This **MCP Server for ODBC** is a small TypeScript layer built on top of `node-odbc`. It routes calls to the host system's local ODBC Driver Manager via `node.js` (specifically using `npx` for TypeScript).
10 
11## Operating Environment Set Up & Prerequisites
12 
13While the examples that follow are oriented toward the Virtuoso ODBC Connector, this guide will also work with other ODBC Connectors. We *strongly* encourage code contributions and submissions of usage demos related to other database management systems (DBMS) for incorporation into this project.
14 
15### Key System Components
16 
171. Check the `node.js` version. If it's not `21.1.0` or higher, upgrade or install explicitly using:
18   ```sh
19   nvm install v21.1.0
20   ```
212. Install MCP components using: 
22   ```sh
23   npm install @modelcontextprotocol/sdk zod tsx odbc dotenv
24   ```
253. Set the `nvm` version using: 
26   ```sh
27   nvm alias default 21.1.0
28   ```
29 
30### Installation
31 
321. Run 
33   ```sh
34   git clone https://github.com/OpenLinkSoftware/mcp-odbc-server.git
35   ```
362. Change directory 
37   ```sh
38   cd mcp-odbc-server
39   ```
403. Run 
41   ```sh
42   npm init -y
43   ```
444. Run 
45   ```sh
46   npm install @modelcontextprotocol/sdk zod tsx odbc dotenv
47   ```
48 
49### unixODBC Runtime Environment Checks
50 
511. Check installation configuration (i.e., location of key INI files) by running: 
52   ```sh
53   odbcinst -j
54   ```
552. List available data source names (DSNs) by running: 
56   ```sh
57   odbcinst -q -s
58   ```
59 
60### Environment Variables
61As good security practice, you should use the `.env` file situated in the same directory as the `mcp-ser` to set bindings for the ODBC Data Source Name (`ODBC_DSN`), the User (`ODBC_USER`), the Password (`ODBC_PWD`), the ODBC INI (`ODBCINI`), and, if you want to use the OpenLink AI Layer (OPAL) via ODBC, the target Large Language Model (LLM) API Key (`API_KEY`).
62 
63```sh
64API_KEY=sk-xxx
65ODBC_DSN=Local Virtuoso
66ODBC_USER=dba
67ODBC_PASSWORD=dba
68ODBCINI=/Library/ODBC/odbc.ini 
69```
70 
71# Usage
72 
73## Tools
74After successful installation, the following tools will be available to MCP client applications.
75 
76### Overview
77 
78|name                 |description|
79|:---                 |:---|
80|`get_schemas`        |List database schemas accessible to connected database management system (DBMS).|
81|`get_tables`         |List tables associated with a selected database schema.|
82|`describe_table`     |Provide the description of a table associated with a designated database schema. This includes information about column names, data types, null handling, autoincrement, primary key, and foreign keys|
83|`filter_table_names` |List tables associated with a selected database schema, based on a substring pattern from the `q` input field.|
84|`query_database`     |Execute a SQL query and return results in JSON Lines (JSONL) format.|
85|`execute_query`      |Execute a SQL query and return results in JSON Lines (JSONL) format.|
86|`execute_query_md`   |Execute a SQL query and return results in Markdown table format.|
87|`spasql_query`       |Execute a SPASQL query and return results.|
88|`sparql_query`       |Execute a SPARQL query and return results.|
89|`virtuoso_support_ai`|Interact with the Virtuoso Support Assistant/Agent — a Virtuoso-specific feature for interacting with LLMs|
90 
91### Detailed Description
92 
93- **`get_schemas`**
94  - Retrieve and return a list of all schema names from the connected database.
95  - Input parameters:
96    - `user` (string, optional): Database username. Defaults to `"demo"`.
97    - `password` (string, optional): Database password. Defaults to `"demo"`.
98    - `dsn` (string, optional): ODBC data source name. Defaults to `"Local Virtuoso"`.
99  - Returns a JSON string array of schema names.
100 
101- **`get_tables`**
102  - Retrieve and return a list containing information about tables in a specified schema. If no schema is provided, uses the connection's default schema.
103  - Input parameters:
104    - `schema` (string, optional): Database schema to filter tables. Defaults to connection default.
105    - `user` (string, optional): Database username. Defaults to `"demo"`.
106    - `password` (string, optional): Database password. Defaults to `"demo"`.
107    - `dsn` (string, optional): ODBC data source name. Defaults to `"Local Virtuoso"`.
108  - Returns a JSON string containing table information (e.g., `TABLE_CAT`, `TABLE_SCHEM`, `TABLE_NAME`, `TABLE_TYPE`).
109 
110- **`filter_table_names`**
111  - Filters and returns information about tables whose names contain a specific substring.
112  - Input parameters:
113    - `q` (string, required): The substring to search for within table names.
114    - `schema` (string, optional): Database schema to filter tables. Defaults to connection default.
115    - `user` (string, optional): Database username. Defaults to `"demo"`.
116    - `password` (string, optional): Database password. Defaults to `"demo"`.
117    - `dsn` (string, optional): ODBC data source name. Defaults to `"Local Virtuoso"`.
118  - Returns a JSON string containing information for matching tables.
119 
120- **`describe_table`**
121  - Retrieve and return detailed information about the columns of a specific table.
122  - Input parameters:
123    - `schema` (string, required): The database schema name containing the table.
124    - `table` (string, required): The name of the table to describe.
125    - `user` (string, optional): Database username. Defaults to `"demo"`.
126    - `password` (string, optional): Database password. Defaults to `"demo"`.
127    - `dsn` (string, optional): ODBC data source name. Defaults to `"Local Virtuoso"`.
128  - Returns a JSON string describing the table's columns (e.g., `COLUMN_NAME`, `TYPE_NAME`, `COLUMN_SIZE`, `IS_NULLABLE`).
129 
130- **`query_database`**
131  - Execute a standard SQL query and return the results in JSON format.
132  - Input parameters:
133    - `query` (string, required): The SQL query string to execute.
134    - `user` (string, optional): Database username. Defaults to `"demo"`.
135    - `password` (string, optional): Database password. Defaults to `"demo"`.
136    - `dsn` (string, optional): ODBC data source name. Defaults to `"Local Virtuoso"`.
137  - Returns query results as a JSON string.
138 
139- **`query_database_md`**
140  - Execute a standard SQL query and return the results formatted as a Markdown table.
141  - Input parameters:
142    - `query` (string, required): The SQL query string to execute.
143    - `user` (string, optional): Database username. Defaults to `"demo"`.
144    - `password` (string, optional): Database password. Defaults to `"demo"`.
145    - `dsn` (string, optional): ODBC data source name. Defaults to `"Local Virtuoso"`.
146  - Returns query results as a Markdown table string.
147 
148- **`query_database_jsonl`**
149  - Execute a standard SQL query and return the results in JSON Lines (JSONL) format (one JSON object per line).
150  - Input parameters:
151    - `query` (string, required): The SQL query string to execute.
152    - `user` (string, optional): Database username. Defaults to `"demo"`.
153    - `password` (string, optional): Database password. Defaults to `"demo"`.
154    - `dsn` (string, optional): ODBC data source name. Defaults to `"Local Virtuoso"`.
155  - Returns query results as a JSONL string.
156 
157- **`spasql_query`**
158  - Execute a SPASQL (SQL/SPARQL hybrid) query return results. This is a Virtuoso-specific feature.
159  - Input parameters:
160    - `query` (string, required): The SPASQL query string.
161    - `max_rows` (number, optional): Maximum number of rows to return. Defaults to `20`.
162    - `timeout` (number, optional): Query timeout in milliseconds. Defaults to `30000`, i.e., 30 seconds.
163    - `user` (string, optional): Database username. Defaults to `"demo"`.
164    - `password` (string, optional): Database password. Defaults to `"demo"`.
165    - `dsn` (string, optional): ODBC data source name. Defaults to `"Local Virtuoso"`.
166  - Returns the result from the underlying stored procedure call (e.g., `Demo.demo.execute_spasql_query`).
167 
168- **`sparql_query`**
169  - Execute a SPARQL query and return results. This is a Virtuoso-specific feature.
170  - Input parameters:
171    - `query` (string, required): The SPARQL query string.
172    - `format` (string, optional): Desired result format. Defaults to `'json'`.
173    - `timeout` (number, optional): Query timeout in milliseconds. Defaults to `30000`, i.e., 30 seconds.
174    - `user` (string, optional): Database username. Defaults to `"demo"`.
175    - `password` (string, optional): Database password. Defaults to `"demo"`.
176    - `dsn` (string, optional): ODBC data source name. Defaults to `"Local Virtuoso"`.
177  - Returns the result from the underlying function call (e.g., `"UB".dba."sparqlQuery"`).
178 
179- **`virtuoso_support_ai`**
180  - Utilizes a Virtuoso-specific AI Assistant function, passing a prompt and optional API key. This is a Virtuoso-specific feature.
181  - Input parameters:
182    - `prompt` (string, required): The prompt text for the AI function.
183    - `api_key` (string, optional): API key for the AI service. Defaults to `"none"`.
184    - `user` (string, optional): Database username. Defaults to `"demo"`.
185    - `password` (string, optional): Database password. Defaults to `"demo"`.
186    - `dsn` (string, optional): ODBC data source name. Defaults to `"Local Virtuoso"`.
187  - Returns the result from the AI Support Assistant function call (e.g., `DEMO.DBA.OAI_VIRTUOSO_SUPPORT_AI`).
188 
189## Basic Installation Testing & Troubleshooting
190 
191### MCP Inspector Tool
192 
193#### Canonical MCP Inspector Tool Edition
194 
1951. Start the inspector from the mcp-server directory/folder using the following command:
196    ```sh
197    ODBCINI=/Library/ODBC/odbc.ini npx -y @modelcontextprotocol/inspector npx tsx ./src/main.ts 
198    ```
1992. Click on the "Connect" button, then click on the "Tools" tab to get started.
200 
201    [![MCP Inspector](https://www.openlinksw.com/data/screenshots/mcp-server-inspector-demo-1.png)](https://www.openlinksw.com/data/screenshots/mcp-server-inspector-demo-1.png)
202 
203#### OpenLink MCP Inspector Tool Edition
204 
205This is a fork of the canonical edition that includes a JSON handling bug fix related to use with this MCP Server.
206 
2071. run
208   ```sh
209   git clone git@github.com:OpenLinkSoftware/inspector.git
210   cd inspector
211   ```
2122. run
213   ```sh
214   npm run start
215   ```
2163. Provide the following value in the `Arguments` input field of MCP Inspectors UI from http://localhost:6274
217   ```sh
218   tsx /path/to/mcp-odbc-server/src/main.ts
219   ```
2204. Click on the `Connect` button to initialize your session with the designated MCP Server
221 
222 
223### Apple Silicon (ARM64) Compatibility with MCP ODBC Server Issues
224 
225#### Node x86_64 vs arm64 Conflict Issue
226 
227The x86_64 rather than arm64 edition of `node` may be in place, but the ODBC bridge and MCP server are arm64-based components.
228 
229You can solve this problem by performing the following steps:
230 
2311. Uninstall the x86_64 edition of `node` by running:
232   ```sh
233    nvm uninstall 21.1.0
234   ```
2352. Run the following command to confirm your current shell is in arm64 mode:
236   ```sh
237   arch
238   ```
239   - if that returns x86_64, then run the following command to change the active mode:
240     ```
241     arch arm64
242     ```
2433. Install the arm64 edition of `node` by running:
244   ```sh
245   nvm install 21.1.0
246   ```
247 
248#### Node to ODBC Bridge Layer Incompatibility
249 
250When attempting to use a Model Context Protocol (MCP) ODBC Server on Apple Silicon machines, you may encounter architecture mismatch errors. These occur because the `Node.js` ODBC native module (`odbc.node`) is compiled for ARM64 architecture, but the x86_64-based edition of the unixODBC runtime is being loaded.
251 
252Typical error message:
253 
254```
255Error: dlopen(...odbc.node, 0x0001): tried: '...odbc.node' (mach-o file, but is an incompatible architecture (have 'x86_64', need 'arm64e' or 'arm64'))
256```
257 
258You solve this problem by performing the following steps:
259 
2601. Verify your `Node.js` is running in ARM64 mode:
261 
262   ```bash
263   node -p "process.arch"  # Should output: `arm64`
264   ```
265 
2662. Install unixODBC for ARM64:
267 
268   ```bash
269   # Verify Homebrew is running in ARM64 mode
270   which brew  # Should point to /opt/homebrew/bin/brew
271   
272   # Remove existing unixODBC
273   brew uninstall --force unixodbc
274   
275   # Install ARM64 version
276   arch -arm64 brew install unixodbc
277   ```
278 
2793. Rebuild the Node.js ODBC module for ARM64:
280 
281   ```bash
282   # Navigate to your project
283   cd /path/to/mcp-odbc-server
284   
285   # Remove existing module
286   rm -rf node_modules/odbc
287   
288   # Set architecture environment variable
289   export npm_config_arch=arm64
290   
291   # Reinstall with force build
292   npm install odbc --build-from-source
293   ```
294 
2954. Verify the module is now ARM64:
296 
297   ```bash
298   file node_modules/odbc/lib/bindings/napi-v8/odbc.node
299   # Should show "arm64" instead of "x86_64"
300   ```
301 
302#### Key Points
303 
304- Both unixODBC and the `Node.js` ODBC module must be ARM64-compatible
305- Using environment variables (`export npm_config_arch=arm64`) is more reliable than `npm config` commands
306- Always verify architecture with the `file` command or `node -p "process.arch"`
307- When using Homebrew on Apple Silicon, commands can be prefixed with `arch -arm64` to force use of ARM64 binaries
308 
309## MCP Application Usage
310 
311### Claude Desktop Configuration
312 
313The path for this config file is: `~{username}/Library/Application Support/Claude/claude_desktop_config.json`.
314 
315```json
316{
317    "mcpServers": {
318        "ODBC": {
319            "command": "/path/to/.nvm/versions/node/v21.1.0/bin/node",
320            "args": [
321                "/path/to/mcp-odbc-server/node_modules/.bin/tsx",
322                "/path/to/mcp-odbc-server/src/main.ts"
323            ],
324            "env": {
325                "ODBCINI": "/Library/ODBC/odbc.ini",
326                "NODE_VERSION": "v21.1.0",
327                "PATH": "~/.nvm/versions/node/v21.1.0/bin:${PATH}"
328            },
329            "disabled": false,
330            "autoApprove": []
331        }
332    }
333}
334```
335 
336### Claude Desktop Usage
337 
3381. Start the application.
3392. Apply configuration (from above) via Settings | Developer user interface.
3403. Ensure you have a working ODBC connection to a Data Source Name (DSN).
3414. Present a prompt requesting query execution, e.g.,
342   ```
343   Execute the following query: SELECT TOP * from Demo..Customers
344   ```
345 
346    [![Claude Desktop](https://www.openlinksw.com/data/screenshots/claude-desktp-mcp-odbc-server-demo-1.png)](https://www.openlinksw.com/data/screenshots/claude-desktp-mcp-odbc-server-demo-1.png)
347 
348### Cline (Visual Studio Extension) Configuration
349 
350The path for this config file is: `~{username}/Library/Application\ Support/Code/User/globalStorage/saoudrizwan.claude-dev/settings/cline_mcp_settings.json`
351 
352```json
353{
354  "mcpServers": {
355    "ODBC": {
356      "command": "/path/to/.nvm/versions/node/v21.1.0/bin/node",
357      "args": [
358        "/path/to/mcp-odbc-server/node_modules/.bin/tsx",
359        "/path/to/mcp-odbc-server/src/main.ts"
360      ],
361      "env": {
362        "ODBCINI": "/Library/ODBC/odbc.ini",
363        "NODE_VERSION": "v21.1.0",
364        "PATH": "/path/to/.nvm/versions/node/v21.1.0/bin:${PATH}"
365      },
366      "disabled": false,
367      "autoApprove": []
368    }
369  }
370}
371```
372 
373### Cline (Visual Studio Extension) Usage
374 
3751. Use Shift+Command+`P` to open the Command Palette.
3762. Type in: `Cline`.
3773. Select: `Cline View`, which opens the Cline UI in the VSCode sidebar.
3784. Use the four-squares icon to access the UI for installing and configuring MCP servers.
3796. Apply the Cline Config (from above).
3807. Return to the extension's main UI and start a new task requesting processing of the following prompt:
381   ```
382   "Execute the following query: SELECT TOP 5 * from Demo..Customers"
383   ```
384 
385    [![Cline Extension](https://www.openlinksw.com/data/screenshots/cline-extension-mcp-server-odbc-demo-1.png)](https://www.openlinksw.com/data/screenshots/cline-extension-mcp-server-odbc-demo-1.png)
386 
387### Cursor Configuration
388 
389Use the settings gear to open the configuration menu that includes the MCP menu item for registering and configuring `mcp servers`.
390 
391### Cursor Usage
392 
3931. Use the Command+`I` or Control+`I` key combination to open the Chat Interface.
3942. Select `Agent` from the drop-down at the bottom left of the UI, where the default is `Ask`.
3953. Enter your prompt, qualifying the use of the `mcp-server for odbc` using the pattern: `@odbc {rest-of-prompt}`.
3964. Click on "Accept" to execute the prompt.
397   
398   [![Cursor Editor](https://www.openlinksw.com/data/screenshots/cursor-editor-mcp-config-for-odbc-server-1.png)](https://www.openlinksw.com/data/screenshots/cursor-editor-mcp-config-for-odbc-server-1.png)
399 
400# Related
401 
402* [MCP Inspector Usage Screencast](https://www.openlinksw.com/data/screencasts/mcp-inspector-odbc-sparql-spasql-demo-1.mp4)
403* [Basic Claude Desktop Usage Screencast](https://www.openlinksw.com/data/screencasts/claude-odbc-mcp-sql-spasql-demo-1.mp4)
404* [Basic Cline Visual Studio Code Extension Usage Screencast](https://www.openlinksw.com/data/screencasts/cline-vscode-mcp-odbc-sql-spasql-1.mp4)
405* [Basic Cursor Editor Usage Screencast](https://www.openlinksw.com/data/screencasts/cursor-odbc-mcp-sql-spasql-demo-1.mp4)
406