(SQL Server) Demonstrates Manual Tool Function Calls

See more AI Examples

Note: This example requires Chilkat v11.4.0 or greater.

For more information, see https://www.chilkatsoft.com/ai_tool_function_caling_briefly_explained.asp

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- Create the following JSON to define tool functions available for the AI to use.
    -- Note: You'll use the following JSON format regardless of the AI provider, whether
    -- it be ChatGPT, Gemini, Claude, Grok, etc.  Chilkat automatically converts to the required
    -- format needed for a given AI provider.

    -- In this example, the application is providing two functions the AI may choose to call.

    -- {
    --   "tools": [
    --     {
    --       "name": "get_horoscope",
    --       "description": "Get today's horoscope for an astrological sign.",
    --       "parameters": {
    --         "properties": {
    --           "sign": {
    --             "type": "string",
    --             "description": "An astrological sign like Taurus or Aquarius"
    --           }
    --         }
    --       }
    --     },
    --     {
    --       "name": "get_compatibility",
    --       "description": "Returns compatibility analysis between two zodiac signs, including a score and explanation.",
    --       "parameters": {
    --         "properties": {
    --           "sign1": {
    --             "type": "string",
    --             "description": "The first zodiac sign (e.g., Aries, Taurus, Gemini)."
    --           },
    --           "sign2": {
    --             "type": "string",
    --             "description": "The second zodiac sign (e.g., Aries, Taurus, Gemini)."
    --           },
    --           "relationship_type": {
    --             "type": "string",
    --             "description": "Type of compatibility to evaluate. (e.g., romantic, friendship, professional, general)"
    --           },
    --           "detail_level": {
    --             "type": "string",
    --             "description": "Level of detail in the response. (e.g., short, medium, detailed)"
    --           }
    --         }
    --       }
    --     }
    --   ]
    -- }

    DECLARE @jsonTools int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonTools OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @toolIdx int
    SELECT @toolIdx = 0

    EXEC sp_OASetProperty @jsonTools, 'I', @toolIdx
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].name', 'get_horoscope'
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].description', 'Get today''s horoscope for an astrological sign.'
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].parameters.properties.sign.type', 'string'
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].parameters.properties.sign.description', 'An astrological sign like Taurus or Aquarius'

    SELECT @toolIdx = @toolIdx + 1
    EXEC sp_OASetProperty @jsonTools, 'I', @toolIdx
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].name', 'get_compatibility'
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].description', 'Returns compatibility analysis between two zodiac signs, including a score and explanation.'
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].parameters.properties.sign1.type', 'string'
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].parameters.properties.sign1.description', 'The first zodiac sign (e.g., Aries, Taurus, Gemini).'
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].parameters.properties.sign2.type', 'string'
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].parameters.properties.sign2.description', 'The second zodiac sign (e.g., Aries, Taurus, Gemini).'
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].parameters.properties.relationship_type.type', 'string'
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].parameters.properties.relationship_type.description', 'Type of compatibility to evaluate. (e.g., romantic, friendship, professional, general)'
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].parameters.properties.detail_level.type', 'string'
    EXEC sp_OAMethod @jsonTools, 'UpdateString', @success OUT, 'tools[i].parameters.properties.detail_level.description', 'Level of detail in the response. (e.g., short, medium, detailed)'

    -- More tools can be added as desired..

    EXEC sp_OASetProperty @jsonTools, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonTools, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @ai int
    EXEC @hr = sp_OACreate 'Chilkat.Ai', @ai OUT

    -- Register the tools that will be made available to the AI.
    EXEC sp_OAMethod @ai, 'RegisterManualTools', @success OUT, @jsonTools

    -- The provider can be "openai", "google", "claude", "grok", "mistral", "custom", etc.
    EXEC sp_OASetProperty @ai, 'Provider', 'openai'
    -- Use your provider's API key.
    EXEC sp_OASetProperty @ai, 'ApiKey', 'MY_API_KEY'
    -- Choose a model.
    EXEC sp_OASetProperty @ai, 'Model', 'gpt-5-mini'

    -- Tool function calling must always occur within a conversation.
    DECLARE @conversation_name nvarchar(4000)
    SELECT @conversation_name = 'convo_astrology'
    DECLARE @sysMessage nvarchar(4000)
    SELECT @sysMessage = 'You are a helpful astrologer'
    DECLARE @devMessage nvarchar(4000)
    SELECT @devMessage = 'Respond only with a horoscope generated by a tool. Use the tool output as the final answer.'
    EXEC sp_OAMethod @ai, 'NewConvo', @success OUT, @conversation_name, @sysMessage, @devMessage

    -- Provide inputs
    EXEC sp_OAMethod @ai, 'InputAddText', @success OUT, 'What is my horoscope? I am an Aquarius.'

    -- Send inputs, tool functions, etc. and ask for a "text" response.
    EXEC sp_OAMethod @ai, 'Ask', @success OUT, 'text'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ai, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonTools
        EXEC @hr = sp_OADestroy @ai
        RETURN
      END

    -- Did the AI respond with requests for tool function calls?
    EXEC sp_OAGetProperty @ai, 'HasFunctionCalls', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN

        DECLARE @jsonFn int
        EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonFn OUT

        EXEC sp_OASetProperty @jsonFn, 'EmitCompact', 0

        EXEC sp_OAMethod @ai, 'GetFunctionCalls', @success OUT, @jsonFn
        EXEC sp_OAMethod @jsonFn, 'Emit', @sTmp0 OUT
        PRINT @sTmp0

        -- Note: Chilkat will convert responses from all AI providers to this format:

        -- {
        --   "function_call": [
        --     {
        --       "name": "get_horoscope",
        --       "call_id": "call_RYmeysYQFocFc7Z2ofkv61dW",
        --       "arguments": "{\"sign\":\"Aquarius\"}",
        --       "args": {
        --         "sign": "Aquarius"
        --       }
        --     }
        --   ]
        -- }

        DECLARE @numFnCalls int
        EXEC sp_OAMethod @jsonFn, 'SizeOfArray', @numFnCalls OUT, 'function_call'
        DECLARE @fn_idx int
        SELECT @fn_idx = 0
        WHILE (@fn_idx < @numFnCalls)
          BEGIN
            EXEC sp_OASetProperty @jsonFn, 'I', @fn_idx

            DECLARE @sbFnName int
            EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbFnName OUT

            EXEC sp_OAMethod @jsonFn, 'StringOfSb', @success OUT, 'function_call[i].name', @sbFnName
            DECLARE @callId nvarchar(4000)
            EXEC sp_OAMethod @jsonFn, 'StringOf', @callId OUT, 'function_call[i].call_id'

            EXEC sp_OAMethod @sbFnName, 'ContentsEqual', @iTmp0 OUT, 'get_horoscope', 1
            IF @iTmp0 = 1
              BEGIN

                -- The get_horoscope function (as defined above) has one argument named "sign".
                DECLARE @zodiac_sign nvarchar(4000)
                EXEC sp_OAMethod @jsonFn, 'StringOf', @zodiac_sign OUT, 'function_call[i].args.sign'

                PRINT 'zodiac_sign = ' + @zodiac_sign

                -- Insert application code here to call your app's get_horoscope function, passing the zodiac_sign to it..

                -- For this example, we'll pretend the app's get_horoscope function returned the following:
                DECLARE @applicationFnCallResult nvarchar(4000)
                SELECT @applicationFnCallResult = 'Aquarius: Next Tuesday you will befriend a baby otter.'

                -- Provide the tool call result as an input for the followup Ask.
                EXEC sp_OAMethod @ai, 'InputAddFnResult', @success OUT, @callId, @applicationFnCallResult
              END
            -- Your application would add code to check for and handle each possible function call.

            SELECT @fn_idx = @fn_idx + 1
          END

        -- After making the requested tool function calls, send the results back to the AI.
        EXEC sp_OAMethod @ai, 'Ask', @success OUT, 'text'
        IF @success = 0
          BEGIN
            EXEC sp_OAGetProperty @ai, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @jsonTools
            EXEC @hr = sp_OADestroy @ai
            EXEC @hr = sp_OADestroy @jsonFn
            EXEC @hr = sp_OADestroy @sbFnName
            RETURN
          END
      END

    -- Get the final AI response.
    DECLARE @sbResponse int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponse OUT

    EXEC sp_OAMethod @ai, 'GetOutputTextSb', @success OUT, @sbResponse
    EXEC sp_OAMethod @sbResponse, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @jsonTools
    EXEC @hr = sp_OADestroy @ai
    EXEC @hr = sp_OADestroy @jsonFn
    EXEC @hr = sp_OADestroy @sbFnName
    EXEC @hr = sp_OADestroy @sbResponse


END
GO