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

a_cvetkovs · ‎02-12-2012

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.

madeelaslam · ‎01-31-2013

Hi Gergely

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

Gergely Szabo · ‎01-31-2013

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

madeelaslam · ‎01-31-2013

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?

Gergely Szabo · ‎01-31-2013

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.

madeelaslam · ‎02-14-2013

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

Gergely Szabo · ‎02-14-2013

Hi,

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

G.

madeelaslam · ‎02-14-2013

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

Gergely Szabo · ‎02-14-2013

What error?

madeelaslam · ‎02-14-2013

SQL Syntax Error

madeelaslam · ‎02-14-2013

Also Array Index Out of Bound IO Exception.

Gergely Szabo · ‎02-14-2013

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.

madeelaslam · ‎02-14-2013

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

Gergely Szabo · ‎02-14-2013

Already told you how to collect logs.

G.

madeelaslam · ‎02-14-2013

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.

madeelaslam · ‎02-14-2013

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