Обсуждение: Exception storing ipaddress with JBoss after upgrade to 8.0-312.jdbc3
Hi, I am trying to store an ipaddress in a PostgreSQL column of a INET type. This used to work with pg74.216.jdbc3 but after an upgrade to postgresql-8.0-312.jdbc3 I get an exception: ERROR: column "ipaddress" is of type inet but expression is of type character varying. The PostgreSQL log shows: LOG: statement: INSERT INTO accesspoint (objectId, ipAddress, locationName, macAddress, manufacturer, serialNumber, supplier, typeName, address) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) 2005-09-27 08:57:48 [12459] ERROR: column "ipaddress" is of type inet but expression is of type character varying HINT: You will need to rewrite or cast the expression. De CMP field is defined as @jboss.jdbc-type VARCHAR and @jboss.sql-type INET Is this a known problem? If so, is there a known sollution (and are you willing to share that sollution ;-))? The suggestion about the rewrite/cast hint is not possible because this is a CMP bean and JBoss takes care of writing that . -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: J.Kraaijeveld@Askesis.nl web: www.askesis.nl
Joost, The new driver uses server side prepared statements and is much more particular about the types being sent. You can force v2 protocol, or create a PGInet type extending PGObject Regards, Dave On 27-Sep-05, at 3:06 AM, Joost Kraaijeveld wrote: > Hi, > > I am trying to store an ipaddress in a PostgreSQL column of a INET > type. > This used to work with pg74.216.jdbc3 but after an upgrade to > postgresql-8.0-312.jdbc3 I get an exception: > > ERROR: column "ipaddress" is of type inet but expression is of type > character varying. > > The PostgreSQL log shows: > > LOG: statement: INSERT INTO accesspoint (objectId, ipAddress, > locationName, macAddress, manufacturer, serialNumber, supplier, > typeName, address) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) > 2005-09-27 08:57:48 [12459] ERROR: column "ipaddress" is of type inet > but expression is of type character varying > HINT: You will need to rewrite or cast the expression. > > De CMP field is defined as @jboss.jdbc-type VARCHAR and @jboss.sql- > type > INET > > Is this a known problem? If so, is there a known sollution (and are > you > willing to share that sollution ;-))? > > The suggestion about the rewrite/cast hint is not possible because > this > is a CMP bean and JBoss takes care of writing that . > > > -- > Groeten, > > Joost Kraaijeveld > Askesis B.V. > Molukkenstraat 14 > 6524NB Nijmegen > tel: 024-3888063 / 06-51855277 > fax: 024-3608416 > e-mail: J.Kraaijeveld@Askesis.nl > web: www.askesis.nl > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >
Hi Dave, On Tue, 2005-09-27 at 09:48 -0400, Dave Cramer wrote: > Joost, > > The new driver uses server side prepared statements and is much more > particular about the types being sent. > > You can force v2 protocol, or create a PGInet type extending PGObject I do not really understand why: the postgresql log file shows that the error is happening at the backend where no Java is running (and I can also not remember that I had to write special code in ODBC: I just send a string with a correct ip address and the database backend converted it to a inet, but that was long ago and I may remember wrong). All examples in the postgresql docs (e.g. example 11-1 setting up a partial index) where the inet type is used an ordinary string is passed to the backend. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: J.Kraaijeveld@Askesis.nl web: www.askesis.nl
Joost Kraaijeveld wrote: > On Tue, 2005-09-27 at 09:48 -0400, Dave Cramer wrote: > >>The new driver uses server side prepared statements and is much more >>particular about the types being sent. >> >>You can force v2 protocol, or create a PGInet type extending PGObject > > I do not really understand why: the postgresql log file shows that the > error is happening at the backend where no Java is running (and I can > also not remember that I had to write special code in ODBC: I just send > a string with a correct ip address and the database backend converted it > to a inet, but that was long ago and I may remember wrong). All examples > in the postgresql docs (e.g. example 11-1 setting up a partial index) > where the inet type is used an ordinary string is passed to the backend. More recent JDBC drivers pass parameters via the extended query protocol which puts the parameter value out-of-band from the query (the query sent to the backend has a $n placeholder where you put the ? in the query, then the parameter values are sent separately). Associated with each parameter is a type. Since you (or rather, JBoss) are telling the JDBC driver that the parameter is a String, it passes the parameter as a text type. There's no implicit cast from text to inet, so the backend complains. It's roughly equivalent to writing out the query with the parameter as '1.2.3.4'::text -- because the type is explicitly specified you don't get the implicit casting of a bare string literal that you'd get otherwise. Or similar to using PREPARE/EXECUTE. Search the pgsql-jdbc archives for more details. -O