SQL Lite DB

Databases
1

Hi,

I am new to xojo 2020. I have a project in mind that uses a sql database. When creating the following fields for my table: id (Integer), startingDate (Date) and amount (Double), as I save the fields get saved as: id (Boolean), startingDate (Unknown) and amount (Time). Is this a known bug or am i missing something? I tried creating the table in xojo and also tried creating the table in DataGrip (DataGrip allows me to save the fields as integers, dates and double) but when i bring the table to xojo, it converts them to boolean, unknown and time. Any guidance is greatly appreciated.
TIA.

2

Back to basices:

a. What is the contents of the “sqlite_master” Table ?

b. Did you wrote some records into the table; and did you read them ?

Why do you think ID is a boolean ? Because it holds 1 ? If teh database holds only 1 record, that ID value have to be 1 (IMHO).

3

C P is adding a new SQLite db to the Xojo project like this:

image
image706×233 50.2 KB

then when is saved and reloaded, it looks like this:
image

Sorry about startDate and startingDate, I had to recreate the db again because I didn’t take a screen shot and changed the name the second time.

I guess all people using Xojo with SQLite db is not using that feature to create the database, they should be using code to create the table or other program to create the database. You can learn more with information from the docs, examples included with Xojo, YouTube videos and the blog.

1 Like
4

  1. for testing, use the sqlite3 CLI program to verify your sql. That is written by the creators of SQLlite.

  2. SQLite has no Date type. Types are integer, real, text, blob. See:

https://www.sqlite.org/datatype3.html

5

Thank you All. And yes Tim u are correct and i am aware that Date type does not exist in SQLite but if you read the a little more down the page you can see SQLite will convert (Using Type Affinity) other sql common datatype to SQLite types. For example my understanding is that Date gets converted to “numeric” but if you see, Xojo is reading or converting it to “unknown”. Why not to “numeric”. The other strange case is the integer type. SQLite does understand integer
yet the xojo ide converts it to ‘boolean’. That to me could be a possible bug?

6

Well I create all my SQLite databases in code, not using the IDE at all for that. If a user runs my app and is starting from scratch, the app will create 10 databases on disk. All in code.

7

It is a bug in that it purports to do something, but doesn’t do it well. That applies to the database editor as a whole, not just the items you’re reporting. The IDE database editor has never really worked right. I strongly recommend not using it.

Best practices are

  1. Do not use the built-in database editor.
  2. Do not drag a database into your project and allow the framework to manage it.
  3. Always connect to your database in code.
  4. Create the database in code if you don’t have a 3rd party tool handy.
2 Likes
8

And all should have the sqlite3 CLI program handy, both for creating production databases but also testing their SQL without needing to run their app.