Too stupid for simple SQLite?

General
1

So I have this database-to-become-one with a table like

CREATE TABLE IF NOT EXISTS "Programs" (
  "ProgramID" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "ProgramName" varchar(128) NOT NULL,
  "ProgramPath" varchar(256),
  "ProgramContent" varchar(10000) NOT NULL,
  "ProgramHash" blob NOT NULL
);

I am reading numerous text files, generate a content hash, check if the database knows name, path and hash of the current file and depending on outcome either update, insert or ignore an entry.
Quite basic, and expectedly the code runs without errors.

Only I found some rows will have an empty content column afterwards, which basically shouldn’t be possible. I made sure the size limit is not exceeded by them, I made sure they do have valid content and I also check db.ErrorCode, suspecting there’d be no database exception. Nothing.
An idea why a column which must not be null might have a null content after insert?

Version is Xojo 2023r1, database is SQLite with Xojo default plugin.

var s As String 
var b As Boolean = f.ReadFileMBS(s)
If b Then
  s = s.DefineEncoding(Encodings.utf8).Trim
  If s.IsEmpty Then Break
  If isupdate Then
    db.ExecuteSQL("Update Programs set ProgramContent = ?, ProgramHash = ? where ProgramPath = ? and ProgramName = ?", s, hash, path, name)
  Else
    db.ExecuteSQL("Insert into Programs (ProgramName, ProgramPath, ProgramContent, ProgramHash) Values (?,?,?,?)", name, path, s, hash)
  End If
  If db.ErrorCode <> 0 Then
    s = db.ErrorMessage
  End If
2

You haven’t insisted that ProgramPath not be Null. And why do you think you have prevented a size limit being exceeded? Varchar(1000) is ignored by SQLite; that column will have type TEXT.

3

Yes, it’s ok that ProgramPath can be Null. But not content.
I thought I remembered SQLite ignores most of the column hints, but having become more accustomed to Postgres I wasn’t sure. So that cannot be the cause of the failure obviously.

4

Just shooting in the dark here:

  1. might there be a difference between s.IsEmpty and s=“”?
  2. is the content really empty or could it be “ “ with the space character being something else than ASCII 32?
5

Thanks, Christoph.

  1. No, there shouldn’t be.
  2. I don’t think so after the trim. The original files are definitely not empty and I would not expect ReadFileMBS to put out something different than the content. I also debugged the code now for those rows and no, there is content on the insert. Still, the database content is Null, so shouldn’t the database prevent that?
  3. The same is valid for all varchar columns being defined as Set without a limitation.
6


 and the same too if I change the column type to blob. Which, I know, should not make a difference, and according to SQLite docs the size limit for a string or blob is 1 billion by default. Which should be sufficient for strings between 7000 and 30.000 bytes approximately.

7


 and it definitely is no size limit. Bigger files have been saved successfully. There is only pure UTF8 text content in the files, nothing special.
I am really clueless.

8

What does NOT NULL mean to you?

Hint: NULL is not the same as “an empty string”. This means that SQLite will be perfectly happy for you to write an empty string to a column, even if defined as NOT NULL.

9

Yes, ok. But there is no empty string I write 

And after insert, the column is Null. Not empty.

Edit: Like this:

Bild 11.04.23 um 20.13
Bild 11.04.23 um 20.132770×230 143 KB

10

How do you know it’s NULL?

11

What program is that? It may decide to represent an empty string by the text ‘NULL’. I would be inclined to use the sqlite3 CLI program and ask it:

select count(*) from programs where somecol is null;

and put the suspect column instead of ‘somecol’;

12

So in your case that would be:

select count(*) from programs where programcontent is null;

If that returns non-zero you have NULLs. Otherwise empty strings, check for these with:

select count(*) from programs where programcontent='';
13

Too many guesses. Get some reproducible basic test sample content together in a zip file and we can find where the problem is without guessing.

1 Like
14

Ask chatGPT, problem solved :grin:

15

Oh this is frustrating. Thanks all!
Usually I use TablePlus for database operations. This project grows on my old iMac, which is not my main development machine anymore, so the license for TablePlus moved to my M1 MacBook.
For SQLite, I had found SQLite Pro to be nice and sufficient in former times, and I started to re-use it and made an update recently. Obviously that does not work as well as in the past.
I opened the db with TablePlus (in demo mode) and found the Null columns were not null but had some hex encoded (but garbled) data inside. I then deleted the database, reran the app and did NOT use SQLitePro to investigate the data. And that’s now TablePlus’ preview 
 for all rows.

Bild 12.04.23 um 01.30
Bild 12.04.23 um 01.302742×308 115 KB

Case closed. Buggy viewer update