You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
The Java language extension runs in the extensibility framework as an add-on to a database engine instance. The framework is an architecture for executing external code: Java (starting in SQL Server 2019), Python (starting in SQL Server 2017) and R (starting in SQL Server 2016). Code execution is isolated from core engine processes, but fully available to relational data as stored procedures, as T-SQL script containing Java, or as Java containing T-SQL. In contrast with R and Python, there are no data science models or machine learning algorithms in the Java extension.
17
+
Starting in SQL Server 2019, you can run custom Java code in the [extensibility framework](../concepts/extensibility-framework.md)as an add-on to the database engine instance.
18
18
19
-
To use the Java extension to run your own code, follow these steps.
19
+
The extensibility framework is an architecture for executing external code: Java (starting in SQL Server 2019), [Python (starting in SQL Server 2017)](../concepts/extension-python.md), and [R (starting in SQL Server 2016)](../concepts/extension-r.md). Code execution is isolated from core engine processes, but fully integrated with SQL Server query execution. This means that you can push data from any SQL Server query to the external runtime, and consume or persist results back in SQL Server.
20
+
21
+
As with any programming language extension, the system stored procedure [sp_execute_external_script](https://docs.microsoft.com/sql/relational-databases/system-stored-procedures/sp-execute-external-script-transact-sql) is the interface for executing pre-compiled Java code.
20
22
21
23
## 1 - Feature installation
22
24
23
-
Run SQL Server 2019 Setup on Windows or Linux to install a database engine instance, adding the Machine Learning Services feature. This feature provides the extensibility framework and adds the security layer that isolates code execution, and protects data from unauthorized access.
25
+
Run SQL Server 2019 Setup on Windows or Linux to install the Java language extension. A SQL Server 2019 database engine instance is required. You cannot add Java integration to earlier versions.
26
+
27
+
On Windows, start the [Installation Wizard](../install/sql-machine-learning-services-windows-install.md). In Feature Selection, select **Machine Learning Services (in-database)**. Although Java integration does not come with machine learning libraries, this is the option in setup that provides the extensibility framework. You can omit R and Python if you wish.
28
+
29
+
On Linux, install the [database engine](https://docs.microsoft.com/sql/linux/sql-server-linux-setup), as well as the [extensibility package and Java extension package](../../linux/sql-server-linux-setup-machine-learning.md).
30
+
31
+
The following examples illustrate the syntax for each Linux operating system.
Using SQL Server Management Studio or another tool that runs Transact-SQL script, configure external script execution on the database engine instance.
50
+
51
+
```sql
52
+
EXEC sp_configure 'external scripts enabled', 1
53
+
RECONFIGURE WITH OVERRIDE
54
+
-- No restart is required after this step as of SQl Server 2019
55
+
```
56
+
57
+
## 3 - Bring your own Java
58
+
59
+
One difference from previous language integrations such as R and Python is that you control which JVM is used with SQL Server.
24
60
25
-
+ On Windows, start the [Installation Wizard](../install/sql-machine-learning-services-windows-install.md). In Feature Selection, select **Machine Learning Services (in-database)**. You can omit R and Python if you wish.
61
+
| Java version | Operating system |
62
+
|--------------|------------------|
63
+
|[Java 1.10](http://jdk.java.net/10/)| Windows |
64
+
| Java 1.8 | Linux |
26
65
27
-
+ On Linux, install the [database engine and extensibility package](../../linux/sql-server-linux-setup-machine-learning.md).
66
+
Given that Java is backwards compatible, earlier versions might work, but the supported and tested versions for this early CTP release are listed in the table.
28
67
29
-
Be sure to complete the enable external script execution step in the installation instructions.
68
+
> [!Note]
69
+
>To run Java with SQL Server, you technically only need the Java Runtime Environment installed (JRE). The JDK is a development kit including the Java compiler and other development related packages. If you already have a development environment and only need a Java runtime on the server machine, you can ignore the JDK installation instructions and only install JRE.
30
70
31
-
## 2 - Download the Java SDK
71
+
## JDK on Windows
32
72
33
-
You can find the Java SDK at [http://jdk.java.net/10/](http://jdk.java.net/10/).
73
+
Download Windows version of the [Java SE Development Kit (JDK)](http://www.oracle.com/technetwork/java/javase/downloads/jdk10-downloads-4416644.html).
34
74
35
-
Install Java JDK under the default /Program Filesif you want to avoid having to grant read perm to **ALL APPLICATION PACKAGES**on an alternate location. The same guidance applies for access to your Java classpath folder(s), where you keep your .class and .jar files.
75
+
Install the JDK under the default /Program Files/ folder if you want to avoid having to grant read permission to **ALL APPLICATION PACKAGES**and the **SQLRUserGroup** security groups on an alternate location. The same guidance applies for access to your Java classpath folders, where you keep your .class or .jar files.
36
76
37
-
### Grant access to non-default Java SDK folder
77
+
> [!Note]
78
+
> The authorization and isolation model for extensions has changed in this release. For more information, see [Differences in a SQL Server Machine 2019 Learning Services installation](../install/sql-machine-learning-services-ver15.md).
38
79
39
-
If you installed the Java SDK to a non-default folder, run the following PowerShell script to grant access to AppContainers hosting Java processes on SQL Server:
80
+
### Grant access to non-default JDK folder (Windows only)
81
+
82
+
You can skip this step if you installed the JDK/JRE in the default folder. For a non-default folder installation, run the following PowerShell scripts to grant access to the **SQLRUsergroup** and SQL Server service accounts (in ALL_APPLICATION_PACKAGES) for accessing the JVM and the Java classpath.
You also need to add the JDK/JRE installation path (for example, "C:\Program Files\Java\jdk-10.0.2") to a system environment variable that you name "JAVA_HOME".
104
+
105
+
To create a system variable, use Control Panel > System and Security > System to access **Advanced System Properties**. Click **Environment Variables** and then create a new system variable for JAVA_HOME.
106
+
107
+
![Environment variable for Java Home](../media/java/env-variable-java-home.png"Setup for Java")
108
+
109
+
## JDK on Linux
110
+
111
+
On Linux, the mssql-server-extensibility-java package automatically installs JRE 1.8 if it is not already installed. It will also add the JVM path to an environment variable called JAVA_HOME.
112
+
113
+
## Limitations in CTP 2.0
114
+
115
+
* The number of values in input and output buffers cannot exceed `MAX_INT (2^31-1)` since that is the maximum number of elements that can be allocated in an array in Java.
49
116
50
-
Connect to the relational database providing your data and create a stored procedure that calls [sp_execute_external_script]() system stored procedure. Save the stored procedure. Permissions on the database apply to Java code execution.
117
+
* Output parameters in sp_execute_external_script are not supported in this version.
51
118
52
-
## 4 - Call the procedure from your app
119
+
* No LOB datatype support for input and output data sets in this version. See [Java and SQL Server data types](java-sql-datatypes.md) for details about which data types are supported in this CTP.
53
120
54
-
Programmatic access to the code occurs when you call the procedure from your code. For one example, see [Using a Stored Procedure with Input Parameters (JDBC)](https://docs.microsoft.com/sql/connect/jdbc/using-a-stored-procedure-with-input-parameters?view=sql-server-2017).
121
+
* Streaming using the sp_execute_external_script parameter @r_rowsPerRead is not supported in this CTP.
55
122
56
-
## See also
123
+
* Partitioning using the sp_execute_external_script parameter @input_data_1_partition_by_columns is not supported in this CTP.
57
124
58
-
[Install SQL Server Machine Learning Services on Windows](../install/sql-machine-learning-services-windows-install.md)
125
+
## Next steps
59
126
60
-
[Install SQL Server Machine Learning Services on Linux](../../linux/sql-server-linux-setup-machine-learning.md)
127
+
+[How to call Java in SQL Server](howto-call-java-from-sql.md)
128
+
+[Java sample in SQL Server](java-first-sample.md)
129
+
+[Java and SQL Server data types](java-sql-datatypes.md)
When using the [Java language extension](extension-java.md), the [sp_execute_external_script](https://docs.microsoft.com/sql/relational-databases/system-stored-procedures/sp-execute-external-script-transact-sql) system stored procedure is the interface used to call the Java runtime. Permissions on the database apply to Java code execution.
18
+
19
+
This article explains implementation details for Java classes and methods that execute on SQL Server. Once you are familiar with these details, review the [Java sample](java-first-sample.md) as your next step.
20
+
21
+
## Basic principles
22
+
23
+
* Compiled custom Java classes must exist in .class files or .jar files in your Java classpath. The [CLASSPATH parameter](#set-classpath) provides the path to the compiled Java files.
24
+
25
+
* The Java method you are calling must be provided in the "script" parameter on the stored procedure.
26
+
27
+
* If the class belongs to a package, the "packageName" needs to be provided.
28
+
29
+
* "params" is used to pass parameters to a Java class. Calling a method that requires arguments is not supported, which makes parameters the only way to pass argument values to your method.
30
+
31
+
> [!Note]
32
+
> This note restates supported and unsupported operations specific to Java in CTP 2.0.
33
+
> * On the stored procedure, input parameters are supported. Output parameters are not.
34
+
> * Streaming using the sp_execute_external_script parameter **@r_rowsPerRead** is not supported.
35
+
> * Partitioning using **@input_data_1_partition_by_columns** is not supported.
36
+
> * Parallel processing using **@parallel=1** is supported.
37
+
38
+
## Call sp_execute_external_script
39
+
40
+
The [sp_execute_external_script](https://docs.microsoft.com/sql/relational-databases/system-stored-procedures/sp-execute-external-script-transact-sql) system stored procedure is the interface used to call the Java runtime. The following example shows an sp_execute_external_script using the Java extension, and parameters for specifying path, script, and your custom code.
, @params = N'@CLASSPATH nvarchar(30), @param1 INT'
54
+
, @CLASSPATH = @myClassPath
55
+
, @param1 = @param1
56
+
```
57
+
58
+
<a name="set-classpath"></a>
59
+
60
+
## Set CLASSPATH
61
+
62
+
Once you have compiled your Java class or classes and placed the .class file(s) or .jar files in your Java classpath, you have two options for providing the classpath to the SQL Server Java extension:
63
+
64
+
**Option 1: Pass as a parameter**
65
+
66
+
One approach for specifying a path to compiled code is by setting CLASSPATH as an input parameter to the sp_execute_external_script procedure. The [Java sample](java-first-sample.md#call-method) demonstrates this technique. If you choose this approach, and have multiple paths, be sure to use the path separator that is valid for the underlying operating system:
67
+
68
+
* On Linux, separate the paths in the CLASSPATH with colon ":".
69
+
* On Windows, separate the paths in CLASSPATH with a semi-colon ";"
70
+
71
+
**Option 2: Register a system variable**
72
+
73
+
Just as you created a system variable for the JDK executables, you can create a system variable for code paths. To do this, created a system environment variable called "CLASSPATH"
74
+
75
+
## Class requirements
76
+
77
+
In order for SQL Server to communicate with the Java runtime, you need to implement specific static variables in your class. SQL Server can then execute a method in the Java class and exchange data using the Java language extension.
78
+
79
+
> [!Note]
80
+
> Expect the implementation details to change in upcoming CTPs as we work to improve the experience for developers.
81
+
82
+
## Method requirements
83
+
To pass arguments, use the **@param** parameter in sp_execute_external_script. The method itself cannot have any arguments. The return type must be void.
84
+
85
+
```java
86
+
public static void test() {}
87
+
```
88
+
89
+
## Data inputs
90
+
91
+
This section explains how to push data to Java from a SQL Server query using **InputDataSet** in sp_execute_external_script.
92
+
93
+
For every input column your SQL query pushes to Java, you need to declare an array.
94
+
95
+
### inputDataCol
96
+
97
+
In the current version of the Java extension, the **inputDataColN** variable is required, where *N* is the column number.
98
+
99
+
```java
100
+
public static <type>[] inputDataColN = new <type>[1]
101
+
```
102
+
103
+
These arrays have to be initialized (the size of the array needs to be greater than 0, and does not have to reflect the actual length of the column).
104
+
105
+
Example: `public static int[] inputDataCol1 = new int[1];`
106
+
107
+
These array variables will be populated with input data from a SQL server query before execution of the Java program you are calling.
108
+
109
+
### inputNullMap
110
+
111
+
Null map is used by the extension to know which values are null. This variable will be populated with information about null values by SQL Server before execution of the user function.
112
+
113
+
The user only needs to initialize this variable (and the size of the array needs to be greater than 0).
114
+
115
+
```java
116
+
public static boolean[][] inputNullMap = new boolean[1][1];
117
+
```
118
+
119
+
## Data outputs
120
+
121
+
This section describes **OutputDataSet**, the output data sets returned from Java, which you can send to and persist in SQL Server.
122
+
123
+
> [!Note]
124
+
> Output parameters in [sp_execute_external_script](https://docs.microsoft.com/sql/relational-databases/system-stored-procedures/sp-execute-external-script-transact-sql) are not supported in this version.
125
+
126
+
### outputDataColN
127
+
128
+
Similar to **inputDataSet**, for every output column your Java program sends back to SQL Server, you must declare an array variable. All **outputDataCol** arrays should have the same length. You need to make sure this is initialized by the time the class execution finishes.
129
+
130
+
```java
131
+
public static <type>[] outputDataColN = new <type>[]
132
+
```
133
+
134
+
### numberofOutputCols
135
+
136
+
Set this variable to the number of output data columns you expect to have when the user function finishes execution.
137
+
138
+
```java
139
+
public static short numberofOutputCols = <expected number of output columns>;
140
+
```
141
+
142
+
### outputNullMap
143
+
144
+
Null map is used by the extension to indicate which values are null. We require this since primitive types don't support null. Currently, we also require the null map for String types, even though Strings can be null. Nullvalues are indicated by "true".
145
+
146
+
This NullMap must be populated with the expected number of columns and rows you are returning to SQL Server.
147
+
148
+
```java
149
+
public static boolean[][] outputNullMap
150
+
```
151
+
152
+
## Next steps
153
+
154
+
+ [Java sample in SQL Server](java-first-sample.md)
155
+
+ [Java and SQL Server data types](java-sql-datatypes.md)
156
+
+ [Java language extension in SQL Server](extension-java.md)
0 commit comments