You can definitely use the Studio Database element to do that. Look in the Element Specifications ref manual to see how to configure Tomcat to use JNDI. This requires configuring the Tomcat/conf/context.xml or server.xml file. Download the jdbc driver (.jar file) from the internet to match your database and verion. Then install it into Tomcat/common/lib directory. Then you'll have to restart VxmlServer. The query would be simple. SELECT DNIS, OPENTIME, CLOSETIME FROM TABLENAME WHERE DNIS={CallData.DNIS} Data is returned as Element data for the Database element with the names of the columns. So here the element data for the DB element would be named DNIS, OPENTIME, CLOSETIME Then you'll want to use a Decision element to check that the CallData DNIS equals the DB DNIS (to ensure that you got something back from the D. One warning about the Studio Database element is that if the database is down, the element throws a Java exception and the call will return to ICM down the error path of the RunExternalScript node. On 3/8/2013 5:41 PM, Cisco Developer Community Forums wrote: Mark Applebee has created a new message in the forum "General Discussion - All Versions": -------------------------------------------------------------- I am trying to move my icm admin script for open/close to  studio application. Basically customer would like to change the open/close time through a web page into a sql database. I am planning to write a call studio application to check the open/close time before sending the call to the menu option. The database have mainly three fields DNIS, Open Time, Close time. I just want the studio application to check whether current time is between open & close time before presenting to menu option. Could someone help me whether I can do this by using a simple database element or do I need to know custom element to do this. If this is a simple sql query, can someone provide me an example or point me in the right direction. -- To respond to this post, please click the following link: http://developer.cisco.com/web/cvp/forums/-/message_boards/view_message/12814654 or simply reply to this email. -- Janine Graves

I have done date and time comparisons many times in Studio and I always write a custom class.

In Java you can instantiate a Calendar class object with numbers representing the date and time you are fetching from your caller and then you can use the before() and after() methods on the Calendar class, thus dealing with the intricacies of leap years and so forth. This technique is superior to anything you may cobble together with the Decision Editor. I did try that path. ;-)

Sometimes there is no other way but to go to Java. If you are a Studio programmer and you cannot program in Java, you will struggle on what, on the surface, seems to be a simple requirement for your application.

If you attend Janine’s Developer’s course, you will get the basics of Java. You don’t need the full monty, but you do need the fundamentals as it applies to manipulating data in Studio.

Regards,
Geoff

FYI, if it's relatively simple comparison the time (no time zone math
that you need to perform) then might get away without using Java.

You can get the current hour, minute, day, month, and year in the
Substitution window under the Date/Time column.

Be aware that they are returned as integers, so they are not padded to
begin with 0 if they're one digit.

But if you are comparing as Number in the Decision element, I think it'd
work for you.

ICM is the best place for determining opening hours. Why would you do this in CVP Studio?

Regards,
Geoff

Asher,
Can you email the schema and code to markapplebee@outlook.com?
Regards
Mark

You can have a web front end that writes to a database and ICM admin scripts that query the database. It has to be optimized to get all the information in one hit since the query will run every 60s. But I have thought about this a few times and prototyped the ICM scripting.

Regards,
Geoff

Bill, that's awesome! I just tried it and it works. But you need to send
in _dnis (which ICM script to run) and a callid (make up a number).
http://vxmlserverip:7000/CVP/Server?application=Test&_dnis=2009&callid=7777777777777777777777

The only WARNING is that you need to follow this up with another URL to
free the vxml server license
http://vxmlserverip:7000/CVP/Server?audium_action=hangup

I guess if the Studio app had a 1-minute Session Timeout, you could omit
the 2nd step.

And the 1 minute Session Timeout in the Studio app works great.
Then you can just use a web browser, use the first URL to call a studio
app that calls an ICM script (passes data and gets back data) and
creates global variables on vxml server.
Just have to remember to do this to all the vxml servers.
LOVE pushing the envelope.

Bill, you also want these global variables to be created on a restart of
VxmlServer. So, you'd need to kick off the URL request every time
VxmlServer restarts. How do you do that?

These would be ICM Global Variables, which are persistent across Call Router restarts even (independent of CVP/VXML Server).
My thought here is really CVP becomes like a middleware of sorts. The URL request would come from a user trying to do something like perform an emergency close, or a meeting close, or even change hours of operation, etc. So you would need a little bit of web work on the front end to collect the data and construct the URL to send to CVP to actually change the variables in ICM.
Even so, I wasn't expecting the first part to be as easy as you've proven it to be! ;-)
Does that description make sense, or are we not talking about the same idea?