cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements
1038
Views
5
Helpful
6
Replies
Akib Dargawale
Beginner

{CallData.ANI}

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

6 REPLIES 6
janinegraves
Rising star

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}'

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

janinegraves
Rising star

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
Beginner

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 -

<!-- The contents of this file will be loaded for each web application -->
<Context>

<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>

<Resource name="jdbc/AkibDb"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;integratedSecurity=true; user=AKIBLAB\Administrator; password=; DatabaseName=AkibDb"
/>
<Manager pathname="" />

</Context>

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.

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
Beginner

hello mam,

Thanks a lot for your help.

setValue method works fine for my application.

Create
Recognize Your Peers
Content for Community-Ad