[yunqa.de] Unique Index failing?
- From: Jamie <dstsol@xxxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Sat, 31 Oct 2009 12:31:40 +0000
Hi Ralf
I have a problem where I am seeing duplicate values in a table, which
seem to contradict a unique index I created at the time the table was
created. I am seeing non-unique combinations of a real date/time value
and an integer value which make up the index.
I am storing a series of values for a day, each a combination of a
date/time (a Delphi TDateTime converted with DateTimeToJulianDate) and a
floating point value. Each day of data is associated with a given
integer value.
I create the table and index with
CREATE TABLE IF NOT EXISTS PeriodData (
UID INTEGER PRIMARY KEY,
CtID INTEGER,
PeriodDT REAL,
PeriodVal REAL);
CREATE UNIQUE INDEX IF NOT EXISTS idxPeriodIDDT ON PeriodData (CtID,
PeriodDT);
I then insert and sometimes overwrite values in the table with a loop like
Stmt := DB.Prepare('INSERT OR REPLACE INTO PeriodData VALUES (?, ?, ?,
?);');
for ValIndex := 0 to Pred(ValCount) do
begin
Stmt.Bind_Int(2, CtID);
Stmt.Bind_Double(3, DateTimeToJulianDate(ValArray[ValIndex].ValDT));
Stmt.Bind_Double(4, ValArray[ValIndex].Value);
Stmt.Step;
Stmt.Reset;
end;
I am ending up with apparently identical date/time stamps for a given
integer value, such as two occurrences of "4, 2455104.1667". The
duplicate times all seem to be at 16:00, 17:30, 19:00 or 22:00.
I assume that either the raw floating point values are fractionally
different and I am failing to find a way of displaying them with
sufficient accuracy to display the difference (is there a way to see the
raw value stored?), or that I am managing to write some data in a way
the circumvents the index?
Any suggestions would be appreciated.
Best regards,
Jamie
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
