SQL Server 2019: Java in SQL Server hard to believe, no?

It has already been a few months that we are testing the next version of SQL Server: SQL Server 2019.
I already blogged about a previous version of SQL Server supporting R and Python.
With the new version of SQL Server 2019, Java will also be integrated.
The Java runtime used is Zulu Open JRE and can be tested from the CTP3.2 of SQL Server 2019

Step 1: The installation

Like a lot of people, I use the GUI to install SQL Server.
On the Feature Selection page, you can select Java in the Machine Learning and Language Extension.

Why not only Machine Learning like the precedent version? Because Java is not a Machine Learning language and now you have this new category “Language Extention”

After checking Java, you have a “Java Install Location”.
As written on the installation page, by default, it’s Zulu Open JRE 11.0.3 which is installed

but you can try to install another one…
I tried to install from a local directory and I searched the Zulu package on the web through this link here

I copied the zip and unzipped it

I put the link into the

Now, just install it.

After the installation finished, I had a look on the result file from the installationile.ini to see what the settings is to install it by script:

As you can see, for scripting the installation, we need to use the label SQL_INST_JAVA

Step 2: Configure Java

According to the documentation of Microsoft

• Add the JRE_HOME variable: create an environment variable JRE_HOME with the Path where jvm.dll is located
  
  
• Grant access to non-default JRE folder: Grant access to the SQLRUsergroup and SQL Service accounts with these commands:
  • icacls “” /grant “SQLRUsergroup”:(OI)(CI)RX /T
    
    In my case, I used a named instance and the usergroup need to be adapted with the instance name

    icacls "C:\Temp\zulu11.33.15-ca-jdk11.0.4-win_x64\zulu11.33.15-ca-jdk11.0.4-win_x64" /grant "SQLRUsergroupSQL2019_CTP32":(OI)(CI)RX /T

• • icacls “” /grant “ALL APPLICATION PACKAGES”:(OI)(CI)RX /T
    

    icacls "C:\Temp\zulu11.33.15-ca-jdk11.0.4-win_x64\zulu11.33.15-ca-jdk11.0.4-win_x64" /grant "ALL APPLICATION PACKAGES":(OI)(CI)RX /T

Step 3: Configure SQL Server Engine

First of all, we enable the usage of “external scripts” through the sp_configure

SELECT * FROM sys.configurations WHERE name= N'external scripts enabled'

If the result is 0, then enable it with:

EXEC sp_configure 'external scripts enabled', 1
RECONFIGURE WITH OVERRIDE

SELECT * FROM sys.configurations WHERE name= N'external scripts enabled'

After that, as you can see the is_dynamic column is 1 then you don’t need to restart the SQL Server Engine to use it

As you can see with SELECT * FROM sys.external_language_files you don’t see Java as external language
We need to register the external language to our Database.

To do this last step, use this command on a database:

CREATE EXTERNAL LANGUAGE Java FROM (CONTENT = N'', FILE_NAME = 'javaextension.dll');

But first, we need to search the package with the javaextention.dll and copy it to another path.

After that, you can register it

At the end, verify again with SELECT * FROM sys.external_language_files

You need to have a new line with the id 65536 and the javaextention.dll

The associated code:

SELECT * FROM sys.external_language_files
USE Test
GO
CREATE EXTERNAL LANGUAGE Java FROM (CONTENT = N'C:\Temp\java-lang-extension.zip', FILE_NAME = 'javaextension.dll');
SELECT * FROM sys.external_language_files

Step 4: Test a java code

I created a little package called test.jar with the class Hello in the package sth printing “Hello World”

But this package will not work… 🙁
To execute under SQL Server, we need to load the library mssql-java-lang-extension into the Referenced Libraries from Eclipse, import the com.microsoft.sqlserver.javalangextension.* and use the execute method.
One important point is also to create the default constructor with

• executorExtensionVersion = SQLSERVER_JAVA_LANG_EXTENSION_V1;
• executorInputDatasetClassName = PrimitiveDataset.class.getName();
• executorOutputDatasetClassName = PrimitiveDataset.class.getName();

It’s just a little bit more complicated… 😕
Export the class as Runnable JAR and go back to SQL Server

The next step is to register the javaSDK and my Runnable JAR test.jar with the command:

CREATE EXTERNAL LIBRARY javaSDK FROM (CONTENT = 'C:\Temp\mssql-java-lang-extension.jar') WITH (LANGUAGE = 'Java');
GO
CREATE EXTERNAL LIBRARY test FROM (CONTENT = 'C:\Temp\test.jar') WITH (LANGUAGE = 'Java');
GO

After the registration, you can execute the DMV sys.external_libraries to see if the registration successed:

SELECT * FROM sys.external_libraries

Now the last step is to execute my library and see “Hello World”:

EXEC sp_execute_external_script  @language = N'Java' , @script = N'sth.Hello'
GO

Et voila, my first Java test with SQL Server. Easy, no? 😎

Here you can find my test.jar file in this zip file