|Java Language Extensions for SQL Server Open Sourced
|Written by Nikos Vaggalis
|Monday, 25 May 2020
Language extensions - that's SQL Server's way of calling Java programs from within T/SQL and it's very useful.
SQL Server had for a long time been integrated with the Common Language Runtime under which it could interface with the general programming languages of the .NET framework, allowing for writing stored procedures, triggers, user-defined types, user-defined functions, user-defined aggregates, and streaming table-valued functions using the likes of Visual Basic .NET or C#.
But that's deep integration. For the rest of the languages, not part of the .NET framework, SQL Server can still interact with them at a minimal level through the so-called Language Extensions SDK, for the time being just programs written in Java.
Java support was added with the Microsoft Extensibility SDK for Java as part of SQL Server 2019 Release Candidate 1 on both Windows and Linux.
The default Java runtime used is Azul's Zulu Open JRE, but you can also use another Java JRE or SDK. There are, however, good reason to opt for the Zulu Java runtime. In order to work around Oracle's licensing traps regarding Java, Microsoft partnered with Azul to provide the Enterprise distribution of Java for free and, most importantly, with support provided by both Microsoft and Azul.
To make it work, you just compile your class as library/.jar, and write a T-SQL stored procedure that calls another one, sp_execute_external_script, which finally makes the call to the Java library as "package.class".
There's a detailed official tutorial on writing a Java class that checks a set of strings passed in from SQL Server against a regular expression. In more detail, we write a stored procedure that takes an input dataset and a regular expression. This procedure executes sp_execute_external_script with those inputs, which in turn calls our library regex.jar to return the rows that fulfilled the given regular expression. It uses a regular expression [Jj]ava that checks if a text contains the word Java or java.
This extensibility goes a long way - you can use it in all kinds of scenarios such as calling REST APIs from within SQL server directly. In other words, you won't have to write a Java program that interfaces with the database through the JDBC to fetch the data that is then used to call a RESTful service in order get some return values back. You can even work with JSON since SQL Server supports JSON handling inherently. So from within your T-SQL you just directly call the Java program that interfaces with the API.
Security wise, it's also important to note that the Java program would execute in the context of a SQL Server query as the same SQL Server sandboxing safeguards apply there too.
The latest news is that Microsoft went one step further and open sourced this Java Language Extension in an effort to motivate the open source community to contribute to the project.
So while not a tight integration with the innards of SQL Server through the CLR, Language Extensions prove immensely valuable. DBMSs have gone a long way integrating advanced functionality found in general programming languages or libraries, but can't do everything. We've seen such an example in "Ingres 11 Technical Preview" when covering the news about Actian's new version of the venerable DBMS.
So in that version there were added things like MD5SUM,SHA1SUM,AES symmetric encryption for BLOB.Also String functions like DMETAPHONE(c1) which returns the metaphone code from a varchar string using the double metaphone algorithm, JARO_WINKLER(c1 c2) which calculates the jaro_winkler distance between two VARCHAR strings,
Also additions on the regular extensions front, by supporting patterns with LIKE, SIMILAR TO, BEGINNING, ENDING.
For example, BEGINNING returns the substring from the string that starts with the first occurrence of the pattern, while ENDING returns the substring from the string that ends with the last occurrence of the pattern:
LIKE and SIMILAR TO return the substring from the string that best matches the pattern:
The point made here is that even with great additions such as these, which are very welcome and enhance the product much, there's still a limit in functionality. Hence calling external libraries that can do it better is highly desirable.
Now there's a thought - what about the OSS community adding a language extension that calls Perl to leverage its advanced regular expression functionality?
or email your comment to: email@example.com
|Last Updated ( Wednesday, 27 May 2020 )