Calling JavaScript functions from TSQL
Why would you ever want to call a JavaScript function from TSQL? It does sound crazy, but there may be a case where you might want to do this. Even if it not for real production use, it might be useful for some test scenarios. And it is very cool and you get to brag about it.
Here is a case I ran into. We have a web app where we do lot of currency conversions. These calculations are done in JavaScript. On the back end, in the SQL Server, we needed to do similar calculations in TSQL. However depending on the datatypes, precision and scale and rounding algorithms used, we ran into a case where the JavaScript calculations in the browser sometimes differed by a penny from the calculations done in the back end. We fixed these, but we needed to test that the JavaScript calculations matched the calculations made in the data base.
The first thing I tried is to see if I can host a JavaScript engine in C#. The idea is that if I can call a JS function from C#, I can then use that output to compare with my data base by using EF or ADO.NET or LINQ. I can then turn this in a test case I can run regularly. The goal is to eliminate UI automation.
I looked at various posts and git hub projects, and eventually landed on this git hub Edge/Chakra JavaScript hosting sample. Note that there may be other JavaScript hosting samples, but the one I mentioned is the latest. It uses the Chakra.dll (Edge) instead of JScript9.dll (IE 11). I also looked into the MSDN docs for JSRT (JavaScript runtime) for more information, but our friends in JSRT did not get to spend too much time with the API documentation, so it was not that useful. At the end, I got what I needed from the sample and reading git bug issue list on the sample.
Here I will show you the bare bones code to host JSRT in C#. [ I am assuming that you downloaded the sample from git hub link above. We will keep most that sample except the main program.cs. Remove that and lets start clean]
JavaScriptRuntime jsRuntime = JavaScriptRuntime.Create();
JavaScriptContext jsContext = jsRuntime.CreateContext();
Once you create a JavaScriptRuntime and JavaScriptContext, you are pretty much ready to run code.
The one small detail is that you need to know is that you need to set a current context on the thread of execution before you do anything in that context.
using (new JavaScriptContext.Scope(jsContext))
{
....
}
In this case, we want to run some JS script. So lets get the source defined in a string for convinience
private static string jsSource = @"
var some_var = Math.pow(10,6);
function convert(amount, sourceToUSDRate, targetToUSDRate)
{
... your JS Code here ...
}";
So your using statement above will now look like this.
using (new JavaScriptContext.Scope(jsContext))
{
// run the script so functions get defined.
JavaScriptContext.RunScript(jsSource);
}
Now that the script ran, your function is defined, and now you can get hold of the function in C#.
Apparently there is a different way to do this, that is by calling ParseScript(). I could not get that to work, So I will skip that path.
JS has the concept of Global Object. A function defined in your code (outside of any object) is a function of the global object.
So first we need to get hold of the global object, ask for the "id" of the function using the name of the function and then get the function. All of this brings back the memories of IDispatch.
static JSEngine()
{
jsRuntime = JavaScriptRuntime.Create();
jsContext = jsRuntime.CreateContext();
using (new JavaScriptContext.Scope(jsContext))
{
// run the script so functions get defined.
JavaScriptContext.RunScript(jsSource);
// Get the convert function handle.
Native.JsGetGlobalObject(out jsGlobalObj);
JavaScriptPropertyId id;
if (Native.JsGetPropertyIdFromName("convert", out id) != JavaScriptErrorCode.NoError)
{
throw new Exception("Convert function not found");
}
Native.JsGetProperty(jsGlobalObj, id, out jsConvertFunc);
}
}
The type of the jsConvertFunc is JavaScriptValue
A couple of points here. Most of what you deal with in JSRT are the JavaScriptValue types.
A JavaScriptValue cane a function, an object, a number, a string or an array. So what we did so far is to get hold of a value that represents a function. Once you have that all that remains is to invoke that function. Here is the code for that.
There is a Gotcha here. When you call a function, the first parameter is the "this" pointer to the object on which the function is called on. In our case there is no specific object, it is the global object. That's why we pass the global object we obtained earlier as the first parameter.
Here is the code we have so far.
namespace EdgeJSHost
{
public class JSEngine
{
private static JavaScriptRuntime jsRuntime;
private static JavaScriptContext jsContext;
private static JavaScriptValue jsConvertFunc;
private static JavaScriptValue jsGlobalObj;
private static string jsSource = @"
shiftValue = Math.pow(10,6);
function convert(amount, sourceToUSDRate, targetToUSDRate)
{
.... your JS code...
}
";
static JSEngine()
{
jsRuntime = JavaScriptRuntime.Create();
jsContext = jsRuntime.CreateContext();
using (new JavaScriptContext.Scope(jsContext))
{
// run the script so functions get defined.
JavaScriptContext.RunScript(jsSource);
// Get the convert function handle.
Native.JsGetGlobalObject(out jsGlobalObj);
JavaScriptPropertyId id;
if (Native.JsGetPropertyIdFromName("convert", out id) != JavaScriptErrorCode.NoError)
{
throw new Exception("Convert function not found");
}
Native.JsGetProperty(jsGlobalObj, id, out jsConvertFunc);
}
}
public static double JSCurrencyConvertFunc(
double amount,
double sourceToUSDRate,
double targetToUSDRate)
{
double resultVal;
using (new JavaScriptContext.Scope(jsContext))
{
// convert args
JavaScriptValue[] jsArguments = new JavaScriptValue[4];
jsArguments[0] = jsGlobalObj;
jsArguments[1] = JavaScriptValue.FromDouble(amount);
jsArguments[2] = JavaScriptValue.FromDouble(sourceToUSDRate);
jsArguments[3] = JavaScriptValue.FromDouble(targetToUSDRate);
JavaScriptValue result;
JavaScriptErrorCode err = Native.JsCallFunction(jsConvertFunc, jsArguments, (ushort)jsArguments.Length, out result);
if (err != JavaScriptErrorCode.NoError)
{
throw new Exception($"{err} - Error calling function");
}
JavaScriptValueType type = result.ValueType;
if (type == JavaScriptValueType.Number)
{
resultVal = result.ToDouble();
}
else if (type == JavaScriptValueType.String)
{
resultVal = double.Parse(result.ToString());
}
else
{
throw new Exception("Invalid return type from Convert Java Script function");
}
}
return resultVal;
}
}
}
See how we got the return value and then can test the type of that value. Depending on the type,
we can convert the JavaScriptValue to a desired C# type.
At this point a simple console app can use the class we created to call the JS function we have
and use it to test various inputs and integrate with the rest of the test framework, call the back end data base and compare values. Pretty cool!.
double val = JSEngine.JSCurrencyConvertFunc(220.00d, 1.0000000d, 0.7375991148811d);
Console.WriteLine(val);
While this is really cool stuff, I wanted to see if I can take it one step further. I wanted to be able to integrate this into the SQL, and do comparisons directly in TSQL. As you know, SQL Server supported CLR integration since 2005. So we could expose our C# function above as a user defined function in SQL via SQL CLR. Once we do that, you could call that UDF in your stored procedures, triggers, views, etc.
So you would be calling CLR from SQL and the CLR code hosts the JavaScript engine, and runs your JavaScript function.
But before that we need to expose a static function from our JSEngine class above
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsPrecise = true,
IsDeterministic = true
)]
public static SqlMoney JSCurrencyConvert(SqlMoney amount, SqlDecimal sourceToUSDRate, SqlDecimal targetToUSDRate)
{
double amountd = amount.ToDouble();
double sourceToUSDRated = sourceToUSDRate.ToDouble();
double targetToUSDRated = targetToUSDRate.ToDouble();
double result = JSCurrencyConvertFunc(amountd, sourceToUSDRated, targetToUSDRated);
return new SqlMoney(result);
}
Now we have a C# assembly (dll) that can be used with SQL CLR.
We need to do a bit of preparation.
Enable CLR in SQL.
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'clr enabled', 1; GO RECONFIGURE; GOYou need to have permission to register unsafe assemblies. In your SQL Sever Management Studio, go to your login, and under securables, set the unsafe assemblies to GRANT
You need to set TRUSTWORTHY ON
ALTER DATABASE SET TRUSTWORTHY ON;Create Assembly in CLR
CREATE ASSEMBLY your assembly name without the dll ext FROM 'Your assembly path' WITH PERMISSION_SET = UNSAFEMap the C# function to a user defined function in SQL.
CREATE FUNCTION JSCurrencyConvert(@amount money, @sourceToUSDRate as decimal(25,13), @targetToUSDRate as decimal(25,13)) returns money as external name [EdgeJSHost].[EdgeJSHost.JSEngine].JSCurrencyConvert
Here is another Gotcha . If the class is under a name space, you need to enclose the
path to the class in [] like [NameSpace1.Namespace2.Class]. It took me a while to figure out how to get this to work.
Anyways, we now have the assembly in the database, mapped the function in C# to an SQL UDF. When you call the UDF in SQL, it will call into CLR which hosts the JS Engine and runs your JavaScript. Let try that.
declare @amount money
declare @s as decimal(25,13)
declare @t as decimal(25,13)
set @amount = 345
set @s = 1.000000000
set @t = 0.7375991148811
select dbo.JSCurrencyConvert(@amount, @s, @t)
That's how you call a Java Script function from SQL via CLR.