Fork me on GitHub

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


(jdbc/execute! ds ["SELECT count(*) FROM thing"])


is this what you mean?


ah sorry, I mean when you have something like an Oracle Function that will return a value when you make a call Related to:


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 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.

👍 2

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)

Russell Mull18:08:16

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?


Yeah, the exception makes sense - how do I get around it?

Russell Mull18:08:13

you need to provide a value that jdbc will convert to a datetime, instead of a string

Russell Mull18:08:57

if you want the db to provide the time, you probably won't be able to use sql/insert


Adding to what Russell said, you can pass it a java.sql.Timestamp


(java.sql.Timestamp. (System/currentTimeMillis))


That's fine, I can pass any timestamp from "outside"


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


You can pass java.sql.Timestamp in the hashmap that you pass to insert!


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 🙂

Russell Mull18:08:05

Yeah, the next.jdbc.sql namespace is intentionally pretty thin. As the ns doc suggests: "For anything more complex, use a library like 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


If you are using postgres, this is one of the uses for triggers that isn't that bad


auto-updated at


Eventually yes, for now it's a legacy DB managed originally by Rails - so all timestamp fields are required, but do not have triggers or default values set up 😢 We will get there