DBlookup not working

[chad_meyer]

Using CVP 11.6 I am working on a simple DB lookup app that will match the area code to state and return that state to ICM.  In Call Studio I am able to define session data when debugging to replicate what will be received from ICM successfully.  My issue is after I deploy the app dial in.  From the ActivityLog I see the parameter "area" being defined, however in the 'num_rows_processed' it is showing 0 results. From Call Studio I am showing 'num_rows_processed' as 1. From ICM I am calling the following application:

"application=AreaCode;area=Call.user.cvpapp.variable0"

I have dumped the contents of Call.user.cvpapp.variable0 into a PV and verified it is showing the correct value.  I am not really sure what else to check as the CVP logs are not offering any insight into the issue and the ErrorLog dows not generate.  Any guidance on this would be great.  This is my first CVP app so maybe I missed something simple?

[bill.king1]

I might be a little confused, when you said DBLookup I initially thought you were referring to the ICM DBLookup function, but I think mean CVP?

If so, one question might be why not use the state lookup functions in ICM to do what you're doing here? Like use the Region function, like here https://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/icm_enterprise/icm_enterprise_11_5_1/Maintenance/Guide/ucce_b_scripting-and-media-routing-guide/ucce_b_scripting-and-media-routing-guide_chapter_010.html#CCMP_RF_UFFF9392...

In any event, if you want to do it in CVP, can you perhaps post the log and the application?Or maybe use the Documenter function to share the application?

When you say you have the contents in variable0 and it is what you want, are you basically saying you see it in the CVP log but it isn't being returned back to ICM as you expect? Perhaps it is just your CVP Subdialog Return settings in the studio application and/or your FromExtVXML ICM setting.

[david.macias]

Ok, so you are getting the value of area in CVP, but when you do the DB dip no data is returned. Can you show us the configuration of the query? Have you validated this query works with SQL Studio or some other SQL client?

david

[chad_meyer]

Hi @bill.king1 ,

Thanks for the link.  Looks like it links to a link to a link (thank you Cisco!) so will have to dig into it more but appreciate the heads up.  For what it's worth, the use case would be to route via state by area code so not sure if that makes your proposed solution too cumbersome or not.  Regarding the DBLookup, yes, I meant the database element in a CVP application, not the build in ICM DB Lookup function.  The application has been attached.  Due to the dial plan the HDS may show the ANI with a + or +1 then the 10 digit number.  In ICM I am essentially isolating the area code portion of the ANI and sending that parameter in the application 

The application has been uploaded.

@david.macias The SQL query works in SQL Studio just fine.  The query, and corresponding app flow in  Call Studio all work as well.  The difference is when I debug the application in Call Studio I see in the activity log that the query returned results from the database (num_returned_rows = 1) where as when I deploy to VXML and call the routing script (num_returned_rows = 0).  I am unsure if the parameter I am passing in ICM 

"application=AreaCode;area=Call.user.cvpapp.variable0"

is sending the value to my application.  Hope that makes sense. Appreciate the assistance.

[david.macias]

Ok so there are two things here. First, can you post the activity log. On call start you should see the parameters being passed to the application and you should see area=. Second part is any chance there's a firewall issue between the VXML browser and the DB?

david

[bill.king1]

I don't see anything attached, but ICM has this type of feature already built in using that CLID Properties node (the link I sent you) using Regions/States, so you wouldn't even have to send it to Studio, you could do it in all in ICM.

If for whatever reason you want to do it in Studio, the first thing I would check is make sure your database is even configured to be called from CVP/VXML. For instance, can you call anything in any table, even a select *? Did you update CVP/VXML to reference the database for instance, like the JNDI steps?

[chad_meyer]

I had no idea this was in here, thanks!  I will play around with this feature.  Is there a way to add area codes in Region Explorer?  I am not seeing how or where to do that.

[bill.king1]

Yes, under Configuration Manager under Misc Tools for Region Editor.

[chad_meyer]

@david.macias Attached is the CallStudio app and activity logs for both the Call Studio app and another from the deployed version.

[david.macias]

Biggest thing I see between those two log is that one says area and the other areacode. The other thing is that for areacode one log shows NA and the other 101. So that might be your issue that you're referencing the wrong parameter.

david

[chad_meyer]

What are you referring to with area and areacode?  From CallStudio the following entry is hardcoded in the debugger and referenced in the log:

Call Studio ActivityLog

10.240.8.41.1624464783902.0.areacode,06/23/2021 11:13:04.308,,start,parameter,area=101

From deployed ActivityLog:

10.240.8.35.1624393454342.11.AreaCode,06/22/2021 15:24:14.342,,start,parameter,area=Call.user.cvpapp.variable0

Should the contents of Call.user.cvpapp.variable0 be shown in the second log, which is "101" or is the variable name the expected behavior?

[david.macias]

Ok, then yeah that's an issue. You shouldn't see the ICM variable name in the activity log. Check the ICM node to ensure you're concatenating correctly. Maybe paste a picture of it so we can help.

david

[chad_meyer · Accepted solution]

Yea, that was my problem.  

I originally had this specified and didn't realize each parameter needed to be concatenated:

Incorrect:

"application=AreaCode;area=Call.user.cvpapp.variable0"

Correct:

concatenate("application=AreaCode;",concatenate("area=",Call.user.cvpapp.variable0))

[david.macias]

Here's a better way to do it:

concatenate("application=AreaCode;area=",Call.user.cvpapp.variable0)

david