Fork me on GitHub
#sql
<
2019-08-15
>
Ashley Smith00:08:06

Hey there, I'm using next-jdbc and I'm trying to simply insert something and get ID of the thing I inserted (the ID auto increments). However, right now, result is nil:

(defn submit-forum-post
  "Validate and upload a post to the database"
  [post]
  (let [body (:body post)]
    (when body
      (println "Receiving post: " body)
      (let [result
          (doall (sql/insert! @db-spec :Posts
              { :PostTitle (:post-title body)
                :PostContent (:post-content body)
                :IsAnonymous (:is-anonymous body)}))]
        (println "RESULT: " result)
        (json/write-str {:result result})))))
The insert is indeed working as posts are appearing. I'm using this function: https://github.com/seancorfield/next-jdbc/blob/master/doc/friendly-sql-functions.md#insert and in the source code this function has the comment:
Given a connectable object, a table name, and a data hash map, inserts the
  data as a single row in the database and attempts to return a map of generated
  keys.
So I'm not quite sure where the problem lies with this. Any ideas? 🙂

dcj01:08:16

@ashley what is the purpose of the doall

Ashley Smith01:08:36

I was just making sure it was being executed

Ashley Smith01:08:44

was unsure when the execution of the insert statement occurred

dcj01:08:44

What happens if you remove the doall?

Ashley Smith01:08:49

it does nothing still

dcj01:08:00

result is still nil?

Ashley Smith01:08:25

am I correct in thinking that this in theory should return the row?

Ashley Smith01:08:16

im rather new to sql and jdbc so I am worried that I'm doing something fundamentally wrong

dcj01:08:11

which database are you using?

hiredman01:08:01

What version of the postgres jdbc driver?

Ashley Smith01:08:22

thats a very good question, I don't know if I have that or if I do, how to check

seancorfield01:08:02

I would expect insert! to return the entire row inserted into the Post table. Including any generated key.

hiredman01:08:31

org.postgresql/postgresql {:mvn/version "42.2.6"} is what next.jdbc is tested with

Ashley Smith01:08:36

yeah, that's my intention as I want to return that ID back to the client so it can create a URL to the post's page

hiredman01:08:26

How your determine what version of the postgres driver you are using will depend on what tooling you are using for your project

Ashley Smith01:08:30

@hiredman do I need to add that to my deps.edn? I have

postgresql/postgresql           {:mvn/version "9.1-901-1.jdbc4"}

hiredman01:08:04

I am not sure how that driver compares to the other

Ashley Smith01:08:42

ah the one you linked has more uses

Ashley Smith01:08:45

let me try that one!

Ashley Smith01:08:53

thank you for bringing this to my attention

dcj01:08:41

I use leiningen, so in my project.clj there is a line like this:

[org.postgresql/postgresql "42.2.6"]

Ashley Smith01:08:31

okay that is bizarre, connection to my db is unsuccessful

Ashley Smith01:08:50

let me work through it

dcj01:08:02

in my dbspec map, I have keys of :dbtype "postgresql", :dbname, :user and :password. If you are using certain connection pooling, you may need :username instead of :user

Ashley Smith01:08:57

yeah I had that already and it was working fine with the other package - would I need to use both at the same time or was I right to replace the previous with this one

seancorfield01:08:21

That version you were using came from 2011 BTW

Ashley Smith01:08:15

well it looks like im on the right track using this one, but clearly there's now something wrong with my way of connecting. For host I have localhost:5432, is this still valid?

seancorfield01:08:56

Just use dbtype dbname user password

Ashley Smith01:08:22

okay, that was the key

seancorfield01:08:08

I'm surprised that worked before. I guess that old driver allowed weird connection strings

Ashley Smith01:08:55

I'm going to need to learn how to re-implement what I had - so before it was localhost:5432 by default but I can pass in the new host to the function I made. In my dockerfile script I have:

cmd ["/usr/local/openjdk-8/bin/java", "-cp", "space.jar", "clojure.main", "-m", "space.api.core", "db:5432"]
which db routed to wherever docker told it to. This seemed to work before but now it doesn't

Ashley Smith01:08:17

Either way though, it worked! next-jdbc now returns the record which is fantastic

Ashley Smith01:08:25

just the plumbing to go

seancorfield01:08:42

You can specify :host and :port separately. If the host is always localhost you only need :port

seancorfield01:08:24

If :port is omitted it defaults to 5432 for postgresql

Ashley Smith01:08:35

just doing one final build 🙂

seancorfield01:08:51

I'm putting my phone down and trying to go back to sleep (2:45 am... Jetlag!).