XML Web Services for Database Access

The good thing about trying a new project is, much like writing an article or teaching a class, it forces you to research alot of things you thought you knew well.

One thing I want in my grid solution is a connection mechanism that can be redundant, and use some common standards. Native drivers can be buggy on some platforms, and ODBC is out - i don't want to have to force the installation of a new driver. The solution I want to use is a plain old SOAP webservice. Turns out, database vendors realized the power of this approach too and DB2, SQL Server, and Oracle (from 9i on) all support a SOAP interface. Can XML Web Services Offer a Standard Across Databases? is the best article I've found on the methods, all of which are addons. DB2 requires the DB2 XML Extender, SQL Server requires the web services toolkit, and Oracle requires the Oracle XDK. Oracle 10g and Yukon have integrated support to an extent, but it is still rarely used.

Unfortunately, while the transport protocol is the same for all of them, the methods are all different. And XQuery is just a huge pain. It's writers apparently have no respect for simplicity and it is much, much more trouble than it's worth, especially when every database supports ANSI SQL for the most part.

But the SOAP advantage is too big too ignore (namely, every platform, and every language, has built in ability to access it), so SOAP it will be for access. The XML elements will be much simpler than any of the big venodr solution sthough. In fact, I'll model it off most native drivers - host, username, password, and query. The result will be a dataset, with error codes if necessary. Nothing fancier is needed I don't think. This approach also allows me to substitute in other databases to provide an even more motley redundant cluster.

Update: Tom pointed out in the comments that "SQL Anywhere from the iAnywhere subsidiary of Sybase provides web services (both as provider and as consumer) as part of the base product, and that the services can be defined entirely within the database, and that a free developer edition download is available from ianywhere.com." Thanks Tom, I did not intend to treat Sybase like an unwanted stepchild.