(SQL Server) Thread Pool Size

Demonstrates how to set the maximum number of threads in Chilkat's thread pool manager. Also demonstrates how to set a thread pool log file for help in diagnosing unexpected problems.

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 @iTmp0 int
    DECLARE @iTmp1 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- All Chilkat classes can be unlocked at once at the beginning of a program
    -- by calling UnlockBundle.  It requires a Bundle unlock code.
    DECLARE @chilkatGlob int
    -- Use "Chilkat_9_5_0.Global" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Global', @chilkatGlob OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @chilkatGlob, 'UnlockBundle', @success OUT, 'Anything for 30-day trial.'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @chilkatGlob, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @chilkatGlob
        RETURN
      END

    -- Set the maximum number of threads in the Chilkat thread pool to 12.
    -- This means that no more than 12 background worker threads will exist simultaneously.
    -- If more than 12 tasks are queued then some must wait for a worker thread to become free.
    -- Note: The Chilkat thread pool manager thread is a thread distinct from the
    -- worker threads.  It starts when the 1st asynchronous task is Run. 
    EXEC sp_OASetProperty @chilkatGlob, 'MaxThreads', 12

    -- Also, the ThreadPoolLogPath can be set to cause the thread pool manager thread to
    -- keep a log file. This is for the purpose of debugging if unexpected problems occur.
    EXEC sp_OASetProperty @chilkatGlob, 'ThreadPoolLogPath', '/home/users/chilkat/logs/threadPoolLog.txt'

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

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

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

    DECLARE @url1 nvarchar(4000)
    SELECT @url1 = 'http://www.marcusmiller.com/'
    DECLARE @url2 nvarchar(4000)
    SELECT @url2 = 'http://www.tromboneshorty.com/'
    DECLARE @url3 nvarchar(4000)
    SELECT @url3 = 'http://www.jamesmorrison.com/'

    -- Call the async version of the QuickGetStr method to return a task object.
    -- The task object is loaded, but is in the Inert state -- meaning it is
    -- not yet scheduled to run on Chilkat's background thread pool.
    DECLARE @task1 int
    EXEC sp_OAMethod @http1, 'QuickGetStrAsync', @task1 OUT, @url1
    EXEC sp_OAGetProperty @http1, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http1, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @chilkatGlob
        EXEC @hr = sp_OADestroy @http1
        EXEC @hr = sp_OADestroy @http2
        EXEC @hr = sp_OADestroy @http3
        RETURN
      END
    DECLARE @task2 int
    EXEC sp_OAMethod @http2, 'QuickGetStrAsync', @task2 OUT, @url2
    EXEC sp_OAGetProperty @http2, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @task1

        EXEC @hr = sp_OADestroy @chilkatGlob
        EXEC @hr = sp_OADestroy @http1
        EXEC @hr = sp_OADestroy @http2
        EXEC @hr = sp_OADestroy @http3
        RETURN
      END
    DECLARE @task3 int
    EXEC sp_OAMethod @http3, 'QuickGetStrAsync', @task3 OUT, @url3
    EXEC sp_OAGetProperty @http3, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http3, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @task1

        EXEC @hr = sp_OADestroy @task2

        EXEC @hr = sp_OADestroy @chilkatGlob
        EXEC @hr = sp_OADestroy @http1
        EXEC @hr = sp_OADestroy @http2
        EXEC @hr = sp_OADestroy @http3
        RETURN
      END

    -- At this point we have 3 task objects, each loaded with a Chilkat method call.
    -- Note: At this point no background threads are running.  The thread pool manager
    -- thread is not started -- it will start when the very first task is Run.

    -- Schedule each task for running on the thread pool.  This changes each task's state
    -- from Inert to Live. The thread pool manager thread starts with the 1st task queued.
    -- If the Global.ThreadPoolLogPath property was set, then
    -- the log file would be created (or appended) at this point.
    EXEC sp_OAMethod @task1, 'Run', @success OUT
    -- Assuming success for brevity...
    EXEC sp_OAMethod @task2, 'Run', @success OUT
    EXEC sp_OAMethod @task3, 'Run', @success OUT

    -- The application is now free to do anything else
    -- while the HTML at the URL's are being downloaded in background threads.

    -- In this case, we'll just wait for all three tasks to finish.
    -- All three tasks are running simultaneously in separate background threads.
    -- We can wait for each in any order.  If Wait is called and the task has already
    -- finished (or been canceled), then the Wait method returns immediately.
    DECLARE @maxWaitMs int
    SELECT @maxWaitMs = 20000
    EXEC sp_OAMethod @task1, 'Wait', @success OUT, @maxWaitMs
    EXEC sp_OAMethod @task2, 'Wait', @success OUT, @maxWaitMs
    EXEC sp_OAMethod @task3, 'Wait', @success OUT, @maxWaitMs
    -- Assuming success for brevity...

    DECLARE @err nvarchar(4000)
    SELECT @err = 'Task failed or canceled'
    DECLARE @html1 nvarchar(4000)
    SELECT @html1 = @err
    DECLARE @html2 nvarchar(4000)
    SELECT @html2 = @err
    DECLARE @html3 nvarchar(4000)
    SELECT @html3 = @err

    -- Now get the HTML downloaded in each task:
    EXEC sp_OAGetProperty @task1, 'StatusInt', @iTmp0 OUT
    EXEC sp_OAGetProperty @task1, 'TaskSuccess', @iTmp1 OUT
    IF (@iTmp0 = 7) and (@iTmp1 = 1)
      BEGIN
        EXEC sp_OAMethod @task1, 'GetResultString', @html1 OUT
      END
    EXEC sp_OAGetProperty @task2, 'StatusInt', @iTmp0 OUT
    EXEC sp_OAGetProperty @task2, 'TaskSuccess', @iTmp1 OUT
    IF (@iTmp0 = 7) and (@iTmp1 = 1)
      BEGIN
        EXEC sp_OAMethod @task2, 'GetResultString', @html2 OUT
      END
    EXEC sp_OAGetProperty @task3, 'StatusInt', @iTmp0 OUT
    EXEC sp_OAGetProperty @task3, 'TaskSuccess', @iTmp1 OUT
    IF (@iTmp0 = 7) and (@iTmp1 = 1)
      BEGIN
        EXEC sp_OAMethod @task3, 'GetResultString', @html3 OUT
      END


    PRINT @html1

    PRINT '----'

    PRINT @html2

    PRINT '----'

    PRINT @html3

    PRINT '----'

    EXEC @hr = sp_OADestroy @task1

    EXEC @hr = sp_OADestroy @task2

    EXEC @hr = sp_OADestroy @task3


    EXEC @hr = sp_OADestroy @chilkatGlob
    EXEC @hr = sp_OADestroy @http1
    EXEC @hr = sp_OADestroy @http2
    EXEC @hr = sp_OADestroy @http3


END
GO