Unknown data type thrown with a case statement where all values are parameters in preparedStatement

[Maxime Lévesque]

I have this statement that throws an exception with this message : Unknown data type: "?, ?"; SQL statement:

    val ps =
      connection.prepareStatement(
        "Select " +
        "  (case" +
        "    when (a.streetname = ?) then ?" +
        "    when (a.streetname = ?) then ?" +
        "    else ?" +
        "    end) as v3 " +
        "From" +
        "  address a")

    ps.setString(1,"z")
    ps.setInt(1,1)
    ps.setString(1,"q")
    ps.setInt(1,2)
    ps.setInt(1,3)

    ps.execute()

I've seen in another post that a workaround is possible by putting
values instead of ?, but this opens the door to sql injection.
Is there another workaround ?

Perhaps inserting cast statements around some or all of the arguments, like :

when (cast ? as String) then  (cast ? as int) ... etc ?

Another question, if I do :

case
  when cast( ? as String) then  cast( ? as int)
  when cast( ? as String) then  cast( ? as BigDecimal)
  otherwise cast(? as float)

will the result be a BigDecimal ?

BTW :  H2 rocks big time !

Thanks

[Noel Grandin]

Maybe your sample code wasn't copied correctly, but you don't seem to be setting most of the parameters?
(the first parameter in your ps.set* calls is always 1)

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/IXB69zFmTF0J.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Thomas Mueller]

Hi,

> I have this statement that throws an exception with this message : Unknown
> data type: "?, ?"

Yes, you need an explicit cast. Otherwise the statement can't be
prepared, because the data type of the parameters is unknown.

> I've seen in another post that a workaround is possible by putting
> values instead of ?, but this opens the door to sql injection.

The best solution is to use explicit CAST(? AS dataType).

> Perhaps inserting cast statements around some or all of the arguments, like
> when (cast ? as String) then  (cast ? as int) ... etc ?

Yes.

> case
>   when cast( ? as String) then  cast( ? as int)
>   when cast( ? as String) then  cast( ? as BigDecimal)
>   otherwise cast(? as float)
>
> will the result be a BigDecimal ?

The correct syntax is:

select id, case
when 1 then cast(id as int)
when 2 then cast(id as decimal)
else cast(id as float) end from test;

and the result is double (float is equal to double in SQL, and double
is 'larger' than decimal because decimal doesn't support infinity and
NaN).

Regards,
Thomas