11 Replies Latest reply: Jan 11, 2018 3:41 AM by rpeddi29@gmail.com RSS

Database integration

rpeddi29@gmail.com

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)

  • 1. Re: Database integration
    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

  • 2. Re: Database integration
    rpeddi29@gmail.com

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

  • 3. Re: Database integration
    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?

  • 4. Re: Database integration
    rpeddi29@gmail.com

    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:

     

    Settings.png

     

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

  • 5. Re: Database integration
    janinegraves

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

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

    *

     

    *

    *

  • 6. Re: Database integration
    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

  • 7. Re: Database integration
    rpeddi29@gmail.com

    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

  • 8. Re: Database integration
    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.

  • 9. Re: Database integration
    rpeddi29@gmail.com

    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?

  • 10. Re: Database integration
    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}
  • 11. Re: Database integration
    rpeddi29@gmail.com

    Thanks Janine..got it working