Lets make Xojo more business centric – Part 2

Couldn’t you do run data into an in-memory database table with Xojo already?
Yes in a way, but there are some drawbacks as well. We jump to the middle process on how to subselect data into another SQLite table which would be the equivalent of my proposed SQLiteMemoryTable. Once you have the data in an in-memory database, you could sub-select it back into other in-memory databases by doing a:
create table MyNewTable1 as select * from MyNewTable where something=somethingelse;

But the initial creation of that in-memory table is problematic in itself. If we were grabbing data from an Oracle database (as an example) the data flow would currently be:
Oracle DB => RecordSet => Create In-Memory Table => Loop through Recordset and push data into In-Memory table

This turn-about and run into an in-memory table is painfully long with even moderate sized data sets. I’ve done it. I’m hoping for some really fast performance from the process if natively written in Xojo (continuing the example from above):
Oracle DB => SQLiteMemoryTable

In addition to that, I’m also hoping for the debugger enhancement of a “BROWSE” button that would pop up a window displaying the rows and columns of that SQLiteMemoryTable. Being able to scroll up and down (and around) that data is so very helpful in overcoming development issues.

Having Xojo know a bit more about those data column types is a nice benefit as well. Xojo should know that you can’t add a currency field + a string field from a SQLiteMemoryTable and throw an error, not an unhandled exception. I understand it won’t catch it at compile time, but even handling it gracefully at runtime would be helpful.

This lays the groundwork on getting the Database Binding to work. We call them “ControlSource” in Foxpro. Imagine that the record pointer is sitting on a particular row of your SQLiteMemoryTable, and that data is displayed on your form. In this example our theoretical user clicks on the “Next” button. The code in your form should be as simple as:
IF NOT MySQLiteMemoryTableExample.EOF THEN
SKIP 1 IN MySQLiteMemoryTableExample
SELF.Refresh
ELSE
Messagebox("You're already on the last record.")
END IF

Automagically, all of the information is displayed. If the user clicks on the “Prev” button, the operation is a “SKIP -1 …” Handle exceptions as needed, of course.

How would this work with an ORM such as Bob Keeney’s ActiveRecord product? No problem. After the “SKIP”, you would turn the SQLiteMemoryTable into an ActiveRecord. If the user wants to save the data, you [A] save it in the normal way back to the source table [B] destroy the SQLiteMemoryTable and [C] call the method that retrieves and creates the SQLiteMemoryTable again with the updated data. Hopefully that is so fast that the user doesn’t even notice a delay. Perhaps even call that in a thread…

Perhaps if this was smart this thread friendly creation of the SQLiteMemoryTable would pagenate data. We have had that in Foxpro. The SQLiteMemoryTable structure is created first and Foxpro pulls down pages of data as defined by the Foxpro environment. Typically is is 100 rows at a time but that can be overridden. It was called “PROGRESSIVE FETCHING”. All of the records eventually ended up in our Cursor (SQLiteMemoryTable) but control was returned to Foxpro and the data was able to be displayed in our grids. There was a call that could be made that determined if all records were returned or not. Basically if the SQL SELECT was done retrieving data.

Selection_144 Take a look at this screen shot. Do you think Foxpro was serious about data handling, including Remote Data handling? Boy howdy. “Remote Data” basically means anything other than Foxpro tables which it handled natively: PostgreSQL, MySQL, M$ SQL Server, Firebird, and others.

I’ll wrap this up for this Part 2 of Making Xojo More Business Centric. I’ll continue the next part discussing how I would propose to change the object model representation in the IDE.

Leave a Reply