Is this a bug or what?

Hi all,
Have noticed something recently with SQLlite which I wonder if it is a bug. I have a few personal hobby apps that use SQLlite for storing data. I have found the last couple of months that if I write an empty string field to something like a label or text box, rather than being blank, it shows a “0”. I haven’t seen that before so wondering if it’s a bug with the most recent XOJO updates.

For example:

var rs As RowSet = DB.SelectSQL("select * from SOMETHING")
Label1.Text = rs.Column("FIELD").StringValue

If the field “FIELD” is empty, it results in the Label1 showing a “0”. This has not happened until recently (I can’t say exactly when).

Thanks,
Barry

While you don’t show a complete sample I would say it probably is a “What”.

2 Likes

I know that behavior would break a lot of my code (but I am still using 2019r1.1) as i assume NULL Field String values are always an empty string (“”)!

One easy foolproof way to test it and create an example to report…

You can just create an in memory database without any tables and just do

Dim rs As RowSet = DB.SelectSQL("Select NULL As Nothing")
Dim S as String =  rs.Column("Nothing").StringValue
Break

and look at S in the debugger

  • Karen

If your “FIELD” was created as type TEXT .StringValue will be empty if it was created as INTEGER (for example) .StringValue will show 0 when the database field is empty.

This self contained sample show it working as expected (empty):


var db as new SQLiteDatabase
db.Connect      // in memory

db.ExecuteSQL("CREATE TABLE something (id INTEGER PRIMARY KEY AUTOINCREMENT, txt TEXT);")

db.ExecuteSQL("INSERT INTO something (txt) VALUES ('');")

var rs as RowSet = db.SelectSQL("SELECT * FROM something")

MessageBox("id #"+rs.Column("id").StringValue+" is """+rs.Column("txt").StringValue+"""")

Quit

Now change “txt TEXT” to “txt INTEGER” and run again, you will see “0”

I know it could do it, but I wrote it properly.

Correct, and we don’t know if OP wrote it like you or not.

That’s why I said that my chips are on the “what” bet.

1 Like

Hi all,
Thanks for the effort. I feel like a real dope now. When I write an app that uses a DB I create the DB first in a program called DB Browser for SQLite. It is good (and free) but can be a little finicky at times with selecting field types etc. I’ve just noticed that the fields in the DB’s that are producing “0”'s have been set as INTEGER. When I reset them to TEXT they work fine. Thanks AlbertoD, you inadvertantly caused me to check.

Sorry to waste your time :neutral_face:

Barry

1 Like
  1. I would view this as unwise. If you’re going to create a db outside your app, use the SQLite CLI. That is closest to the metal. DB Browser for SQLite tries to be too clever.

  2. I always create any DB my app needs, plus attendant folders, inside the app, first checking whether some or all bits of the structujre exist already and creating those which do not.

This is right.

You have less chances to make the error you report if you create your Database Rows by yourself.

You can also add a module/window to display your db structure (not data), something like:

You may even add code to buttons to change the db structure…

Hi Tim, thanks for the advice. Thinking SQLite CLI was an app, I googled it and found myself on the SQLite site and now assume it stands for something like SQLite Command Line Interface (or similar) - yes??

Anyway, I downloaded a ZIP file, and the apps in that kept opening what looks like terminal and I don’t really want to mess around there.

Emile, I also appreciate your comments, but not totally sure what the image is representing.

I’ve been using DB Browser for a while now and this is the first time I’ve had this issue. Just have to be careful. Also, I only use it to create the initial DB, my app then uses that DB and does everything else within - creating any extra tables, updating etc.

You guys are both, no doubt correct, but this works for a humble hobbiest like me (well, most of the time :thinking:)

Barry

It is a graphical representation of a SQLite database.
(the SQLite structure, schema).
It is draw using Xojo.

Available TABLEs list the created TABLEs, (on the left) and on the right, you have the details of the TABLE (Record Schema).

In the software, I choose a sqlite file and it draws that to me. So easy to understand.

It’s not Terminal (although you run it in Terminal) and what you type there is either SQL commands like you have in your program, or dot commands to set how it works or give other information. Such as .schema which will list your tables and their structure.

How to add a SQLite TABLE (from the documentation):

This code creates the Team table:

' db is a SQLite database
Var sql As String
sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"
Try
  db.ExecuteSQL(sql)
  MessageBox("Team table created successfully.")
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

No need to use a Third Party software (even a free one)… :wink:

Hi Emile,
Thanks. I do understand that code and have (and do) use it if I need to create a table in the DB. For example, one of the apps I have is a budgeting app that when a new item is added requires its own ledger table so I use that type of code to create that.

But, 2 things tho, firstly, that code only creates the table, it does not create the actual DB so it must exist first and, secondly, unless it’s an app as above where tables may need to be dynamically added (which in my experience is the usual case), is it not just a waste of some code to have something hard-wired in that will only ever be used once?

Note, I’m questioning you guys, just trying to understand the whys sometimes as well as the hows.

Barry

Tim,
Ok, I wasn’t game to try anything once that terminal opened up.

I’m about to show my age here, but is it similar to the way one would work with the old dBase database from the (??) 80’s?

Barry

You can easily create the db-file in Xojo. This enables you to check if the file exists and if not: create it.
Doing all in Xojo also has the advantage that you could use the included encryption - something most free sqlite editors dont support, because then they would have to pay a license fee for the encryption…

Thomas,
Thanks for the reply. Yeah, I am aware that you can create it from the app but I never have till now because, not being a pro and just programming for the fun and mental stimulation, they are only for me so there is no real need to have the app create the DB. However, given all the feedback on this, I’ll start doing it, again as an exercise in learning.

Thanks (and everyone else)
Barry