native sql function to encode binary data using base64

2 Mar 2006 5:48 PM

Uma

I need to encode binary data using base64 before storing to MS SQL 2005.
The select statement using FOR XML, BINARY BASE64 is not an option here,
due to the presence of xml tags unabling the results to be stored in a
variable.
Is there a native SQL function to do the base64 encoding without the xml tags?
Thanks.

2 Mar 2006 7:22 PM

JT

I think there is a .NET System* function that can do this. Perhaps better to
convert to base64 on application side.

Show quote

"Uma" <U**@discussions.microsoft.com> wrote in message
news:E44D5AEE-5A04-4AD2-990F-EBE110512C17@microsoft.com...
>I need to encode binary data using base64 before storing to MS SQL 2005.
> The select statement using FOR XML, BINARY BASE64 is not an option here,
> due to the presence of xml tags unabling the results to be stored in a
> variable.
> Is there a native SQL function to do the base64 encoding without the xml
> tags?
> Thanks.

2 Mar 2006 7:51 PM

Uma

I think you are referring to the convert.ToBase64String() function. I need
to use the function within a stored procedure, so the â€œapplicationâ€ is SQL
Server itself. I was hoping for something like Oracleâ€™s
utl_encode.base64_encode(). Thanks for your help.

Show quote

"JT" wrote:

> I think there is a .NET System* function that can do this. Perhaps better to
> convert to base64 on application side.
>
> "Uma" <U**@discussions.microsoft.com> wrote in message
> news:E44D5AEE-5A04-4AD2-990F-EBE110512C17@microsoft.com...
> >I need to encode binary data using base64 before storing to MS SQL 2005.
> > The select statement using FOR XML, BINARY BASE64 is not an option here,
> > due to the presence of xml tags unabling the results to be stored in a
> > variable.
> > Is there a native SQL function to do the base64 encoding without the xml
> > tags?
> > Thanks.
>
>
>

2 Mar 2006 8:04 PM

David Gugick

Uma wrote:

> I need to encode binary data using base64 before storing to MS SQL
> 2005.
> The select statement using FOR XML, BINARY BASE64 is not an option
> here,
> due to the presence of xml tags unabling the results to be stored in
> a variable.
> Is there a native SQL function to do the base64 encoding without the
> xml tags? Thanks.

You'll have to use something outside T-SQL to do this. If you had an
intermediate layer outside of SQL Server, you could use the BASE64
clause of the FOR XML statement. The problem with FOR XML is that it's
not much use if you need to use the results of the query within the
T-SQL batch.

SELECT CAST(N'My String Data' as VARBINARY(8000)) as "MyText" FOR XML
RAW, BINARY BASE64

Here's an article posted to SQLServerCentral.com that includes source
for an extended stored procedure you could call from T-SQL:
http://www.sqlservercentral.com/columnists/mcoles/freeencryption.asp

--
David Gugick - SQL Server MVP
Quest Software