search

kostafey / ejc-sql

Emacs SQL client uses Clojure JDBC.
278 stars 29 forks source link

Unable to execute update statement #92

Open EOtrade opened 5 years ago

EOtrade commented 5 years ago

After connecting to PostgreSQL database, I can execute select statements, but any update statement produce error:

Error: This ResultSet is closed.

My configuration:

(use-package ejc-sql
  :config
  (ejc-set-rows-limit nil)
  (setq ejc-org-mode-show-results nil)
  (add-hook 'ejc-sql-minor-mode-hook
            (lambda ()
              (auto-complete-mode t)
              (ejc-ac-setup)))

  ;; Define connections
 (ejc-create-connection
   name
   :classpath "/home/rrudakov/.m2/repository/postgresql/postgresql/9.3-1102.jdbc41/postgresql-9.3-1102.jdbc41.jar"
   :subprotocol "postgresql"
   :subname (format "//%s:5432/%s" "hostname" "dbname")
   :user "username"
   :password "password"))

Emacs version: GNU Emacs 26.1 (build 1, x86_64-pc-linux-gnu, GTK+ Version 3.24.7) of 2019-03-17

kostafey commented 5 years ago

Could you provide a full stack trace, please (by enabling M-x toggle-debug-on-error)? Also, please show your leiningen & java versions (via lein version).

EOtrade commented 5 years ago 
$ lein version
Leiningen 2.9.1 on Java 1.8.0_202 Java HotSpot(TM) 64-Bit Server VM
java -version
java version "1.8.0_202"
Java(TM) SE Runtime Environment (build 1.8.0_202-b08)
Java HotSpot(TM) 64-Bit Server VM (build 25.202-b08, mixed mode)

toggle-debug-on-error doesn't change anything. I got popup: image

kostafey commented 5 years ago

Looks like you run queries from org-mode buffer. Ok, I can't reproduce exactly this issue, but could you please try the last version from master.

rrudakov commented 5 years ago

Looks like you run queries from org-mode

I tried both, from org-mode and from ejc-get-temp-edit-buffer. In master I still can reproduce the issue.

p.s: Wrote from another account

kostafey commented 5 years ago

Looks like, you query is considered as resulted with :result-set. Probably, we face here with select for update case or something like that. The actual code used to determine whether this SQL should have a result set or not is damn trivial: https://github.com/kostafey/ejc-sql/blob/master/src/ejc_sql/connect.clj#L121

Could you check it out? The actual debug process is simple. ejc-sql can be considered as common Clojure & Leningen & CIDER project. So, after run ejc-connect for any sql-mode or org-mode buffer, you can open connect.clj file, then type and eval (@db atom contains connection info of the last used database):

(in-ns 'ejc-sql.connect)
(eval-sql-core :db @db
               :sql "Actual SQL query")
rrudakov commented 5 years ago

image Looks the same.

kostafey commented 5 years ago

What about direct evaluation:

(j/execute! @db (list "UPDATE profiles SET avatar='test' WHERE id=1"))
rrudakov commented 5 years ago 

  Show: Project-Only All 
  Hide: Clojure Java REPL Tooling Duplicates  (10 frames hidden)

1. Unhandled org.postgresql.util.PSQLException
   This ResultSet is closed.

AbstractJdbc2ResultSet.java: 2852  org.postgresql.jdbc2.AbstractJdbc2ResultSet/checkClosed
AbstractJdbc2ResultSet.java: 1875  org.postgresql.jdbc2.AbstractJdbc2ResultSet/setFetchSize
       Jdbc4Statement.java:   37  org.postgresql.jdbc4.Jdbc4Statement/createResultSet
AbstractJdbc2Statement.java:  221  org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler/handleResultRows
    QueryExecutorImpl.java: 1853  org.postgresql.core.v3.QueryExecutorImpl/processResults
    QueryExecutorImpl.java:  255  org.postgresql.core.v3.QueryExecutorImpl/execute
AbstractJdbc2Statement.java:  561  org.postgresql.jdbc2.AbstractJdbc2Statement/execute
AbstractJdbc2Statement.java:  405  org.postgresql.jdbc2.AbstractJdbc2Statement/executeWithFlags
AbstractJdbc2Connection.java:  382  org.postgresql.jdbc2.AbstractJdbc2Connection/execSQLUpdate
AbstractJdbc2Connection.java:  904  org.postgresql.jdbc2.AbstractJdbc2Connection/getTransactionIsolation
                  jdbc.clj:  790  clojure.java.jdbc/db-transaction*
                  jdbc.clj:  769  clojure.java.jdbc/db-transaction*
                  jdbc.clj:  782  clojure.java.jdbc/db-transaction*
                  jdbc.clj:  769  clojure.java.jdbc/db-transaction*
                  jdbc.clj: 1030  clojure.java.jdbc/db-do-execute-prepared-statement
                  jdbc.clj: 1024  clojure.java.jdbc/db-do-execute-prepared-statement
                  jdbc.clj: 1062  clojure.java.jdbc/db-do-prepared
                  jdbc.clj: 1042  clojure.java.jdbc/db-do-prepared
                  jdbc.clj: 1446  clojure.java.jdbc/execute!/execute-helper
                  jdbc.clj: 1450  clojure.java.jdbc/execute!
                  jdbc.clj: 1417  clojure.java.jdbc/execute!
                  jdbc.clj: 1438  clojure.java.jdbc/execute!
                  jdbc.clj: 1417  clojure.java.jdbc/execute!
                      REPL:  273  ejc-sql.connect/eval10189
                      REPL:  273  ejc-sql.connect/eval10189
             Compiler.java: 7062  clojure.lang.Compiler/eval
             Compiler.java: 7025  clojure.lang.Compiler/eval
                  core.clj: 3206  clojure.core/eval
                  core.clj: 3202  clojure.core/eval
                  main.clj:  243  clojure.main/repl/read-eval-print/fn
                  main.clj:  243  clojure.main/repl/read-eval-print
                  main.clj:  261  clojure.main/repl/fn
                  main.clj:  261  clojure.main/repl
                  main.clj:  177  clojure.main/repl
               RestFn.java: 1523  clojure.lang.RestFn/invoke
    interruptible_eval.clj:   79  nrepl.middleware.interruptible-eval/evaluate
    interruptible_eval.clj:   55  nrepl.middleware.interruptible-eval/evaluate
    interruptible_eval.clj:  142  nrepl.middleware.interruptible-eval/interruptible-eval/fn/fn
                  AFn.java:   22  clojure.lang.AFn/run
               session.clj:  171  nrepl.middleware.session/session-exec/main-loop/fn
               session.clj:  170  nrepl.middleware.session/session-exec/main-loop
                  AFn.java:   22  clojure.lang.AFn/run
               Thread.java:  748  java.lang.Thread/run
kostafey commented 5 years ago
  1. Ok, first of all, please update ejc-sql from master. It uses org.clojure/java.jdbc from upstream.
  2. Then please try the newest connection configuration parameters: 
    (ejc-create-connection
"my-db-conn"
:classpath "/home/rrudakov/.m2/repository/postgresql/postgresql/9.3-1102.jdbc41/postgresql-9.3-1102.jdbc41.jar"
:dbtype "postgresql"
:dbname "dbname"
:host "hostname"
:port "5432"
:user "a_user"
:password "secret")
  3. Also, you can try the newest PostgreSQL JDBC driver (https://github.com/pgjdbc/pgjdbc): 
    mvn org.apache.maven.plugins:maven-dependency-plugin:get -Dartifact=org.postgresql:postgresql:42.2.5

    then update ejc-create-connection:

    (ejc-create-connection
...
:classpath "/home/rrudakov/.m2/repository/postgresql/postgresql/42.2.5/postgresql-42.2.5.jar
...
  4. If no luck with previous steps, it's worth to try run JDBC without clojure/java.jdbc wrapper at all, e.g.: https://docs.oracle.com/javase/tutorial/jdbc/basics/connecting.html I case of success with only this approach, please, open an issue in clojure/java.jdbc - Jira directly.
rrudakov commented 5 years ago

Seems like it works now. Thank you very much.

kostafey commented 5 years ago

You are welcome. Feel free to ask about ejc-sql usage and feature requests.

AndreaCrotti commented 4 years ago

Hi @kostafey , I'm getting the same error trying to execute INSERT/CREATE TABLE I tried also to run with the master version of ejc-sql (lein repl and connecting from emacs), but still get the same. And also j/execute! directly in the repl returns the same error.

Any suggestions about how to debug/fix it?

kostafey commented 4 years ago

@AndreaCrotti Could you please update ejc-sql to try with newest org.clojure/java.jdbc "0.7.10" from upstream. If no luck with it, could you provide the database type and JDBC driver you use for access.

AndreaCrotti commented 4 years ago

Actually I was already trying with 0.7.10, I just ran alein ancient upgrade before trying.

The db is a Postgres instance in a docker container

services:
  pg:
    image: postgres:10.4
    ports:

I can try with some other version too potentially if that could the issue.

AndreaCrotti commented 4 years ago

I also tried now with Postgres 12.1 to see if it made a difference but I get the same result..

kostafey commented 4 years ago

@AndreaCrotti, could you provide your database configuration created by ejc-create-connection with mangled username and password, please?

meditans commented 2 years ago

I have the same problem - namely, if I try to do INSERT or CREATE TABLE I get:

Error: This ResultSet is closed.

I could provide the appropriate versions, or better errors, if I knew how, @kostafey. An interesting tidbit I noticed though: when I connect via ejc-connect-interactive I get this error:

2. Unhandled clojure.lang.Compiler$CompilerException
   Error compiling *cider-repl build-28.0.91/ejc-sql:localhost:36775(clj)* at (11:85)
...
1. Caused by java.lang.RuntimeException
   Unable to resolve symbol: tap> in this context
ave6990 commented 1 month ago

I have the same problem. PostgreSQL 15.5

---project.clj---
...
[org.clojure/java.jdbc "0.7.12"]
[org.postgresql/postgresql "42.7.12"]
...