April 20, 2008 at 11:48 pm
Hi...
I wrote a routine for encoding in Base64 algorithm and integrate in an extended stored procedure, it works fine when I pass CHAR, VARCHAR, BINARY, etc data types... but I realized something, those data types only allows me 8000 char length max, in fact, the algorithm requires 4 output bytes (characters) for every three input bytes (character).. at least while encoding... so, if my input parameter is bigger that 6000 chars, the output parameters get truncated...
I tried passing TEXT input parameters through a user-defined function, and it worked just with the input parameter, but the function can return TEXT, NTEXT or IMAGE data-types, so and end up with just half of the problem solved, I can pass big input data but can not retrieve the result, at least in T-SQL, I think that if I program something using ADO or .NET I could set up the input - output parameters whatever I liked, but that wouldn't be a real solution, I would be better if I just program the whole thing in and external application using ADO or .NET and throw away the extended stored procedure.
Is there any way I can pass to this extended stored procedure big input type parameters, and retrieve the result from T-SQL or something similar, using external applications is not what I'm looking for.
Something like a IMAGE field on a table and pass it to the base64 encode function and it return the encoded result...
Thanks in advance...
April 21, 2008 at 1:53 am
Or do it all in SQL Server?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67831
N 56°04'39.16"
E 12°55'05.25"
April 21, 2008 at 12:17 pm
Peso (4/21/2008)
Or do it all in SQL Server?
That's good, but what I really want is an input data bigger than VARCHAR(6000)...
In that T-SQL script @PlainText (input data) has a limit of 6000 chars.
I want to encode something bigger than that, my problem really is how to capture the output parameter from the extended_SP, I can input TEXT, NTEXT or IMAGES using a scalar function
This is an approach of what I want to accomplish
CREATE FUNCTION dbo.TESTING (@inText TEXT)
RETURNS TEXT
AS
BEGIN
DECLARE @outText TEXT
EXEC master.dbo.xp_base64_enconde @inText, @outText OUTPUT
RETURN @outText
END
but obviously it doesn't work because I can't declare TEXT variables, neither declare a function which return TEXT, NTEXT or IMAGE.
😉
June 4, 2008 at 10:38 pm
I think I found a way to perform BASE64 encodings of varbinary longer than 6000.
Along with the complementary decoding of BASE64 strings longer than 8000.
This makes use of the SQL-2005 XML handling functions.
/* HISTORY:
* 04-Jun-08FSCreated. Based on bunch of XML investigations.
*/
SET NOCOUNT ON
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'FN_Base64_Encode' AND type = 'FN')
DROP FUNCTION FN_Base64_Encode
GO
CREATE FUNCTION FN_Base64_Encode
(
@input varbinary(max)
)
RETURNS
varchar(max)
AS BEGIN
--local variables
DECLARE
@output varchar(max),
@x xml,
@y varchar(max)
--initialise variables
SET @output = ''
--check for NULL input, results in NULL output
IF (@input IS NOT NULL)
BEGIN
SET @x = (SELECT q=@input FOR XML RAW, BINARY BASE64);
SET @y = CAST(@x AS varchar(max))
-- X :
-- | |
--INDEX : 123456789 LEN-3
SET @output = SUBSTRING( @y, 9, LEN(@y)-11 )
END
ELSE
BEGIN
SET @output = NULL
END
--return the result
RETURN @output
END
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'FN_Base64_Decode' AND type = 'FN')
DROP FUNCTION FN_Base64_Decode
GO
CREATE FUNCTION FN_Base64_Decode
(
@base64 varchar(max)
)
RETURNS
varbinary(max)
AS BEGIN
DECLARE
@output varbinary(max),
@x xml
SET @output = 0x00
SET @x = CAST(' ' as xml)
SET @output = (SELECT @x.value('(/row/@q)[1]','varbinary(max)'))
RETURN @output
END
GO
/*
-- TEST the fn_base64encode and fn_base64_decode functions.
DECLARE @Base64Encoded varchar(max)
DECLARE @BinaryEncoded varbinary(max)
DECLARE @Roundtrip varchar(max)
SET @Base64Encoded = 'PD94bWwgdmVyc2lvbj0nMS4wJyBlbmNvZGluZz0nSVNPLTg4NTktMSc/PjxDMlMgdmVyc2lvbj0nNy4wLjElOCcgc3RhdHVzY29kZT0nMjAwJyBjdXJyZW50cGFja2V0PScxJyB0b3RhbHBhY2tldHM9JzUnIGMyc19pbnZpdGVfY29va2llPSc0NDk1OCcgY29va2llPSctMjQ1MSc+PCFbQ0RBVEFbXV0+PC9DMlM+';
SET @BinaryEncoded = dbo.fn_base64_decode( @Base64Encoded )
SET @Roundtrip = dbo.fn_base64_encode( @BinaryEncoded )
SELECT PATINDEX(@Base64Encoded, @Roundtrip), @Base64Encoded, CAST(@BinaryEncoded as varchar(max)), @BinaryEncoded, @RoundTrip
SELECT PATINDEX(@Base64Encoded, @Roundtrip), @Base64Encoded, CAST(@BinaryEncoded as xml), @BinaryEncoded, @RoundTrip
GO
*/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply