DatabaseMetaData - getTables and weird data types

Hi,

I am working on a JDBC app that will convert Postgress to Mysql and back. Or
at least do as good a job as possible.

I have a very generic type of conversion working, but I am having trouble
understanding some things I am getting from Postgress.

For example when  I run this code.

DatabaseMetaData inputMetadata_out =  m_outputCon.getMetaData();

        ResultSet outputTypes = inputMetadata_out.getTypeInfo();

        while (outputTypes.next()){

            for (int dd=1;dd<19;dd++){
            System.out.println(dd+"Type info for Postgress  "
+outputTypes.getString(dd));
            }

        }

I get some things that make sense like this:
(line 2 is the Java.lang.sql DATA_TYPE and 3 is precision)

1Type info for Postgress  float8
2Type info for Postgress  8
3Type info for Postgress  9
4Type info for Postgress  null
5Type info for Postgress  null
6Type info for Postgress  null
7Type info for Postgress  0
8Type info for Postgress  f
9Type info for Postgress  3
10Type info for Postgress  f
11Type info for Postgress  f
12Type info for Postgress  f
13Type info for Postgress  null
14Type info for Postgress  null
15Type info for Postgress  null
16Type info for Postgress  null
17Type info for Postgress  null
18Type info for Postgress  10


But then I also get this:

1Type info for Postgress  _float8
2Type info for Postgress  1111
3Type info for Postgress  9
4Type info for Postgress  null
5Type info for Postgress  null
6Type info for Postgress  null
7Type info for Postgress  0
8Type info for Postgress  f
9Type info for Postgress  3
10Type info for Postgress  f
11Type info for Postgress  f
12Type info for Postgress  f
13Type info for Postgress  null
14Type info for Postgress  null
15Type info for Postgress  null
16Type info for Postgress  null
17Type info for Postgress  null
18Type info for Postgress  10

and weirder:

1Type info for Postgress  lztext
2Type info for Postgress  1111
3Type info for Postgress  9
4Type info for Postgress  null
5Type info for Postgress  null
6Type info for Postgress  null
7Type info for Postgress  0
8Type info for Postgress  f
9Type info for Postgress  3
10Type info for Postgress  f
11Type info for Postgress  f
12Type info for Postgress  f
13Type info for Postgress  null
14Type info for Postgress  null
15Type info for Postgress  null
16Type info for Postgress  null
17Type info for Postgress  null
18Type info for Postgress  10

and

1Type info for Postgress  drive
2Type info for Postgress  1111
3Type info for Postgress  9
4Type info for Postgress  null
5Type info for Postgress  null
6Type info for Postgress  null
7Type info for Postgress  0
8Type info for Postgress  f
9Type info for Postgress  3
10Type info for Postgress  f
11Type info for Postgress  f
12Type info for Postgress  f
13Type info for Postgress  null
14Type info for Postgress  null
15Type info for Postgress  null
16Type info for Postgress  null
17Type info for Postgress  null
18Type info for Postgress  10


What are these _ types? For example the Float8 says it is of Java.lang.sql
type 8 and the _Float8 one says it is of type 1111

I am still not sure what these numbers exactly correspond to in Java types.
The Float8 in Postgress Should correspond to a type Real in Java for
example, but I would like to be able
to find that out, from the MetaData without looking up the info in a table.
I am just not sure if this is possible.

If you look at the /jdk1.3/docs/guide/jdbc/spec/jdbc-spec.frame14.html

you will see some SQL to Java.lang.sql mappings. But the getTypeInfo for
DATA_TYPE returns a Short, from what I can see this should correspond to
what Java would treat that type as, rather than as the postgress type name.


I don't know a great deal about postgress which is part of my problem, but
also I am worried about how much I can depend on the Metadata info, I got
some unknown types from Postgress as well.  This topic is not the most
popular. Most examples I see are with hardcoded queries, that look really
gross.


Thanks,

Eric







Frazier Consulting
http://www.kwinternet.com/eric
(250) 655 - 9513