SQL Server
SQL Server
Conversation with Streaming Responses
See more AI Examples
Demonstrates an AI conversation with receiving streaming responses.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
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @ai int
EXEC @hr = sp_OACreate 'Chilkat.Ai', @ai OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- The provider can be "openai", "google", "claude", "deepseek", "xai", or "perplexity".
-- Support for additional providers will be added in future versions of Chilkat.
EXEC sp_OASetProperty @ai, 'Provider', 'google'
-- Use your provider's API key.
EXEC sp_OASetProperty @ai, 'ApiKey', 'MY_API_KEY'
-- Choose a model.
EXEC sp_OASetProperty @ai, 'Model', 'gemini-2.5-flash'
-- Indicate streaming mode is to be used.
EXEC sp_OASetProperty @ai, 'Streaming', 1
-- Create a new conversation to be maintained locally in memory.
-- If the conversation is the first to be created, it is also automatically selected.
DECLARE @systemMsg nvarchar(4000)
SELECT @systemMsg = 'You are a creative storyteller'
DECLARE @developerMsg nvarchar(4000)
SELECT @developerMsg = ''
DECLARE @conversationName nvarchar(4000)
SELECT @conversationName = 'test_conversation'
EXEC sp_OAMethod @ai, 'NewConvo', @success OUT, @conversationName, @systemMsg, @developerMsg
-- Add a text input.
EXEC sp_OAMethod @ai, 'InputAddText', @success OUT, 'Write a detailed story about a turtle who decides to run a bakery. Describe the setting, the kinds of pastries, how the turtle feels, and include at least three paragraphs.'
-- Ask the AI for text output.
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 @ai
RETURN
END
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
DECLARE @finished int
SELECT @finished = 0
DECLARE @abortFlag int
SELECT @abortFlag = 0
DECLARE @maxWaitMs int
SELECT @maxWaitMs = 5000
WHILE Not @finished
BEGIN
DECLARE @result int
EXEC sp_OAMethod @ai, 'PollAi', @result OUT, @abortFlag
IF @result = 1
BEGIN
-- We have output waiting. It should be instantly available. The maxWaitMs is just-in-case.
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 @ai
EXEC @hr = sp_OADestroy @sbEventName
EXEC @hr = sp_OADestroy @sbDelta
EXEC @hr = sp_OADestroy @sbFullResponse
RETURN
END
-- Some AI providers send many "empty" events. Just ignore them.
EXEC sp_OAMethod @sbEventName, 'ContentsEqual', @iTmp0 OUT, 'empty', 1
IF Not @iTmp0
BEGIN
-- The delta contains the new output. This could be emitted to a display or the terminal
-- as real-time output.
EXEC sp_OAMethod @sbEventName, 'ContentsEqual', @iTmp0 OUT, 'delta', 1
IF @iTmp0
BEGIN
-- This example will emit each delta to its own line.
EXEC sp_OAMethod @sbDelta, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
-- Accumulate the delta's so we can show the full response later.
EXEC sp_OAMethod @sbFullResponse, 'AppendSb', @success OUT, @sbDelta
END
ELSE
BEGIN
-- A streaming AI response is always terminated by a single "null_terminator" event.
EXEC sp_OAMethod @sbEventName, 'ContentsEqual', @finished OUT, 'null_terminator', 1
END
END
END
ELSE
BEGIN
IF @result = 0
BEGIN
-- Nothing is immediately available. Sleep for 1/10 of a second before polling again.
EXEC sp_OAMethod @ai, 'SleepMs', NULL, 100
END
ELSE
BEGIN
-- Something failed..
EXEC sp_OAGetProperty @ai, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
SELECT @finished = 1
END
END
END
-- -------------------------------------------------------------
-- The response is in markdown format.
-- Also see Markdown to HTML Conversion Examples.
-- -------------------------------------------------------------
-- Show the accumulated (full) response.
PRINT '----'
EXEC sp_OAMethod @sbFullResponse, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
PRINT '----'
-- ----------------------------------------------------------------------------------------------------------
-- For the 2nd request in this conversation, ask for a shorter version of the story.
EXEC sp_OAMethod @ai, 'InputAddText', @success OUT, 'Rewrite the story, but this time make it shorter, about one third as long.'
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 @ai
EXEC @hr = sp_OADestroy @sbEventName
EXEC @hr = sp_OADestroy @sbDelta
EXEC @hr = sp_OADestroy @sbFullResponse
RETURN
END
EXEC sp_OAMethod @sbFullResponse, 'Clear', NULL
SELECT @finished = 0
WHILE Not @finished
BEGIN
DECLARE @result int
EXEC sp_OAMethod @ai, 'PollAi', @result OUT, @abortFlag
IF @result = 1
BEGIN
-- We have output waiting. It should be instantly available. The maxWaitMs is just-in-case.
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 @ai
EXEC @hr = sp_OADestroy @sbEventName
EXEC @hr = sp_OADestroy @sbDelta
EXEC @hr = sp_OADestroy @sbFullResponse
RETURN
END
-- Some AI providers send many "empty" events. Just ignore them.
EXEC sp_OAMethod @sbEventName, 'ContentsEqual', @iTmp0 OUT, 'empty', 1
IF Not @iTmp0
BEGIN
-- The delta contains the new output. This could be emitted to a display or the terminal
-- as real-time output.
EXEC sp_OAMethod @sbEventName, 'ContentsEqual', @iTmp0 OUT, 'delta', 1
IF @iTmp0
BEGIN
-- This example will emit each delta to its own line.
EXEC sp_OAMethod @sbDelta, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
-- Accumulate the delta's so we can show the full response later.
EXEC sp_OAMethod @sbFullResponse, 'AppendSb', @success OUT, @sbDelta
END
ELSE
BEGIN
-- A streaming AI response is always terminated by a single "null_terminator" event.
EXEC sp_OAMethod @sbEventName, 'ContentsEqual', @finished OUT, 'null_terminator', 1
END
END
END
ELSE
BEGIN
IF @result = 0
BEGIN
-- Nothing is immediately available. Sleep for 1/10 of a second before polling again.
EXEC sp_OAMethod @ai, 'SleepMs', NULL, 100
END
ELSE
BEGIN
-- Something failed..
EXEC sp_OAGetProperty @ai, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
SELECT @finished = 1
END
END
END
PRINT '----'
EXEC sp_OAMethod @sbFullResponse, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ai
EXEC @hr = sp_OADestroy @sbEventName
EXEC @hr = sp_OADestroy @sbDelta
EXEC @hr = sp_OADestroy @sbFullResponse
END
GO