SQL Server

AI: Simple Conversation

See more AI Examples

Initiates a conversation and sends two requests. The second request includes the transcript of the messages between the user and assistant from the first 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', 'claude'

    -- Use your provider's API key.
    EXEC sp_OASetProperty @ai, 'ApiKey', 'MY_API_KEY'

    -- Choose a model.
    EXEC sp_OASetProperty @ai, 'Model', 'claude-opus-4-1-20250805'

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

    -- -------------------------------------------------------------------------------------------
    -- Make the first request...
    -- Add a text input.
    EXEC sp_OAMethod @ai, 'InputAddText', @success OUT, 'Say Hello'

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

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

    -- -------------------------------------------------------------
    -- The response is in markdown format.
    -- Also see Markdown to HTML Conversion Examples.
    -- -------------------------------------------------------------

    -- Sample output:

    -- # Hello! 👋
    -- ## Welcome!
    -- It's **great** to meet you. How can I *assist* you today?
    -- ---
    -- Feel free to ask me anything, and I'll respond in `markdown` format.

    -- -------------------------------------------------------------------------------------------
    -- Send the 2nd request in the conversation.
    EXEC sp_OAMethod @ai, 'InputAddText', @success OUT, 'Thanks for saying Hello. Please give me a 2 sentence bit of wisdom from Confucious.'
    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:

    -- ## Wisdom from Confucius
    -- > *"The man who moves a mountain begins by carrying away small stones. It does not matter how slowly you go as long as you do not stop."*
    -- These words remind us that **persistence** and **patience** are the keys to achieving even the most daunting goals. 🏔️

    -- -------------------------------------------------------------------------------------------
    -- 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": "developer",
    --       "content": [
    --         {
    --           "type": "input_text",
    --           "text": "Respond only with markdown"
    --         }
    --       ]
    --     },
    --     {
    --       "role": "user",
    --       "content": [
    --         {
    --           "type": "input_text",
    --           "text": "Say Hello"
    --         }
    --       ]
    --     },
    --     {
    --       "role": "assistant",
    --       "content": [
    --         {
    --           "type": "output_text",
    --           "text": "# Hello! 👋\n\n**Welcome!** It's *great* to meet you.\n\nHow can I assist you today?"
    --         }
    --       ]
    --     },
    --     {
    --       "role": "user",
    --       "content": [
    --         {
    --           "type": "input_text",
    --           "text": "Thanks for saying Hello. Please give me a 2 sentence bit of wisdom from Confucious."
    --         }
    --       ]
    --     },
    --     {
    --       "role": "assistant",
    --       "content": [
    --         {
    --           "type": "output_text",
    --           "text": "## Wisdom from Confucius\n\n> *\"The man who moves a mountain begins by carrying away small stones. It does not matter how slowly you go as long as you do not stop.\"*\n\nThese words remind us that **persistence** and **patience** are the keys to achieving even the most daunting goals. 🏔️"
    --         }
    --       ]
    --     }
    --   ]
    -- }

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


END
GO