Skip site navigation (1) Skip section navigation (2)

Re: queries against CIDR fail against 8.0.3?

From: Kris Jurka <books(at)ejurka(dot)com>
To: Russell Francis <rfrancis(at)ev(dot)net>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: queries against CIDR fail against 8.0.3?
Date: 2005-09-29 17:12:50
Message-ID: Pine.BSO.4.61.0509291206240.25137@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-jdbc

On Thu, 29 Sep 2005, Russell Francis wrote:

> I have a web application which is running against PG 7.3.9 and seems to
> work without a problem.  Recently, I have been trying to run it against
> 8.0.3.  In both cases, I am using the jdbc3-8.0-312 driver.
>
> PreparedStatement s = dbConn.prepareStatement(
> "SELECT * FROM institution WHERE ( institution.network >>=  ? ) LIMIT 1" );
> s.setObject( 1, (String)request.getRemoteAddr() );
> if( s.execute() )
> {
> 	...
> }
>
> DEBUG http-8180-Processor25 net.fitne.vlrc.actions.IndexAction - ERROR:
> operator does not exist: cidr >>= character varying
> net.ev.dao.DAOException: ERROR: operator does not exist: cidr >>=
> character varying
>
> Does anyone have any ideas on how to address this issue?  Or at least an
> explanation as to why it works in 7.3.9 but not 8.0.3?
>

The 8.0 driver has added full V3 protocol support which is not available 
in 7.3 servers, so it falls back to using the V2 protocol when connecting 
to the 7.3 server.  Now, the 8.0 server fails because the V3 protocol uses 
real prepared statements.  When you call setString() you are telling the 
driver that you will be passing a string parameter, so it prepares a 
server side statement taking a string data type.  This is the difference 
between:

V2: WHERE network >>= '10.1.3.1'
V3: WHERE network >>= '10.1.3.1'::varchar

The first treats the parameter as an unknown literal which allows more 
liberal casting while the second has the parameter type somewhat nailed 
down.

The easiest solution is to write your query as "WHERE network >>= ?::cidr" 
to so you get the correct type.

Kris Jurka


In response to

pgsql-jdbc by date

Next:From: Nanu RamDate: 2005-09-29 17:14:29
Subject: Unsubscribe
Previous:From: Kris JurkaDate: 2005-09-29 17:06:13
Subject: Re: How to retieve binary data (bytea) without problem ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group