iDatamining.org

I am looking for projects to work on
Please contact with me at yiyu.jia@iDataMining.org!

Wednesday, March 16, 2011

buit-in datatype supported by DB2 for i5/OS and MySQL

To complete one of my work, I need to know list of data type supported by RDBMS. So, I wrote a simple Java application to fetch it. Below is java code and output.

Db2MetaData.java using jt400.jar. (This is just a quick work)
package jia.blog.db2;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Yiyu Jia
 */
public class Db2MetaData {
   Connection conn = null;
   DatabaseMetaData dmd = null;

    public Db2MetaData() {
     
    }

  public void openConnection () {

      try {                  
          String url = "jdbc:as400://172.0.0.1"; //change ip address
          DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
          conn = DriverManager.getConnection(url, "accountName", "password");
      }
    catch (Exception e) {
         Logger.getLogger(Db2MetaData.class.getName()).log(Level.SEVERE, null, e);
       }
  }  

 
  public void closeConnection () {

    if (conn != null) {
       try {
           conn.close();           
           System.out.println("Connection closed");
           }
       catch(SQLException e) {
          Logger.getLogger(Db2MetaData.class.getName()).log(Level.SEVERE, null, e);
          }
       }
  }  


    public void displayMaxConnection() {
        try {
            DatabaseMetaData metadata = conn.getMetaData();
            int maxConnection = metadata.getMaxConnections();
            System.out.println("Maximum Connection = " + maxConnection);
        } catch (SQLException ex) {
            Logger.getLogger(Db2MetaData.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public void displayTypeInfo() {
        try {
            DatabaseMetaData metadata = conn.getMetaData();
            ResultSet resultSet = metadata.getTypeInfo();
            while (resultSet.next()) {
                String typeName = resultSet.getString("TYPE_NAME");
                System.out.println(typeName);
            }
            resultSet.close();
        } catch (SQLException ex) {
            Logger.getLogger(Db2MetaData.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

  public static void main(String args[])  {
      Db2MetaData db2Meta = new Db2MetaData();
      db2Meta.openConnection();
      db2Meta.displayMaxConnection();
      db2Meta.displayTypeInfo();
      db2Meta.closeConnection();
  }

}

Supported data type printed by JDBC
DB2 i5 R6V1 MySQL 5.5.8

CHAR
CHAR() FOR BIT DATA
GRAPHIC
LONG VARGRAPHIC
LONG VARCHAR FOR BIT DATA
VARCHAR
VARCHAR() FOR BIT DATA
VARGRAPHIC
DATE
TIMESTAMP
TIME

DECIMAL
DOUBLE
FLOAT
INTEGER
NUMERIC
REAL
SMALLINT
BIGINT
ROWID
DECFLOAT
DATALINK
BLOB
CLOB
DBCLOB

BIT
BOOL
TINYINT
TINYINT UNSIGNED
BIGINT
BIGINT UNSIGNED
LONG VARBINARY
MEDIUMBLOB
LONGBLOB
BLOB
TINYBLOB
VARBINARY
BINARY
LONG VARCHAR
MEDIUMTEXT
LONGTEXT
TEXT
TINYTEXT
CHAR
NUMERIC
DECIMAL
INTEGER
INTEGER UNSIGNED
INT
INT UNSIGNED
MEDIUMINT
MEDIUMINT UNSIGNED
SMALLINT
SMALLINT UNSIGNED
FLOAT
DOUBLE
DOUBLE PRECISION
REAL
VARCHAR
ENUM
SET
DATE
TIME
DATETIME
TIMESTAMP
Below is a picture linked from IBM web site. It listed out built-in data type supported by DB2 for i5/OS RvV4.source
Here are links for DB2 data type to jdbc data type mapping and MySQL data type to JDBC data type mapping

No comments:

Post a Comment