This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2021-08-02
Channels
- # announcements (3)
- # aws (2)
- # babashka (60)
- # beginners (21)
- # cljs-dev (35)
- # cljsrn (3)
- # clojure (53)
- # clojure-android (2)
- # clojure-australia (3)
- # clojure-europe (45)
- # clojure-france (4)
- # clojure-nl (4)
- # clojure-uk (6)
- # clojurescript (33)
- # core-typed (1)
- # cursive (13)
- # datomic (6)
- # duct (1)
- # emacs (2)
- # fulcro (10)
- # introduce-yourself (3)
- # jobs (2)
- # jobs-discuss (13)
- # leiningen (1)
- # malli (19)
- # missionary (63)
- # music (1)
- # off-topic (21)
- # pathom (3)
- # polylith (18)
- # practicalli (12)
- # proletarian (1)
- # reagent (40)
- # reitit (23)
- # releases (1)
- # remote-jobs (1)
- # ring (14)
- # ring-swagger (1)
- # shadow-cljs (13)
- # sql (30)
- # testing (27)
- # tools-deps (31)
- # vim (10)
- # xtdb (4)
has anyone used next.jdbc
to call an sql function that will return a value?
I can't seem to find anything in the docs to show how to do this
ah sorry, I mean when you have something like an Oracle Function
that will return a value when you make a call
Related to:
https://github.com/seancorfield/next-jdbc/issues/120
You currently have to explicitly call the function in SQL via EXEC something()
I wonder if it's because I'm using an Oracle db but it seems like I get
ORA-00900: invalid SQL statement
I did get the function
call working with the
{ ? = call function_name(?, ?)}
syntax but I'm not sure how you pull out the return value as it just returned
[#:next.jdbc{:update-count -1}]
I don't know either. I don't use Oracle and I never use SQL functions or procedures like that. Feel free to start a thread here https://github.com/seancorfield/next-jdbc/discussions and I'll dig into what -1
means in that context and how to get actual results back. You may be able to trigger that by passing :multi-rs true
in the options hash map.
Thanks Sean. I'll write something up tomorrow and try and get an example project set up.
I did give multi-rs true
a try but didn't seem to do anything. Not sure if it's because we need to register the out param somehow.
My Google-fu is failing me (because of what I'm searching for I guess) - is there any guide on how to have statements like (next.jdbc.sql/insert! tx :some_table {:updated_at "current_timestamp" })
or (next.jdbc.sql/insert! tx :some_table {:updated_at "now()" })
or even (next.jdbc.sql/insert! tx :some_table {:expires_at "now() + interval '7 days'"})
work? I keep running into an exception about mismatched types (varchar vs timestamp)
Each of these is going to boil down to something like (sql/execute! tx ["insert into some_table (updated_at) values (?)" "current_timestamp")
. With this context, does the exception make sense?
you need to provide a value that jdbc will convert to a datetime, instead of a string
if you want the db to provide the time, you probably won't be able to use sql/insert
if you want the application to provide the time, check out https://cljdoc.org/d/seancorfield/next.jdbc/1.2.659/api/next.jdbc.date-time
I just assumed that insert!
would support some sort of parameter hinting - hugsql supports that in its template language. This is helpful though - looks like I'll have to drop to "raw" execute instead
I know ;-) That's not the problem here - I want to push as much as possible to the underlying database
Oh alright, I probably misread, I thought you said you were okay with passing the timestamp from outside. I guess what you meant is you already know it 🙂
Yeah, the next.jdbc.sql
namespace is intentionally pretty thin. As the ns doc suggests: "For anything more complex, use a library like HoneySQL https://github.com/jkk/honeysql to generate SQL + parameters."
Yep, I guess I just assumed that insert!
is a bit smarter or can be helped with in terms of parameter types but I understand the limitations now
@U0JEFEZH6 You might find using HoneySQL to generate the [SQL & params]
vector easier, and then using next.jdbc/execute!
on that.
That would let you specify SQL-as-data and you can have SQL function calls etc in HoneySQL: [:now]
= NOW()
, for example; [:+ [:now] [:interval 7 :days]]
:
dev=> (hsql/format {:insert-into :some-table :values [{:updated-at [:+ [:now] [:interval 7 :days]]}]})
["INSERT INTO some_table (updated_at) VALUES (NOW() + INTERVAL ? DAYS)" 7]
@U04V70XH6 thanks! Normally we use hugsql, but it's tedious to use for simpler queries that's why I was investigating other approaches