login about faq

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.


asked Apr 15 '13 at 20:18

enriquemx's gravatar image


'  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?


answered Apr 15 '13 at 20:32

blaze4218's gravatar image


edited Apr 15 '13 at 20:34

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......


answered Apr 15 '13 at 20:43

enriquemx's gravatar image


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


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


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...


answered Apr 15 '13 at 21:02

blaze4218's gravatar image


edited Apr 15 '13 at 21:32

so, you basically split the cipher text into chunks, and send each of those chunks to the chilkat methods, adding all of the responses into one string.

(Apr 15 '13 at 21:03) blaze4218

Your first post read "now I can decompress strings in SQL" and so I was under the impression that the cipher data was encoded as some sort of string, and that is why I indicated splitting your "cipher text". But you are using BeginDecompressString which accepts data in variant form. I believe you should still apply the same logic, but you will need to split your @src with something other than the string splitting formula at the link I posted...

(Apr 15 '13 at 21:31) blaze4218

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.


answered Apr 15 '13 at 21:35

enriquemx's gravatar image


I see. I couldn't tell from your post, sorry. I'll ask again that you post your LastErrorText for more information about what is going wrong. The LastErrorText will always have information even if no errors occur.

(Apr 15 '13 at 21:38) blaze4218

and maybe set the VerboseLogging property to 1

(Apr 15 '13 at 21:39) blaze4218

Also, even if you are changing the value of @src in each call, are you storing all of the method returns in @txt2 without concatenating it to the previous value? Because you might be overwriting the data.

(Apr 15 '13 at 21:42) blaze4218

This is the LastErrorText:

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

answered Apr 15 '13 at 22:04

enriquemx's gravatar image


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.
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'


answered Apr 16 '13 at 08:09

chilkat's gravatar image

chilkat ♦♦


(Apr 16 '13 at 09:06) enriquemx
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or __italic__
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: Apr 15 '13 at 20:18

Seen: 3,171 times

Last updated: Apr 16 '13 at 09:06

powered by OSQA