How to execute a sql with MCP?

[Hakstar]

Now, MCP can provide prompts, tools, and resources for LLMs. Most agent frameworks, like Autogen and LangChain, support MCP tools, and prompts can be set in system_message. However, how to use resources effectively has become an important challenge.

For example, when setting up a MySQL server with MCP (which only has one tool: executing SQL), the process involves generating an SQL query, executing it with the tool, and then letting the LLM handle the raw data. But in reality, users only need the raw data itself. I'm not sure if using resources can solve this issue or if there's a better way for users to interact with the LLM.

[saqadri]

Now, MCP can provide prompts, tools, and resources for LLMs. Most agent frameworks, like Autogen and LangChain, support MCP tools, and prompts can be set in system_message. However, how to use resources effectively has become an important challenge.

For example, when setting up a MySQL server with MCP (which only has one tool: executing SQL), the process involves generating an SQL query, executing it with the tool, and then letting the LLM handle the raw data. But in reality, users only need the raw data itself. I'm not sure if using resources can solve this issue or if there's a better way for users to interact with the LLM.

@Hakstar would you be open to sharing some of the code for your agent application here to illustrate your point? That could help me provide more specific feedback or suggestions!

[Hakstar]

I am using an MCP server (https://github.com/designcomputer/mysql_mcp_server/tree/main), and I want to build a MySQL agent that can query data from the database. I created a simple demo based on the example, as shown below.

prompt = """
You are a professional MySQL database agent capable of executing SQL statements using tools to fulfill user requests.

## Query Step Guide
1. First, understand the database structure: Use `SHOW TABLES` to view all available tables.
2. Understand table structure: Use `SHOW COLUMNS FROM <TABLE_NAME>` to check table field information.
3. Understand table relationships: Use `SHOW CREATE TABLE <TABLE_NAME>` to view the table creation statement, including foreign key relationships.
4. Construct and execute appropriate queries.

## Common Issue Handling
- When encountering the error `Table <TABLE_NAME> doesn't exist`:
  * Use `SHOW TABLES` to check all available tables.
  * Verify if the table name is misspelled.

- When unsure about the table structure:
  * Use `DESC <TABLE_NAME>` to check column information.
  * Use `SHOW COLUMNS FROM <TABLE_NAME>` to get detailed column information.

- When needing to understand table relationships:
  * Use `SHOW CREATE TABLE <TABLE_NAME>` to check foreign key relationships.

## Execution Process
1. Analyze user requirements to determine the necessary data and tables.
2. If unsure about the table structure, first query the required table structure.
3. Construct appropriate SQL statements, which may involve fuzzy queries.
4. Execute the query and return the tool invocation results.
5. If an error occurs, analyze the issue and modify the query.

## Notes
- Upon task completion, return the tool invocation results using a markdown table and end with `TERMINATE`.
- If the task cannot proceed, inform the user of the issue and end with `TERMINATE`.
"""
async def example_usage():
    async with app.run() as agent_app:
        logger = agent_app.logger
        context = agent_app.context

        logger.info("Current config:", data=context.config.model_dump())

        mysql_agent = Agent(
            name="mysql",
            instruction=prompt,
            server_names=["mysql"],
        )

        async with mysql_agent:
            result = await mysql_agent.list_tools()
            logger.info("Tools available:", data=result.model_dump())

            llm = await mysql_agent.attach_llm(OpenAIAugmentedLLM)
            result = await llm.generate_str(
                message="Find all `Video` Type device",
                request_params=RequestParams(model="qwen2.5:32b"),
            )
            logger.info(f"Result: {result}")

However, after execution, the LLM processes the raw data returned from the tool call.

[INFO] 2025-03-31T09:04:30 mcp_agent.mcp_basic_agent - Result:  
It looks like you have provided a tool response containing information about some video devices. I can help parse this data and extract key information. Below is the parsed information:  

1. **Non- Device**:  
   - ID: 10 System  
   - Name: Virtual Video  

2. **Devices List**:  
   - Device IDs range from `************` to `***************`.  
   - Names include: ***, *** Reservoir 1 (this actually shares the same ID, which might be an error), *** Reservoir 3, ***, *** Reservoir, *** Mountain Reservoir, *** , *** Village.  

This is a case of invoking a tool, but perhaps querying MySQL data as a Resource within the MCP server would be a better approach? However, the current agent implementation examples contain very little usage of Resources.

I'm not sure if using SQL queries as an agent tool is the best approach. If you have a better implementation model, I would greatly appreciate your valuable suggestions.

[saqadri]

Thank you @Hakstar! I'll get back to you this week. We are also adding support for MCP resources this week, so I can see if that would be a better primitive to follow. Stay tuned