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

Rusty

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

Jeffrey Cameron

Well-Known Member
Licensed User
Longtime User
[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.
 
Last edited:
Upvote 0

Rusty

Well-Known Member
Licensed User
Longtime 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
Longtime 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
Longtime 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
Cookies are required to use this site. You must accept them to continue using the site. Learn more…