Automatically Adding Casting to 'CASE' Expressions in Dialects

[Alex D.]

Is there a good way to have a dialect automatically add casting to 'THEN' expressions in 'CASE' statements?

I'm working on a dialect for the H2 database, via Jython.  H2 requires values in a 'THEN' expression to have explicit casts (see: Unknown data type thrown with a case statement where all values are parameters in preparedStatement )

I would like to make it so that my H2 dialect will automatically add casting to 'CASE' expressions, so that users of the dialect don't have to use literal_columns each time they want to use a 'CASE' expression.

One idea I had was to add a 'before_execute' event that checks elements to see if their case statements, but that seems a bit kludgy.  is there a better way?

Levity:

Q: What do a database developer and a hollywood producer have in common?

A: They both can have problems with casting.

[Michael Bayer]

On Sep 28, 2012, at 11:15 AM, Alex D. wrote:

Is there a good way to have a dialect automatically add casting to 'THEN' expressions in 'CASE' statements?

I'm working on a dialect for the H2 database, via Jython.  H2 requires values in a 'THEN' expression to have explicit casts (see: Unknown data type thrown with a case statement where all values are parameters in preparedStatement )

I would like to make it so that my H2 dialect will automatically add casting to 'CASE' expressions, so that users of the dialect don't have to use literal_columns each time they want to use a 'CASE' expression.

One idea I had was to add a 'before_execute' event that checks elements to see if their case statements, but that seems a bit kludgy.  is there a better way?

if you're writing a dialect, then you're in control of everything to do with rendering SQL and there's no need to consider the usual event hooks.    In this case you'd look to override visit_case() in your compiler class.  Below is a pseudo-approach for how to rewrite the case() with the cast() expressions inside of it:

class MyCompiler(Compiler):

    def visit_case(self, clause, **kwargs):

       adjusted = case(

                              whens=[(wh, cast(res, String)) for wh, res in clause.whens,

                              else_ = ...

                           )

       return super(MyCompiler, self).visit_case(adjusted, **kwargs)

Levity:

Q: What do a database developer and a hollywood producer have in common?

A: They both can have problems with casting.

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

[Alex D.]

Roger that, thanks for the prompt response.