Korma
Tasty SQL for Clojure.
sqlkorma
When I make a call to insert with multiple rows in korma I get the exception...
Failure to execute query with SQL:
...snip...
SQLException:
Message: near ",": syntax error
SQLState: null
Error Code: 0
What is going on?
Extra details: I am using sqlite as my database.
Source: (StackOverflow)
I'm trying to get Korma to output the SQL it would execute so I can debug a problem I'm having, but the docs are very terse on how to use the as-sql function. Can anyone give me an example of how to make Korma output the SQL for an INSERT query?
Source: (StackOverflow)
I'm trying to reproduce this query from the Postgres docs:
WITH moved_rows AS (
DELETE FROM products
WHERE
"date" >= '2010-10-01' AND
"date" < '2010-11-01'
RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;
Can Korma actually do it (besides just writing raw SQL, of course)? I see no mention of it in the docs.
Thanks...
Source: (StackOverflow)
How does sum-if work in Korma?
Here is the sample query
SELECT SUM(if(items.quantities > 1, 1, 0)) AS multiples FROM items;
I got this to work with raw-exec provided by Korma. But, I am interested in knowing how to write this in the Korma syntax.
I have tried looking at http://sqlkorma.com/docs#select
Source: (StackOverflow)
I've been trying to do the following in Korma to no avail:
sql:
PREPARE q (int) AS SELECT * FROM post a
WHERE EXISTS
(SELECT parent_id
FROM post_map
WHERE parent_id=a.id AND parent_id=$1);
EXECUTE q(1);
my best Korma attempt:
(defn children [parent-id] ;; clojure
(if (number? parent-id)
(exec-raw (str
"PREPARE q (int) AS SELECT * FROM post a WHERE EXISTS
(SELECT parent_id FROM post_map WHERE parent_id=a.id AND parent_id=$1);
EXECUTE q(" parent-id ")")
:results)))
And this is the error I keep getting: (I don't really understand the :: operator below:)
Failure to execute query with SQL:
PREPARE q (int) AS SELECT * FROM post a WHERE EXISTS
(SELECT parent_id FROM post_map WHERE parent_id=a.id AND parent_id=$1);
EXECUTE q(1) :: nil
PSQLException:
Message: No results were returned by the query.
SQLState: 02000
Error Code: 0
PSQLException No results were returned by the query. org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery (AbstractJdbc2Statement.java:274)
I don't think this is a terribly outlandish thing to want to do with a query, so I'm wondering if Korma just isn't going to work for my project. Am I just doing it wrong?
UPDATE: this is what I ended up doing (after I bailed on Korma [sorry Korma]).
(defn children [parent-id]
(if (unsigned? parent-id)
(sql/with-connection db
(sql/with-query-results results
[(str "select " field-list ", b.parent_id from post a, post_map b where a.id=b.child_id and a.id in "
"(select child_id from post c, post_map d where c.id=d.parent_id and c.id=?)") parent-id]
(into [] results)))))
Source: (StackOverflow)
I'm currently using SQLKorma for a project, and I'm running into a bit of a snag with it.
I have constructed a query with two left-joins; one of them contains an array with entries that I wish to use in my WHERE clause.
This is trivial to express in SQL. Note that this is a primarily redacted query.
SELECT
cu.name,
c.description,
c.created_at AT TIME ZONE 'utc'
FROM calendar_users cu LEFT JOIN calendars c ON cu.id = c.user_id
LEFT JOIN meetings m ON c.id = m.id
WHERE 'status_report' ILIKE ANY (m.meeting_metadata)
GROUP BY m.meeting_metadata, c.created_at, cu.name, cu.description
ORDER BY c.created_at DESC
The portion in regards to ILIKE ANY is what I'd like to be able to translate to Korma.
I understand from the docs that the ANY clause isn't supported from the WHERE clause, and I should look into using raw or exec-raw instead.
With that, I want to pass in a parameterized raw string into the WHERE clause to accomplish what I'm trying to go for.
This I've attempted, but it does fails with a syntax error in Postgres:
(select calendars
(fields calendar-user-cols)
(join :calendar_users (= :calendars.user_id :calendar_users.id))
(join :meetings (= :calendars.id :meetings.id))
(where (raw ["? ILIKE ANY(meetings.meeting_metadata)" metadata])))
Specifically:
PSQLException:
Message: ERROR: syntax error at or near "["
Position: 1006
SQLState: 42601
Error Code: 0
How would I go about this using Korma? Do I have to resort to a full-blown exec-raw query?
Source: (StackOverflow)
Using Korma and MySQL I am trying to select from a table named posts, there is a field for the published date that is nil by default.
mysql> describe posts;
+-----------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------------------+-------+
| id | int(11) | NO | PRI | 0 | |
| title | varchar(250) | YES | | NULL | |
| content | text | YES | | NULL | |
| status | tinyint(1) | YES | | 0 | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | |
| published | timestamp | NO | | 0000-00-00 00:00:00 | |
| author | int(11) | NO | MUL | NULL | |
+-----------+--------------+------+-----+---------------------+-------+
7 rows in set (0.02 sec)
If I try to select and use the published field I get the following error:
user=> (select posts (fields :id :title :content :status :created
:published)) Failure to execute query with SQL: SELECT posts.id,
posts.title, posts.content, posts.status, posts.created,
posts.published FROM posts :: [] ClassCastException
java.lang.RuntimeException cannot be cast to java.sql.SQLException
clojure.java.jdbc/print-sql-exception (jdbc.clj:350)
If I don't use the published field everything works fine:
user=> (select posts (fields :id :title :content :status :created
:author)) [{:id 1, :title "Hello World!", :content "Welcome to
Beats, the World's most advanced Clojure Beat Engine!", :status
true, :created #, :author 1} {:id 2,
:title "Hello World!, again!", :content "Sayin 't 'gain! Welcome
to Beats, the World's most advanced Clojure Beat Engine!",
:status true, :created #, :author 2}]
How can I handle this field? I've tried to add a transform function to my entity with just a simple log statement, but it doesn't appear to even get called.
Source: (StackOverflow)
I am trying to use where macro in a function:
(defentity student
(pk :id)
(table :student)
(entity-fields :id :name :age :class)
(database prod))
(defn query-student [cond]
(select student
(where cond)))
I test it:
(select student
(where {:age [> 13]}))
(query-student '{:age [> 13]})
it looks OK, but this
(select student
(where (or {:age [> 13]} {:class [in ["1" "2" "3"]]})))
(query-student '(or {:age [> 13]} {:class [in ["1" "2" "3"]]}))
does not work!
Failure to execute query with SQL:
SELECT "student".* FROM "student" WHERE (or {:age [> 13]} {:class [in ["1" "2" "3"]]}) :: []
PSQLException:
Message: ERROR: syntax error at or near "or"
Location：42
SQLState: 42601
Error Code: 0
PSQLException ERROR: syntax error at or near "or"
Location：42 org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2101)
I want to know why? Something wrong?
Source: (StackOverflow)
I am using compojure, cheshire and korma (and postgre db) for creating a rest service.
I've created a table with two string fields (name and description) with such structure:
(defentity posts
(pk :id)
(table :posts)
(entity-fields :name :description))
I can insert records into this table but when I try to exec
(defn get-all-posts []
(select posts))
and return the results from the server
defroutes app-routes
(GET "/" [] (get-start))
(context "/posts" []
(GET "/" [] (get-all-posts))
...
I receive such an error:
java.lang.IllegalArgumentException
No implementation of method: :render of protocol: #'compojure.response/Renderable found for class: clojure.lang.PersistentVector
As I see I need to convert posts collection to json. How to do it?
Source: (StackOverflow)
I am trying to create a very simple API with korma
Users can query a database like so:
localhost:8080/my_postgres_db/users.json?where[age]=50&limit=1
Currently I am getting an error when trying to apply a where clause to an existing, composable, query.
clojure.lang.ArityException: Wrong number of args (2) passed to: core$where
The code in question:
(defn- comp-query [q [func arg]]
(let [sql-fn (ns-resolve 'korma.core (-> func name symbol))]
(sql-fn q arg)))
(defn compose-query [table col]
(reduce comp-query (select* table) col))
Usage:
(def clauses {:where {:column1 10} :fields "a,b" :limit 10 :offset 500})
(-> (compose-query table clauses) select)
Everything behaves as expected, except for where clauses. I can combine limit, offset and fields in any way I choose and I get the expected results. Only when I have a :where key in my map do I run into the error.
Am I attempting something I shouldn't? Is this bad clojure? Any help would be appreciated.
Note: I have read this SO question
Edit: from lein repl I can manually compose a query in the same fashion and it works
(where (select* "my_table") {:a 5})
Edit:
If I modify my compose-query function to this:
(defn compose-query [table col]
; remove where clause to process seperately
(let [base (reduce comp-query (select* table) (dissoc col :where))]
(if-let [where-clause (:where col)]
(-> base (where where-clause))
base)))
Everything works as expected.
Source: (StackOverflow)
I am trying to generate korma query conditions based on a map of columns and values that I pass into a function.
I am finding that when an empty map is passed to korma's where:
(select "things"
(where conditions))
Generates queries with an empty WHERE which causes a SQL error:
SELECT * FROM things WHERE () LIMIT 10
However using this form:
(select "things"
(if-not (empty? conditions)
(where conditions)))
Results in an error: "Wrong number of args (1) passed to: core$where"
Is there an idiomatic way of handling dynamic clauses in korma?
UPDATE
The following works, but is pretty clumsy (note the strangely necessary if format)
(defn do-select []
(-> (select* "things")
(fields :id :data)
(limit 10)))
(defn add-constraints [query conditions]
(if (empty? conditions)
query
(where query (conditions-to-clause-map conditions))))
(select (do-select)
(add-constraints conditions)
(where (is_owner? owner)))
Source: (StackOverflow)
I am trying to use clojure kORMa with compojure, the webapp is here nepleaks.
First I added kORMa dependency
[korma "0.3.0-RC4"]
lein deps works cool, then created src/hotel/conf/datasource.clj which looks as follows :
(ns hotel.conf.datasource)
(use [korma.db])
(use [korma.core])
)
(defdb db (mysql {:db "nepleaks"
:user "root"
:password "mysql55"}))
(defentity users)
lein ring server is neither throwing any exception nor creating the database entity.
Am I missing something? Do I need more configuration?
Source: (StackOverflow)
I want to use clojure to build a web service with a RESTful API that exposes resources stored in a relational database (mysql in this case). I'd like to use a library that, given a specification of the db schema, would translate incoming requests to db queries, or korma constructs.
Examples might be:
GET /users?status=4
translates to something like:
SELECT * FROM `users` WHERE `status` = 4;
or:
PUT /users/12
would be:
UPDATE `users` SET ... WHERE `id` = 12
Is there anything out there that would facilitate this?
Source: (StackOverflow)
I have some tables with a lot of columns where I sometimes need to just exclude 5-6 out of 50-60. Yes my tables are 3NF normalised, yes some have even more than 60 columns, if this sounds weird to you dear reader, try modelling biological entities.
To my question:
Is it possible to exclude fields by default in selects in Korma instead of specifying tens of columns in entity-fields at entity definition?
Source: (StackOverflow)