UCCX DB Write step "" issue

dcscuba23 · ‎07-16-2012

Hi Guys,

I am running UCCX 8.5. I have identified what the issue is but can’t seem to figure out a way around it. I am doing a simple DB write step to a MSSQL 2005 server. The issue is the query fails when the VALUE is set to a string variable in my script. Since it is a string variable, CCX adds the double quotes around the value. When I run a trace on the SQL DB with SQL profiler, the insert fails because SQL will only accept a value with a single quote. If I change the VALUE in my script to let’s a char variable it works, because CCX populates the char variable value with a single quote and not double… Is there a trick to strip the double quotes off a string variable when trying to pass over to a DB for write steps? Any guidance would be deeply appreciated! Thanks!

Thanks,

Damon

Anthony Holloway · ‎07-16-2012

Hmmm, sounds like a defect, and you would be doing us all a huge service if you filed this with Cisco for proper analysis and then resolution.

With that said though, I think I hear you correctly, and can offer one possible untested solution.

Pass the quotes into the DB.

If your SQL query looks something like this today:

INSERT INTO table3 (name) VALUES ($name)

Then change it to:

INSERT INTO table3 (name) VALUES ('$name')

That could possibly encase the double quoted string variable in single quotes and pass the query validation, but now your DB holds a value with quotes in it. You could take it a step further and see if a SQL function could solve the problem, or just live with it.

E.g., This will find all double quotes in the string, and then remove them.

INSERT INTO table3 (name) VALUES (REPLACE('$name', '"', ''))

Good luck and happy bug reporting!

Anthony Holloway

Please use the star ratings to help drive great content to the top of searches.

EDIT: I had my SQL UPDATE and INSERT syntax mixed up. Corrected.

dcscuba23 · ‎07-16-2012

Hi Anthony,

Thank you for the suggestions. I did try your first suggestion before I posted to this forum . I just tried your REPLACE method and the editor complains about the syntax and throws an error. I will open up a TAC case and see what happens. Will update the thread if TAC or I come up with a solution.

Thanks,

Damon

Anthony Holloway · ‎07-16-2012

What was the outcome from your testing with the '$name' method?

Anthony Holloway

Please use the star ratings to help drive great content to the top of searches.

dcscuba23 · ‎07-16-2012

Hi Anthony,

My test is query is as follows:

INSERT INTO [TestCalls]

([FieldTest])

VALUES

('$strTestName')

With that syntax it fails with error:

This query is successfull if variable is type char:

INSERT INTO [TestCalls]

([FieldTest])

VALUES

($charTestName)

This query fails if variable is a string

INSERT INTO [TestCalls]

([FieldTest])

VALUES

($strTestName)

Running SQL profiler trace on the database shows the insert fails and the value being sent with "". Haven't heard back yet from my TAC case.

Anthony Holloway · ‎07-16-2012

Hmm... that's too bad. I assume you've searched the forum before posting? Have you also search the bug toolkit, or the TAC case collection? What about the cisco-voip mailing list?

Seriously, good luck!

Anthony Holloway

Please use the star ratings to help drive great content to the top of searches.

dcscuba23 · ‎07-16-2012

Yeah, I have scoured all possible forums, toolkits, and mailing lists for a solution but have come up blank.

Initial response from my TAC case is that it is working as designed..... Not what I wanted to hear. It sounds like I will have to come up with an alternate solution. All I want to accomplish is to write my populated variable ( grabbed from a DB Read and GET) information off somewhere for certain calls, after a "successful" branch operation for tracking purposes. I am now thinking I may need to do this by calling some web service or updating an xml file sitting on a web server.

Thanks,

Damon

Jonathan Schulenberg · ‎07-17-2012

This is an alarming response from TAC. I suggest you push back because the response doesn't pass the straight-face test. I mean honestly, this would make the CCX database integration essentially useless.

Your escalation options are:

1. Your Cisco AM. If you have a pending sale to use as leverage it will help motivate him/her.

2. The Technical Team Lead of your engineer.

3. A Cisco Duty Manager, available by calling CIN (+18005532447) and raising a fit.

Please remember to rate helpful responses and identify helpful or correct answers.

dcscuba23 · ‎07-17-2012

Hi Jonathan,

I agree. I have since pushed the issue and will see where that goes. Will send an update later once I get more info.

Thanks,

Damon

dcscuba23 · ‎07-18-2012

Hi Guys,

After another conversation with TAC, they have confirmed that UCCX is working as designed. However, TAC provided a work around due to this being an issue in SQL. There is a parameter in MSSQL that you you can change in order to accept doube quote values. Here is the info they provided:

“By default only single quoted strings are allowed to be entered into SQL database columns. Double quoted strings are used for identifiers and reserved words. This is because of an option "QUOTED IDENTIFIER" which is ON by default.

If you turn that OFF using command "SET QUOTED_IDENTIFIER OFF", you should be able to use double quoted string to enter values into database.

Here is a link I found explaing the SQL setting:

http://msdn.microsoft.com/en-us/library/ms174393.aspx

I will test my previous queries after this DB change is made and will let everybody know the results.

Thanks

Damon

Anthony Holloway · ‎07-19-2012

dcscuba23 wrote:

There is a parameter in MSSQL that you you can change in order to accept doube quote values.

PLEASE DO NOT CHANGE IT!

Do not allow Cisco to tell you to modify your database to accomodate poor programming on their part.

What about those customers who do not own the database they connect to? E.g., Third party data warehousing.

What about those customers whose internal DBA's will not budge with this setting because of interdependency issues? E.g., In house applications accessing the database. And if the setting change is possible, it could cause tens of thousands of dollars in man-hours for code rewrites, testing, and software deployment.

My point isn't that the setting is dangerous to MSSQL, my point is that Cisco has a defect, and they are avoiding resposibility. By you making the change to your DB and moving on, they will never fix it.

Quite simply, the quotes used to define a String literal are not a part of the String value itself, therefore they should not be passed to SQL as if they were.

Example:

String a = "word";
String b = "\"word\"";
(a == b); // False

Please do this for all of us! We're counting on you!

Anthony Holloway

Please use the star ratings to help drive great content to the top of searches.

orochi_yagami · ‎11-13-2012

Hi dscuba,

Did you manage to resolve the double quote issue with TAC?

regards,

3d_networks · ‎01-30-2013

Hi,

How did you manage to get this resolve? i'm still facing the problem even changed the database quote identifier to OFF.

Can shed some light on this?

TIA

dcscuba23 · ‎02-08-2013

Hi,

I was able to get this to work. My customer did not want to change the quote identifier setting on their database due to other customer applications hitting it ( we left the default setting of ON ), so with trial and error I was able to get this to work properly with the following query in my dbwrite step using my string variables:

INSERT INTO [HistoricalCalls]

([Location]

,[Source]

,[Tracking]

,[CallType])

VALUES

($strClientName, $strSource, $strTracking, $strCallType)

SET QUOTED_IDENTIFIER OFF;

This allowed the quoted parameter to be turned off on specific tables that I was inserting the data into. It worked liked a champ. I haven't tested this query on anything other than MSQL 2005, so I can't guarantee that it will work on other versions!

Hope this helps!

-D

joe.bennett · ‎09-22-2013

I'm having the same issue with passing a boolean, with SQL expecting single quotes and UCCX sending the variable unquoted. Reply from TAC so far has been 'as designed', but I'll let you know how I go.

Joe