How can I check if a table has already a row?
I am working on a Preferences table which needs 1 row to save and read the changes made in the app. This isn’t a problem with the database that I created beforehand in another app, but when creating a new DB with code, I cannot find a way to add that single row.
You can use the REPLACE function of SQLite to insert or replace a row (INSERT)
App.DB.ExecuteSQL("REPLACE INTO <tablename> (<fieldname>) VALUES (?)", "Value to insert")
What my app does at startup is to check whether each database the app uses is present, and if not, creates it. At the same time as creating the databases, for some tables (e.g. for the global settings) it inserts a single row with the default values for all those global values.
In other words, create the row when you create the table.
You use a RowSet to read the ROWS of a DataBase:
RowSet
Once there, you can ask to know how many ROWs there is/are in that TABLE:
RowCount
You can even read that ROW contents when you know you have 1 or more ROWs in that TABLE.
You do not know how to add a Row in a TABLE ?
What’s the actual code that fails ?
But if you organise your app properly, you don’t have to resort to these tricks later.
Yes.
Yes, I used this book to learn how to use Xojo and the sqlite database, really helpful. But I don’t remember there was info about how to add a row in a database that was created by code. Maybe the update has info, I will check.
The problem arrises in the workflow. I am creating a new database with code which then is saved on the hard drive (following the book “I wish I knew …”), so it’s not open at that time of creation. To add a row I first need to open it and it’s here where I get into the issue of how do I check if the Preferences table already has a row. I know how to add it, but not how to check if the table already has one, that was my original problem.
Read my first answer far above about RowCount and Reading Records from a Data Base.
All you want to do it to know how many Rows there are in the TABLE (you do not talked about TABLE; did you add one ?) and if there is one ROW, Read it to be sure it have what you search (that is what you want to do anyway…).
OR… are-you waiting for code ?
Yes, I read the answers above and will try this out, I was merely clarifying my original question. Thank you. And as I mentioned above, the table/columns are created by code, I only didn’t know how to check if a table had a row. Nope, not waiting for code 
Then with that code, at the same time, you add the one and only row:
db.ExecuteSQL ("insert into mytable (col1, col2, col3, ...) values (1, 2, 'a string', ...)")
If you do it this way, then you know that if the table exists, then the row exists.
If you are going to initialise all columns with this, then the first part (col1, col2, col3 …) may be omitted so you have:
db.ExecuteSQL ("insert into mytable values (1, 2, 'a string', ...)")
col1, col2, etc are your column names, in the order they appear in the table.
Why are you doing these two things separately? Do them together and your problem ceases to exist.
Except you, everyone creates data bases with code. Even the Language Reference gaves code to achieve that.
Here’s code that works with Xojo 2022r2.
It creates a .sqlite file near the project file, add a TABLE, and adds a Row with minimum data (Four COLUMNs are filled only, but it is a simple example).
The documentation contains all, but one have to takes bribes here and there…
I hope it will works with you.
Var db As New SQLiteDatabase
Var DB_FI As New FolderItem("AVH_DB.sqlite")
// Set the FolderItem to the db Reference
db.DatabaseFile = DB_FI
Try
db.CreateDatabase
Catch Error As DatabaseException
MessageBox "Unable to create the Data Base file."
Return
End Try
If db.Connect Then
// Add a TABLE And Rows
Var sql_Cmd As String
sql_Cmd = "CREATE TABLE IF NOT EXISTS Employee(" +_
"Name_First TEXT, " +_
"Name_Last TEXT, " +_
"Job_Title TEXT, " +_
"Job_Salary Text, " +_
"Employe_ID TEXT, " +_
"Employe_Photo TEXT, " +_
"Employe_Entry_Date DATE, " +_
"ID INTEGER NOT NULL, PRIMARY KEY(ID));"
Try
db.BeginTransaction
// Add the TABLE
db.ExecuteSQL(sql_Cmd)
db.CommitTransaction
Catch error As DatabaseException
MsgBox("Database error: " + db.ErrorMessage)
db.RollbackTransaction
End Try
// Add a Row
Var row_DBR As New DatabaseRow
// Fill the row
row_DBR.Column("Name_First") = "André"
row_DBR.Column("Name_Last") = "van Haren"
row_DBR.Column("Job_Title") = "DB Developer"
row_DBR.Column("Employe_Entry_Date") = "2016-08-16"
Try
db.AddRow("Employee", row_DBR)
Catch error As DatabaseException
MessageBox("(AddRow) DB Error: " + error.Message)
End Try
Else
MsgBox("Unable to open the Data Base File. Error: " + db.ErrorMessage)
End If
Emily, we seem to misunderstand each other, like I mentioned a couple of times in this post, I am creating the database with code.
Thanks Tim, I got it to work this way. I was confused by the fact that I was trying to write to the Preferences table before this way, but actually didn’t have the db loaded because the CreatedDatabase was saved to the hard drive and not immediately loaded in the app. A blind spot from side…
I shared Xojo code… working Xojo code.
Can you define what kind of code you are talking about ?
What do you mean by “have the db loaded” ?