cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
1500
Views
15
Helpful
11
Replies

CUDLI - Stored Procedure

chad_meyer
Level 1
Level 1

 We are onboarding 1000+ voicemail boxes multiple times and am looking for a way to bulk update the SMTP Subject in notifications.  There is no BAT field that allows this field to be updated for some reason and am looking to execute this via CUDLI.  I found that tbl_notificationdevicesmtp.statictext is the table/column I need to update and am able to do so via a direct UPDATE command...which I have since found out this is highly NOT recommended due to foreign key constraints with the new Unity Connection DB structure.  I am looking into a stored procedure to execute this, however am not sure how to accomplish this.  I see the csp_notificationdevicesmtpmodify procedure but not sure how I can alter it to allow a quick update of all fields.  Any guidance would be extremely appreciated.  Thank you.

11 Replies 11

lindborg
Cisco Employee
Cisco Employee

Hey Chad.

So yeah - updating tables directly hasn't been a good idea on any version of Unity Connection - the number of FK constraints and rules are huge and, of course, if TAC catches you doing that in the logs, you're going to be out in the cold - definitely don't do that.

Stored procs are designed to update one call at a time - you can't execute a single stored proc to update all notification devices in one shot.  However, I did make a tool designed to help with just this by allowing you to do simple Python scripting to do iterations over a set of objects and execute stored procs for them - the Python Scripting Host can be found here: http://www.ciscounitytools.com/Applications/CxN/PythonScriptingHost/PythonScriptingHost.html

You'll see some examples out there, look at the "Change Conversation For All Users in COS" example on the home page and you'll get the idea how to do this I think.

First, get the ObjectIds of all the active SMTP devices (assuming this is what you want to do) - the query would look like this:

select objectid from vw_notificationdevicesmtp where displayname='SMTP' and active=1

that gets stored in a one column data table as in the COS update example, then you iterate over each ObjectId in the list and run a stored proc that looks like this:

execute procedure csp_notificationdevicesmtpmodify (pObjectId='11cb177c-9816-47b1-820d-2c572f6caf23',pStaticText='blah blah'::lvarchar)

in the script that gets broken up into starting a command and adding parameters like this:

Helpers.StartNewCommand('csp_NotificationDeviceSmtpModify')
Helpers.AddCommandParam('pObjectId',ProcDataType.Char,user['objectId'])
Helpers.AddCommandParam('pStaticText',ProcDataType.VarChar,'blah blah')

Of course there's lots more you can do with Python scripts there but that's the basics.  The training videos out there walk you through a number of the more common scenarios as well.

-Jeff

 

Thanks for the detailed response Jeff.  This seems like a pretty powerful solution.  Would it be possible to get an output of the phonenumber and objectid fields from the vw_notificationdevicesmtp where phonenumber is not null?  With the following code I am receiving an "Execution Complete" in the Results pane with no entries showing in the Output viewer:

 

import clr 
clr.AddReference('System.Data')
clr.AddReference('Cisco.Unity.Connection.PythonScriptingHost')
from System.Data import DataTable
from System import DateTime
from Cisco.Unity.Connection.PythonScriptingHost import ProcDataType

Helpers.SetActiveDatabase('UnityDirDb')

query = 'SELECT ObjectId, phonenumber FROM vw_Notificationdevicesmtp WHERE phonenumber is not NULL'
dtDevices = Helpers.CreateDataTable(query)

assert dtDevices.Rows.Count>0,"No notification devices found!"

Any thoughts? 

Update.  I rewrote the app to import via CSV file.  The app is launching and reading all entries, however nothing is being updated on the server.  Current app source:

import clr 
clr.AddReference('System.Data') 
from System.Data import DataTable 
clr.AddReference('Cisco.Unity.Connection.PythonScriptingHost')
from Cisco.Unity.Connection.PythonScriptingHost import ProcDataType
from datetime import datetime 

Helpers.SetActiveDatabase("UnityDirDb")

#create simple function to print out to form and to write to log file
def myLog(str):
    print str
    Helpers.AppendToFile('ModifyNotificationDevices.log',datetime.now().strftime("[%m-%d-%Y %H:%M:%S]:") + str)
    return

myLog("** Starting import of users from CSV **")
myLog("Reading users from Csv")
dtUsers = Helpers.ReadCsvFileIntoDataTable('statictextimport.csv')
myLog("Users from Csv="+ str(dtUsers.Rows.Count))

for user in dtUsers.Rows:
    myLog(user['ObjectId'])
    Helpers.StartNewCommand('csp_NotificationDeviceSMTPModify')
    Helpers.AddCommandParam('pObjectId',ProcDataType.Char,'objectid')
    Helpers.AddCommandParam('pStaticText',ProcDataType.LVarChar,str('statictext'))
    Helpers.ExecuteProc_NoRet()

Looking in the PythonScriptingHost log I am showing the following error:

(error) Could not get lock to db access in CallStoredProc

 

Anyone have any ideas on this?

HeyChad - sorry, missed the follow ups here.

I'll take a look at this later today or tomorrow AM - have to finish up some work on another project first.  

actually, looks pretty simple - you were passing the static string 'objectid' into the stored proc objectID for the device reference - you wanted to pull the row objectId from the table.  I tried this against my 12.5 and 11.0 servers and it works ok:

 

import clr 
clr.AddReference('System.Data')
clr.AddReference('Cisco.Unity.Connection.PythonScriptingHost')
from System.Data import DataTable
from System import DateTime
from Cisco.Unity.Connection.PythonScriptingHost import ProcDataType

Helpers.SetActiveDatabase('UnityDirDb')

query = 'SELECT ObjectId FROM vw_Notificationdevicesmtp WHERE phonenumber is not NULL'
dtDevices = Helpers.CreateDataTable(query)

assert dtDevices.Rows.Count>0,"No notification devices found!"
print("Devices ="+ str(dtDevices.Rows.Count))

for device in dtDevices.Rows:
    print("Device="+device['objectid'])
    Helpers.StartNewCommand('csp_NotificationDeviceSMTPModify')
    Helpers.AddCommandParam('pObjectId',ProcDataType.Char,device['ObjectId'])
    Helpers.AddCommandParam('pStaticText',ProcDataType.LVarChar,'testing')
    Helpers.ExecuteProc_NoRet()

Jeff,

Apologies but I am not seeing what you are referencing.  I am trying to update the statictext field based on objectID via CSV file.  It looks like your previous response is looking up all objectIDs and updating all to say 'testing'.  Here is what I am seeing in the logs:

 

[Thread 006], [19/04/01 01:31:59], (error) Could not get lock to db access in StartNewCommand
[Thread 006], [19/04/01 01:31:59], (warning) duplicate parameter may be included:pObjectId=1c21baa3-5aeb-43c7-bb1d-b36598285f6f, in full command= execute procedure csp_NotificationDeviceSMTPCreate ( pObjectId='1c21baa3-5aeb-43c7-bb1d-b36598285f6f',pStaticText='Chad Meyer'::lvarchar,pObjectId='1c21baa3-5aeb-43c7-bb1d-b36598285f6f',pStaticText='Chad Meyer'::lvarchar,pObjectId='1c21baa3-5aeb-43c7-bb1d-b36598285f6f',pStaticText='Chad Meyer'::lvarchar,
[Thread 006], [19/04/01 01:31:59], (warning) duplicate parameter may be included:pStaticText=Chad Meyer, in full command= execute procedure csp_NotificationDeviceSMTPCreate ( pObjectId='1c21baa3-5aeb-43c7-bb1d-b36598285f6f',pStaticText='Chad Meyer'::lvarchar,pObjectId='1c21baa3-5aeb-43c7-bb1d-b36598285f6f',pStaticText='Chad Meyer'::lvarchar,pObjectId='1c21baa3-5aeb-43c7-bb1d-b36598285f6f',pStaticText='Chad Meyer'::lvarchar,pObjectId='1c21baa3-5aeb-43c7-bb1d-b36598285f6f',

how you get the objectIds in question isn't important - I just used a table fetch to make my sample usable stand alone without having to create a csv.

 

you have a bug in your code.

 

Here's your code chunk in quesiton

for user in dtUsers.Rows:
    myLog(user['ObjectId'])
    Helpers.StartNewCommand('csp_NotificationDeviceSMTPModify')
    Helpers.AddCommandParam('pObjectId',ProcDataType.Char,'objectid')
    Helpers.AddCommandParam('pStaticText',ProcDataType.LVarChar,str('statictext'))
    Helpers.ExecuteProc_NoRet()

the line "Helpers.AddCommandParam('pObjectId',ProcDataType.Char,'objectid')" is passing in the string 'objectid' as the object id - you want that to be "user['ObjectId']" to pull it from the table.  

Jeff,

This is still not working for me on my 11.5 system.  Code was altered per your instruction.  Log files are pretty blank now after this change and am showing this executing successfully.  I verified the correct objectID was being called to update as well.  Any thoughts?

 

import clr 
clr.AddReference('System.Data') 
from System.Data import DataTable 
clr.AddReference('Cisco.Unity.Connection.PythonScriptingHost')
from Cisco.Unity.Connection.PythonScriptingHost import ProcDataType
from datetime import datetime 

Helpers.SetActiveDatabase("UnityDirDb")

dtUsers = Helpers.ReadCsvFileIntoDataTable('import.csv')

for user in dtUsers.Rows:
    print ('updating:'+user['objectid'])
    Helpers.StartNewCommand('csp_NotificationDeviceSMTPModify')
    Helpers.AddCommandParam('pObjectId',ProcDataType.Char,"user['objectid']")
    Helpers.AddCommandParam('pStaticText',ProcDataType.LVarChar,user['statictext'])
    Helpers.ExecuteProc_NoRet

You're still passing in a string there, not the value from the table... ,"user['objectid']" instead of user['objectid'] passes the literal string "user['objectid']" instead of the value from the table.

...you're also missing the parens on the strored proc call there.

Not sure how to help you further- I've provided working code, the tool is not the problem here.

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: