In my test desktop program I have 10 TextFields and 1 TextArea. I want to populate these with data from a SQLite database that already exists. I have looked in the documentation and other examples I found on the web but, in all of the cases that I found so far, it gives example code to connect to the database but it doesn’t tell me where to put this code. I’m also not sure where you would put the code to populate these fields. Can anyone tell me where I can find this kind of info, maybe a pdf or something?
Have you checked out the examples supplied with the IDE? Examples->Database->SQLite are worth looking at.
I looked at some of the examples and I see the code. Maybe it’s just me but I still don’t see where that code goes relative to my app. I was trying to duplicate an app that I wrote in Purebasic as a way to test xojo before purchasing it but, while I find that xojo is really quick at designing a screen layout, I find it easier to code SQLite databases in Purebasic. In Purebasic I just create a procedure to connect to the database and one to retrieve that data to populate the fields and then just call them when needed.
It should be the same in Xojo. Unless I’m missing something.
It also really depends on the workflow of your app.
Trying to understand your question:
Database files are FolderItems.
The example code from Database.Connect
Var dbFile As FolderItem
dbFile = Folderitem.ShowOpenFileDialog("")
If dbFile <> Nil Then
Var db As New SQLiteDatabase
db.DatabaseFile = dbFile
Try
db.Connect
MessageBox("Connected to " + dbFile.Name)
Catch error As DatabaseException
MessageBox("Error: " + error.Message)
End Try
End If
This code will allow you to Connect with the database that in the above case you have selected manually using a Finder (or Windows equivalent) File dialog. In your code, it is called db.
Where are you going to run this code? For testing, you could run this code in the Action event of a Push Button. As a continuation of the code in the Action event, you could write the code to get the data from the database
If you want this to be a Global, that you can access throughout your program, one option would be to create a Module with some explanatory name like MyDatabase. You could create one property of MyDatabase that was a SQLiteDatabase and call it something like myInfo and run code like the above to connect myInfo with the SQLiteDatabase on your disk.
But from your question, I wonder if your problem has nothing to do with the code itself but rather “where that code goes”. This may, in turn, reflect unfamiliarity with an event-based language like Xojo and have nothing to do with databases per se. So if the notion of “putting code in the Action event of a Push Button” seems very foreign, then you need to start with the beginning materials that explain the basics of the Xojo language. It is a road worth taking.
2 Likes
As @Robert_Livingston already wrote, Xojo is event based. So the simple answer would be, put your db connection code into an event that raises before you access the data in the database. This could be the App or Window Open Event for example. You would then add the database object as a property to a Module or the App object for example, to be able to use it later.
But with local database files, you can even connect-transact-disconnect in one “big” method/code. Because accessing the file should be relative fast.
And when are they needed? You haven’t told us that. If the fields need populating when you press a button, then put the calls to your database code in the button’s Action event handler.
Thanks to all of you for the responses.
I think that part of the problem may be that in other languages that I have used such as dBase, Clipper and Purebasic, for example, I just write the code using procedures etc and calling them as I need them. I am not used to dragging objects onto a window and then writing code for that object. Actually, I can see how that is done in xojo. For example, I dragged a button into the main window and then I added code to tell the app to exit whenever it is pressed. Where I am confused, I guess, is that the database is not something that I drag into the window, so I don’t know where to put the code. In the case of the button, the code is attached to the button but, in the case of the database, there is nothing in the window to attach it to.
Maybe it is easier if I try to explain how this app is supposed to work. The app is called “PrehistoricLife”. When the app is run it opens the main window which has 9 TextFields located vertically down the left side which will contain information about the prehistoric animal,… name, size, diet etc. In the center is a TextArea that contains a few paragraphs of information about how and where the animal lived. On the right side is a TabPanel where the first tab will contain a picture of the animal and the second tab will contains an image showing the size of the animal as compared to a human.
When the program is first executed, it should connect to the database, retrieve all of the data in the first record and populate the TextFields and the TextArea. The images in the TabPanel are not held in the database. They will be populated by getting the images from a folder. I have a “Previous” and “Next” button. When the “Next” button is pressed it should get and display the next record in the database and, of course, the reverse for the previous button. I Think that I can figure out how to attach the database code to the “Next” and “Previous” buttons. What I don’t understand is where do I put the code to connect to the database, and then retrieve and display the first record when the app is first executed.
If you’re writing code that is unlikely to be reused elsewhere in the app, then in the instance I referred to, put it all in the button’s Action event handler. Doing stuff with a database is just code, so what reason could you have not put put it there, if that’s where it’s needed.
I have lots of code in event handlers, but I also have lots of methods that are general. So, for databases, I have wrapper methods around what Xojo supplies to handle error cases and in particlular the SQLITE_BUSY error. Those methods and others I put in separate modules or folders, see the library pane for their icons, and put things in there. Then I can refer to them from where I need to.
For startup, put code in the app’s Open event.
As for connecting to the database, do that where you need to. You could connect to it once (in the app’s Open event handler), or for example in each button’s Action event handler. It’s up to you, really. If you choose the former, then you need the app to have a property that you store the database’s handle in so you can refer to that from where you need it.
You may feel unable to decide these questions definitively at the moment. So I’d say don’t worry about it, choose which way ro go and thus get an app going. You may decide later to move things around but that’s not a major problem.
Thank you very much. My problem was not so much about attaching code to a button because I think that I understand that part. It was more the fact that I didn’t know where to put the code to connect to the database and populate the fields with the data from the first record on the initial startup of the app. So, from what you said, I would put that code into the app’s Open event handler. After that I would attach code to the “Previous” and “Next” buttons to navigate through the database. I’ll give that a shot. Thanks again.
The app open event occurs before the controls are instantiated, so it’s probably best in your case to put your code in the window open event, which happens after the controls are created.
And generally speaking, it’s not recommended to put any task which work with non-highly available data in open events. Even while we are talking about a local stored database file in your case, i’d “never” recommend to connect to and pull data from a database during an open event.
A quick and dirty way, could be adding (by drag&drop) a timer to your window and set the period to 200-300ms. Then in the timer you add the code to connect to the database. This would give your Window time to present (draw) all the controls, before you access any external source. 
Do you think he knows abut timers ? This guy do know nothing about ecents, so timers…
Just a simple remark; no bad meaning.
1 Like
Maybe not yet, maybe he does. But that’s not the point. Just trying to be helpfull.
But i hear what you say @Emile_Schwarz . Thank you 
Your code can go almost anywhere, you can add a Module to your project and then put the Method that contains the code in it, and call it from wherever you need to. Just be sure to include a reference to the database and whatever else is needed as either arguments to the method or as global variables, of properties of a class that you pass to the method.
Thanks again everyone. I really appreciate all of the help. I have been able to connect to the database and I am now going to do some reading on how to retrieve the data from the first record in the database and populate the fields upon initial execution of the app. Once I figure out how to retrieve the first record and to populate the fields I am confident that I will be able to code the “Previous” and “Next” buttons to navigate through the records.
Thanks again.
1 Like
The Language Reference have everything you need to achieve that (I talk for SQLite): code snippets…