How to retrieve user_uuid to a value in a select

[Felix Varela]

Hi! 

Could someone help me with this?

Env:

CA SDM 14.1 'tus-110'

CA Service Catalog 14.1.01.136

SQL Server 2008

 

I builded a object report in the catalog to retrieve the name and user id to a form in the catalog.

 

i create 2 variables, one for the value, one for the label, in the value i put the useruuid and in the label i put the name of the person.

 

When i retrieve the value of the select it show "undefined" like the value is not retrieving.

 

I tried put other columns in the variable value for testing, i tested with userid, type, and last_name and it works, the values hast retrieved successfully, but when i try to retrieve useruuid it does not work.

 

The value is showed in the report like others but i cannot retrieved with a getselectedvalue even with getselection....

Please help!

[Michael Mueller]

Maybe converting the column value to a string type will help.
In Oracle , I would go for
Select ...raw_to_hex(contact_uuid) ...
Hope this helps
Regards
....Michael

[Stephanie Maehr]

for sqlserver this could be 

contact_uuid = convert(varbinary,IIF('%contact_uuid%' = '','0x00','%contact_uuid%'),1)

 

The more complex IIF condition is required since catalog would possibly execute that query with an empty UUID ...

[Louis van Amelsfort]

Good Morning Felix.

I am not sure whether I understand your question correct.
Still I propose to check on the below document:
https://comm.support.ca.com/kb/How-to-get-UUID-shown-properly-in-a-catalog-formonlookupfield/kb000048450

You face this problem because UUID is binary and this is a not supported datatype for variables in Catalog.
The following approach resolves this.
Use Case/Select UUID for a contact: Here convert function is used to convert binary data to integer
table: ca_contact
fields: userid,first_name
query: select userid,first_name
from ca_contact
where CONVERT(integer,location_uuid)=%loc_uuid_int%
Here we are converting binary UUID to integer and filtering matching records based on first select field selection.

Kind regards, Louis.

[Stephanie Maehr]

Hi Louis,

the KD is great and will work for many cases, but a better conversion to utilize for the UUID is STRING per my suggestion above. 

Converting to integer may result in duplicate values for two different UUIDs.
Example:
UUID for contact1 "0x648AF38B6A62E14F97FEE67239D5CD55" and
UUID for contact2 "0x827194373E56BF46ADCE047F39D5CD55" will both be
converted to integer value "970313045". 

 

Because of that the recommendation was as follows:

 

contact_uuid = convert(varbinary,IIF('%contact_uuid%' = '','0x00','%contact_uuid%'),1)

 

(the more complex IIF condition is required since catalog would possibly execute that query with an empty UUID ...)

Maybe we can extend the KD accordingly.

[Pier Olivier Tremblay]

Stephanie_Maehr, my guess is that he wants to get the value selected in a form, not pass a variable to a report object. So your suggestion, even if good, does not apply here.

 

varelafpy, Since there is no contact with an empty UUID, you do not need an IIF.

 

You can use the CA function or SQL convert method : 

 

CA function : select dbo.hex(contact_uuid) from ca_contact

SQL Convert : select convert(nvarchar(32),contact_uuid,2) from ca_contact

[Felix Varela]

Thanks Olivier it works!

 

I used the convert function

"SQL Convert : select convert(nvarchar(32),contact_uuid,2) from ca_contact" added to my query and it works great.