SQLite database or disk is full

I get error on ExecuteNonQuery (INSERT …):

[quote]
Error invoking method “Gadgeteer.Modules.Matjaz.MatjazTemperatureHumidity” (check arguments to Program.BeginInvoke are correct)
#### Exception GHI.Premium.SQLite.SQLiteException - 0x00000000 (1) ####
#### Message: database or disk is full
#### GHI.Premium.SQLite.Database::PrepareSQLStatment [IP: 0016] ####
#### GHI.Premium.SQLite.Database::ExecuteNonQuery [IP: 0012] ####
#### SmartHouse.Database.SQLiteDatabase::ExecuteNonQuery [IP: 0016] ####
#### SmartHouse.Sensors.TemperatureHumidity::TemperatureHumidity_MeasurementComplete [IP: 0024] ####
#### Gadgeteer.Modules.Matjaz.MatjazTemperatureHumidity::OnMeasurementCompleteEvent [IP: 0042] ####
#### System.Reflection.MethodBase::Invoke [IP: 0000] ####
#### Gadgeteer.Program::DoOperation [IP: 001a] ####
#### Microsoft.SPOT.Dispatcher::PushFrameImpl [IP: 0054] ####
#### Microsoft.SPOT.Dispatcher::PushFrame [IP: 001a] ####
#### Microsoft.SPOT.Dispatcher::Run [IP: 0006] ####
#### Gadgeteer.Program::Run [IP: 0020] ####
A first chance exception of type ‘GHI.Premium.SQLite.SQLiteException’ occurred in GHI.Premium.SQLite.dll[/quote]
I am using 16GB sd card. and I have 20MB on it.
On random occasion the SQLite library also destroy table schema. Then the database can not be read with SQLite studio or SQLite expert program. I will post another post, when i catch the error.

Can you show your code please?

Yeah, it can be many things : problem with the SD card density. FAT Format not compliant to 16GB size format, problem with Persistent storage and so on…

I don’t think so. When the file is destroyed, there is no way to get it back. Even PC programs can’t read it. I uploaded a new empty database and now it works. I already insert 200 records and it works. Giving the code would be hard, because there is simply to much, but i will give it a try. By the way, reading all other files works great.
I know it is a hard way to tell anything if I don’t give steps to reproduce the problem. I am just asking, if someone had the same problem.
MatjazTemperatureHumidity is upgraded driver that can take wait parameter in StartContinuousMeasurements method. (http://www.tinyclr.com/codeshare/entry/624)


        private static void TemperatureHumidity_MeasurementComplete(Gadgeteer.Modules.Matjaz.MatjazTemperatureHumidity sender, double temperature, double relativeHumidity)
        {
            if (double.IsNaN(LastTemperature) || Math.Abs(LastTemperature - temperature) >= 0.1)
            {
                DateTime time = DateTime.Now;
                AddTemperature(time, temperature);
                LastTemperature = temperature;
                LastDateTime = time;
            }
            if (double.IsNaN(LastHumidity) || Math.Abs(LastHumidity - relativeHumidity) >= 1)
            {
                DateTime time = DateTime.Now;
                AddHumidity(time, relativeHumidity);
                LastHumidity = relativeHumidity;
                LastDateTime = time;
            }
        }

        private static void AddTemperature(DateTime time, double temp)
        {
            DB.Spalnica.ExecuteNonQuery("INSERT INTO Temperature (DateTime, Temperature) VALUES ('" + time.ToString() + "'," + temp + ")");
        }

        private static void AddHumidity(DateTime time, double hum)
        {
            DB.Spalnica.ExecuteNonQuery("INSERT INTO Humidity (DateTime, Humidity) VALUES ('" + time.ToString() + "'," + hum + ")");
        }

//another file

    public class SQLiteDatabase : GHI.Premium.SQLite.Database
    {
        private string _FileName;
        private Timer _AutoClosed = new Timer(1000 * 60 * 10);  //10 minutes

        public SQLiteDatabase(string fileName, int AutoCloseTimeout)
        {
            _FileName = fileName;
            _AutoClosed.Tick += new Timer.TickEventHandler(_AutoClosed_Tick);
        }

        void _AutoClosed_Tick(Timer timer)
        {
            Close();
            Debug.Print("Database autoclosed");
        }

        public new void Close()
        {
            base.Close();
            Opened = false;
        }

        public new void ExecuteNonQuery(string query)
        {
            if (!Opened)
                Open();
            _AutoClosed.Restart();
            base.ExecuteNonQuery(query);
        }

        public new SQLiteDataTable ExecuteQuery(string query)
        {
            if (!Opened)
                Open();
            _AutoClosed.Restart();
            return base.ExecuteQuery(query);
        }

        public void Open()
        {
            base.Open(_FileName);
            Opened = true;
        }

        private new void Open(string file)
        {
            base.Open(_FileName);
            Opened = true;
        }

        bool _Opened = false;
        public bool Opened
        {
            get
            {
                return _Opened;
            }
            set
            {
                if (_Opened != value)
                {
                    _Opened = value;
                    if (_Opened)
                        _AutoClosed.Start();
                    else
                        _AutoClosed.Stop();
                }
            }
        }
    }

I am thinking in 2 ways.
Maybe 2 parallels ExecuteNonQuery cause troubles.
Execute reading 100 records from database takes 1-2 seconds. Maybe during the reading data it is not safe inserting new records in same table/database.
I notice this database file collapsed around 5 times in 3 days.

IF you’re playing with only one table and 2 fields, I’m not sure that do the plumb for a database is the best idea. Why not simply use a delimited file ?

Because after a year, i could have a few thousands records? With sql i can query record from 1500 to 1600 very easy. With file a little harder.
I don’t know. I saw SQLite and say. Great. Let’s have a database.

I have also noticed something in your ExecuteNonQuery function.

Right after you check if DB is opened it is not guaranteed that it will be still opened.

Timer tick can happen at that point which will close the DB file.

True, some lock is needed there. Thanks.

It happened again. The code:


        private static void AddTemperature(DateTime time, double temp)
        {
            DB.Spalnica.ExecuteNonQuery("INSERT INTO Temperature (DateTime, Temperature) VALUES ('" + time.ToString() + "'," + temp + ")");
        }

Raise error:

Everything was OK for 4 days and now this. I inserted a few hundred records in 2 tables during those 4 days.

You are not encapsulating the temperature value properly. Anything besides an integer in SQL needs encapsulation. Failure to do so can corrupt the database, and leads to failures such as you describe.


        private static void AddTemperature(DateTime time, double temp)
        {
            DB.Spalnica.ExecuteNonQuery("INSERT INTO Temperature (DateTime, Temperature) VALUES ('" + time.ToString() + "','" + temp + "')");
        }

That is a great news. I added lock to all methods and add index on DateTime field. The code broke after a few inserts.
I will reply after a few days if i don’t see the error again or sooner if something goes wrong.

Thanks.

Still not good. Different error, different thread: http://www.tinyclr.com/forum/topic?id=10321