This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2018-08-10
Channels
- # announcements (2)
- # beginners (37)
- # boot (1)
- # cider (76)
- # clara (14)
- # cljs-dev (132)
- # cljsjs (1)
- # cljsrn (2)
- # clojure (18)
- # clojure-colombia (5)
- # clojure-finland (1)
- # clojure-hamburg (1)
- # clojure-italy (2)
- # clojure-nl (8)
- # clojure-russia (1)
- # clojure-spec (28)
- # clojure-uk (85)
- # clojurescript (84)
- # code-reviews (25)
- # cursive (10)
- # data-science (3)
- # datomic (30)
- # editors (1)
- # emacs (3)
- # fulcro (106)
- # graphql (4)
- # hyperfiddle (26)
- # jobs (2)
- # jobs-discuss (124)
- # keechma (3)
- # leiningen (1)
- # lumo (6)
- # off-topic (5)
- # other-lisps (5)
- # reagent (5)
- # ring-swagger (4)
- # shadow-cljs (140)
- # spacemacs (22)
- # specter (2)
- # sql (48)
- # tools-deps (78)
- # vim (7)
Hi. Couldn't find a better place to ask my question. Here is an equivalent of do/progn
in SQL ? It's to do something along the lines of: SELECT * FROM (CREATE TEMP TABLE ... ; INSERT INTO temp_table ... ; SELECT FROM temp_table ...)
.
Well I expect the answer to be no
. What if i use a sql function ?
@tristefigure begin
... end
should delineate a sequential block of statements I think?
sql isn't really an expression language like that, so you can't nest arbitrary things in arbitrary places
Ah, if @tristefigure specifically means SELECT ... FROM <some block of code ending in an expression>
then, yeah, you can't mix SQL and DDL like that.
Stored procedures might get you closer to what you want...?
Yes, I'm currently exploring this solution (stored procedures, BEGIN..END
not working). However I think it implies I have to specify the returned columns each time I would call that hypothetical progn
stored procedure.
I'm not trying to achieve something specific here. I'm just hacking things around in sql-korma, trying to see how far I can get introducing the notion of "composite queries" (prepared statements with multiple, ;
-delimited queries, mixing both SELECT and INSERT/UPDATE/DELETE statements). Of course, I'm hitting a bone when it comes to using a composite query as a subquery. Now you might ask, what would I want to do that. Here's why: Korma comes with the concept of "entities". It's either a raw table name or a subquery. If I could directly select from a composite query that'd be cool and I'd avoid declaring a sql function wrapping each composed query I want to run.
(this is why I prefer to work in raw SQL and avoid ORM-style abstractions over it! 🙂 )
@tristefigure have you looked at https://www.hugsql.org/?
Only cursorily. Had to make the hard choice of selecting one sql library among the many alternatives that exist in the Clojure ecosystem. I'm quite happy with my choice so far, since what I broadly do is questioning the form APIs have nowadays (REST or GraphQL). My goal is to see how viable it is to expose an API as a controlled DSL of SQL. By that i mean a form of SQL that is easily parseable and upon which I can add facilities for checking authorizations, checking inputs etc... So using korma's surface dsl and intermediary format makes sense as not everything has to be run as SQL. The philosophy I try to follow is to be as transparent as possible when confronted to the need to put an abstraction (say HTTP) over another one (say SQL) so as to not lose expression power along the way. Following this train of thought, I hope to be able to expose any korma based app as a service with minimal efforts and consume it from any other app/language by just adjusting the ORM used there (i.e. consume my SQL-like APIs through Ruby's ActiveRecord for instance).
I think Korma is non-idiomatic, to be honest. We don't have "objects" so an ORM-like library makes no sense to me (but, then, I've used ORMs in lots of OOP languages and hated the experience almost universally so I'm a bit biased).
My approach to SQL in Clojure is to rely on the low-level Contrib library (`org.clojure/java.jdbc`) as much as possible, use HoneySQL where I need programmatically composable queries, else consider something like HugSQL if I want raw SQL kept external to Clojure source code (which, actually, I've never felt the need for).
If you wanted to "standardize" on a DSL, there's always https://github.com/walkable-server/walkable to run Datomic-like DataScript queries over SQL DBs I guess.
Hello all, I´m trying to understand how to use transaction with clojure jdbc and Hikari connection poll?
something like (jdbc/execute db "rollback") may do it, but it might also mess with internal machinery in clojure.java.jdbc (it keeps track of transaction nesting levels to some degree)
I suspect the exact behavior in that case is database dependent, but most will automatically rollback an uncommitted transaction if the connection is lost
I have an other situation that the insert must be commited if the last operation are ok
so even if it did some how live on somewhere, you can't get a handle on it from another connection to roll it back
I don't understand what you mean, you either execute your sql in a transaction or not
I´m doing this
(j/with-db-connection [conn {:datasource (:db-pool sistema)}]
(j/insert! conn tabela dados))
I use with-db-transaction when I need a transaction (which I really try not to need), and I really try to avoid polling databases as well, so I don't recall doing both together
He means "database pool" not poll, I think.
Above he says "Hikari connection poll"
a connection pool that messes with transaction behavior in anyway is a pretty poor connection pool
If you want to execute a series of SQL statements and have them either all succeed or all fail, you wrap them all in a single transaction:
(j/with-db-transaction [t-conn db-spec]
...
...
...
...)
if they all succeed, they'll all be committed; if anything throws an exception, they will all be rolled back.(using a connection pool is irrelevant here)
with-db-transaction
creates a connection with an active transaction (`t-conn` above) and you should use that as the "db spec" for all the enclosed SQL statements.