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

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    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

JavaScript

function getDays() {
    return ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];
}

function getRange(start, end) {
    var arr = [];
    for (var i = start; i <= end; i++) {
        arr.push(i);
    }
    return arr;
}
// Usage: getRange(1, 5) -> [1, 2, 3, 4, 5]

function getEmployees() {
    return [
        { id: 101, name: "Alice", role: "Dev" },
        { id: 102, name: "Bob", role: "Manager" }
    ];
}