Handling JDBC date type coercion

[lynaghk]

I'm using ClojureQL with PostgreSQL, and one frustrating issue is that I can't use operators on dates:

@(-> rates
     (select (where (> :check_in "2011-05-26")))
     (take 1))

gives

ERROR: operator does not exist: date > character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 49
  [Thrown class org.postgresql.util.PSQLException]

So far the best I've been able to manage is to use JodaTime via clj-time to make a Java.sql.Date:

(select (where (> :check_in (Date. (to-long (parse (formatters :date)
                                                   "2011-05-26"))))))

which is not the best (aside from being verbose, it also has some trouble at the endpoints---this query returns an object with check_in = 2011-05-26, even though I specified greater-than)

Is there a nice way to reach into JDBC and tell it what's what?

[bendlas]

Type coercion is not in ClojureQL. Although there might be a pluggable coercion architecture in the future, it's clearly out of scope for the current implementation. For repl usage, coercion would be convenient, but for production use it would be mostly superfluous or even outright dangerous. Just think of insufficiently validated user input, that gets coerced to something awful.

I suggest you write a function (defn sql-date [s] (java.sql.Date. (to-long (parse (formatters :date))))) for repl usage.

If you want to see this feature in CQL, you can write a feature request with a specification, addressing security/performance issues.

[lynaghk]

Writing a function is certainly doable, yes, and I agree with you about the importance of validating user input.

I don't know anything at all about JDBC---my question is more along the lines of, "When I am using ClojureQL, what is the appropriate way to dig into the JDBC driver to add a typecast?".

On Mon, Aug 22, 2011 at 8:24 AM, bendlas < reply@reply.github.com>wrote:

Type coercion is not in ClojureQL. Although there might be a pluggable coercion architecture in the future, it's clearly out of scope for the current implementation. For repl usage, coercion would be convenient, but for production use it would be mostly superfluous or even outright dangerous. Just think of insufficiently validated user input, that gets coerced to something awful.

I suggest you write a function (defn sql-date [s] (java.sql.Date. (to-long (parse (formatters :date))))) for repl usage.

If you want to see this feature in CQL, you can write a feature request with a specification, addressing security/performance issues.

Reply to this email directly or view it on GitHub: https://github.com/LauJensen/clojureql/issues/103#issuecomment-1871100

[bendlas]

The values are ultimately set by the Java.sql.Statement.set* methods. AFAIK there are no coercion capabilities in JDBC. The postgres driver has type conversions, but they are not column specific. If you still want to make use of that, you'd need to setup the driver and probably pass in a custom datasource. See with-connection on how to do that.

[lynaghk]

Okay, will do. Thanks bendlas.