Android Question SQLite INSERT OR REPLACE doesn't work with empty string in key field

[Rusty]

I am trying to use INSERT OR REPLACE but one of the key fields contains an empty space.
For example:
LastName = 'Jones'
FirstName = 'Mary'
MiddleName = ''

Using the code:

CREATE TABLE [Patients] ( [OrganizationID] nvarchar(50) NOT NULL COLLATE NOCASE, [GroupID] nvarchar(50) NOT NULL COLLATE NOCASE, [FacilityID] nvarchar(50) NOT NULL COLLATE NOCASE, [MRN] nvarchar(50) NOT NULL COLLATE NOCASE, [LastName] nvarchar(50) NOT NULL COLLATE NOCASE, [FirstName] nvarchar(50) NOT NULL COLLATE NOCASE, [MiddleName] nvarchar(50) NOT NULL COLLATE NOCASE, [DOB] datetime NOT NULL COLLATE NOCASE, [Gender] smallint NOT NULL, [UniqueKey] nvarchar(20) COLLATE NOCASE, [Language] varchar(3) COLLATE NOCASE DEFAULT 'Eng', [Address1] nvarchar(50) COLLATE NOCASE, [Address2] nvarchar(50) COLLATE NOCASE, [City] nvarchar(50) COLLATE NOCASE, [State] nvarchar(20) COLLATE NOCASE, [ZipCode] nvarchar(12) COLLATE NOCASE, [OfficePhone] nvarchar(20) COLLATE NOCASE, [HomePhone] nvarchar(20) COLLATE NOCASE, [MobilePhone] nvarchar(20) COLLATE NOCASE, [EMail] nvarchar(50) COLLATE NOCASE, [SlingDate] datetime DEFAULT '1900-01-01', [Occurrences] integer NOT NULL, [Persistence] integer NOT NULL, [SurveyIDs] nvarchar COLLATE NOCASE, [Photo] blob(2147483647), [Year] varchar(4) NOT NULL COLLATE NOCASE DEFAULT '', [Make] nvarchar(50) COLLATE NOCASE DEFAULT '', [Model] nvarchar(50) COLLATE NOCASE DEFAULT '', [PurchaseDate] datetime COLLATE NOCASE DEFAULT '1900-01-01', [LastAnswers] nvarchar COLLATE NOCASE, PRIMARY KEY ([OrganizationID], [GroupID], [FacilityID], [MRN], [LastName], [FirstName], [MiddleName], [DOB], [Gender]) )

INSERT OR REPLACE INTO [Patients] VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[OSNC, OSNC, ALL, MJ2, Jones, Mary, , 1952-08-18 00:00:00 00:00:00, 2, null, Eng, 123 Maple, , Denver, COLORADO, 80111-, (303) 351-3349, (303) 351-3856, , mjones@somewhere.com, 1900-01-01 00:00:00, 0, 0, [[]], null, , , , 1900-01-01 00:00:00, abc

If the Row already exists, the INSERT OR REPLACE Duplicates the row.
However, if I give Mary Jones a middle name (which is included in the unique key), IT WORKS!
Is there a way to make this work?
Thanks,
Rusty

 

[Jeffrey Cameron]

[MiddleName] nvarchar(50) NOT NULL COLLATE NOCASE

How can you add a new row with a null value for a not null column? That should cause the insert to fail. Insert or Replace is not insert or UPDATE.

 

[Rusty]

Hi Jeffrey,
It is not a NULL value, it is an empty string... If you'll look a bit further to the right, (just after the 2), there is a null.
Thanks for the suggestion though

 

[OliverA]

It’s not an empty string. Empty string would be “” or ‘’. (Should be regular double quotes or single quotes. I’m on my phone, therefore the weird quotes)

Update: you could do an EXPLAIN QUERY PLAN to see what is going on. https://www.sqlite.org/eqp.html

 

[Rusty]

Hmmm... In columns that are NULL, when I browse them with the DB Browser for SQLite, it shows Null in the column and an empty string in the MiddleName column. I tried putting NULL in that location and it is not the same as the "empty string" value and as such, it causes duplicate rows...

...UPDATE... It is not the empty string , it is that I had messed up the date field! The date field contains yyyy-dd-mm hh:mm:ss where the time is always 00:00:00 and these entries just happened to have yyyy-dd-mm 00:00:00 00:00:00 … two time values...
I was forcing the time to zeros and happened to duplicate it on the INSERT OR REPLACE.
I've fixed this issue and now it works even with the empty string ...
Thanks for your help
Rusty