Fork me on GitHub
#sql
<
2020-08-12
>
Roger Amorin Vieira18:08:48

Hi, I'm trying to do a select in database using Honey sql in clojure. My database is MYSQL, and this is the sql that i want: select LPAD(CardPayment.authorization_code, 6, 0) as authorization from CardPayment

Roger Amorin Vieira18:08:43

Someone know how to use LPAD function in honeysql? I dont find in the documents and examples

seancorfield18:08:09

@contato509

user=> (require '[honeysql.core :as h] '[honeysql.helpers :refer [select from]])
nil
user=> (-> (select #sql/call [:lpad :CardPayment.authorization_code 6 0]) (from :CardPayment) (h/format))
["SELECT lpad(CardPayment.authorization_code, ?, ?) FROM CardPayment" 6 0]

Roger Amorin Vieira18:08:02

@seancorfield thanks so much, you save my day of work

seancorfield18:08:41

With the AS alias:

user=> (-> (select [#sql/call [:lpad :CardPayment.authorization_code 6 0] :authorization]) (from :CardPayment) (h/format))
["SELECT lpad(CardPayment.authorization_code, ?, ?) AS authorization FROM CardPayment" 6 0]
user=> 

seancorfield18:08:45

And here's an alternative if you need parameters to lpad being evaluated (so it uses h/call instead of the tagged literal):

user=> (-> (select [(h/call :lpad :CardPayment.authorization_code 6 0) :authorization]) (from :CardPayment) (h/format))
["SELECT lpad(CardPayment.authorization_code, ?, ?) AS authorization FROM CardPayment" 6 0]
user=> 

dangercoder21:08:31

Has anyone looked into wrapping the "R2DBC" libraries from the creators of Spring, a way to communicate with your database in a "non-blocking" fashion? It's bascially based on a java library called "Reactor" and compatible with rx java.

dangercoder21:08:17

From a performance perspective, I guess it's interesting. But I found the interop quite cubersome when trying to wrap it with clojure.

seancorfield21:08:04

I've seen a couple of people over the years ask about async database I/O libraries but they're always non-standard and niche. Sometimes they're just "fake" async wrappers around JDBC (which is inherently blocking/sync), sometimes they're proprietary to a specific database. A couple of initiatives in that area have posted notices that they are no longer maintained. I think once we have fibers (lightweight threads from Project Loom) in the JDK and good adoption, we'll start to see some interesting stuff within and around JDBC.

💯 6
hiredman21:08:42

there is a presentation where oracle says they will no longer work on ADBA (which was some kind async database access standard) and that project loom is the future

seancorfield22:08:15

Thank you! That was one of the ones I was thinking of, yes!

seancorfield21:08:30

That r2dbc API looks very builder-centric which is not a nice pattern to work with from Clojure really, although clojure.java.data.builder would probably make it more palatable @jarvinenemil

👍 3
Nassin21:08:35

nicer would be a clojure data access library that talks directly to postgres, no jdbc, no idea how much work it would be but pg is well documented

Nassin22:08:34

looks like there is a java one already https://vertx.io/docs/vertx-pg-client/java/

seancorfield22:08:56

@kaxaw75836 Except all those of us who do not use PostgreSQL 🙂

Nassin22:08:07

@seancorfield do you pick mysql for new projects?

seancorfield23:08:28

We use Percona's fork of MySQL for all our projects at work.

seancorfield23:08:59

Years ago we were a SQL Server shop (back when we ran Windows servers in production). But we switched from IIS/Windows/SQL Server to Apache/Red Hat/MySQL about ten years ago and we've been very happy with that.