Clojure PostgreSQL JDBC keep getting error executing query

Question

i hope someone could help me with this,

So i'm building database backed website with clojure and postgresql, but i keep getting error. Here are the code and error on REPL:

(ns app.config-postgre
  (require [clojure.java.jdbc :as sql]))

(def db1
  {:classname "org.postgresql.Driver"
   :subprotocol "postgresql"
   :subname "//localhost:5432/dbname"
   :username "username"
   :password "password}) 
;;that's not the real username and password, the real one is right

here goes the code that caused error :

(sql/insert! db1 :user123
             {:username "user" :password "pass" :user-id "1"})
;;PSQLException ERROR: syntax error at or near "user"
  Position: 43  org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2157)

and

(sql/execute! db1 ["INSERT INTO user123 VALUES ('{\"user\", \"pass\" ,1}')"])
;;PSQLException ERROR: null value in column "password" violates not-null constraint
  Detail: Failing row contains ({user,pass,1}, null, null).  org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2157)

and

(sql/execute! db1 ["INSERT INTO user123 VALUES ('{\"user\"},{\"pass\"},{1}')"])
;;PSQLException ERROR: malformed array literal: "{"user"},{"pass"},{1}"
  Detail: Junk after closing right brace.
  Position: 29  org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2157)

and

(sql/execute! db1 ["INSERT INTO user123 VALUES ('\"user\",\"pass\",1')"])
;;PSQLException ERROR: malformed array literal: ""user","pass",1"
  Detail: Array value must start with "{" or dimension information.
  Position: 29  org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2157)

and

(sql/execute! db1 ["INSERT INTO user123 VALUES ('user','pass',1)"])
;;PSQLException ERROR: malformed array literal: "user"
  Detail: Array value must start with "{" or dimension information.
  Position: 29  org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2157)

Any suggestion? Thank you in advance

edit schema for table user123 :

CREATE TABLE user123
(
  username character varying(100)[] NOT NULL,
  password character varying(100)[] NOT NULL,
  "user-id" integer NOT NULL,
  CONSTRAINT user_pkey PRIMARY KEY ("user-id")
)

@schaueho CREATE TABLE user123 ( username character varying(100)[] NOT NULL, password character varying(100)[] NOT NULL, "user-id" integer NOT NULL, CONSTRAINT user_pkey PRIMARY KEY ("user-id") ) — Dimas Angga Saputra
– Dimas Angga Saputra, Commented Jun 17, 2015 at 6:06
Your attempts look reasonable to me, the only thing that comes to my head is that maybe the "user-id" column name might cause some confusion. Have you tried with a different value than "user" for the username parameter to rule out that it's not barfing on the "user-id"? Otherwise, try specifying the loglevel property of the postgresql driver, maybe this will give you more information. See the documentation on sql/get-connection, this should be possible with the DriverManager approach. — schaueho
– schaueho, Commented Jun 17, 2015 at 6:42
i'm no database expert, but it appears to me that you are specifying username as an array of variable (but bounded) length strings - you probably, unless you are intending to have several user names per row, do not mean this. i would drop the [] from the definition and try again. same with password. — peter
– peter, Commented Jun 17, 2015 at 8:15

Sergii Lagutin · Accepted Answer · 2015-06-17 14:52:27Z

1

Use (sql/execute! db1 ["INSERT INTO user123 VALUES (?, ?, ?)" "user" "pass" 1]) for your purpose

edited Jun 17, 2015 at 14:52

answered Jun 17, 2015 at 13:46

Sergii Lagutin

10.7k1 gold badge37 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

WIZARDELF · Accepted Answer · 2015-06-17 16:53:41Z

0

(sql/execute! db1 ["INSERT INTO user123 VALUES ({'user'}, {'pass'}, 1);"])

answered Jun 17, 2015 at 16:53

WIZARDELF

3,9275 gold badges35 silver badges50 bronze badges

Comments

naod · Accepted Answer · 2015-06-22 20:25:13Z

0

I think your should change :username to :user in your db specification.

answered Jun 22, 2015 at 20:25

naod

1

Collectives™ on Stack Overflow

Clojure PostgreSQL JDBC keep getting error executing query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related