Hello everybody, I tried to create a Service Task which connects to an MsSQL Server but i got just errors. Has anybody an idea where is my issue?
I tried to create an extra class for the connection to MSSQL with three function createconnection closeconnection and the Statement.
Thanks everybody for your Help!!!
I added the following dependency:
------------------------Dependency MSSQL connetor-------------------------
com.microsoft.sqlserver
mssql-jdbc
7.4.1.jre12
-------------------------Java Delegate----------------------------------------------------------------
package company.Sonderartikel;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import org.camunda.bpm.engine.delegate.DelegateExecution;
import org.camunda.bpm.engine.delegate.JavaDelegate;
public class preissuche implements JavaDelegate {
static ResultSet rs;
public void execute(DelegateExecution execution) throws Exception {
String Artikelnummer = (String) execution.getVariable("Artikelnummer");
MsSQLcon dbcon = new MsSQLcon("MsSQLcon", "datacenter", "1433", "diverses", "******", "****************");
dbcon.createconnection();
rs=dbcon.getinfos(Artikelnummer);
if(rs.next()==false)
{
execution.setVariable("Einkaufspreis", 0);
}
else
{
execution.setVariable("Einkaufspreis", rs.getString(2));
}
dbcon.closeconnection();
}
}
-----------------------------------------------SQL Class------------------------------------------------------
package company.Sonderartikel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MsSQLcon {
static Connection conn = null;
static Statement stmt = null;
static ResultSet rs = null;
private String Name;
private String IP;
private String Port;
private String DB;
private String User;
private String Password;
//Constructor
MsSQLcon(String Name, String IP, String Port, String DB, String User, String Password) {
this.Name = Name;
this.IP = IP;
this.Port = Port;
this.DB = DB;
this.User = User;
this.Password = Password;
}
//connection Builder
public void createconnection() {
try {
conn = DriverManager.getConnection("jdbc:sqlserver://" + IP + ":" + Port + ";databaseName=" + DB + ";" + "user=" + User + ";password=" + Password);
System.out.println(Name + " connection created");
} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: ex.getMessage()");
System.out.println("SQLState: ex.getSQLState()");
System.out.println("VendorError: ex.getErrorCode()");
}
}
public void closeconnection(){
try {
conn.close();
System.out.println(Name + " connection closed");
} catch (SQLException e) {
e.printStackTrace();
System.out.println("Fehler in SQL Close connection");
}
}
// SQL Statement
public ResultSet getinfos(String Artikelnummer) {
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT [Artikel] ,[Preis] FROM [diverses].[dbo].[Preisliste_2020_001] where Artikel='"+Artikelnummer+"'");
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
}
---------------------------------Error----------------------
SQLException: ex.getMessage()
SQLState: ex.getSQLState()
VendorError: ex.getErrorCode()
29-May-2020 08:20:34.141 SEVERE [http-nio-8080-exec-98] org.camunda.commons.logging.BaseLogger.logError ENGINE-16006 BPMN Stack Trace:
ServiceTask_0gs6z60 (activity-execute, ProcessInstance[810666ac-a174-11ea-bb9a-00051bd1ec33], pa=Sonderartikel)
ServiceTask_0gs6z60, name=Preis ermitteln
^
|
ExclusiveGateway_1oxsxhg
^
|
StartEvent_1
29-May-2020 08:20:34.143 SEVERE [http-nio-8080-exec-98] org.camunda.commons.logging.BaseLogger.logError ENGINE-16004 Exception while closing command context: null
java.lang.NullPointerException
at company.Sonderartikel.preissuche.getinfos(preissuche.java:59)
at company.Sonderartikel.preissuche.execute(preissuche.java:79)
at org.camunda.bpm.engine.impl.bpmn.delegate.JavaDelegateInvocation.invoke(JavaDelegateInvocation.java:39)