{uccx scripting: db subsystem setup}

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)
UploadMSSQLDriver
AfterJDBCDriverUpload

Add the Data Source: (Subsystems>Database>Data Source) and Testing the Connection…
TestDataSourceGUI

From the UCCX Appadmin perspective we are done now. We could go to UCCX Serviceability to check the status of our DB Subsystem..it should show that it is In Service from the Engine’s perspective:
DB_SubSystemInService

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:
RazorSql_ReadDBSelect

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).

DBReadScr_RefreshDB

Once refreshed you will see the Data Source Name: (don’t worry about the Timeout value at this point)
DBReadScr_General

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)

DBRead_TestFieldSelect

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:

DBReadTestFieldSel_Variables

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.

return sam;

One response to “{uccx scripting: db subsystem setup}

  1. I am just a beginner in the UCCX world although I can describe you explanation as short and clean.
    it was very useful and targeted.
    thank you so much

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