(SQL Server) Call a JavaScript Function Passing an Array Argument

See more JavaScript Examples

Note: This example requires Chilkat v11.4.0 or greater.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This is the JavaScript function we'll call:

    -- function calculateTotal(numbers) {
    --   console.log(numbers);
    --   let total = 0;
    --   
    --   // Loop through every number in the array
    --   for (const num of numbers) {
    --     console.log(num);
    --     total += num;
    --   }
    --   
    --   return total;
    -- }

    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_array_arg.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 the function calculateTotal(numbers)

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

    EXEC sp_OASetProperty @funcCall, 'EmitCompact', 0

    -- Create JSON specifying the function name and arguments
    -- In this case, there is only 1 argument, and it is an array.

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

    -- Create the arguments array.
    DECLARE @argsArray int
    EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @argsArray OUT

    -- The 1st argument in the arguments array is itself an array.
    -- Passing -1 indicates to append to the array.
    DECLARE @arg int
    EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @arg OUT

    EXEC sp_OAMethod @argsArray, 'AddArrayAt2', @success OUT, -1, @arg

    -- Fill in the values for the 1st argument.
    EXEC sp_OAMethod @arg, 'AddNumberAt', @success OUT, -1, '10.50'
    EXEC sp_OAMethod @arg, 'AddNumberAt', @success OUT, -1, '20.00'
    EXEC sp_OAMethod @arg, 'AddNumberAt', @success OUT, -1, '5.25'

    -- Add the "args" array to the funcCall.
    EXEC sp_OAMethod @funcCall, 'AppendArrayCopy', @success OUT, 'args', @argsArray

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

    -- The funcCall is as follows.  Notice that the 1st (and only) argument is an array.

    -- {
    --   "name": "calculateTotal",
    --   "args": [
    --     [
    --       10.50,
    --       20.00,
    --       5.25
    --     ]
    --   ]
    -- }

    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
        EXEC @hr = sp_OADestroy @argsArray
        EXEC @hr = sp_OADestroy @arg
        RETURN
      END

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

    -- Result:
    -- {
    --   "type": "double",
    --   "value": 35.75
    -- }

    -- The function also emitted text to the console.

    DECLARE @sbOut int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbOut OUT

    EXEC sp_OAMethod @js, 'ConsoleOutputSb', NULL, @sbOut
    EXEC sp_OAMethod @sbOut, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Output:
    -- 10.5,20,5.25
    -- 10.5
    -- 20
    -- 5.25

    -- -----------------------------------------------------------
    -- Note: If the array argument is simple, this is an alternative
    -- and simpler way of creating the funcCall:

    EXEC sp_OAMethod @funcCall, 'Clear', NULL
    EXEC sp_OAMethod @funcCall, 'UpdateString', @success OUT, 'name', 'calculateTotal'
    EXEC sp_OAMethod @funcCall, 'UpdateNumber', @success OUT, 'args[0][0]', '10.50'
    EXEC sp_OAMethod @funcCall, 'UpdateNumber', @success OUT, 'args[0][1]', '20.00'
    EXEC sp_OAMethod @funcCall, 'UpdateNumber', @success OUT, 'args[0][2]', '5.25'
    EXEC sp_OAMethod @funcCall, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @sbScript
    EXEC @hr = sp_OADestroy @js
    EXEC @hr = sp_OADestroy @result
    EXEC @hr = sp_OADestroy @funcCall
    EXEC @hr = sp_OADestroy @argsArray
    EXEC @hr = sp_OADestroy @arg
    EXEC @hr = sp_OADestroy @sbOut


END
GO

function calculateTotal(numbers) {
  console.log(numbers);
  let total = 0;
  
  // Loop through every number in the array
  for (const num of numbers) {
    console.log(num);
    total += num;
  }
  
  return total;
}