Confusion with DatabaseMetadata.getTypeInfo.

[dmjpro]

I am copying from a Database to another Database.
I am generalizing this program.
Actually to reduce the overhead of Data Type mapping between the source Database and target Database.
What I am doing ..... I am getting the Table column info using DatabaseMetadata.getColumns and there I getting the Column type of java.sql.Types.
Now to map these types with the target Database types I am retrieving the SQL types using DatabaseMetadata.getTypeInfo.
Now my confusion is some constant values are missing with a particular Database(here Postgres)
If my Source Database's Column Type matches with a Constant Value which is missing in my Target Database then what should I be doing?
Should I map this manually?
Please Help!

Debasis Jana

[dmjpro]

I am copying from a Database to another Database.
I am generalizing this program.
Actually to reduce the overhead of Data Type mapping between the source Database and target Database.
What I am doing ..... I am getting the Table column info using DatabaseMetadata.getColumns and there I getting the Column type of java.sql.Types.
Now to map these types with the target Database types I am retrieving the SQL types using DatabaseMetadata.getTypeInfo.
Now my confusion is some constant values are missing with a particular Database(here Postgres)
If my Source Database's Column Type matches with a Constant Value which is missing in my Target Database then what should I be doing?
Should I map this manually?
Please Help!

Debasis Jana

Anybody can help me out of this?

Debasis Jana

[r035198x]

Now my confusion is some constant values are missing with a particular Database(here Postgres)
..

Could you post an example of the missing constant values?

[dmjpro]

Could you post an example of the missing constant values?

Thanks r035198x!
Actually the does vendor take all the constant values and put a SQL Type Name against name?

Right now i don't remember
OK let me check .. I have a code to run then i ll check manually what constant values are missing.

Debasis Jana

[JosAH]

Thanks r035198x!
Actually the does vendor take all the constant values and put a SQL Type Name against name?

Right now i don't remember
OK let me check .. I have a code to run then i ll check manually what constant values are missing.

Debasis Jana

I know the answer already without 'running any code'. Every compliant JDBC
interface has to supply a complete Type interface. That doesn't imply that all
those are actually implemented by the database engine. That's where the
DatabaseMetaData comes in.

I'm afraid this turns out to be 'one of those questions' ...

kind regards,

Jos

[dmjpro]

I know the answer already without 'running any code'. Every compliant JDBC
interface has to supply a complete Type interface. That doesn't imply that all
those are actually implemented by the database engine. That's where the
DatabaseMetaData comes in.

I'm afraid this turns out to be 'one of those questions' ...

kind regards,

Jos

hahahahahaah!
yeah my behaviour maid you make a such comment!
Actually i run this code.
What you think that also I tried but I got surprised some constant values are missing.
Neways I am now ...trying to run this code.

Debasis Jana.

[dmjpro]

I know the answer already without 'running any code'. Every compliant JDBC
interface has to supply a complete Type interface. That doesn't imply that all
those are actually implemented by the database engine. That's where the
DatabaseMetaData comes in.

I'm afraid this turns out to be 'one of those questions' ...

kind regards,

Jos

Josh here is my Code.

ResultSet data_types = db_metadata.getTypeInfo();
        while(data_types.next()){
            int type = data_types.getInt("DATA_TYPE");
            String type_name = data_types.getString("TYPE_NAME");
 
            System.out.println("Type: " + type + "\tType Name: " + type_name);
        }
 

and the missing constants are ....

BLOB 2004
BOOLEAN 16
CLOB 2005
DECIMAL 3

Now I didn't check all the constants.
But If i get any missing then what should i be doing?
What I am trying to Josh to map a source SQL Type to a target SQL type.
Source and target database may not be same.

Debasis Jana.

[dmjpro]

See this link guys.

postgressql bugs
Now one more problem ....
I am now converting from Sybase to Postgres.
Now suppose Sybase implements java.sql.Types.XXX of size 10 but postgres may not implement this type of size more than 10.

Actually what my code does first i am extracting the column types as java.sql.Types.XXX and the column size
Now to convert it into the target database i am calling this method

/**
     * This method returns  a Database specific column type for a java.sql.Types.XXXX.
     * This also checks that whether the found datatype supports the size given by the source.
     * @param sourceType The java.sql.Types.XXX value.
     * @param sourceSize The source database cloumn size.
     * @param targetConn The target connection for which the column name to be obtained.
     * @return It returns column name for java.sql.Types.XXXX.
     * If not found for java.sql.Types.XXX then returns null;
     * @exception It throws SQLException if any database operation fails.
     */
public String getColumnType(int sourceType,int sourceSize,Connection targetConn) throws SQLException{
        System.out.println("Source Type: " + sourceType + "\tSource Size: " + sourceSize);
        DatabaseMetaData db_metadadta = targetConn.getMetaData();
        ResultSet rs_columns = db_metadadta.getTypeInfo();
        while(rs_columns.next()){
            int type = rs_columns.getInt("DATA_TYPE");
            String name = rs_columns.getString("TYPE_NAME");
            short min_scale = rs_columns.getShort("MINIMUM_SCALE"),
                  max_scale = rs_columns.getShort("MAXIMUM_SCALE");
            if(sourceType==type){
                System.out.println("Column Type Name: " + name + "\tMinimum Scale: " + min_scale + "\tMaximum Sclae: " + max_scale);
                if(sourceSize>=min_scale && sourceSize<=max_scale){
                    rs_columns.getStatement().close();
                    return name;
                }
            }
        }
        rs_columns.getStatement().close();
        return null;
    }
 

Hope all the experts can get my point.

Debasis Jana.

[dmjpro]

See this link guys.

postgressql bugs
Now one more problem ....
I am now converting from Sybase to Postgres.
Now suppose Sybase implements java.sql.Types.XXX of size 10 but postgres may not implement this type of size more than 10.

Actually what my code does first i am extracting the column types as java.sql.Types.XXX and the column size
Now to convert it into the target database i am calling this method

/**
     * This method returns  a Database specific column type for a java.sql.Types.XXXX.
     * This also checks that whether the found datatype supports the size given by the source.
     * @param sourceType The java.sql.Types.XXX value.
     * @param sourceSize The source database cloumn size.
     * @param targetConn The target connection for which the column name to be obtained.
     * @return It returns column name for java.sql.Types.XXXX.
     * If not found for java.sql.Types.XXX then returns null;
     * @exception It throws SQLException if any database operation fails.
     */
public String getColumnType(int sourceType,int sourceSize,Connection targetConn) throws SQLException{
        System.out.println("Source Type: " + sourceType + "\tSource Size: " + sourceSize);
        DatabaseMetaData db_metadadta = targetConn.getMetaData();
        ResultSet rs_columns = db_metadadta.getTypeInfo();
        while(rs_columns.next()){
            int type = rs_columns.getInt("DATA_TYPE");
            String name = rs_columns.getString("TYPE_NAME");
            short min_scale = rs_columns.getShort("MINIMUM_SCALE"),
                  max_scale = rs_columns.getShort("MAXIMUM_SCALE");
            if(sourceType==type){
                System.out.println("Column Type Name: " + name + "\tMinimum Scale: " + min_scale + "\tMaximum Sclae: " + max_scale);
                if(sourceSize>=min_scale && sourceSize<=max_scale){
                    rs_columns.getStatement().close();
                    return name;
                }
            }
        }
        rs_columns.getStatement().close();
        return null;
    }
 

Hope all the experts can get my point.

Debasis Jana.

Guys I solved that problem. What I am doing for those missing values I am doing it hard-code. One more thing which has minimum-scale and maximum scale zero that can't be of any size.

Debasis Jana