Replacement For Odbc

Perhaps it's time to replace ODBC (OpenDataBaseConnectivity) and its relative, JDBC. Or, at least provide an alternative. ODBC is defined in terms of C-like functions. A text-based web service over HTTP seems to be the way to go as far as open platform. XML could be involved, however, the raw data should perhaps be sent via something like CSV or FlirtDataTextFormat because XML is not very compact. Text compression (but still text) is also a feature to consider.

You're talking about the format of data transmitted over the wire. ODBC/JDBC say nothing about that format. They define an API. For compatibility with the maximum number of client-side languages, ODBC defines C-like functions. An alternative to C-like functions would be a communications protocol, not an API. Such communications protocols are well described by CORBA GIOP/IIOP, SOAP, and so forth. When you implement SMEQL, you can make its ODBC/JDBC drivers communicate using CSV or FlirtDataTextFormat and your users won't even need to know it because they'll be interfacing with the ODBC/JDBC API.

We don't need a new communications protocol for it, we have HTTP for that. We need a "command protocol" to put on top of HTTP, for lack of a better term.
See SOAP, XML-RPC, REST, etc.

Defining it as a web service would also reduce or eliminate the need for client-side (desktop-installed) drivers. Driver installation and management can be time-consuming in a large shop.

In many shops, it's now common to provide WebService front-ends to DBMSs.

Do you mean like a web-based QueryByExample screen-and-report set? Sure, but that's more high-level than what I have in mind. Plus, it cannot be used very effectively as app programming language "calls". I'm thinking of something that submits textual SQL to a RDBMS, and gets back a result set (table).

No. I mean a WebService that provides one or more methods invocable using standard WebService mechanisms, where the methods issue some query against a DBMS and return a ResultSet to the client. I've seen some implementations where the WebService provides only one method -- ExecuteSQL(String str, String userID, String password) -- to allow clients to execute arbitrary queries against a DBMS.

Why did you use the term "front-ends"?

It's a front-end to a DBMS back-end.

As far as I know, such a thing is not standardized.

It obeys WebService standards. One of my colleagues set up such a mechanism to allow me to access his SQL server. He gave me the appropriate URL. I pointed to it with a Java-based WebService client-generation Wizard. It generated the skeleton code. Then I just needed to invoke connection.ExecuteSQL("select fish from blah", "me", "secret") to use it. Nice.

Here's a rough draft of what the markup could look like:

 <?xml...>
 <!-- qpm = query protocol markup -->
 <qpm:query name="myQuery" language="sql">
    SELECT * FROM farm WHERE goats='YES'
 </qpm:query>
 <qpm:execute 
    query="myQuery" 
    system="bigRDBMS"
    userName="bob" 
    password="layneslaw" 
    outname="result_1"
    outformat="csv"
    />
 <!-- use different DB and format with same query -->
 <qpm:execute 
    query="myQuery" 
    system="testRDBMS"
    userName="bob" 
    password="darn" 
    outname="result_2"
    outformat="flirt"
    />

Return side:

 <qpm:resultset name="result_1" format="csv">
 "descript","count","a_flag","b_flag","c_flag"
 "foo",123,0,1,0
 "bar",456,0,1,0
 "glop",123,0,1,0
 </qpm:resultset>
 <qpm:resultset name="result_2" format="flirt">
 ...
 @record
 $foo
 $123
 $0
 $1
 $0
 ...
 </qpm:resultset>

We might also optionally have something like:

 ...
 <qpm:credentials
    name="mycred"
    system="testRDBMS"
    userName="bob" 
    password="darn"
    />
 <qpm:execute 
    query="myQuery" 
    outname="result_1"
    credentials="mycred"
    />

These can be wrapped in app-language-specific API's, similar to your above "ExecuteSQL" method.

I prefer names over nesting in order to avoid repetition of markup code. I have no problem with nested being an alternative, though.

Or, you could use existing WebService standards and save yourself the (almost completely redundant) effort of implementing the above.

What standard are you referring to? WebService standards define how to make a service. They don't define the specifics of that service.

I meant you could use standards-based WebService generation tools, which create services that employ standard message formats like SOAP, to create the "ExecuteSQL" WebService and a client in literally a few minutes. I'm not clear what the above markup adds. I can appreciate wanting to access SQL databases over HTTP in order to get around firewalls and so forth, but I'm curious why you want to invent new messaging formats and protocols to do it given WebServices were created for precisely this sort of purpose. SQL DBMS access over WebServices would be straightforward -- create Web methods to replicate the ODBC and/or JDBC API.

Some will call it "runSql", others "executeQuery", others will split the credential submission from the query text execution in their API, some will be using non-OOP languages, etc. Further, off-the-shelf query tools/browsers couldn't readily adapt to different API's. There's more to the world than Java.

So? What makes your XML format more likely to be standardised than my ExecuteSQL() Web method? And what does anything I've written (except mentioning JDBC along with ODBC) have to do with Java or OOP?

Anyway, if generalising the scheme is a goal, a better idea is to create a system whereby any number of arbitrary ODBC/JDBC drivers can be installed on the server side and exposed as a single WebService. Then, a client-side ODBC/JDBC driver, designed to work with that WebService, is installed on clients as needed. Thus, the client-side still uses ODBC/JDBC drivers as usual, but all communication is over HTTP via WebService standards.