Converting Sql Server UniqueIdentifier Column into oracle Raw Column

One Star

Converting Sql Server UniqueIdentifier Column into oracle Raw Column

Does somebody know the best way to convert SqlServer 2008 UniqueIdentifier Column to Oracle10g Raw Column using the Tmap Component ?
Thanks
One Star

Re: Converting Sql Server UniqueIdentifier Column into oracle Raw Column

I need this answer too.
Thanks
One Star kcd
One Star

Re: Converting Sql Server UniqueIdentifier Column into oracle Raw Column

I have the same issue only to MySQL.
Without a TMap I found setting the MSSQL input schema to Type data[] and DB Type to uniqueidentifier and the output schema (MySQL in my case) to Type data[] and DB Type BINARY(16) (or CHAR(16), but not VARCHAR) seems to work fine. You know you haven't got it right when ETL complains about the length because it will treat the GUID as a string of length 36.
I'm now looking at how to do this for GUIDs stored as strings in an XML file, which will require explicit conversion like you suggest.
FYI http://wiki.mcneel.com/developer/scriptsamples/guidtostring
Cheers
Kevin
One Star kcd
One Star

Re: Converting Sql Server UniqueIdentifier Column into oracle Raw Column

Here you go:
In tMap use GUID.stringToGuid(SomeStringColumn)
Create the custom routine GUID as follows

public class GUID {
/**
*
* {talendTypes} byte | Byte
*
* {Category} User Defined
*
* {param} string("00000000-0000-0000-0000-000000000000") input: The guid as a string.
*
*/
public static byte[] stringToGuid(String guid) {
if (guid == null) {
return null; //$NON-NLS-1$
}

UUID uuid = UUID.fromString(guid);

long msb = uuid.getMostSignificantBits();
long lsb = uuid.getLeastSignificantBits();

byte[] output = new byte;
// Reverse the first 4 bytes
output = (byte) (msb >>> 8 * (7 - 3));
output = (byte) (msb >>> 8 * (7 - 2));
output = (byte) (msb >>> 8 * (7 - 1));
output = (byte) (msb >>> 8 * (7 - 0));
// Reverse 5th and 5th
output = (byte) (msb >>> 8 * (7 - 5));
output = (byte) (msb >>> 8 * (7 - 4));
// Reverse 7th and 8th
output = (byte) (msb >>> 8 * (7 - 7));
output = (byte) (msb >>> 8 * (7 - 6));
// Copy the rest
for (int i = 8; i < 16; i++) {
output = (byte) (lsb >>> 8 * (7 - i));
}
return output;
}
}