(SQL Server) Streaming AI with Manual AI Tool Function Calling

See more AI Examples

Demonstrates how to get AI responses in streaming mode, including manual tool function calls.

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 a single function 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"
    --           }
    --         }
    --       }
    --     }
    --   ]
    -- }

    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'
    -- 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 markdown.'
    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.'

    -- Get the response in streaming mode.
    EXEC sp_OASetProperty @ai, 'Streaming', 1

    -- In streaming mode, if we receive an AI event that is a request for tool use,
    -- we'll need to make the call to the JavaScript and then continue with a followup Ask,
    -- until the final response is received.

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

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

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

    -- When PollAi returns with an event, it's highly unlikely the
    -- call to NextAiEvent does not immediately return.  Setting a max
    -- timeout is just a precaution..
    DECLARE @maxWaitMs int
    SELECT @maxWaitMs = 5000

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

    DECLARE @finished int
    SELECT @finished = 0
    DECLARE @numAsks int
    SELECT @numAsks = 0
    -- Set a max # of followup Asks to prevent any unexpected infinite looping.
    WHILE Not @finished and (@numAsks < 10)
      BEGIN

        -- Send the request to the AI model.
        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 @sbEventName
            EXEC @hr = sp_OADestroy @sbDelta
            EXEC @hr = sp_OADestroy @sbFullResponse
            EXEC @hr = sp_OADestroy @jsonFn
            RETURN
          END

        DECLARE @madeFunctionCalls int
        SELECT @madeFunctionCalls = 0
        DECLARE @streamingDone int
        SELECT @streamingDone = 0

        WHILE Not @streamingDone
          BEGIN
            DECLARE @result int
            EXEC sp_OAMethod @ai, 'PollAi', @result OUT, 0
            IF @result < 0
              BEGIN
                EXEC sp_OAGetProperty @ai, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0

                PRINT 'Failed.'
                EXEC @hr = sp_OADestroy @jsonTools
                EXEC @hr = sp_OADestroy @ai
                EXEC @hr = sp_OADestroy @sbEventName
                EXEC @hr = sp_OADestroy @sbDelta
                EXEC @hr = sp_OADestroy @sbFullResponse
                EXEC @hr = sp_OADestroy @jsonFn
                RETURN
              END
            IF @result > 0
              BEGIN
                -- We have an event..
                EXEC sp_OAMethod @ai, 'NextAiEvent', @success OUT, @maxWaitMs, @sbEventName, @sbDelta
                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 @sbEventName
                    EXEC @hr = sp_OADestroy @sbDelta
                    EXEC @hr = sp_OADestroy @sbFullResponse
                    EXEC @hr = sp_OADestroy @jsonFn
                    RETURN
                  END

                -- Is this an event where the AI is requesting a function call?
                EXEC sp_OAMethod @sbEventName, 'ContentsEqual', @iTmp0 OUT, 'function_call', 1
                IF @iTmp0
                  BEGIN
                    EXEC sp_OAMethod @jsonFn, 'LoadSb', @success OUT, @sbDelta

                    -- 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

                            SELECT @madeFunctionCalls = 1
                          END
                        -- Your application would add code to check for and handle each possible function call.

                        SELECT @fn_idx = @fn_idx + 1
                      END

                  END
                ELSE
                  BEGIN
                    EXEC sp_OAMethod @sbEventName, 'ContentsEqual', @iTmp0 OUT, 'empty', 1
                    IF Not @iTmp0
                      BEGIN
                        EXEC sp_OAMethod @sbFullResponse, 'AppendSb', @success OUT, @sbDelta

                        EXEC sp_OAMethod @sbEventName, 'ContentsEqual', @iTmp0 OUT, 'null_terminator', 1
                        IF @iTmp0
                          BEGIN
                            SELECT @streamingDone = 1
                          END
                      END
                  END

              END
            ELSE
              BEGIN
                -- No event arrived, so wait a short time rather than spin in a loop..
                EXEC sp_OAMethod @ai, 'SleepMs', NULL, 100
              END

          END

        IF Not @madeFunctionCalls
          BEGIN
            SELECT @finished = 1
          END

        SELECT @numAsks = @numAsks + 1
      END


    PRINT 'Full Response:'
    EXEC sp_OAMethod @sbFullResponse, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @jsonTools
    EXEC @hr = sp_OADestroy @ai
    EXEC @hr = sp_OADestroy @sbEventName
    EXEC @hr = sp_OADestroy @sbDelta
    EXEC @hr = sp_OADestroy @sbFullResponse
    EXEC @hr = sp_OADestroy @jsonFn
    EXEC @hr = sp_OADestroy @sbFnName


END
GO