Thread: How to set numeric in PreparedStatement

I want to set a numeric in a prepared statement, but I'm getting a
nasty exception for a simple query. The code is simple, and all I want
to do is query for a numeric type.

Here's the simplified data model:

create table account (
  id  varchar(3) primary key,
  balance  numeric(10,2));

And a code snippet that generates the exception:

  ...
  PreparedStatement pstmt = connectoin.prepareStatement(
    "select id from account where balance > ? and balance < ?";
  pstmt.setDouble( 1, 10.0 );
  pstmt.setDouble( 2, 100.0 );
  ResultSet rs = pstmt.executeQuery();
  ...

And finally, the exception thrown:

Exception in thread "main" java.sql.SQLException: ERROR:  Unable to identify an operator '>' for types 'numeric' and
'float8'
        You will have to retype this query using an explicit cast

        at org.postgresql.Connection.ExecSQL(Connection.java:398)
        at org.postgresql.jdbc2.Statement.execute(Statement.java:273)
        at org.postgresql.jdbc2.Statement.executeQuery(Statement.java:54)
        at org.postgresql.jdbc2.PreparedStatement.executeQuery(PreparedStatement.java:101)
        at SQLTest.main(SQLTest.java:16)


How can I accomplish this? I'm running postgresql 7.0.3 with the jdbc2
driver.

On Wed, 21 Mar 2001, Aaron Brashears wrote:

>   PreparedStatement pstmt = connectoin.prepareStatement(
>     "select id from account where balance > ? and balance < ?";
>   pstmt.setDouble( 1, 10.0 );
>   pstmt.setDouble( 2, 100.0 );
>   ResultSet rs = pstmt.executeQuery();
>   ...
>
> And finally, the exception thrown:
>
> Exception in thread "main" java.sql.SQLException: ERROR:  Unable to identify an operator '>' for types 'numeric' and
'float8'
>         You will have to retype this query using an explicit cast
>


May be this would help?

     "select id from account where balance > ?::numeric(10,2) and
balance < ?::numeric(10,2)";


Juhan Ernits

On Thu, Mar 22, 2001 at 10:12:16AM +0200, Juhan-Peep Ernits wrote:
>
> May be this would help?
>
>      "select id from account where balance > ?::numeric(10,2) and
> balance < ?::numeric(10,2)";
>
>
> Juhan Ernits
>


Perfect! Thank you, it works great.

I've never seen that syntax before, is it part of sql?