Using Xojo Web to create complete web apps and solutions means not having to learn a bunch of interpreted languages and dozens of ever-changing frameworks. I’m looking at you: HTML, CSS (is that even a language?), JavaScript, PHP, et al. Of course, Xojo Web not only makes it possible to create your own web apps, but it also acts as the perfect middleware that your desktop and iOS apps can communicate with. Learn about APIs and web services with Xojo in the tutorial blog post.

In this two part tutorial you will see how easy it is to create a basic web service using Xojo Web. In the second part, we will create a Desktop client to talk with the web service (you may want to add iOS to the list).

Before we start, let me point out that this tutorial leaves out some details related to error checking, validation, data sanitizing and other specifics related to inputs and outputs in order to focus on the central topic. In addition, if you want to follow and reproduce the steps of this tutorial, then you need to download the Chinook Database, named “test.sqlite” in this tutorial.

Services in practice

Let’s start creating a web service! Open the Xojo IDE, create a new Web project, and name it WebService. The first thing you’ll notice is that Xojo adds a web page to the project by default – even when our web service does not display a UI. The good part is that you can mix both worlds, adding the webservice part to your regular Web app!.

In fact this tutorial will put all the logic in the App object. Start by adding a new property in charge of the reference to our SQLite database (of course, it will work also with PostgreSQL or MySQL engines). Select the App object from the Navigator (the left panel in the Xojo IDE), choosing the Insert > Property option from the contextual menu. With the new property selected, go to the Inspector to set the name, type and scope using these values:

Name: Database
Type: SQLiteDatabase
Scope: Private

Next, we will add the Chinook Database to the Resources folder of our Web App. For that, select the deployment target under Build Settings (it can be macOS, Windows, Linux or Xojo Cloud, for example), and add a Copy Files build step to it. In the resulting Editor, add the Chinook database SQLite file and make sure you select the “Resources Folder” entry in the Destination popup menu, and the “Both” entry in the Applies To popup menu.

Now is time to create a new SQLiteDatabase instance and assign it to this property, so it will point to the SQLite database file when the app is running. For that, make sure the App object is selected and add the Opening Event Handler to it using the Insert > Event option. Write the following code in the resulting Code Editor:

#Pragma Unused args

Var f As FolderItem = SpecialFolder.Resource("Chinook_Sqlite.sqlite")

If f.Exists Then
  
  Try
    database = New SQLiteDatabase
    database.DatabaseFile = f
    
    Call database.Connect
  Catch e As DatabaseException
    MessageBox "Error connecting to the database"
  End Try
  
End If

As you can see, it is pretty much the same code we already use when creating SQLiteDatabase instances in our Desktop apps, linking to our SQLite database file and stablishing the connection so we can operate with the database engine from our app.

All the Magic of HandleURL

Xojo Web projects offer a simple way to handle the request received. It is using the HandleURL event. This is the one that fires every time a client app (it may be a Web Browser, desktop or mobile app) connects to the URL associated with the IP address and port combination that is listening for incoming requests.

For example, a valid URL that can be trapped and processed by HandleURL is:

http://www.nice-web-domain.com/getCustomers

Where getCustomers is in this case one of our API methods.

So, with the App object selectd, choose Insert > Event in order to add the HandleURL event.

As we will see, once the event has been added to a web app, it will receive the Request parameter (a WebRequest data type), waiting from us to send back a Boolean value as response: True to process the request or False (the default value) to ignore the response.

Introducing Request, where the information lives!

In fact, we will find in the Request object everything we need to process and (if it is the case) respond to the request from our Web service. For example, through we can get the interesting component from the Path property. If we consider this URL:

http://www.nice-web-domain.com/getCustomers?spain

The Request.Path property will return the getCustomers string; so our web service can process it acordingly from this point on.

Receiving and Sending JSON data

In order to keep this tutorial brief, our web API only has two methods in it: GetAll and AddAlbum. Using the first one, the client app will get the album name in the database wrapped in JSON format. With the second method, our client app will ask the web service to add a new record (a new album) to the right table on our example database.

How can we process the request associated data inside the HandleURL event? Here is where we will find very useful another of the Request object properties. The Body property includes the sent data as part of the request that are not already present in the headers. Generally speaking, it includes additional data via the PUT and POST verbs.

Now we can put the following code into our HandleURL Event Handler:

Select Case Request.Path // What is the method received as part of the request? (URL)
Case "GetAll"
  Var output As JSONItem = GetAllAlbums // Assign the processed data to the output variable, if the received method is 'GetAllAlbums'
  
  Response.Header("charset") = "utf-8"
  Response.MIMEType = "application/json"
  Response.Status = 200
  response.write( output.ToString ) // And send it back to the client that made the request, converting the JSON to a String in first place
  
Case "AddAlbum"
  Var data As String = Request.Body.DefineEncoding(encodings.UTF8) // We have to apply the right encoding to the received data
  Var Input As JSONItem = New JSONItem( data ) // Creating a new JSON object from it
  addNewAlbum( Input.Value("newAlbum") ) // In this case, the request is to add a new Album to the database; passing thus the received data as part of the input
End Select

Return True

The main point here is that we assign the received data to the data variable (if any), and define a known encoding to them so we won’t get into trouble while processing them afterwards. Of course, our example always expects to receive the additional data in JSON format, so this is why we create a new JSON instance from this data.

We use the Select…Case command to decide what method has to execute the web service, and that is based on the component stored in the Path property as you recall. So, if the request uses the GetAll method in the URL, we will call the real method GetAllAlbums in our Xojo code. After processing the information it will return a new JSONItem as the response we will send to the client.

How can we send the response to the request? Really simple: calling the Write method on the Response object, passing as parameter the text we want to send back. In our example, this is the JSONItem referenced by the Output variable. We also use the Response object to set the character encoding to UTF-8 and the MIME type to “Application/json”, in addition of setting the status value to 200 (that is, the request has been successfully handled).

If we receive a request with the AddAlbum method of our API, then we call the real addNewAlbum method in our Xojo code, passing as parameter the JSONItem object in charge of store the received data from the request (this is the one referenced by the input variable). In fact, the record structure is stored inside the newAlbum root node of the JSONItem.

When it comes to the Database

While HandleURL is in charge of processing the received request, we will use a couple of methods in our example app that will act as a link between the API and the database in the backend, both for retrieving and storing the requested information. (In a real world app it is very advisable to introduce checks and data sanitization before dealing with the database!)

Choose the App object again and use the Insert > Method option in order to add the GetAllAlbums method, using the following method signature for that:

- Method Name: getAllAlbums
- Return Type: JSONItem
- Scope: Private

This is the code in charge of generating the JSONitem that we will write as part of the request response, including the node for every expected database record from the Album table in our example database:

Var rc As RowSet = database.SelectSQL("Select * from album order by title asc") // Get the Recordset as result of the SQL selection: all the records
Var item As New JSONItem

If rc.RowCount > 0 Then // We have records on the RecordSet
  
  While Not rc.AfterLastRow // so lets iterate them!
    Var d As New Dictionary // creating a new dictionary for each record, and that we will convert in a node
    d.Value("artistid") = rc.Column("artistid").StringValue // assingning the record ID to the name 'ArtistId' of the JSONItem
    d.Value("title") = rc.Column("title").StringValue // and the Title value to the 'Title' field of the JSONItem
    item.Value(rc.Column("albumid").StringValue) = d // You know you can assign a Dictionary as the value for a JSONItem node. Very useful!
    
    rc.MoveToNextRow
  Wend
  
  rc.Close
  
End If

var output As New JSONItem

output.Value("AllAlbums") = item // Then let's hang all these records form a main Node

Return output // And return it to the caller

Next, create a new method named addNewAlbum. This is the one our web service will use to add a new record to the database, using for that the received data as part of the request. Use the following signature for the method definition:

- - Method Name: addNewAlbum
  - Paramters: item as JSONItem
  - Scope: Private

And put the following code in the associated Code Editor:

Var title As String = item.Value("Title") // get the data associated to the "Title" field
Var artistid As String = item.Value("ArtistId") // and the 'ArtistID'

database.ExecuteSQL("insert into album(title,artistid) values(?, ?)", title, artistid ) // and insert that data as a new record into the database table

As you can see, the code is very simple: it gets the values for the received keys in the JSONitem nodes and uses them as part of the SQL sentence in order to add a new record to the database.

A Web service… ready to serve!

As you have seen, the code and app structure are really minimal! Of course, this is just a simple example but it gives you a good idea of the kind of possibilities web services offer and how fast you can put it together using Xojo and OOP concepts you already know! Of course, you can run your web app (and services) right from the IDE (just make sure to select 8081 as the Debug Port)… what is probably the recommended way to follow this simple example. For other more complex web apps, remember that you can use the one click solution Xojo Cloud and of course any compatible Linux, Windows or Mac server.

Javier Rodriguez has been the Xojo Spanish Evangelist since 2008, he’s also a Developer, Consultant and Trainer who has be using Xojo since 1998. He manages AprendeXojo.com and is the developer behind the GuancheMOS plug-in for Xojo Developers, Markdown Parser for Xojo, HTMLColorizer for Xojo and the Snippery app, among others

*Read this post in Spanish

Web Services: Xojo Web, at your service