Thread: setString and timestamps
I understand from looking at the archive that there some changes around 8.0 in this area, but I am confused as to how to proceed. I have some code which takes a table and inserts values into it where the values have come from XML and are thus all strings. Some of these XML fields are timestamps, but this generic code does not have table specific knowledge. From what I have read I think I have to use something like the metadata for this table to find the type of this column, and if it is a timestamp then I need to take the string and convert it into a timestamp before I set it into the parameterized insert. Is this right, and are there any other datatypes which are not implicitly converted from String to whatever is necessary? If it is only timestamps, why are they singled out for this special treatment? Also there was some question about the difference between timestamps with and without zone, mine are without should that make a difference. Is there a HOWTO about this somewhere? David
On Fri, 17 Feb 2006, David Goodenough wrote:
> I understand from looking at the archive that there some changes around
> 8.0 in this area, but I am confused as to how to proceed.
>
> I have some code which takes a table and inserts values into it where the
> values have come from XML and are thus all strings. Some of these XML
> fields are timestamps, but this generic code does not have table specific
> knowledge.
>
> From what I have read I think I have to use something like the metadata
> for this table to find the type of this column, and if it is a timestamp then
> I need to take the string and convert it into a timestamp before I set it
> into the parameterized insert. Is this right, and are there any other
> datatypes which are not implicitly converted from String to whatever is
> necessary?
In the 8.0 and 8.1 driver all types have strong typing information. You
can see this with integers as well. The driver will do the equivalent of:
jurka=# create table inttest(a int);
CREATE TABLE
jurka=# insert into inttest values('1'::varchar);
ERROR: column "a" is of type integer but expression is of type character
varying
HINT: You will need to rewrite or cast the expression.
If you really have only string data and no typing information your best
bet is probably to use an 8.2dev driver and set the stringtype=unspecified
URL parameter to restore the old behavior of automatic type
interpretation.
http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
Kris Jurka
On Friday 17 February 2006 18:33, Kris Jurka wrote:
> On Fri, 17 Feb 2006, David Goodenough wrote:
> > I understand from looking at the archive that there some changes around
> > 8.0 in this area, but I am confused as to how to proceed.
> >
> > I have some code which takes a table and inserts values into it where the
> > values have come from XML and are thus all strings. Some of these XML
> > fields are timestamps, but this generic code does not have table specific
> > knowledge.
> >
> > From what I have read I think I have to use something like the metadata
> > for this table to find the type of this column, and if it is a timestamp
> > then I need to take the string and convert it into a timestamp before I
> > set it into the parameterized insert. Is this right, and are there any
> > other datatypes which are not implicitly converted from String to
> > whatever is necessary?
>
> In the 8.0 and 8.1 driver all types have strong typing information. You
> can see this with integers as well. The driver will do the equivalent of:
>
> jurka=# create table inttest(a int);
> CREATE TABLE
> jurka=# insert into inttest values('1'::varchar);
> ERROR: column "a" is of type integer but expression is of type character
> varying
> HINT: You will need to rewrite or cast the expression.
>
> If you really have only string data and no typing information your best
> bet is probably to use an 8.2dev driver and set the stringtype=unspecified
> URL parameter to restore the old behavior of automatic type
> interpretation.
>
> http://jdbc.postgresql.org/documentation/head/connect.html#connection-param
>eters
>
> Kris Jurka
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
What is the best way of finding the column types in generic code. That is to
say if I have a method with no knowledge of a specific table and I am about
to do an insert and I wish to get the parameters in the correct format what
APIs exist that allow me to find out the type of a named column. I have
looked through the JDBC docs without success so far, but it may be that
I am looking for the wrong thing. If I need to use a Postgresql only API
that is fine.
Thanks
David
On Mon, 20 Feb 2006, David Goodenough wrote: > What is the best way of finding the column types in generic code. That is to > say if I have a method with no knowledge of a specific table and I am about > to do an insert and I wish to get the parameters in the correct format what > APIs exist that allow me to find out the type of a named column. I have > looked through the JDBC docs without success so far, but it may be that > I am looking for the wrong thing. If I need to use a Postgresql only API > that is fine. > DatabaseMetaData.getColumns or PreparedStatement.getParameterMetaData are probably what you're looking for. Kris Jurka
On Monday 20 February 2006 17:42, Kris Jurka wrote: > On Mon, 20 Feb 2006, David Goodenough wrote: > > What is the best way of finding the column types in generic code. That > > is to say if I have a method with no knowledge of a specific table and I > > am about to do an insert and I wish to get the parameters in the correct > > format what APIs exist that allow me to find out the type of a named > > column. I have looked through the JDBC docs without success so far, but > > it may be that I am looking for the wrong thing. If I need to use a > > Postgresql only API that is fine. > > DatabaseMetaData.getColumns or PreparedStatement.getParameterMetaData are > probably what you're looking for. > > Kris Jurka PreparedStatement.getParameterMetaData was the one I wanted. My copy of Java Enterprise in a Nutshell is too old, I must get a new one. Thank you David