Announcement Announcement Module
Collapse
No announcement yet.
SimpleJdbcInsert and DB2/400 Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SimpleJdbcInsert and DB2/400

    Hi,

    I'm using a DriverManagedDatasource with the JTOpen driver to access DB2 on a AS/400 (iSeries) system.

    I am trying to use SimpleJdbcInsert without specifying the schema name (which is already specified in my datasource URL "jdbc:as400:<server>/<schema>;libraries=*LIBL,<schema>;") and I am getting the following error:

    Code:
    org.springframework.dao.DataAccessResourceFailureException: 
    Unable to locate table meta data for '<table_name>' in the default schema
    I assumed that SimpleJdbcInsert would use the default schema from datasource configuration. Has anyone experienced the same situation?

    Thanks!

  • #2
    Looking at the code of GenericTableMetaDataProvider, I found what looks like root cause of the issue.

    The GenericMetaDataProvider creates a Map containing the table metadata where the key is the schema name and then (if you didn't provide a schema name using the withSchemaName() method ) it looks for the table in the same map using the uppercased username as the key. Obviously, it won't work unless the username is equal to the schema name (or lib in the case of DB2).

    A possible correction would be iterating through the values of the Map until a table with the provided name is found and then returning it.

    I am using Spring 2.5.6-SEC01. I don't know if this still exists in more current versions.

    Comment


    • #3
      Same here

      Hi there,

      I'm facing the same problem. I defined the data source through the WAS admin console and specified the currentSchema.

      Then it seems that Spring is unable to get the defined currentSchema from the table metadata.

      Any solution for this?

      Jose

      Comment


      • #4
        Solution here.

        Hi again,

        I've corrected this issue and now it works fine. It's very likely that I'm missing something but, form the analisys of the code I got:

        Class org.springframework.jdbc.core.metadata.GenericTabl eMetaDataProvider

        1) Method: private void locateTableAndProcessMetaData
        Tries to find out the table metadata. If you have defined the schema in the currentSchema property (WAS admin console), if finds it here:
        line 277
        Code:
        tmd.setSchemaName(tables.getString("TABLE_SCHEM"));
        and saves it here
        line 284 ->
        Code:
        tableMeta.put(tmd.getSchemaName().toUpperCase(), tmd);
        So now we have the schema in the map under tmd.getSchemaName()

        2) Afterwards line 306,
        Code:
         if schemaName == null
        As schemaName comes from the context, and (wild guess) it comes from SimpleJdbcInsert.withSchemaName("XXXX") and we haven't written .withSchemaName because
        the schema was defined in WAS, that property will always be null and thus, it'll never access the else block where the schema could be extracted from the map (it'll try to get it from the userName):

        Code:
        if (schemaName == null) {
        				tmd = tableMeta.get(userName.toUpperCase());
        				if (tmd == null) {
        					tmd = tableMeta.get("PUBLIC");
        					if (tmd == null) {
        						tmd = tableMeta.get("DBO");
        					}
        					if (tmd == null) {
        						throw new DataAccessResourceFailureException("Unable to locate table meta data for '" + tableName + "' in the default schema");
        					}
        				}
        			}
        			else {
        				tmd = tableMeta.get(schemaName.toUpperCase());
        				if (tmd == null) {
        					throw new DataAccessResourceFailureException("Unable to locate table meta data for '" + tableName + "' in the '" + schemaName + "' schema");
        				}
        			}
        In conclusion, I'd say that we should be able to access the previously found metadata (lines 277-284), despite the fact that it wasn't defined programatically. So, just by setting the schema value in the method arg schemaName it will work:

        Code:
        /**
        	 * Method supporting the metedata processing for a table
        	 */
        	private void locateTableAndProcessMetaData(DatabaseMetaData databaseMetaData, String catalogName, String schemaName, String tableName) {
        		Map<String, TableMetaData> tableMeta = new HashMap<String, TableMetaData>();
        		ResultSet tables = null;
        
        		try {
        			tables = databaseMetaData.getTables(
        				catalogNameToUse(catalogName),
        				schemaNameToUse(schemaName),
        				tableNameToUse(tableName),
        				null);
        			while (tables != null && tables.next()) {
        				TableMetaData tmd = new TableMetaData();
        				tmd.setCatalogName(tables.getString("TABLE_CAT"));
        				tmd.setSchemaName(tables.getString("TABLE_SCHEM"));
        				tmd.setTableName(tables.getString("TABLE_NAME"));
        				tmd.setType(tables.getString("TABLE_TYPE"));
        				if (tmd.getSchemaName() == null) {
        					tableMeta.put(userName.toUpperCase(), tmd);
        				}
        				else {
        					tableMeta.put(tmd.getSchemaName().toUpperCase(), tmd);
        					/**
        					 * WORKAROUND to allow discovered metadata to be used afterwards - from line 310 on
        					 * @author JINGA4X - JosÚ A. ═˝igo
        					 */
        					schemaName=tmd.getSchemaName().toUpperCase();
        				}
        			}
        		}
        		catch (SQLException se) {
        			logger.warn("Error while accessing table meta data results" + se.getMessage());
        		}
        		finally {
        			if (tables != null) {
        				try {
        					tables.close();
        				} catch (SQLException e) {
        					logger.warn("Error while closing table meta data reults" + e.getMessage());
        				}
        			}
        		}
        
        		if (tableMeta.size() < 1) {
        			logger.warn("Unable to locate table meta data for '" + tableName +"' -- column names must be provided");
        		}
        		else {
        			TableMetaData tmd = null;
        			if (schemaName == null) {
        				tmd = tableMeta.get(userName.toUpperCase());
        				if (tmd == null) {
        					tmd = tableMeta.get("PUBLIC");
        					if (tmd == null) {
        						tmd = tableMeta.get("DBO");
        					}
        					if (tmd == null) {
        						throw new DataAccessResourceFailureException("Unable to locate table meta data for '" + tableName + "' in the default schema");
        					}
        				}
        			}
        			else {
        				tmd = tableMeta.get(schemaName.toUpperCase());
        				if (tmd == null) {
        					throw new DataAccessResourceFailureException("Unable to locate table meta data for '" + tableName + "' in the '" + schemaName + "' schema");
        				}
        			}
        
        			processTableColumns(databaseMetaData, tmd);
        		}
        	}
        There it is. I'd appreciate if any team member could consider what I've explained here to check if I'm missing something,

        BTW, I'm using 2.5.6-SEC1 but I've seen in the source from 3.0.3 that it's still there

        Jose
        Last edited by spring-like-jose; Aug 5th, 2010, 07:46 AM.

        Comment

        Working...
        X