Accessing remote databases

Pyrrho now has quite a range of open-source embedded versions: for Windows, Windows Phone 7.1 Linux/Mono, Android, and Silverlight 5. There is also an embedded version of the standard edition. The whole purpose of an embedded DBMS is that the database is basically private to the application.

However, the application may also wish to contact other database servers. Pyrrho provided extended SQL syntax so that any REST service can be contacted from within SQL. An HTTP GET can be used anywhere a value is expected, and HTTP ADD, HTTP UPDATE and HTTP DELETE can be used anywhere a statement is expected. There is of course a url parameter, which is an SQL value, so expressions are allowed, and for ADD and UPDATE there is also a value to be used as data. Mime types are also supported. The full syntax for this feature is

Statement = .. | HTTP HttpRest .
HttpRest = (ADD|UPDATE) url_Value data_Value [AS mime_string]
| DELETE url_Value .
Value = .. | HTTP GET url_Value [ AS mime_string ] .

If HTTP GET is used in a context where the type is constrained (e.g. within an INSERT), Pyrrho will provide type conversion of the returned value to the expected data type. Otherwise the value is treated as a string.

This feature is available in all editions of Pyrrho, although currently Phone and Silverlight are not allowed to use the TCP API.

If the remote database is provided by a Pyrrho server, then access to it can be managed entirely within SQL, by arranging for the connection to access all of the databases concerned. For security, access from one Pyrrho server to remote servers must be set up as part of server configuration. But with embedded Pyrrho, a connection string such as “mylocaldb,corpdb@data.corp.com” could be used to contact a database corpdb on a company server in addition to the embedded database mylocaldb. Several local and remote databases can be accessed.

When the connection string gives a list of Pyrrho databases in this way, references to tables and other database objects are attempted on each database in the connection in order. Usually, any changes are only being made to the first named database. There are several semantic restrictions: schema changes require a single database connection, and local and remote databases cannot be mixed in the one query (but subqueries are okay).

As usual, let me know of any problems or unexpected behaviour.