01-31-2013 05:00 AM - edited 03-14-2019 11:11 AM
Hi,
I have an HA setup with UCCX 8.5.1 SU2, after we do a restart for what ever reason the system does not seem to read the custom jar file which does stored procedure calls to an oracle database. I have to restart engine, and or servers multiple times before this will re-establish. I am uprading to SU4 here very soon, but see no bugs after SU2 listed for this type problem. Any help would be appreciated.
Thanks
John
01-31-2013 06:02 AM
Hi,
can you check the catalina.out file - do you see any exceptions within the timeframe of restarting UCCX?
Also, is there any specific reason you use a custom JAR instead of talking to the Oracle DB server directly (just curious)?
G.
01-31-2013 06:59 AM
Hi we had some exceptions, but nothing specific in the catalina/tomcat logs for that timeframe..The customized java code is written to make a call to stored functions in oracle database through JDBC connection. The stored functions are a series of queries whose results are based on a previous query...
01-31-2013 07:03 AM
Hi,
it's kind of difficult to troubleshoot without actually seeing the custom jar contents. Anyway, did you check the other side? Does the UCCX try to contact the Oracle DB server. Actually, how do you know that it does not work?
G.
01-31-2013 10:42 AM
Attached is the custom file, I only know its not working from testing it, I cannot verify it is even trying to work. The return on a known good account is simply no data or basically a bad response.. My dba's say it is not possible/feasible to determine whether the uccx oracle database user is trying to logon or not..
01-31-2013 10:47 AM
Hi,
that's a very interesting response from a DBA
Anyway, I will try to decompile/reveng your custom JAR (it's only two classes), will try to figure it out.
G.
01-31-2013 11:16 AM
Hi,
well, I can't think of any good reason why you need this at all. I mean, it's a simple stored procedure being called, so nothing special - I was expecting some complicated calculations in Java. But all your custom code does is this: reads a string named callerinput, and calls a function which in turn returns one number (Float type).
How much did your company pay for this?
Anyway, a few things to consider:
1. A connection is created each and every time this is called. And I see they are not closed (what I'm missing is conn.close(), I would proably insert a finally block, and close the connection there). Not nice, eats up resources slowly.
2. A function can be called within a SELECT statement - so unless you have an unsupported version of Oracle, you don't need such "scaffolding".
3. The JDBC URL is "jdbc:oracle:thin:@sdorapsardev01:1521:ardev91", sdorapsardev01 is the server hostname - are you sure it's resolvable all the time? Would be safer to either use FQDN's or IP addresses.
So in my humble opinion, you may want to talk to whoever manages your database and ask him/her the following: create a view that calls this function. Then integrate UCCX the standard way - this would give you safer access, like pooled connections. And also, you'll be able to use the standard DB Read/Get/Release steps.
G.
01-31-2013 02:14 PM
Gergely - Just in case you don't get your input rated - +5
01-31-2013 11:30 PM
Aaron, thanks, I really appreciate it.
cowherjohn, I don't have a working instance of an Oracle DB right now, but will take a look around later. It would be a pity to leave it like that when an easier and more elegant solution is available.
G.
02-01-2013 05:57 AM
Gergely, I thank you for your responses, the Oracle pieces are beyond my experice and I will be working with my DBA and application staff to see if we can manage to rework the access for my UCCX script, thank you again for your input. That being said the intergration/access we have works consistently all the time, the only problem is when the servers are booted effectively going off the network, at that point the queries do not work without restarting several times. This is the problem I see and there are previous similar bugs in the uccx : CSCtk10613 Bug Details CRS Engine not reading custom jar files. This was the reasons for building the way we did :
"To create view and using standard DB read /get/release steps from uccx was the origional idea. But your DBA said she does not know how to do this. We discussed the possibility to call stored procedure from uccx before the java approach. And uccx can only read the return from a temporary table called "view". At that time admin said that is not possible within the time line. Yes if you can have your dba figure out how to return the result the stored procedure to view, then that will work also."
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide