Name: boT120536 Date: 01/25/2001


C:\>java -version
java version "1.1.8"

When using JDBC-ODBC bridge with Access 2000 DatabaseMetaData.getColumns returns
IS_NULLABLE="YES" for table columns that are "required" ie. not nullable.

This test was run under Windows NT 4.0 using JDK 1.1.8 (downloaded from
http://java.sun.com/products/jdk/1.1).

Results of test:
--------------------------------------------------------------------------------
C:\XLE\xle_metacheck_18jdk>java MetaCheckTest insert company
coid,name,addr,notnullint 160,'Testco60','NY',1
Metadata for table COMPANY:

Cat. Schema Table Col. Type Nullable
------------------------------------------------
C:\Program Files\Microsoft Office\Databases\Testdb1 null COMPANY COID INTEGER
YES
C:\Program Files\Microsoft Office\Databases\Testdb1 null COMPANY NAME VARCHAR
YES
C:\Program Files\Microsoft Office\Databases\Testdb1 null COMPANY ADDR VARCHAR
YES
C:\Program Files\Microsoft Office\Databases\Testdb1 null COMPANY NOTNULLINT
INTEGER YES
C:\Program Files\Microsoft Office\Databases\Testdb1 null COMPANY NOTNULLTEXT
VARCHAR YES

Unable to obtain primary key information directly - trying index information...

Primary Keys : COID seq 1

Metadata checked - No errors found in arguments

SQL Error(XLE) : [Microsoft][ODBC Microsoft Access Driver] The field
'COMPANY.notnulltext' cannot contain a Null value because the Required property
for this field is set to True. Enter a value in this field.

Test completed

-------------------------------------------------------------------------------

NOTE that under "Metadata for table COMPANY" all columns have nullable "YES"
although COID, NOTNULLINT and NOTNULLTEXT are "required" in the table ie.
should show "NO".

Now see the same test data and program run with a DB/2 database.
--------------------------------------------------------------------------------
C:\XLE\xle_metacheck_18jdk>java MetaCheckTest insert company
coid,name,addr,notnullint 160,'Testco60','NY',1
Metadata for table COMPANY:

Cat. Schema Table Col. Type Nullable
------------------------------------------------
null ADMINISTRATOR COMPANY COID INTEGER NO
null ADMINISTRATOR COMPANY NAME VARCHAR YES
null ADMINISTRATOR COMPANY ADDR VARCHAR YES
null ADMINISTRATOR COMPANY NOTNULLINT INTEGER NO
null ADMINISTRATOR COMPANY NOTNULLTEXT VARCHAR NO

Primary Keys : COID seq 1

Error: Table Column NOTNULLTEXT must be provided and may not be null

Test completed
--------------------------------------------------------------------------------

Here the getColumns returns IS_NULLABLE="NO" for the not_nullable columns and
the program notices the missing column and does not attempt the insert.

Below is the source code that obtains MetaData from the database and prints the
information on the report:
--------------------------------------------------------------------------------
 // read metadata
DatabaseMetaData dbmd = con.getMetaData();
String tableName = xleIns.tableName;

 //get metadata about table columns and save this in vector metaCols
ResultSet cols = dbmd.getColumns(null,null,tableName,"%");
tableFound = saveCols(cols);


/**
 * Save the columns metadata in vector metaCols
 * Creation date: (11/02/00 9:36:44 AM)
 * @param rs java.sql.ResultSet
 */
public boolean saveCols (ResultSet rs) throws SQLException {
boolean tableFound, moreColumns;

if (rs.next() == false) { //Result set is empty
System.out.println ("Error: Table " + xleIns.tableName +
" can not be found.");
tableFound = false;
return tableFound;
}
else {
tableFound = true;
moreColumns = true;
System.out.println ("Metadata for table " + xleIns.tableName +
":");
System.out.println (" ");
System.out.println ("Cat. Schema Table Col. Type
Nullable ");
System.out.println
("------------------------------------------------ ");
}

while (moreColumns) {
MetaCol mc = new MetaCol();

//xleIns.tableColumns.addElement(column.toUpperCase());
mc.cat = rs.getString(1);
mc.schema = rs.getString(2);
mc.table = (rs.getString(3)).toUpperCase();
mc.colName = (rs.getString(4)).toUpperCase();
mc.type = (rs.getString(6)).toUpperCase();
mc.pos = rs.getInt(17);
mc.nullable = (rs.getString(18)).toUpperCase();

System.out.println (mc.cat + " " + mc.schema + " " + mc.table +
" "
+ mc.colName + " " + mc.type + " " + mc.nullable);
metaCols.addElement(mc);

if (rs.next()) moreColumns = true;
else moreColumns = false;
}

return tableFound;
}
--------------------------------------------------------------------------------

I also checked the NULLABLE column (type integer - position 11) in the result
set produced by getColumns. It is also incorrect for Access tables. '1' is
returned for all columns although '0' should be returned for the "not-nullable"
columns.

Here is the same problem, reproduced with JDK version 1.3:

C:\XLE\xle_metacheck_18jdk>java -version
java version "1.3.0"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.0-C)
Java HotSpot(TM) Client VM (build 1.3.0-C, mixed mode)

C:\XLE\xle_metacheck_18jdk>java MetaCheckTest insert company
coid,name,addr,notnullint 160,'Testco60','NY',1
Metadata for table COMPANY:

Cat. Schema Table Col. Type Nullable
------------------------------------------------
C:\Program Files\Microsoft Office\Databases\Testdb1 null COMPANY COID INTEGER
YES
C:\Program Files\Microsoft Office\Databases\Testdb1 null COMPANY NAME VARCHAR
YES
C:\Program Files\Microsoft Office\Databases\Testdb1 null COMPANY ADDR VARCHAR
YES
C:\Program Files\Microsoft Office\Databases\Testdb1 null COMPANY NOTNULLINT
INTEGER YES
C:\Program Files\Microsoft Office\Databases\Testdb1 null COMPANY NOTNULLTEXT
VARCHAR YES

Unable to obtain primary key information directly - trying index information...

Primary Keys : COID seq 1

Metadata checked - No errors found in arguments

SQL Error(XLE) : [Microsoft][ODBC Microsoft Access Driver] The field
'COMPANY.notnulltext' cannot contain a Null value because the Required property
for this field is set to True. Enter a value in this field.

Test completed

(Review ID: 115618)
======================================================================