### Initialize Project and Install Dependencies (Node.js)

Source: https://docs.cloud.cdata.com/en/Clients/Nodejs-Client-Embedded

Sets up a new Node.js project and installs necessary LangChain.js and MCP adapter packages. Ensure Node.js 18 or later is installed.

```bash
mkdir connect-ai-mcp
cd connect-ai-mcp
npm init -y
npm install @langchain/mcp-adapters @langchain/openai @langchain/langgraph
```

--------------------------------

### LlamaIndex MCP Client Setup and Agent Execution

Source: https://docs.cloud.cdata.com/en/Clients/LlamaIndex-Client-Embedded

This Python script sets up the MCP client, retrieves tools, initializes an OpenAI LLM, and creates a ReActAgent to process queries. Replace 'OAUTH_JWT_TOKEN' and 'YOUR_OPENAI_KEY' with your actual credentials. Ensure Python 3.10+ is installed.

```python
from llama_index.llms.openai import OpenAI
from llama_index.tools.mcp import BasicMCPClient, aget_tools_from_mcp_url
from llama_index.core.agent import ReActAgent
import asyncio

client = BasicMCPClient(
    "https://mcp.cloud.cdata.com/mcp",
    headers={"Authorization": "Bearer OAUTH_JWT_TOKEN"}
)

async def main():
    # List available tools
    tools = await aget_tools_from_mcp_url("https://mcp.cloud.cdata.com/mcp", client=client)
   
    llm = OpenAI(model="gpt-4o", api_key="YOUR_OPENAI_KEY")   
    # Create ReActAgent
    agent = ReActAgent(tools=tools, llm=llm, verbose=True)
    # # Run a query
    response = await agent.run("List all the catalogs for me please")
    print(response)

asyncio.run(main())
```

--------------------------------

### Install cdata-connect-ai

Source: https://docs.cloud.cdata.com/en/Clients/Python-Client

Install the base Python connector using pip.

```bash
pip install cdata-connect-ai
```

--------------------------------

### Get Job Details OpenAPI Specification

Source: https://docs.cloud.cdata.com/en/API/Describe-Job

This OpenAPI specification defines the GET /job/{jobId} endpoint for retrieving job details. It includes request parameters, response schemas, and example responses.

```yaml
openapi: 3.1.0
info:
  title: CData Connect AI REST API Embedded
  version: v1
servers:
  - url: https://cloud.cdata.com/api
    description: Production base URL
security:
  - BearerAuth: []
paths:
  /job/{jobId}:
    get:
      tags:
        - Job
      summary: Get Job
      description: |
        Retrieve the details for a specific job.
      operationId: getJob
      parameters:
        - name: jobId
          in: path
          required: true
          schema:
            type: string
          description: The Id of the job you want to retrieve details from.
      responses:
        '200':
          description: OK
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/JobInfo'
              example:
                jobType: Caching
                id: string
                name: string
                enabled: true
                created: '2024-01-01T00:00:00Z'
                lastModified: '2024-01-01T00:00:00Z'
                nextRunTime: '2024-01-01T00:00:00Z'
                intervalUnit: Hour
                intervalValue: 1
                logVerbosity: 1
                lastRun:
                  dateTime: '0001-01-01T00:00:00Z'
                  duration: 63894847975
                  status: JOB_QUEUED
                  rowsAffected: 0
                  nextRunTime: '0001-01-01T00:00:00Z'
                params:
                  timeCheckColumn: ''
                  autoTruncateStrings: 'false'
                  sourceConnection: ff3cfe6-72e2-4edb-914f-4409c01c1418
                  sourceConnectionName: MailChimp1
                  sourceName: MailChimp
                  sourceSchema: MailChimp
                  sourceTable: Conversations
      servers:
        - url: https://cloud.cdata.com/api
          description: Production base URL for job endpoints
components:
  schemas:
    JobInfo:
      type: object
      required:
        - jobType
        - id
        - name
        - enabled
        - created
        - lastModified
        - nextRunTime
        - intervalUnit
        - intervalValue
        - logVerbosity
      properties:
        jobType:
          type: string
          description: The type of job (Caching).
        id:
          type: string
          description: The job's Id.
        name:
          type: string
          description: The job's name.
        enabled:
          type: boolean
          description: A Boolean value indicating whether the job is enabled.
        created:
          type: string
          format: date-time
          description: The date and time the job was created.
        lastModified:
          type: string
          format: date-time
          description: The date and time the job was last modified.
        nextRunTime:
          type: string
          format: date-time
          description: The date and time the job is scheduled to be run next.
        intervalUnit:
          type: string
          description: >-
            A string representing the interval unit for the job
            (Hour/Day/Week/Month).
        intervalValue:
          type: integer
          format: int32
          description: >-
            An integer that represents how many of the selected interval unit
            the job should wait before running again.
        logVerbosity:
          type: integer
          format: int32
          description: A number indicating the level of log verbosity (1-5).
        lastRun:
          $ref: '#/components/schemas/JobLastRun'
        params:
          $ref: '#/components/schemas/JobParams'
      additionalProperties: false
    JobLastRun:
      type: object
      description: >-
        The last run metrics for the job, including the date and time, duration,
        status, rows affected, and next run time.
      properties:
        dateTime:
          type: string
          format: date-time
          nullable: true
        duration:
          type: integer
          format: int64
          nullable: true
        status:
          type: string
          nullable: true
        rowsAffected:
          type: integer
          format: int64
          nullable: true
        nextRunTime:
          type: string
          format: date-time
          nullable: true
          description: The date and time the job is scheduled to be run next.
      additionalProperties: false
    JobParams:
      type: object
      description: >-
        An object containing job parameters. Caching jobs use the fields below;
        other job types may include additional properties.
      properties:
        sourceConnection:
          type: string
          format: uuid

```

--------------------------------

### ROW_NUMBER() Function Example

Source: https://docs.cloud.cdata.com/en/SQL-Reference/Window-Functions

Assigns a unique sequential integer to each row within its partition, starting from 1 for the first row in each partition.

```sql
SELECT Name, Role, Earnings, ROW_NUMBER() OVER (ORDER BY Role) FROM Employees
```

```sql
SELECT Name, Role, Earnings, ROW_NUMBER() OVER (PARTITION BY Role ORDER BY Earnings) FROM Employees
```

--------------------------------

### Offset Pagination Example

Source: https://docs.cloud.cdata.com/en/Data-Sources/APIGlobalSettings

Illustrates offset pagination with specified offset and page size parameters.

```bash
https://myapi?pageOffset=5000&pageSize=1000
```

--------------------------------

### Initialize Node.js Project

Source: https://docs.cloud.cdata.com/en/Clients/Axios-Client

Initializes a new Node.js project. Run this command in your project directory.

```bash
npm init -y
```

--------------------------------

### Truncate Date to Week with Monday Start

Source: https://docs.cloud.cdata.com/en/SQL-Reference/Date-Functions

Truncates a date to the beginning of the week, with Monday as the first day of the week. This example shows the previous Monday.

```sql
SELECT DATE_TRUNC2('week', '2020-02-04', 'monday');
-- Result: '2020-02-02', which is` 
`the previous Monday
```

--------------------------------

### List Jobs OpenAPI Specification

Source: https://docs.cloud.cdata.com/en/API/List-Jobs

This OpenAPI specification defines the GET /job/list endpoint for listing jobs. It includes details on request parameters, responses, and example payloads.

```yaml
openapi: 3.1.0
info:
  title: CData Connect AI REST API Embedded
  version: v1
servers:
  - url: https://cloud.cdata.com/api
    description: Production base URL
security:
  - BearerAuth: []
paths:
  /job/list:
    get:
      tags:
        - Job
      summary: List Jobs
      description: >
        Administrators, impersonating a service user, can request a list of jobs
        for a sub-account.
      operationId: listJobs
      responses:
        '200':
          description: OK
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/JobListResult'
              example:
                jobs:
                  - jobType: Caching
                    id: string
                    name: string
                    enabled: true
                    created: '2024-01-01T00:00:00Z'
                    lastModified: '2024-01-01T00:00:00Z'
                    nextRunTime: '2024-01-01T00:00:00Z'
                    intervalUnit: Hour
                    intervalValue: 1
                    logVerbosity: 1
                    lastRun:
                      dateTime: '0001-01-01T00:00:00Z'
                      duration: 63894847975
                      status: JOB_QUEUED
                      rowsAffected: 0
                      nextRunTime: '0001-01-01T00:00:00Z'
                    params:
                      timeCheckColumn: ''
                      autoTruncateStrings: 'false'
                      sourceConnection: f6f3cfe6-72e2-4edb-914f-4409c01c1418
                      sourceConnectionName: MailChimp1
                      sourceName: MailChimp
                      sourceSchema: MailChimp
                      sourceTable: Conversations
      servers:
        - url: https://cloud.cdata.com/api
          description: Production base URL for job endpoints
components:
  schemas:
    JobListResult:
      type: object
      properties:
        jobs:
          type: array
          items:
            $ref: '#/components/schemas/JobInfo'
          nullable: true
      additionalProperties: false
    JobInfo:
      type: object
      required:
        - jobType
        - id
        - name
        - enabled
        - created
        - lastModified
        - nextRunTime
        - intervalUnit
        - intervalValue
        - logVerbosity
      properties:
        jobType:
          type: string
          description: The type of job (Caching).
        id:
          type: string
          description: The job's Id.
        name:
          type: string
          description: The job's name.
        enabled:
          type: boolean
          description: A Boolean value indicating whether the job is enabled.
        created:
          type: string
          format: date-time
          description: The date and time the job was created.
        lastModified:
          type: string
          format: date-time
          description: The date and time the job was last modified.
        nextRunTime:
          type: string
          format: date-time
          description: The date and time the job is scheduled to be run next.
        intervalUnit:
          type: string
          description: >-
            A string representing the interval unit for the job
            (Hour/Day/Week/Month).
        intervalValue:
          type: integer
          format: int32
          description: >-
            An integer that represents how many of the selected interval unit
            the job should wait before running again.
        logVerbosity:
          type: integer
          format: int32
          description: A number indicating the level of log verbosity (1-5).
        lastRun:
          $ref: '#/components/schemas/JobLastRun'
        params:
          $ref: '#/components/schemas/JobParams'
      additionalProperties: false
    JobLastRun:
      type: object
      description: >-
        The last run metrics for the job, including the date and time, duration,
        status, rows affected, and next run time.
      properties:
        dateTime:
          type: string
          format: date-time
          nullable: true
        duration:
          type: integer
          format: int64
          nullable: true
        status:
          type: string
          nullable: true
        rowsAffected:
          type: integer
          format: int64
          nullable: true
        nextRunTime:
          type: string
          format: date-time
          nullable: true
          description: The date and time the job is scheduled to be run next.
      additionalProperties: false
    JobParams:
      type: object

```

--------------------------------

### Install Connect Gateway from GitHub Release

Source: https://docs.cloud.cdata.com/en/Connect-Gateway

Install the Connect Gateway directly from a GitHub release URL. This method is suitable for air-gapped environments. Ensure you specify the correct version of the chart.

```bash
helm install connect-gateway \
  https://github.com/CDataSoftware/connect-gateway-helm/releases/download/v1.0.0/connect-gateway-1.0.0.tgz \
  --set gateway.locationId=<location-id> \
  --set gateway.accountId=<account-id> \
  --set gateway.apiKey=<api-key> \
  -n connect-gateway --create-namespace
```

--------------------------------

### Calculate Days Since Gregorian Calendar Start - TO_DAYS

Source: https://docs.cloud.cdata.com/en/SQL-Reference/Date-Functions

Use TO_DAYS to get the number of days elapsed since 1582-10-15. This function is equivalent to the MySQL TO_DAYS function.

```sql
SELECT TO_DAYS('02-06-2015');
-- Result: 736000
```

--------------------------------

### PARTITION BY Clause Example

Source: https://docs.cloud.cdata.com/en/SQL-Reference/Window-Functions

Demonstrates how to use the PARTITION BY clause within the OVER clause to divide a window into partitions based on column values. Each partition is processed independently.

```sql
SELECT A, B, OVER (PARTITION BY A ORDER BY B) From Lead
```

--------------------------------

### List Catalogs OpenAPI Specification

Source: https://docs.cloud.cdata.com/en/API/Catalogs

This OpenAPI specification defines the GET /catalogs endpoint for listing user-configured connections. It includes request parameters, response schemas, and example responses.

```yaml
openapi: 3.1.0
info:
  title: CData Connect AI REST API
  version: v1
servers:
  - url: https://cloud.cdata.com/api
    description: Production base URL
security:
  - Basic: []
paths:
  /catalogs:
    get:
      tags:
        - Metadata
      summary: List Catalogs
      description: >
        This operation returns information about available catalogs. Catalogs
        are connections that the user has set up in their account.
      parameters:
        - name: workspace
          description: Restricts results to the specified workspace.
          in: query
          schema:
            type: string
      responses:
        '200':
          description: OK
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/QueryResult'
              example:
                results:
                  - affectedRows: -1
                    schema:
                      - catalogName: CData
                        columnLabel: TABLE_CATALOG
                        columnName: TABLE_CATALOG
                        dataType: 5
                        dataTypeName: VARCHAR
                        length: 255
                        nullable: true
                        ordinal: 0
                        precision: 255
                        scale: 0
                        schemaName: QueryFederation
                        tableName: sys_catalogs
                    rows:
                      - - Salesforce1
components:
  schemas:
    QueryResult:
      type: object
      properties:
        results:
          type: array
          items:
            $ref: '#/components/schemas/ResultSet'
          nullable: true
        error:
          $ref: '#/components/schemas/ErrorDetail'
      additionalProperties: false
    ResultSet:
      type: object
      properties:
        affectedRows:
          type: integer
          format: int32
          description: The number of rows affected by the query, or -1 if not applicable.
        schema:
          type: array
          items:
            $ref: '#/components/schemas/ColumnInfo'
          nullable: true
        rows:
          type: array
          items:
            type: array
            items: {}
          nullable: true
      additionalProperties: false
    ErrorDetail:
      type: object
      properties:
        code:
          $ref: '#/components/schemas/ErrorCode'
        message:
          type: string
          nullable: true
          description: The error message.
      additionalProperties: false
    ColumnInfo:
      type: object
      properties:
        catalogName:
          type: string
          nullable: true
          description: The name of the catalog containing the column.
        columnLabel:
          type: string
          nullable: true
          description: The label of the column.
        columnName:
          type: string
          nullable: true
          description: The name of the column.
        dataType:
          $ref: '#/components/schemas/DataType'
        dataTypeName:
          type: string
          nullable: true
          description: The data type name of the column.
        length:
          type: integer
          format: int32
          description: The length of the column.
        nullable:
          type: boolean
          description: Whether the column is nullable.
        ordinal:
          type: integer
          format: int32
          description: The ordinal position of the column.
        precision:
          type: integer
          format: int32
          nullable: true
          description: The precision of the column.
        scale:
          type: integer
          format: int32
          nullable: true
          description: The scale of the column.
        schemaName:
          type: string
          nullable: true
          description: The name of the schema containing the column.
        tableName:
          type: string
          nullable: true
          description: The name of the table containing the column.
      additionalProperties: false
    ErrorCode:
      enum:
        - 0
        - 1
        - 2
        - 3
        - 4
        - 5
        - 6
        - 7
        - 8
        - 9
        - 10
        - 11
        - 12
        - 13
        - 14
        - 15
        - 16
        - 17
        - 18
        - 19
        - 20
        - 21
        - 22
        - 23
        - 24
        - 25
        - 26
        - 27
        - 28
        - 29
        - 30
        - 31
        - 32
        - 33
        - 34
        - 35
        - 36
        - 37
        - 38

```

--------------------------------

### Start Codex and Sign In

Source: https://docs.cloud.cdata.com/en/Clients/Codex-Client-Embedded

Start the Codex application. For the first run, sign in via ChatGPT and complete browser authentication. Alternatively, set the OPENAI_API_KEY environment variable to use an API key.

```bash
codex
```

--------------------------------

### Number Pagination Example

Source: https://docs.cloud.cdata.com/en/Data-Sources/APIGlobalSettings

Demonstrates number-based pagination with page number and page size parameters.

```bash
https://myapi?pageNum=6&pageSize=1000
```

--------------------------------

### Create Job Request Example (YAML)

Source: https://docs.cloud.cdata.com/en/API/Create-Job

This example demonstrates the structure of a request to create a new job. It includes job type, scheduling, logging, and source parameters. Ensure all required fields are populated correctly.

```yaml
jobType: Caching
intervalUnit: Hour
intervalValue: '1'
logVerbosity: '1'
enabled: 'true'
params:
  timeCheckColumn: ''
  autoTruncateStrings: 'false'
  sourceConnection: 36b2c04f-70f4-44a1-876a-29e42237ee63
  sourceName: OData1
  sourceSchema: OData
  sourceTable: Categories
  mergeKeys:
    - colName: Id
      colType: varchar
      isPrimaryKey: true
    - colName: WorkspaceId
      colType: varchar
      isPrimaryKey: true
```

--------------------------------

### Verify Claude Code Installation

Source: https://docs.cloud.cdata.com/en/Clients/ClaudeCode-Client

After installation, verify that the Claude Code package is installed globally by listing installed npm packages. This command shows the installed version and path.

```bash
npm list -g @anthropic-ai/claude-code
```

--------------------------------

### Install Codex CLI

Source: https://docs.cloud.cdata.com/en/Clients/Codex-Client-Embedded

Install the Codex CLI globally using npm. Ensure Node.js 18 or later is installed.

```bash
npm install -g @openai/codex
```

--------------------------------

### Start Connect Gateway with Docker Compose

Source: https://docs.cloud.cdata.com/en/Connect-Gateway

Use this command to start the Connect Gateway service defined in your `docker-compose.yml` file. Docker Compose will pull the image if it's not already present.

```bash
docker compose up
```

--------------------------------

### Run Metabase JAR File

Source: https://docs.cloud.cdata.com/en/Clients/Metabase-Client

Execute this command in the directory where the Metabase JAR file is located. Ensure you wait for the 'Metabase Initialization Complete' message before proceeding.

```bash
java -jar metabase.jar
```

--------------------------------

### Find pattern start position with PATINDEX

Source: https://docs.cloud.cdata.com/en/SQL-Reference/String-Functions

Use PATINDEX to find the starting position of a pattern within a string. The wildcard '%' can only be used at the start or end of the pattern.

```sql
SELECT PATINDEX('123%', '1234567890');
-- Result: 1
```

```sql
SELECT PATINDEX('%890', '1234567890');
-- Result: 8
```

```sql
SELECT PATINDEX('%456%', '1234567890');
-- Result: 4
```

--------------------------------

### CDATACONNECT.QUERY Example Query

Source: https://docs.cloud.cdata.com/en/Clients/Excel-Client-Functions

Example of a SQL query string to be used with the CDATACONNECT.QUERY function. This specific example selects all data from the 'Automations' table in the MailChimp data source.

```SQL
SELECT * FROM [MailChimp2].[MailChimp].[Automations]
```

--------------------------------

### Initialize LangChain MCP Client and Agent

Source: https://docs.cloud.cdata.com/en/Clients/LangChain-Client-Embedded

Set up the MCP client with server details and load remote tools. Initialize a LangChain ReAct agent with an LLM and the discovered tools to process user prompts.

```python
import asyncio
from langchain_mcp_adapters.client import MultiServerMCPClient
from langchain_openai import ChatOpenAI
from langgraph.prebuilt import create_react_agent
from config import Config

async def main():
    # Initialize MCP client with one or more server URLs    
    mcp_client = MultiServerMCPClient(
        connections={
            "default": {  # you can name this anything
            "transport": "streamable_http",
            "url": Config.MCP_BASE_URL,
            "headers": {"Authorization": f"Bearer {Config.MCP_AUTH}"},
        }
    }
    )
    # Load remote MCP tools exposed by the server
    all_mcp_tools = await mcp_client.get_tools()
    print("Discovered MCP tools:", [tool.name for tool in all_mcp_tools])
    # Create and run the ReAct style agent
    llm = ChatOpenAI(
        model="gpt-4o", 
        temperature=0.2,
        api_key="YOUR_OPEN_API_KEY"   #Use your OpenAPI Key here. This can be found here: https://platform.openai.com/.
    )
    agent = create_react_agent(llm, all_mcp_tools)
    user_prompt = "Tell me how many sales I had in Q1 for the current fiscal year."   #Change prompts as per need
    print(f"\nUser prompt: {user_prompt}")
    # Send a prompt asking the agent to use the MCP tools
    response = await agent.ainvoke(
        { "messages": [{ "role": "user", "content": (user_prompt),}]}
    )
    # Print out the agent's final response
    final_msg = response["messages"][-1].content
    print("Agent final response:", final_msg)

if __name__ == "__main__":
    asyncio.run(main())
```