Input parameter type does not match output when using DB.performQuery

132 views
Skip to first unread message

Mike Starov

unread,
May 11, 2011, 12:13:00 PM5/11/11
to Lift
In my Postgres DB I have a simple function that takes an INTEGER
argument and returns the same INTEGER. Then I perform a query on it
using DB object.

r = DB.performQuery("SELECT * FROM function1(?)", List(3))
r: (List[String], List[List[Any]]) = (List(function1),List(List(3)))

which is successful, but when I try using Long as an argument I get an
error

r = DB.performQuery("SELECT * FROM function1(?)", List(3L))
org.postgresql.util.PSQLException: ERROR: function function1(bigint)
does not exist
Hint: No function matches the given name and argument types. You might
need to add explicit type casts.
Position: 15
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:
2077)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:
1810)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
257)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:
498)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:
386)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:
271)
at net.liftweb.db.DB$$anonfun$performQuery$1$...

So far so good. What bothers me is the return value of the query. I
cant seem to use it as an Int. When I try following code it fails

(r._2(0)(0)).asInstanceOf[Int]
java.lang.ClassCastException: java.lang.Long cannot be cast to
java.lang.Integer
at scala.runtime.BoxesRunTime.unboxToInt(Unknown Source)
at .<init>(<console>:9)
at .<clinit>(<console>)
at RequestResult$.<init>(<console>:9)
at RequestResult$.<clinit>(<console>)
at RequestResult$scala_repl_result(<console>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
57)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
43)
at java.lang.reflect.Method.invoke(Method.java:616)
at scala.tools.nsc.Interpreter$Request$$anonfun$loadAndRun$1$$anonfun
$apply$17.apply(Interpreter.scala:988)
at scala.tools.nsc.Interpreter$Request$$anonfun$loadAndRun$1$$anonfun
$apply$17.apply...

However if I try to cast the result as Long it is successful.

(r._2(0)(0)).asInstanceOf[Long]
res28: Long = 3

Is this a designed behavior or a bug? You have to supply an Int but
expect a Long. Are there other rules like that for other types. Where
would I find documentation on this?

How do I convert that value back to Int?

Thank you.



David Pollak

unread,
May 11, 2011, 12:29:09 PM5/11/11
to lif...@googlegroups.com
The conversion from the ResultSet to each column type is done here:

  private def asAny(pos: Int, rs: ResultSet, md: ResultSetMetaData): Any = {
    import java.sql.Types._
    md.getColumnType(pos) match {
      case ARRAY | BINARY | BLOB | DATALINK | DISTINCT | JAVA_OBJECT | LONGVARBINARY | NULL | OTHER | REF | STRUCT | VARBINARY => rs.getObject(pos)

      case DECIMAL | NUMERIC => rs.getBigDecimal(pos)

      case BIGINT | INTEGER | /* DECIMAL | NUMERIC | */ SMALLINT | TINYINT => rs.getLong(pos)

      case BIT | BOOLEAN => rs.getBoolean(pos)

      case VARCHAR | CHAR | CLOB | LONGVARCHAR => rs.getString(pos)

      case DATE | TIME | TIMESTAMP => rs.getTimestamp(pos)

      case DOUBLE | FLOAT | REAL => rs.getDouble(pos)
    }
  }

So all Integerish numbers are treated as Long.

Two things I'd suggest:
  1. Use pattern matching to extract values from Any rather than using explicit casts (if you need an example, please let me know)
  2. Match expected numbers to java.lang.Number and then do a longValue or intValue to get the type you want
Hope this helps.




--
You received this message because you are subscribed to the Google Groups "Lift" group.
To post to this group, send email to lif...@googlegroups.com.
To unsubscribe from this group, send email to liftweb+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/liftweb?hl=en.




--
Lift, the simply functional web framework http://liftweb.net

Mike Starov

unread,
May 11, 2011, 1:05:40 PM5/11/11
to Lift
Thank you. Here is what I got

(r._2(0)(0)) match
{
case a: Number if a.longValue <= Integer.MAX_VALUE && a.longValue
>= Integer.MIN_VALUE => a.intValue
case _ => a.longValue
}

or I can do a simple cast (r._2(0)(0)).asInstanceOf[Number].intValue
since in this case I know what the return value of function should be.

If you were thinking of something else then I'd love to see your
example.



On May 11, 9:29 am, David Pollak <feeder.of.the.be...@gmail.com>
wrote:
> The conversion from the ResultSet to each column type is done here:
>
>   private def asAny(pos: Int, rs: ResultSet, md: ResultSetMetaData): Any = {
>     import java.sql.Types._
>     md.getColumnType(pos) match {
>       case ARRAY | BINARY | BLOB | DATALINK | DISTINCT | JAVA_OBJECT |
> LONGVARBINARY | NULL | OTHER | REF | STRUCT | VARBINARY => rs.getObject(pos)
>
>       case DECIMAL | NUMERIC => rs.getBigDecimal(pos)
>
>       case BIGINT | INTEGER | /* DECIMAL | NUMERIC | */ SMALLINT | TINYINT
> => rs.getLong(pos)
>
>       case BIT | BOOLEAN => rs.getBoolean(pos)
>
>       case VARCHAR | CHAR | CLOB | LONGVARCHAR => rs.getString(pos)
>
>       case DATE | TIME | TIMESTAMP => rs.getTimestamp(pos)
>
>       case DOUBLE | FLOAT | REAL => rs.getDouble(pos)
>     }
>   }
>
> So all Integerish numbers are treated as Long.
>
> Two things I'd suggest:
>
>    1. Use pattern matching to extract values from Any rather than using
>    explicit casts (if you need an example, please let me know)
>    2. Match expected numbers to java.lang.Number and then do a longValue or
> Simply Lifthttp://simply.liftweb.net

David Pollak

unread,
May 11, 2011, 1:30:08 PM5/11/11
to lif...@googlegroups.com
On Wed, May 11, 2011 at 10:05 AM, Mike Starov <mikes...@gmail.com> wrote:
Thank you. Here is what I got

(r._2(0)(0)) match
{
  case a: Number if a.longValue <= Integer.MAX_VALUE && a.longValue
>= Integer.MIN_VALUE => a.intValue
  case _ => a.longValue
}

or I can do a simple cast (r._2(0)(0)).asInstanceOf[Number].intValue
since in this case I know what the return value of function should be.

If you were thinking of something else then I'd love to see your
example.

That about does it.

There hasn't been much attention paid to performQuery... if you've got a better way to extract values from the query (perhaps an implicit parameter or something), please feel encouraged to make suggestions.
 



--
Lift, the simply functional web framework http://liftweb.net
Reply all
Reply to author
Forward
0 new messages