An Azure Function that I am building needs to be able to execute a procedure in an Azure SQL Server Database.
I have working Java code in Eclipse (based on @duffmo's answer in Java DB connection)
I then ported the code to an Azure Function in Visual Studio Code, for deployment to Azure. (Note I have removed security code etc.) I created the project using View/Command Palette/Azure Functions - Create New Project
package com.function;
import java.sql.*;
import java.util.*;
import com.microsoft.azure.functions.ExecutionContext;
import com.microsoft.azure.functions.HttpMethod;
import com.microsoft.azure.functions.HttpRequestMessage;
import com.microsoft.azure.functions.HttpResponseMessage;
import com.microsoft.azure.functions.annotation.AuthorizationLevel;
import com.microsoft.azure.functions.annotation.FunctionName;
import com.microsoft.azure.functions.annotation.HttpTrigger;
/**
* Azure Functions with HTTP Trigger.
*/
public class Function {
/**
* This function listens at endpoint "/api/HttpTrigger-Java". Two ways to invoke
* it using "curl" command in bash: 1. curl -d "HTTP Body" {your
* host}/api/HttpTrigger-Java&code={your function key} 2. curl "{your
* host}/api/HttpTrigger-Java?name=HTTP%20Query&code={your function key}"
* Function Key is not needed when running locally, it is used to invoke
* function deployed to Azure. More details:
* https://aka.ms/functions_authorization_keys
*/
private static final String DEFAULT_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String DEFAULT_URL = "jdbc:sqlserver://myserver.database.windows.net:1433;database=mydb;loginTimeout=10;user=myuser@myserver;password=mypassword;";
@FunctionName("HttpTrigger-Java")
public HttpResponseMessage run(@HttpTrigger(name = "req", methods = { HttpMethod.GET,
HttpMethod.POST }, authLevel = AuthorizationLevel.FUNCTION) HttpRequestMessage<Optional<String>> request,
final ExecutionContext context) {
Connection connection = null;
try {
connection = createConnection(DEFAULT_DRIVER, DEFAULT_URL,context);
connection.setAutoCommit(false);
String sqlUpdate = "{call MYDB.MYPROC(?,?}";
List<Object> parameters = Arrays.asList("Bar", "Foo");
execute(connection, sqlUpdate, parameters);
connection.commit();
} catch (Exception e) {
rollback(connection);
e.printStackTrace();
} finally {
close(connection);
}
return null;
}
public static Connection createConnection(String driver, String url, ExecutionContext context) throws ClassNotFoundException, SQLException {
Class.forName(driver);
return DriverManager.getConnection(url);
}
public static void close(Connection connection) {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(Statement st) {
try {
if (st != null) {
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void rollback(Connection connection) {
try {
if (connection != null) {
connection.rollback();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static double execute(Connection connection, String sql, List<Object> parameters) throws SQLException {
CallableStatement call = connection.prepareCall(sql);
try {
int i = 0;
for (Object parameter : parameters) {
call.setObject(++i, parameter);
}
call.executeUpdate();
} finally {
close(call);
}
return 0;
}
}
However the line
Class.forName(driver);
causes the following error
java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:583)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:521)
at java.base/java.lang.Class.forName0(Native Method)
I tried to solve this by
- Putting the
sqljdbc4.jarin a "lib" directory Manually adding the following to the pom.xml
<dependency><groupId>com.microsoft.sqlserver</groupId><artifactId>sqljdbc4</artifactId><version>4.0</version></dependency>Trying to install the jar from the terminal via
mvn install:install-file -Dfile=’C:=myPath\myFunction\lib\sqljdbc4.jar'-DgroupId=package -DartifactId=sqljdbc4 -Dversion='4.0' -Dpackaging=jarexperimented with changing the order of 'microsoft' and 'sqlserver' in the DEFAULT_DRIVER string.
Try and add SQLJDBC from the new Java Dependencies view (see @hemangs answer) - but it does not appear in the list
I edited the .classPath as per @asndr's answer in .classpath - note that I did not manage to access the .classPath from within VS Code, but rather via File Explorer - and then ran
view/Command Palette/Java: Clean the java language server workspace
Any ideas?
