ESB trestrequest job writes to mssql in IDE but no... - Qlik Community

JasonMiller · ‎2020-09-22

I have a job based on trestrequest that receives data and writes to an mssql server. It works in the IDE on windows and in the IDE on the same centos server that hosts the runtime, but it doesn't work when built to a jar file and deployed to the runtime. When I run tail -f tesb.log and then issue a get request to my rest service via a browser, I can see in the log file it is receiving the call, but I don't see any new records in the database, and I don't see the word "success" returned to the browser like happens when I run in the ide at localhost:8090/service/etc. The browser stays blank, and I can't figure out where to find any error messages.

The localhost:8040/system/console bundles list shows these:

Microsoft JDBC Driver for SQL Servercom.microsoft.sqlserver.mssql-jdbc 7.2.2 Active

OPS4J Pax JDBC MSSQL Driver Adapterorg.ops4j.pax.jdbc.mssql 1.4.0 Active

I modified the job to use a tmssqlconnection, but I can't figure out if I need to use either of the options at the bottom "use or register a shared db connection" or "specify a data source alias".

I have issued some feature:install commands into the container client, but most of them failed with various errors like this so I gave up on that:

tadmin@trun()> feature:install tesb-datasource-sqlserver

Error executing command: Unable to resolve root: missing requirement [root] osgi.identity; osgi.identity=shell-compat; type=karaf.feature; version="[4.2.7,4.2.7]"; filter:="(&(osgi.identity=shell-compat)(type=karaf.feature)(version>=4.2.7)(version<=4.2.7))" [caused by: Unable to resolve shell-compat/4.2.7: missing requirement [shell-compat/4.2.7] osgi.identity; osgi.identity=org.apache.karaf.shell.console; type=osgi.fragment; version="[4.2.7,4.2.7]"; resolution:=mandatory [caused by: Fragment was not selected for attachment: org.apache.karaf.shell.console/4.2.7]]

I think the "pax" driver listed above is the result of one of these ^^ commands that I found. I also tried to change the job to using a jdbc driver instead, but I couldn't figure out how to establish that connection inside the IDE, so I gave up on that too. I could try again with that if necessary.

The container ssh "client" shell currently lists these three items when I run list | grep -i sql

323 x Active x 80 x 7.2.2 x Microsoft JDBC Driver for SQL Server

324 x Active x 80 x 1.4.0 x OPS4J Pax JDBC MSSQL Driver Adapter

327 x Active x 80 x 8.4.0 x Microsoft JDBC Driver for SQL Server

I think it's listing two from Microsoft because I copied the driver jar file into the deploy directory based on some documentation on installing drivers. So at this point I feel like I'm making a mess, and if someone could give me a pointer I'd appreciate it before I mess things up. I'm using Talend 7.3 ESB and the default container from the Runtime_ESESB directory running standalone. I feel like I have a few directions I could go, but if someone could direct me a bit so I don't to chase them all down one at a time by trial and error that would be great.

Thanks!

Anonymous · ‎2020-09-23

Hello,

Could you please clarify in which Talend version/edition you are? Is there any more error message in tesb.log?

Best regards

Sabrina

JasonMiller · ‎2020-09-24

7.3 ESB Community Edition. I deleted jtds-1.3.1.jar from container/deploy and then copied it back in, and it produced this content in tesb.log, but no error

This shows in the Karaf system console bundles list as a bundle called "jtds"

jtdsjtdsSymbolic NamejtdsVersion1.3.1Bundle Locationwrap:file:/opt/container/deploy/jtds-1.3.1.jar$Bundle-SymbolicName=jtds&Bundle-Version=1.3.1Last ModificationThu Sep 24 23:06:20 EDT 2020Start Level80Exported Packagesnet.sourceforge.jtds.jdbc,version=0.0.0

net.sourceforge.jtds.jdbc.cache,version=0.0.0

net.sourceforge.jtds.jdbcx,version=0.0.0

net.sourceforge.jtds.jdbcx.proxy,version=0.0.0

net.sourceforge.jtds.ssl,version=0.0.0

net.sourceforge.jtds.util,version=0.0.0Imported Packagesjavax.crypto,version=0.0.0 from org.eclipse.osgi (0)

javax.crypto.spec,version=0.0.0 from org.eclipse.osgi (0)

javax.naming,version=0.0.0 from org.eclipse.osgi (0)

javax.naming.spi,version=0.0.0 from org.eclipse.osgi (0)

javax.net,version=0.0.0 from org.eclipse.osgi (0)

javax.net.ssl,version=0.0.0 from org.eclipse.osgi (0)

javax.sql,version=0.0.0 from org.eclipse.osgi (0)

javax.transaction.xa,version=1.2.0 from javax.transaction-api (308) -- Overwritten by Boot Delegation

javax.transaction.xa,version=0.0.0 from org.eclipse.osgi (0) -- Overwritten by Boot Delegation

org.ietf.jgss,version=0.0.0 from org.eclipse.osgi (0)Service ID 424Types: org.osgi.service.jdbc.DataSourceFactoryManifest HeadersImplementation-URL: http://jtds.sourceforge.net

Bundle-Name: jtds

Manifest-Version: 1.0

Ant-Version: Apache Ant 1.8.2

Specification-Title: JDBC

Bnd-LastModified: 1601003180034

Bundle-SymbolicName: jtds

Implementation-Title: jTDS JDBC Driver

Import-Package: javax.crypto; resolution:=optional, javax.crypto.spec; resolution:=optional, javax.naming; resolution:=optional, javax.naming.spi; resolution:=optional, javax.net; resolution:=optional, javax.net.ssl; resolution:=optional, javax.sql; resolution:=optional, javax.transaction.xa; resolution:=optional, jcifs; resolution:=optional, jcifs.smb; resolution:=optional, org.ietf.jgss; resolution:=optional

Bundle-ManifestVersion: 2

Implementation-Version: 1.3.1

Main-Class: net.sourceforge.jtds.jdbc.Driver

Originally-Created-By: 1.7.0_21-b11 (Oracle Corporation)

Require-Capability: osgi.ee; filter:="(&(osgi.ee=JavaSE)(version=1.7))"

Export-Package: net.sourceforge.jtds.jdbc; uses:="javax.transaction.xa", net.sourceforge.jtds.jdbc.cache; uses:="net.sourceforge.jtds.jdbc", net.sourceforge.jtds.jdbcx; uses:="javax.naming, javax.naming.spi, javax.sql, javax.transaction.xa", net.sourceforge.jtds.jdbcx.proxy; uses:="net.sourceforge.jtds.jdbcx", net.sourceforge.jtds.ssl; uses:="javax.net", net.sourceforge.jtds.util

Bundle-Version: 1.3.1

Created-By: 1.8.0_262 (Oracle Corporation)

Specification-Version: 3.0

Tool: Bnd-2.3.0.201405100607

Generated-By-Ops4j-Pax-From: wrap:file:/opt/container/deploy/jtds-1.3.1.jar$Bundle-SymbolicName=jtds&Bundle-Version=1.3.1

JasonMiller · ‎2020-09-25

I think I found either a resolution or at least a workaround.. I followed the directions here https://community.talend.com/s/article/Best-practices-for-adding-custom-libraries-in-Talend-Jobs-routines-beans-and-routes-KY25Z, listed under "Add a library in a routine"

Basically create a new routine, right click the routine, and click "Edit Routine Libraries". This creates a stub function with kind of a hello world in it, and I'm not super happy about having to include that function in the build output. Then right click on the ESB job and click "Setup Routine Dependencies" and select the newly created routine. After build, the jtds-1.3.1.jar file will be included in the built job jar lib folder, and the services will behave correctly inside talend runtime.

David_Beaty · ‎2020-10-06

Hi,

As you're running this on the Runtime and to service REST requests, you might be better off installing the Karaf features JDBC and PAX-JDBC-MSSQL. Then you can create the Datasource connection that the runtime holds open and the job just connects to that.

This link and this one might help.

Thanks

ESB trestrequest job writes to mssql in IDE but not in osgi runtime

JDBC

REST

Sql Server Database

Talend Data Integration

Talend ESB

Talend Runtime

Talend Studio

v7.x