Integrations
Uses .env files for securely storing and accessing configuration variables like API keys and database credentials.
Offers the ability to return SQL query results formatted as Markdown tables through the execute_query_md tool.
Provides routing of database calls through Node.js to the host system's ODBC Driver Manager, allowing for connection to various database systems.
Introduction
This 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).
Server Implementation
This 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).
Operating Environment Set Up & Prerequisites
While 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.
Key System Components
- Check the
node.js
version. If it's not at least21.1.0
or higher, upgrade or install explicitly using:Copy - Install MCP components using: Copy
- Set the
nvm
version using:Copy
Installation
- Run Copy
- Change directory Copy
- Run Copy
- Run Copy
unixODBC Runtime Environment Checks
- Check installation configuration (i.e., location of key INI files) by running: Copy
- List available data source names (DSNs) by running: Copy
Environment Variables
As 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
).
Usage
Tools
After successful installation, the following tools will be available to MCP client applications.
Overview
name | description |
---|---|
get_schemas | List database schemas accessible to connected database management system (DBMS). |
get_tables | List tables associated with a selected database schema. |
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 |
filter_table_names | List tables associated with a selected database schema, based on a substring pattern from the q input field. |
query_database | Execute a SQL query and return results in JSON Lines (JSONL) format. |
execute_query | Execute a SQL query and return results in JSON Lines (JSONL) format. |
execute_query_md | Execute a SQL query and return results in Markdown table format. |
spasql_query | Execute a SPASQL query and return results. |
sparql_query | Execute a SPARQL query and return results. |
virtuoso_support_ai | Interact with the Virtuoso Support Assistant/Agent — a Virtuoso-specific feature for interacting with LLMs |
Detailed Description
get_schemas
- Retrieve and return a list of all schema names from the connected database.
- Input parameters:
user
(string, optional): Database username. Defaults to"demo"
.password
(string, optional): Database password. Defaults to"demo"
.dsn
(string, optional): ODBC data source name. Defaults to"Local Virtuoso"
.
- Returns a JSON string array of schema names.
get_tables
- Retrieve and return a list containing information about tables in a specified schema. If no schema is provided, uses the connection's default schema.
- Input parameters:
schema
(string, optional): Database schema to filter tables. Defaults to connection default.user
(string, optional): Database username. Defaults to"demo"
.password
(string, optional): Database password. Defaults to"demo"
.dsn
(string, optional): ODBC data source name. Defaults to"Local Virtuoso"
.
- Returns a JSON string containing table information (e.g.,
TABLE_CAT
,TABLE_SCHEM
,TABLE_NAME
,TABLE_TYPE
).
filter_table_names
- Filters and returns information about tables whose names contain a specific substring.
- Input parameters:
q
(string, required): The substring to search for within table names.schema
(string, optional): Database schema to filter tables. Defaults to connection default.user
(string, optional): Database username. Defaults to"demo"
.password
(string, optional): Database password. Defaults to"demo"
.dsn
(string, optional): ODBC data source name. Defaults to"Local Virtuoso"
.
- Returns a JSON string containing information for matching tables.
describe_table
- Retrieve and return detailed information about the columns of a specific table.
- Input parameters:
schema
(string, required): The database schema name containing the table.table
(string, required): The name of the table to describe.user
(string, optional): Database username. Defaults to"demo"
.password
(string, optional): Database password. Defaults to"demo"
.dsn
(string, optional): ODBC data source name. Defaults to"Local Virtuoso"
.
- Returns a JSON string describing the table's columns (e.g.,
COLUMN_NAME
,TYPE_NAME
,COLUMN_SIZE
,IS_NULLABLE
).
query_database
- Execute a standard SQL query and return the results in JSON format.
- Input parameters:
query
(string, required): The SQL query string to execute.user
(string, optional): Database username. Defaults to"demo"
.password
(string, optional): Database password. Defaults to"demo"
.dsn
(string, optional): ODBC data source name. Defaults to"Local Virtuoso"
.
- Returns query results as a JSON string.
query_database_md
- Execute a standard SQL query and return the results formatted as a Markdown table.
- Input parameters:
query
(string, required): The SQL query string to execute.user
(string, optional): Database username. Defaults to"demo"
.password
(string, optional): Database password. Defaults to"demo"
.dsn
(string, optional): ODBC data source name. Defaults to"Local Virtuoso"
.
- Returns query results as a Markdown table string.
query_database_jsonl
- Execute a standard SQL query and return the results in JSON Lines (JSONL) format (one JSON object per line).
- Input parameters:
query
(string, required): The SQL query string to execute.user
(string, optional): Database username. Defaults to"demo"
.password
(string, optional): Database password. Defaults to"demo"
.dsn
(string, optional): ODBC data source name. Defaults to"Local Virtuoso"
.
- Returns query results as a JSONL string.
spasql_query
- Execute a SPASQL (SQL/SPARQL hybrid) query return results. This is a Virtuoso-specific feature.
- Input parameters:
query
(string, required): The SPASQL query string.max_rows
(number, optional): Maximum number of rows to return. Defaults to20
.timeout
(number, optional): Query timeout in milliseconds. Defaults to30000
, i.e., 30 seconds.user
(string, optional): Database username. Defaults to"demo"
.password
(string, optional): Database password. Defaults to"demo"
.dsn
(string, optional): ODBC data source name. Defaults to"Local Virtuoso"
.
- Returns the result from the underlying stored procedure call (e.g.,
Demo.demo.execute_spasql_query
).
sparql_query
- Execute a SPARQL query and return results. This is a Virtuoso-specific feature.
- Input parameters:
query
(string, required): The SPARQL query string.format
(string, optional): Desired result format. Defaults to'json'
.timeout
(number, optional): Query timeout in milliseconds. Defaults to30000
, i.e., 30 seconds.user
(string, optional): Database username. Defaults to"demo"
.password
(string, optional): Database password. Defaults to"demo"
.dsn
(string, optional): ODBC data source name. Defaults to"Local Virtuoso"
.
- Returns the result from the underlying function call (e.g.,
"UB".dba."sparqlQuery"
).
virtuoso_support_ai
- Utilizes a Virtuoso-specific AI Assistant function, passing a prompt and optional API key. This is a Virtuoso-specific feature.
- Input parameters:
prompt
(string, required): The prompt text for the AI function.api_key
(string, optional): API key for the AI service. Defaults to"none"
.user
(string, optional): Database username. Defaults to"demo"
.password
(string, optional): Database password. Defaults to"demo"
.dsn
(string, optional): ODBC data source name. Defaults to"Local Virtuoso"
.
- Returns the result from the AI Support Assistant function call (e.g.,
DEMO.DBA.OAI_VIRTUOSO_SUPPORT_AI
).
Basic Installation Testing & Troubleshooting
MCP Inspector Tool
Canonical MCP Inspector Tool Edition
- Start the inspector from the mcp-server directory/folder using the following command:Copy
- Click on the "Connect" button, then click on the "Tools" tab to get started.
OpenLink MCP Inspector Tool Edition
This is a fork of the canonical edition that includes a JSON handling bug fix related to use with this MCP Server.
- runCopy
- runCopy
- Provide the following value in the
Arguments
input field of MCP Inspectors UI from http://localhost:6274Copy - Click on the
Connect
button to initialize your session with the designated MCP Server
Apple Silicon (ARM64) Compatibility with MCP ODBC Server Issues
Node x86_64 vs arm64 Conflict Issue
The x86_64 rather than arm64 edition of node
may be in place, but the ODBC bridge and MCP server are arm64-based components.
You can solve this problem by performing the following steps:
- Uninstall the x86_64 edition of
node
by running:Copy - Run the following command to confirm your current shell is in arm64 mode:Copy
- if that returns x86_64, then run the following command to change the active mode:Copy
- if that returns x86_64, then run the following command to change the active mode:
- Install the arm64 edition of
node
by running:Copy
Node to ODBC Bridge Layer Incompatibility
When 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.
Typical error message:
You solve this problem by performing the following steps:
- Verify your Node.js is running in ARM64 mode:Copy
- Install unixODBC for ARM64:Copy
- Rebuild the Node.js ODBC module for ARM64:Copy
- Verify the module is now ARM64:Copy
Key Points
- Both unixODBC and the Node.js ODBC module must be ARM64-compatible
- Using environment variables (
export npm_config_arch=arm64
) is more reliable than npm config commands - Always verify architecture with the
file
command ornode -p "process.arch"
- When using Homebrew on Apple Silicon, commands can be prefixed with
arch -arm64
to force use of ARM64 binaries
MCP Application Usage
Claude Desktop Configuration
The path for this config file is: ~{username}/Library/Application Support/Claude/claude_desktop_config.json
.
Claude Desktop Usage
- Start the application.
- Apply configuration (from above) via Settings | Developer user interface.
- Ensure you have a working ODBC connection to a Data Source Name (DSN).
- Present a prompt requesting query execution, e.g.,Copy
Cline (Visual Studio Extension) Configuration
The path for this config file is: ~{username}/Library/Application\ Support/Code/User/globalStorage/saoudrizwan.claude-dev/settings/cline_mcp_settings.json
Cline (Visual Studio Extension) Usage
- Use Shift+Command+P to open the Command Palette.
- Type in:
Cline
. - Select: Cline View, which opens the Cline UI in the VSCode sidebar.
- Use the four-squares icon to access the UI for installing and configuring MCP servers.
- Apply the Cline Config (from above).
- Return to the extension's main UI and start a new task requesting processing of the following prompt:Copy
Cursor Configuration
Use the settings gear to open the configuration menu that includes the MCP menu item for registering and configuring mcp servers
.
Cursor Usage
- Use the
Command + I
orControl + I
key combination to open the Chat Interface. - Select
Agent
from the drop-down at the bottom left of the UI, where the default isAsk
. - Enter your prompt, qualifying the use of the
mcp-server for odbc
using the pattern:@odbc {rest-of-prompt}
. - Click on "Accept" to execute the prompt.
Related
This server cannot be installed
local-only server
The server can only run on the client's local machine because it depends on local resources.
Provides generic Open Database Connectivity (ODBC) to any Database Management System (DBMS) that's accessible via an ODBC Connector (Driver).
Related MCP Servers
- -securityAlicense-qualityEnables execution of SQL queries and management of Microsoft SQL Server database connections through the Model Context Protocol.Last updated -10TypeScriptMIT License
- -securityAlicense-qualityAn MCP server that enables MySQL database integration with Claude. You can execute SQL queries and manage database connections.Last updated -1PythonMIT License
- -security-license-qualityA Model Context Protocol server that provides tools for connecting to and interacting with various database systems (SQLite, PostgreSQL, MySQL/MariaDB, SQL Server) through a unified interface.Last updated -Python
- -security-license-qualityEnables LLM tools like Claude Desktop to query databases via ODBC connections, allowing access, analysis, and insight generation from database data while maintaining security through read-only safeguards.Last updated -PythonMIT License
Appeared in Searches
- Information about Java programming language or related topics
- Connecting to PostgreSQL database, retrieving table schema, and executing SQL queries
- Information or resources about SQL Server
- Running MySQL Queries Through a MySQL Socket
- A server for connecting AWS infrastructure to a real-time AI-powered dashboard