« Back to Contact Center Express Scripting Questions

UCCX Script - DBWRITE STEP NOT WORKING

Combination View Flat View Tree View
Threads [ Previous | Next ]
UCCX Script - DBWRITE STEP NOT WORKING
Answer
3/19/13 12:43 AM
Hi folks,
I'm trying to write a record on the SQL Server 2005, but I'm getting a trouble to date field as below:
 
INSERT INTO CC_PESQUISA_SATISFACAO (CALLING_NUMBER,CALLED_NUMBER,AGENT_ID,CALL_DATE,QUEUE_NAME,SCORE) VALUES ( 8092 , 562426 , 'jisilva' , Mon Mar 18 20:02:25 BRT 2013 , 'CSQ_CC_UNIMED_EXM_EXAMES' , 5 )
 
java.sql.SQLException: The name "Mon" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
 
SQL: INSERT INTO UNIMED_CC_PESQUISA_SATISFACAO (CALLING_NUMBER,CALLED_NUMBER,AGENT_ID,CALL_DATE,QUEUE_NAME,SCORE) VALUES ($INT_CALLING_NUMBER,$INT_CALLED_NUMBER,$STR_AGENT_ID,$DT_CallDate,$STR_QUEUE_NAME,$INT_SCORE)
 
How I can convert the date variable to a format that is accepted by MS SQL 2005 and UCCX?
 
 
Thank you
Flag

RE: UCCX Script - DBWRITE STEP NOT WORKING
Answer
3/19/13 9:06 AM as a reply to Jefferson Islan Silva.
 
Hi Jefferson Islan Silva
Follow the below guidelines in your script
a)The entries in the db are in the form of "mm/dd/yyyy".
ie. 3/19/2013
b)Create A variable of type Date getTodaysDate variable
c)Use Set to access each field Set getTodaysDate = new java.util.Date()->It will return D["March 19, 2013 12:41:26 PM MSK", TZ[XX/XX]],Set monthInt = getTodaysDate.getMonth(),Set day =  getTodaysDate.getDate(), Set year = getTodaysDate.getYear()
d)Use increment step to increments our monthInt variable by 1.  We do this because Java returns the month as an integer value in the range of 0-11 instead of 1-12.  So again, we are transforming the values into a more user-friendly format
e)Use Set  step sets the yearInt variable to the current value of yearInt + 1900.  This is due to Java returning the year as the number of years since 1900, for example “104” for 2004.
f)Use toString() for each  the first of three steps used to convert the parts of the date into Strings.  This will allow us to concatenate the pieces of the date into a usable format.  This step converts the monthInt variable into the “monthString” String variable.
g)Use Set  step step uses the Set function to concatenate our individual String variables into a usable format with “/” separators.  Essentially, we now end up with a String variable “todaysDateString” which is something like “6/12/2004”.
h)Use $todaysDateString in your sql statement as below
 
INSERT INTO CC_PESQUISA_SATISFACAO (CALLING_NUMBER,CALLED_NUMBER,AGENT_ID,CALL_DATE,QUEUE_NAME,SCORE) VALUES ( 8092 , 562426 , 'jisilva' , $todaysDateString, 'CSQ_CC_UNIMED_EXM_EXAMES' , 5 )
 
Flag