Thread: jdbc and automagic casting
Hello,
I have an INSERT which does not behave the same under jdbc and psql.
With psql, INSERTing a poperly formed VARCHAR into an inet column
would work, eg.:
=# \d user_record;
Table "user_record"
Column | Type | Modifiers
----------------------+------------------------+-----------
id | integer |
username | character varying(64) |
registered_ip | inet |
INSERT INTO user_record (0, 'abc', '127.0.0.1') is valid and works.
The string is automagically casted as an inet value.
Using jdbc, this is not the case, and causes me loads of trouble (and
exceptions).
Is there a way around it?
I do not have access to the java source code, there is thus no way to
update the code to programatically force a CAST.
Thanks for any help,
Guillaume
On 09/19/2011 10:39 AM, Guillaume wrote: > Hello, > > I have an INSERT which does not behave the same under jdbc and psql. > > With psql, INSERTing a poperly formed VARCHAR into an inet column > would work, eg.: > > =# \d user_record; > Table "user_record" > Column | Type | Modifiers > ----------------------+------------------------+----------- > id | integer | > username | character varying(64) | > registered_ip | inet | > > INSERT INTO user_record (0, 'abc', '127.0.0.1') is valid and works. > The string is automagically casted as an inet value. > > Using jdbc, this is not the case, and causes me loads of trouble (and > exceptions). > Is there a way around it? > > I do not have access to the java source code, there is thus no way to > update the code to programatically force a CAST. do you know if they used preparedstatement or build dynamic sql? for now , I'm not sure if it would make a difference in this case.. > > Thanks for any help, > Guillaume > >
On Mon, 19 Sep 2011, Guillaume wrote: > INSERT INTO user_record (0, 'abc', '127.0.0.1') is valid and works. > The string is automagically casted as an inet value. > > Using jdbc, this is not the case, and causes me loads of trouble (and > exceptions). > Is there a way around it? If you are using setString with a parameter that's not a string, that's not correct. You should use instead setObject(x, y, Types.OTHER). > I do not have access to the java source code, there is thus no way to > update the code to programatically force a CAST. Without access to the source code you can use the connection url parameter stringtype=unspecified. http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters Kris Jurka
Hi,
Thanks to both of you, it helped me find a solution out of this.
I ran a few tests. Basically this INSERT:
INSERT INTO ip_list VALUES ('127.0.0.1')
in 4 different cases, in a small standalone java snippet to understand
what's going on:
- prepared statement (with setString) and default stringtype
- prepared statement (with setString) and stringtype=unspecified
- dynamic sql and default stringtype
- dynamic and stringtype=unspecified
Out of those, both prepared statements failed, but both dynamic sql
worked as expected.
It so happens that setString() in a prepared statement sends a varchar
to postgres, and postgres has no way to convert a varchar to an inet
type (8.4).
This can be confirmed by this in psql:
INSERT INTO dsl.ip_list VALUES (CAST('127.0.0.1' AS CHARACTER
VARYING));
ERROR: column "ip" is of type inet but expression is of type
character varying
LINE 1: ....ip_list VALUES (CAST('127....
I am not sure of the internal conversion done in the usual case INSERT
INTO dsl.ip_list VALUES ('127.0.0.1' );
Anyway, to get out of this, I 'just' had to create a new CAST:
CREATE CAST (CHARACTER VARYING AS inet) WITH INOUT AS ASSIGNMENT;
Now varchars are properly converted to inet on the postgres side, so
it all works for me.
I find it a bit surprising that postgres does not know how to convert
from varchar to inet implicitly (although the inet() operator does
exist), but there is at least a solution.
Thanks for your help,
Guillaume
On Tue, Sep 20, 2011 at 2:18 AM, Guillaume <lomig42@gmail.com> wrote: > I find it a bit surprising that postgres does not know how to convert > from varchar to inet implicitly (although the inet() operator does > exist), but there is at least a solution. I would say from Pg 8.3 and on, this is not a bug but an intentional behavior. http://www.postgresql.org/docs/8.3/static/release-8-3.html#AEN87384 -- Regards, Richard Broersma Jr.