Here are a few examples of identified interoperability issues that highlight the scope of this problem.
ODBC/JDBC SQL Escapes
ADO.NET does not define a query language; therefore data providers are not required to mask the differences in supported SQL statements between databases. Progress DataDirect has added support for ODBC/JDBC SQL escapes for scalar functions, outer joins, and stored procedures in our providers. This means that developers can pass standardized SQL statements to all of our data providers.
For example, if you wanted to use the Oracle native syntax for the function Substring, your SQL statement might look like this:
SELECT substr('ABCDEFG',3,4) FROM mytable
The equivalent statement for Sybase would look like this:
SELECT substring('ABCDEFG',3,4) FROM mytable
Using ODBC/JDBC escapes, the SQL statement would be the same regardless of which backend database you access:
SELECT {substring('ABCDEFG',3,4) FROM mytable}
If you wanted to execute an outer join using native Oracle syntax, your SQL statement might look like this:
SELECT ename, dname FROM emp a, dept b WHERE a.deptno =
b.deptno(+)ORDER BY ename
If you wanted to execute the same outer join using native Sybase or SQL Server syntax, your SQL statement might look like this:
SELECT ename, dname FROM emp a, dept b WHERE a.dept =*
b.deptno ORDER BY ename
If you used ODBC/JDBC outer join escapes, your SQL statement would be the same for all of the databases:
SELECT ename, dname FROM {OJ dept LEFT OUTER JOIN emp ON
dept.deptno = emp.deptno} ORDER BY ename
DataDirect Technologies' ADO.NET data providers translate the SQL escapes for the specific backend database.
ODBC/JDBC SQL Escapes
Most ADO.NET data providers use the database's native syntax for parameter markers in SQL statements. In Microsoft's SQL Server data provider, a simple SQL Statement would look like this:
SELECT * FROM table WHERE column=@parametername
With Microsoft's Oracle data provider, that same SQL statement would look like this:
SELECT * FROM table WHERE column=:parametername
Developers must add database-specific code to their programs to generate the different syntax for these two data providers.
DataDirect Technologies has standardized parameter markers across our data providers. For all of our data providers (Oracle, SQL Server, DB2, and Sybase), this SQL statement would be:
SELECT * FROM table WHERE column=?
Error-handling Objects
Microsoft implements the interfaces for error handling differently among their data providers. Their SQL Server data provider has an ErrorCollection object and their Oracle data provider does not. This distinction makes sense based on the differences between the ways the two databases generate errors. However, application developers now have to use different logic for these two data providers: one requires iteration through the ErrorCollection object to retrieve individual errors and the other does not.
All of DataDirect Technologies' data providers implement the ErrorCollection object, so the code logic is the same across all of our data providers.
Standard Error-Code Mapping
In addition to not having a completely standard error-handling methodology, ADO.NET makes no attempt to map the various database-specific errors to any kind of standard error numbers. The Error objects for all of DataDirect Technologies' ADO.NET data providers have a property called SQLState, which contains the ANSI standard SQLState mapping for any errors returned by the database. Coding to use this property can greatly simplify making your code portable across data providers and databases.
For example, if you add code to your application to handle an "invalid password" given by a user at connect time, you will get different error numbers and message text back from the various backend databases.
An invalid password error in Oracle is returned as Oracle error number 1017 with a message text of invalid username/password; logon denied.
An invalid password error in Microsoft SQL Server is retuned as SQLServer error number 18456 with a message text of Login failed for user '<username>'.
An invalid password error in Sybase is returned as Sybase error number 4067 with a message text of Login failed because an incorrect password was supplied.
Coding to handle either the native error number or the message text means that you must have completely different code when using the different databases. However, if you code to the standard ANSI SQLState, your code would expect an invalid password error to return a SQLState of 28000, regardless of the database to which you are connected, and can use the same code to handle the error. Only DataDirect Technologies' data providers offer this standard error-code mapping.
Oracle Ref Cursors
The Progress DataDirect Connect for Oracle data provider supports returning Ref Cursors as standard result sets. Other Oracle ADO.NET data providers may force you to bind the Ref Cursors as an output parameter. Ref Cursors are, conceptually, just a result set. Just as Microsoft SQL Server, DB2 UDB, and Sybase ASE return result sets from stored procedures, Ref Cursors give you the ability to return result sets from an Oracle stored procedure.
The Progress DataDirect data providers treat all of these the same way as (multiple) result sets. This means that you can code for result sets returning from stored procedures in Oracle the same way you code for them in Sybase, SQL Server, or DB2. You do not need to use different code logic just to handle Oracle.
Arrays of Parameters (Bulk Inserts/Updates)
Binding arrays of parameter values is a way for an application to specify multiple rows of values for a single SQL statement on a single execution of a command. Specifying parameters in such a manner can allow the values to be sent to the database server in a single round trip (depending on the native capabilities of the backend database) and can result in significantly improved performance. This type of operation is typically used on INSERT statements to speed up the time it can take to fill a table.
ADO.NET does not have a standardized way to specify arrays of parameters to data providers. In fact, many data providers do not have this capability at all! Progress DataDirect supports a standard mechanism to specify arrays of parameters to all of our data providers, as shown in the following C# code example:
// Create an array of values that need to be inserted int[]
myArrayDeptNo = new int[3]{10, 20, 30};
// Set the command text on the Command object
cmd.CommandText = "insert into dept(deptno) values (?)";
// Set the ArrayBindCount to indicate the number of values
cmd.ArrayBindCount = 3;
// Create a parameter for the array operations OracleParameter prm =
new OracleParameter("deptno", OracleDbType.Int32);
prm.Direction = ParameterDirection.Input;
prm.Value = myArrayDeptNo;
// Add the parameter to the parameter collection
cmd.Parameters.Add(prm);
// Execute the command
cmd.ExecuteNonQuery();
Developers working with multiple databases in .NET applications need to be concerned about the lack of rigidity in the ADO.NET data access standard. This document has shown just a few examples of the interoperability issues programmers are most likely to face when using ADO.NET data providers from different vendors. Other issues do exist. If a careful choice of data providers is not made initially, a substantial amount of recoding may need to be done to make .NET applications work across databases.
At DataDirect Technologies, our sole focus is on building outstanding data connectivity components such as ADO.NET data providers. Our ADO.NET data providers are built with a common architecture and a focus on interoperability. We've solved many of the interoperability issues that currently exist between data providers, such as bulk inserts and bulk updates, error-handling objects and error-code mapping, SQL escapes and parameter markers in SQL statements. We will continue to focus on interoperability as we bring out new releases of existing data providers. Our programming direction ensures that our data providers will deliver the highest degree of interoperability on the market.
With a focus on interoperability, a complete set of data providers for all the major databases, and outstanding performance from an architecture that does not require database client libraries and works entirely within the .NET Common Language Runtime, DataDirect Technologies is clearly the best choice for your .NET applications.
DataDirect Technologies offers the following ADO.NET data providers that support the .NET Framework Version 2.0:
- DataDirect Connect for ADO.NET 3.0 DB2 data provider
- DataDirect Connect for ADO.NET 3.0 Oracle data provider
- DataDirect Connect for ADO.NET 3.0 SQL Server data provider
- DataDirect Connect for ADO.NET 3.0 Sybase data provider
Existing code written for earlier versions of the .NET Framework and earlier versions of DataDirect Connect for .NET is compatible with the 3.0 version of the data providers. Note that the applications must be re-compiled using the .NET Framework 2.0.
However, if your applications require Windows 98 and Windows Me and/or the .NET Framework 1.x, you can use the DataDirect Connect for .NET 2.2 data providers, which DataDirect will continue to make available.