SQL Server

Call a JavaScript Function Returning an Array

See more JavaScript Examples

Demonstrates how to call a JavaScript function that returns an array. 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 @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- ----------------------------------------------------------------------------------
    -- The Javascript functions called in this example are shown at the bottom of this page.
    -- -----------------------------------------------------------------------------------

    DECLARE @sbScript int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbScript OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @sbScript, 'LoadFile', @success OUT, 'js_function_returning_array.js', 'utf-8'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sbScript, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbScript
        RETURN
      END

    DECLARE @js int
    EXEC @hr = sp_OACreate 'Chilkat.Js', @js OUT

    DECLARE @result int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @result OUT

    EXEC sp_OASetProperty @result, 'EmitCompact', 0

    -- Call Eval to add the function to the context's global object
    EXEC sp_OAMethod @js, 'Eval', @success OUT, @sbScript, @result
    IF @success = 0
      BEGIN
        -- Examine the result for an exception.
        EXEC sp_OAMethod @result, 'Emit', @sTmp0 OUT
        PRINT @sTmp0

        -- Also examine the LastErrorText.
        EXEC sp_OAGetProperty @js, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbScript
        EXEC @hr = sp_OADestroy @js
        EXEC @hr = sp_OADestroy @result
        RETURN
      END

    -- ------------------------------------------------------------------------------
    -- Call each function

    DECLARE @funcCall int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @funcCall OUT

    -- Create JSON specifying the function name and arguments
    -- The function has no arguments, so we only specify the name.

    EXEC sp_OAMethod @funcCall, 'UpdateString', @success OUT, 'name', 'getDays'

    EXEC sp_OAMethod @js, 'CallFunction', @success OUT, @funcCall, @result
    IF @success = 0
      BEGIN
        -- Examine the result for an exception.
        EXEC sp_OAMethod @result, 'Emit', @sTmp0 OUT
        PRINT @sTmp0

        -- Also examine the LastErrorText.
        EXEC sp_OAGetProperty @js, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sbScript
        EXEC @hr = sp_OADestroy @js
        EXEC @hr = sp_OADestroy @result
        EXEC @hr = sp_OADestroy @funcCall
        RETURN
      END

    EXEC sp_OAMethod @result, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Output:
    -- {
    --   "type": "array",
    --   "value": [
    --     "Monday",
    --     "Tuesday",
    --     "Wednesday",
    --     "Thursday",
    --     "Friday"
    --   ]
    -- }

    -- Access each array value..
    DECLARE @count int
    EXEC sp_OAMethod @result, 'SizeOfArray', @count OUT, 'value'
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @count
      BEGIN
        EXEC sp_OASetProperty @result, 'I', @i
        EXEC sp_OAMethod @result, 'StringOf', @sTmp0 OUT, 'value[i]'
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

    -- ------------------------------------------------------------------------------
    -- Call the getRange(start,end) function

    EXEC sp_OAMethod @funcCall, 'Clear', NULL
    EXEC sp_OAMethod @funcCall, 'UpdateString', @success OUT, 'name', 'getRange'
    EXEC sp_OAMethod @funcCall, 'UpdateInt', @success OUT, 'args[0]', 14
    EXEC sp_OAMethod @funcCall, 'UpdateInt', @success OUT, 'args[1]', 21
    EXEC sp_OAMethod @js, 'CallFunction', @success OUT, @funcCall, @result
    EXEC sp_OAMethod @result, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Output:
    -- {
    --   "type": "array",
    --   "value": [
    --     14,
    --     15,
    --     16,
    --     17,
    --     18,
    --     19,
    --     20,
    --     21
    --   ]
    -- }

    EXEC sp_OAMethod @result, 'SizeOfArray', @count OUT, 'value'
    SELECT @i = 0
    WHILE @i < @count
      BEGIN
        EXEC sp_OASetProperty @result, 'I', @i
        EXEC sp_OAMethod @result, 'IntOf', @iTmp0 OUT, 'value[i]'
        PRINT @iTmp0
        SELECT @i = @i + 1
      END

    -- ------------------------------------------------------------------------------
    -- Call the getEmployees() function

    EXEC sp_OAMethod @funcCall, 'Clear', NULL
    EXEC sp_OAMethod @funcCall, 'UpdateString', @success OUT, 'name', 'getEmployees'
    EXEC sp_OAMethod @js, 'CallFunction', @success OUT, @funcCall, @result
    EXEC sp_OAMethod @result, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Output:
    -- {
    --   "type": "array",
    --   "value": [
    --     {
    --       "id": 101,
    --       "name": "Alice",
    --       "role": "Dev"
    --     },
    --     {
    --       "id": 102,
    --       "name": "Bob",
    --       "role": "Manager"
    --     }
    --   ]
    -- }

    EXEC sp_OAMethod @result, 'SizeOfArray', @count OUT, 'value'
    SELECT @i = 0
    WHILE @i < @count
      BEGIN
        EXEC sp_OASetProperty @result, 'I', @i

        EXEC sp_OAMethod @result, 'StringOf', @sTmp0 OUT, 'value[i].name'
        PRINT 'name: ' + @sTmp0

        EXEC sp_OAMethod @result, 'StringOf', @sTmp0 OUT, 'value[i].role'
        PRINT 'role: ' + @sTmp0

        EXEC sp_OAMethod @result, 'IntOf', @iTmp0 OUT, 'value[i].id'
        PRINT 'id: ' + @iTmp0

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

    EXEC @hr = sp_OADestroy @sbScript
    EXEC @hr = sp_OADestroy @js
    EXEC @hr = sp_OADestroy @result
    EXEC @hr = sp_OADestroy @funcCall


END
GO