I have a database with 11 columns. I am trying to figure out how to retrieve the first record in the database and populate 10 TextFields with the data. The first column of the database is “record”, the second is “name” and the 3rd is “meaning”. I want to populate TextField1 with the data in the “name” column and TextField2 with the data in the “meaning” column.
I have been looking at different examples of getting data from a database and so far all I have is the following, which may well be incorrect…
Dim dbFile As FolderItem = GetFolderItem(“dinobase.sqlite”)
Dim db As New SQLiteDatabase
db.DatabaseFile = dbFile
If db.Connect Then
MsgBox(“Connected!”)
Else
MsgBox("Error: " + db.ErrorMessage)
End If
Var sql As RowSet
sql = db.SelectSQL(“SELECT * FROM dino WHERE record = 1”)
The app does connect to the database but I get an error on the line sql = db.SelectSQL(“SELECT * FROM dino WHERE record = 1”)
The error says: MainWindow.Open, line 10
This item does not exist
sql = db.SelectSQL(“SELECT * FROM dino WHERE record = 1”)
@Wayne Golding Thanks Wayne, I changed them to straight quotes and I do not get that error any more however, it is flaging a different error. That line has the little red bug on it and it shows me the following:
Name | Value
db | SQLiteDatabase
dbFile | Nil
Exception |DatabaseException
self | MainWindow.MainWindow
sql | Nil
It says that it has connected to the database so I’m not sure why it doesn’t seem to find the data.
This should only work if you have a column named record but guessing from your error that you do not.
SQLite normally has a hidden column rowid but I believe that does not necessarily start at 1 if you have deleted records. It should be an increasing number though.
And it has row_number(), but that counts row in the result set. Not the original table.
A couple of choices that I suspect will work:
* sql = db.SelectSQL("SELECT * FROM dino ORDER BY rowid LIMIT 1")
* sql = db.SelectSQL("SELECT * FROM dino WHERE row_number() = 1 ORDER BY rowid")
I’d suggest using LIMIT 1 instead of row_number(). Then if you want to later get a row other than the first, add the OFFSET xx clause. The OFFSET is the number of rows to skip at the beginning of the result set. The LIMIT is the number of rows to return in the result set.
So this gets the 5th record (i.e., it skips over 4 record):
sql = db.SelectSQL("SELECT * FROM dino ORDER BY rowid LIMIT 1 OFFSET 4")
Edit: Changed a few instances of smart/curly quotes to standard quotes and wrapped in code tags to make more readable.
@Wayne Golding Yes, the dino table does exist and the first column in the database is “record”.
On my hard disk I have this path: Xojo Projects\PrehistoricLife\dinobase.db The PrehistoricLife.xojo_binary_project is in the PrehistoricLife folder with the database.
The columns in the table “dino” are:
record, name, meaning, pronounce, period, group, size, lived, diet, fossils, factfile
The column “record” is INT and the rest are CHAR.
I don’t know why it can connect to the database but not find the “dino” table.
Ahh, you have the wrong path here, as evidenced your dbFile object is nil
`Dim dbFile as FolderItem = GetFolderItem("\Xojo Projects\PrehistoricLife\dinobase.db")`
may work. But only on your machine. Is this an application you will distribute? And if so, are you intending to include a copy of the database with the app’s resources?
There are various ways to skin this cat. But as coded, you are not finding the file at the specified path. So the SQL select has to fail.
@Douglas_Handy I tried all 3 of your suggestions and I’m still getting the same error. So dbFile is Nil and sql is Nil. If dbFile is Nil, it seems to me that it is not finding the database but if that were the case, how is it connecting?
John, @Douglas_Handy is correct. I don’t know why you’re getting a “Connected!” message but the code doesn’t seem to allow that. Can I suggest that you move the database to your documents folder and access it using
Dim dbFile As FolderItem = SpecialFolder.Documents.Child("dinobase.sqlite")
@Wayne_Golding My mistake… the database file is actually dinobase.sqlite and not dinobase.db. That was an error in my last post. I tried using ```
`Dim dbFile as FolderItem = GetFolderItem("\Xojo Projects\PrehistoricLife\dinobase.sqlite")
but got the same error.
@Douglas_Handy When I run the code I get the messageBox saying “Connected” the when I click the “OK” button I get the error. I may distribute it and the database would be included because all of the data on each prehistoric animal is in the database. There are 115 records.
@Douglas_Handy I looked at the blog but I can’t add a Build Step because it requires a license to do so. I am using the free version to test xojo before deciding if I will purchase it. So far, while it is much easier and faster to design a UI in xojo, I find it much easier to code and work with databases in Purebasic, which is what I was using prior to checking out xojo. I am using the database from my PrehistoricLife app that I wrote in Purebasic. I was trying to duplicate that app as a way of testing xojo.
A couple of comments about your database schema, you don’t need the [ and ] around the column names, and SQLite ignores the character counts. So this would do:
create table dino (record integer, name text, meaning text, ...
Also after any statement such as:
Dim dbFile As FolderItem = GetFolderItem("dinobase.sqlite")
you should put:
if (dbfile.Exists=False) Then
MsgBox ("Could not find database")
Quit ()
End if
That way you know at what stage you have a problem.
