Question about database integration from Call Studio

[rpeddi29]

hi ,

I am new to this Cisco Unified call studio. I have a question on how to integrate with database. Like as per doc, it seems we should provide a JNDI name for DB connection but not clear on how or where to create the required JNDI to connect to a sql server? I have call studio 11.5.1.

Can anyone help me on this? and also, how do we get session ucid ?

Thanks

[janinegraves]

1. Download the Sql Server JDBC driver and put that under

C:\cisco\cvp\vxmlservere\tomcat\lib\

2. Modify C:\cisco\cvp\vxmlservere\tomcat\conf\context.xml

<Resource name="jdbc/customer" auth="Container"

type="javax.sql.DataSource"

factory=”org.apache.tomcat.jdbc.pool.DataSourceFactory”

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

url="jdbc:sqlserver://hostname:1433;user=someone;password=someone;DatabaseName=yourDbName"

/>

3. Restart VXML Server

4. In studio use the JNDI name that appears after the *jdbc/ *of the

name attribute of the context.xml file.

For example, in the above example, the JNDI name is customer

[rpeddi29]

Thanks Janine Graves for the quick response .

BUt Issue is I just have the call studio and from call studio , want to test the DB integration..Currently we Don't have CVP.

Not sure about this path: C:\cisco\cvp\vxmlserver\tomcat\lib\. ..How do we get this path?Should call studio be installed in vxml server and is there specific installer to set up vxml server? Is it set up as part of CVP installation. Is there a way we test the DB ntegration only using call studio?

[rpeddi29]

Can anyone please respond to my queries above?

[janinegraves]

1. install the jdbc driver into C:\Cisco\CallStudio\eclipse\plugins\com.audiumcorp.studio.debug.runtime\CATALINA_HOME\lib

and

2. modify the context.xml file in C:\Cisco\CallStudio\eclipse\plugins\com.audiumcorp.studio.debug.runtime\CATALINA_HOME\conf

3. Then in studio, use the DB element and specify the jndi name as configured in the context.xml file after the slash in the attribute called name

<Resource name="jdbc/yourJndiName" auth="Container"

type="javax.sql.DataSource"

factory=”org.apache.tomcat.jdbc.pool.DataSourceFactory”

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

url="jdbc:sqlserver://hostname:1433;user=someone;password=someone;DatabaseName=yourDbName" />

4. Ensure that you've set the permissions correctly on the SqlServer.

5. Once you save the program, you can right-click the appname and select Debug Call Studio Project.

6. In the  bottom right of the Debug Perspective, press Connect to make a simulated call. It should connect to the database if you've configured it right.
If you get an error message then go to C:\Cisco\CallStudio\eclipse\plugins\com.audiumcorp.studio.debug.runtime\AUDIUM_HOME\applications\appname\logs and look at the logs for help.

[piyush aghera]

**** update:  never mind, I got this working.. resource names were entered in wrong file. *********

I followed exact same method as above to debug Database application in call studio 11.6 and it cannot connect to JNDI resource.  It gives below error, may I get some guidance as to how can I make it work please?

10.10.2.9.1599974757846.0.Outbound_Email,09/13/2020 01:25:57.908, The error was: A built-in element encountered an exception of type com.audium.server.AudiumException. There was a problem looking up the JNDI data source 'pcspoc'. The root cause was: javax.naming.NameNotFoundException: Name [pcspoc] is not bound in this Context. Unable to find [pcspoc].
com.audium.server.AudiumException: A built-in element encountered an exception of type com.audium.server.AudiumException.
at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:416)
at com.audium.server.controller.Controller.goToAction(Controller.java:4386)
at com.audium.server.controller.Controller.goToElement(Controller.java:4022)
at com.audium.server.controller.Controller.enterVoiceApplication(Controller.java:1896)
at com.audium.server.controller.Controller.newCall(Controller.java:1769)
at com.audium.server.controller.Controller.doPost(Controller.java:943)
at com.audium.server.controller.Controller.doGet(Controller.java:543)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1502)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1458)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: com.audium.server.AudiumException: There was a problem looking up the JNDI data source 'pcspoc'.
at com.audium.server.action.database.DatabaseAction.getDataSourceForTomcat(DatabaseAction.java:237)
at com.audium.server.action.database.DatabaseAction.getDataSource(DatabaseAction.java:192)
at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:305)
at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:390)

[Gerry O'Rourke]

Piyush,

Your Tomcat config is not quite right.

Have a look at this reference and see if it helps:

http://orourke.tv/web/doku.php?id=vendors:cisco:uc:cvp:jndi-cvp

Regards,

Gerry

[kavle]

Hi Piyush ,

I have the same error as above .

Caused by: javax.naming.NameNotFoundException: Name [sbtcd] is not bound in this Context. Unable to find [sbtcd].

an idea what can fix this .

I am trying to do a SQL DB integration with call studio app. Getting below error.

"""The error was: A built-in element encountered an exception of type com.audium.server.AudiumException.
There was a problem looking up the JNDI data source 'sbtcd'. The root cause was: javax.naming.NameNotFoundException: Name [sbtcd] is not bound in this Context. Unable to find [sbtcd]"""

below are the jar files I have in my CVP server.

 under the location - C:\Cisco\CVP\VXMLServer\Tomcat\lib

mysql-connector-java-8.0.11.jar
mssql-jdbc-12.2.0.jre8.jar

CVP Version - 12.6

below is my context.xml parameters.

<Resource name="jdbc/sbtcd"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
DriverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://Hostname:1433;databaseName=sb_abcd;
user=abdcxyz;
password=abcd123;encrypt=true;trustServerCertificate=true"/>

[piyush aghera]

Hi,

Version 12 and above doesnot need below line:

factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

Remove it from context.xml file, restart CVP VXML service or run update.bat file for the VXML application that connects using this resource and try again.

[kavle]

wow good to know. thank you for replying.

[janinegraves]

To test with Studio Debugger, substitute C:\cisco\cvp\vxmlserver\tomcat

with this:

C:\Cisco\CallStudio\eclipse\plugins\com.audiumcorp.studio.debug.runtime\CATALINA_HOME\

[ln33147]

Hello Janine,

Can the password in "url" be masked? 

[piyush aghera]

Hi,

One way to not enter username/password in context.xml file is to use Windows based authentication to connect to SQL. Below are the steps I followed to configure Windows based authentication with CVP/VXML 12.6(2).

1. Copy mssql-jdbc_auth-12.6.2.x64.dll to C:\Cisco\CVP\jre\bin of CVP server. This file is needed to enable windows authentication.
2. Copy mssql-jdbc-12.6.2.jre8.jar to C:\Cisco\CVP\VXMLServer\Tomcat\lib of CVP server. This is SQL driver for JNDI connection.
3. Add below to context.xml file under C:\Cisco\CVP\VXMLServer\Tomcat\conf of CVP server:

<Resource name="jdbc/hdsdb" auth="Container"

type="javax.sql.DataSource"

factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

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

url="jdbc:sqlserver://<SQL server>:1433;  DatabaseName=hds;  

integratedSecurity=true;  <<- Enables Windows authentication

encrypt=false" />

4. Change "Log On As" user for "Cisco CVP VXML Server" service to a Windows server account that has read access on SQL database.

5. Restart Cisco CVP VXML Server service.

Attached are the files mentioned in step 1 and 2. If you want to use these steps in CVP Call Studio debug mode, then you still have to give username/password in context.xml file. 

P.S. it took me almost 3 weeks and 2 TAC engineers to figure this all out.

Update: Version 12 and above doesnot need below line in context.xml file.

factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

[Omar Deen]

Thank you, @piyush aghera ... was banging my head against the wall trying to figure this out until I came across your post.

[piyush aghera]

I am glad it helped!!