07-22-2020 03:06 PM
Hello All,
Error Received During Reactive Debug: "null; nested exception is: java.lang,ArrayIndexOutOfBoundsException"
I'm testing a Post Call Survey Script. I got the Datasource configured in UCCX and Test Connection is successful. I can also select the Datasource and see the fields for that Table inside the DB Write step when I select the Table from "Show all fields (select table)" dropdown box.
When I attempt to Execute SQL statement under the Test tab, I receive an error (*Remote Error: java.lang.NullPointerException). The Test Statement is below. If I copy and paste this exact statement and run it under RazorSQL DB browser it successfully writes it to the database.
INSERT INTO PostCallSurvey.dbo.SurveyResults(CallerID,AgentExt,Q1Answer,Q2Answer) VALUES('5558675309','3233',5,'Yes')
The SQL Tab of the DB Write step has the following:
INSERT INTO PostCallSurvey.dbo.SurveyResults(CallerID,AgentExt,Q1Answer,Q2Answer) VALUES('$CallerID','$AgentExt',$Q1Answer,'$Q2Answer')
Database Fields:
Error Message:
Any idea what I'm doing wrong?
Thanks in Advance,
Matt
Solved! Go to Solution.
07-24-2020 09:57 AM
07-22-2020 03:58 PM
07-23-2020 08:21 AM
07-23-2020 09:56 AM
07-23-2020 10:13 AM
For DB_SS and DB_STEP I checked them from Debugging to XDebugging2. Not sure if I need to go higher or not. But here's what I found in the log:
817662075: Jul 23 12:33:27.119 EDT %MIVR-ENG-7-UNK:Execute step of Task 33000200680 : DB Write: resource: WriteSurvey, database: PostCallSurvey_DS /* Write results to SQL ... */ 817662076: Jul 23 12:33:27.119 EDT %MIVR-STEPS_DB-7-UNK:Task:33000200680 DB Write Step Execution 817662077: Jul 23 12:33:27.119 EDT %MIVR-STEPS_DB-7-UNK:Task:33000200680 DB Resource: WriteSurvey 817662078: Jul 23 12:33:27.119 EDT %MIVR-STEPS_DB-7-UNK:Task:33000200680 Data Source Name: PostCallSurvey_DS 817662079: Jul 23 12:33:27.119 EDT %MIVR-STEPS_DB-7-UNK:Task:33000200680 SQL Command: INSERT INTO PostCallSurvey.dbo.SurveyResults(CallerID,AgentExt,Q1Answer,Q2Answer) VALUES('$CallerID','$AgentExt','$Q1Answer','$Q2Answer') 817662080: Jul 23 12:33:27.119 EDT %MIVR-SS_DB-7-UNK:EDBS Subsystem is initialized properly...... 817662081: Jul 23 12:33:27.119 EDT %MIVR-SS_DB-7-UNK:EDBS_DS:MaxActive cnxs: 50 817662082: Jul 23 12:33:27.119 EDT %MIVR-SS_DB-7-UNK:EDBS_DS:NumActive cnxs: 0 817662083: Jul 23 12:33:27.119 EDT %MIVR-SS_DB-7-UNK:EDBS_DS:Connection request IN { 817662084: Jul 23 12:33:27.124 EDT %MIVR-SS_DB-7-UNK:EDBS_DS:Connection request OUT } jdbc:jtds:sqlserver://SQL2016.domain.com:1433/PostCallSurvey, UserName=UCCX, jTDS Type 4 JDBC Driver for MS SQL Server and Sybase 817662085: Jul 23 12:33:27.124 EDT %MIVR-STEPS_DB-7-UNK:Task:33000200680 Checkout success for Database: PostCallSurvey_DS, eCon: 5142 817662086: Jul 23 12:33:27.124 EDT %MIVR-STEPS_DB-7-UNK:DBWriteStep: tokenizedSQL = INSERT INTO PostCallSurvey.dbo.SurveyResults(CallerID,AgentExt,Q1Answer,Q2Answer) VALUES(', startIndex = 91 817662087: Jul 23 12:33:27.124 EDT %MIVR-STEPS_DB-7-UNK:DBWriteStep: varName = CallerID'; tokenIndex = 100 817662088: Jul 23 12:33:27.125 EDT %MIVR-STEPS_DB-7-UNK:Task:33000200680 Exception: : Exception=java.lang.ArrayIndexOutOfBoundsException 817662089: Jul 23 12:33:27.125 EDT %MIVR-STEPS_DB-7-EXCEPTION:java.lang.ArrayIndexOutOfBoundsException 817662090: Jul 23 12:33:27.126 EDT %MIVR-STEPS_DB-3-EXCEPTION_OCCURED:EXCEPTION_OCCURED
The Array of data being passed definitely doesn't look right. The line numbered 817662086, doesn't look right.
Do Integer values being passed need to be quoted, or just strings? It's basically (String, String, Int, String). I hardcoded the AgentExt Value since I'm calling the script directly and there wouldn't be an AgentExt getting passed to this script right now.
Thanks,
Matt
07-23-2020 10:54 AM
07-23-2020 11:32 AM
07-23-2020 12:56 PM - edited 07-23-2020 01:04 PM
Generally speaking:
Interestingly, the Set step is doing something for us implicitly, which is Type Casting (aka Type Conversion). This is where you assign a String like "3" to an int variable, and the Set step will attempt to change it to a valid int for you and then make the assignment happen.
This is pretty useful in the instance where you want to read the contents of a file simply by assigning a String to the value of a Document, in which case the Set step will read the contents of the Document and assign the String the content.
E.g.,
Set file_content = DOC[file1.txt] Set http_response = URL[https://ifconfig.me/ip]
07-23-2020 02:52 PM
Thanks for the explanation. Much appreciated.
So it seems like the issue is somewhere possibly in the variable assignment.
I edited the DB Write Step.
I changed it from this:
INSERT INTO SurveyResults(CallerID,AgentExt,Q1Answer,Q2Answer,DateTime) VALUES ('$CallerID','$AgentExt',$Q1Answer,'$Q2Answer',$time)
To This:
INSERT INTO PostCallSurvey.dbo.SurveyResults(CallerID,AgentExt,Q1Answer,Q2Answer,DateTime) VALUES ('5551239876','3233',5,'Yes',2100183939)
I did this to see if the variables themselves were the issue, and this WORKED... So what is the problem with the "VALUES" part of the sql when using variables instead of hard-coded values? Even hard coding values into the variables failed as well.
-Matt
07-23-2020 01:30 PM - edited 07-23-2020 01:33 PM
FYI... I just added a new Column to the Database table for a date timestamp, which I'm saving as an integer.
Do you spot anything wrong with the info below?
DB Fields:
Variables During Reactive Debug, right at DB Write step:
DB Write SQL Tab:
INSERT INTO SurveyResults(CallerID,AgentExt,Q1Answer,Q2Answer,DateTime) VALUES ('$CallerID','$AgentExt',$Q1Answer,'$Q2Answer',$time)
*Manually running this query works just fine if I replace the variables above in the query with the same values from the variables screenshot above, it updates the Table just fine
UCCX Debugs:
Current step: DB Write: resource: WriteSurvey, database: PostCallSurvey_DS <<<<< 819700776: Jul 23 15:27:10.773 EDT %MIVR-ENG-7-UNK:Execute step of Task 33000201928 : DB Write: resource: WriteSurvey, database: PostCallSurvey_DS 819700777: Jul 23 15:27:10.773 EDT %MIVR-STEPS_DB-7-UNK:Task:33000201928 DB Write Step Execution 819700778: Jul 23 15:27:10.773 EDT %MIVR-STEPS_DB-7-UNK:Task:33000201928 DB Resource: WriteSurvey 819700779: Jul 23 15:27:10.773 EDT %MIVR-STEPS_DB-7-UNK:Task:33000201928 Data Source Name: PostCallSurvey_DS 819700780: Jul 23 15:27:10.773 EDT %MIVR-STEPS_DB-7-UNK:Task:33000201928 SQL Command: INSERT INTO SurveyResults(CallerID,AgentExt,Q1Answer,Q2Answer,DateTime) VALUES ('$CallerID','$AgentExt',$Q1Answer,'$Q2Answer',$time) 819700781: Jul 23 15:27:10.773 EDT %MIVR-SS_DB-7-UNK:EDBS Subsystem is initialized properly...... 819700782: Jul 23 15:27:10.773 EDT %MIVR-SS_DB-7-UNK:EDBS_DS:MaxActive cnxs: 50 819700783: Jul 23 15:27:10.773 EDT %MIVR-SS_DB-7-UNK:EDBS_DS:NumActive cnxs: 0 819700784: Jul 23 15:27:10.773 EDT %MIVR-SS_DB-7-UNK:EDBS_DS:Connection request IN { 819700785: Jul 23 15:27:10.774 EDT %MIVR-SS_DB-7-UNK:EDBS_DS:Connection request OUT } jdbc:jtds:sqlserver://SQL2016.domain.com:1433/PostCallSurvey, UserName=UCCX, jTDS Type 4 JDBC Driver for MS SQL Server and Sybase 819700786: Jul 23 15:27:10.774 EDT %MIVR-STEPS_DB-7-UNK:Task:33000201928 Checkout success for Database: PostCallSurvey_DS, eCon: 5838 819700787: Jul 23 15:27:10.775 EDT %MIVR-STEPS_DB-7-UNK:DBWriteStep: tokenizedSQL = INSERT INTO SurveyResults(CallerID,AgentExt,Q1Answer,Q2Answer,DateTime) VALUES (', startIndex = 82 819700788: Jul 23 15:27:10.775 EDT %MIVR-STEPS_DB-7-UNK:DBWriteStep: varName = CallerID'; tokenIndex = 91 819700789: Jul 23 15:27:10.775 EDT %MIVR-STEPS_DB-7-UNK:Task:33000201928 Exception: : Exception=java.lang.ArrayIndexOutOfBoundsException 819700790: Jul 23 15:27:10.782 EDT %MIVR-STEPS_DB-7-EXCEPTION:java.lang.ArrayIndexOutOfBoundsException 819700791: Jul 23 15:27:10.783 EDT %MIVR-STEPS_DB-3-EXCEPTION_OCCURED:EXCEPTION_OCCURED
07-24-2020 09:57 AM
07-24-2020 11:42 AM
Thanks Anthony.
Ughh... I would have bet anything that I tried this without quotes and it didn't work. Or, maybe I did and there was some other issue at that point, maybe with a variable or something...
But, that worked!! Thanks!
Working SQL Statement:
INSERT INTO PostCallSurvey.dbo.SurveyResults(CallerID,AgentExt,Q1Answer,Q2Answer,DateTime) VALUES($CallerID,$AgentExt,$Q1Answer,$Q2Answer,$time)
Thanks again for the assistance, very much appreciated!
-Matt
07-25-2020 12:25 PM
Awesome! I'm glad you were able to resolve it. I guess I could have mentioned the quotes earlier, but like I said, it's been so long since I've done a DB integration in UCCX. These days it's always HTTP APIs. Cheers!
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: