• Post Reply Bookmark Topic Watch Topic
  • New Topic

How do I call a stored procedure during server startup of Tomcat

 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I need to execute a SP on load of datasource setting during tomcat server statup which is similar to 'DataStoreHelper' configuration in WAS console.

http://stackoverflow.com/questions/20361473/how-to-setup-an-oracle-jdbc-data-source-on-websphere-application-server-7-step-b

Any help regarding this is much appreciated!!!


Thanks,
Vijay
 
Tim Holloway
Bartender
Posts: 18471
61
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why are you asking a WebSphere question in the Tomcat forum?

Tomcat doesn't have a WAS Console. The WAS documentation are for a completely different program than Tomcat - specifically, IBM's WebSphere Application Server.
 
Paul Clapham
Sheriff
Posts: 21976
36
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm interpreting the question as "I found out how to do this thing in Websphere, but how do I do it in Tomcat?" But just in case, I copied the thread into the Websphere forum as well.
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 65677
129
IntelliJ IDE Java jQuery Mac Mac OS X
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Server startup or application startup?

Server startup doesn't make much sense since different apps can use different databases.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Guys , I know Tomcat is not WebSphere.

I gave WAS as a reference , I am trying to find a similar mechanism which is provided by IBM in WAS i.e DataStoreHelper.I need to execute one SP only once during the server startup not 'Application'

Apologies for the confusion created.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham - you are right , I am looking for the solution in tomcat , one idea is implementing startup servlet...currently working on that , Please let me know if you find any simple configuration is available to achieve the same.
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 65677
129
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
vdammala vkumar wrote:Paul Clapham - you are right , I am looking for the solution in tomcat , one idea is implementing startup servlet...


A "startup servlet" is not going to be able to run outside of an application. And a "startup servlet" is not a great idea in any case; use a context listener.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok , sure...

Thanks for your suggestion.Much appreciated.
 
Tim Holloway
Bartender
Posts: 18471
61
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK. Can we clarify this?

A Tomcat DataStore (Connection Pool) is constructed when a webapp context that defines it is deployed. Or, in the case of shared pools, at server startup.

A ServletContextListener is too late in the lifecycle for this, since the application context shouldn't be assumed initialized before external resources are ready. The pool might have already started creating Connections.

More likely you want either A) a stored procedure to execute when a pool connection is first created or B) a stored procedure to execute whenever a connection is pulled from the pool and handed to a webapp.

So I need to know which of the above is the case. It would help to know what the stored procedure is supposed to do, though.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please find more details about the problem , contextListener approach looks fine to me.I just deployed it and it is working fine so far.

problem details


I have a StoredProcedure through which I will create "DBMS_ SESSION" variable , this variable is used further in triggers/views etc.

I am calling this SP from about five "RESTful" services which are currently deployed in tomcat along with another 10 services , all these 5 services are running fine for last 1month time but stopped couple of days ago due to unavailability of the variable
added in "DBMS" session.It works fine when I restarted the tomcat server or re-deploy the service as well.

so , I am trying to move the SP call from java layer to tomcat and make it global.

Please let me know if you need more details about the problem.

Thanks,
Vijay

 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am fine with any approach which makes the DBMS session variable persistant
 
Tim Holloway
Bartender
Posts: 18471
61
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
WHen you say "DBMS_SESSION" variable, do you mean that this stored procedure produces and returns some sort of session ID used by the triggers and stuff?

And is it REALLY going to be the same session for everyone using the web application? If not, you probably want to invoke the stored procedure in a HttpSession listener and store its value in the user's HttpSession.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is not application(java) or user session , it is a database session variable used across multiple triggers and views used in those five webservices.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
EX:

DBMS_SESSION.SET_CONTEXT('context_name','variable_name', "variable_value");
 
Tim Holloway
Bartender
Posts: 18471
61
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, I don't understand that, but I think probably what I'd do is subclass whatever database connection pool class you're using and add the SP call to an override to its initialization code.

If only a single webapp was using this feature, I'd make it more portable by doing the SP call in its ServletContextListener and storing the result in Application scope.

Sorry I can't give a cleaner solution, but I'm just not understanding how all this fits.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This started failing again...ServletContextListener approach suggested by Bear is not helping.
 
Tim Holloway
Bartender
Posts: 18471
61
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Two thoughts come to mind,

First, any sufficiently complex solution to a problem is probably the wrong one. To quote Einstein: Everything should be as simple as possible - but no simpler.

The second relates to the first. It's really beginning to sound like you solved a problem, but the solution itself is a (possibly bigger) problem than the original problem. We get a lot of that. Maybe it's time we understood what the actual business goal was.

One thing that has troubled me all along is that you're trying to condition an entire database when a connection pool is established on a single web server. That sounds like an awfully fragile thing to do and not something I'd normally even consider doing at that level.
 
vdammala vkumar
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We create one SQL function and calling the stored procedure inside that function.

That particular function has been configured in context.xml like this

validationQuery="select FUNC_NAME from dual"

We are planning to apply this change to production tomcat servers , kindly let me know if you foresee any issues in doing this.
 
Tim Holloway
Bartender
Posts: 18471
61
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The validationQuery is made whenever you request a Connection from the pool. Its purpose is to ensure that Oracle hasn't timed out and closed that connection.

If a group of Connections has timed out, then the query will be made once for each connection tested until either a still-open connection has been found (query succeeds) or the number of available Connections in the pool is less than the low-water limit causing a new Connection(s) to be built from scratch. And you can reasonably expect that any or all new Connections being built will also have had the validation Query run against them.

Therefore, a validation Query should ideally have 2 characteristics:

First, it should be low-overhead. Since it's going to get called a lot.

Secondly, it should be idempotent. That is, the 5991st call shouldn't expect to find anything that the first call didn't. Because you have no way of predicting when or how often the query will be made,
 
my overalls have superpowers - they repel people who think fashion is important. Tiny ad:
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!