topic Re: Converting Sql Server UniqueIdentifier Column into oracle Raw Column in Talend Studio

Converting Sql Server UniqueIdentifier Column into oracle Raw Column

_AnonymousUser — Sat, 16 Nov 2024 13:57:13 GMT

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

Re: Converting Sql Server UniqueIdentifier Column into oracle Raw Column

Anonymous — Wed, 08 Jun 2011 10:27:19 GMT

I need this answer too.
Thanks

Re: Converting Sql Server UniqueIdentifier Column into oracle Raw Column

Anonymous — Thu, 25 Aug 2011 22:57:03 GMT

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

Re: Converting Sql Server UniqueIdentifier Column into oracle Raw Column

Anonymous — Fri, 26 Aug 2011 05:15:50 GMT

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;
}
}