RC6: Trouble with DATETIME NOT NULL DEFAULT 0 after ALTER TABLE in VB6

[tmighty2]

Hello!

I’ve run into a confusing situation with RC6 SQLite and VB6.

I add a column like this:

cn.Execute "ALTER TABLE foo ADD COLUMN lastcheckdate DATETIME NOT NULL DEFAULT 0"

Expectation: all existing rows should then have lastcheckdate = 0.

But when I test it:

Dim nCmdsel As cSelectCommand
Set nCmdsel = m_Cn.CreateSelectCommand("SELECT lastcheckdate, rowid FROM foo WHERE lastcheckdate=?")

nCmdsel.SetDate 1, 0

Set r = nCmdsel.Execute
Debug.Print r.RecordCount   ' --> returns 0

This approach yields the same result:

Dim dt As Date
Dim nCmdsel As cSelectCommand
Set nCmdsel = m_Cn.CreateSelectCommand("SELECT lastcheckdate, rowid FROM foo WHERE lastcheckdate=?")

nCmdsel.SetDate 1, dt

Set r = nCmdsel.Execute
Debug.Print r.RecordCount   ' --> returns 0

Whereas:

Set r = m_Cn.OpenRecordset("SELECT lastcheckdate, rowid FROM foo LIMIT 1")
Debug.Print r.RecordCount   ' --> returns 1

So rows are there, but nothing matches WHERE lastcheckdate=0.

Even more strange:

Debug.Print "value: " & ChrW(34) & r.Fields("lastcheckdate").Value & ChrW(34)
' --> value: ""

Debug.Print IsEmpty(r.Fields("lastcheckdate").Value)
' --> True

So VB6 shows the field as Empty when hovering over the variable.

But then:

Dim iLong As Long
iLong = -1
iLong = CLng(r.Fields("lastcheckdate").Value)
Debug.Print iLong   ' --> 0

No error is raised, and it silently converts to 0.

From pragma_table_info('foo'):

pragma_table_info('foo'):
name: lastcheckdate
type: DATETIME
notnull: 1
dflt_value: 0

So on schema level it looks correct.

Problem:
Existing rows after the ALTER TABLE do not actually contain 0. They behave like Empty/Null/"" in VB6, which cannot be matched with SetDate 1, 0.

Questions:

Is this expected SQLite behavior (default only applies to new inserts, not existing rows)?

If yes, what’s the recommended RC6/VB6 way to normalize old rows (so that they really contain 0 and can be queried with SetDate)?

Thanks a lot for any advice!