Problem: The Recording step in a UCCX script is widely used - for instance, many users choose UCCX as a quick "voice mail" solution. However, due to technical restrictions, saving the recorded message in the UCCX internal repository is not always a good idea, as it takes up valuable space, and accessing the recordings is not always easy.
Analysis: Having recordings off the UCCX server repository would be more convenient. The UCCX would only be "responsible" for recording the message and save it in a file server, or even better, a database table, where the file would be available for the desired post-processing. UCCX is perfectly capable of saving anything in a database table (using the proper license). So it's relatively easy to write a UCCX script that saves recordings in a database table - as a binary large object (BLOB).
Solution:
*** DISCLAIMER: the usual blah blah. This is a proof of concept. I am not responsible for any damage. No, I don't think this is an elegant solution, but it works. Yes, I do have a customer that uses this script in a real environment. ***
Application versions: UCCX 8.0(2) and Microsoft SQL Server 2005 (so the SQL statements are actually Transact-SQL).
I. Saving a prompt in a database
First, there's a problem we need to solve: the BLOB datatype is unsupported in UCCX. Try it. If you present a table containing a BLOB column, UCCX will start complaining. This is why we will... sort of make it believe it uses a database table with supported columns when actually it does not. Here's how:
1. Create two database tables: one will be a table that actually holds the prompt (perhaps with an identifier) - in this example it's name is t2; and another one - in this example t3 - which is actually a dummy one. Here are the table creation scripts used in this example:
CREATE TABLE [dbo].[t2](
[id] [int] IDENTITY(5000,1) NOT NULL,
[randString] [nvarchar](50) NULL,
[binData] [varbinary](max) NULL
)
and
CREATE TABLE [dbo].[t3](
[id] [int] NULL,
[dummyBinData] [varchar](8000) NULL
)
2. create a UCCX script that will help you record the prompt and insert it into the t2 database table. In this example, it's just a script that uses one variable, of type Document, name recording. It contains three steps, 1. Accept, Recording that yields the value of the variable recording, and finally, 3. DB Write that inserts the value of variable recording into the t2 database table, with the following SQL query:
INSERT INTO t2 VALUES ('xxx',$recording)
Notice: the 'xxx' will be the value of the column randString, $recording the value of binData in the t2 table (you don't need to insert anything into the id column, as it is automatically generated). Of course, one may insert something meaningful instead of 'xxx' into the column randString. Like the ANI. Use your imagination.
Voila, you've got your recording in a database table, serialized.
The role of UCCX ends here, use a Java servlet or JSP or PHP or *your-favorite-language* to retrieve it.
In Java, I would probably use the getBinaryStream method of the result set to get an InputStream (it will be then read accordingly).
II. Retrieving and playing recording from a database
1. create another script that would read the recording out of the database table, convert it into a Prompt type variable, and play it. In my example, this script has the following variables: inString of type String, byteArrayIS of type java.io.ByteArrayInputStream. The script itself:
1. Accept
2. DB Read. Now here comes the tricky part 1: you actually perform the SQL query on table t2, with aliasing the binData column, the new name will be dummyBinData (look, ma, there's a column named dummyBinData in the t3 table as well). The SQL query itself would be something like this:
SELECT binData as dummyBinData FROM t2 WHERE randString='xxx'
3. DB Get: tricky part 2: in this step you actually choose the t3 table (which has two fields, id and dummyBinData, both with types perfectly supported by UCCX), map dummyBinData to the UCCX variable inString.
Voila, you've got your recording into the UCCX script. It's a String though, and you need to do some magic to convert it into a Prompt.
4. the document titled "Cisco Unified Contact Center Express Expression Language Reference Guide, Release 8.0(1) Cisco Unified Contact Center Express Scripting and Development Series: Volume 3" tells you that the Prompt can also be an InputStream, provided this InputStream can provide correctly encoded data (in my case it's G.711). Converting a String to ByteArrayInputStream (a subclass of InputStream) is easy: ByteArrayInputStream(stringVariable.getBytes()) - but in our case, the String type variable actually contains the hexadecimal representation of the binary object - so in other words, we need to convert some hexa data to binary. So in this fourth step, which is actually a Set step, we do this:
Set ByteArrayIS =
{
int len = inString.length();
byte[] data = new byte[len / 2];
int iter = 0;
for (iter = 0; iter < len; iter += 2) {
data[iter / 2] = (byte) ((Character.digit(inString.charAt(iter), 16) << 4)
+ Character.digit(inString.charAt(iter+1), 16));
}
return new java.io.ByteArrayInputStream(data);
}
And now our UCCX variable named byteArrayIS contains the binary representation of the recording.
Finally,
5. Play Prompt step - only thing we need to to is casting byteArrayIS to Prompt, but it's easy: (Prompt) byteArrayIS.
Here's a screenshot:
Final thoughts: no, I don't think it's an elegant solution. Storing large binary objects and pushing them back and forth between a SQL database and UCCX is something I don't like - simply because prompts 1. occupy too much space in SQL, 2. should be files or streams. But anyway, this is a working solution so enjoy it.
G.
