Database integration

[rpeddi29]

Hi,

I'm trying to test DB integration from call studio. I have added the sql server DB connection string details in C:\Cisco\CallStudio\eclipse\plugins\com.audiumcorp.studio.debug.runtime\CATALINA_HOME\conf\context.xml .When trying to test it from call studio, running into below error. Can anyone help me resolve this please?

The error was: A built-in element encountered an exception of type com.audium.server.AudiumException. Cannot create PoolableConnectionFactory (This operation is not supported.) The root cause was: java.sql.SQLException: Cannot create PoolableConnectionFactory (This operation is not supported.)

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:4363)

at com.audium.server.controller.Controller.goToElement(Controller.java:3999)

at com.audium.server.controller.Controller.enterVoiceApplication(Controller.java:1875)

at com.audium.server.controller.Controller.newCall(Controller.java:1748)

at com.audium.server.controller.Controller.doPost(Controller.java:926)

at com.audium.server.controller.Controller.doGet(Controller.java:540)

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: Cannot create PoolableConnectionFactory (This operation is not supported.)

at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:367)

at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:390)

... 28 more

Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (This operation is not supported.)

at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2293)

at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2038)

at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1532)

at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:313)

... 29 more

Caused by: java.sql.SQLFeatureNotSupportedException: This operation is not supported.

at com.microsoft.sqlserver.jdbc.SQLServerConnection.isValid(SQLServerConnection.java:2906)

at org.apache.tomcat.dbcp.dbcp2.DelegatingConnection.isValid(DelegatingConnection.java:917)

at org.apache.tomcat.dbcp.dbcp2.PoolableConnection.validate(PoolableConnection.java:282)

at org.apache.tomcat.dbcp.dbcp2.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:356)

at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2306)

at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2289)

[janinegraves]

You need to download the jdbc driver for your db and put it into Catalina_home/common/lib

Sent from my Verizon 4G LTE smartphone

[rpeddi29]

yes that is done already....i have the sqljdbc4.jar in that path

[janinegraves]

It works on my Studio Debuggers, so now it's the details that you must

sort out:

What version do you have of: Studio, SqlServer, CVP?

Please post the context.xml

Please post a snapshot clearly showing the Settings tab of the Studio

database element (including the query you're trying to make)

Also - have you created a valid user/pwd and assigned permissions on the

SqlServer side?

[rpeddi29]

Call Studio version is 11.5.1

SQL server is 2012

Context.xml:

<?xml version='1.0' encoding='utf-8'?>
<!--
  Licensed to the Apache Software Foundation (ASF) under one or more
  contributor license agreements.  See the NOTICE file distributed with
  this work for additional information regarding copyright ownership.
  The ASF licenses this file to You under the Apache License, Version 2.0
  (the "License"); you may not use this file except in compliance with
  the License.  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
-->
<!-- The contents of this file will be loaded for each web application -->
<Context>

    <!-- Default set of monitored resources. If one of these changes, the    -->
    <!-- web application will be reloaded.                                   -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>

    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <!--
    <Manager pathname="" />
    -->

    <!-- Uncomment this to enable Comet connection tacking (provides events
         on session expiration as well as webapp lifecycle) -->
    <!--
    <Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />
    -->
<Resource name="jdbc/eONEDB" auth="Container" type="javax.sql.DataSource" 
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://192.168.10.65:1433;user=sa;password=gem1n1;DatabaseName=VTSystem" />
</Context>

- settings in call studio:

- I can access the sql server with the credentials provided . There is no access issue

[janinegraves]

try adding this as one of the attributes of the tag:*

<Resourcefactory="org.apache.tomcat.jdbc.pool.DataSourceFactory"

*

*

*

[rpeddi29]

Hi Janine graves,

Thanks for your help.I am able to connect to database but have an issue. I am trying to run an query that fetches two fields. this db query i sof type "single". So I should be seeing two element data with those column names as being returned from that query right but don't see that happening. If I capture he return xml resultset, I can see the xml with those two fields and data but not clear on how can we use those fields at a later stage in call flow. Can you please advise as why we are not seeing the fields being returned as element variables or if we have to do something to get those fields then how can we d that?

thanks

[janinegraves]

1.Was the switch to a different jdbc driver the way you got the DB element to work?
2.When you say that the element data variables aren't being created by your select statement, how do you know that?
Studio/VXMLServer create element data with the name of the returned columns (or alias).
If you execute "select acctnum,balance,credit from mytable" you'll end up with element data named acctnum, balance, and credit. However - Studio doesn't display that in the Substitution window and VXML Server  doesn't log that info. You could see them in the Studio Debugger, but won't see them in the Activity Log, unless you add them to the Activity Log yourself.
3. If you execute the query "select count(*) from mytable" - VxmlServer will not be able to create (or let you select) a variable named count(*). So you have to use an alias  "select count(*) as numrecords from mytable" will create element data named numrecords.  To select it, you go into the Substitution window, select ElementData, select the DBelement, and then enter the variable name from your keyboard numrecords  (or acctnum, or balance, or credit in the previous example), then press Add Tag.

4.If you get the resultset variable, you can parse that using the Set Value element - see the ElementSpecifications ref manual for its usage, and see the CVP Users Guide for Studio and VXMLServer for the commands to include to parse the xml.

[rpeddi29]

I just added the entry for factory for the db resource in context.xml file and that resolved the issue.

Yes, I have query like select a,b from t1...so I should see the a and b created under element data in substitution window right so that those can be used at later stage.??? So can't we get these directly instead of getting an xml result set and parsing?

[janinegraves]

The element data variable DOES exist, but doesn't display in the Substitution window by default. You must type it in from your keyboard.

1. Go into the Substitution window,
2. Select ElementData tab,
3. Select the DBelement,
4. Enter the variable name from your keyboard (it will not display automatically)
5. Press Add Tag:    {Data.Element.Database_01.myVarNameTypedFromKeyboard}

[rpeddi29]

Thanks Janine..got it working

[janinegraves]

Someone else I know had a similar problem on 11.5 and was able to get it

working by switching to a different *driver *jtds-1.3.1.jar driver,

and using this