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.

On Sep 5, 2:02 am, Sigfried <sig.fr...@hotmail.com> 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 a �crit :
> On Sep 5, 2:02 am, Sigfried <sig.fr...@hotmail.com> 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...

On Sep 5, 3:13 am, Sigfried <sig.fr...@hotmail.com> wrote:
> Msj121 a =E9crit :
>
>
>
> > On Sep 5, 2:02 am, Sigfried <sig.fr...@hotmail.com> 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.

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