Java class for data type mapper?

[New Java 456]

I'm sure someone has this. Need to map data types from all the various
dbs. I can reverse it out of the Postgresql doc but does anyone at
least have a better doc; for I'd have to look up each sqltype
definition from the vendor jdbc classes. http://www.ispirer.com/wiki/sqlways/postgresql/data-types
We need Java code, not a tool. Maybe this mapping exists inside some
ETL open source tool already?
Thanks in advance for any pointers or suggestions,
TimJowers

 

[John B. Matthews]

I'm sure someone has this. Need to map data types from all the
various dbs. I can reverse it out of the Postgresql doc but does
anyone at least have a better doc; for I'd have to look up each
sqltype definition from the vendor jdbc classes.
http://www.ispirer.com/wiki/sqlways/postgresql/data-types We need
Java code, not a tool. Maybe this mapping exists inside some ETL open
source tool already? Thanks in advance for any pointers or
suggestions,

Typically, the JDBC driver encapsulates the mapping:

<http://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/mapping.html>

"There is considerable variation among the different SQL types
supported by the different databases." See also, "Notes and
Lamentations":

<http://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/mapping.html#table1>

 

[Arne Vajhøj]

I'm sure someone has this. Need to map data types from all the various
dbs. I can reverse it out of the Postgresql doc but does anyone at
least have a better doc; for I'd have to look up each sqltype
definition from the vendor jdbc classes. http://www.ispirer.com/wiki/sqlways/postgresql/data-types
We need Java code, not a tool. Maybe this mapping exists inside some
ETL open source tool already?

If you have the database running then you can so like this:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class GetDBTypes {
public static void test(String driver, String conurl, String un,
String pw) throws ClassNotFoundException, SQLException {
Class.forName(driver);
Connection con = DriverManager.getConnection(conurl, un, pw);
ResultSet rs = con.getMetaData().getTypeInfo();
while(rs.next()) {
System.out.println(rs.getString("TYPE_NAME") + " -> " +
rs.getString("DATA_TYPE"));
}
rs.close();
con.close();
}
public static void main(String[] args) throws Exception {
test("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/Test",
"root", "");
}
}

Arne

PS: The integer written out is java.sql.Types !

 

[Roedy Green]

I'm sure someone has this. Need to map data types from all the various
dbs. I can reverse it out of the Postgresql doc but does anyone at
least have a better doc; for I'd have to look up each sqltype
definition from the vendor jdbc classes. http://www.ispirer.com/wiki/sqlways/postgresql/data-types
We need Java code, not a tool. Maybe this mapping exists inside some
ETL open source tool already?
Thanks in advance for any pointers or suggestions,
TimJowers

Since you are going to access via JDBC, you need to know the JDBC
types
see http://mindprod.com/jgloss/jdbc.html

The catch is you will use the JBDC types and Java types in your
program, but the PostGre types in your the SQL you use to initially
define the database.

Perhaps by experiment you can create a chart combining all three.

Just make a table with one of everything and see how it maps in Java.

I made my own PostGre table, but it is not as good as the one you
already found.

see http://mindprod.com/jgloss/postgresql.html

If you do this, please pass on your findings so I can post a complete
HTML table with six columns PostGre, Bounds, JDBC, Java, Notes.

 

[Lew]

The catch is you will use the JBDC types and Java types in your
program, but the PostGre [sic] types in your the SQL you use to initially
define the database.

It's "Postgres" or "PostreSQL", not "Postgre".

 

[New Java 456]

The catch is you will use the JBDC types and Java types in your
program, but the PostGre [sic] types in your the SQL you use to initially
define the database.

It's "Postgres" or "PostreSQL", not "Postgre".

Thanks Everyone,

What I'm more after is somethign that says a varchar2 in Oracle is a
varchar in Sybase is a character varying in POstgresql. I've worked
with JDBC enough to know the types are represented as integer values
in each driver from the viewpoint of Java so mapping even those to the
data types of the database is tough.

So, some library or class implementing this mapping. As it stands,
I'll try to whack out something using the Postgresql notes. Well, if
you wonder why, the reason to do this is to migrate date from one
database to another adn may possibly include the requirement to create
the destination table. Since the client has very large Oracle
expertise but ate or was eaten by a company who uses Sybase then the
need is to be able to quickly and easily move data between the two for
reporting. They do have some ETL tools but these are also not easy to
integrate into their existing Java progams, amony other things.

Thanks again,
Tim

 

[Roedy Green]

It's "Postgres" or "PostreSQL", not "Postgre".

This is amusing. Your spelling correction contains a spelling error.

It's official name is: PostgreSQL

That is the only name they use on their site.
In the history section, they point out it was originally called
Postgres.

The way I look at it, Postgre is some sort of synthethic adjective
modifying SQL, which is a redundant part of its name when discussing
SQL engines so I feel no guilt calling by a nickname - PostGre.

Many would consider it overly prissy to correct someone for using the
term "Ford" instead of "Henry Ford Company" the company's original
name.

 

[Arne Vajhøj]

This is amusing. Your spelling correction contains a spelling error.

It's official name is: PostgreSQL

That is the only name they use on their site.
In the history section, they point out it was originally called
Postgres.

The way I look at it, Postgre is some sort of synthethic adjective
modifying SQL, which is a redundant part of its name when discussing
SQL engines so I feel no guilt calling by a nickname - PostGre.

Many would consider it overly prissy to correct someone for using the
term "Ford" instead of "Henry Ford Company" the company's original
name.

Sure. But that comparison is not relevant.

Postgres as short name for PostgreSQL is fine.

PostGre is a mysterious abbreviation only used by you.

The analogy would be to call the car company for "For".

Arne