What NLS should I use for Binary UUID?

[alo]

Hi,

I am using ODBC connector pulling data from SQL database and writing to a sequential file. the UUID does not look right on the output file.

Here is the info:
Source:
UUID - is defined as Binary (16)
NLS: : Using project default NLS map ISO8859-1
ODBC connector-> View Data
UUID = 41BEB2BFC1D9DE4D973B6186E723452F

Transformer/Target:
Sequential file:
UUID - is transferred to Varchar(36) and element is set to SQL.GUID
NLS: : Using project default NLS map ISO8859-1
Output UUID= A????M?;a??#E/

Here is the error message:
nls_map_buffer_out() - NLS mapping error, row 1 (approx), row ="A????M?;a??#E/" (received the same error when the output file is defined as Binary (16))

I have been researching and testing with different NLS, but they did not work for me.

Please let me know if you have any suggestions.

Thanks,
Amy

[chulett]

I would suggest dumping the SQL.GUID element and just loading it to a plain old Varchar(36).

[ray.wurlod]

Code: Select all

SELECT CAST (UUID AS VARCHAR(36)), ... FROM tablename

and describe the data type as VarChar(36). You may also need a function such as RAWTOHEX() in the SQL.

[alo]

Thanks, Craig.
I dumped the SQL.GUID element and just used Varchar(36). I am still getting the same error.

[alo]

Thanks, Ray.

I will try and let you all know.

[alo]

Hi Ray,
RAWTOHEX() is for Oracle SQL, correct? We use MS SQL server.

Our workaround is to convert data in Stored procedure and call the SP from DataStage and inset data into a target table.

Code: Select all

Select CONVERT(VARCHAR(MAX),UUID,2) AS UUID from tablename

By using the same CONVERT function in ODBC connector, the value can not be passed. I was wondering if there is a way (other than execute a SP).

Any DataStage functions can be used to convert the binary data?

[chulett]

So, which is it - are you targeting a flat file or a database table? If a table, what is the data type of the target field? Also note that Ray said "such as" so you would look for an equivalent in SQL Server.

[alo]

Target is a table (Microsoft SQL Server MDS) and the data type is Nvarchar(32). I was trying to load the data to a flat file -> staging table -> target table.