URLConnection and Active Record

Creating a series of classes that represent database objects and facilitate interaction with the database makes a lot of sense to me but how do you do this when the database is a RESTful API server and not a persistent database connection such as MySQL or SQLite? I can still create classes but I can’t figure out how to embed the URLConnection. How do you create common CRUD methods and then link them to the URLConnection?

This is how I do it:

  1. build a Dictionary of the the SQL command components ie SQLCommand (‘SELECT * FROM XXXX’, ‘INSERT INTO XXX’, UPDATE XXXX SET’ or ‘DELETE FROM XXXX’), the optional WHERECommand (‘id = 100’), the optional ORDERBYCommand (‘FirstName’), the optional GROUPBYCommand (‘LastName’), the optional LIMITNumber (100), three optional and matching arrays for either inserting or Prepared Statements (FieldNames() as String, FieldTypes() as String and FieldValues() as Variant)
  2. Turn the Dictionary into a JSON
  3. Compress and encrypt the JSON as Base64
  4. Place Base64 into URLConnection and SendSync to Aloe Express (or other)
  5. In Aloe Express, reverse the Base64, decryption and compression into SQL Prepared statement
  6. Run the SQL Prepared statement (SQLSelect or SQLExecute) on CubeSQL, MySQL, PostgreSQL or MSSQL, etc
  7. build a Dictionary of the results (DBError boolean, DBErrorMessage, DBErrorCode and optional RecordSet)
  8. Turn the Dictionary into a JSON
  9. Compress and encrypt the JSON as Base64
  10. Return the Base64 as the URLConnection response
  11. Reverse the Base64, decryption and compression into optional RecordSet
  12. Return the RecordSet (or DBError boolean for SQLExecute) to user

Thanks for the response… I do appreciate it and since it was the only one, I accept it humbly.

However, I was thinking about a more generic situation in which we are not in control of the server… such as a public API with a specification already defined using OpenAPI. A proper API doesn’t pass SQL statements. Instead it uses path and query parameters along with HTTP methods such as ‘GET’, ‘POST’, ‘PUT’ and ‘DELETE’.

I’ve spent some time since starting this thread attempting to use objects when performing basic CRUD functions using URLConnection with a public API and I think I know why so few people responded. Because of the asynchronous nature of URLConnection, you simply can not use an ActiveRecord strategy for development.

I just wanted to make sure I wasn’t missing something.

Hey @Kristin Green, I do use URLConnection along with ORM Classes to interface with public api’s. But I’ve never used or even looked at Active Record from @Bob Keeney. I’m sure their classes etc. are awesome. Perhaps tagging Bob here will get a response.

Perhaps I’m using the incorrect terminology? Active Record vs. ORM? I’ve studied the code produced by ARGen and I learn something new every time I look at it… but I fail to see how it could be used with a RESTful API server.

My goal is to leverage Classes to facilitate CRUD operations via URLConnection in the same way that you might do it with a local database. The big difference is that an SQLSelect() is synchronous while URLConnection is Asynchronous.

I’ve created classes that describe my data objects (‘Teams’, ‘Players’, etc…) and their associated properties (‘TeamID’, ‘TeamName’, etc…). I implemented a constructor to create a ‘Team’ from a JSONItem when receiving a record from the server… and a Team.ToJSON method to spit it back out again for CREATE and UPDATE operations.

Examples of database applications that use databases with persistent connections often include object classes with methods for reading, saving, creating and deleting data but how do you do that with URLConnection?

I’d love to learn more about how you use ORM Classes with URLConnection. Do you use a single URLConnection per window for all methods or do you create a URLConnection for each method so that you can perform the correct actions for each response?

to use ActiveRecord you would have to rewrite the guts since it basically assumes a connection to a database (postgresql, sybase, ms sql, etc)

I truly wish Xojo had not deprecated the interfaces that existed for Databases as then you COULD write code that worked regardless of whether you had an URLConnection or database under the hood as it would still BE a Database with database records etc etc etc

[quote=475898:@Norman Palardy]

I truly wish Xojo had not deprecated the interfaces that existed for Databases as then you COULD write code that worked regardless of whether you had an URLConnection or database under the hood as it would still BE a Database with database records etc etc etc[/quote]

Interfaces are contracts. If you brake the contract, you turn obsolete all the work based on it.

A few years ago I recommended this approach :
https://forum.xojo.com/31764-any-tutorial-for-database-interface/p1#p267599

Now I notice that I have to rewrite huge amounts of code if I want to assure our principal apps to run on future macOS versions. No way „you can use deprecated code for very long time“.

With these horrible API changes Xojo has become very shaky ground.

You could just as well be using threads, no?

From what I can tell Active Record is an implementation of ORM and it is probably brilliant. The only reason I haven’t gone there is that like you I’m dealing with RESTful API’s as both a consumer & supplier and that means my classes need to be agnostic to both the data source and database engine.

If you create a database interface and then implement a class on it which makes use of a specific REST Api out there in the cloud, then you can program against the interface, instead of directly accessing the REST Api or a database connection.

Of course the REST Api must provide endpoints as an equivalent to the usual CRUD operations. If this is the case, then you can implement a class for the REST Api, based on the database interface you defined.

The general concept and example of database interface has been shown here (xojo API 1):
https://forum.xojo.com/31764-any-tutorial-for-database-interface/p1#p267599

That example demonstrates on how to implement classes for different dbms, like SQLite, CubeSQL, MS SQLServer, Postgres.

But in the same way one could add a new class which handles data from a REST Api, using a URLConnection.

AFAIK, ActiveRecord is not helping you with this.

So one would have to create ones own interface and class based on that interface. Above, David Cox has described the steps to follow, when you write the methods of such a class.

Certainly
I had asked that all the database plugins and their classes be implemented from thos interfaces ( since all of them already implemented all the methods they just didnt state that they were implementing the interfaces)
At least then
a) we could have written our own code that was treated as a database and all associated classes in pure Xojo code
b) Xojo’s willingness to deprecate those interfaces might have dropped a bit since they depend on them as well

And they could have had all new interafces for the new database classes in API 2.0 to implement and off they go with a whole new set

coulda shoulda woulda :frowning:

I wouldn’t use ActiveRecord with REST API. The two are more less at odds with one another.

I’ve worked on integrating Xojo solutions with a number of web APIs. I consider it one of my strong points. I usually create a module [ServiceName], a base class to authenticate and handle errors, and then subclass the base for each endpoint I need. It’s straight forward, and very similar to the ActiveRecord structure generated by ARGen.

I can then write code similar to this, using an array property on MyCloud to keep the request in scope.

// Create person
dim oGuy as new MyCloud.Person
oGuy.Name = "Guy Smith"

// Handle response and retain reference
AddHandler oGuy.UpdateComplete, WeakAddressOf UpdateCompleteHandler
MyCloud.AppendRequest(oGuy)

// Start request
oGuy.Update

Some versions of Xojo have bugs with re-using connections, so I would avoid reusing the object if possible. For the average API interaction you shouldn’t have an issue using a new URLConnection for each request (unless the API requires a persistent connection). In the design I use, each object has it’s own URLConnection array for queuing requests.

I would recommend avoiding shortcuts, they usually come back to bite you. Use the event driven nature of URLConnection to capture responses, parse them, and raise your own class events. Trying to do web API interactions synchronously might tempt you to use unstable methods like DoEvents.

This much, I was able to figure out on my own but it seems that I’m going to have to brush up on my OOP as I’m not entirely clear on how you would handle reads/writes within a class when URLConnection must be instantiated within a Window. Is Base a sub-class of URLConnection?

Sorry but this is a bit beyond my skill level. Any tutorials or even doc pages you could point me to would be appreciated.

Use a repository pattern, Active Record does not really stack up very well beyond desktop apps.

This is precisely the question that I’m asking. HOW do you do this?

Consider a REST server with a public API that I have no control over. It defines /teams and /players as two resources available on the endpoint provided (ie: https://myapi.example.com/).

To get a list of players, you would perform this HTTP call:

GET https://myapi.example.com/players

The response would be an array of JSON objects with PlayerID, PlayerName, TeamID, etc…

To get Player #1, you would perform this HTTP call:

GET https://myapi.example.com/players/1

The response would be a single JSON object with PlayerID, PlayerName, TeamID, etc…

Now, in my Xojo Desktop project, I have defined a module called, ‘Resources’… within which, I first defined a ‘BaseResource’ class (I’ll elevate base code to this later for reuse) and then sub-classed it to create two classes that represent the two resources: ‘Team’ and ‘Player’. I created a constructor on each class that populates it’s properties from a JSONItem.

Public Sub Constructor(id As Integer) Me.PlayerID = data.Lookup("PlayerID").IntegerValue Me.PlayerName = data.Lookup("PlayerName").StringValue Me.TeamID = data.Lookup("TeamID").IntegerValue End Sub

I created a shared method in Player called, ‘View’ that takes an integer and uses URLConnection to request the player’s info and return the data in the form of a Player object.

[code]Public Shared Function View(id As Integer) as Player
Var connection As New URLConnection
connection.Send(“GET”, “https://myapi.example/com/players/” + id.ToString)

// Handle ContentReceived to obtain p As Player
Return p
End Function[/code]

At this point, I’m not clear on how/where I would handle the ContentReceived event when the response comes back from the server. I’ve never used AddHandler before but I’m digging through the docs now…

Us a repository pattern - simple objects: players and teams… repositories to handle communication with the server, serialization and deserialization of the simple objects…

I’m giving Tim a gold star for this one. (Do we do that here?) His answer, as vague as it was, pointed me in the right direction and I finally figured it out. I say, ‘vague’ because it was a couple of levels above my current Xojo Wizarding Level and it made a few assumptions that I didn’t get… at first. A big thanks to Tim as I would never have figured this out otherwise.

In the interest of sharing, here is how I managed to construct an object-oriented system for interfacing with a REST server. If it saves you some time, let me know.

Remember, the goal is to have a system of classes and subclasses that not only define your data objects (aka database tables) but to also handle all the communication with the server for all CRUD operations. The class structure looks like this:

Public Class Base Public Class Team As Base Public Class Player As Base

First, I created a ‘Base’ class within a module. I subclass my data objects from this. It handles all communication to and from the REST server by defining the URLConnection as a property and instantiating it in code.

Public Property Connection as URLConnection

Next, I created a method, within Base, called, ‘Read’ that will be called from its subclasses (and the instance of those subclasses) that instantiates the URLConnection and then uses it to send a request to the server. Before it sends it’s request, it adds a handler to the instance that will be triggered when the content is received. This was the ‘ah ha’ moment for me. I had never done this before so this was a pretty big deal. This is how you use URLConnection without having to drag it into your window!

Public Sub Read(url As String, primaryKey As Integer) Connection = New URLConnection AddHandler Connection.ContentReceived, WeakAddressOf HandleRead Connection.Send("GET", url + "/" + primaryKey.ToString) End Sub

I then created the method that handles the response from the server. It parses the content into a JSONItem and then politely removes itself from the Connection instance. Finally, it sends the JSONItem to an event called, ‘Read Complete’.

Public Sub HandleRead(sender As URLConnection, URL As String, HTTPStatus As Integer, content As String) Var data As New JSONItem(content) RemoveHandler Connection.ContentReceived, WeakAddressOf HandleRead RaiseEvent ReadComplete(data) End Sub

The key to this was to first define an event that could then be ‘raised’. Notice how it accepts my JSONItem and ‘passes’ it along to the subclass when it handles the event. This was the key to handing the response back down to the subclass… and ultimately, to the instance of it in my window.

Event ReadComplete(data As JSONItem)

So, now let’s say that I have a subclass of, ‘Base’, called, ‘Team’. It has a single property called, ‘TeamName’ As String. I can now create a method called, ‘Read’ that extends the Read method in Base. It simply passes up the url and primary key needed. (If your REST server uses a single endpoint for all resources, you could just pass the name of the resource here… if only we could get the name of the class… hmmmm…)

Public Sub Read() Super.Read("https://myapi.example.com/Team", 123) End Sub

At this point, we’re finally ready to implement the class in our window. I made a window called, ‘TeamWindow’ and it’s the edit window that appears when the user double-clicks on the name of a team in the list of teams. I added a TextField called, ‘TeamName’ and the usual buttons for deleting, saving and cancelling. I also added a ProgressWheel, called, ‘Wheel’… just for fun.

I added one property to my window that will contain all the data and functionality associated with the object… I called it, ‘This’.

Public Property This as Team

Then I added a method that displays the ProgressWheel, adds a handler when the response comes back, and calls the Team.Read() method that sends the url and primary key up to the Base.Read() method that actually sends the request to the REST server.

Public Sub ReadRecord() Wheel.Visible = True AddHandler This.ReadComplete, WeakAddressOf HandleRead This.Read End Sub

When the response comes back, it is handled by Base.HandleRead() which then sends the data down to Team via the ReadComplete event. Team catches the data and populates it’s properties with the fresh data.

Sub ReadComplete(data As JSONItem) Handles ReadComplete Me.TeamName = data.Value("TeamName") ReadComplete End Sub

But wait, there’s more! Finally, our TeamWindow.HandleRead() method also gets the call because Team.ReadComplete calls itself at the end which allows the window to also handle it.

Public Sub HandleRead(sender As City) RemoveHandler This.ReadComplete, WeakAddressOf HandleRead TeamName.Value = This.TeamName Changed = False Wheel.Visible = False DeleteButton.Enabled = True End Sub

After politely removing itself (as all good handlers should), it makes sure that all fields have the latest Team-related data, tells the window that nothing has changed, turns off the ProgressWheel and enables the delete button.

Rinse and repeat for Create, Update, and Delete functions.

The result is that I now have very clean windows without a lot of messy code. Just the basics. All my API communications are handled in my data classes and can be easily ported to other projects.

I even created self-containing PopupMenus and ListBoxes that populate themselves by automatically querying the server when needed! :slight_smile:

[quote=477739:@Kristin Green]I’m giving Tim a gold star for this one. (Do we do that here?) His answer, as vague as it was, pointed me in the right direction and I finally figured it out. I say, ‘vague’ because it was a couple of levels above my current Xojo Wizarding Level and it made a few assumptions that I didn’t get… at first. A big thanks to Tim as I would never have figured this out otherwise.

In the interest of sharing, here is how I managed to construct an object-oriented system for interfacing with a REST server. If it saves you some time, let me know.

Remember, the goal is to have a system of classes and subclasses that not only define your data objects (aka database tables) but to also handle all the communication with the server for all CRUD operations. The class structure looks like this:

Public Class Base Public Class Team As Base Public Class Player As Base

First, I created a ‘Base’ class within a module. I subclass my data objects from this. It handles all communication to and from the REST server by defining the URLConnection as a property and instantiating it in code.

Public Property Connection as URLConnection

Next, I created a method, within Base, called, ‘Read’ that will be called from its subclasses (and the instance of those subclasses) that instantiates the URLConnection and then uses it to send a request to the server. Before it sends it’s request, it adds a handler to the instance that will be triggered when the content is received. This was the ‘ah ha’ moment for me. I had never done this before so this was a pretty big deal. This is how you use URLConnection without having to drag it into your window!

Public Sub Read(url As String, primaryKey As Integer) Connection = New URLConnection AddHandler Connection.ContentReceived, WeakAddressOf HandleRead Connection.Send("GET", url + "/" + primaryKey.ToString) End Sub

I then created the method that handles the response from the server. It parses the content into a JSONItem and then politely removes itself from the Connection instance. Finally, it sends the JSONItem to an event called, ‘Read Complete’.

Public Sub HandleRead(sender As URLConnection, URL As String, HTTPStatus As Integer, content As String) Var data As New JSONItem(content) RemoveHandler Connection.ContentReceived, WeakAddressOf HandleRead RaiseEvent ReadComplete(data) End Sub

The key to this was to first define an event that could then be ‘raised’. Notice how it accepts my JSONItem and ‘passes’ it along to the subclass when it handles the event. This was the key to handing the response back down to the subclass… and ultimately, to the instance of it in my window.

Event ReadComplete(data As JSONItem)

So, now let’s say that I have a subclass of, ‘Base’, called, ‘Team’. It has a single property called, ‘TeamName’ As String. I can now create a method called, ‘Read’ that extends the Read method in Base. It simply passes up the url and primary key needed. (If your REST server uses a single endpoint for all resources, you could just pass the name of the resource here… if only we could get the name of the class… hmmmm…)

Public Sub Read() Super.Read("https://myapi.example.com/Team", 123) End Sub

At this point, we’re finally ready to implement the class in our window. I made a window called, ‘TeamWindow’ and it’s the edit window that appears when the user double-clicks on the name of a team in the list of teams. I added a TextField called, ‘TeamName’ and the usual buttons for deleting, saving and cancelling. I also added a ProgressWheel, called, ‘Wheel’… just for fun.

I added one property to my window that will contain all the data and functionality associated with the object… I called it, ‘This’.

Public Property This as Team

Then I added a method that displays the ProgressWheel, adds a handler when the response comes back, and calls the Team.Read() method that sends the url and primary key up to the Base.Read() method that actually sends the request to the REST server.

Public Sub ReadRecord() Wheel.Visible = True AddHandler This.ReadComplete, WeakAddressOf HandleRead This.Read End Sub

When the response comes back, it is handled by Base.HandleRead() which then sends the data down to Team via the ReadComplete event. Team catches the data and populates it’s properties with the fresh data.

Sub ReadComplete(data As JSONItem) Handles ReadComplete Me.TeamName = data.Value("TeamName") ReadComplete End Sub

But wait, there’s more! Finally, our TeamWindow.HandleRead() method also gets the call because Team.ReadComplete calls itself at the end which allows the window to also handle it.

Public Sub HandleRead(sender As City) RemoveHandler This.ReadComplete, WeakAddressOf HandleRead TeamName.Value = This.TeamName Changed = False Wheel.Visible = False DeleteButton.Enabled = True End Sub

After politely removing itself (as all good handlers should), it makes sure that all fields have the latest Team-related data, tells the window that nothing has changed, turns off the ProgressWheel and enables the delete button.

Rinse and repeat for Create, Update, and Delete functions.

The result is that I now have very clean windows without a lot of messy code. Just the basics. All my API communications are handled in my data classes and can be easily ported to other projects.

I even created self-containing PopupMenus and ListBoxes that populate themselves by automatically querying the server when needed! :-)[/quote]
That sounds quite cool, do you have a running project for that ? like a sample project ?

Thanks.

I don’t have one yet. I was in the middle of a big project when I figured this out and I haven’t had time to distill it any further than this.