SQL Server
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.Chilkat SQL Server Downloads
-- 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