odbc_metadata.java

derby database source code.good for you.

		System.out.println("SQLProcedureColumns:");
		s.execute(
				"call sysibm.sqlprocedurecols(null, '%', 'GETPCTEST%', '%', 'DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_PROCEDURE_COLUMNS);

		System.out.println("SQLTables:");
		s.execute(
			"call sysibm.sqltables (null, null, null, 'SYSTEM TABLE', 'DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_TABLES);

		System.out.println("SQLColumns:");
		s.execute(
			"call sysibm.sqlcolumns ('', null, '', '', 'DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_COLUMNS);

		System.out.println("SQLColumnPrivileges:");
		s.execute(
			"call sysibm.sqlcolprivileges ('Huey', 'Dewey', 'Louie', 'Frooey', 'DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_COLUMN_PRIVILEGES);

		System.out.println("SQLTablePrivileges:");
		s.execute(
			"call sysibm.sqltableprivileges ('Huey', 'Dewey', 'Louie', 'DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_TABLE_PRIVILEGES);

		System.out.println("SQLSpecialColumns: getBestRowIdentifier");
		s.execute(
			"call sysibm.sqlspecialcolumns (1, '', null, 'LOUIE', 1, 1, 'DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_BEST_ROW_IDENTIFIER);

		System.out.println("SQLSpecialColumns: getVersionColumns");
		s.execute(
			"call sysibm.sqlspecialcolumns (2, 'Huey', 'Dewey', 'Louie', 1, 1, 'DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_VERSION_COLUMNS);

		System.out.println("SQLPrimaryKeys:");
		s.execute(
			"call sysibm.sqlprimarykeys ('', '%', 'LOUIE', 'DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_PRIMARY_KEYS);

		System.out.println("SQLForeignKeys: getImportedKeys");
		s.execute(
			"call sysibm.sqlforeignkeys (null, null, null, null, null, null, " +
					"'IMPORTEDKEY=1;DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_IMPORTED_KEYS);

		System.out.println("SQLForeignKeys: getExportedKeys");
		s.execute(
			"call sysibm.sqlforeignkeys (null, null, null, null, null, null, " +
				"'EXPORTEDKEY=1;DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_EXPORTED_KEYS);

		System.out.println("SQLForeignKeys: getCrossReference");
		s.execute(
			"call sysibm.sqlforeignkeys ('', null, 'LOUIE', '', null, 'REFTAB', 'DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_CROSS_REFERENCE);

		System.out.println("SQLGetTypeInfo");
		s.execute(
			"call sysibm.sqlgettypeinfo (0, 'DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_TYPE_INFO);

		System.out.println("SQLStatistics:");
		s.execute(
			"call sysibm.sqlstatistics ('', 'SYS', 'SYSCOLUMNS', 1, 0, 'DATATYPE=''ODBC''')");
		checkODBCNamesAndTypes(s.getResultSet(), GET_INDEX_INFO);

		System.out.println(
			"\n=============== End ODBC 3.0 Compliance Tests =================\n");

	}

	/**
	 * This is where we load the metadata result set schema
	 * that is specified in the ODBC 3.0 spec.  When we do
	 * validation of the ODBC result set, we will compare	
	 * the actual results with the target values that we
	 * load here.
	 *
	 * Target lists consist of three strings for each column
	 * in the result set.  The first string is the expected
	 * (ODBC 3.0) column name. The second string is the
	 * expected column type.  The third string is the
	 * expected column nullability (null means that the
	 * column is nullable; any non-null String means that
	 * the column is NOT NULLABLE).
	 *
	 * The target values in this method come from the following
	 * URL:
	 *
	 * http://msdn.microsoft.com/library/default.asp?url=/library/
	 * en-us/odbc/htm/odbcsqlprocedurecolumns.asp
	 *
	 */
	protected void loadODBCTargets() {

		odbcComplianceTargets[GET_PROCEDURES] = new String [] {

			"PROCEDURE_CAT", "VARCHAR", null,
			"PROCEDURE_SCHEM", "VARCHAR", null,
			"PROCEDURE_NAME", "VARCHAR", "NOT NULL",
			"NUM_INPUT_PARAMS", "INTEGER", null,
			"NUM_OUTPUT_PARAMS", "INTEGER", null,
			"NUM_RESULT_SETS", "INTEGER", null,
			"REMARKS", "VARCHAR", null,
			"PROCEDURE_TYPE", "SMALLINT", null

		};

		odbcComplianceTargets[GET_PROCEDURE_COLUMNS] = new String [] {

			"PROCEDURE_CAT", "VARCHAR", null,
			"PROCEDURE_SCHEM", "VARCHAR", null,
			"PROCEDURE_NAME", "VARCHAR", "NOT NULL",
			"COLUMN_NAME", "VARCHAR", "NOT NULL",
			"COLUMN_TYPE", "SMALLINT", "NOT NULL",
			"DATA_TYPE", "SMALLINT", "NOT NULL",
			"TYPE_NAME", "VARCHAR", "NOT NULL",
			"COLUMN_SIZE", "INTEGER", null,
			"BUFFER_LENGTH", "INTEGER", null,
			"DECIMAL_DIGITS", "SMALLINT", null,
			"NUM_PREC_RADIX", "SMALLINT", null,
			"NULLABLE", "SMALLINT", "NOT NULL",
			"REMARKS", "VARCHAR", null,
			"COLUMN_DEF", "VARCHAR", null,
			"SQL_DATA_TYPE", "SMALLINT", "NOT NULL",
			"SQL_DATETIME_SUB", "SMALLINT", null,
			"CHAR_OCTET_LENGTH", "INTEGER", null,
			"ORDINAL_POSITION", "INTEGER", "NOT NULL",
			"IS_NULLABLE", "VARCHAR", null

		};

		odbcComplianceTargets[GET_TABLES] = new String [] {

			"TABLE_CAT", "VARCHAR", null,
			"TABLE_SCHEM", "VARCHAR", null,
			"TABLE_NAME", "VARCHAR", null,
			"TABLE_TYPE", "VARCHAR", null,
			"REMARKS", "VARCHAR", null

		};

		odbcComplianceTargets[GET_COLUMNS] = new String [] {

			"TABLE_CAT", "VARCHAR", null,
			"TABLE_SCHEM", "VARCHAR", null,
			"TABLE_NAME", "VARCHAR", "NOT NULL",
			"COLUMN_NAME", "VARCHAR", "NOT NULL",
			"DATA_TYPE", "SMALLINT", "NOT NULL",
			"TYPE_NAME", "VARCHAR", "NOT NULL",
			"COLUMN_SIZE", "INTEGER", null,
			"BUFFER_LENGTH", "INTEGER", null,
			"DECIMAL_DIGITS", "SMALLINT", null,
			"NUM_PREC_RADIX", "SMALLINT", null,
			"NULLABLE", "SMALLINT", "NOT NULL",
			"REMARKS", "VARCHAR", null,
			"COLUMN_DEF", "VARCHAR", null,
			"SQL_DATA_TYPE", "SMALLINT", "NOT NULL",
			"SQL_DATETIME_SUB", "SMALLINT", null,
			"CHAR_OCTET_LENGTH", "INTEGER", null,
			"ORDINAL_POSITION", "INTEGER", "NOT NULL",
			"IS_NULLABLE", "VARCHAR", null

		};

		odbcComplianceTargets[GET_COLUMN_PRIVILEGES] = new String [] {

			"TABLE_CAT", "VARCHAR", null,
			"TABLE_SCHEM", "VARCHAR", null,
			"TABLE_NAME", "VARCHAR", "NOT NULL",
			"COLUMN_NAME", "VARCHAR", "NOT NULL",
			"GRANTOR", "VARCHAR", null,
			"GRANTEE", "VARCHAR", "NOT NULL",
			"PRIVILEGE", "VARCHAR", "NOT NULL",
			"IS_GRANTABLE", "VARCHAR", null

		};

		odbcComplianceTargets[GET_TABLE_PRIVILEGES] = new String [] {

			"TABLE_CAT", "VARCHAR", null,
			"TABLE_SCHEM", "VARCHAR", null,
			"TABLE_NAME", "VARCHAR", "NOT NULL",
			"GRANTOR", "VARCHAR", null,
			"GRANTEE", "VARCHAR", "NOT NULL",
			"PRIVILEGE", "VARCHAR", "NOT NULL",
			"IS_GRANTABLE", "VARCHAR", null

		};

		// Next two corresond to ODBC's "SQLSpecialColumns".

		odbcComplianceTargets[GET_BEST_ROW_IDENTIFIER] = new String [] {

			"SCOPE", "SMALLINT", null,
			"COLUMN_NAME", "VARCHAR", "NOT NULL",
			"DATA_TYPE", "SMALLINT", "NOT NULL",
			"TYPE_NAME", "VARCHAR", "NOT NULL",
			"COLUMN_SIZE", "INTEGER", null,
			"BUFFER_LENGTH", "INTEGER", null,
			"DECIMAL_DIGITS", "SMALLINT", null,
			"PSEUDO_COLUMN", "SMALLINT", null

		};

		odbcComplianceTargets[GET_VERSION_COLUMNS] = 
			odbcComplianceTargets[GET_BEST_ROW_IDENTIFIER];

		odbcComplianceTargets[GET_PRIMARY_KEYS] = new String [] {

			"TABLE_CAT", "VARCHAR", null,
			"TABLE_SCHEM", "VARCHAR", null,
			"TABLE_NAME", "VARCHAR", "NOT NULL",
			"COLUMN_NAME", "VARCHAR", "NOT NULL",
			"KEY_SEQ", "SMALLINT", "NOT NULL",
			"PK_NAME", "VARCHAR", null

		};

		// Next three correspond to ODBC's "SQLForeignKeys".

		odbcComplianceTargets[GET_IMPORTED_KEYS] = new String [] {

			"PKTABLE_CAT", "VARCHAR", null,
			"PKTABLE_SCHEM", "VARCHAR", null,
			"PKTABLE_NAME", "VARCHAR", "NOT NULL",
			"PKCOLUMN_NAME", "VARCHAR", "NOT NULL",
			"FKTABLE_CAT", "VARCHAR", null,
			"FKTABLE_SCHEM", "VARCHAR", null,
			"FKTABLE_NAME", "VARCHAR", "NOT NULL",
			"FKCOLUMN_NAME", "VARCHAR", "NOT NULL",
			"KEY_SEQ", "SMALLINT", "NOT NULL",
			"UPDATE_RULE", "SMALLINT", null,
			"DELETE_RULE", "SMALLINT", null,
			"FK_NAME", "VARCHAR", null,
			"PK_NAME", "VARCHAR", null,
			"DEFERRABILITY", "SMALLINT", null

		};

		odbcComplianceTargets[GET_EXPORTED_KEYS] =
			odbcComplianceTargets[GET_IMPORTED_KEYS];

		odbcComplianceTargets[GET_CROSS_REFERENCE] =
			odbcComplianceTargets[GET_IMPORTED_KEYS];

		odbcComplianceTargets[GET_TYPE_INFO] = new String [] {

			"TYPE_NAME", "VARCHAR", "NOT NULL",
			"DATA_TYPE", "SMALLINT", "NOT NULL",
			"COLUMN_SIZE", "INTEGER", null,
			"LITERAL_PREFIX", "VARCHAR", null,
			"LITERAL_SUFFIX", "VARCHAR", null,
			"CREATE_PARAMS", "VARCHAR", null,
			"NULLABLE", "SMALLINT", "NOT NULL",
			"CASE_SENSITIVE", "SMALLINT", "NOT NULL",
			"SEARCHABLE", "SMALLINT", "NOT NULL",
			"UNSIGNED_ATTRIBUTE", "SMALLINT", null,
			"FIXED_PREC_SCALE", "SMALLINT", "NOT NULL",
			"AUTO_UNIQUE_VAL", "SMALLINT", null,
			"LOCAL_TYPE_NAME", "VARCHAR", null,
			"MINIMUM_SCALE", "SMALLINT", null,
			"MAXIMUM_SCALE", "SMALLINT", null,
			"SQL_DATA_TYPE", "SMALLINT", "NOT NULL",
			"SQL_DATETIME_SUB", "SMALLINT", null,
			"NUM_PREC_RADIX", "INTEGER", null,
			"INTERVAL_PRECISION", "SMALLINT", null

		};

		// Next one corresponds to ODBC's "SQLStatistics".
		odbcComplianceTargets[GET_INDEX_INFO] = new String [] {

			"TABLE_CAT", "VARCHAR", null,
			"TABLE_SCHEM", "VARCHAR", null,
			"TABLE_NAME", "VARCHAR", "NOT NULL",
			"NON_UNIQUE", "SMALLINT", null,
			"INDEX_QUALIFIER", "VARCHAR", null,
			"INDEX_NAME", "VARCHAR", null,
			"TYPE", "SMALLINT", "NOT NULL",
			"ORDINAL_POSITION", "SMALLINT", null,
			"COLUMN_NAME", "VARCHAR", null,
			"ASC_OR_DESC", "CHAR", null,
			"CARDINALITY", "INTEGER", null,
			"PAGES", "INTEGER", null,
			"FILTER_CONDITION", "VARCHAR", null

		};

	}

	/**
	 * Takes result set metadata and sees if the names
	 * and types of its columns match what ODBC 3.0
	 * dictates.
	 */
	protected void checkODBCNamesAndTypes(ResultSet rs, int procId)
		throws SQLException
	{

		ResultSetMetaData rsmd = rs.getMetaData();

		int numCols = rsmd.getColumnCount();
		int targetCols = odbcComplianceTargets[procId].length / 3;
		if (numCols < targetCols) {
		// result set is missing columns, so we already know
		// something is wrong.
			System.out.println(
				" --> ODBC COMPLIANCE FAILED: Result set was missing columns.");
			return;
		}

		// Check type and name of each column in the result set.
		for (int i = 1; i <= targetCols; i++) {

			int offset = 3 * (i - 1);
			if (!rsmd.getColumnLabel(i).equals(odbcComplianceTargets[procId][offset])) {
				System.out.println(
					"--> ODBC COMPLIANCE FAILED: Column name '" + rsmd.getColumnLabel(i) +
					"' does not match expected name '" +
					odbcComplianceTargets[procId][offset] + "'.");
			}
			if (!rsmd.getColumnTypeName(i).equals(odbcComplianceTargets[procId][offset + 1])) {
				System.out.println(
					"--> ODBC COMPLIANCE FAILED: Column type '" + rsmd.getColumnTypeName(i) +
					"' does not match expected type '" +
					odbcComplianceTargets[procId][offset + 1] + "' for column '" +
					rsmd.getColumnLabel(i) + "'.");
			}

		}

		System.out.println("==> ODBC type/name checking done.");

	}

	/**
	 * Takes result set metadata and sees if the
	 * nullability of its columns match what ODBC 3.0
	 * dictates.
	 */
	protected boolean badNullability(int odbcProc, String colName,
		int colNum, boolean wasNull)
	{

		return (wasNull &&
			odbcComplianceTargets[odbcProc][3 * (colNum-1) + 2] != null);

	}

	private static String addQuotes(String str) {

		if (str == null)
			return "null";

		if (str.length() == 0)
			return "''";

		if ((str.charAt(0) == '\'') && (str.charAt(str.length()-1) == '\''))
		// already have quotes.
			return str;

		return "'" + str + "'";

	}

}