GetSqlQuery

Instructions

Implementation Reference

• src/handlers/system/readonly/handleGetSqlQuery.ts:173-240 (handler)

  Main handler function for the GetSqlQuery tool. Uses ADT client to execute SQL queries via SAP ADT Data Preview API, parses XML response, and returns structured results.

  export async function handleGetSqlQuery(context: HandlerContext, args: any) {
    const { connection, logger } = context;
    try {
      logger?.info('handleGetSqlQuery called');
  
      if (!args?.sql_query) {
        throw new McpError(ErrorCode.InvalidParams, 'SQL query is required');
      }
  
      const sqlQuery = args.sql_query;
      const rowNumber = args.row_number || 100; // Default to 100 rows if not specified
  
      logger?.info(`Executing SQL query (rows=${rowNumber})`);
  
      const client = createAdtClient(connection, logger);
      const response = await client
        .getUtils()
        .getSqlQuery({ sql_query: sqlQuery, row_number: rowNumber });
  
      if (response.status === 200 && response.data) {
        logger?.info('SQL query request completed successfully');
  
        // Parse the XML response
        const parsedData = parseSqlQueryXml(
          response.data,
          sqlQuery,
          rowNumber,
          logger,
        );
  
        logger?.debug(
          `Parsed SQL query data: rows=${parsedData.rows.length}/${parsedData.total_rows ?? 0}, columns=${parsedData.columns.length}`,
        );
  
        const result = {
          isError: false,
          content: [
            {
              type: 'text',
              text: JSON.stringify(parsedData, null, 2),
            },
          ],
        };
        if (args.filePath) {
          logger?.debug(`Writing SQL query result to file: ${args.filePath}`);
          writeResultToFile(result, args.filePath);
        }
        return result;
      } else {
        throw new McpError(
          ErrorCode.InternalError,
          `Failed to execute SQL query. Status: ${response.status}`,
        );
      }
    } catch (error) {
      logger?.error('Failed to execute SQL query', error as any);
      // MCP-compliant error response: always return content[] with type "text"
      return {
        isError: true,
        content: [
          {
            type: 'text',
            text: `ADT error: ${String(error)}`,
          },
        ],
      };
    }
  }

• src/handlers/system/readonly/handleGetSqlQuery.ts:6-26 (schema)

  Tool definition with input schema: requires 'sql_query' (string), optional 'row_number' (number, default 100).

  export const TOOL_DEFINITION = {
    name: 'GetSqlQuery',
    available_in: ['onprem', 'cloud'] as const,
    description:
      '[read-only] Execute ABAP SQL SELECT queries on database tables and CDS views via SAP ADT Data Preview API. Use for ad-hoc data retrieval, row counts, and filtered queries.',
    inputSchema: {
      type: 'object',
      properties: {
        sql_query: {
          type: 'string',
          description: 'SQL query to execute',
        },
        row_number: {
          type: 'number',
          description: '[read-only] Maximum number of rows to return',
          default: 100,
        },
      },
      required: ['sql_query'],
    },
  } as const;

• src/handlers/system/readonly/handleGetSqlQuery.ts:52-165 (helper)

  Helper function parseSqlQueryXml that parses the SAP ADT XML response from freestyle SQL queries into a structured JSON format (columns, rows, metadata).

  export function parseSqlQueryXml(
    xmlData: string,
    sqlQuery: string,
    rowNumber: number,
    logger?: ILogger,
  ): SqlQueryResponse {
    try {
      // Extract basic information
      const totalRowsMatch = xmlData.match(
        /<dataPreview:totalRows>(\d+)<\/dataPreview:totalRows>/,
      );
      const totalRows = totalRowsMatch ? parseInt(totalRowsMatch[1], 10) : 0;
  
      const queryTimeMatch = xmlData.match(
        /<dataPreview:queryExecutionTime>([\d.]+)<\/dataPreview:queryExecutionTime>/,
      );
      const queryExecutionTime = queryTimeMatch
        ? parseFloat(queryTimeMatch[1])
        : 0;
  
      // Extract column metadata
      const columns: Array<{
        name: string;
        type: string;
        description?: string;
        length?: number;
      }> = [];
      const columnMatches = xmlData.match(/<dataPreview:metadata[^>]*>/g);
  
      if (columnMatches) {
        columnMatches.forEach((match) => {
          const nameMatch = match.match(/dataPreview:name="([^"]+)"/);
          const typeMatch = match.match(/dataPreview:type="([^"]+)"/);
          const descMatch = match.match(/dataPreview:description="([^"]+)"/);
          const lengthMatch = match.match(/dataPreview:length="(\d+)"/);
  
          if (nameMatch) {
            columns.push({
              name: nameMatch[1],
              type: typeMatch ? typeMatch[1] : 'UNKNOWN',
              description: descMatch ? descMatch[1] : '',
              length: lengthMatch ? parseInt(lengthMatch[1], 10) : undefined,
            });
          }
        });
      }
  
      // Extract row data
      const rows: Array<Record<string, any>> = [];
  
      // Find all column sections
      const columnSections = xmlData.match(
        /<dataPreview:columns>.*?<\/dataPreview:columns>/gs,
      );
  
      if (columnSections && columnSections.length > 0) {
        // Extract data for each column
        const columnData: Record<string, (string | null)[]> = {};
  
        columnSections.forEach((section, index) => {
          if (index < columns.length) {
            const columnName = columns[index].name;
            const dataMatches = section.match(
              /<dataPreview:data[^>]*>(.*?)<\/dataPreview:data>/g,
            );
  
            if (dataMatches) {
              columnData[columnName] = dataMatches.map((match) => {
                const content = match.replace(/<[^>]+>/g, '');
                return content || null;
              });
            } else {
              columnData[columnName] = [];
            }
          }
        });
  
        // Convert column-based data to row-based data
        const maxRowCount = Math.max(
          ...Object.values(columnData).map((arr) => arr.length),
          0,
        );
  
        for (let rowIndex = 0; rowIndex < maxRowCount; rowIndex++) {
          const row: Record<string, any> = {};
          columns.forEach((column) => {
            const columnValues = columnData[column.name] || [];
            row[column.name] = columnValues[rowIndex] || null;
          });
          rows.push(row);
        }
      }
  
      return {
        sql_query: sqlQuery,
        row_number: rowNumber,
        execution_time: queryExecutionTime,
        total_rows: totalRows,
        columns,
        rows,
      };
    } catch (parseError) {
      logger?.error('Failed to parse SQL query XML:', parseError as any);
  
      // Return basic structure on parse error
      return {
        sql_query: sqlQuery,
        row_number: rowNumber,
        columns: [],
        rows: [],
        error: 'Failed to parse XML response',
      } as any;
    }
  }

• src/lib/handlers/groups/SystemHandlersGroup.ts:179-182 (registration)

  Registration of GetSqlQuery tool in the SystemHandlersGroup, mapping GetSqlQuery_Tool definition to handleGetSqlQuery handler.

  {
    toolDefinition: GetSqlQuery_Tool,
    handler: (args: any) => handleGetSqlQuery(this.context, args),
  },

• src/lib/handlers/groups/SystemHandlersGroup.ts:47-54 (registration)

  Import of GetSqlQuery_Tool and handleGetSqlQuery from the handler file into the registration group.

  import {
    TOOL_DEFINITION as GetSession_Tool,
    handleGetSession,
  } from '../../../handlers/system/readonly/handleGetSession';
  import {
    TOOL_DEFINITION as GetSqlQuery_Tool,
    handleGetSqlQuery,
  } from '../../../handlers/system/readonly/handleGetSqlQuery';