SQL Server

Prove Stateful Multi-Turn Conversation

See more AI Examples

This example is to prove that the 2nd request in a conversation is part of a stateful multi-turn conversation. The 2nd request asks for information provided in the 1st request. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr 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'

    -- 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 helpful assistant'
    DECLARE @developerMsg nvarchar(4000)
    SELECT @developerMsg = ''
    DECLARE @conversationName nvarchar(4000)
    SELECT @conversationName = 'test_conversation'
    EXEC sp_OAMethod @ai, 'NewConvo', @success OUT, @conversationName, @systemMsg, @developerMsg

    -- -------------------------------------------------------------------------------------------
    -- Our first request will establish a secret that we'll ask for in the 2nd request.
    -- Add a text input.
    EXEC sp_OAMethod @ai, 'InputAddText', @success OUT, 'Please remember this: The word I''ll ask for later is BlubberFish.  What''s the word?'

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

    -- Note: The inputs accumulated through one or more calls to Input* methods are cleared when the Ask method returns with success.

    -- Get the text 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

    PRINT '----'

    -- Sample output:

    -- The word is **BlubberFish**.

    -- -------------------------------------------------------------------------------------------
    -- Let's ask for the word. If this is a stateful conversation, the AI should respond correctly.
    EXEC sp_OAMethod @ai, 'InputAddText', @success OUT, 'OK, I''m going to ask you again, what''s the word?'
    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 @sbResponse
        RETURN
      END

    -- Get the text response.
    EXEC sp_OAMethod @sbResponse, 'Clear', NULL
    EXEC sp_OAMethod @ai, 'GetOutputTextSb', @success OUT, @sbResponse
    EXEC sp_OAMethod @sbResponse, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample output:

    -- The word is **BlubberFish**.

    -- -------------------------------------------------------------------------------------------
    -- Look at the conversation so far...
    -- 
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @ai, 'ExportConvo', @success OUT, @conversationName, @json
    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Note: The conversation transcript format exported and imported by Chilkat is always the OpenAI format,
    -- regardless of the AI provider.  (Chilkat translates the format from OpenAI to whatever is needed by the actual AI provider being used.)

    -- {
    --   "input": [
    --     {
    --       "role": "system",
    --       "content": [
    --         {
    --           "type": "input_text",
    --           "text": "You are a helpful assistant"
    --         }
    --       ]
    --     },
    --     {
    --       "role": "user",
    --       "content": [
    --         {
    --           "type": "input_text",
    --           "text": "Please remember this: The word I'll ask for later is BlubberFish.  What's the word?"
    --         }
    --       ]
    --     },
    --     {
    --       "role": "assistant",
    --       "content": [
    --         {
    --           "type": "output_text",
    --           "text": "The word is **BlubberFish**."
    --         }
    --       ]
    --     },
    --     {
    --       "role": "user",
    --       "content": [
    --         {
    --           "type": "input_text",
    --           "text": "OK, I'm going to ask you again, what's the word?"
    --         }
    --       ]
    --     },
    --     {
    --       "role": "assistant",
    --       "content": [
    --         {
    --           "type": "output_text",
    --           "text": "The word is **BlubberFish**."
    --         }
    --       ]
    --     }
    --   ]
    -- }

    -- -------------------------------------------------------------------------------------------
    -- If we unselect the conversation, so that no conversation is selected,
    -- then the same query results in the AI not knowing the word.

    EXEC sp_OASetProperty @ai, 'SelectedConvo', ''

    EXEC sp_OAMethod @ai, 'InputAddText', @success OUT, 'OK, I''m going to ask you again, what''s the word?'
    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 @sbResponse
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- Get the text response.
    EXEC sp_OAMethod @sbResponse, 'Clear', NULL
    EXEC sp_OAMethod @ai, 'GetOutputTextSb', @success OUT, @sbResponse
    EXEC sp_OAMethod @sbResponse, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample output:

    -- I apologize, but I don't have the context of our previous interaction or what "the word" you're referring to might be.
    -- Could you please remind me or provide the clue again? I'm ready to help you figure it out!

    EXEC @hr = sp_OADestroy @ai
    EXEC @hr = sp_OADestroy @sbResponse
    EXEC @hr = sp_OADestroy @json


END
GO