Extracting a blob data file from sqlite

crain1981

If (Decrypt_TextBox.Text = "") Then
MessageBox.Show("Need to put in the file location or select file in grid for decryption")
Else

            'declaring string for file'
            Dim filename As String
            'Getting the file name that is selected'
           
            filename = getFilename()
            'Starting to abstract the data from the database'
            database.ConnectionString = "Data Source = test.db3;"
            database.Open()
            Dim command As SQLite.SQLiteCommand
            Dim sqlstatement As String
            command = database.CreateCommand
            'The following will get the bytes of the file'
            Dim filesize As Integer
            sqlstatement = "SELECT FileSize From Vault WHERE FileName=@file;"
            command.CommandText = sqlstatement
            command.Parameters.AddWithValue("@file", filename)
            Dim reader As SQLite.SQLiteDataReader
            reader = command.ExecuteReader
            filesize = reader.GetValue(0)
            reader.Close()
            Dim data(filesize) As Byte
            'The following should get the data'
            sqlstatement = "SELECT FileData FROM Vault WHERE FileName=@file;"
            command.CommandText = sqlstatement
            command.Parameters.AddWithValue("@file", filename)
            reader = command.ExecuteReader

I have encrypted files in this database. I need to extract this file and decrypt it. I've been thinking about using a memory stream but these files will get fairly large (2GB). This is as far as I've gotten using vb .net framework 2.0. Any help is helpful?

Luc Pattyn

where do the files originate, how did they get encrypted, how did they enter the database? would retrieving them not be pretty much the reverse process? and why is it you store them in the database to begin with? I wouldn't store gigablobs, I'd use files and store the file's path in the db. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

crain1981

At first its encrypted using SHA1 as a file and stored into the database. The problem is that sqlite libraries doesn't give me a clear picture on how to extract the file. After further research and bouncing ideas of people, they have suggested binarystream and it goes make since but i don't know how it will the end will be just yet.

Luc Pattyn

I expect one uses a byte array to write or read a blob field; and a stream to encrypt or decrypt; so basically reading from the database and decrypting should be pretty much the same as encrypting and writing to the database. If the latter works for huge files, so should the former. Suggestion: try and solve the problem without any encryption/decryption first; then add them in. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.

crain1981

Still don't how to extract the file either way.

Luc 648011

I have no experience with SQLite. However I did search CodeProject articles for SQL read BLOB and found a lot, including this: Reading and Writing BLOB Data to Microsoft SQL or Oracle Database[^]. I'm sorry the examples are in C#, that does not change the philosophy, it merely changes the syntax of things. I suggest you read it, or some of the other hits, and try what you learn. I once more stress the fact that IMO writing to a BLOB field and reading from a BLOB field are bound to be very similar. :)

crain1981

I agree with you but now I'm having problems trying to convert the file back to its original form.