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

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