-
Jan 23rd, 2009, 09:48 AM
#1
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:
DECLARE @Base64Data VARCHAR(MAX)
SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX),'testing...'))
SELECT @Base64Data AS Base64, CONVERT(VARCHAR(MAX), dbo.f_Base64ToBinary(@Base64Data)) AS My_Data
And the actual functions:
sql Code:
-- =============================================
-- Author: <Michael Ciurescu>
-- Create date: <20090123>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION dbo.f_BinaryToBase64
(
@bin VARBINARY(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Base64 VARCHAR(MAX)
/*
SELECT dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), 'Converting this text to Base64...'))
*/
SET @Base64 = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'VARCHAR(MAX)')
RETURN @Base64
END
-- =============================================
-- Author: <Michael Ciurescu>
-- Create date: <20090123>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION dbo.f_Base64ToBinary
(
@Base64 VARCHAR(MAX)
)
RETURNS VARBINARY(MAX)
AS
BEGIN
DECLARE @Bin VARBINARY(MAX)
/*
SELECT CONVERT(VARCHAR(MAX), dbo.f_Base64ToBinary('Q29udmVydGluZyB0aGlzIHRleHQgdG8gQmFzZTY0Li4u'))
*/
SET @Bin = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Base64"))', 'VARBINARY(MAX)')
RETURN @Bin
END
Last edited by CVMichael; Jan 24th, 2009 at 07:10 PM.
-
Aug 29th, 2011, 04:48 AM
#2
New Member
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?
-
Aug 29th, 2011, 05:31 PM
#3
Re: SQL Server 2005 - Convert any data to Base64 and back
BIGINT:
sql Code:
DECLARE @BigIntData BIGINT
DECLARE @Base64Data VARCHAR(MAX)
SET @BigIntData = 1242353245346345643
SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), @BigIntData))
SELECT @Base64Data AS Base64, CONVERT(BIGINT, dbo.f_Base64ToBinary(@Base64Data)) AS [BigInt]
DATETIME:
sql Code:
DECLARE @myDate DATETIME
DECLARE @Base64Data VARCHAR(MAX)
SET @myDate = GETDATE()
SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), @myDate))
SELECT @Base64Data AS Base64, CONVERT(DATETIME, dbo.f_Base64ToBinary(@Base64Data)) AS myDate
XML:
sql Code:
DECLARE @XML XML
DECLARE @Base64Data VARCHAR(MAX)
SET @XML = (
SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS row
WHERE TABLE_NAME = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME NOT LIKE 'sys%')
FOR XML AUTO, ELEMENTS, ROOT('root')
)
SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), @XML))
SELECT @Base64Data AS [Base64]
DECLARE @New_XML XML
SET @New_XML = CONVERT(XML, dbo.f_Base64ToBinary(@Base64Data))
SELECT row.col.value('./TABLE_NAME[1]','NVARCHAR(128)') AS TABLE_NAME
, row.col.value('./ORDINAL_POSITION[1]','INT') AS ORDINAL_POSITION
, row.col.value('./COLUMN_NAME[1]','NVARCHAR(128)') AS COLUMN_NAME
FROM @New_XML.nodes('//row') AS row(col)
-
Apr 7th, 2012, 10:46 PM
#4
New Member
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?
-
Jan 4th, 2013, 04:54 AM
#5
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|