Post Call Survey - Error during DB Write Step

[Matthew Martin]

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

[Anthony Holloway]

I haven't done a DB integration in a long time, but I would be looking at the logs. Can you make sure the debugging level is enable for your DB subsystem in Serviceability > Trace > Configuration and then reproduce then collect the Engine logs for review? It will have the INSERT statement in the logs.

[Matthew Martin]

Thanks Anthony, thanks for the reply.

I'll give that a try now and report back.

-Matt

[Anthony Holloway]

Ok cool. I suspect we'll see an extra set of quotation marks on the string variables. E.g., '"Yes"'

[Matthew Martin]

 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

[Anthony Holloway]

In your initial post $Q1Answer is not surrounded in single quotes, but in the debug it is. Since this is an integer field in the DB, it shouldn't have that. Is it possible you tried it at one point, and the script hasn't refreshed or been updated?

[Matthew Martin]

Yea, that's definitely possible. I edited that SQL line so many times now...
Does it matter within the Menu where I have the Set steps to set the Q1Answer variable, which is quoted. Is one of these methods better/correct (*Q1Answer is an int)?
Set Q1Answer = '5'
or
Set Q1Answer = 5

In the sample script I based most of this off of they had the integer surrounded by single quotes. So I just left it that way.
Also, when using the Set step to set a string to a variable. Should single or double quotes be used?

Thanks Again,
Matt

[Anthony Holloway]

Generally speaking:

• No quotation marks around integers
• Double quotes around Strings
• Single quotes around single characters

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]

[Matthew Martin]

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

[Matthew Martin]

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

[Anthony Holloway · Accepted solution]

Have you ever tried it without surround the variables which are Strings inside of single quotes?

E.g., $var instead of '$var'

[Matthew Martin]

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

[Anthony Holloway]

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!