Sql For Remote Calls

May I float the idea of using SQL in place of RemoteProcedureCalls, SOAP, and WebServices?

You send SQL to a designated server and get back comma-delimited or XML rows. For security purposes, only designated tables or views are visible to the target outsiders.

SQL allows users to select only the rows and columns that they want. Many techies are familiar with it so the training curve will be lower than some newfangled XML DDL or SOAP.

However, perhaps some way should be provided to avoid CartesianJoins (which SQL makes far too easy to accidently do). Otherwise outsiders can hose up your server.

--top

An interesting idea, and perhaps appropriate for certain WebServices that wrap a collection of queryable data, though I'd be more inclined to simply expose the (well-secured) SQL DBMS over the network. However, most WebServices expose a limited set of relatively unrelated, relatively simple procedure calls. Given that WebService and RemoteProcedureCall client development environments generally do not require the developer to tangle with SOAP or IIOP directly, and simply translate the WSDL or IDL description of the service into client-side procedure and/or class declarations that generate and parse SOAP or IIOP messages on your behalf, there's no gain to using SQL.

For example, how is the following...

 response = provider.doRemoteInvocation("INSERT INTO ccTransaction(amount, payee, description, ccnum, ccname, expirydate, seccode) VALUES (12.99, 'KFC34983', 'Big Bucket of Chicken', '1234345657482342', 'Mr. D Voorhis', '08/07', 555)");

...superior to the following?

 response = provider.doCCTransaction(12.99, 'KFC34983', 'Big Bucket of Chicken', '1234345657482342', 'Mr. D Voorhis', '08/07', 555);

The latter requires conversion code, the former can be plugged in directly. The latter uses API deprecation, the former is flexible. The latter requires looking up documentation, the former doesn't. The latter requires data-type matching code and has international date/string/etc problems, the former is universal.

Good points. Let's look at them:

The latter requires conversion code, the former can be plugged in directly.
- True, but only if the former is accessing a SQL database. If it is, then you might as well connect directly to the SQL DBMS. However, services often aren't SQL based. They commonly interface to legacy systems or hardware devices, or directly drive procedural or OO code. Your SQL-based scheme would require you to implement a SQL interpreter to drive your server-side procedural or OO code. Ouch. On the other hand, a call-based system works happily whether the service is SQL-based or not. It's trivial to embed a SQL query to a DBMS in a server-side method. Parsing a SQL string that represents invocation of a server-side method? Not as trivial, and costs a bit of extra CPU time on the server side.
The latter uses API deprecation, the former is flexible.
- I'm not sure what you mean by "uses API deprecation", but in the latter, if the API changes you auto-generate the service interface proxies from the WSDL or IDL, attempt to recompile your code and/or run tests, note any failures and alter client-side code as needed.
- In the former, if the SQL schema changes, you locate the ad-hoc (i.e., there is no standard for this) documentation that describes the schema changes, manually alter the client-side SQL, recompile your code and/or run tests, note any failures and alter client-side code as needed. How, exactly, is this more flexible?
The latter requires looking up documentation, the former doesn't.
- Both require looking up documentation. The latter for the API docs, the former for the schema docs.
The latter requires data-type matching code and has international date/string/etc problems, the former is universal.
- I'm not sure what you mean by "data-type matching code," at least not in any sense that would distinguish a remote procedure call from a query in a meaningful way. As for international date/string/etc. problems, the former is only universal if it adheres precisely to a SQLxx standard. How many SQL DBMS products do so? As such, on this point the former approach is no better or worse than the latter approach, and you still have to convert your native formats to SQL strings. In remote calls, native formats are usually automatically converted to the wire protocol and back to native in the proxy code generated by the IDL or WSDL compiler. -- DV

This is not to deprecate the potential power of mapping system resources to relations in order to take advantage of the composability of the relational algebra. For example, if diverse resources were represented as relations you could trivially JOIN user account information (represented as a relation variable) with system login logs (relvar, again) with mail download data (a UNION of the IMAP and POP3 log relvars) and use the result to INSERT email messages into the SMTP server. This might be used to send a batch of "please log in and change your password" emails addressed to users who haven't logged into the system within the last month, but who have recently downloaded their email.

This is a relatively underexplored idea, though I believe the PICK operating system successfully used a variation on this. However, I'm not sure using SQL as an interface to WebServices or RemoteProcedureCalls is the best example of it. Even in a purely relational environment, I would still expect to invoke remote procedures via a procedure call syntax, in the same way I invoke local procedures.

-- DaveVoorhis

Perhaps merge with RelationalAlternativeToXml when cooked.