How to connect to the SQL DB from inside of a Java Service?

, ,
1

Greetings,

I have a need for implementing a complex algorithm involving multiple calls for a SQL DB that can not be resolved to a single call, so I need to be able to address the DB from inside of a Java Service.
When using this code:

	String dbURL = "jdbc:sqlserver://....";  // the connection string I verified and it's valid
	Connection conn = DriverManager.getConnection(dbURL);

I get an error like this:

java.sql.SQLException: No suitable driver found for Server=…

Following various advices, I’ve added JDBC drivers to the project folder:
image

and also made sure that same drivers are installed on the server as this guide recommended https://documentation.softwareag.com/webmethods/adapters_estandards/Adapters/JDBC/JDBC_9-0/9-0_Adapter_for_JDBC_Install_and_Users_Guide.pdf
image

Yet the error persists.

I was offered to use ExecuteService template using a pre-configured connection to launch the service and use the following connection method:

	java.sql.Connection conn = 	(java.sql.Connection)IDataUtil.get(pipeline.getCursor(),  
								"$db_service_connection");

While it technically works, I’m unable to debug the Java code as it requires launching it from a TestHarness application, so I can get either data to debug with or the debugging ability itself, but not both. I would rather prefer to use the “classic” method of connection, if possible.

Thank you.

2

Hi Kirill,

which version of wM are you running on?
Which version of JDBC Adapter are you using?
Any Fixes applied to them?

Regarding the driver:
Please do not use both jars together.
Depending on the wM version (and the Jave version it is running on) you will only need either the jre8 (for java version 8+) or the jre11 (for java version 11+) variant.

Remember to import the driver classes in your java service.
When updating drivers in the JDBC Adapters code/jars/ folder, remember to reload the adapter package to activate them.

When your algorithm is involving multiple calls, you can try to split these into several adapter services then.
Or you can implement the algorithm in a StoredProcedure and invoke this from corresponding AdapterServiceTemplate.

Regards,
Holger

1 Like
3

which version of wM are you running on?
Which version of JDBC Adapter are you using?

Product webMethods Integration Server
Version 10.7.0.0
Updates IS_10.7_Core_Fix3
IS_10.7_SPM_Fix1
Description webMethods Adapter for JDBC
Adapter Version 10.3
Updates JDBC_10.3_Fix13

Please do not use both jars together.
Depending on the wM version (and the Jave version it is running on) you will only need either the jre8 (for java version 8+) or the jre11 (for java version 11+) variant.

As I remove the extra driver from the package, the Java project gets marked as having errors and trying to launch it also throws the warning, although none of particular elements inside the project report an error. Reloading the package and restarting the IS does not get rid of it, and only putting the extra driver back removes the mark.
image

When your algorithm is involving multiple calls, you can try to split these into several adapter services then.

It’s not like that. When processing data that the original call returns (and using it as a source of parameters), subsequent calls may be needed. I’ve contacted the database team that might resolve the logic into a stored procedure, but so far I’m not convinced that their response will be positive, so I’m looking for alternate ways.

4

You can still split the adapter services even if you can’t convert the logic to a SP. As long as you don’t loop through a DB resultset and make subsequent calls to DB, performance wise speaking, it shouldn’t make a huge impact on your service calls.

I understand subsequent calls may not be needed as well. It certainly sounds like creating a Java service for DB calls is an overkill. I would implement this logic using flow services, and create multiple DB adapter services if they are needed. As long as you don’t loop through a result set and make subsequent calls to DB with that result set, it shouldn’t impact your performance too much.

2 Likes
5

Hi Kirill,

I agree with Engin.

For better understanding of your use case, please provide an outline of your algorithm in pseudo code.
From that we might be able to assist you in splitting up the statements and form a flow service outline.

Regards,
Holger

6

Hi Kirill,

regarding error mark for your project in Designer:
Please check the compile properties for project and set them to Java 1.8.

Even when Designer itself might be running with a higher Java version, you cannot compile the project with this version as IS 10.7 only understands Java 8. The Java version being used can be seen on the About page of IS.
Therefore the jre11 jar is not applicable here.

Regards,
Holger

7

Thanks, I cleaned the unused reference from properties. After that, with only single driver in the package, the connection worked.

As for the reason I chose Java Service over a Flow Service to implement the logic, it is not because subsequent calls were needed as such (it’s the other way around - I needed subsequent call from the Java because the code had to be in Java in the first place), but because the plain resultSet of the “master” call had to be reorganized into a multi-level hierarchical structure for output (and then the primary ID of certain records had to be replaced with results of subsequent SQL calls based on records’ contents) - something I really wouldn’t want to do with Flow “language”. I guess I might distribute various parts of the algorithm between Java and Flow components so that SQL calls would come from Flow parts… but I’ll be glad if I won’t have to.

Thank you both!

8

Not sure why you need to do this programatically. Can’t you use join at sql level? Are the subsequent calls supposed to be made to a different DB server?

If you use flow services you can use your DB adapter services as transformers and execute them in parallel. It will be a nightmare to implement it in a java service IMO. Using flow services doesn’t really degrade the performance (much). It effects the performance only if you create pipelines iteratively (in other terms if you loop through your result set and make external calls or if you do multiple string manipulations in each iteration). Do you have a TPS requirement? What is the reason avoiding the flow service?

1 Like
9

Java is only a nightmare without a secret ingredient :smiley: . Some time ago I wrote myself a helper that can analyze pipeline’s sig and generate Java classes for all input and output document types, with preset code to read and write into the pipeline (similarly to what stock “Generate code for implementing” option provides), so that only the data transformation itself needs to be done manually. It’s somewhat crude, and generated class names can get messy for complex structures, but the ability to manipulate data in any ways that Java allows is the reason I prefer it to flowservices for any serious task. And as I said above, this particular task does require some juggling with received data, additional SQL calls not being the bigger of inconveniences.

10

22 posts were split to a new topic: Transformers run concurrently or in parallel

11

An easier or a better way to do this in wM is to create a JDBC connection from IS to the database that you are connecting to and then use the JDBC adapter template ExecuteService. Call the Java service from this adapter service. Inside the Java service call below line of code and then you can work with the standard prepared statement in java to execute the query or a stored proc. In this way the DB connection and transaction management is managed by IS and not your code.

java.sql.Connection conn = (java.sql.Connection)IDataUtil.get(pipeline.getCursor(), "$db_service_connection");

2 Likes