Why JDBC cannot be used to select column types for all SQL server

Discussion in 'Java' started by Sigfried, Sep 5, 2008.

  1. Sigfried

    Sigfried Guest

    I've spent 2 days to find out that the JDBC MetaData getTypeInfo() is
    not enough to choose:
    1/ which java.sql.Types.XXX : to use
    2/ which corresponding column type to choose (in create table sql)

    I ended up testing the url string, for each vendor i tested (assuming
    the last version for the server), having hard coded values.
     
    Sigfried, Sep 5, 2008
    #1
    1. Advertising

  2. Sigfried

    Msj121 Guest

    On Sep 5, 2:02 am, Sigfried <> wrote:
    > I've spent 2 days to find out that the JDBC MetaData getTypeInfo() is
    > not enough to choose:
    > 1/ which java.sql.Types.XXX : to use
    > 2/ which corresponding column type to choose (in create table sql)
    >
    > I ended up testing the url string, for each vendor i tested (assuming
    > the last version for the server), having hard coded values.


    The question seems difficult to understand. In regard to "types" is
    this because you want to be able to read in data from any table using
    one class and do this dynamically? So one class to read any table and
    put it into any object? Though I would suggest using JEE 5 (or
    Hibernate) you could scrounge together a simple solution of looking at
    the object your making and assume the columns match the data type (if
    not then there is a mistake in object code or database). I could only
    suggest this for a small project though where you have a handle on
    everything. I think Hibernate or JEE 5 EJBs would be better.

    Also, when creating SQL tables - what do you mean you don't know the
    corresponding column type. Does this mean based on the object you
    don't know how to dynamically create a table for that object type? I
    still suggest EJBs and Hibernate for this, but a translator used in
    the original idea could be implemented here as well. It shouldn't be
    overly complicated as there aren't that many object types.

    Perhaps I misunderstood the problem.

    MSJ121
     
    Msj121, Sep 5, 2008
    #2
    1. Advertising

  3. Sigfried

    Sigfried Guest

    Msj121 a écrit :
    > On Sep 5, 2:02 am, Sigfried <> wrote:
    >> I've spent 2 days to find out that the JDBC MetaData getTypeInfo() is
    >> not enough to choose:
    >> 1/ which java.sql.Types.XXX : to use
    >> 2/ which corresponding column type to choose (in create table sql)
    >>
    >> I ended up testing the url string, for each vendor i tested (assuming
    >> the last version for the server), having hard coded values.

    >
    > The question seems difficult to understand. In regard to "types" is
    > this because you want to be able to read in data from any table using
    > one class and do this dynamically? So one class to read any table and
    > put it into any object? Though I would suggest using JEE 5 (or
    > Hibernate) you could scrounge together a simple solution of looking at
    > the object your making and assume the columns match the data type (if
    > not then there is a mistake in object code or database). I could only
    > suggest this for a small project though where you have a handle on
    > everything. I think Hibernate or JEE 5 EJBs would be better.
    >
    > Also, when creating SQL tables - what do you mean you don't know the
    > corresponding column type. Does this mean based on the object you
    > don't know how to dynamically create a table for that object type? I
    > still suggest EJBs and Hibernate for this, but a translator used in
    > the original idea could be implemented here as well. It shouldn't be
    > overly complicated as there aren't that many object types.
    >
    > Perhaps I misunderstood the problem.


    Without using heavy tools like Hibernate, i wanted to export some data
    to "any SQL server". So if i have text data of fixed size, using only
    JDBC, i should use java.sql.DatabaseMetaData.getTypeInfo(), and keep the
    first typename corresponding to java.sql.Types.VARCHAR. Then i will do:

    create table (
    ....
    columnName typename(maxSize),
    ....
    )

    But the same code didn't work for mysql and postgresql.

    I would need this method in jdbc:

    /**
    * @param maxSize if > 0, strings will be of maximum length maxSize
    */
    getLimitedTextColumnDataType(int maxSize);

    This method would return "varchar(200)" without having to read the SQL
    server documentation... What is called a generic way i assume.

    And i won't talk about charset encoding...
     
    Sigfried, Sep 5, 2008
    #3
  4. Sigfried

    Msj121 Guest

    On Sep 5, 3:13 am, Sigfried <> wrote:
    > Msj121 a écrit :
    >
    >
    >
    > > On Sep 5, 2:02 am, Sigfried <> wrote:
    > >> I've spent 2 days to find out that the JDBC MetaData getTypeInfo() is
    > >> not enough to choose:
    > >> 1/ which java.sql.Types.XXX : to use
    > >> 2/ which corresponding column type to choose (in create table sql)

    >
    > >> I ended up testing the url string, for each vendor i tested (assuming
    > >> the last version for the server), having hard coded values.

    >
    > > The question seems difficult to understand. In regard to "types" is
    > > this because you want to be able to read in data from any table using
    > > one class and do this dynamically? So one class to read any table and
    > > put it into any object? Though I would suggest using JEE 5 (or
    > > Hibernate) you could scrounge together a simple solution of looking at
    > > the object your making and assume the columns match the data type (if
    > > not then there is a mistake in object code or database). I could only
    > > suggest this for a small project though where you have a handle on
    > > everything. I think Hibernate or JEE 5 EJBs would be better.

    >
    > > Also, when creating SQL tables - what do you mean you don't know the
    > > corresponding column type. Does this mean based on the object you
    > > don't know how to dynamically create a table for that object type? I
    > > still suggest EJBs and Hibernate for this, but a translator used in
    > > the original idea could be implemented here as well. It shouldn't be
    > > overly complicated as there aren't that many object types.

    >
    > > Perhaps I misunderstood the problem.

    >
    > Without using heavy tools like Hibernate, i wanted to export some data
    > to "any SQL server". So if i have text data of fixed size, using only
    > JDBC, i should use java.sql.DatabaseMetaData.getTypeInfo(), and keep the
    > first typename corresponding to java.sql.Types.VARCHAR. Then i will do:
    >
    > create table (
    > ...
    > columnName typename(maxSize),
    > ...
    > )
    >
    > But the same code didn't work for mysql and postgresql.
    >
    > I would need this method in jdbc:
    >
    > /**
    > * @param maxSize if > 0, strings will be of maximum length maxSize
    > */
    > getLimitedTextColumnDataType(int maxSize);
    >
    > This method would return "varchar(200)" without having to read the SQL
    > server documentation... What is called a generic way i assume.
    >
    > And i won't talk about charset encoding...


    I think I have a better idea as to what you mean now. I have never
    attempted to do that kind of thing from scratch (except where I knew a
    few different databases would be used and extended based off of them),
    but I have seen Hibernate with some different XML connection types
    allowing interfacing with the same code to different databases... but
    you did say you didn't want Hibernate.

    Certainly an interesting idea, but I don't think it will be an easy
    solution without using some framework like Hibernate.
     
    Msj121, Sep 5, 2008
    #4
  5. Sigfried

    Arne Vajhøj Guest

    Sigfried wrote:
    > Without using heavy tools like Hibernate, i wanted to export some data
    > to "any SQL server". So if i have text data of fixed size, using only
    > JDBC, i should use java.sql.DatabaseMetaData.getTypeInfo(), and keep the
    > first typename corresponding to java.sql.Types.VARCHAR. Then i will do:
    >
    > create table (
    > ...
    > columnName typename(maxSize),
    > ...
    > )
    >
    > But the same code didn't work for mysql and postgresql.
    >
    > I would need this method in jdbc:
    >
    > /**
    > * @param maxSize if > 0, strings will be of maximum length maxSize
    > */
    > getLimitedTextColumnDataType(int maxSize);
    >
    > This method would return "varchar(200)" without having to read the SQL
    > server documentation... What is called a generic way i assume.


    But getTypeInfo also return the max length of the type, so you can
    loathsomely optimize getLimitedTextColumnDataType yourself.

    Jay


    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    "Freemasonry has a religious service to commit the body of a deceased
    brother to the dust whence it came, and to speed the liberated spirit
    back to the Great Source of Light. Many Freemasons make this flight
    with *no other guarantee of a safe landing than their belief in the
    religion of Freemasonry*"
     
    Arne Vajhøj, Sep 5, 2008
    #5
  6. Sigfried

    Arne Vajhøj Guest

    Sigfried wrote:
    > Without using heavy tools like Hibernate, i wanted to export some data
    > to "any SQL server". So if i have text data of fixed size, using only
    > JDBC, i should use java.sql.DatabaseMetaData.getTypeInfo(), and keep the
    > first typename corresponding to java.sql.Types.VARCHAR. Then i will do:
    >
    > create table (
    > ...
    > columnName typename(maxSize),
    > ...
    > )
    >
    > But the same code didn't work for mysql and postgresql.
    >
    > I would need this method in jdbc:
    >
    > /**
    > * @param maxSize if > 0, strings will be of maximum length maxSize
    > */
    > getLimitedTextColumnDataType(int maxSize);
    >
    > This method would return "varchar(200)" without having to read the SQL
    > server documentation... What is called a generic way i assume.


    But getTypeInfo also return the max length of the type, so you can
    easily implement getLimitedTextColumnDataType yourself.

    Arne
     
    Arne Vajhøj, Sep 6, 2008
    #6
  7. Sigfried

    Lew Guest

    Sigfried wrote:
    > [Quelqu'un qui n'est pas] Arne Vajh??j a ??crit :
    >> But getTypeInfo also return the max length of the type, so you can
    >> loathsomely optimize getLimitedTextColumnDataType yourself.


    Please do not quote the swindler. Quote what Nicole consciously did write.

    What he certainly wrote:
    > But getTypeInfo also return the max length of the type, so you can
    > easily implement getLimitedTextColumnDataType yourself.


    --
    Lew



    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    "Here in the United States, the Zionists and their co-religionists
    have complete control of our government.

    For many reasons, too many and too complex to go into here at this
    time, the Zionists and their co-religionists rule these
    United States as though they were the absolute monarchs
    of this country.

    Now you may say that is a very broad statement,
    but let me show you what happened while we were all asleep..."

    --- Benjamin H. Freedman

    [Benjamin H. Freedman was one of the most intriguing and amazing
    individuals of the 20th century. Born in 1890, he was a successful
    Jewish businessman of New York City at one time principal owner
    of the Woodbury Soap Company. He broke with organized Jewry
    after the Judeo-Communist victory of 1945, and spent the
    remainder of his life and the great preponderance of his
    considerable fortune, at least 2.5 million dollars, exposing the
    Jewish tyranny which has enveloped the United States.]
     
    Lew, Sep 8, 2008
    #7
  8. Sigfried

    Lew Guest

    Sigfried wrote:
    > [Quelqu'un qui n'est pas] Arne Vajh??j a ??crit :
    >> But getTypeInfo also return the max length of the type, so you can
    >> loathsomely optimize getLimitedTextColumnDataType yourself.


    Please do not quote the swindler. Quote what Nicole consciously did write.

    What he certainly wrote:
    > But getTypeInfo also return the max length of the type, so you can
    > easily implement getLimitedTextColumnDataType yourself.


    --
    Lew



    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    "Here in the United States, the Zionists and their co-religionists
    have complete control of our government.

    For many reasons, too many and too complex to go into here at this
    time, the Zionists and their co-religionists rule these
    United States as though they were the absolute monarchs
    of this country.

    Now you may say that is a very broad statement,
    but let me show you what happened while we were all asleep..."

    --- Benjamin H. Freedman

    [Benjamin H. Freedman was one of the most intriguing and amazing
    individuals of the 20th century. Born in 1890, he was a successful
    Jewish businessman of New York City at one time principal owner
    of the Woodbury Soap Company. He broke with organized Jewry
    after the Judeo-Communist victory of 1945, and spent the
    remainder of his life and the great preponderance of his
    considerable fortune, at least 2.5 million dollars, exposing the
    Jewish tyranny which has enveloped the United States.]
     
    Lew, Sep 8, 2008
    #8
  9. Sigfried

    Sigfried Guest

    Arne Vajhøj a écrit :
    > Sigfried wrote:
    >> Without using heavy tools like Hibernate, i wanted to export some data
    >> to "any SQL server". So if i have text data of fixed size, using only
    >> JDBC, i should use java.sql.DatabaseMetaData.getTypeInfo(), and keep the
    >> first typename corresponding to java.sql.Types.VARCHAR. Then i will do:
    >>
    >> create table (
    >> ...
    >> columnName typename(maxSize),
    >> ...
    >> )
    >>
    >> But the same code didn't work for mysql and postgresql.
    >>
    >> I would need this method in jdbc:
    >>
    >> /**
    >> * @param maxSize if > 0, strings will be of maximum length maxSize
    >> */
    >> getLimitedTextColumnDataType(int maxSize);
    >>
    >> This method would return "varchar(200)" without having to read the SQL
    >> server documentation... What is called a generic way i assume.

    >
    > But getTypeInfo also return the max length of the type, so you can
    > loathsomely optimize getLimitedTextColumnDataType yourself.


    The max length ? Where ? I only saw "create param" and it's more a
    grammar description like [(M|A)] for example. Anyway, postgresql returns
    null for this field.
     
    Sigfried, Sep 8, 2008
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Keith Wansbrough

    Re: jdbc help:sun.jdbc.odbc.JdbcOdbcDriver

    Keith Wansbrough, Aug 16, 2004, in forum: Java
    Replies:
    0
    Views:
    801
    Keith Wansbrough
    Aug 16, 2004
  2. Mr. SweatyFinger
    Replies:
    2
    Views:
    2,215
    Smokey Grindel
    Dec 2, 2006
  3. Lew
    Replies:
    0
    Views:
    480
  4. loial
    Replies:
    0
    Views:
    561
    loial
    Nov 17, 2010
  5. Matt
    Replies:
    2
    Views:
    228
    Roland Hall
    Jan 11, 2004
Loading...

Share This Page