Main Site Documentation

SQLite database VACUUM command


#1

Hi everyone!

I’m using the GHIElectronics.NETMF.SQLite library (with the BreakContinue SQLite wrapper: https://www.ghielectronics.com/community/codeshare/entry/142) and everything is working fine, except I can’t execute a VACUUM command (https://www.sqlite.org/lang_vacuum.html).

Here’s the code:





```cs
try
{
                _connection.Open();
                string sqlString = "VACUUM";
                var command = new SQLiteCommand(sqlString , _connection);
                command.ExecuteNonQuery();
}
catch (Exception e)
{
                SharedInterfaces.Logger.Instance.LogException(e);
                result = false;
}

I get the following exception:

#### Exception GHIElectronics.NETMF.SQLite.SQLiteException - 0x00000000 (6) ####

 #### Message: Could not execute SQL statement.

 #### GHIElectronics.NETMF.SQLite.Database::ExecuteNonQuery [IP: 0022] ####

 #### BreakContinue.Data.SQLite.SQLiteConnection::RunNonQuery [IP: 001b] ####

 #### BreakContinue.Data.SQLite.SQLiteCommand::ExecuteNonQuery [IP: 0021] ####

A first chance exception of type 'GHIElectronics.NETMF.SQLite.SQLiteException' occurred in GHIElectronics.NETMF.SQLite.dll

Is the VACUUM not a supported command in GHIElectronics.NETMF.SQLite? auto_vacuum (http://www.sqlite.org/pragma.html#pragma_auto_vacuum) seems to be working fine. If so is there an alternative to keep the databse small and defragmented?

Thanks in advance,
istee.


#2

@ istee - Just to be sure, could you try an execute that command using just our library in a small program?


#3

@ John - Thanks for your reply. I’m already home, but I will try it first thing tomorrow.


#4

@ John - I tried running the GHIElectronics.NETMF.SQLite library with a small program.

Here’s the code:

using System;
using Microsoft.SPOT;
using GHIElectronics.NETMF.SQLite;
namespace GHIMFVACUUMTEST
{
    public class Program
    {
        public static void Main()
        {
            var database = new Database();
            try
            {
                database.Open(":memory:");
                database.ExecuteNonQuery("CREATE Table Temperature(Room TEXT, Time INTEGER, Value DOUBLE)");
                database.ExecuteNonQuery("INSERT INTO Temperature (Room, Time, Value) VALUES ('Kitchen', 010000, 4423)");
                database.ExecuteNonQuery("INSERT INTO Temperature (Room, Time, Value) VALUES ('Living Room', 053000, 9300)");

                database.ExecuteNonQuery("VACUUM");
            }
            catch (Exception ex)
            {
                string err = Database.GetLastError();
                Debug.Print("Error message: " + err);
            }
        }
    }
}

Unfortunately, the result is the same:

The thread '<No Name>' (0x2) has exited with code 0 (0x0).
    #### Exception GHIElectronics.NETMF.SQLite.SQLiteException - 0x00000000 (1) ####
    #### Message: Could not execute SQL statement.
    #### GHIElectronics.NETMF.SQLite.Database::ExecuteNonQuery [IP: 0022] ####
    #### GHIMFVACUUMTEST.Program::Main [IP: 002d] ####
A first chance exception of type 'GHIElectronics.NETMF.SQLite.SQLiteException' occurred in GHIElectronics.NETMF.SQLite.dll
Error message: 

#5

@ istee - It looks like you’re using an older version of our SDK. I tried with our latest SDK and the command still didn’t work. We will take a look and see if we can find anything.


#6

@ John - Thanks for your reply. Please update this post if there’s any development regarding this issue.


#7

@ istee - We will.