I did check and I have all the BEGINs and COMMITs. I understand that by default
the sqlite busy signal is given right away so what I do is I wait till the next
interval elapses to try to update the DB again by that time whatever the
transaction that causes the DB to block me before should have been completed.
These transaction takes between 0.03sec to 1 sec. My fastest thread update at
every 10 seconds. Here is part of the code.
public void UpdateOneHourTable(System.Data.Odbc.OdbcConnection conn, float[]
counterValues, int startRow)
{
string CommandText = "BEGIN;";
System.Data.Odbc.OdbcCommand command = new
System.Data.Odbc.OdbcCommand(CommandText, conn);
command.ExecuteNonQuery();
int rowId = startRow;
for (int x = 0; x < counterValues.Length; x++)
{
CommandText = "UPDATE OneHour SET DateTime=datetime('now','localtime'), Value="
+ counterValues[x].ToString() + " WHERE RowId=" + rowId.ToString() + ";";
command.CommandText = CommandText;
command.ExecuteNonQuery();
rowId++;
}
command.CommandText = "COMMIT;";
command.ExecuteNonQuery();
}
public void UpdateOneDayTable(System.Data.Odbc.OdbcConnection conn)
{
string CommandText = "BEGIN;";
System.Data.Odbc.OdbcCommand command = new
System.Data.Odbc.OdbcCommand(CommandText, conn);
command.ExecuteNonQuery();
//Delete any entries that are over one day old.
CommandText = "DELETE FROM OneDay WHERE DateTime <
datetime('now','localtime','-24 hours') OR DateTime >
datetime('now','localtime') OR Value < 0;";
command.CommandText = CommandText;
command.ExecuteNonQuery();
//Get the average values in the past 5mins from each individual counter in
OneHour table and put it in the OneDay table.
CommandText = "INSERT INTO OneDay SELECT CounterId,
datetime('now','localtime'), avg(Value) FROM OneHour WHERE DateTime >=
datetime('now','localtime','-5 minutes') AND Value >= 0 GROUP BY CounterId;";
command.CommandText = CommandText;
command.ExecuteNonQuery();
command.CommandText = "COMMIT;";
command.ExecuteNonQuery();
}
_________________________________________________________________
In a rush? Get real-time answers with Windows Live Messenger.
http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_realtime_042008
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
