Transactions and clobs

[Simon Holgate]

Hi,

I'm trying out korma and liking it but struggling with clobs.

Following clojure.java.jdbc I can read the clob as in this gist:
https://gist.github.com/1632764

I can't seem to work out how to do this with korma. At least I can't
seem to use the connection defined with defdb. I'm sure this is me
being dumb but I'd appreciate a pointer.

Many thanks.

[Simon Holgate]

Replying to myself... I have a solution if I use get-connection:
(with-connection (get-connection mydb)
(transaction
(with-query-results rs ["select documentation from station"]
; rs will be a sequence of maps,
; one for each record in the result set.
(doseq [row rs] (println (clob-to-string (:documentation
row)))))))

Is this the best way to do it? Is there a more elegant way?

Thanks.

[Chris Granger]

Hey Simon,

In the 0.3.0-alphas there's full transaction support using korma.db/transaction, which will remove a lot of complexity you're seeing. I believe the clob can then be handled in a transform:

(defentity station

  (transform #(-> %

                  (update-in [:documentation] clob-to-string))))

(transaction

  (select station

          (fields :documentation)))

Cheers,
Chris.

[Simon Holgate]

Hi Chris,

> In the 0.3.0-alphas there's full transaction support

thanks for that. Unfortunately alpha13 breaks "select" in oracle.

From what I can see the problem is that table names and columns are
now quoted so the sql comes out like:
SELECT "station"."id" from "station"
This works fine in postgres but oracle can't find table "station"
(in sqlplus:
desc "station";
ERROR:
ORA-04043: object "station" does not exist)

In 0.2.1 the sql looks like:
SELECT station.id from station
which works fine in oracle.

I've been digging about in your commits to try and find where you made
the change but I can't find it at present.

FWIW, I've migrated from oracle to postgres for testing at present and
avoided the clobs for now.

Thanks for all your work.

[Chris Granger]

Hey Simon,

The reason this is happening is likely because when the tables were created, they weren't delimited and oracle will then uppercase all field and table names for you :) There are two ways to "fix" this, one is to just not use delimiters the other is to set the naming strategies. Both of these are done when you define your connection:

(defdb prod (oracle {:db ...

                             ;;Don't use delimiters

                             :delimiters ""

                             ;;set naming strategy

                             :naming {:keys clojure.string/lower-case :fields clojure.string/upper-case}}))

Cheers,
Chris.

[Simon Holgate]

Hi Chris,

> The reason this is happening is likely because when the tables were
> created, they weren't delimited and oracle will then uppercase all field
> and table names for you :) There are two ways to "fix" this, one is to just
> not use delimiters the other is to set the naming strategies. Both of these
> are done when you define your connection:

Brilliant. Thanks!

[Simon Holgate]

Hi Chris,

> In the 0.3.0-alphas there's full transaction support using
> korma.db/transaction, which will remove a lot of complexity you're seeing.
> I believe the clob can then be handled in a transform:
>
> (defentity station
>   (transform #(-> %
>                   (update-in [:documentation] clob-to-string))))
>
> (transaction
>   (select station
>           (fields :documentation)))
>

As a follow up to this, (I'm using 0.3.0-beta1) is there anyway of
defining the entity in such a way that if I don't
request :documentation the select doesn't bork?
i.e. if I want to do
(select station
(fields :id))
it fails at present so I have to do
(select station
(fields :id :documentation))
even if I only want the ids.

At present I do 3 selects on station (different queries with other
joins) so I have to return the documentation 3 times as well.

Cheers,

Simon

[Simon Holgate]

Just realised that I can simply define another entity to handle this.
Thanks.

[Chris Granger]

I don't think I would suggest that path, instead, I use a little function to conditionally update:

(defn update? [m k func]

  (if (m k)

    (update-in m [k] func)

    m))

(defentity station

  (transform #(-> %

                  (update? :documentation clob-to-string))))

Cheers,
Chris.