casting a string to base64 2

[bouwob]

I recently came across a problem where I need to return data in base64 format. I would like to do this on the SQL end so I do not have to update the large number of WebPages that use a bind to a data grid. I have been scouring the web for 2 days now and have yet to find anything of use.

Has anyone done this before?

Is it possible?

tia

 

[SQLDenis]

Do you not use THE Google?

first result for base64 sql

http://www.google.com/search?hl=en&rls=com.microsoft:en-us:IE-SearchBox&rlz=1I7GGLG&q=base64+sql

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

I think that first search result link uses a custom-installed library...

 

[bouwob]

CREATE PROCEDURE StringToBase64(@String VARCHAR(4000), @Base64 VARCHAR(4000) OUTPUT) AS

DECLARE @ByteArray INT, @OLEResult INT


EXECUTE @OLEResult = sp_OACreate 'ScriptUtils.ByteArray', @ByteArray OUT
IF @OLEResult <> 0 PRINT 'ScriptUtils.ByteArray problem'

--Set the string.
EXECUTE @OLEResult = sp_OASetProperty @ByteArray, 'String', @String
IF @OLEResult <> 0 PRINT 'String problem'

--Get base64
EXECUTE @OLEResult = sp_OAGetProperty @ByteArray, 'Base64', @Base64 OUTPUT
IF @OLEResult <> 0 PRINT 'Base64 problem'

EXECUTE @OLEResult = sp_OADestroy @ByteArray

declare @optString varchar(4000)

EXEC dbo.StringToBase64 
    @String = '1323,1234,54.fg',
    @Base64 = @optString OUTPUT

select @optString

The sp_oa..... are actually pretty cool. They are not persay SQL but from what I could see are system dll's. They use there own memory space (256mb) and if you do not destroy the objects you will create a memory leak within your system (and crash the SQL box).

That being said my procedure is returning null everytime. anybody see anything that is of interest?

I also did google but my original results with

tsql cast string to "base 64" -xml
tsql cast to "base 64" -xml
tsql convert to base64

did not show the the same results.

tia

 

[bouwob]

IS there an edit button?? I cant ever seem to find one.

anyways new rewrite

alter PROCEDURE StringToBase64(@String VARCHAR(4000), @Base64 VARCHAR(4000) OUTPUT) AS

DECLARE @ByteArray INT, @OLEResult INT
DECLARE @src varchar(255), @desc varchar(255)

EXECUTE @OLEResult = sp_OACreate 'ScriptUtils.ByteArray', @ByteArray OUT, 4
IF @OLEResult <> 0 
BEGIN
   EXEC sp_OAGetErrorInfo @ByteArray, @src OUT, @desc OUT 
   raiserror('Error Creating COM Component1 0x%x, %s, %s',16,1, @OLEResult, @src, @desc)
    RETURN
END

--Set the string.
EXECUTE @OLEResult = sp_OASetProperty @ByteArray, 'String', @String
IF @OLEResult <> 0 
BEGIN
   EXEC sp_OAGetErrorInfo @String, @src OUT, @desc OUT 
   raiserror('Error Creating COM Component2 0x%x, %s, %s',16,1, @OLEResult, @src, @desc)
    RETURN
END

--Get base64
EXECUTE @OLEResult = sp_OAGetProperty @ByteArray, 'Base64', @Base64 OUTPUT
IF @OLEResult <> 0 
BEGIN
   EXEC sp_OAGetErrorInfo @Base64, @src OUT, @desc OUT 
   raiserror('Error Creating COM Component 0x%x3, %s, %s',16,1, @OLEResult, @src, @desc)
    RETURN
END

EXECUTE @OLEResult = sp_OADestroy @ByteArray

when I run it now I recieve a

Msg 50000, Level 16, State 1, Procedure StringToBase64, Line 10
Error Creating COM Component1 0x800401f3, ODSOLE Extended Procedure, Invalid class string

which leads me to believe that there is an issue with 'ScriptUtils.ByteArray'. How do I verify that it is registered correctly within the database? Does that error mean anything better to any of you?

 

[ESquared]

>>I think that first search result link uses a custom-installed library

I meant ScriptUtils is probably not something you have loaded on your SQL Server. It seems like a third-party package you have to install (and perhaps purchase).

"Invalid Class String" is exactly the kind of error that I would expect to get when the custom library ScriptUtils is not installed on your computer. I'm sorry to say that you've been wasting your time on this answer. I'll try to find some code for you.

 

[ESquared]

So I built a function for you. This is not off the internet.

CREATE FUNCTION ToBase64 (@Input varchar(6000))
RETURNS varchar(8000)
AS
BEGIN
   DECLARE
      @Output varchar(8000),
      @Bits varbinary(3),
      @Pos int
   SET @Pos = 1
   SET @Output = ''
   WHILE @Pos <= Len(@Input) BEGIN
      SET @Bits = Convert(varbinary(3), Substring(@Input, @Pos, 3))
      SET @Output = @Output + Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', Substring(@Bits, 1, 1) / 4 + 1, 1)
      SET @Output = @Output + Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', Substring(@Bits, 1, 1) % 4 * 16 + Substring(@Bits, 2, 1) / 16 + 1, 1)
      SET @Output = @Output + Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', Substring(@Bits, 2, 1) % 16 * 4 + Substring(@Bits, 3, 1) / 64 + 1, 1)
      SET @Output = @Output + Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', Substring(@Bits, 3, 1) % 64 + 1, 1)
      SET @Pos = @Pos + 3
   END
   RETURN (Left(@Output, Len(@Output) - 3 + Len(@Bits)) + Replicate('=', 3 - Len(@Bits)))
END
GO
SELECT dbo.ToBase64('Base 64 encoding')   
-- QmFzZSA2NCBlbmNvZGluZw==

 

[bouwob]

rock on ESquared

That solution is so much better than the round around going though sp_OE...... system functions. Thanks you so much for y9our help on this.