topic Re: Datasource and Web service in Talend Studio

Datasource and Web service

SncJt — Sat, 16 Nov 2024 04:05:14 GMT

Hi there,

There is a thing I would like to be sure, because it is not so clear on the documentation.

Here is the documentation :

https://help.talend.com/reader/P98mtIVGCSoGmSITOuxPTw/TSNegZNcYUVGqIlO5M3QGw

I have a job, which is a WebService (using a tRestRequest in entrance, tRestResponse in answer, etc...).

This job runs on our runtime (Karaf).

I have DB connections in this job, and I have checked the "Specify a datasource alias", with an alias.

The datasource exists and is well defined on Karaf.

On the documentation, it is written this :

This section introduces the use of connection pooling in Jobs with tRouteInput component, which can be started from a Route using the cTalendJob component. Similar use cases and configurations can also be used with ESB data services.

Does my Web service will use my data source ? (if everything is well configured of course)

Because it is written that the data source is use with the Routes (tRouteInput) only.

Thanks a lot for your answer

Re: Datasource and Web service

Anonymous — Thu, 21 Nov 2019 22:24:41 GMT

The "Datasource Alias" will work in Talend Runtime. However, it will not work in the Studio, So when you are using this you will need to make sure the alias is set and any Studio test configurations are removed before running it in the Runtime. The "Specify a datasource alias" option is described here for Oracle. It works in the same way for all DBs.

Re: Datasource and Web service

Anonymous — Wed, 04 Dec 2019 09:55:12 GMT

Hi, i also have deployed a trestRequest job, i have subjobs for logging that connect to mysql DB with mariadb-java-client-1.8.0.jar driver.

I'm trying to add connection pooling. I've defined and deployed ds:

<?xml version="1.0" encoding="UTF-8"?>
<blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0"
default-activation="lazy">

<bean id="mysqlDataSource" class="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource">
<property name="url" value="jdbc:xxx"/>
<property name="user" value="xxx"/>
<property name="password" value="xxx"/>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.datasources.SharedPoolDataSource" destroy-method="close">
<property name="connectionPoolDataSource" ref="mysqlDataSource"/>
<property name="maxActive" value="20"/>
<property name="maxIdle" value="5"/>
<property name="maxWait" value="-1"/>
</bean>

<service ref="dataSource" interface="javax.sql.DataSource">
<service-properties>
<entry key="osgi.jndi.service.name" value="ds-mysql"/>
</service-properties>
</service>
</blueprint>

I flag "Specify a data source alias" and named it "ds-mysql"

When I try to call my ws I receive this error:

java.lang.RuntimeException: Child job running failed.
java.lang.RuntimeException: Driver Class should not be empty, please set it

It seems that the flag "Specify a data source alias" is completely ignored.

What i am missing?

Thanks

Re: Datasource and Web service

Anonymous — Wed, 04 Dec 2019 10:05:02 GMT

Where are you running your service? In the Studio or in the Runtime?

Re: Datasource and Web service

Anonymous — Wed, 04 Dec 2019 10:23:06 GMT

I deployed the jar in test environment.

btw if i run "service:list DataSource" it seems to be ok.

[javax.sql.DataSource]
----------------------
service.id = 385
osgi.jndi.service.name = ds-mysql
osgi.service.blueprint.compname = dataSource
service.bundleid = 286
service.scope = bundle
Provided by :
Bundle 286

Re: Datasource and Web service

Anonymous — Wed, 04 Dec 2019 14:53:49 GMT

From what you have shown, this should be working. Can you share a screenshot of a service that is not working. Also can you share the DB config for that job?

Re: Datasource and Web service

Anonymous — Wed, 04 Dec 2019 15:07:10 GMT

Hi,

I've posted the xml bean with DB config and the error in the previous message.

below you find the screen of the option i set to use a ds alias in the dbconnection component, i also try the same config in a tdboutput with the connection option "use this component", i also try to remove all context params (JDBC URL, Driver Class, User Id, Password) that i normally use in TOS ide

tDBConn.JPG

Re: Datasource and Web service

Anonymous — Wed, 04 Dec 2019 15:44:05 GMT

Can you show how your service is built? I cannot see any issues so far. I may have to recreate this. So can you tell me what version you are using so that I can recreate this as closely as possible? If you are using a subscription version, it might be better for you to speak to support.

Re: Datasource and Web service

Anonymous — Wed, 04 Dec 2019 16:00:15 GMT

Hi,

thank you for your patience, this is my main job, but i don't think it can help you:

here's a subjob that reads from db:

every log subjob writes some data...

I'm using TOS for ESB 7.1.1.20181026_1147

I try to recap what's going on:

the service is working fine if i don't use db-alias flag but use instead a cfg file with db info.
If i try to use datasource alias flag, (I clear all db parameters in components to be sure that the info is taken from xml ds), than i get this error "Driver Class should not be empty, please set it" just as the flag is not set.

Re: Datasource and Web service

Anonymous — Wed, 04 Dec 2019 16:50:58 GMT

Thanks for this. I will try and recreate it. However, in the meantime you might be able to try something. Can you write a quick service to use a DB component without a tRunJob. So just a simple service to return a value from a database with all of the components running in the service and not in a tRunJob. That *could* be it.

I'll need to set up a MySQL DB and replicate your type of job. I'll get on to this as soon as I am through a few meetings. I'll probably be able to respond by tomorrow.

Re: Datasource and Web service

Anonymous — Wed, 04 Dec 2019 17:03:12 GMT

Hi,

thank you for you time, here's what i did:

and here's what i got:

Re: Datasource and Web service

Anonymous — Wed, 04 Dec 2019 23:11:02 GMT

I *think* I might have figured your issue. What version of MySQL are you using? You say you are using the mariadb-java-client-1.8.0.jar driver. Is this because you are using MariaDB? If so, your blueprint is likely wrong. I cannot test this I'm afraid. If you are using MySQL then you need to know which version you are using. If you are using v5 then you need to run the following command in your runtime....

bundle:install mvn:mysql/mysql-connector-java/5.1.18

....then restart all relevant bundles. This should either fix your issue or give you a different error.

If you are using MariaDB, you will need to change your blueprint.

My *guess* at what this should be is something like below....

<?xml version="1.0" encoding="UTF-8"?>
<blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0"
default-activation="lazy">

<bean id="mysqlDataSource" class="org.mariadb.jdbc.MariaDbDataSource">
<property name="url" value="jdbc:xxx"/>
<property name="user" value="xxx"/>
<property name="password" value="xxx"/>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.datasources.SharedPoolDataSource" destroy-method="close">
<property name="connectionPoolDataSource" ref="mysqlDataSource"/>
<property name="maxActive" value="20"/>
<property name="maxIdle" value="5"/>
<property name="maxWait" value="-1"/>
</bean>

<service ref="dataSource" interface="javax.sql.DataSource">
<service-properties>
<entry key="osgi.jndi.service.name" value="ds-mysql"/>
</service-properties>
</service>
</blueprint>

As I said, I cannot test this myself.

Can you give whichever of these suggestions that apply a go, then let me know how you got on?

Re: Datasource and Web service

Anonymous — Thu, 05 Dec 2019 08:41:03 GMT

Hi,

I try to change my bean in:

<bean id="mysqlDataSource" class="org.mariadb.jdbc.MariaDbDataSource">

to be sure i also run this:

bundle:install mvn:org.mariadb.jdbc/mariadb-java-client/2.5.2

I got always the same result:

Exception in component tDBInput_1 (TestDs)
java.lang.RuntimeException: Driver Class should not be empty, please set it

Re: Datasource and Web service

Anonymous — Thu, 05 Dec 2019 09:24:21 GMT

Quick update:

I try to add an oracle DS, installing ojdbc8-12.2.0.1.jar and configuring a

   <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close">

(I used a sample xml from ..tesb\add-ons\datasources\dataservice)

i change my test job to connect to an oracle db, i check if the datasource is visible and it's fine, but i got the same error:

Re: Datasource and Web service

Anonymous — Thu, 05 Dec 2019 14:02:33 GMT

Other update, i create and build a test job in TOS 5.6.3, deployed on the same server, it works!

I believe that's a bug of 7.1.1 that completely ignores "specify ds alias" flag.

The question now is : can i "override" the flag by setting it by code?

Re: Datasource and Web service

Anonymous — Thu, 05 Dec 2019 17:00:52 GMT

Sorry about the delay in getting back to you. I've been quite busy today, but I have been investigating this when I have had the chance. I cannot reproduce this error in v7.1 or v7.2. I have set up an ESB Runtime for both v7.1 and v7.2 and have written services (tRestRequest--->tFlowToIterate--->tDBInput--->tXMLMap---->tRestResponse) in both environments. My DB was a v8 MySQL. While I did get other issues (authentication issues usually associated with MySQL and using the root user), I could not recreate yours. In fact I was able to get the services running pretty quickly once I'd figured out my root user issue.

As such I do not believe this to be a bug. What it *could* be is an issue with the service when it was built or (more likely) an issue with the tDBInput component. There are occasions when components have issues like this and all it usually takes is a component replacement with in the job/service.

I am using a Mac and believe I have given you all other info other than the version of Java I am using. This is v1.8.0_191-b12.

Sorry I haven't been able to help so far, but maybe one of my suggestions in this post might help?

Re: Datasource and Web service

Anonymous — Fri, 06 Dec 2019 09:31:11 GMT

Thanks for your support,

these are my environment specs:

Talend 7.1.1 -> JOB DOESN'T USE DATASOURCE (Java : zulu8.0.232)
(java.lang.RuntimeException: Driver Class should not be empty, please set it)

Talend 5.6.3 -> JOB USES DATASOURCE (Java : 1.7.0_79)

OS : Windows 7

When i build my RestService i only rightclick the job -> Build Job -> OSGI Bundle For ESB

I tried to recreate new test services taking a new tDBInput from palette... with no success.
I think i'm throwing in the towel.

Re: Datasource and Web service

Anonymous — Fri, 06 Dec 2019 10:49:43 GMT

Interesting. The only difference between our setups looks like Zulu...ignoring the operating systems. I guess that *could* be the reason I cannot reproduce this. I would recommend trying v7.2 or maybe waiting for 7.3. I know there are still a few bugs in the v7.3 M3 version which is out for public testing.

This is really frustrating as if I could reproduce it I could either help you figure it out from my end or raise it as a bug if there was no way around. But since I cannot even reproduce it, I can't do either. However, if you are willing to go through the steps, you can raise this as a potential bug here https://jira.talendforge.org/