How to save ICM data to an SQL DB

[JoeMattia]

Hello,

I know it is possible in UCCE to retrieve SQL data to use in ICM routing, but is there a way to store ICM data into an SQL database in order that other applications can retrieve that data?

Here's what we're trying to accomplish. We would like an ICM script to collect data from a caller using the IVR to actually collect the data then have ICM store that into temporary variables. We would then like ICM to store those temp vars into an SQL database so that another app can look at that table for that data and populate it's screens.

Is this possible?

We are running UCCE 7.1(5) with CRS (IVR) 4.0(5)

We have IVR scripts to collect the data and ICM scripts to store this collected data into ICM variables, but now I need to know how to store them into an SQL table.

Any help with this will greatly be appreciated.

Thanks,

Joe Mattia

[david.macias]

Yes, this is possibl, but do not do this with ICM, do this through your IVR or some other peripheral (webservice).  The DB write step would be the way to go about this with IP IVR.

david

[JoeMattia]

David,

that is exactly what I was hoping to hear. Thanks.

I've spent the most part of today working with a test IVR script to accomplish the simple task of saving to an SQL DB and I'm getting there slowly.

I created a test SQL DB, configured the database subsystem on the IVR server, used the Get Digit String command to prompt the user to enter a customer number, then used the DB Read followed by DB Write to save that value into a DB field. I was successfull in saving a forced value, but now I'll try saving the user's input and see how that goes.

The first time I tested this, it saved the value, but any time after that, it didn't work and now I can't even open up that SQL table. It appears to be locked.

I also think I didn't set up a proper primary key on the test DB, that could be causing this issue as well.

Do you happen to have an example of this type of IVR script?

[as1412]

Hi Joe,

If you are looking at only populating the third party application(TPA) "on the fly" , you could integrate the IP IVR with the TPA directly using java .

This way you do not need to store the IVR CED in a DB - unless you want it stored ,in which case it could be done the way David has suggested.

Regards,
Arun

[JoeMattia]

Arun,

Thanks for your input as well, it is greatly appreciated. I'm not too familiar with java but that does sound like a better way to go.

I will need to investigate that when time permits so that I can take that path and save any DB access.

As per my request above from David, would you happen to have any simple IVR scripts that would show me how to properly use the DB Read and DB Write commands populating DB files from the IVR variables taken from user's inputed data?

We're currently running IVR (CRS) 4.0(5)

[JoeMattia]

David and Arun,

thanks again for your suggestions. I just wanted to give you an update that I spent more time working on this and finally got it to work.

I initially had my SQL fields set as varchars and thought the issue of not being able to save was due to that they were not set as integers.

So, I created a new table setting all of the fields to int, and it worked like a charm. I then went back and set the original table's fields to int, and it started working for that table as well. I then went and changed them back to varcahrs and it continued to work.

I'm not sure why it didn't work initially with all fields set to varchar in MS SQL, but it seems to be ok now.

Thanks again for all of your help with this, I truely appreciate it.

Regards,

Joe

[david.macias]

Joe,

That is rather strange, but to be honest with you, I've run into similar problems in the past.  However, while I love using varchar since it's so flexible, with IP IVR I'm of the mindset that if you're storing just INTs to set the column to INT.  Just a thought.

david