GAD database query

[valeriu.craciun]

Hi guys,

Anyone who made database queries directly from GAD? I have to do it, because the query contains the target agent and it cannot be done before sending the call to the agent. It would be really helpful to know your ideas and thoughts about it!

Kind regards,
Valeriu

[cavagnaro]

what do you mean?? GAD is agent softphone interface only...if I understand you correctly what you want is to get the target agent from a DB and then route to this agent. If so you have to do this on your strategy and it is totally doable, I have done that several times.
Also would make absolutely no sense to get the agent on GAD because it is already on a agent...what he would do? Transfer to that agent??? You will have to do a custom tab that will show the agent queried from the DB, but again, no sense at all

[valeriu.craciun]

Hi
I have a different problem: after the call has reached the agent on its GAD instance, I need to query an external DB (through a stored procedure) to automate some of the agent action, using agent name (RTargetAgentSelected) and some other attached data that comes on the call.
I can call the same stored procedure from the routing strategy, but unfortunately I do not the agent that actually tooked the call until I have reached the agent that has the call connected. Any other way to do that?

Regards,
Valeriu

[valeriu.craciun]

The need of the database query is obtain an OTP ( One-Time-Password) generated by calling a stored procedure with IN parameters: GAD user, ConnectionID and some attached data that comes with the call. After obtaining this OTP (string) I need to pop-up a new browser window with a custom url that contains the user and otp in it, directly from GAD.
I know how to create a custom jsp, which can be executed on specific GAD actions( like call answered, call ringing etc. ), but the problem remains with the DB query.

Regards,
Valeriu

[cavagnaro]

So your issue is how to do a DB query from your JSP page?

[valeriu.craciun]

Well, yes. But the actual problem is how to do so without affecting GAD deployment in the tomcat, since it doesn't quite keeps the standard J2EE deployment descriptors and deploy type. It doesn't have the WEB-INF ( to create a web.xml to define an application datasource) and classes/lib directory ( to drop in a ojdbc6.jar for Oracle database interrogation ).

So the problem is has anyone else done this and can give some hints about it?

Regards,
Valeriu

[cavagnaro]

Well maybe on incoming interaction you can create a custom tab that will execute everything you need and then also do the DB query as needed and then open a new browser instance outside GAD.

[René]

Hi Valeriu,

I did described customization several times without any major issues. Database connection was retrieved by custom JSP page running in GDesktop's tab from DB Connection Pool defined on GDesktop's Tomcat instance.

R.

[fnunezsa]

Actually you can do it within your strategy if you use the SelectDN function instead of target block and:
1) parse the output of the SelectDN function to retrieve the actual targeted Agent
2) run your DB query to retrieve your OTP
3) use the TRoute function to route the call to the target agent in step 1

You should keep in mind that your transition_time setting should allow for DB query execution without timing out or you will end up having the same agent targeted for more than one consecutive calls

[valeriu.craciun]

Hi Rene,

Can you please describe a little bit the process? How did you defined a DB connection pool on the GDesktop's Tomcat?

Regards,
Valeriu

[valeriu.craciun]

Hi fnunezsa,

I thought of it too, but it has more implications:
* the agents are part of an Agent Group/Virtual Agent Group already and I cannot target just one of them, I must preserve the current routing rules and timeouts on targets.
* also what happens if RONA occurs when targeting one subscriber, I must send the call back on target ( if it is possible ) and then make a new database query for the newly returned agent?

I have thought of it and the logical conclusion was to call the database procedure just once, if the agent has the call connected, but if it is not possible I would look for a routing solution.

Regards,
Valeriu

[René]

Hi Valeriu,

Pool of db connections is defined in context.xml file stored in webapps/gdesktop/META-INF indirectory. Sample context file is shown below

[code] <?xml version="1.0" encoding="UTF-8" ?>
- <Context docBase="/gdesktop" path="/gdesktop" reloadable="true">
  <Resource auth="Container" driverClassName="net.sourceforge.jtds.jdbc.Driver" maxActive="30" maxIdle="5" maxWait="5000" name="<resource name>" type="javax.sql.DataSource" url="jdbc:jtds:sqlserver://<dbserver>:<port>/<database name>" username="<username>" password="<password>" removeAbandoned="true" removeAbandonedTimeout="60" />
  </Context>[/code]

Following Java code is used in custom JSP page to retrieve connection from the pool and return it back.

[code]DataSource ds = null;
Connection conn = null;

try {
    Context initCtx = new InitialContext();
    Context envCtx = (Context) initCtx.lookup("java:comp/env");
    ds = (DataSource)envCtx.lookup("<resource name>");
}
catch (javax.naming.NamingException jex) {
    errorMsg += "Cannot get resource\n";
    errorMsg += jex.getMessage();
}

if (ds != null && errorMsg.length() == 0)
{
    try{
        conn = ds.getConnection();
...
[/code]

R.

[valeriu.craciun]

Hi Rene,

Thanks for the info. I think there is an additional modification to be done in the webapps/gdesktop/WEB-INF/web.xml file:
...
    <resource-ref>
        <description>Some description </description>
        <res-ref-name><resource name></res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
... 

My problem was to add the META-INF directory, but I think it can be solved by modifying the global content.xml ( and adding the resource in there as decribed by you ) which is located in $CATALINA_HOME/conf/content.xml ( CATALINA_HOME=tomcat root drectory ). Also you need to drop in the jar file for the jdbc driver within the $CATALINA_HOME/lib in order for the DBCP classloader to load it.

Thank you very much for the solution proposed I will provide implementation details after I finish it.

Regards,
Valeriu

[René]

Hi Valeriu,

You're right. Sorry, I forgot about this piece.

R.

[valeriu.craciun]

Hi,

I am almost ready, but I have one more little problem, I couldn't find the exact event to match my need - when the user answers the call.
Right now is on javascript-onaddinteraction as showing, which is happening as soon as the call arrives on AgentDesktop of the agent:
<gcn-resources>
    <desktop>
    <javascript-onaddinteraction>
      <![CDATA[
                document.forms.customPage.elements.userName.value = userName;
                document.forms.customPage.elements.idInteraction.value = idInteraction;
                document.forms.customPage.submit();
            ]]>
    </javascript-onaddinteraction>
    <html-body>
            <![CDATA[
                <form name="customPage" target="_blank" method="get"
                      action="custom/customPage.jsp">
                    <input type="hidden" name="userName"/>
                    <input type="hidden" name="idInteraction"/>
                </form>
            ]]>
        </html-body>
    </desktop>
</gcn-resources>

..and also it is a locking action, since no other pop-up or other can be executed unless the first is closed.
Using <javascript-oninteractionstatuschange> I couldn't match the exact answered call or some unique action, but it had the advantage of multiple execution of the jsp file in the same time- multiple pop-up windows in the same time.

Some hints of the possible actions, that I can relate my jsp executions and a non-locking one?

Regards,
Valeriu