Select from H2 adds "clob:" to texts in clojure

Question

I am using a h2 database with clojure. I made a table and put some data in like this

(j/create-table :locations
    [:id "bigint primary key auto_increment"]
    [:title "varchar (255)"]
    [:part "clob"])

  (j/insert-records :locations
    {:title "Steven Gerrard: I'm not the new Beckham" :part "He might not have the Becks appeal -- but Steven Gerrard says he's ready to light up Hollywood in his own way..."}))
    )
   )

And then I selected the data

(defn newest []
 (database/mysql-db)
 (let [results (j/with-connection db-spec
                  (j/with-query-results res
                    ["select id, title, part from locations"]
                    (doall res)))]
    results))

And I used the data on a clostache page

<div class="container">
    <sections>
         {{#newest}}
           <p style="padding-bottom: 15px;">{{title}}<p>
           <p style="padding-bottom: 15px;">{{part}}<p>
         {{/newest}}
     </sections>
  </div>

On the page i get

Steven Gerrard: I'm not the new Beckham

clob0: 'He might not have the Becks appeal -- but Steven Gerrard says he''s ready to light up Hollywood in his own way...'

How can I remove the clob0: string that is attached to the text I have in the database? This happens even if I use text instead of clob for the part column in my database.

score 3 · Answer 1 · edited May 23 '17 at 12:02

The 'clob0:' prefix is not actually attached to your data in database.

The reason you get this is because ResultSet returns instances of class java.sql.Clob for column "part" (for both CLOB and TEXT data types, they are actually synonyms).

It's not proper way to convert Clob to String by using toString() method, implementation is free to return any human-readable form. Correct way may be found in other answers.

score 1 · Answer 2 · answered Mar 30 '17 at 11:04

You might have noticed that new lines get lost by the above... something like this adds them back in...

(defn clob-to-string [clob]
        "Turn an Clob into a String, with new new lines"
    (with-open [rdr (java.io.BufferedReader. (.getCharacterStream clob))]
      (let [lseq (line-seq rdr)
            butlast-line (butlast lseq)
            butlast-line-mapped (map (fn [l] (str l "\n")) butlast-line)
            last-line (last lseq)
            all-lines-with-newline (concat butlast-line-mapped last-line)
            ]
        (apply str all-lines-with-newline)
        )))

score 0 · Answer 3 · answered Aug 22 '15 at 18:58

I removed the clob: with:

(defn clob-to-string [row]
  (assoc row :text (with-open [rdr (java.io.BufferedReader. (.getCharacterStream (:text row)))]
    (apply str (line-seq rdr)))))

(defn newest []
 (database/mysql-db)
 (j/query db-spec
          ["select id, title, text from news"]
          :row-fn clob-to-string
    ))

Select from H2 adds "clob:" to texts in clojure

3 Answers3