ODBC .NET Data Provider restrictions

The ODBC .NET Data Provider is no longer tested. Users are recommended to use the IBM® Data Server Provider for .NET.

The following table identifies usage restrictions for the ODBC .NET Data Provider:
Table 1. ODBC .NET Data Provider restrictions
Class or feature Restriction description Db2® servers affected
ASCII character streams You cannot use ASCII character streams with OdbcParameters when using DbType.AnsiString or DbType.AnsiStringFixedLength.
The ODBC .NET Data Provider will throw the following exception: 
"Specified cast is not valid"

Workaround: Use DbType.Binary instead of using DbType.AnsiString or DbType.AnsiStringFixedLength.

 All
Command.Prepare Before executing a command (Command.ExecuteNonQuery or Command.ExecuteReader), you must explicitly run OdbcCommand.Prepare() if the CommandText has changed since the last prepare. If you do not call OdbcCommand.Prepare() again, the ODBC .NET Data Provider will execute the previously prepared CommandText.
For Example: 
[C#]
command.CommandText="select CLOB('ABC') from table1";
command.Prepare();
command.ExecuteReader();
command.CommandText="select CLOB('XYZ') from table2";

// This ends up re-executing the first statement
command.ExecuteReader();
All
CommandBehavior. SequentialAccess When using IDataReader.GetChars() to read from a reader created with CommandBehavior.SequentialAccess, you must allocate a buffer that is large enough to hold the entire column. Otherwise, you will hit the following exception: 
Requested range extends past the end of the array.
   at System.Runtime.InteropServices.Marshal.Copy(Int32 source,
     Char[] destination, Int32 startIndex, Int32 length)
   at System.Data.Odbc.OdbcDataReader.GetChars(Int32 i,
     Int64 dataIndex, Char[] buffer, Int32 bufferIndex, Int32 length)
   at OleRestrict.TestGetCharsAndBufferSize(IDbConnection con)
The following example demonstrates how to allocate an adequate buffer: 
CREATE TABLE myTable(c0 int, c1 CLOB(10K))
SELECT c1 FROM myTable;

[C#]
cmd.CommandText = "SELECT c1 from myTable";
IDataReader reader = 
cmd.ExecuteReader(CommandBehavior.SequentialAccess);

Int32 iChunkSize = 10;
Int32 iBufferSize = 10;
Int32 iFieldOffset = 0;

Char[] buffer = new Char[ iBufferSize ];

reader.Read();
reader.GetChars(0, iFieldOffset, buffer, 0, iChunkSize);
The call to GetChars() will throw the following exception: 
"Requested range extends past the end of the array"
To ensure that GetChars() does not throw the exception mentioned previously, you must set the BufferSize to the size of the column, as follows: 
Int32 iBufferSize = 10000;
Note that the value of 10,000 for iBufferSize corresponds to the value of 10K allocated to the CLOB column c1.
 All
CommandBehavior. SequentialAccess The ODBC .NET Data Provider throws the following exception when there is no more data to read when using OdbcDataReader.GetChars(): 
NO_DATA - no error information available
   at System.Data.Odbc.OdbcConnection.HandleError(
    HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
   at System.Data.Odbc.OdbcDataReader.GetData(
    Int32 i, SQL_C sqlctype, Int32 cb)
   at System.Data.Odbc.OdbcDataReader.GetChars(
    Int32 i, Int64 dataIndex, Char[] buffer, 
    Int32 bufferIndex, Int32 length)
 All
CommandBehavior. SequentialAccess You may not be able to use large chunksizes, such as a value of 5000, when using OdbcDataReader.GetChars(). When you attempt to use a large chunk size, the ODBC .NET Data Provider will throw the following exception: 
Object reference not set to an instance of an object.
   at System.Runtime.InteropServices.Marshal.Copy(Int32 source,
     Char[] destination, Int32 startIndex, Int32 length)
   at System.Data.Odbc.OdbcDataReader.GetChars(
    Int32 i, Int64 dataIndex, Char[] buffer, 
    Int32 bufferIndex, Int32 length)
   at OleRestrict.TestGetCharsAndBufferSize(IDbConnection con)
 All
Connection pooling The ODBC .NET Data Provider does not control connection pooling. Connection pooling is handled by the ODBC Driver Manager. For more information about connection pooling, see the ODBC Programmer's Reference in the MSDN library located at 
http://msdn.microsoft.com/library
 All
DataColumnMapping The case of the source column name needs to match the case used in the system catalog tables, which is upper-case by default. All
Decimal columns Parameter markers are not supported for Decimal columns.

You generally use OdbcType.Decimal for an OdbcParameter if the target SQLType is a Decimal column; however, when the ODBC .NET Data Provider sees the OdbcType.Decimal, it binds the parameter using C-type of SQL_C_WCHAR and SQLType of SQL_VARCHAR, which is invalid.

For example: 
[C#]
cmd.CommandText = "SELECT dec_col FROM MYTABLE WHERE dec_col > ? ";
OdbcParameter p1 = cmd.CreateParameter();
p1.DbType = DbType.Decimal;
p1.Value = 10.0;
cmd.Parameters.Add(p1);
IDataReader rdr = cmd.ExecuteReader();
You will get an exception: 
ERROR [07006] [IBM][CLI Driver][SQLDS/VM] 
   SQL0301N  The value of input host variable or parameter 
   number "" cannot be used because of its data type.  
   	SQLSTATE=07006

Workaround: Instead of using OdbcParameter values, use literals exclusively.

 Db2 for VM/VSE
Key information The schema name used to qualify the table name (for example, MYSCHEMA.MYTABLE) must match the connection user ID. The ODBC .NET Data Provider is unable to retrieve any key information in which the specified schema is different from the connection user id.
For example: 
CREATE TABLE USERID2.TABLE1(c1 INT NOT NULL PRIMARY KEY);

[C#]
// Connect as user bob
odbcCon = new OdbcConnection("DSN=sample;UID=bob;PWD=mypassword");

OdbcCommand cmd = odbcCon.CreateCommand();

// Select from table with schema USERID2
cmd.CommandText="SELECT * FROM USERID2.TABLE1";

// Fails - No key info retrieved
da.FillSchema(ds, SchemaType.Source);

// Fails - SchemaTable has no primary key
cmd.ExecuteReader(CommandBehavior.KeyInfo)

// Throws exception because no primary key
cbuilder.GetUpdateCommand();
 All
Key information The ODBC .NET Data Provider cannot retrieve key information when opening a IDataReader at the same time. When the ODBC .NET Data Provider opens a IDataReader, a cursor on the server is opened. If key information is requested, it will then call SQLPrimaryKeys() or SQLStatistic() to get the key information, but these schema functions will open another cursor. Since Db2 for VM/VSE does not support cursor withhold, the first cursor is then closed. Consequently, IDataReader.Read() calls to the IDataReader will result in the following exception: 
System.Data.Odbc.OdbcException: ERROR [HY010] [IBM][CLI Driver]
  CLI0125E  Function sequence error. SQLSTATE=HY010
Workaround: You will need to retrieve key information first then retrieve the data. For example: 
[C#]
OdbcCommand cmd = odbcCon.CreateCommand();
OdbcDataAdapter da = new OdbcDataAdapter(cmd);

cmd.CommandText  = "SELECT * FROM MYTABLE";

// Use FillSchema to retrieve just the schema information
da.FillSchema(ds, SchemaType.Source);
// Use FillSchema to retrieve just the schema information
da.Fill(ds);
 Db2 for VM/VSE
Key information You must refer to database objects in your SQL statements using the same case that the database objects are stored in the system catalog tables. By default database objects are stored in uppercase in the system catalog tables, so most often, you need to use uppercase.

The ODBC .NET Data Provider scans SQL statements to retrieve database object names and passes them to schema functions such as SQLPrimaryKeys and SQLStatistics, which issue queries for these objects in the system catalog tables. The database object references must match exactly how they are stored in the system catalog tables, otherwise, an empty result set is returned.
Db2 for OS/390
Db2 for OS/400
Db2 for VM/VSE
Key information for batched non-select SQL statements The ODBC .NET Data Provider is unable to retrieve any key information for a batch statement that does not start with "SELECT".
Db2 for OS/390
Db2 for OS/400
Db2 for VM/VSE
LOB columns The ODBC .NET Data Provider does not support LOB datatypes. Consequently, whenever the Db2 server returns a SQL_CLOB (-99), SQL_BLOB (-98) or SQL_DBCLOB (-350) the ODBC .NET Data Provider will throw the following exception: 
"Unknown SQL type - -98"     (for Blob column)
"Unknown SQL type - -99"     (for Clob column)
"Unknown SQL type - -350"    (for DbClob column)
Any methods that directly or indirectly access LOB columns will fail.
Workaround: Set the CLI/ODBC LongDataCompat keyword to 1. Doing so will force the CLI driver to make the following data type mappings to data types the ODBC .NET Data Provider will understand:
  • SQL_CLOB to SQL_LONGVARCHAR
  • SQL_BLOB to SQL_LONGVARBINARY
  • SQL_DBCLOB to SQL_WLONGVARCHAR
To set the LongDataCompat keyword, run the following Db2 command from a Db2 command window on the client machine: 
db2 update cli cfg for section common using longdatacompat 1
You can also set this keyword in your application, using the connection string as follows: 
[C#]
OdbcConnection con =
  new OdbcConnection("DSN=SAMPLE;UID=uid;PWD=mypwd;LONGDATACOMPAT=1;");
For a list of all the CLI/ODBC keywords, refer to the UID CLI/ODBC configuration keyword.
 All
OdbcCommand.Cancel Executing statements after running OdbcCommand.Cancel can lead to the following exception: 
"ERROR [24000] [Microsoft][ODBC Driver Manager] 
    Invalid cursor state"
 All
OdbcCommandBuilder The OdbcCommandBuilder fails to generate commands against servers that do not support escape characters. When the OdbcCommandBuilder generates commands, it first makes a call to SQLGetInfo, requesting the SQL_SEARCH_PATTERN_ESCAPE attribute. If the server does not support escape characters an empty string is returned, which causes the ODBC .NET Data Provider to throw the following exception: 
Index was outside the bounds of the array.
   at System.Data.Odbc.OdbcConnection.get_EscapeChar()
   at System.Data.Odbc.OdbcDataReader.GetTableNameFromCommandText()
   at System.Data.Odbc.OdbcDataReader.BuildMetaDataInfo()
   at System.Data.Odbc.OdbcDataReader.GetSchemaTable()
   at System.Data.Common.CommandBuilder.BuildCache(
    Boolean closeConnection)
   at System.Data.Odbc.OdbcCommandBuilder.GetUpdateCommand()
 Db2 for OS/390®, DBCS servers only; Db2 for VM/VSE, DBCS servers only
OdbcCommandBuilder Case-sensitivity is important when using the OdbcCommandBuilder to automatically generate UPDATE, DELETE, and INSERT statements. By default, Db2 stores schema information (such as table names, and column names) in the system catalog tables in upper case, unless they have been explicitly created with case-sensitivity (by adding quotation marks around database objects during create-time). As such, your SQL statements must match the case that is stored in the catalogs (which by default is uppercase).
For example, if you created a table using the following statement: 
"db2 create table mytable (c1 int) "
then Db2 will store the table name "mytable" in the system catalog tables as "MYTABLE".
The following code example demonstrates proper use the OdbcCommandBuilderclass: 
[C#]
OdbcCommand cmd = odbcCon.CreateCommand();
cmd.CommandText  = "SELECT * FROM MYTABLE";
OdbcDataAdapter da = new OdbcDataAdapter(cmd);
OdbcCommandBuilder cb = new OdbcCommandBuilder(da);
OdbcCommand updateCmd = cb.GetUpdateCommand();
In this example, if you do not refer to the table name in upper-case characters, then you will get the following exception: 
"Dynamic SQL generation for the UpdateCommand is not
supported against a SelectCommand that does not return
any key column information."
 All
OdbcCommandBuilder The commands generated by the OdbcCommandBuilder are incorrect when the SELECT statement contains the following column data types: 
REAL
FLOAT or DOUBLE
TIMESTAMP
These data types cannot be used in the WHERE clause for SELECT statements.
Db2 for OS/390
Db2 for OS/400
Db2 for VM/VSE
OdbcCommandBuilder. DeriveParameters The DeriveParameters() method is mapped to SQLProcedureColumns and it uses the CommandText property for the name of the stored procedure. Since CommandText does not contain the name of the stored procedure (using full ODBC call syntax), SQLProcedureColumns is called with the procedure name identified according to the ODBC call syntax. For example: 
"{ CALL myProc(?) }"
This which will result in an empty result set, where no columns are found for the procedure).		 All
OdbcCommandBuilder. DeriveParameters To use DeriveParameters(), specify the stored procedure name in the CommandText (for example, cmd.CommandText = "MYPROC"). The procedure name must match the case stored in the system catalog tables. DeriveParameters() will return all the parameters for that procedure name it finds in the system catalog tables. Remember to change the CommandText back to the full ODBC call syntax before executing the statement. All
OdbcCommandBuilder. DeriveParameters The ReturnValue parameter is not returned for the ODBC .NET Data Provider. All
OdbcCommandBuilder. DeriveParameters DeriveParameters() does not support fully qualified stored procedure names. For example, calling DeriveParameters() for CommandText = "MYSCHEMA.MYPROC" will fail. Here, no parameters are returned. All
OdbcCommandBuilder. DeriveParameters DeriveParameters() will not work for overloaded stored procedures. The SQLProcedureColumns will return all the parameters for all versions of the stored procedure. All
OdbcConnection. ChangeDatabase The OdbcConnection.ChangeDatabase() method is not supported. All
OdbcConnection. ConnectionString
  • The Server keyword is ignored.
  • The Connect Timeout keyword is ignored. CLI does not support connection timeouts, so setting this property will not affect the driver.
  • Connection pooling keywords are ignored. Specifically, this affects the following keywords: Pooling, Min Pool Size, Max Pool Size, Connection Lifetime and Connection Reset.
 All
OdbcDataReader. GetSchemaTable The ODBC .NET Data Provider is not able to retrieve extended describe information from servers that do not return extended describe information. Therefore, if you are connecting to a server that does not support extended describe (the affected servers), the following columns in the metadata table returned from IDataReader.GetSchemaTable() are invalid:
  • IsReadOnly
  • IsUnique
  • IsAutoIncrement
  • BaseSchemaName
  • BaseCatalogName
Db2 for OS/390,
version 7 or lower
Db2 for OS/400
Db2 for VM/VSE
Stored procedures To call a stored procedure, you need to specify the full ODBC call syntax.
For example, to call the stored procedure, MYPROC, that takes a VARCHAR(10) as a parameter: 
[C#]
OdbcCommand cmd = odbcCon.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "{ CALL MYPROC(?) }"
OdbcParameter p1 = cmd.CreateParameter();
p1.Value = "Joe";
p1.OdbcType = OdbcType.NVarChar;
cmd.Parameters.Add(p1);
cmd.ExecuteNonQuery();
Note: Note that you must use the full ODBC call syntax even if you are using CommandType.StoredProcedure.
This is documented in MSDN, under the OdbcCommand.CommandText Property.
 All
Stored procedures: no column names for result sets The Db2 for OS/390 version 6.1 server does not return column names for result sets returned from a stored procedure. The ODBC .NET Data Provider maps these unnamed columns to their ordinal position (for example, "1", "2" "3"). This is contrary to the mapping documented in MSDN: "Column1", "Column2", "Column3". Db2 for OS/390 version 6.1
Unique index promotion to primary key The ODBC .NET Data Provider promotes nullable unique indexes to primary keys. This is contrary to the MSDN documentation, which states that nullable unique indexes should not be promoted to primary keys. All