When I last left you in regards to ‘UCCX DB Integration’ I was talking about the tools that help aid you when you have a project that calls for you to interact with an external DataBase within a UCCX Script. Ok, it has been over 2 months and I still haven’t posted a part 2 in this “sql” series. Last week a friend asked me to help get him started with integrating with an MS SQL Server because as he said it..’it has been a while since I last did this.’
Product: UCCX 8.x + with Premium License, MS SQL Server 2000
Scenario: You need to interact with a MS SQL Server.
Most of this post is going to be in screenshots..at this point there will not be any code (that I leave behind)..as I’m just going to focus on setting up the DB Subsystem and testing the DB Connection from there.. and from the tool I use, which I previously introduced, called RazorSQL. Let’s get started:
You need the Java Driver used for the JDBC connection: (Subsystems>Database>Driver)
My next step in this process is to access ‘the’ Table in the DB that I am interested in from Razor..just to ensure I “know” what I’m doing…below is a screenshot of that along with the SELECT statement:
Now that I know that works the first place to start in the UCCX Script in order for you to Get Data is you have to “connect” to the DB; you connect to the DB using the DB Read Step in the UCCX Editor; when you first connect to the DB using the step below, you need to Refresh the Database Schema (this is in the General tab below).
Next we are going to visit the Field Selection tab: Here, I am using the same SELECT statement I used when performing this in Razor; if you look close enough you can see that I returned 2 Rows of Data in this Test Connection..the same amount of Data returned within the RazorSQL Tool (at this point we know we are on the right track)
Going back to the Field Selection: In the previous Test Statement we used ‘Hard Coded’ data where variables will be used: the zipcode and the doctor category. So my next step is to replace these hardcoded (Strings) with variables from within my script:
This should get you off the ground when connecting to an MS SQL Server 2000-2008. Let me know what you think. Let me know if I can help you with SELECT statements..I am going to leave you with the currently supported driver for connecting with MS SQL Servers if you happen to need it HERE.