sql

All things SQL and JDBC...
Roger Amorin Vieira 2020-08-12T18:34:48.457900Z

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 Vieira 2020-08-12T18:35:43.458700Z

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

seancorfield 2020-08-12T18:44:09.461600Z

@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 Vieira 2020-08-12T18:45:02.462200Z

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

seancorfield 2020-08-12T18:45:41.462500Z

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

seancorfield 2020-08-12T18:46:45.463200Z

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

Roger Amorin Vieira 2020-08-12T18:48:15.463700Z

It worked, thanks

dangercoder 2020-08-12T21:11:31.465200Z

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.

dangercoder 2020-08-12T21:12:17.466100Z

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

dangercoder 2020-08-12T21:13:10.466500Z

https://r2dbc.io/

seancorfield 2020-08-12T21:40:04.470100Z

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.

💯 2
seancorfield 2020-08-12T21:45:30.471300Z

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

👍 1
2020-08-12T21:48:42.472300Z

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

Nassin 2020-08-12T21:53:35.474Z

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

Nassin 2020-08-12T22:04:34.474700Z

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

seancorfield 2020-08-12T22:24:15.474900Z

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

seancorfield 2020-08-12T22:24:56.475400Z

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

Nassin 2020-08-12T22:44:07.475800Z

@seancorfield do you pick mysql for new projects?

seancorfield 2020-08-12T23:09:28.476300Z

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

seancorfield 2020-08-12T23:10:59.477500Z

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.