Microsoft introduced the ability to call Java code from SQL Server in around the SQL Server 2019 CTP 2.0 release, and I have written some posts posts about this:
Throughout the CTP releases some functionality has changed, and in CTP 2.5 Microsoft introduced the Java Language Extension SDK. The Java code we write needs now to include the Java SDK and extend an abstract base from the SDK.
With the introduction of the Java Language SDK, came some changes to how you handle method parameters, and I touched upon it briefly in the Java & SQL Server 2019 Extensibility Framework: The Sequel post. In this post, I want to look more in detail at parameters and how to handle them.
DISCLAIMER: This post contains Java code. I am not a Java guy, in fact, the only Java I have ever written is the code for the SQL Server 2019 Java posts. So, the code is not elegant in any shape or form, and I am absolutely certain it can be done in a much better way. However, this is not about Java as such, but how you call Java code from SQL Server, and what you need to implement on the Java side.
Java Code in SQL Refresh
If you have not done any Java code in SQL Server, or at least not recently, here are some posts which introduce you to Java in SQL Server:
- SQL Server 2019, Java & External Libraries - I. Part I of a couple of posts where we look at how we can deploy Java code to the database, so it can be loaded from there.
- Java & SQL Server 2019 Extensibility Framework: The Sequel. We look at the implications of the introduction of the Java Language Extension SDK.
- SQL Server 2019 Extensibility Framework & External Languages. We look at SQL Server 2019 Extensibility Framework and Language Extensions.
- SQL Server 2019 CTP3.2 & Java. SQL Server 2019 CTP 3.2 and Azule OpenJDK.
- SQL Server 2019 & Java Null Handling: Take Two. We take a second look at how to handle null values in datasets being passed to and from Java code.
If you want to follow along with what we are doing in this post, you need:
- A SQL Server 2019 CTP 3.0, (or later), instance.
- A database where you have registered Java as a language. Read this post if you are unsure how to register Java as an external language.
- The Java Language SDK deployed to the database above. This post explains how.
- Understand how to deploy Java code to the database. The post here helps with that.
You also need an editor with which you can write Java code. I am partial to Visual Studio Code myself, and I wrote a post about VS Code and Java here.
Let us take a look back at parameters to refresh our memory.
BJLSDK (Before Java Language SDK)
Before the introduction of the Java Language SDK, (before CTP 2.5), you called into a method specified in the
@script variable of the
sp_execute_external_script, (SPEES), procedure. You defined the parameters you wanted to send to the Java code as class variables with the same name as the SQL parameters but without the
Code Snippet 1 Call from T-SQL to Java with Parameters
We see in Code Snippet 1 how we want to call into the
adder method in the
JavaTest1 class, passing in two parameters:
@y. When we execute the code the Java C++ language extension gets the parameter values, and looks in the code for two class-level variables named
y, and assigns the values to those variables. The
adder method then uses
AJLSDK (After Java Language SDK)
I mentioned above how a couple of things changed after Microsoft introduced the Java Language SDK. One of them was that you no longer define a method in SPEES’s
@script parameter. The parameter instead defines a class you want to call into.
The class must extend the
AbstractSqlServerExtensionExecutor abstract base class and implement the
execute method from the base class. The
execute method is the method the Java C++ language extension calls into, and the signature looks like so:
Code Snippet 2 The
From the signature in Code Snippet 2 we see how the
execute method takes two parameters and has a return type.
To call into Java after the introduction of the Java Language SDK has not changed much:
Code Snippet 3: Call from T-SQL to Java with Parameters
The only real difference between Code Snippet 3 and Code Snippet 2 is that the
@script parameter is now
package.class, instead of
For the parameters the Java C++ language extension creates a new instance of a
LinkedHashMap and populates it with the parameter names, (sans
@), and the values.
Having the recap out of the way, let us see how we handle the parameters in our Java code. Let us first start with some skeleton code:
Code Snippet 4: Java Starter Code
We see in Code Snippet 4 that we have a class,
JavaTest1, which extends the
AbstractSqlServerExtensionExecutor class. In the constructor, we set some required class members, and the
execute method is empty.
NOTE: The post Java & SQL Server 2019 Extensibility Framework: The Sequel explains about the constructor in Code Snippet 4.
The code we want to write is to handle the parameters we send in when we execute the code in Code Snippet 3, and in our code, we want to add the two parameters together. With that in mind, our code looks like so:
Code Snippet 5: Handle Parameters
We see in Code Snippet 5 how we handle the parameters that come in from SQL Server:
- As we said above, the Java C++ language extension populates the
LinkedHashMapwith the parameters from SQL Server, so we retrieve the
getmethod. As the
getmethod returns an object, we cast it to
- We then add
ytogether and we call
printfto show the result.
After we compile and package the code into a
.jar file we deploy to our database:
Code Snippet 6: Deploy to the Database
From Code Snippet 6 we see how I deploy to my SQL Server based on a file-path:
W:\sql-params-1.0.jar, and how I indicate that what I deploy is Java code:
LANGUAGE = 'Java'. The post SQL Server 2019, Java & External Libraries - I covers in detail how to deploy Java code to SQL Server.
NOTE: I can use a file path when I deploy as my SQL Server is on my local machine.
When we have deployed as in Code Snippet 6 we execute the code in Code Snippet 3:
Figure 1: Add Result
In Figure 1 we see that our code worked, cool!
Now then, what if one of the parameters in Code Snippet 3 is not
DECLARE:ed and set, but instead generated by something, (perhaps a
SELECT), and it doesn’t have a value:
Code Snippet 7: Null Parameter
In Code Snippet 7 we see how we retrieve the values of the two parameters, and one is null, (no value). Let us see what happens when we execute the code:
Figure 2: Null Parameter
Hmm, not good, we get a
NullPointerException as we see in Figure 2. As a Java developer, this is what you would expect, whereas if we executed similar code in T-SQL, it would work - but the result would be
NULL. I wrote here about null handling in datasets, and how there is a “convenience” method
getColumnNullMap which indicates null values for a column in the dataset.
Unfortunately, we do not have the same for parameters, so we need to handle it ourselves. Since the parameter value’s type in the
LinkedHashMap is an
Object, we should be able to do a null check quite easily before we do the cast. Alternatively, we can use the primitive type’s wrapper class to accomplish the same thing:
Code Snippet 8: Handle Null Parameter
In Code Snippet 8 we see an example of how we can handle null parameters. As you see in the code snippet, we use the
Integer wrapper class for
int. Our logic is that if we get a null value, then the result should also be null.
In SQL Server, when we execute a procedure, we can return values as output parameters, and the way we indicate that a parameter is for output is by using the
Code Snippet 9: Output Parameters - I
We see in Code Snippet 9 how we assign
@ret as an output parameter and then select the value after the execution of the procedure.
How do we do this in Java? Well, initially output parameters were not supported, but that changed with SQL Server CTP 3.0, (or 3.1). The reason why output parameters were not supported was that the Java C++ language extension did not have an implementation for output parameters. However, as I said above - that changed in CTP 3.0/3.1, and output parameters are now part of the
The Java C++ language extension populates, as I said above, the
LinkedHashMap and since it is a reference type when the call returns the Java C++ language extension loops through the parameters and outputs the parameters adorned with
The code to implement output parameters in our Java code looks like so:
Code Snippet 10: Output Parameters in Java
We see in Code Snippet 10 how we have changed the Java code to return the result of the addition of
y as an output parameter. We retrieve the
y values as before, and dependent on whether
y is null or not we either
retVal parameter as
null or the result of the addition.
NOTE: It is not necessary to retrieve the
retValparameter as in Code Snippet 10 since in this case, it is a pure output parameter. SQL Server, however, allows output parameters both to be used as in as out. So in your Java code, you may get a value coming in for an output parameter.
The SQL code to execute against the code in Code Snippet 10 looks like so:
Code Snippet 11: Output Parameters - II
We see in Code Snippet 11 how we:
- Declare a third parameter
- In the
@paramsparameter we define the
The result of executing the code in Code Snippet 11 is:
Figure 3: Output Parameter
As we see in Figure 3 it works. If we were to send in null for
@y, we get back
NULL in the
In this post, we saw how to handle parameters, and we said:
LinkedHashMapparameter in the
executemethod exposes parameters.
- For primitive data type parameters, we can use Java’s wrapper classes to handle null values.
- The Java C++ language extension handles now output parameters.
- We assign a value to an output parameter by doing a
If you have comments, questions etc., please comment on this post or ping me.
comments powered by Disqus