Fork me on GitHub
#sql
<
2022-09-04
>
theequalizer7313:09:39

Hi friends, I’m trying to run this statement using next.jdbc, but I’m getting

Unhandled org.postgresql.util.PSQLException
   No hstore extension installed.
This is the function:
(defn points-per-user-montly
  [db]
  (with-open [conn (jdbc/get-connection db)]
    (jdbc/execute! conn ["
with all_points as
  (select uid, com_id, sum(points) as points,
          extract (month from created_at) as le_month,
          extract (year from created_at) as le_year
   from acc_points
   group by uid, com_id, le_month, le_year
   union
   select uid, com_id, sum(points) as points,
          extract (month from created_at) as le_month,
          extract (year from created_at) as le_year
   from points
   group by uid, com_id, le_month, le_year
  ),
  total_points as
  (select tp.uid,
          account.name,
          tp.com_id,
          sum(tp.points) as suma_puntos,
          le_month as month,
          le_year as year
   from all_points tp
   join account on account.uid = tp.uid
   join com on com.com_id = tp.com_id
   group by tp.uid, account.name, tp.com_id, com.cod_com,
            com.name, month,year
)
select tp.uid, account.name, tp.cod_com, com.name,
       tp.suma_puntos, tp.month, tp.year, metas.meta_indiv
from total_points tp
left join metas on metas.com_id = tp.com_id
and metas.month = tp.month
and metas.year = tp.year
join account on account.uid = tp.uid
join com on com.com_id = tp.com_id;"
{:builder-fn rs/as-unqualified-kebab-maps}])))
This is my stack trace:
PgPreparedStatement.java:  535  org.postgresql.jdbc.PgPreparedStatement/setMap
  PgPreparedStatement.java: 1037  org.postgresql.jdbc.PgPreparedStatement/setObject
HikariProxyPreparedStatement.java:   -1  com.zaxxer.hikari.pool.HikariProxyPreparedStatement/setObject
               prepare.clj:   42  next.jdbc.prepare/eval11408/fn
               prepare.clj:   -1  next.jdbc.prepare/eval11408/fn
               prepare.clj:   29  next.jdbc.prepare/eval11383/fn/G
               prepare.clj:   55  next.jdbc.prepare/set-parameters
               prepare.clj:   48  next.jdbc.prepare/set-parameters
               prepare.clj:  144  next.jdbc.prepare/create
               prepare.clj:   83  next.jdbc.prepare/create
            result_set.clj:  862  next.jdbc.result-set/eval11971/fn
             protocols.clj:   33  next.jdbc.protocols/eval11075/fn/G
                  jdbc.clj:  244  next.jdbc/execute!
                  jdbc.clj:  231  next.jdbc/execute!
                      REPL:  144  plim.db.reportes/points-per-user-montly
                      REPL:  141  plim.db.reportes/points-per-user-montly
                      REPL:  178  plim.db.reportes/eval41225
                      REPL:  177  plim.db.reportes/eval41225
             Compiler.java: 7194  clojure.lang.Compiler/eval
             Compiler.java: 7149  clojure.lang.Compiler/eval
                  core.clj: 3215  clojure.core/eval
                  core.clj: 3211  clojure.core/eval
    interruptible_eval.clj:   87  nrepl.middleware.interruptible-eval/evaluate/fn/fn
                  AFn.java:  152  clojure.lang.AFn/applyToHelper
                  AFn.java:  144  clojure.lang.AFn/applyTo
                  core.clj:  667  clojure.core/apply
                  core.clj: 1990  clojure.core/with-bindings*
                  core.clj: 1990  clojure.core/with-bindings*
               RestFn.java:  425  clojure.lang.RestFn/invoke
    interruptible_eval.clj:   87  nrepl.middleware.interruptible-eval/evaluate/fn
                  main.clj:  437  clojure.main/repl/read-eval-print/fn
                  main.clj:  437  clojure.main/repl/read-eval-print
                  main.clj:  458  clojure.main/repl/fn
                  main.clj:  458  clojure.main/repl
                  main.clj:  368  clojure.main/repl
               RestFn.java: 1523  clojure.lang.RestFn/invoke
    interruptible_eval.clj:   84  nrepl.middleware.interruptible-eval/evaluate
    interruptible_eval.clj:   56  nrepl.middleware.interruptible-eval/evaluate
    interruptible_eval.clj:  152  nrepl.middleware.interruptible-eval/interruptible-eval/fn/fn
                  AFn.java:   22  clojure.lang.AFn/run
               session.clj:  218  nrepl.middleware.session/session-exec/main-loop/fn
               session.clj:  217  nrepl.middleware.session/session-exec/main-loop
                  AFn.java:   22  clojure.lang.AFn/run
               Thread.java:  834  java.lang.Thread/run

valtteri14:09:50

Hi, I think your options map shouldn’t be within the query vector. If you pop {:builder-fn rs/as-unqualified-kebab-maps} to the outer form that should work. Now JDBC is trying to treat that map as a parameter to your query.

theequalizer7314:09:07

Yeah! That’s true! I miss-placed the options map! Thanks my friend!

👍 1