Link to home
Create AccountLog in
Avatar of Camillia
CamilliaFlag for United States of America

asked on

Size of file is different after I save and want to read it back

LowFatSpread helped me with the solution here:
https://www.experts-exchange.com/questions/28020795/String-or-binary-is-truncated-varbinary-max-field.html?anchorAnswerId=38861456#a38861456

1. I save the file using the method in that solution and I have it below. I put a debug step and the datalength of the doc is 4804

 declare @estring varbinary(max),@i bigint,@l bigint
select @i=1,@l=datalength(@doc)
SELECT 'size before loop'
SELECT DATALENGTH(@l)

OPEN SYMMETRIC KEY MyTableKey DECRYPTION
BY CERTIFICATE EncryptMyCert

declare @temp varbinary(8000)

set @temp=convert(varbinary(8000),'')
set @estring=convert(varbinary(8000),'')

while @i<=@l
BEGIN

       SELECT 
                @estring =  @temp + 
                                 ENCRYPTBYKEY(KEY_GUID('MyTableKey'),
                                              SUBSTRING(@doc, @i,
                                                        CASE WHEN @i + 7899 <= @l
                                                             THEN 7900
                                                             ELSE @l - @i +1            
           
                                                        END))
       
                                                     
                                          
       set @i = @i + 7900 
                                              
                                                        
end 

 SELECT 'size after loop'
SELECT DATALENGTH(@estring) -- size is 4808

Insert into table....
    select ....
             @estring

Open in new window


This is how I read it back and I have debug steps with the datalength. When I read it back in code, the file is corrupt. Not sure what i need to change


OPEN SYMMETRIC KEY ARMSTableKey DECRYPTION

BY CERTIFICATE EncryptArmsCert


SET NOCOUNT ON;

 declare @estring varbinary(max),@i bigint,@l bigint
select @i=1
SELECT @l= datalength(doc) FROM dbo.DocumentFiles WHERE Id = @Id

declare @temp varbinary(8000)

set @temp=convert(varbinary(8000),'')
set @estring=convert(varbinary(8000),'')

 SELECT 'before loop'
 SELECT '@l'
 SELECT @l  ---***** size is 4804
 
while @i<=@l
BEGIN

      
       SELECT 
                @estring =  @temp + 
                                              SUBSTRING(CONVERT(VARBinary(MAX),DECRYPTBYKEY(Doc)), @i,
                                               --SUBSTRING(Doc, @i,
                                                        CASE WHEN @i + 7899 <= @l
                                                             THEN 7900
                                                             ELSE @l - @i +1            
           
                                                        END)
                   FROM dbo.DocumentFiles WHERE Id = @Id
                                                     
                                          
       set @i = @i + 7900 
                                              
                                                        
end 

SELECT DATALENGTH(@estring) --**** size is 4761

Open in new window


If I need to do all this in code, i can go that route if this doesnt work
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Camillia

ASKER

hmmm, let me try that. I'm using this now
http://support.microsoft.com/kb/307010

maybe you're right...the size is too small...let me try