{uccx scripting: db get data}

In this follow up post to DB integration in UCCX, we’re finally going to attempt to get our hands dirty..First we’ll start easy and pull out a simple set of data from a single table with an easy sql statement. I also want to utilize the Java Array type in order to hopefully dispel any intimidation you might have toward this object (variable). And lastly, I’m going to write some equivalent Java code around JDBC that does away with the built-in Cisco steps to “open” your mind to the fact that although Cisco has a specific step for something doesn’t necessary require you to use it..(this will obviously be geared to those of you out there with some programming experience..whether it is C, python, perl, php, etc).

Here is a list we can use for this post. We probably won’t demonstrate getting all of this data..but I will demonstrate everything necessary to start from a single table..to joining multiple tables together to get at the data in the DB..

  1. Get the Authors First and Last Name
  2. How many titles are in circulation for the Author?
  3. What are the title names
  4. What is the Title’s Publisher
  5. What is the Price per Title
  6. What stores are selling the title
  7. How many sells per store per title

Let’s examine Question #1: In an IVR, the user interface would ask the caller for specific information in order to get the data..let’s look at the table where this information is at to determine what type of data a telephony user interface (tui) information would most accompany:

authors_fnln

Ah! field au_id can be used in a TUI..And the magic step to get this data? Get Digit String of course..however the dashes won’t exist and for the purposes of this post I won’t be leaving you any type of script that uses Triggering Contacts (because I doing this from the airport and I’m not going to use a phone..) but I will demonstrate putting in the dashes for this because it just sounds fun..Below, using a Set Step using the String (we want to assign value to) we will use to get the Data we want from the Database I will demonstrate how to format the String properly..

String s = "213468915";
//s represents the value we got from the GET DIGIT STRING STEP
if (s.length() == 9) {
     s = s.substring(0,3) + "-" + s.substring(3,5) +
         s.substring(5);
} else {
     //BAD INPUT..RE-ENTER
}
return s;

If we want to DB Get data from the Database, we have to use the DB Read Step; I showed you one of these in a previous post. And not only do you have to Read (connect) the database, the Select statement in the Read step has to be able get data from the Tables you are trying to reference.

dbread_select_auth_pub

However, the DB Get Step can only get data from one table at a time (as one would expect); here we are using authors as you can tell from the select  statement.

dbget_fn_ln

As you can see, the table, authors and the field names I am interested in are shown above. I’m not going to get too much into the rules of using these steps because I really don’t know all of the rules..I just adjust as I encounter “limitations”; and oh, btw, Cisco doesn’t really list the rules of the road either..only a particular SELECT statement doesn’t work:

SELECT count(*) from table

In the image below you will find a routine SELECT statement: * is a wildcard that returns all fields in a particular table if the where clause is matched; however, if you can help it..you should refine the statement down to just the information you need.

basicselectstmt1

All we want is the First Name and Last Name:

specficselectstmt2

Below is a very simple reactive debug for item number one.

ex_get_fn_ln

I realize you probably have to experience this for yourself in order to really get this..What you should take away from this is this: when you connect to the DB and use the DB Get Step..if you didn’t ask for the Field you are looking for in the SELECT statement..although you can see it above..doesn’t mean you will get it.

Now I’m going to switch gears. With the callers au_id I’m now going to check to see how many books this Author has in circulation; below is the table we’ll be referencing to get that data.

titles_table1

The Data we’ve collected is au_id. In the above Table (called titles) there is no reference to that data: au_id.  So how do we get the title(s) the authors (represented by au_id); we need to join a new table in our query from the table below. We are going to take the au_id and grab all the title_id(s) from the titleauthor table.

titleauthor1

Let’s get down to business..with the SELECT Statement we’re going to need in order to get the Data we want..

SELECT title from titles
join titleauthor on titles.title_id = titleauthor.title_id
join authors on titleauthor.au_id = authors.au_id
where authors.au_id='213-46-8915'

The UCCX Editor Screenshot:

dbread_titles

And as you can see above with (Number of rows returned) this particular author has 2 Titles in Circulation. And I probably should break down this statement just a bit:

SELECT title from titles
/*titles = table from db, title = field in titles */
join titleauthor on titles.title_id = titleauthor.title_id
/* titleauthor = table from db, title_id in that table
is equal to the same title_id field from titles */
join authors on titleauthor.au_id = authors.au_id
/* authors = table from db, au_id field from titleauthor
is equal to the same au_id from authors */

I don’t know if that will make any sense..but that’s about as good as I can explain the statement that ultimately retrieves title. Next I’m going to discuss the UCCX Scripting portion of this particular get from the DB.

Before we get the titles (for this example we are going to get more than 1 title) we need to “talk” about what variables we’ll need to “move the cursor” in the returned data from the DB and then the data structure we’ll use to store the returned data. From an IVR viewpoint, returning a Book Title to a telephony interface may not be ideal unless you have a Text to Speech Server that speaks that title name to the caller (just a disclaimer as I am using my example as a demonstration of getting data from a db..not necessarily the practical usage case for it). Below is the big picture of my application logic when I’m getting Titles using DB Read/Get:

readgetTitleBigPic

In explaining the graphic above, first (as we already know) we use the DB Read in order to get to the appropriate Table/Field(s) we want to get data from. Then we use a DB Get step to set the appropriate Application Values from the Values “extracted” from the DB (in this case a String called sTitle); if there is Data at the Cursor (you can read about how DB cursors thing the cursor in a document that moves line by line as you hit carriage-return) in the DB the Successful branch of the DB Get step is chosen.  Below is a graphic of the expression that is used in this case:

getdataSuccessBranch

In the following expression I actually implemented it (initially) at least 2 different ways. In the manner above, I have 2 int(s) that keep a count of 1.) number of book titles 2.) number of times we’ve transitioned from the Successful branch and the No Data branch of the Step (we only want to do it once).  So when we are getting the number of Titles we aren’t actually setting the value of our container (sTitleArr). After this expression is run, there is a GoTo Step that returns execution of the Application back to the DB Get Step to get the next set of Data (move the cursor to the next row). Really fast, I want to talk about that Array and how to populate value into it.  The first time the No Data branch is taken the Array is initialized with a Size (length) which is set to the size of the TitleCounter integer:

sTitleArr = new String[ 2 ];
//In my code it is new String[ iTitleCounter ]
//The Value becomes
sTitleArr = new String[] {"",""};

In this case, (I know) there are 2 Titles returned with the dataset that I’m using a staticly entered value..but that is not the programmatic way of doing things unless we know the constant (final) value (size) of the container. Then, back to the Successful output branch each index of the container (Array) is given a value of each successive Title that is returned from the Database:

//Loop 1
sTitleArr[0] = sTitle;
//Loop 2
sTitleArr[1] = sTitle;

In all of the languages that I know a Container (e.g. List, Array) starts at position 0..and just as a quick tip..if you tried to set a value for [ 3 ] you would get an ArrayIndexOutOfBounds Exception.

So allow me to post a closeup of the next expression I need to explain: the No Data Output Branch.

nodataoutput_1

From the above, if this is the 2nd time we’ve hit this step then we know ‘we’re finished’ however if not (False) then we Initialize our Array (container) with the Number of Titles that were counted from the Successful branch; we then return our TitileCount back to zero and Increment our DataCounter by 1 (so that next time we hit this branch..we will return True from this IF statement) then we go back to the DB Read Step (this is critical..you can’t go to the Get step).

At this point, I’m going to stop with a video of the script during a reactive debug and you can see the titles that are populated in the container (Array). Let me know what you think..

return sam;

8 responses to “{uccx scripting: db get data}

  1. Sam – thanks for this write-up and all the other UCCX write-ups on your page. Question, in your example how would you take the next step and read back the array to the caller via a prompt? My situation is tracking numbers for shipments, so I will have letters and numbers in each row of the array that I need to re-back to the caller.

    Thanks again,
    Dan

    • I’ve written about this before..ultimately you are going to construct and concatenate your prompts which is certainly legal..
      To repeat back (spell) Tracking Numbers your prompt would look something on the lines of:

      String numOfShipments = ValueFromDB
      Prompt p = S[numOfShipments]

      And Concatenating prompts such as “There are” + p + “number of shipments in route” would be 3 prompts..

      p1 = P[thereare.wav]
      p2 = S[numOfShipments]
      p3 = P[shipmentsInRoute.wav]
      //DP puts alittle delay in the playback
      Prompt containerPrompt = p1 + DP[200] + p2 + DP[200] + p3

      • Actually I was looking at how to get the values within the array spoken. So my results come back and are stored in the array. How do you extract the results and readback the tracking number themselves? See screenshot of my failed attempt.

        Thanks,
        Dan

      • In Java the way to get to individual values of an array is by using Indexes. For example, the Size of the array in your example is 2 (you can get this by using Array.length). An Array’s Index starts at 0 so the first value of the Array is called by like this:

        String s = sTrackingNumbersArray[0]

        Then you can convert s to the Appropriate Prompt S[s]

        Hopefully this helps..there’s definitely much more I could break down..but I’m hoping based on what I’ve said..you can play around with the data now..and get it to work out the way you need it to (How many possible values could the Array Have (using length you can ensure your iteration is setup properly)? Are you going to have to set up some Sort of Iteration To Cycle Through the Array?..typically you use a Counter (integer) to Iterate over it where counter = 0; and the first element of the Array is Array[counter] then with iteration you Increment the counter by 1 (counter++) and use a go to to get to the next element in the Array..)

  2. This helps alot with my current project, thank you very much. One question, if I need data from two different tables, do I need to run DB read once for each table, or is it possible to get everything in one? Would I need to create a view or stored procedure for it?

    Finnur

    • Finnur..it’s been a while! Glad you have reached out..From my experience with the DB Get Step..you can only get Data from 1 Table at a time..so you’ll have to spin up another Read/Get..Hope this helps you out..

  3. Great Stuff on UCCX scripting. Although i read it for the first time but found very useful. Expecting such great stuff on this in future as well. Thanks SAM.

    Regards,
    Mukesh Singh

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s