Solved: Re: {CallData.ANI}

Akib Dargawale · ‎10-19-2021

Hello,

I want to fetch records from the SQL server.

Records are successfully fetched using this query- "select rechargeDetails from dbo.MobileNoDetails where mobileNo=9988776655".

but when I use "select rechargeDetails from dbo.MobileNoDetails where mobileNo={CallData.ANI}" a error occurs.

192.168.1.31.1634705802869.1.DbDemo,10/20/2021 10:26:43.916, The error was: A built-in element encountered an exception of type com.audium.server.AudiumException. Incorrect syntax near 'sip:'. The root cause was: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'sip:'.
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:4446)
at com.audium.server.controller.Controller.goToElement(Controller.java:4082)
at com.audium.server.controller.Controller.continueCall(Controller.java:3762)
at com.audium.server.controller.Controller.doPost(Controller.java:1175)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:494)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:412)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:754)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1385)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.audium.server.AudiumException: Incorrect syntax near 'sip:'.
at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:367)
at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:390)
... 27 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'sip:'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:548)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:479)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7347)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2713)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:204)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:401)
at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:322)
... 28 more

janinegraves · ‎10-21-2021

As I suspected, your ANI contains |sip:7788994455@maahiron.com and
that's causing the DB error.
|

You can clearly see the {CallData.ANI} value at the beginning of each
call in the Activity Log.|
|

|.DbDemo,10/21/2021 12:03:31.006,,start,newcall||
.DbDemo,10/21/2021 12:03:31.006,,start,ani,sip:7788994455@maahiron.com|

You can use the SetValue element tab called Settings.
Right-click: Add Variable
Name the variable LocalANI ---as it'll become a Local Variable (not
element data or session data).

In the Value field, use javascript to replace all non-digits with an
empty string:
{CallData.ANI}.replace(/\D/g,"")

Then change your Sql expression with this:
select rechargeDetails from dbo.MobileNoDetails where mobileNo=
{LocalVar.LocalANI}

View solution in original post

janinegraves · ‎10-20-2021

I can only assume the error with "sip:" is coming from a problem connecting to the database.

0. Does the query work from sql workbech (outside of studio)?

1 What JDBC database driver did you install into VXMLServer/Tomcat/lib? it must match be compatible with the cvp java version and the sqlserver version.
2 So - what version of CVP and SqlServer do you have?
3 What is in your context.xml in VXMLServer/Tomcat/conf/context.xml?
4 What is the value of the {CallData.ANI} (it shows in the Activity Log)? if it is empty, or NA, or contains any non-digits, then you should enclose the value in apostrophes --- select rechargeDetails from dbo.MobileNoDetails where mobileNo='{CallData.ANI}'

janinegraves · ‎10-20-2021

And can you post a screenshot of the Settings tab of your Database Element too?

janinegraves · ‎10-20-2021

Rereading the error message - this error makes me think that your {CallData.ANI} starts with "sip:" (as happens sometimes with sip telephony). ****Error Caused by: com.audium.server.AudiumException: Incorrect syntax near 'sip:'.****

If that's the case, you'll need to use the SetValue element and JavaScript to extract just the dialed number portion.

Or there may be a way to configure the gateway, ICM, or VVB to pass in just the phone number.

Akib Dargawale · ‎10-21-2021

Hello mam,

Thanks for your reply.

CVP version is 12.0 and msserver is 2014.

I have installed mssql-jdbc-6.4.0.jre7.jar

Context file -

<Context>

<WatchedResource>WEB-INF/web.xml</WatchedResource>

SQL Query-

select rechargeDetails from dbo.MobileNoDetails where mobileNo= {CallData.ANI}

I have attached the error and activity log with the screenshot of the database element.

janinegraves · ‎10-21-2021

As I suspected, your ANI contains |sip:7788994455@maahiron.com and
that's causing the DB error.
|

You can clearly see the {CallData.ANI} value at the beginning of each
call in the Activity Log.|
|

|.DbDemo,10/21/2021 12:03:31.006,,start,newcall||
.DbDemo,10/21/2021 12:03:31.006,,start,ani,sip:7788994455@maahiron.com|

You can use the SetValue element tab called Settings.
Right-click: Add Variable
Name the variable LocalANI ---as it'll become a Local Variable (not
element data or session data).

In the Value field, use javascript to replace all non-digits with an
empty string:
{CallData.ANI}.replace(/\D/g,"")

Then change your Sql expression with this:
select rechargeDetails from dbo.MobileNoDetails where mobileNo=
{LocalVar.LocalANI}

Akib Dargawale · ‎10-24-2021

hello mam,

Thanks a lot for your help.

setValue method works fine for my application.