Good day - this problem is driving me crazy and I'd much appreciate any
advice you SQLite Gurus could offer.
I am using sqlite3 -version:
3.35.2 2021-03-17 19:07:21 ea80f3002f4120f5dcee76e8779dfdc88e1e096c5cdd06904c20fd26d50c3827
for Windows x86 , and I installed the VSIX package for that version.
I am trying to insert a timestamp with microsecond precision into
a column declared with DDL:
'timestamp NUMERIC NOT NULL UNIQUE PRIMARY KEY ASC'
and I have a UNIQUE index defined for it.
I want to ensure records with identical timestamps cannot be inserted,
yet strftime makes my timestamps identical, so I do (in shell), eg:
INSERT INTO $my_table, (timestamp, ...) VALUES
( strftime('%s', '2021-03-19T11:00:00.123456+00:00', 'utc') + .123456,
...
) ;
When I insert a row into the empty table with the 'sqlite3' shell, the
insert succeeds .
With exactly the same values, used in a C program that does
sqlite3_prepare(), sqlite3_bind() (which succeeds using SAME
values I used in the shell!), and sqlite3_step(), with the relevant
clause of the query being:
strftime('%s', ?, 'utc') + ?, ...
and EXACTLY the same valid values as above being used,
the sqlite3_step() call fails with error :
return code: 19 (SQLITE_CONSTRAINT)
and the error message:
'NOT NULL constraint failed: ${DBNAME}.timestamp'
I print out the values I bind to the first (index 1) and second (index 2)
placeholders , and they are identical to the values I use in the shell query
which gets no error.
Please could anyone suggest where I might be going wrong ?
I have tried to set '.explain on' and '.echo on' in the schema I run
from my code , which creates the table, but no queries / commands
are echoed to stdout / stderr. Is there any way I can get the sqlite
library to print out the actual query it has prepared / is stepping through ?
I can share the C code & build command line with a human email,
but can't post it here due to NDA.
Thanks in advance for any helpful replies !