hugsql 2022-10-28 | Slack Archive

Eduard Dyckman12:10:40

Good day! I have a question regarding type casting, I hope, I am on the right track with asking it here 🙂 Here is the SQL:

-- :name list-goods :? :n
-- :doc retrieves all goods created by a chat
SELECT * FROM goods
WHERE created_by = :created_by

And the actual call:

(db/list-goods {:created_by (-> dialog :chat :id)})

The function is already bound to the connection, I am using conman. The type of created_by parameter is Long but in the query statements log I actually see a string

2022-10-28 11:50:48.822 GMT [86] LOG:  execute <unnamed>: SELECT * FROM goods
        WHERE created_by = $1
2022-10-28 11:50:48.822 GMT [86] DETAIL:  parameters: $1 = '97469468'

Thanks!

lukasz14:10:57

Something like this should fix it:

-- :name list-goods :? :n
-- :doc retrieves all goods created by a chat
SELECT * FROM goods
WHERE created_by = :created_by::integer

Eduard Dyckman14:10:22

Still no luck!

2022-10-28 14:34:47.329 GMT [90] LOG:  execute <unnamed>: SELECT * FROM goods
        WHERE created_by = $1::integer
2022-10-28 14:34:47.329 GMT [90] DETAIL:  parameters: $1 = '97469468'

lukasz14:10:54

Right, so the second issue is that you're not passing the connection:

(db/list-goods db-conn {:created_by (-> dialog :chat :id)})

or something like that - where db-conn can be provided by a connection pool, "raw" JDBC connection or transaction

lukasz14:10:56

Not sure how conman works, but looking at it I'd say It's a Bad Idea ™️ (in my opinion)

Eduard Dyckman14:10:19

Yea,I don't have an opinion about that, I was just trying out luminus. So the query gets executed, so it definitely works, the only problem is casting

lukasz14:10:10

one thing: have you fully reloaded the namespace that defines queries?

lukasz14:10:42

it's one thing that was getting me early on when working with HugSQL: you have to reload the ns after editing SQL files, since changes are not reflected automatically in the REPL

Eduard Dyckman15:10:46

Yea, I even restarted the repl to be sure 🙂

lukasz18:10:39

That's odd - I'd suggest removing the conman/mount machinery, and trying out your hugsql queries with a "raw" jdbc connection

lukasz18:10:08

because the error you've posted doesn't look like a typical SQL error with a type mismatch I think

Eduard Dyckman18:10:44

Okay, thanks, I will try!

curtis.summers23:10:25

The SQL data type is derived from the Java data type in the JDBC driver. So, are you sure that the Java (Clojure) data type you are passing in is actually a Long?

Eduard Dyckman06:10:23

I can confirm, the output for type function for that var is java.lang.Long

Eduard Dyckman20:10:07

Ok, I can confirm that problem is not in the hugsql. I tried to call def-sqlvec-fns and it generated proper SQL and params. I am pretty confident, that it's somewhere in luminus generated code, I will figure it out and open an issue/fix in luminus

curtis.summers21:10:43

Maybe also check which jdbc library you are using. Looks like PG for your database. Are you using the standard postgresql jdbc library?

Eduard Dyckman10:10:27

Yes, I think it should be next-jdbc

Eduard Dyckman10:10:37

okay, it's really embarrassing, but it turns out, that problem is that I put :n instead of :* into the query annotation...

lukasz15:10:40

ha, I keep forgetting about these, at least everyone in this thread was a helpful https://en.wikipedia.org/wiki/Rubber_duck_debugging 😉

Eduard Dyckman17:10:03

Indeed! Thank you, I definitely boosted up my knowledge of jdbc and how to implement custom type conversions in next-jdbc 🙂 Does it even make sense to have :n for query statements? Maybe such misusage should throw exception?

lukasz17:10:58

I thinks so - :n should be allowed if your query type is :?

Eduard Dyckman18:10:51

Probably for something like this:

SELECT COUNT(*) FROM users;

2022-10-28

Channels