Extract file in zip format
-
Good afternoon, Devs. My name is André, and I'm new here.
I'd like to ask for your assistance with a problem I'm currently facing. I've taken over the migration of an application where the database is in SQL Server, and there's a table containing files stored in a column of type "ntext." According to the client, these files were compressed in zip format and saved by the previous company's system.
However, I've been unable to extract the information in any way. Below, you can see an image of the table and its contents. I had to create a column of type "varchar(max)" and copy the content from the "docArquivoAssociado" column in an attempt to solve the issue, but with no success.
Any suggestions?
-
whoever stored those in next likely caused them to be corrupted without repair.
zip files should have been stored as image or varbinary(max)
assuming they were stored correctly, e.g. no bytes have been changed/lost, you would save that particular column as a stream to a file and it should then open as a zip file.
-
Its more involved than that, you need to know the text encoding and the specific compression algorithm as well. You will have to know how that was written to the table to reverse it and as frederico alluded to, get it changed to use varbinary as storage ASAP, then extend your table to include the encoding and compression metadata so someone else doesn't have to reverse engineer it.
-
But their system can still extract the files within their Delphi-based system. However, I need to convert this database, but I also need to extract the files. I created a column of type varchar(max) and saved the data from the ntext column, but I'm still unable to do it.
-
where is the current data used by the Delphi system?
and how did you copy it into your new table (and as I said it should be varbinary(max))
and can you ask them for the code where they read from their storage/db table and store on the file - that may also give you some guidance on what to do.
-
ZIP files are binary files so will not save correctly as a string. Binary can be encoded as a string although the size will obviously be increased. One way to encode binary as a string is called Base64. This can be done in SQL Server but probably should be done by the application.
https://medium.com/falafel-software/t-sql-easy-base64-encoding-and-decoding-872ad184bd7e
- Ken McKelvey wrote:
ZIP files are binary files so will not save correctly as a string. Binary can be encoded as a string although the size will obviously be increased. One way to encode binary as a string is called Base64. This can be done in SQL Server but probably should be done by the application.
https://medium.com/falafel-software/t-sql-easy-base64-encoding-and-decoding-872ad184bd7e
as long as you have a varbinary capable field there is no need to store it as a string - hence conversion is not required. and on this case, and based on the image, the file was stored as binary values, but on a ntext (then moved to a varchar) field - this is the bit that is wrong.
as long as the data is correctly stored, saving it to a file is a straightforward step in the majority of languages.
-
In case you did not pick up the crucial bit from the previous post, data stored as ntext must get converted to nvarchar.
If you go from ntext to plain old varchar you will lose some data.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
So, I tried converting to "nvarchar(max)" and "varbinary(max)," but without success. Therefore, I had to resort to a more radical approach, which involved extracting data from the client's portal. This compelled their system to generate a file for me to download and store in my database.
-
you never answered the questions of how you loaded from their system onto SQL Server - neither on how you are extracting it and trying to save to a file - as well as how their own code works.
as well you never told us what ERROR you get after saving the files and trying to open them.
as for your latest comment - I hope you followed our advise and you are loading those files onto a varbinary(max) column - or (likely better for some) using filestream for it.
- André Silveira wrote:
So, I tried converting to "nvarchar(max)" and "varbinary(max)," but without success. Therefore, I had to resort to a more radical approach, which involved extracting data from the client's portal. This compelled their system to generate a file for me to download and store in my database.
I sincerely hope you have a backup before all those changes were made. And, unless you know exactly how to do the unzipping, you need to stop shooting in the barn... you might hit your favorite cow. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Good afternoon, my friend. I apologize for the delay in responding; we encountered some setbacks in the project and had to shift our focus. But to answer your question: Their system is "Desktop," and somehow, it has an integration with their portal, which, in turn, generates the URL for the file through a POST request. I read and save it in my PostgreSQL database.
I created a column in the table called "blob" and converted the content to this column without altering the value of the original column, just to try to extract it. However, even with this approach, I was unsuccessful.
-- Adicionando uma nova coluna blob à tabela
ALTER TABLE dbo.AD_ComissaoPerm_ExpedienteAtaImagem
ADD ColunaBlob VARBINARY(MAX);
-- Atualizando os dados na nova coluna com a conversão de ntext para varbinary(max)
UPDATE dbo.AD_ComissaoPerm_ExpedienteAtaImagem
SET ColunaBlob = CAST(CAST(Imagem AS NVARCHAR(MAX)) AS VARBINARY(MAX));
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply