(SQL Server) Using Chilkat StringBuilder to Avoid Large Strings

Some programming languages can have limitations on string lengths, or it can be inefficient to return large strings back to the application, only to be passed back into Chilkat in a subsequent method call.

For Chilkat functions that return a string where the returned string can potentially be very large, there is typically the same function with the name ending in "Sb", where the returned string is deposited into the last argument. This allows for the string remain within an object, and thus never marshalled to/from the application.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    -- This is the potentially inefficient way of getting JSON from a URL and loading it into a Chilkat JsonObject.
    -- It gets the string directly..
    DECLARE @jsonStr nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @jsonStr OUT, 'https://www.chilkatsoft.com/helloWorld.json'
    DECLARE @success int
    EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonStr

    -- This is the more efficient method if the JSON is potentially large
    DECLARE @sb int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT

    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://www.chilkatsoft.com/helloWorld.json', @sb
    EXEC sp_OAMethod @json, 'LoadSb', @success OUT, @sb

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sb


END
GO