Using JDBC driver from Java Service

, ,
1

Hi there…

Is there any one know how to connect to db (Oracle8i or Oracle9i) using JDBC from Java Service? I have a request to make a db transaction via java service.

Thanks before…

2

I think this has been covered in previous threads. Please search the forums.

I would encourage you to not follow this path, if at all possible. Connecting to a DB from a Java service is not well supported. It would be better to create a JDBC adapter service if that will do what you need. Then, if you must call that from a Java service you can use the Service.doInvoke method. (I would also question the real need for a Java service, but that’s another topic.)

3

Thank’s rob for your warn, but the decision we make to use the java service for connecting to db is to maintain our connection to db it self. My partner said is hard to maintain the connection to db using service adapter. But if you have another solution for our case it will be helpfull. And if you know what kind of problem i’ll face if i use java service to connect to db please tell me…

Thank’s… :slight_smile:

4

I am wondering, if your concern is to maintain the connection with DB then you can configure the JDBC Adapter Connection with Minimum Pool Size greater than zero (‘0’) which will make sure that these many number of connections are always maintained with the DB.

5

What feature/function are your trying to address with maintaining a DB connection? Perhaps if share the basics of what you need to do, we can offer approaches.

The JDBC adapter, which I assume you’re using (WmDB shouldn’t be used), provides for configuring a connection, as you’ve presumably done using IS Administrator. In that configuration, you configure the size of the connection pool. The pool will keep connections to the DB per that configuration (setting the min pool size above 0 is generally not a good idea).

There are a few items that you’ll need to address.

  1. Where to get the connection parameters. You’ll either need to use non-public methods to get configuration information from a WmDB connection or a JDBC adapter connection; or you’ll need to store the information in a file somewhere (not a big deal, but just another thing to manage)

  2. Where to store the connection(s) once you create them.

  3. How to manage multiple threads that will want to use the connection(s).

  4. How to clear/drop the connection when the DB has restarted or when the connection is stale (DB dropped the connection due to idle).

These things aren’t overly difficult but why reinvent the wheel? The JDBC adapter connection pool is most likely the right way to do what you need to achieve.

Again, if you can share what you need to do that led you to want to use Java and a persistent connection, then we can confirm that the JDBC adapter is the way to go and if not, we can help with alternatives.

6

How can be a custom DB pool easier to maintain that WM built-in connection pool? If you want to bypass WM connection pool you have to follow the java way using DataSource with configuration in your own file meaning the maintainance is done via xml files; if a change is required in connection configuration you have to edit this file and eventually restart the connection pool manager. Other way is to use a JNDI to lookup for DataSource, in this case you will have to follow JNDI GUI Management console to configure DataSource.

7

Hi Ioan…

The reason why I must using java to connect to db because i have to swith the destination database even the server address to run the query based on the parameter. It’s possible to solve my problem by making a lot of JDBC adapter based on each parameter, but it’s easier to us to put the data of connection parameter on the table, so we can easily maintain it when the changes occur. We face the problem because the system not integrated yet. If you have other suggestion for my case it will be helped me out…

Thank’s for your attention… :slight_smile:

8

It is good you think about maintenance, but you should not give up on some built in capabilities just to make it easy to maintain. And I am not sure if is going to be easier to have this functionality in java services. I used this approach to connect from java service directly to DB when I had to use some code that already was doing this and I just integrate in my flows.

Before you choose java service for DB connection, please check if WmDB is not suitable for your solution. WmDB let you get a connection and then you can pass around this connection to your flow service that executes the SQL. When you get the connection you can pass connection parameters like $dbAlias, $dbDriver etc …

Even a better solution would be JDBCAdapter, you have an option to pass $connectionName to your service adapter. Just add all your DB connections using JDBCAdpater console and then in your flow service decide what connectionName to use.

9

So the desire wasn’t to “maintain a connection with the DB” but to be able to dynamically select which DB to use for a given operation?

  • Define a JDBC adapter connection for each DB instance

  • In your config DB table, store the $connectionName to allow dynamic selection of the DB instance

  • When a service is to invoke a JDBC service, lookup the $connectionName from the DB using whatever DB selection logic you need to use. (You’ll use a static JDBC connection for this)

In this way, the adapters manage the connection pools on your behalf and you still get the dynamic selection of which DB at run-time.

Were you planning on implementing connection pooling in Java? If not, you’ll incur the overhead of establishing a connection for each query (DBAs really don’t like that). If so, were you planning on developing pool maintenance facilities? For example, clearing the pool on some types of errors (e.g. stale connections), dropping the pool when the configuration changes, etc.

When you say “lot of JDBC adapter based on each parameter” what exactly do you mean? Is it more than just the DB host name that will vary? How is managing that config in DB tables any easier than managing in IS Admin? It seems very much the same to me.

10

I’d strongly encourage avoiding both Java and WmDB for DB connectivity.

WmDB has many quirks. The only reason it is still available, IMO, is because when wM tried to deprecate it many customers complained loudly as they would be forced to update all of their existing code. IMO, new implementations should never use WmDB.

11

You don’t have to implement your pool connection, most of DBs JDBC DataSource implementations are coming with this feature. i.e. Oracle has oracle.jdbc.pool.OracleDataSource which takes care of what you are saying! You just have to provide to correct setup parameters, just like you do within JDBCAdpater admin console.

12

Good points.

Still not the direction I would advise, though.

13

I don’t disagree with you, but still I think is better WmDB then Java Service. As I said earlier, the best option is to use JDBCAdpater as is giving you better performance and maintainance is much easier.

14

Hi Rob, so do you mean I can change the connection name for every adapter service i have created? how can I do that from java service? can you give me an example? coz i can’f find it in the documentation.

Thank’s… :slight_smile:

15

Hi Rob…
Sorry for troubling you, i have find a way to change the connection name for the adapter service. I’ll tell my PM, hope he would accept my your suggestion to don’t use java to connect to DB. Thank’s again… :slight_smile:

16

By “change connection name” I assume you mean you’ve found that by specifying $connectionName at run-time you can indicate which connection the JDBC service is to use.

I ask because there is also a service that can change the JDBC service property to associate the service with another connection–you don’t want to be doing that at run-time.

17

yes Rob, by changing the $connectionName, I can change the connection for the specify adapter. But there’s also a function to change the connection name pub:art:service.setAdapterServiceNodeConnection (I think the one that you said to avoid), I just feel curious about this function, what’s for and how does it’s work.

Hi Ioan, thank’s for your information about how JDBC work again pooling, it’s something i worried if use direct connection from java to db.

Thanks all for your help. :slight_smile:

18

Each JDBC adapter service is associated with a specific connection. You specify which when you create the service. The connection name is stored in the node.ndf file of the service. Originally, once this connection was specified, it could never be changed.

Later releases allowed you to change the connection associated with the service. That is the setAdapterServiceNodeConnection service you found. It was intended to be used at design time, in case you needed to change the connection for some reason. This changes the node.ndf file. I suggested avoiding this at run-time because doing so would not be thread-safe (multiple threads trying to change the connection will collide, likely resulting in file access contention).

The latest releases added $connectionName as an input parameter to provide run-time flexibility.

19

Hi wMUsers,

I’m wondering about scenario when JDBC adapter services are called from Java code. Let’s assume that jdbc connection is configured with LOCAL_TRANSACTIION. How implicit transaction handling does look in such case? What about explicit handling?

just curious…

Thanks,
Holden