Fork me on GitHub
#sql
<
2021-08-02
>
Sam H11:08:54

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

emccue13:08:43

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

emccue13:08:52

is this what you mean?

Sam H13:08:37

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

seancorfield16:08:59

You currently have to explicitly call the function in SQL via EXEC something()

Sam H18:08:24

I wonder if it's because I'm using an Oracle db but it seems like I get

ORA-00900: invalid SQL statement

Sam H18:08:26

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}]

seancorfield19:08:11

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.

👍 2
Sam H20:08:35

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.

lukasz18:08:03

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?

lukasz18:08:46

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

indy18:08:08

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

indy18:08:42

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

lukasz18:08:00

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

lukasz18:08:08

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

indy18:08:32

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

lukasz18:08:05

I know ;-) That's not the problem here - I want to push as much as possible to the underlying database

indy18:08:02

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 https://github.com/jkk/honeysql to generate SQL + parameters."

lukasz18:08:07

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

seancorfield19:08:04

@U0JEFEZH6 You might find using HoneySQL to generate the [SQL & params] vector easier, and then using next.jdbc/execute! on that.

seancorfield19:08:36

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]

lukasz20:08:30

@U04V70XH6 thanks! Normally we use hugsql, but it's tedious to use for simpler queries that's why I was investigating other approaches

emccue20:08:46

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

emccue20:08:51

auto-updated at

lukasz20:08:49

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