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

Rusty

Well-Known Member
Licensed User
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:
B4X:
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
 

Rusty

Well-Known Member
Licensed User
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 :)
 
Upvote 0

OliverA

Expert
Licensed User
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
 
Upvote 0

Rusty

Well-Known Member
Licensed User
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
 
Upvote 0
Top