SQL Server 2005 - Convert any data to Base64 and back-VBForums
Results 1 to 5 of 5

Thread: SQL Server 2005 - Convert any data to Base64 and back

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,726

    SQL Server 2005 - Convert any data to Base64 and back

    Even though the actual conversion is a one line code, it is still better to have it in a function (at least you don't have to remember all that XML stuff...)

    The first function f_BinaryToBase64 takes binary data as input, but anything can be converted to binary.

    The second function f_Base64ToBinary takes the Base64 data (the output of the first function), and outputs the original binary data (that can be converted back to your data type).

    How to use with strings for example:
    sql Code:
    1. DECLARE @Base64Data VARCHAR(MAX)
    2. SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX),'testing...'))
    3.  
    4. SELECT @Base64Data AS Base64, CONVERT(VARCHAR(MAX), dbo.f_Base64ToBinary(@Base64Data)) AS My_Data

    And the actual functions:
    sql Code:
    1. -- =============================================
    2. -- Author:      <Michael Ciurescu>
    3. -- Create date: <20090123>
    4. -- Description: <Description, ,>
    5. -- =============================================
    6. CREATE FUNCTION dbo.f_BinaryToBase64
    7. (
    8.     @bin VARBINARY(MAX)
    9. )
    10. RETURNS VARCHAR(MAX)
    11. AS
    12. BEGIN
    13.     DECLARE @Base64 VARCHAR(MAX)
    14.    
    15.     /*
    16.         SELECT dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), 'Converting this text to Base64...'))
    17.     */
    18.    
    19.     SET @Base64 = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'VARCHAR(MAX)')
    20.    
    21.     RETURN @Base64
    22. END
    23.  
    24.  
    25. -- =============================================
    26. -- Author:      <Michael Ciurescu>
    27. -- Create date: <20090123>
    28. -- Description: <Description, ,>
    29. -- =============================================
    30. CREATE FUNCTION dbo.f_Base64ToBinary
    31. (
    32.     @Base64 VARCHAR(MAX)
    33. )
    34. RETURNS VARBINARY(MAX)
    35. AS
    36. BEGIN
    37.     DECLARE @Bin VARBINARY(MAX)
    38.    
    39.     /*
    40.         SELECT CONVERT(VARCHAR(MAX), dbo.f_Base64ToBinary('Q29udmVydGluZyB0aGlzIHRleHQgdG8gQmFzZTY0Li4u'))
    41.     */
    42.    
    43.     SET @Bin = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Base64"))', 'VARBINARY(MAX)')
    44.  
    45.     RETURN @Bin
    46. END
    Last edited by CVMichael; Jan 24th, 2009 at 06:10 PM.

  2. #2
    New Member
    Join Date
    Aug 2011
    Posts
    13

    Re: SQL Server 2005 - Convert any data to Base64 and back

    Your example works when the function performing the mapping returns the same type as the input parameter type (in this case a string). Can you please give an example when the mapping function returns a different type?

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,726

    Re: SQL Server 2005 - Convert any data to Base64 and back

    BIGINT:
    sql Code:
    1. DECLARE @BigIntData BIGINT
    2. DECLARE @Base64Data VARCHAR(MAX)
    3.  
    4. SET @BigIntData = 1242353245346345643
    5.  
    6. SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), @BigIntData))
    7.  
    8. SELECT @Base64Data AS Base64, CONVERT(BIGINT, dbo.f_Base64ToBinary(@Base64Data)) AS [BigInt]

    DATETIME:
    sql Code:
    1. DECLARE @myDate DATETIME
    2. DECLARE @Base64Data VARCHAR(MAX)
    3.  
    4. SET @myDate = GETDATE()
    5.  
    6. SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), @myDate))
    7.  
    8. SELECT @Base64Data AS Base64, CONVERT(DATETIME, dbo.f_Base64ToBinary(@Base64Data)) AS myDate

    XML:
    sql Code:
    1. DECLARE @XML XML
    2. DECLARE @Base64Data VARCHAR(MAX)
    3.  
    4. SET @XML = (
    5.     SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME
    6.     FROM INFORMATION_SCHEMA.COLUMNS AS row
    7.     WHERE TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME NOT LIKE 'sys%')
    8.     FOR XML AUTO, ELEMENTS, ROOT('root')
    9. )
    10.  
    11. SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), @XML))
    12.  
    13. SELECT @Base64Data AS [Base64]
    14.  
    15.  
    16. DECLARE @New_XML XML
    17. SET @New_XML = CONVERT(XML, dbo.f_Base64ToBinary(@Base64Data))
    18.  
    19. SELECT row.col.value('./TABLE_NAME[1]','NVARCHAR(128)') AS TABLE_NAME
    20.     , row.col.value('./ORDINAL_POSITION[1]','INT') AS ORDINAL_POSITION
    21.     , row.col.value('./COLUMN_NAME[1]','NVARCHAR(128)') AS COLUMN_NAME
    22. FROM @New_XML.nodes('//row') AS row(col)

  4. #4
    New Member
    Join Date
    Apr 2012
    Posts
    1

    Re: SQL Server 2005 - Convert any data to Base64 and back

    I have a table with PK,Biometric Image1,Image2 and need to update the Image1 and Image2 to base64 encode.

    the function does work, but i need to create a stored procedure to copy the table contents between linked server and then update the image data to base64encode

    please help?

  5. #5
    New Member
    Join Date
    Jan 2013
    Posts
    1

    Thumbs up Re: SQL Server 2005 - Convert any data to Base64 and back

    Great post. Here's how to dinamically create query to convert columns from XML. I used this in ServiceBroker app


    Code:
    DECLARE @XmlDocument XML;
    		SET @XmlDocument = (select TOP 1 * FROM BRO.Dialogs FOR XML AUTO, BINARY BASE64, ROOT('root'));
    		--SELECT @XmlDocument	;
    		
    		declare @SQL varchar(max), @SQL2 varchar(max), @SQL3 varchar(max);
    	    select @SQL = coalesce(@SQL + ','+ space(1),' ') + quotename(COLUMN_NAME),
    			@SQL2 = coalesce(@SQL2 + ',' + space(1),' ') + (CASE WHEN DATA_TYPE = 'binary'
    				   THEN  'dbo.f_Base64ToBinary(' + COLUMN_NAME + ')'
    				   ELSE COLUMN_NAME END),
    		    @SQL3 = coalesce(@SQL3 + ',' + space(1),' ') + (CASE WHEN DATA_TYPE = 'binary'
    				   THEN  COLUMN_NAME + ' ' + 'VARCHAR(max)'
    				   WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN COLUMN_NAME + ' ' + DATA_TYPE +'(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(20)) + ')'
    				   ELSE COLUMN_NAME + ' ' + DATA_TYPE END)
    		from INFORMATION_SCHEMA.COLUMNS
    		where TABLE_SCHEMA = 'BRO' AND TABLE_NAME = 'Dialogs'
    		and COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' +TABLE_NAME), COLUMN_NAME, 'IsIdentity') <> 1
    		ORDER BY ORDINAL_POSITION;
    		--SELECT @SQL, @SQL2, @SQL3;
    		
    		declare @var_query nvarchar(max)
    		
    		SET @var_query = '
    		DECLARE @DocHandle int;
    		DECLARE @XmlDocument XML;
    		SET @XmlDocument = ''' +CAST(@XmlDocument AS NVARCHAR(max)) + ''';
    		-- Create an internal representation of the XML document.
    		EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
    		SELECT  '+ @SQL2+ ' 
    		FROM OPENXML (@DocHandle, ''/root/BRO.Dialogs'',1)
    		WITH ( '+ @SQL3 +' );'
    		
    		--SELECT @var_query
    		
    		exec dbo.sp_executesql @statement = @var_query

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.