(SQL Server) Keeping Query Params in a Hashtable

Demonstrates how to use a Chilkat Hashtable to hold query params and re-emit as a query param string.

Note: The ToQueryString method was added in Chilkat v9.5.0.92.

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 @ht int
    -- Use "Chilkat_9_5_0.Hashtable" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Hashtable', @ht OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Add name/value pairs from an existing query param string where the values are already URL encoded.
    DECLARE @qp nvarchar(4000)
    SELECT @qp = 'txt=this%20is%20a%20test&color=blue&fruits=apples%26oranges'
    EXEC sp_OAMethod @ht, 'AddQueryParams', @success OUT, @qp

    -- Add some additional name/value pairs.
    EXEC sp_OAMethod @ht, 'AddStr', @success OUT, 'name', 'Jack Sparrow'
    EXEC sp_OAMethod @ht, 'AddStr', @success OUT, 'movie', 'Pirates of the Carribean'

    -- Iterate over the hash table to show what we have..

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

    EXEC sp_OAMethod @ht, 'GetKeys', @success OUT, @keys
    DECLARE @numKeys int
    EXEC sp_OAGetProperty @keys, 'Count', @numKeys OUT
    DECLARE @key nvarchar(4000)

    DECLARE @val nvarchar(4000)

    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numKeys
      BEGIN
        EXEC sp_OAMethod @keys, 'StringAt', @key OUT, @i
        EXEC sp_OAMethod @ht, 'LookupStr', @val OUT, @key


        PRINT @key + ' = ' + @val
        SELECT @i = @i + 1
      END

    -- Output for the above loop:

    -- color = blue
    -- name = Jack Sparrow
    -- fruits = apples&oranges
    -- txt = this is a test
    -- movie = Pirates of the Carribean

    -- Get all name/value pairs as a query parm string with values URL encoded
    EXEC sp_OAMethod @ht, 'ToQueryString', @qp OUT

    PRINT @qp

    -- Result:
    -- color=blue&name=Jack%20Sparrow&fruits=apples%26oranges&txt=this%20is%20a%20test&movie=Pirates%20of%20the%20Carribean

    EXEC @hr = sp_OADestroy @ht
    EXEC @hr = sp_OADestroy @keys


END
GO