DB is malformed after some time.
I just have a timer to test writing to the DB
using System;
using System.Collections;
using Microsoft.SPOT;
using GHI.SQLite;
using System.Threading;
namespace TouchLogger
{
public class SQL_DBase
{
// Create a database in memory,
// file system is possible however!
Database myDatabase = null;
public void Init()
{
try
{
myDatabase = new Database("\\SD\\MicLog1.db");
// Process SQL query and save returned records in SQLiteDataTable
ResultSet result = myDatabase.ExecuteQuery("SELECT * FROM Powerup");
}
catch (Exception Ex)
{
Debug.Print(Ex.Message);
myDatabase.ExecuteNonQuery("CREATE Table Powerup (Room TEXT, Time INTEGER, Value INTEGER)");
Debug.Print("DB Create");
}
try
{
// Process SQL query and save returned records in SQLiteDataTable
ResultSet result = myDatabase.ExecuteQuery("SELECT * FROM Powerup");
//ResultSet result = myDatabase.ExecuteQuery("SELECT * FROM Powerup where Value=777");
String[] origin_names = result.ColumnNames; // Get a copy of columns orign names example
ArrayList tabledata = result.Data; // Get a copy of table data example
String fields = "Fields: ";
for (int i = 0; i < result.ColumnCount; i++)
{
fields += result.ColumnNames[i] + " |";
}
Debug.Print(fields);
object obj;
String row = "";
for (int j = 0; j < result.RowCount; j++)
{
row = j.ToString() + ":";
for (int i = 0; i < result.ColumnCount; i++)
{
obj = result[j, i];
if (obj == null)
row += "N/A";
else
row += obj.ToString();
row += " |";
}
Debug.Print(row);
}
}
catch (Exception Ex)
{
Debug.Print(Ex.Message);
}
myDatabase.Dispose();
Timer MyTimer = new Timer(new TimerCallback(RunMe), null, 5000, 1000);
}
void RunMe(object o)
{
try
{
Debug.Print("Timer!");
myDatabase = new Database("\\SD\\MicLog1.db");
myDatabase.ExecuteNonQuery("INSERT INTO Powerup (Room, Time, Value) VALUES ('Kitchen', 010000, 4423)"); //add rows to table
myDatabase.ExecuteNonQuery("INSERT INTO Powerup (Room, Time, Value) VALUES ('Living', 010000, 777)"); //add rows to table
Debug.Print("DB Insert");
myDatabase.Dispose();
}
catch (Exception Ex)
{
Debug.Print(Ex.Message);
myDatabase.ExecuteNonQuery("CREATE Table Powerup (Room TEXT, Time INTEGER, Value INTEGER)");
Debug.Print("DB Create");
}
}
}
}
@ microt - Roughly how long does it take to become malformed? What are you seeing that leads you to believe it is malformed? What other processes are running on the board?
@ microt - What board are you using? I would run the test again keeping the database open the entire time and not disposing of it and recreating it in every timer callback.
@ microt - I ran the code you provided on a G120 and it ran without issue for over thirty minutes. I would try to just read and write a fairly large file from the SD card and see if the issue persists so we can try and narrow it down.
@ andre - whatâs the result if you dont dispose the database after each write?
It still gave this error
DB Loop!
#### Exception GHI.SQLite.Database+QueryExecutionException - 0x00000000 (5) ####
#### Message: unable to open database file
#### GHI.SQLite.Database::ExecuteNonQuery [IP: 002a] ####
#### TouchLogger.SQL_DBase::Init [IP: 0050] ####
A first chance exception of type âGHI.SQLite.Database.QueryExecutionExceptionâ occurred in GHI.SQLite.dll
unable to open database file
@ John, I tried just dispose and that did not help either.
I think I found the issue. The SDCard as not a static object so was getting disposed by the GC.I am running a test so will confirm this soon
One thing I am still not comfortable is.
If the DB is malformed. How do I fix it.
Restarting the board does not help. Because the DB is still malformed.
Is there anything I can run on the DB so it can fix itself.
I am ok in loosing some values.
But I do not want to delete the DB file in the field and start over.
@ microt - I am not sure how to recover corrupted SQLite databases. You might find more information on SQLiteâs site for that. We did not write the SQLite driver itself, only a few wrapper calls to use in C#.
Iâll reiterate what I said before. You canât help but expect issues if thereâs an interruption during write on any device. Now that you know you had a code issue with SD fix it. As for power, flush frequently. Every insert should have a flush. Make it part of your driverâŚchanges = flush. Put on a battery monitor or backup to help mitigate issues. Do regular backups of the db file in case of catastrophic failure.
@ microt - i donât really use SQLite but if i see the âFailed allocation for 100 blocks, 1200 bytesâ the i would say itâs a memory issue. Should be easy to check if you change your select * to a select top 100 * and see if you still have the memory allocation error.
What you really need to figure out is not how to load all rows into memory at once and try to print them, but to iterate through them. Thatâs totally a SQLite question that you might need specific guidance on - but a quick Bing search tells me that you can use a LIMIT clause with two parameters to bring in only a chunk of data at a time, so you could construct something to help not flood the memory⌠out of interest, how many rows are you talking about, and is printing them out actually an approach you would take??