WebApplication.HandleSpecialURL and database

As stated in the documentation:

We can use handleSpecialURL to APIs or Web services. To do this, we generally need to send data, and thus to send queries to the database.

As the database is connected to the sessions and not the application, how? Especially for SQLite.

We also open a database connection in webApp.open that will only query processing for handleSpecialURL?
In this case, the sessions can send multiple read requests simultaneously to the database, but HandleSpecialURL send only one request at a time (one thread)? Right ?
there is no risk of corruption?

To implement a “session-based” API, I’ve implemented an APISession object. The API needs a way to log in and set a session cookie. Use the APISession much like you use a WE Session. Each APISession would have its own database connection, etc.

Thank you Brad!

it is in Studio WebEssential?
I just watched the “Sign in Sample” project. This is very interesting! :slight_smile:
But I do not need to open a session in fact.
For example, I’d like to offer an API that returns a price.

An external website sends a request to mywebapp/api/134733 ?price ,
“134733” is the ID of a product.

The webApp then makes a request to the database by asking the price of the item No. 134733, and the price is sent to the web site. it is simple and very fast, there is no need to provide a session with login/password.
The problem is that the database is connected to the session, not the application.

It’s not in Web Essentials. I’ve done one of these, but it didn’t seem general enough to put in there.

If you want to use a specific API, make a new application which it will open the database on application.open event. Then, you can have access to database from handleSpecialURL. I have tested it, the only issue that it creates queues (no simultaneously processing). This tool is not for api/rest server.

I do not understand. You create a session from handleSpecialUrl?

My example is e-commerce web site. The website sends requests to the Xojo webApp. For safety reasons, we can verify that queries are well from the IP address of website.

The example I gave (opening a connection via app.open) does not work?

Thank you Antonis. But for now, I need to use SQLite. I do not think a SQLite database can be opened by two applications simultaneously, right?

I have a class called APISession, subclass of Object. I use it to maintain a (lower case) “session” as a client talks to the web app through the API. First, the client needs to “sign in”, then a cookie is set that binds further requests to an APISession. It mimics what WE gives you with the Session class/object for interactive UI sessions. Make more sense?

ok. So, whenever you create an object out of a session, this object can open a connection with SQLite? Each of these objects will have its own SQLite connection/thread, right?

Yes, each APISession has a connection to a SQLite database. Continuing… if the APISession doesn’t hear from its client for awhile, it times out, closes its connection to the database, and goes away.

ok, I see. And there is no risk of data corruption with SQLITE?

See the Multiuser property of REALSqlDatabase/SQLiteDatabase.

Yes, thank you Tim, I already use the WAL mode. But I think it can be used only within a single application.

I have a doubt now. SQLite is not a server, it does not have a port, and it is not designed to work with multiple applications. Multiple threads of a single application, then yes. Normally SQLITE is designed to be embedded in an application.

But you’re only talking about a single web app, right? Both the normal web pages and HandleSpecialURL are the same app.

Actually, if you’re not on Windows, even multiple applications hitting the same SQLite database simultaneously (WAL enabled, of course) should work fine.


Engine and server are really separate issues. You’d be pretty amazed what the little SQLite engine can do if you design your database schema appropriately.

ok, thank you Brad and Tim.