Table Metadata from DB2 using jdbcTemplate

[sanumala]

Hi

I have a List<String> tableNames;

I wanted to

for(String tableName :tableNames)
{
     //Here I wanted to get table meata data (ie, column names and types)
     //using jdbcTemplate
}

In particular IBM DB2 UDB has some isssue where you can't retrive the metadata if table doesn't have any rows. For now we are using something like

select * from table_name fetch first 1 row only

If result set is null we are inserting dummy data and retrieving metadata using simple jdbc like below:

ResultSet rs = stmt1.executeQuery("select * from "+tableName+" fetch first 1 rows only with ur") ;
if(rs == null)
{
    //build a statment like
    insert into schema_a.Tablename select * from schema_b.TableName
}

Is there anyway I can use jdbcTemplate to avoid all above bolierplate code?

I dont want to insert any dummy data.

Here in my case jdbcTemplate is built in runtime based on what environment user selected and we are using weblogic data sources, SchemaawareProxy to set schema.

I can write something like

MyCustomObj obj= jdbcTemplate.queryForObject(sql, new MyRowMapperImpl())

if(obj == null)
{
jdbcTemplate.update(insert into schema_a.Tablename select * from schema_b.TableName fetch first 1 row only");
}

//Then again get resultset or directly get metadata from other schema.

But here I have a risk always "what if other schema doesn't have data?"

I have read this thread

http://forum.springsource.org/showthread.php?t=10812

But In my case I am dealing with DB2 UDB 9.5.

Please help!

[sanumala]

One alternative I can use for this is

for(String tableName: tableNames)
{
   jdbcTemplate.query("describe select * from"+tableName);

}

But when I run this query using like below:

        JdbcTemplate template = new JdbcTemplate(dbds);// This is my datasource
        for(RecalByPassCdwTableEnum e : RecalByPassCdwTableEnum.values())
        {
            List<MetaDataResults> res = template.query("describe select * from myschema."+e.toString(), new MetaDataRowMapper());
            
            System.out.println(res.size());
        }

I am getting following exception

Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [describe select * from myschema.EMPLOYEE]; SQL state [null]; error code [-99999]; executeQuery method cannot be used for update.; nested exception is com.ibm.db2.jcc.b.SqlException: executeQuery method cannot be used for update.
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:406)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:463)
	at Test.main(Test.java:66)
Caused by: com.ibm.db2.jcc.b.SqlException: executeQuery method cannot be used for update.
	at com.ibm.db2.jcc.b.zc.a(zc.java:2387)
	at com.ibm.db2.jcc.b.zc.a(zc.java:1684)
	at com.ibm.db2.jcc.b.zc.a(zc.java:497)
	at com.ibm.db2.jcc.b.zc.executeQuery(zc.java:481)
	at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:440)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
	... 3 more

Please help.

[sanumala]

Can anybody help?

[sanumala]

I found solution. Instead of describe select * we can use sysibm.syscolumns table to get the metadata information.

Here is the same code:

DB2SimpleDataSource dbds=new DB2SimpleDataSource();
        dbds.setDatabaseName("dbname");
        dbds.setDescription("desciption");
        dbds.setUser("username");
        dbds.setPassword("password");
        dbds.setDriverType(4);
        dbds.setServerName("server"); 
        dbds.setPortNumber(12345);
        
        JdbcTemplate template = new JdbcTemplate(dbds);// This is my datasource
        List<MetaDataResults> res = null;
        List<List<MetaDataResults>> finalRes= new ArrayList<List<MetaDataResults>>();
        for(RecalByPassCdwTableEnum e : RecalByPassCdwTableEnum.values())
        {
            String query = "SELECT NAME, COLTYPE, TBNAME FROM SYSIBM.SYSCOLUMNS WHERE TBNAME ='"+e.getCode()+"' AND TBCREATOR='"+dbds.getUser().toUpperCase()+"'";
            System.out.println(query);
            res = template.query(query, new MetaDataRowMapper());
            finalRes.add(res);
        }
        for(List<MetaDataResults> rList: finalRes)
        {
            for(MetaDataResults r: rList)
            {
                System.out.println("Table Name ::: "+r.getTableName()+"   Column Name  ::: "+r.getColName()+"   Column Type ::: "+r.getColType());
            }
            
        }