Long Binary-Daten mit VB.NET

Sehr geehrte Damen und Herren,

ich bin gerade dabei ein Programm (in VB.NET) zu schreiben. Hierzu möchte ich gerne aus einer bestehenden SQL Anywhere Datenbank Daten auslesen. In diesem speziellen Fall, welche im Format LONG BINARY. Leider funzt das nicht so wie ich dies gerne möchte. Die Daten, die in der Datenbank gespeichert sind, lauten:

0x436f6d705a6c69628c000000789c8b76cbcf2b298ee572ce2f2ac82f4a5408ae2c2e49cdb53634b4368042aee890c424a08a68b79cc474206d009434004ae8999b1b012158454066724969516a2c97351080b5a45694c47255db189b585b5a4374d4ba24162b64169728a400e9e092c4bc94c4a214dd80a2fcacd4ec1205454545060073292924

Mittels eines SQL-Befehls (RichTextBox1.Text = SQL_binär_abfrage("SELECT text FROM projekte WHERE PROJEKTINDEX='" & _projektIDX & "'", _conn) möchte ich diese abrufen. In einer Funktion werden sie verarbeitet.

Public Function SQL_binär_abfrage(sql_string As String, connection As SAConnection)
    Dim command As New SACommand(sql_string, _conn)
    Dim buffer() As Byte = Nothing
    Dim reader As SADataReader = Nothing
    Dim result As String = ""
    Try
        reader = command.ExecuteReader
        While reader.Read
            If Not reader.IsDBNull(0) Then
                Dim len = reader.GetBytes(0, 0, Nothing, 0, 0)
                ReDim buffer(CInt(len))
                reader.GetBytes(0, 0, buffer, 0, CInt(len))
                result = System.Text.Encoding.Default.GetString(buffer)
            Else
                result = ""
            End If
        End While
        reader.Close()
    Catch ex As Exception
        MessageBox.Show("Fehler: " & ex.Message & " Nummer: " & ex.HResult, "Fehler Fenster...", MessageBoxButtons.OK, MessageBoxIcon.Error)
        reader.Close()
    End Try
    Dim temp = System.Text.Encoding.Default.WebName
    Return result

End Function

Sie sollen dann in einer RichText-Box angezeigt werden.

Ich wäre Ihnen sehr dankbar, wenn sie mir einen kleinen Gedankenanstoß oder eine mögliche Lösung mitteilen könnten. Vielen herzlichen Dank für Ihre Bemühungen.

Mit freundlichen Grüßen

Matthias

vb.net compression

asked 29 Jul '17, 02:56

Zaunkonig
26●1●1●4
accept rate: 0%

edited 31 Jul '17, 08:38

Volker Barth
40.2k●361●550●822

Hi Matthias,

My German is not so good, I think if you need the translation, you can freely use Google Translate. I want to thank you that you gave me the beginning of the file. If you open it with a viewer, you will see the following content:

File from Hex

It doesn't like a normal document file. If this is a word document, or OpenOffice document, it should have the signature of the Zip archive.

Can you please clarify what kind of content is stored in the DB? Is it "RichText friendly"?

Thanks,
Vlad

(29 Jul '17, 04:10) Vlad

Replies hidden

I know it sounds ridiculous, but Google shows MKV tools where the word CompZlib is mentioned. Are you sure this is a document file, but not an MVK or something meaningless? :) ISO... IMG... whatever

(29 Jul '17, 04:14) Vlad

Hello Vlad,

Thank you for your quick reply.

In the original program it looks like this (https://www.dropbox.com/s/rj53g31tcdvru2v/Control.png?dl=0). The text is different!!

Have already tried to create a file to see what file format it could be. However, all without success. Also ZIP programs I tried. On the Internet I found a Zlib.dll, also this I tried. Even without Ergenis. I thought it was in SQLAnywhere maybe a command, which I have not yet found, which me the data in plain text displays.

Through the old original program I know that the position of TAB, bold, underline, etc. are stored in this binary string. Therefore, I assume that it is friendly to RichText.

Do you have an idea, they have left me. ;-)

Thanks for your help.

Matthias

(29 Jul '17, 05:40) Zaunkonig

Replies hidden

Matthias, I have converted your answer to the comment. Please give me some time to think about it. I will reply later.

(29 Jul '17, 05:55) Vlad

Try SELECT DECOMPRESS ( text, 'gzip' ) FROM projekte ...

Also try running Zlib and/or JCraft to decompress it (but my money is on Zlib since JCraft is a pure Java implementation).

The SQL Anywhere 16 "third-party legal" file C:\Program Files\SQL Anywhere 16\ThirdPartyLegal\SQLAnywhere16ThirdPartyLegal.txt mentions those two products:

JCraft, Inc.

Component:  JZLib Compression Library 

Copyright (c) 2000,2001,2002,2003 ymnk, JCraft,Inc. All rights reserved. 

...

Jean-Loup Gailly and Mark Adler 

Component: Zlib Compression Library 

Copyright(c) 1995-2003 Jean-loup Gailly and Mark Adler. All rights reserved.

(29 Jul '17, 06:25) Breck Carter

Replies hidden

Breck-san, I have tried to COMPRESS a simple text, both compression standards - gzip and zip produce different "headers". They do not have chars 0x43 0x6f 0x6d 0x70 0x5a. I doubt this is (g)zip.

Unfortunately Zlib is just a library rather than an executable file. I am not sure Matthias will be able to decompress it, but it might make sense to decompress the output using DeflateStream. It exists in .NET and uses Zlib.

(29 Jul '17, 07:32) Vlad

Thank you for your support.

The option "SELECT DECOMPRESS (TEXT, 'gzip') FROM Projekte ..." I have tried. Here I get the error message that an argument is missing.

I've been trying it around for some time. The deflate method in VB.NET I will look at and try.

If not, I will have to reject the idea.

Thank you very much for your quick and competent help.

Greetings Matthias

(29 Jul '17, 10:59) Zaunkonig

Replies hidden

What SQL Anywhere version do you use?

Note that DECOMPRESS returns binary data, so you might need to cast it explicitly to char/nchar, see the docs. I would strongly suggest to try the SQL statement directly within DBISQL before using it within application code (unless you need to call a .NET function).

FWIW, I'm a native German speaker. "Du kannst ruhig auf deutsch antworten, auch wenn dann nur wenige antworten können.“

(29 Jul '17, 13:36) Volker Barth

Good Morning

I have tried the different possibilities. DeflateStream, GzipSstream, all to no avail. Also directly in InactiveSQL (SELECT cast (decompress (text, 'gzip') as long VARCHAR) from projects). Here I get the following error message: https://www.dropbox.com/s/c49exx1eu6rpf0i/Fehlermeldung_Decompress.PNG?dl=0

I use version 16, but I know that the old database used version 12.

Wish another nice Sunday.

Greeting Matthias

(30 Jul '17, 05:14) Zaunkonig

Does the same -960 SQLCODE error appear when you use the COMPRESS function with only one argument, thereby leaving it to the function to choose the appropriate algorithm (zip vs. gzip)?

In that case, it has been compressed outside the database, and you might ask in a general forum like SO for further hints on compression...

(30 Jul '17, 06:45) Volker Barth

Or, I am curious to get at least a DB file with a single table and single record, where such binary blob is stored. I'd like to look at it. Cannot believe that it is impossible :)

If you have an old application in .NET, you can try to decompile it to see HOW and WHAT they store in the DB file.

(30 Jul '17, 09:39) Vlad

@Volker Barth: I am not sure, but I believe it always the same error message. I also think that the processing of the data took place not in the database, but externally in the program. I tried all day around. Converts from byte to string and vice versa. With temporary storage on the hard disk and re-read. Everything without success. Then I thought, crap, I just have to forego the data and write my own data into the LongBinary field in the database. The store worked great. With the reading I also have problems. Is no longer converted to RTF format. And can not be processed anymore. Also not if I let the data again on the hard drive write and then load again.

If I write the RTF data into a new column of the type vChar, I can store the RFT data properly and also reload.

So I'm going to go on with it. Too bad, otherwise I would have preferred, but if it does not work, then it does not work. I do not want to exclude that I might make a mistake somewhere, I'm not so fit.

@Vlad: Here you find a zip file with a test database. Hope I did it right, maybe you can start something. https://www.dropbox.com/s/kxqjeokfsdwb5ws/test.rar?dl=0

Thank you very much for your support.

Greeting Matthias

(30 Jul '17, 12:52) Zaunkonig

When you store data of any data type and then read the stored data, it should certainly be identical to the original value. If it is not, an undesired conversion must have taken place.

Can you show how you store and re-read the data, including the data types used?

(30 Jul '17, 13:16) Volker Barth

> it should certainly be identical

...unless it isn't, in the case of lossy compression (Netflix, Spotify, etc).

We may all be correct in assuming this is Good Old Lossless Compression Of Very Important Data... but what if it isn't?

(31 Jul '17, 05:14) Breck Carter

@guys :) I am really out of ideas. I have looked at the DB file that Matthias has provided. All the records start with this interesting CompZlib header. There is a single record that has "Matthias" as data, so I would confirm that the data is stored as it is.

I have tried to analyze the file with some tools (as well as tried to decompress it with openssl-zlib (there is single trick to do this)), but again the file looks normal, but unknown to me.

My next assumption is that the blob data is probably the application-specific format. Is the application available somewhere, or this is a private one?

By the way, I have used the offline tool TrID, and it couldn't determine the file type.

(31 Jul '17, 05:28) Vlad

it should certainly be identical
...unless it isn't, in the case of lossy compression (Netflix, Spotify, etc)

Well, I was simply relating to storing data in a SQL Anywhere database and reading it back into the application, in which case I still assume that original and read values should be identical when appropriate data types are used, and that differences then might be due to inappropriate data conversion (say, storing via char and reading via binary types)...

(31 Jul '17, 08:27) Volker Barth

FWIW, could it be that other FAQ does also apply here (i.e. possibly using a long varchar instead of a long binary to store/read compressed data) - just a very wild guess...

(31 Jul '17, 08:43) Volker Barth

Hello everybody,

I have feared that this will be the result. I have been trying to read and process this data for some time. The fear I had already synonymous, that the data were specially processed and then written into the database. The dataset where Matthias is in it, I have registered as control by SQL.

@Vlad: Thank you very much for your attempts and results.

@All: Also to you a heartfelt thanks, that you have used your time to find a solution for my problem.

I also tried again to convert the data in different data forms, unfortunately, everything without success. So I'll give up the possibility to process this data and go a different way.

Thanks again to all for your support.

Greeting Matthias

(06 Aug '17, 03:55) Zaunkonig

Oh, it is sad :(

If it were a .NET/Java application, there is a chance to decompile it and see how the data is stored.

Thank you for your reply.

(06 Aug '17, 05:21) Vlad

More comments hidden

showing 5 of 19 show all flat view

Be the first one to answer this question!

toggle preview

community wiki:

Follow this question

By Email:

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

By RSS:

Answers

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

learn more about Markdown

Question tags:

compression ×13
vb.net ×3

question asked: 29 Jul '17, 02:56

question was seen: 2,049 times

last updated: 06 Aug '17, 05:21

SAP SQL Anywhere

Long Binary-Daten mit VB.NET

Follow this question

Related questions