Fork me on GitHub
#sql
<
2018-08-10
>
tristefigure16:08:51

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 ?

seancorfield16:08:32

@tristefigure begin ... end should delineate a sequential block of statements I think?

hiredman16:08:40

sql isn't really an expression language like that, so you can't nest arbitrary things in arbitrary places

seancorfield16:08:13

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.

seancorfield16:08:40

Stored procedures might get you closer to what you want...?

tristefigure16:08:53

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.

seancorfield17:08:21

(this is why I prefer to work in raw SQL and avoid ORM-style abstractions over it! 🙂 )

tristefigure18:08:52

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).

seancorfield18:08:26

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).

seancorfield19:08:09

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).

seancorfield19:08:40

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.

fabrao23:08:37

Hello all, I´m trying to understand how to use transaction with clojure jdbc and Hikari connection poll?

fabrao23:08:03

how to I get transaction from datasource connect to do rollback?

hiredman23:08:56

it is very uncommon to do rollbacks manually

hiredman23:08:07

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)

fabrao23:08:55

but if I´ll do some inserts and it fails in last?

hiredman23:08:10

what do you mean fails?

fabrao23:08:36

like connection lost

hiredman23:08:58

I suspect the exact behavior in that case is database dependent, but most will automatically rollback an uncommitted transaction if the connection is lost

fabrao23:08:17

I have an other situation that the insert must be commited if the last operation are ok

hiredman23:08:26

a sql transaction is also tied to the connection

fabrao23:08:49

so, I have to manualy rollback it

hiredman23:08:49

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

hiredman23:08:41

I don't entirely follow

fabrao23:08:48

if I don´t have transaction, I have to delete the last insert record

hiredman23:08:05

but you have a transaction

fabrao23:08:35

(jdbc/execute db "rollback") ?

hiredman23:08:39

you may want to use another isolation level

fabrao23:08:57

ok, let me try it

hiredman23:08:05

I don't understand what you mean, you either execute your sql in a transaction or not

fabrao23:08:22

I´m doing this

(j/with-db-connection [conn {:datasource (:db-pool sistema)}]
     (j/insert! conn tabela dados))

hiredman23:08:36

you should use with-db-transaction

fabrao23:08:17

but if I use other with-db-transaction is it other transaction?

hiredman23:08:56

you have no transaction there

fabrao23:08:48

Have you ever used Hikari-CP?

fabrao23:08:48

have you ever used with-db-transaction with database poll?

hiredman23:08:10

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

hiredman23:08:29

the main thing would be to avoid putting your polling loop inside the transaction

seancorfield23:08:31

He means "database pool" not poll, I think.

hiredman23:08:04

oh, sure, c3p0 mostly

seancorfield23:08:17

Above he says "Hikari connection poll"

hiredman23:08:48

a connection pool that messes with transaction behavior in anyway is a pretty poor connection pool

hiredman23:08:11

so they should be separate concerns

seancorfield23:08:45

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.

seancorfield23:08:55

(using a connection pool is irrelevant here)

seancorfield23:08:37

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.