Error updating a postgresql enum column

[Luis Ángel]

I have a column on a table that stores a postgresql enum (4 different varchar values); I can't read that column as a string without problems but... when I try to update the value I get this error:

column "currency" is of type currency_enum but expression is of type character varying

Hint: You will need to rewrite or cast the expression.

Has anybody an idea on how to solve that?

[Luis Ángel]

I have checked the source code and parameters of a PreparedStatement are set using setObject, could that be the problem? In our legacy data access service, we are using setString to update postgresql enum types, and it's working without problems.

[David Whittaker]

Can you post the code you are using to do the update?

[Luis Ángel Vicente Sánchez]

This is a simplified version (I have deleted the rest of the fields...); currency is of type Option[Currency] and it's converted to Option[String].

I'm using 0.9.6-SNAPSHOT and I have defined a KeyedEntityDef for DbPreferences; I have enabled logging and the statement is an update statement so everything is working as expected... but I have got that error:

 

 def modifyPreferences(pref: Preferences): ValidationNEL[String, Preferences] = {

    import schema._

    pref.id.map(id =>

      inTransaction {

        val dbpf = DbPreferences(

          id,

          pref.currency.map(_.code)

        )

        preferences.insertOrUpdate(dbpf)

      }

    ).toSuccess(

      "Preferences instance with invalid ID. Can't be created/modified."

    ).liftFailNel

2012/10/16 David Whittaker <da...@iradix.com>

[Maxime Lévesque]

>I can't read that column as a string without problems
> but...

Just to make sure : you can or can't read without problems ?

> column "currency" is of type currency_enum but expression is of type
> character varying

If I understand correctly you are mapping the enum as a String becaue
Squeryl's enums
are backed by Int, correct ?

Squeryl uses setObject internally, so it looks like the JDBC driver is
not doing the conversion
from String to postgres enum when setObject is called,

to confirm this, could you override this def in your PostgresAdapter :

def fillParamsInto(params: Iterable[AnyRef], s: PreparedStatement) {
var i = 1;
for(p <- params) {
// call specific for strings :
o match {
case x:String => s.setString(i, x)
case _ => s.setObject(i, p)
}
i += 1
}
}

[Luis Ángel Vicente Sánchez]

I can read them without problems... I think you are right about the problem with setObject, I have just update the driver to the latest one and if it's still failing I will try what you have told me.

2012/10/16 Maxime Lévesque <maxime....@gmail.com>

[Luis Ángel Vicente Sánchez]

Driver update didn't fix anything. I'm trying your suggestion but...
if I extend my custom adapter like this:

class CustomAdaptater extends PostgreSqlAdapter {
override def quoteIdentifier(s: String) = s

override def fillParamsInto(params: Iterable[AnyRef], s: PreparedStatement) {
var i = 1;
for (p <- params) {

// call specific for strings :

p match {

case x: String => s.setString(i, x)
case _ => s.setObject(i, p)
}
i += 1
}
}
}

I get this error:

[error] /Users/luis/dev/project/core/src/main/scala/core/data/DataService.scala:52:
method fillParamsInto overrides nothing
[error] override def fillParamsInto(params: Iterable[AnyRef], s:
PreparedStatement) {

If I remove the method... the project compiles.

2012/10/16 Maxime Lévesque <maxime....@gmail.com>

[Maxime Lévesque]

The base method is here :

https://github.com/max-l/Squeryl/blob/master/src/main/scala/org/squeryl/internals/DatabaseAdapter.scala#L278

I just compiled this, successfuly :

override def fillParamsInto(params: Iterable[AnyRef], s: PreparedStatement) {
var i = 1;
for (p <- params) {
// call specific for strings :
p match {
case x: String => s.setString(i, x)
case _ => s.setObject(i, p)
}
i += 1
}
}

make sure you are importing java.sql.PreparedStatement,
(watch the exact signature of the method)...


2012/10/16 Luis Ángel Vicente Sánchez <langel...@gmail.com>:

--
A new scientific truth does not triumph by convincing its opponents
and making them see the light, but rather because its opponents
eventually die, and a new generation grows up that is familiar with
it.

-- Max Plank

[Luis Ángel Vicente Sánchez]

I have attached my custom adapter... I still get this error message:

[error] /Users/luis/dev/myapp/core/src/main/scala/core/data/CustomPGSQLAdaptater.scala:12:

method fillParamsInto overrides nothing
[error] override def fillParamsInto(params: Iterable[AnyRef], s:
PreparedStatement) {

[error] ^
[error] one error found
[error] (pr-core/compile:compile) Compilation failed

If I comment that method it compiles, even if I'm overriding also
quoteIdentifiers method... It doesn't make any sense. I'm using sbt
0.12, scala 2.9.1 and squeryl 0.9.6-SNAPSHOT.

2012/10/16 Maxime Lévesque <maxime....@gmail.com>:

[Luis Ángel Vicente Sánchez]

OK, I actually find the problem... eclipse 'Organize imports' added
this to my class:

import scala.collection.immutable.Iterable

2012/10/17 Luis Ángel Vicente Sánchez <langel...@gmail.com>:

[Luis Ángel Vicente Sánchez]

Overriding fillParamsInto doesn't work... I have checked that it's
effectively calling setString for all string parameters and I'm still
getting the same error:

column "currency" is of type currency_enum but expression is of type
character varying

Hint: You will need to rewrite or cast the expression.

Using plain JDBC, our legacy data layer can change that value using a
call to setString without problems... same driver... same database

[Maxime Lévesque]

Squeryl has the information to call more specific setX (X being a
String, Int, Long, etc...)
instead of setObject, so it could be changed for this, but from what
you are reporting it
would not make a difference. There is a single call to setObject in
Squeryl's code base,
(well there are two, but the other one is only dealing with Int in a
very narrow case),
so something is different when you call setString via plain jdbc and
when it get's called
by Squeryl. There is one possibility : is your enum field nullable,
and could you be passing
it a None (truning into a jdbc null) ? In this case the setObject
would be kicking in instead....

[Luis Ángel Vicente Sánchez]

No, I have added a println to check the exact values that are being
set either with setString or with setObject and there are no null
values; there are three string fields (two enums and one varchar) and
all of them went through setString method.

I will make more tests... it has to be something easy to solve...

2012/10/17 Maxime Lévesque <maxime....@gmail.com>:

[Luis Ángel Vicente Sánchez]

I have solve the problem tweaking how the update / insert statement
are written and declaring the dbType of the column as XXXXX_enum in
the schema definition. If we detect that a column has a DBType column
attribute ending by _enum, we write:

? :: enum_type

otherwise we write:

?

So we are type casting the string to a enum type; I'm not sure if this
is the best way to solve the problem as we rely on
DatabaseAdapter.writeValue that has the following "TODO comment":

// TODO: move to StatementWriter (since it encapsulates the 'magic' of
swapping values for '?' when needed)
// and consider delaying the ? to 'value' decision until execution, in
order to make StatementWriter loggable
// with values at any time (via : a kind of prettyStatement method)

I have attached the file with our custom postgresql adapter. I don't
know if it's a faster/more perfomance way of finding if a column has
defined a DBType attribute and check if it ends with _enum, so I
accept any suggestion to improve it.

Kind regards,

Luis

[Dmitry Grigoriev]

Ran into same problem, Luis's solution looks perfect, many thanks for attachment!