Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

DB Write Step Does Not Work in CCX Script Editor

Hello all!

I am trying to write some data in external database from CCX script, but "DB Write" step always goes to "SQL Error" although I am using correct SQL syntax. I have checked it through MS SQL Management studio and everything works.

Interesting is the fact, that "DB Read" step works fine!!!

Another weard thing is that making test from "DB Write" step properties shows "Rows altered 1"...

Nearby is commands I am trying to execute with no success:

delete from [Samsung-DK] where Question1=150

and

insert into Samsung-DK(Question1) values (200)

I will very appreciate any help or advice, because I cannot find any clue in Cisco manuals.

Thank you in advance.

33 REPLIES

DB Write Step Does Not Work in CCX Script Editor

Hi,

Please refer the section "DB Write Step" on page 202 onwards in the below document,

http://www.cisco.com/en/US/docs/voice_ip_comm/cust_contact/contact_center/crs/express_8_5/programming/guide/uccx851_step_ref.pdf

Hope it helps,

Anand

Please rate helpful posts..

New Member

DB Write Step Does Not Work in CCX Script Editor

Some quick advice, check formatting(especially spaces) and use variables. Try just the INSERT command first, then just the DELETE command and see which is broken.

DELETE FROM [Samsung-DK] WHERE Question1 = 150

INSERT INTO Samsung-DK(Question1) VALUES ($Var1)

-store 200 in variable Var1

New Member

DB Write Step Does Not Work in CCX Script Editor

Thank you, Raymon!

Your advise partially helped. Now I'm using following syntax:

INSERT INTO [Samsung-DK](Question1) VALUES ($Var1)

That is weard, it works in "Test" tab, but uploading script to CCX server this command does nothing. Do you have any clues?

New Member

DB Write Step Does Not Work in CCX Script Editor

Hi everyone!

I have found that was the problem and a solution for this. Decided to post solution here, just in case someone will experience the same.

The problem was that CCX Script editor can't interconnect different SQL version syntax. On the server, running CCX, I have MS SQL Server 2000, but on the server, running data base, MS SQL Server 2008 is installed. That is interesting, adding ODBC object successed through Administration Tools, but running SQL through scripts reverted to SQL error.

Solution is to install ODBC driver for SQL 2008, create new ODBC object with this driver and everything works with no problems.

New Member

DB Write Step Does Not Work in CCX Script Editor

Hi Andrejs and others,

Can you please elaborate the steps that how you create DSN and use DB Steps? as we are facing Issue in creating DSN that is required in DB Steps.

Our requirement is as under:

> We have to use DB Write component to log IP IVR related data in custom created tables.

In  my previous experience with UCCX 6.5/7, on Windows 2000 server and SQL  Server, I easily created DSN (using App Admin) that was then available  in DB Tools of CRS Editor.

Kindly help, with UCCX 8.5.1 on Linux and Informix Server, how can I use DB Write tool to use SQL Insert Command.

Kindly note that we have created custom Tables successfully.

Your prompt reply will help us to go forward.

Regards

Kashif Surhio

DB Write Step Does Not Work in CCX Script Editor

Hi,

you don't need to change anything in your UCCX scripts, DB write should work fine, unless you use a really exotic, version-specific SQL syntax.

On UCCX 8.5.1, you don't use DSN's. You have to download the JTDS JDBC driver (

http://sourceforge.net/projects/jtds/files/) and upload it to the UCCX using the appadmin web interface.

G.

New Member

DB Write Step Does Not Work in CCX Script Editor

Hi Gergely

UCCX veriosn 8.5(1)

We want to write/ insert the data from script editor using DBWrite method,  We have created the DSN to the external database, external database is Oracle 10g, the connection is successfully established and tested from Cisco UCCX Datasource web page. But when  we write the Insert statement into the script there is no sysntax error, but the data is not inserted into the database, when we test the query it said 0 rows affedted, we build the query and its syntax is correct as editor save the script successfully. In the database the data is not inserted, but when we delete the data through script editor it says 1 row afftected, so i am confused why data is not inserted into the database table.

Kindly let us know what to do and how to investigate further.

Regards

Adeel Aslam

DB Write Step Does Not Work in CCX Script Editor

Hi,

the test function of the DB Write step does not actually write.

Did you try running your script in reactive debugging mode?

Also, please do post your SQL statement - might be a problem with it. And a screenshot of the relevant parts of the DB Write step.

G.

New Member

DB Write Step Does Not Work in CCX Script Editor

Hi Gergely

Thanks for your prompt reply.

1. Insert Query

INSERT INTO BALANCEINQUIRY (NEWID) VALUES('3')

2. Yes, trying to run the script in the debugging mode and there is no error.

3. At this time i am unable to send you the screen shot,

Kindly let me know if i am missing anything.

Regards

Adeel Aslam

DB Write Step Does Not Work in CCX Script Editor

What is the data type of BALANCEINQUIRY.NEWID? Is it VARCHAR or INT?

G.

New Member

DB Write Step Does Not Work in CCX Script Editor

Hi Gergely

We are using Oracle its Number Type

DB Write Step Does Not Work in CCX Script Editor

And if you try

INSERT INTO BALANCEINQUIRY (NEWID) VALUES(3)

?

New Member

DB Write Step Does Not Work in CCX Script Editor

Same result. record not inserted into table

DB Write Step Does Not Work in CCX Script Editor

Alright, can you enable the traces for the database subsystem and database steps, retry the query and then observe the output of catalina.out ?

G.

New Member

DB Write Step Does Not Work in CCX Script Editor

Hi Gergely

I did not get this point, can you kindly expalin me in details.

DB Write Step Does Not Work in CCX Script Editor

Er... what is it you don't understand?

New Member

DB Write Step Does Not Work in CCX Script Editor

Alright, can you enable the traces for the database subsystem and  database steps, retry the query and then observe the output of  catalina.out ?

What do you mean by database subsytem and database steps. you are talking about Cisco Script Editor.

How we can observer the catalina.out output?

DB Write Step Does Not Work in CCX Script Editor

I see.

No, it's the UCCX web administration page, CCX Serviceability (aka "uccxservice"), Trace > Configuration, enable Debugging for LIB_DATABASE, DB_MGR, STEPS_DB, SS_DB.

Install the Real Time Monitoring Tool - RTMT (Tools > Plugins on the CCX Administration aka "appadmin" page). Click Trace & Log Central, then Collect files. Check all. Download it as zipped files. Zip it up. Post [servername]_CiscoTomcatLogs*.zip and [servername]_*MIVR*.zip.

G.

New Member

DB Write Step Does Not Work in CCX Script Editor

Hi  Gergely Szabo

Thanks for your reply. i am sorry not replying to you on time,

Gergely i have checked and inserted the data from scrip DbWrite method, the query having all static data is inserted successfully into the database.

but now when i use the same query and pass the script variable into the query then it gives me SQL Error

find my query for your reference

INSERT INTO BALANCEINQUIRY(NEWID,CNIC,DATEANDTIME,ANI,TRANSACTIONSTATUS,ACCOUNTNUMBER) VALUES ('7',s_cnic,SYSDATE,'1212','000: Processed Ok','0073900001')

s_cnic is variable defined in the scrip editor, amd value is '4130313304985'

find the attached image also.-

Need your help in this regard.

Thanks

Adeel Aslam

DB Write Step Does Not Work in CCX Script Editor

Hi,

could you modify the query: use $s_cnic instead of just s_cnic.

G.

New Member

DB Write Step Does Not Work in CCX Script Editor

Hi Gergely

Thanks for your reply, I have checked this one also (placing the $ sign before s_cnic)  but its not working and giving the error. So Kindly advice me what to do.

Regards

Adeel Aslam

DB Write Step Does Not Work in CCX Script Editor

What error?

New Member

DB Write Step Does Not Work in CCX Script Editor

SQL Syntax Error

New Member

DB Write Step Does Not Work in CCX Script Editor

Also Array Index Out of Bound IO Exception.

DB Write Step Does Not Work in CCX Script Editor

Hi, what happens if you try to squeeze the whole SQL statement on one line?

I see there are two lines now (the second starting with the word values). I am afraid UCCX detects this as two separate statements.

G.

New Member

DB Write Step Does Not Work in CCX Script Editor

I tried to use only one paramter also but the behaviour is same, So its not occuring due to 2 lines in Script editor.

Which log file of UCCX will give us  idea about this problem / behaviuor.

Thanks

Adeel Aslam

DB Write Step Does Not Work in CCX Script Editor

Already told you how to collect logs.

G.

New Member

Re: DB Write Step Does Not Work in CCX Script Editor

Gergely Szabo wrote:

Already told you how to collect logs.

G.

Thanks for reply, find the attached files for your review. Kindly provide your feedback so that i can move forward.

New Member

DB Write Step Does Not Work in CCX Script Editor

Dear Gergely

From the MIVR Logs , found this error

1636072: Feb 14 13:24:35.455 PKT %MIVR-SS_DB-7-UNK:SS_EDBS::checkConnectivityForDSN: dibuat

1636073: Feb 14 13:24:35.455 PKT %MIVR-SS_DB-7-UNK:EDBS Subsystem is initialized properly......

1636074: Feb 14 13:24:35.455 PKT %MIVR-SS_DB-7-UNK:EDBS_DS:MaxActive cnxs: 16

1636075: Feb 14 13:24:35.455 PKT %MIVR-SS_DB-7-UNK:EDBS_DS:NumActive cnxs: 0

1636076: Feb 14 13:24:35.455 PKT %MIVR-SS_DB-7-UNK:EDBS_DS:Connection request IN {

1636077: Feb 14 13:24:35.455 PKT %MIVR-SS_DB-7-UNK:EDBS_DS:Connection request OUT } jdbc:oracle:thin:@//172.16.24.154:1521/dib, UserName=DIBIVR, Oracle JDBC driver

1636078: Feb 14 13:24:35.461 PKT %MIVR-SS_DB-7-UNK:Leaving method checkConnectivityForDSN: isBadConnection returns :: false

1636079: Feb 14 13:24:35.461 PKT %MIVR-SS_DB-7-UNK:SS_EDBS::Checked the connectivity for all the DSNs

1636080: Feb 14 13:24:35.599 PKT %MIVR-STEPS_DB-7-UNK:Task:53000000032 DB Write Step Execution

1636081: Feb 14 13:24:35.599 PKT %MIVR-STEPS_DB-7-UNK:Task:53000000032 DB Resource: ss

1636082: Feb 14 13:24:35.599 PKT %MIVR-STEPS_DB-7-UNK:Task:53000000032 Data Source Name: DIB

1636083: Feb 14 13:24:35.599 PKT %MIVR-STEPS_DB-7-UNK:Task:53000000032 SQL Command: INSERT INTO BALANCEINQUIRY (NEWID,CNIC,DATEANDTIME,ANI,TRANSACTIONSTATUS,ACCOUNTNUMBER)

values ('7',$S_CNIC,SYSDATE,'1212','000: Processed Ok','0073930001')

1636084: Feb 14 13:24:35.599 PKT %MIVR-SS_DB-7-UNK:EDBS Subsystem is initialized properly......

1636085: Feb 14 13:24:35.599 PKT %MIVR-SS_DB-7-UNK:EDBS_DS:MaxActive cnxs: 16

1636086: Feb 14 13:24:35.599 PKT %MIVR-SS_DB-7-UNK:EDBS_DS:NumActive cnxs: 1

1636087: Feb 14 13:24:35.599 PKT %MIVR-SS_DB-7-UNK:EDBS_DS:Connection request IN {

1636088: Feb 14 13:24:35.599 PKT %MIVR-SS_DB-7-UNK:EDBS_DS:Connection request OUT } jdbc:oracle:thin:@//172.16.24.154:1521/DIB, UserName=DIBIVR, Oracle JDBC driver

1636089: Feb 14 13:24:35.599 PKT %MIVR-STEPS_DB-7-UNK:Task:53000000032 Checkout success for Database: DIB, eCon: 117996

1636090: Feb 14 13:24:35.599 PKT %MIVR-STEPS_DB-7-UNK:DBWriteStep: tokenizedSQL = INSERT INTO BALANCEINQUIRY (NEWID,CNIC,DATEANDTIME,ANI,TRANSACTIONSTATUS,ACCOUNTNUMBER)

values ('7',, startIndex = 101

1636091: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-UNK:DBWriteStep: varName = S_CNIC; tokenIndex = 107

1636092: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-UNK:Task:53000000032 Exception: : Exception=java.lang.ArrayIndexOutOfBoundsException: -1

1636093: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:java.lang.ArrayIndexOutOfBoundsException: -1

1636094: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at java.util.Vector.elementAt(Vector.java:430)

1636095: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at com.cisco.wfframework.obj.WFWorkflowContext.getValue(WFWorkflowContext.java:216)

1636096: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at com.cisco.wf.steps.ivr.DBWriteStep.parseSQL(DBWriteStep.java:439)

1636097: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at com.cisco.wf.steps.ivr.DBWriteStep.execute(DBWriteStep.java:279)

1636098: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at com.cisco.wfframework.obj.WFBeanStep.executeImpl(WFBeanStep.java:141)

1636099: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at com.cisco.wfframework.obj.WFStep.execute(WFStep.java:174)

1636100: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at com.cisco.wfframework.obj.WFWorkflowTask.executeStep(WFWorkflowTask.java:494)

1636101: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at com.cisco.wfframework.engine.core.WFEngineWorkflowTask.executeStep(WFEngineWorkflowTask.java:122)

1636102: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at com.cisco.app.impl.WFWorkflowAppDebugTaskWrapper.executeStep(WFWorkflowAppDebugTaskWrapper.java:416)

1636103: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at com.cisco.wfframework.engine.core.WFEngineWorkflowDebugTask.step(WFEngineWorkflowDebugTask.java:125)

1636104: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at com.cisco.wfframework.engine.rmi.RemoteWorkflowTaskImpl.stepAndReturnConnection(RemoteWorkflowTaskImpl.java:121)

1636105: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at com.cisco.app.impl.ApplicationManagerImpl$2$2.stepAndReturnConnection(ApplicationManagerImpl.java:833)

1636106: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at sun.reflect.GeneratedMethodAccessor286.invoke(Unknown Source)

1636107: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

1636108: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at java.lang.reflect.Method.invoke(Method.java:597)

1636109: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:303)

1636110: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at sun.rmi.transport.Transport$1.run(Transport.java:159)

1636111: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at java.security.AccessController.doPrivileged(Native Method)

1636112: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at sun.rmi.transport.Transport.serviceCall(Transport.java:155)

1636113: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:535)

1636114: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:790)

1636115: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:649)

1636116: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

1636117: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

1636118: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-7-EXCEPTION:    at java.lang.Thread.run(Thread.java:662)

1636119: Feb 14 13:24:35.600 PKT %MIVR-STEPS_DB-3-EXCEPTION_OCCURED:EXCEPTION_OCCURED

2522
Views
5
Helpful
33
Replies