In my years programming with Filemaker, I always created a window (layout) by placing fields from a database on this or that window. So each widow is essentially an instance of a database. This is obviously different with Xojo, where window objects are not internally connected to database objects. I understand that. I created an Xojo Web app that works fine right now. It lets the user enter data, export that data to a CSV file, and email that CSV file to another person, all while in a browser. That’s fine. Now I want to attach a sqlite file to it for data storage.
From my studying the documentation and videos, if I want to have more the one window access the same sqlite file (only one window will be open at a given moment), it sounds like I can do one of these:
Each time a window opens, have its Open event connect to the sqlite file and test its connection, using a Public Method to do this. Perhaps a Method created by the Session object.
Have the Session object connect the sqlite file and make that file available to subsequently opened windows by placing it in a Public Property of that Session instance.
Subclass the WebWindow class, creating an class called, say, DatabaseWindow. Have Window1 be an instance of that class and have it connect to the sqlite file. Then have all subsequent windows that need to access that sqlite file be instances of this new class.
I know this probably shows a lot a naivete on my part here, so please bear with me while I learn Xojo.
I think I have to make that class be connected to the sqlite file in its Open Event, by definition, instead of having an instance of that class (Window1) do the connecting.
Correct. If you let the DatabaseWindow class do the connection in its Open event all of the subsequence instances of it will have it by default. One place to change code instead of n instances.
FWIW, for web apps we tends to have two standard connections. One in the app object and one in the session. We have a generic method that checks if the session is available and if so uses the session connection. If no session available we check to see if it’s in the main thread or if it’s in a thread of its own. The main thread gets the app object connection but we create a new connection if it’s in another (not main) thread. The drawback to this approach is that if your connection drops, like a db server, you have to take preventative measures to keep the connection alive.
I’m working on another project where the original developer took a completely different path. Anytime he needed to use the database he created a new connection, did the work, and then closed the connection. So any given window might have 10-15 connections at various times. This approach never drops a connection but it does take a little bit of time to make a new connection. A busy window/page can take a long time (comparatively speaking) to having global connections.
I’ve not seen too many apps do the connection at the window/page level but I see no reason why it wouldn’t work. As long as all db work is done in the window/page I don’t see why it wouldn’t work.
Thanks, Bob. Please let me know if this is correct. If I just connect to the database in the App object (or the Session object) and place it in a Public Property called, say, “DB”, I can access it from all Windows at any time, as App.DB (or Session.DB). However, if the database goes down at the server, I’m out of luck unless I have code in each Window to check its connection on Open. I get around this problem by making sure any Window I open is an instance of the DatabaseWindow class I mentioned earlier, as long as all accessing of the database is done in a DatabaseWindow instance.
While the Xojo standard implementation is indeed different from what FileMaker offers, you still can have controls performing just like you are used to. One possible way is to create a custom class that contains a recordset as a computed property. In the setter of the property, you add another line that invokes a notification scheme this could be an event or an interface.
Controls watching this event or that are registered to the interface can now compute their values depending on the recordfield they have been told to display. And it works the other way around too: You can use them to update the current recordset, either instantly like in FileMaker, or you can even introduce a “Save Changes” feature/button.
And thats just one of several possible ways to FileMakerize your Xojo project.
Quite similar the database: You can subclass it and create an error handling that checks if the connection was lost and tries to reestablish it before really reporting an error. And then never have to worry about it outside of this database subclass.
As a general rule: Whenever you feel tempted to install duplicates of a method or property in a variety of places, you can almost certainly do it only once by using OOP. Its very much like normalizing a database structure.
Ulrich was suggesting having your controls set and save themselves. I’m really interested in the method he suggested. I’ve been working on ‘EasyData’ which is a class where you set properties for a ‘Table Occurrence kinda like FileMaker’ and then tell controls to update. Then on LostFocus, it automatically Saves the data.
We should all talk and standardize on a way to do this…
I would be very interested to do so. As you know, I started a framework doing this too, but I realized I made it too complicated and it would have to be redone to make it available for really easy use. The main factor I forgot, I think, was to enclose the database itself on a subclass that makes the whole thing really OOP with events firing when a table changed and so on. I find event structures much easier to handle than the interface implementation I chose.
Basically I started it when I did not have enough experience with SQLite. So, if we can decide on a basic structure, why not join forces and see if we cant come up with real FileMaker feeling for Xojo users. Id gladly share with you what I got so far (which is working, and even fast, though many of the controls in my project draw twice accidentally [which isnt so easy to fix, therefore the redesign idea]!)
Here’s my take on it. We developed and use ActiveRecord in most projects. We looked into subclassing controls and incorporating ActiveRecord with them but in the long run we felt it just made more work and actually made apps MUCH harder to debug.
So, instead of that we have 3 standard methods in every window/page/container that interfaces ActiveRecord data with UI: Load, Save, Validate. Seriously, that’s it.
In the myriad of projects we’ve worked on, there are just too many variations of how some types of data want to be formatted and displayed, and manipulated. In the generic Load and Save events we do that conversions right there, and in one method, so it’s easy to find and fix. Doing it in a subclass you’d have to create n+1 variations for each use-case.
Sure, there are simple things that would be a no brainer. A TextField and a Checkbox for a plain string and checkbox, respectively, are easy. But for things like a Listbox it gets ugly pretty quick. Even simple things like PopupMenu’s can get tricky because are you using the Text or the RowTag for the database. Perhaps what’s worst is a numbers only field. Do you have a decimal? Dollar signs? Thousands separators? How many significant digits? Min? Max? So for every use case scenario you have to test it and maintain it and pretty soon you’ve got a convoluted mess.
In 15 years I’ve seen a couple of developers try the data away subclassed controls approach and it all boils down to is that they are hard to make them generic enough to make them useful to everyone. They tend to be project-specific so putting them in another project is meaningless. I’d much rather spend that time getting a UI that works and using my 3 methods working rather than farting around with an infinite variation of subclasses.
One of the things we are currently working on with ActiveRecord (via the ARGen utility) is the ability to auto generate UI and auto generate some of this Load/Save/Validate code. Still in very initial phases of the project but as how ARGen saved us a ton of time in database code, we’re hoping version 2 will save us some time putting the UI together.
The way I approach this is each control has a Load/Save event (which also acts as validation). It’s a single event, so the mirror image code of loading and unloading the control is all in one place for easy maintenance. When you update one side, you’re less likely to forget to update the other side. This is where you “connect the wires” with the database. It’s not automatic, but it lets the programmer be very precise and focused on how he wants the control to interact with the data. A simple example might be
Function FieldLoad(Load as Boolean) as Boolean
if Load then
me.Text = customer.name
else
if me.Text = "" then
MsgBox "Please enter a Customer Name"
return True
end
customer.name = me.Text
end
End Function
It has seemed to hit the right balance between automation and precise control. And for simple scenarios where you’re just collecting data, it’s a breeze to set up. A fully automated setup might save a few minutes of programming time, but to me, the trade off is worth it.
the only difficulty with it being on each control is handling any interdependencies that MAY be required to be set up as part of the loading (ie/ if you have code in the change event that checks the value of another control)
not impossible and I’m sure you have a mechanism for dealing with this
ActiveRecord and ARGen has saved me sooooo much time. I have mocked up a dbase (in SQLite - even if longterm we are going to use another dbase engine), used ARGen to create the AR classes, then create a basic UI shell around them, and in minutes, I had a mocked up UI/with sample data running. I mocked up a daily notes taker app for a client with database file in 20 minutes. They wanted an app to keep track of notes taken on a given day, split up into separate files (or records in dbase) for each note for each day. The part I couldnt mock up in the 20 minutes was the Calendar control to select the day, and the meta-tags for each note (they want to add meta-tags to help organize them).
The (potential) client was very impressed. I just had to add the rest of the code to it and they would be happy.
I dont let my controls save/update the data in the database itself. I have it update the AR object and have the window save/update it (either based on timer or closing) or have a save button.
This is a great conversations! Fantastic to digest other ideas.
With EasyData, the EasyData Class doesn’t subclass controls, but works with them. Just as Ulrich went thru revisions, we did too. Our first version did subclass controls, but that was such a pain as Bob stated. So we flipped it where the EasyData Class contains a RecordSet and methods to automatically set and save controls. It’s similar to what Ralf said, but you can have one EasyData class instance for each ‘FileMaker like Table Occurrence’.
For a List, we’d use one EasyData Class. For a Detail Window, we could have many. In fact for a ‘FileMaker like Portal’, each row in has it’s own EasyData Class.
The end result is that if you have to add a field somewhere, you just add it to the table and toss a control on the window.
Yes, I felt myself caught in too many complexities like Bob described too. On the other hand, having automatic controls for the majority of cases is too comfortable to kick the idea too soon for my personal taste.
I handled this by installing events on the controls which can be used to perform a custom SQLSelect or data interpretation while they still can be used out of the box for the most frequent cases. But again, the code has become too messy when I tried to solve too many not-foreseen problems. Hal, I think we should definitely talk. Sounds like our concepts are very close to each other.
I do it with subclassing controls, but with a class interface. I find it easy to handle
there is a method to populate the control, and a method to save the datas to the database
then the detail window is a subclass of window, and there is a method that search all controls that have the interface
and send the populate event to them, with the recordset of the record as a parameter.
the controls have also a property of the table and field name, and I find this easy to handle, and still very evolutive.
