Hands On: Cross-DB Resource Guide for Devs

More and more often, enterprise developers of all stripes (Java, VB/.NET, C++, etc.) are asked to build cross-database access for applications and end users. This week, as part of IDN's reference library series for "developers as integrators," we present the latest in a growing list of cross-db methods for JDBC, OBDC, ADOdb, JDO, SQL and XML.

Tags: Developers, XML, ADO, SQL, Database, Cross-database Access, SQL Server,

As part of Integration Developer News reference libraries series for "developers as integrators," we present the latest techniques and resources from leading developers concerned with cross-database integration.

  • JDBC Access Methods -- Many Java developers have been working with JDBC for years, but it can be a challenge to know if you're getting the most out of your JDBC code solutions or just slowing your CPU performance. JavaWorld presents an analysis of three (3) characteristics required for a well-designed database access method. The article, written by Michael Juntao Yuan, a Ph.D. candidate at the University of Texas at Austin, and a frequent JavaWorld contributor, also describes how properly written JDBC access methods can greatly reduce the maintenance overhead and improve your Web application's flexibility.

  • ADO ActiveX Data Objects-- Microsoft SQL Server allows multiple SQL statements and/or Stored Procedures to be run from a single ADO call. Experienced developers often take advantage of this powerful feature in a controlled manner to do amazing things. AccessHelp.net, a professional services firm, provides some top ADO insights, including advice about making the right connection choices and avoiding the use of batched commands. From the trenches, ADO developer Joel Hanley also offers some first-person advice from his ADO weblog.

  • ADOdb -- ADOdb is an object-oriented library written in PHP that abstracts database operations for portability. It's modeled on Microsoft's ADO, but has a handful of modifications for cross-database access, including pivot tables, generating HTML for paging record sets with next and previous links, cached record sets, HTML menu generation, etc.). The latest version of ADSOdb (2.31, released 8/20), with tutorials on how to get started, is at . Free copies of the latest ADOdb scripts are also available at Hotscripts.com.

  • XML Queries in SQL Server -- For the thousands of you using SQL Server 2000, offers a technical overview tutorial on using the three different types of FOR XML queries supported, including RAW, AUTO and EXPLICIT. Each has performance pros and cons:

    1. The RAW type offers the best overall performance, especially if you're moving a lot of data. The disadvantage? Not all XML-based applications are able to use the format the RAW type returns.

    2. The AUTO type offers the next best overall performance, and many more XML-based applications are able to use the format returned, unless, of course, your application requires XML data in a predefined format.

    3. The EXPLICIT type is for sites whose application must accept data in a predefined format. EXPLICIT is generally the slowest-performing option.

  • Enterprise Data Access (C++) -- For developers who need to support multiple C++ and/or SQL target databases, Mathtools provides an easy-to-navigate annotated list of more than several dozen tools (free and fee-based) for these environments. . While Mathtools offers a good overview and click-through resources list, site users must register to access/download any company-specific resources.

  • Data Replication -- Some cross-database access is for storage and data replication, rather than simply user access. Given this requirement for high availability, the latest issue of Windows & .NET Magazine offers some helpful insights to developers (and their system administrator partners) on how to architect and implement a "clustering" solution using WinNT and/or Win2K Advanced Server.

  • XML Data Support -- A variety of XML tips, FAQs and forums is available at the XML area of Tek-Tips.com . Some of the latest topics address include: Using XML to Update a Remote Database; Printing an HTML Table with XML; Searching an XML DB; and Selecting Distinct Items from XML using ASP. (As with many forums, not all queries receive a prompt reply, but the XML area is one of the more active areas recently, as we've seen on Tek-Tips.)

  • Migrating OLE-Based Data Sharing -- William Vaughn, a seasoned ActiveX Data Objects (ADO) developer and author, presents a simple step-by-step tutorial for converting your existing ADO connection strings to ADO.NET. This straightforward article from Visual Studio Magazine starts the developer from a typical OLE DB connection string (used by ADO to connect to an SQL database), and provides clear instruction and code samples from there. The article is an extract from his latest books, ADO.NET Examples and Best Practices (cowritten with Peter D. Blackburn [APress, 2002]) with editions for VB and C# programmers.