Fork me on GitHub
#sql
<
2017-07-05
>
seancorfield05:07:14

[org.clojure/java.jdbc "0.7.0-beta4"] is available with a number of bug fixes compared to earlier Beta builds.

petr.mensik09:07:41

I am trying to insert a file into the Postgres bytea type. However official Java doc say you can use PreparedStatement#setBinaryStream (not sure how to use it from Clj since there are two params to this method). I also tried inserting stream directly with (io/input-stream "my-file.pdf") but that didin't work either (JDBC cannot recognize type java.io.BufferedInputStream). Thanks for any advice

Shantanu Kumar10:07:39

@petr.mensik If you have access to the PreparedStatement object you can use Java interop to set the param using setBinaryStream

petr.mensik10:07:44

@kumarshantanu I guess I could construct whole query via PreparedStatement and interop but I am looking for better option here 🙂

Shantanu Kumar10:07:45

@petr.mensik In case you can try (shameless plug) https://github.com/kumarshantanu/asphalt you can specify (asphalt.core/defsql "the sql" {:params-setter (fn [ps params] (.setBinaryStream ps 1 (first params)))}) — here params-setter lets you do your thing with the prepared statement

petr.mensik14:07:03

I've solved it like this (maybe it helps somebody)

(doto (.prepareStatement conn "INSERT INTO files (name, size, content_type, user_id, content) VALUES (?, ?, ?, ?, ?)"
                              Statement/RETURN_GENERATED_KEYS)
       (.setString 1 (:filename file))
       (.setInt 2 (:size file))
       (.setString 3 (:content-type file))
       (.setInt 4 (user-model/get-actual-user :id))
       (.setBinaryStream 5 (io/input-stream (:tempfile file)))
       (.executeUpdate)))))

cycle33715:07:45

I'm having some difficulty selecting all the fields and also aggregating COUNT(*) in the same select

cycle33715:07:54

can anyone help me ?

mattly16:07:17

@avabinary what does your query look like?

mattly16:07:37

any field that's not an aggregate needs to be in the group by clause

cycle33716:07:09

"SELECT hotels.name, COUNT(*) FROM hotels"

cycle33716:07:45

I want both the total hotels and their names

cycle33716:07:01

I'd like very much that the result of count to be returned as a new column in the result of SELECT * from 'hotels'

seancorfield16:07:21

@petr.mensik There's a prepare-statement function in java.jdbc that should allow you to do most of that directly -- and you can extend the protocols to handle custom types. See http://clojure-doc.org/articles/ecosystem/java_jdbc/using_sql.html#protocol-extensions-for-transforming-values

cycle33716:07:07

closer to what I'm after is this

cycle33716:07:13

SELECT productCode, productName, COUNT(*) As Total FROM products

cycle33716:07:32

except this returns only one entry and I want all of the entries

cycle33716:07:09

productCode   	                  productName	                                Total 
S10_16781969                       Harley Davidson Ultimate Chopper	110
`

petr.mensik16:07:29

@seancorfield thanks, that's what I was looking for

cycle33716:07:19

i'm lucked out

cycle33716:07:35

I can just do a count

cycle33716:07:42

on the select results

cycle33716:07:13

but that means two selects, one just for counting ... because my main select is paginated

thomaslp19:07:18

is anyone here familiar with datasplash?