Announcement Announcement Module
Collapse
No announcement yet.
Table Metadata from DB2 using jdbcTemplate Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Table Metadata from DB2 using jdbcTemplate

    Hi

    I have a List<String> tableNames;

    I wanted to
    Code:
    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

    Code:
    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:

    Code:
    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

    Code:
    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
    HTML Code:
    http://forum.springsource.org/showthread.php?t=10812
    But In my case I am dealing with DB2 UDB 9.5.

    Please help!

  • #2
    One alternative I can use for this is

    Code:
    for(String tableName: tableNames)
    {
       jdbcTemplate.query("describe select * from"+tableName);
    
    }
    But when I run this query using like below:

    Code:
            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

    Code:
    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.
    Last edited by sanumala; Feb 17th, 2011, 04:31 PM.

    Comment


    • #3
      Can anybody help?

      Comment


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

        Here is the same code:

        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());
                    }
                    
                }

        Comment

        Working...
        X