Database Element and Error handling

[dirk.fidorski]

Hi Team,

I am wondering what is the best way to build a scenario requesting a MS SQL Server and when its down, continue in the Flow doing some Standard routing.

I know this has been discussed here some times, years ago, building a custom class extending DB Element by Exception Handling, but now we have Event Handling in CallStudio, where I expected there "must be an easier way" in 11.5 Release...

My Main Questions:

1) Database Element does not have a timeout exit State, where do I configure the timeout for a JNDI request to MS SQL Server? In Context XML?

2) What I would expect the database element does would be:

- I add Java Exception as an Event (AudiumException)to the DB Element, drag this new exit state to a CVP Subdialog return giving back the values I basically want to return to ICM in DB-down scenarios and continue in the Flow... Leaving success exit in ICM... and all good.

What I see it actually does is the following:

- It stops, does not write Error logs, App logs just basically stop after the DB Element, and CVP hangs up.

- If I remove my CVP-Subdialog Start Element in my Flow, then I get an Error log telling me AudiumException has happened, and If I handle it by an Event as explained above, then i would expect it leaves the Success-exit of my RunExtScript in ICM, but no, It leaves the Error Exit... However at least it seems to go back to ICM this way...somehow...

3) Why does removing CVP Subdialog Start at least allow "some kind of catching exception" and when its there at the beginning of my script , then I cannot catch anything?!?

Is all this "normal" behavior?

How is handling database down scenarios (including defineable timeouts) done the best way?

Thanks

Regards

Dirk

[janinegraves]

1.You can catch java exceptions in the Events tab of the Database

Element beginning in CVP 10.5

2.Just go into your DB element's Events tab.

A) Select Add (in the middle)

B) At the top, highlight New_Event_1

C)Then at the bottom, configure a Name (this becomes the label of the

new exit state, spaces aren't allowed). For example name it JavaExc

D) and specify your Event List of java exceptions. Note you can use .

(or java.lang.Exception - case sensitive) to catch ALL java exceptions.

Or you can comma separate a list of exceptions (no spaces allowed!)

3.Now any java exception will continue down the new exit state named

JavaExc and you can do whatever you'd like in the Studio application.

[dirk.fidorski]

Hi Janine,

thanks a lot for your reply, really appreciate!

Your above answer tells me it "should" work exactly as I thought, basically what you write above is exactly what i did.

I did some more troubleshooting, and I guess I need to move this thread into a different direction.

I did some more tests and recongized when catching java.lang.Exception it works absolutely fine continuing in CVP script, leaving the JavaExc exit and so on, if I for Example simulate a DB-Error by typing a JNDI name, that doesn't exist in my Database Element.

So the CVP Script seems (and always seemed) to be fine. App logs look like that:

Database,enter,

Database,exit,JavaExc

Return false,enter,

Return false,exit,

end,how,app_session_complete

end,result,normal

end,duration,0

However if I put the proper JNDI name and shut down the DB server (so its no longer reachable via ping) then it does not behave like that. It gives me around 12-15s of silence, and after that my call gets terminated, not going back to ICM at all.

App logs look like that:

Database,enter,

end,how,disconnect

end,result,normal

end,duration,12

My context.xml looks like that:

<Resource name="jdbc/ResName"

auth="Container"

type="javax.sql.DataSource"

driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"

url="jdbc:sqlserver://XXX.XXX.XXX.XXX:1433;databaseName=dbname;user=sa;password=mypw"

/>

I connect to a SQL Server 2014 and am using "sqljdbc41.jar" from sqljdbc_6.0.8112.100_enu.exe

any thoughts where I can "continue" troubleshooting? DB connection itself works fine, as long as the Server is reachable...

Did I miss something simple somewhere?

Regards

Dirk

[janinegraves]

A few things you should be aware of:

1. Starting in Studio 10.5 you must go to

Project/Properties/CallStudio/General Settings and change the Subdialog

setting to 'true' (instead of 'false').

There's never a case where it needs to be set to 'false' and when it's

false it causes unhandled exceptions to disconnect the call.

This setting ONLY comes into play when an unhandled exception occurs.

If set to true, unhandled exceptions will return to ICM down the error

path of the RunExtScript node.

2. Whenever the VXML Gateway makes an http request to VXMLServer, the

gateway waits a small amount of time (10s default) for VXML Server to

send it more vxml to continue the call flow. If the gateway doesn't

receive anything, it throws a VoiceXML Event named error.badfetch which

returns back to VXMLServer. I suspect this is what you're experiencing

when the system disconnects.

If you add some event handling for the VXML Event named error.badfetch

to the application, I bet that your call flow will follow that path.

However, you can NOT catch VXML Events in a Database element.

But you CAN catch the VoiceXML Event in the Start of Call element's

Events tab.

Select VXML Event and configure either error.badfetch or . (to catch

all vxml events).

This will allow you to return the callflow and data back to ICM.

FYI, you can configure this 10s teimout in the VXML Gateway using 'http

client response timeout 10'

(where 10 can be replaced by any number of seconds. Cisco's default is 10).

3. I'm not positive how to configure the amount of time the VXML Server

allots trying to connect to the DB?

If you google Tomcat JNDI MSSQL you'll find lots of optional attributes

that can be configured in the context.xml - I'm not positive which one

affects the timeout. And be careful because some are in seconds and

others are in milliseconds!

There's also the possibility that it's configured in the jdbc driver -

so you may have to search around for how to do that.

Hope this helps. Please let us know which of these fixes your final issue.

[dirk.fidorski]

Hi Janine,

again thanks for your help!!

Ok, set SubDialog to true in properties, makes Sense to me.

Also most probably the 10s VXML timeout is happening ->  however, I feel 10s is ok, I just somehow need to get the DB timeout lower than that...

So I am struggling with your number 3.

I have tested a whole bunch of settings (from google) to achieve jdbc timeout, but for now without any success (tried microsoft jdbc and jTDS with lot of parameters).

If there is anybody out there, that has done this before an could point me to a working example of context xml with proper values lets say for a 5s timeout for a query to MS SQL that would be great!

Thx

Dirk

[janinegraves · Accepted solution]

FYI, in looking around on StackOverflow, it looks like it's the JDBC

driver that doesn't support determining if there's a network connection

to the server where the DB is running. It talks about a 15 second timeout...

From this URL:

http://stackoverflow.com/questions/35439885/jdbc-microsoft-sql-timeout-not-fired:

"MS JDBC driver looks like it supports query timeout, but it must have

connection to server first.

So you cannot use it to detect network failure.

Temporary solution is to start connection again before each query.

This has default timeout of 15 seconds which I didn't manage to change"

[janinegraves]

Here's a workaround until you figure out how to set a timeout to determine if the server is down.

Prior to the DB element, use a RestClient element to make an HTTP request of just http://ipAddressOfDbServer

Configure the connection timeout to whatever timeout you want.

In the Rest Client's events tab, catch the java exception named java.net.SocketTimeoutException

If the DB server is down the app will follow the JavaExc path of the Rest Client.

If the DB server is up the app will follow the done path of the Rest Client.

It'll probably be returning an error message, but if you get anything back, then the server is up.

I just tested it and it seemed to work pretty well.

[dirk.fidorski]

Thanks again Janine,

I have done some more testing and it works with the MS JDBC driver and additional Parameters in Context.xml / JDBC-URL like: loginTimeout. I am not yet 100% happy, as it seems it does not work the first time when a DB just gets down, but however, it works ob subsequent tries...

(This was a main Issue for me, as most of times I only tested once and considered it as non-working )

I'll for sure do some more investigations, but for now i'll consider this as solved thanks to your support!

Regards

Dirk

[janinegraves]

so the first time the MSSQL is down, you're having to wait 15seconds?

You might consider using the Rest Client element workaround, as you can

configure a timeout there.