Service Task for MsSQL Connection

Camunda 7 Topics Discussion & Questions
1

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)

2

This problem has almost nothing to do with Camunda.

Anyhow, these lines

possibly should read like these

System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());

then you’ll see more details why the database connection could not not be established.

1 Like
3

Thanks a lot I got it.

4