(SQL Server) Call a JavaScript Function Passing an Object 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 describeCar(car) {
    -- 	console.log(`This is a ${car.year} ${car.make} ${car.model}.`);
    -- }

    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, 'Append', @success OUT, 'function describeCar(car) { console.log(`This is a ${car.year} ${car.make} ${car.model}.`); }'

    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 describeCar(car)

    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 object.

    -- {
    --   "name": "describeCar",
    --   "args": [
    --     {
    --       "make": "Toyota",
    --       "model": "Corolla",
    --       "year": 2022
    --     }
    --   ]
    -- }

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

    -- Create the JSON object that is the argument.
    DECLARE @arg int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @arg OUT

    EXEC sp_OAMethod @arg, 'UpdateString', @success OUT, 'make', 'Toyota'
    EXEC sp_OAMethod @arg, 'UpdateString', @success OUT, 'model', 'Corolla'
    EXEC sp_OAMethod @arg, 'UpdateInt', @success OUT, 'year', 2022

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

    EXEC sp_OAMethod @argsArray, 'AddObjectCopyAt', @success OUT, 0, @arg

    -- 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

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

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

    -- The describeCar JavaScript function returns nothing. 
    -- Therefore, the result is "undefined".

    -- {
    --   "type": "undefined",
    --   "value": "undefined"
    -- }

    -- However, the function 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:
    -- This is a 2022 Toyota Corolla.

    -- -----------------------------------------------------------
    -- Note: If the object 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', 'describeCar'
    EXEC sp_OAMethod @funcCall, 'UpdateString', @success OUT, 'args[0].make', 'Toyota'
    EXEC sp_OAMethod @funcCall, 'UpdateString', @success OUT, 'args[0].model', 'Corolla'
    EXEC sp_OAMethod @funcCall, 'UpdateInt', @success OUT, 'args[0].year', 2022
    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 @arg
    EXEC @hr = sp_OADestroy @argsArray
    EXEC @hr = sp_OADestroy @sbOut


END
GO