I'm writing a small backup app, where a folder is selected, zipped, and stored in an SQLite database as a blob along with it's MD5 hash, date, paths ect....

My problem is that when the zipped file is around 600MB+ I get an SQLiteExecption out of memory.

The error was here, but it will not post :(

Here is the Method in which it errors out.

/// <summary>
        /// Backs up a zipped folder to the database
        /// </summary>
        /// <param name="foldername">Name folder to backup</param>
        /// <param name="path">Path to the temp zip file</param>
        /// <param name="source">Path to the original folder location</param>
        /// <returns>True if successful, false if not</returns>
        private bool Backup(string foldername, string path, string source)
        {
            string hash = FileHash(path);

            Debug.WriteLine("Backup");
            Debug.WriteLine("foldername " + foldername);
            Debug.WriteLine("path " + path);
            Debug.WriteLine("source " + source);
            Debug.WriteLine("hash " + hash);

            SQLiteCommand cmd = new SQLiteCommand(db);

            bool backupexists = TableExists(foldername);

            Debug.WriteLine("Table exists " + backupexists.ToString());

            cmd.CommandText = "CREATE TABLE IF NOT EXISTS " + foldername + "(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT"
                + ", data BLOB"
            + ", tempfile VARCHAR(2048)"
            + ", sourcepath VARCHAR(2048)"
            + ", hash VARCHAR(32)"
            + ", updated datetime);";

            int rowsaffected = cmd.ExecuteNonQuery();

            Debug.WriteLine("rows affected " + rowsaffected.ToString());

            byte[] blob = null;

            try
            {
                blob = File.ReadAllBytes(path);
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                MessageBox.Show("An error occurred reading file\nThe folder was not backed up\n error: " + ex.Message,
                    Properties.Settings.Default.AppName);
                if (File.Exists(path))
                {
                    File.Delete(path);
                }
                Debug.WriteLine("Fail ReadAllBytes");
                this.Invoke(new Dummy(() => { Thread1Complete(); }));
                return false;

            }

            DateTime date = DateTime.Now;

            cmd.CommandText = "INSERT INTO " + foldername + "(data, tempfile, sourcepath, hash, updated) VALUES (@bin, @temp, @source, @hash, @updated)";

            cmd.Prepare();
            cmd.Parameters.Add("@bin", DbType.Binary, blob.Length).Value = blob;
            cmd.Parameters.Add("@temp", DbType.String, path.Length).Value = path;
            cmd.Parameters.Add("@source", DbType.String, source.Length).Value = source;
            cmd.Parameters.Add("@hash", DbType.String, hash.Length).Value = hash;
            cmd.Parameters.Add("@updated", DbType.DateTime).Value = date;

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex) // <<<<<<<<<<<<<<< This exception occurs
            {
                Debug.WriteLine("Fail ExecuteNonQuery");
                Debug.WriteLine(ex.ToString());
                if (File.Exists(path))
                {
                    File.Delete(path);
                }
                this.Invoke(new Dummy(() => { Thread1Complete(); }));
                return false;
            }

            if (File.Exists(path))
            {
                File.Delete(path);
            }
            Debug.WriteLine("Win");
            cmd.Dispose();
            this.Invoke(new Dummy(() => { Thread1Complete(); }));
            return true;
        }

When I've searched the SQLite site for similar problems I cannot find one same as mine, but similar ones are just closed and dismissed because they cannot be reproduced.

I have 4GB physical memory on 32 bit windows 7 pro, using VS 2010.

At the time of failure, task manager reports 42% memory use and the app process Memory (private working set) is at 640MB roughly.

I'm hoping someone might have come across this issue, or have better net search skills than myself.

Thank you for taking the time to read.

(edit)

The error

SQLite error (7): failed to HeapAlloc 640162637 bytes (8), heap=7120000
A first chance exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll
A first chance exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll
Fail ExecuteNonQuery
System.Data.SQLite.SQLiteException (0x80004005): out of memory
out of memory
at System.Data.SQLite.SQLite3.Bind_Blob(SQLiteStatement stmt, SQLiteConnectionFlags flags, Int32 index, Byte[] blobData) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLite3.cs:line 1368
at System.Data.SQLite.SQLiteStatement.BindParameter(Int32 index, SQLiteParameter param) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteStatement.cs:line 334
at System.Data.SQLite.SQLiteStatement.BindParameters() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteStatement.cs:line 228
at System.Data.SQLite.SQLiteCommand.BuildNextCommand() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:line 380
at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:line 387
at System.Data.SQLite.SQLiteDataReader.NextResult() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteDataReader.cs:line 1308
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteDataReader.cs:line 117
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:line 805
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:line 853
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteCommand.cs:line 838
at Backup.Form1.Backup(String foldername, String path, String source) in C:\Users\Susan\Documents\Visual Studio 2010\Projects\Backup\Backup\Form1.cs:line 319

Hey so quick question, did you happen to check the max limit yet?

I quickly google SQLite's limits for Blobs and found something interesting

During part of SQLite's INSERT and SELECT processing, the complete content of each row in the database is encoded as a single BLOB. So the SQLITE_MAX_LENGTH parameter also determines the maximum number of bytes in a row.

I just did a quick scan of this page https://www.sqlite.org/limits.html

I also found on that site that their is a maximum length for the SQL Statement itself. I wonder if maybe that could be your issue, the string is just to massive in size (it's on that same page)

Hi, thanks for you interest.

I will try to increase, but like the linked article says, max length is around 1GB by default, and even with combined blob and other data in row it is nowhere near 1GB.

Yeah I wasn't sure what length was what. It seemed I saw a bunch of different lengths being thrown around for different things. The few I saw just caught my eye.

If you do figure it out can you please let us know? I have been perfecting a custom SQLite library to allow for easier interaction with a SQLite Database and its tables (a complete layer used to interact with SQLite), and am constantly trying to improve it. So something like this could be of great use to me

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.