UCCX SQL get DB step looking for boolean

[rastetterj]

Hello,

I an using the Get DB step to obtain the value of the field in a SQL DB.  The data type is java.lang.boolean.  The value will be either 1 for active or 0 for not active.  I have created the local variable but I have no Idea how to write the Boolean expression that gives me the correct result.

I would like it to return the value of the cell or a true false value based off of the number 1 or 0.

1= True

0= False.

(From here I can create an IF statement to finish the logic, but I cannot get the Boolean local variable to populate with anything other than false)

The column name in the database is Active and the local Boolean variable is giftCardStatus.  Let me know if you need any additional information.

[Gergely Szabo]

Hi,

can you give us the DB type and version and also the datatype of the DB column named "Active".

G.

[rastetterj]

We are using SQL server 2012 SP1 Enterprise Edition.  The datatype is java.lang.boolean

[Gergely Szabo]

Er... I mean the data type used in the database table. It's certainly not java.lang.Boolean ;-)

G.

[rastetterj]

Sorry.  It is a "bit"

[Gergely Szabo]

Hmm, bit is should be supported and mapped to Boolean automatically. I will try that in my lab tonight. G.

[rastetterj]

Any luck?

What I don't understand is why, when I perform the Get DB step it doesn't just populate the local variable?

[Gergely Szabo]

Hi,

no, I did not have time to play around with it yet, unfortunately.

Anyway, according to the JTDS type mapping chart, it should work automatically, unless something blocks it. That might be an abstraction layer within UCCX, perhaps (so DB data types are first mapped to an internal object type and then to a visible Java type), I guess for compatibility reasons. JTDS itself maps BIT to Boolean, however, Oracle does not have BIT. So if UCCX needs to support both, then it surely overrides the JTDS provided map.

Anyway, I will double check that - hopefully on Monday or Tuesday and will let you know.

G.

[Gergely Szabo · Accepted solution]

Hi,

tested it - and it works as expected. Here's my database table:

CREATE TABLE test1 (id INT PRIMARY KEY IDENTITY(1,1), varcharcol NVARCHAR(20), bitcol BIT)
INSERT INTO test1 VALUES ('yes','yes',1)
INSERT INTO test1 VALUES ('no','no',0)

Created a UCCX script with the DB Read, DB Get and DB Release steps. The SQL query is as simple as it can be:

SELECT * FROM test1

The value of bitcol is mapped to a local script variable, type boolean, named bool, initial value is false.

When running the script, the frist row from the table is read, and the value of bool becomes true.

UCCX 8.0(2), SQL Server 2005 Standard.

G.

[rastetterj]

I will give this a shot.  I have been really busy.  Hope to test soon.

[rastetterj]

This worked as indicated.  I was just having a hard time wrapping my head around it.  Thank you for your time and continued contribution.