Archived Forum Post

Index of archived forum posts

Question:

Deflate a long string

Apr 16 '13 at 09:06

With the recent link I received of the compression_x64 library, now I can decompress strings in SQL, but now I have another problem.

When the decompressed string is longer tan 4000 characters, the function returns an empty string, is there a solution to this? maybe to pass a pointer to a ntext, or something like that, or to be able to receive in chunks the information decompressed? when I use the begindecompressString,MoreDecompressString, etc, it only returns info when the endDecompressString is called, and if the string is longer tan 4000 chars it returns an empty string.

THANKS IN ADVANCE !!!!


Answer

'  Note: BeginCompressStringENC may return a zero-length
'  string.  This is normal if the input is buffered and no
'  compressed data is yet available.
'  However, a null reference indicates an error -- which is
'  generally only possible if the component was never unlocked.

If it can sometimes return an empty string, the LastErrorText could help determine what went wrong.

Is it possible that you invoked it incorrectly? In the example the new data is concatenated with previous data

strOriginal = strOriginal & compress.MoreDecompressStringENC(chunk3)

are you doing this as well? or are you expecting compress.EndDecompressStringENC() to return the deciphered string?


Answer

I´m trying to use it in SQL as an activex.
So the instructions are like this:

exec sp_OAMethod @compress,'BeginDecompressString',@txt2 OUT,@src
exec sp_OAMethod @compress,'MoreDecompressString',@txt2 OUT,@src
exec sp_OAMethod @compress,'EndDecompressString',@txt2 OUT,@src
They all run fine, no errors, but only the endDecompressString retuns a value, and when the result string is longer tan 4000 chars (which is the limit for nvarchar variables in SQL) it returns empty.

Thanks again......


Answer

I don't know SQL, but it appears to me that you are passing the same value to the activex control in all three calls. The example I found indicates that each call to MoreDecompressString() should pass a different portion of the string. This requires that you first split the string into chunks

--  here is your code to split @src into chunks
--  http://stackoverflow.com/questions/2647/split-string-in-sql (help with that logic)
--  I guess we end up with something like @src == @srcChunk1 + @srcChunk2 + @srcChunk3, etc

--  and now we can begin using the activex control on each chunk    
exec sp_OAMethod @compress1 , 'BeginDecompressString', @txt2_1 OUT , @srcChunk1
exec sp_OAMethod @compress1 , 'MoreDecompressString' , @txt2_2 OUT , @srcChunk2 
exec sp_OAMethod @compress1 , 'EndDecompressString'  , @txt2_3 OUT , @srcChunk3

--  and now concatenate the three parts to form your original (plain-text) string   
exec @deflated = @txt2_1 + @txt2_2 + @txt2_3

--  I hope my SQL isn't horribly inaccurate, I tried to base it on your post...

I couldn't find an example in SQL for what you are trying, but here is the VBScript version which could help you translate it into SQL

--edit

sorry, I forgot the link (-__-) http://www.example-code.com/vbscript/streaming_deflate_string.asp

--edit

Fixed my code. I see from http://www.example-code.com/sql/default.asp that I incorrectly interpreted your code. Yikes, I was a bit off...


Answer

I´m changing the value of src in each call, the functions work fine when the result of the compressed string doesn´t exceed 4000 chars.


Answer

This is the LastErrorText:

ChilkatLog:    EndDecompressString:      DllDate: Apr 15 2013      ChilkatVersion: 9.4.1.3      UnlockPrefix: AXXXXXCompress      Username: SP2013:SP2013$      Architecture: Little Endian; 64-bit      VerboseLogging: 1      Success.    --EndDecompressString  --ChilkatLog

Answer

When decompressing in chunks via Begin/More/End, the decompressor can only return whatever decompressed might already be available given the input it has already seen. The behavior you are seeing is possible -- it entirely depends on the data. For example, imagine the characteristics of the compressed data in two situations:

1) A single character 'x' repeated 1 million times.
vs.
2) A million-character string composed of entirely random characters.

In SQL Server, local variables can hold a maximum of 4000 characters. For property values or string methods that may return longer strings, such as LastErrorText or SessionLog, use a temporary table, as shown below:

    -- Fetch a longer property string into a temp table:
    DECLARE @tmp TABLE (sessionLog ntext)
    INSERT INTO @tmp EXEC sp_OAGetProperty @ftp, 'SessionLog'