SQLite Forum

Bug ? Same query that works in shell produces constraint violation when run by a C program with sqlite3_{prepare,bind,step}
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:


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 !