UCCX 10.6 DB Write

[michael.bruesch]

I'm making a simple script where the caller enters a badge number, enters a reason for being out that day, and the day they will be out (1-today, 2-tomorrow). I'm having trouble writing the data to Oracle. I've tried every combination of formatting I can think of but it won't write the collected variables to the database.

From the DB Write step -> SQL tab I select the table I want to insert to from the "Show all fields (select table):" dropdown. Here's what it shows:

DB Field Names                 Data Types

HC_BADGE_NUMBER      java.lang.String

HC_REASON_CODE         java.math.BigDecimal

HC_CALLOUT_DATE        java.sql.Date

HC_TIMESTAMP               java.sql.Timestamp

Should the Insert statement be written like this?

INSERT INTO HR_CALLOUTS (HC_BADGE_NUMBER, HC_REASON_CODE, HC_CALLOUT_DATE, HC_TIMESTAMP) 
VALUES ($StrBadgeNumber, $BD_Reason, $DTE_CallOutDate, SYSDATE)

Where $StrBadgeNumber is a String variable in the script, $BD_Reason is a Big Decimal, and $DTE_CallOutDate is a Date. This is not working for me. It is giving me the very long formatted date for $DTE_CallOutDate, which Oracle doesn't like. I only need this date in 'MM/DD/YYYY' format, no time value.

I've also tried turning everything into a string and making one SQL string but that isn't working either. What am I doing wrong here?

EDIT: I switched the $DTE_CallOutDate to have hardcoded SQL with an integer variable to add to SYSDATE if they want tomorrow:

INSERT INTO HR_CALLOUTS (HC_BADGE_NUMBER, HC_REASON_CODE, HC_CALLOUT_DATE, HC_TIMESTAMP) 
VALUES ($StrBadgeNumber, $BD_Reason, SYSDATE + $IntTomorrow, SYSDATE)

This finally works! But I need to remove the time value from the HC_CALLOUT_DATE. I've tried:

INSERT INTO HR_CALLOUTS (HC_BADGE_NUMBER, HC_REASON_CODE, HC_CALLOUT_DATE, HC_TIMESTAMP) 
VALUES ($StrBadgeNumber, $BD_Reason, TO_DATE(TO_CHAR(SYSDATE + $IntTomorrow, 'MM/DD/YYYY')), SYSDATE)

$IntTomorrow = 0 if they want today or $IntTomorrow = 1 if they want tomorrow, but now I'm getting an Invalid Month error.

My first question!

Thanks.

[Joseph Thvedt · Accepted solution]

Try this:

TRUNC(sysdate) + $IntTomorrow

[michael.bruesch]

I actually just figured this out yesterday. I meant to come here and answer my own question, but that's exactly how I got it to work. Thanks Joseph!

[Joseph Thvedt]

Not to hijack the thread or anything, but have you done a db write where a date or timestamp is passed as a variable from the script, rather than getting the date from the database itself (like using SYSDATE)? I've been struggling to do that. It actually needs to be the current time, so normally I'd just use SYSDATE, but it may do multiple inserts in a loop, and I want all of them to have the identical date and time.

I tried passing variables of type Date, java.sql.Timestamp, and java.util.Date, but none of them would work. I'd get unsupported data type errors or something similar.

I ended up writing a ridiculous block in the expression editor, where I broke up the date into year, month, day, hour, minute, and second, zero-padded them as necessary, concatenated them into a string, and then finally in my db write step, I used to_date($myString, 'yyyymmddhh24miss'). It works, but it sure seems like a silly amount of work to do something so simple. Surely there's a way to just pass a date directly into the db write.

[michael.bruesch]

I could not get it to take a Date variable in the SQL using just the built-in script steps. I looked in the UCCX Engine logs and found it was formatting the Date into a very looong Date format which the DB doesn't like. I could not figure out how to get it to just MM/DD/YYYY format.

[Joseph Thvedt]

There's another annoyance -- the script (or the debugger) can't return an error message from the database? We've got to find it in the engine logs? Ugh.

I found an ancient thread where a guy said he didn't even use DB steps in scripts; he wrote Java for database interaction. I'm beginning to understand why. I have no Java skills, but I may have to give it a try.

[michael.bruesch]

The 3rd party company that help us set up all the initial scripts also used Java blocks to call stored procedures in Oracle whenever they needed to do a database dip. I thought that was stupid since there are built-in steps for that, but now I see why.