madhat2r/ejc-sql

Emacs SQL client uses Clojure JDBC.

ejc-sql

ejc-sql turns Emacs into a simple SQL client; it uses a JDBC connection to databases via clojure/java.jdbc lib.

You can use multiple connections at the same time. Autocompletion and basic formatting of SQL scripts are also available.

• Installation
• Install JDBC Drivers
• Configuration
• Usage
• Autocomplete
• Troubleshooting
• Requirements
• License

Installation

1. To run Clojure, install Leiningen (assumes you have already installed Java 7+).

2. Add MELPA (if not yet present) to your package-archives list.

   Then you can install ejc-sql with the following command:

   M-x package-install [RET] ejc-sql [RET]

Install JDBC drivers

If you are familiar with JDBC, please omit this section.

The most common way is to install JDBC drivers to your ~/.m2 directory. Here is a list of such installation examples. Anyway, it will become outdated soon, so please consult Google to install your database JDBC driver.

First of all, install Maven, then you can install your JDBC driver with one of the following commands.

Oracle

Download JDBC driver manually from oracle.com

Fix your actual JDBC version number -Dversion, filepath -Dfile and run command like this:

mvn install:install-file -Dfile="~/downloads/ojdbc7.jar" -DgroupId=com.oracle.jdbc -DartifactId=ojdbc7 -Dversion=12.1.0.2 -Dpackaging=jar -DgeneratePom=true

MS SQL Server

Download JDBC driver manually from microsoft.com

Fix your actual JDBC version number -Dversion, filepath -Dfile and run command like this:

mvn install:install-file -Dfile="~/downloads/sqljdbc.jar" -DgroupId=com.microsoft.sqlserver  -DartifactId=sqljdbc -Dversion=6.0 -Dpackaging=jar -DgeneratePom=true

or from Maven Central:

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=com.microsoft.sqlserver:mssql-jdbc:6.2.2.jre8

JTDS

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=net.sourceforge.jtds:jtds:1.3.1

PostgreSQL

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=postgresql:postgresql:9.3-1102.jdbc41 -DrepoUrl=http://clojars.org/repo/

MySQL

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=mysql:mysql-connector-java:5.1.6

MariaDB

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=org.mariadb.jdbc:mariadb-java-client:1.1.7

H2

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=com.h2database:h2:1.4.192

SQLite

mvn org.apache.maven.plugins:maven-dependency-plugin:2.10:get -Dartifact=org.xerial:sqlite-jdbc:3.8.11.2

Configuration

Setup connections with ejc-create-connection function in your .emacs. Its first arg is your custom database connection name, the remaining args are the same as database connection structure of clojure/java.jdbc lib.

The configuration of ejs-sql might look like this:

(require 'ejc-sql)

;; Create your JDBC database connections configuration:

;; MySQL example
(ejc-create-connection
 "MySQL-db-connection"
 :classpath (concat "~/.m2/repository/mysql/mysql-connector-java/5.1.6/"
                     "mysql-connector-java-5.1.6.jar")
 :classname "com.mysql.jdbc.Driver"
 :subprotocol "mysql"
 :subname "//localhost:3306/my_db_name"
 :user "a_user"
 :password "secret")

;; MS SQL Server example
(ejc-create-connection
 "MS-SQL-db-connection"
 :classpath (concat "~/.m2/repository/com/microsoft"
                     "/sqlserver/sqljdbc/4.2/sqljdbc-4.2.jar")
 :classname "com.microsoft.sqlserver.jdbc.SQLServerDriver"
 :subprotocol "sqlserver"
 :subname "//localhost:1433"
 :user "a_user"
 :password "secret"
 :database "my_db_name")

;; MS SQL Server via :connection-uri example
(ejc-create-connection
 "MS-SQL-db-connection-uri"
 :classpath (concat "~/.m2/repository/com/microsoft"
                     "/sqlserver/sqljdbc/4.2/sqljdbc-4.2.jar")
 :classname "com.microsoft.sqlserver.jdbc.SQLServerDriver"
 :connection-uri (concat "jdbc:sqlserver://localhost\\\\instance:1433;"
                         "databaseName=my_db_name;"
                         "user=a_user;"
                         "password=secret;"))

;; MS SQL Server via JTDS example
(ejc-create-connection
 "MS-SQL-db-connection-JTDS"
 :classpath (concat "~/.m2/repository/net/sourceforge/jtds"
                     "/jtds/1.3.1/jtds-1.3.1.jar")
 :classname "net.sourceforge.jtds.jdbc.Driver"
 :connection-uri (concat "jdbc:jtds:sqlserver://localhost:1433/dbname;"
                         "instance=instance;"
                         "user=a_user;"
                         "password=secret;"))

;; Oracle example
(ejc-create-connection
 "Oracle-db-connection"
 :classpath (concat "~/.m2/repository/com/oracle/jdbc"
                     "/ojdbc7/12.1.0.2/ojdbc7-12.1.0.2.jar")
 :classname "oracle.jdbc.driver.OracleDriver"
 :subprotocol "oracle"
 :subname "thin:@localhost:1521:my_db_name"
 :user "a_user"
 :password "secret"
 :separator "/")

;; H2 example
(ejc-create-connection
 "H2-db-connection"
 :classpath (file-truename
             "~/.m2/repository/com/h2database/h2/1.4.191/h2-1.4.191.jar")
 :classname "org.h2.Driver"
 :subprotocol "h2"
 :subname "file://~/projects/my_proj/db/database;AUTO_SERVER=TRUE"
 :user "a_user"
 :password "secret")

;; H2 remote example
;; run on remote server first:
;; java -jar ~/.m2/repository/com/h2database/h2/1.4.192/h2-1.4.192.jar -tcpAllowOthers
(ejc-create-connection
 "H2-remote-db-connection"
 :classpath "~/.m2/repository/com/h2database/h2/1.4.192/h2-1.4.192.jar"
 :classname "org.h2.Driver"
 :connection-uri (concat "jdbc:h2:tcp://192.168.0.1:9092/~/db/database;ifexists=true;"
                         "user=a_user;"
                         "password=secret;"))

;; PostgreSQL example
(ejc-create-connetion
 "PostgreSQL-db-connection"
 :classpath (concat "~/.m2/repository/postgresql/postgresql/9.3.1102.jdbc41/"
                     "postgresql-9.3-1102.jdbc41.jar")
 :classname "org.postgresql.Driver"
 :subprotocol "postgresql"
 :subname "//localhost:5432/my_db_name"
 :user "a_user"
 :password "secret")

ejc-set-rows-limit set limit for the number of records to output (1000 by default). Set to nil if you want to disable this limit.

(ejc-set-rows-limit 1000)

Usage

First of all, open your SQL buffer file (or any temporary buffer) and connect to your database

M-x ejc-connect <RET> MySQL-db-connection <RET>.

and wait until "Connected." message appears. Since connection information is buffer-local, you should use ejc-connect for any new buffer. There is a handy function to create temporary buffer for playing with SQL: ejc-switch-to-sql-editor-buffer.

Then type

select <something> from <mytable>

and press C-c C-c to run it. Use \ char to separate expressions to evaluate. It's possible to run multiple statements, you can use ; to separate them.

Have much fun!

Use existing nREPL

If you have to restart Emacs multiple times, you can keep the ejc-sql clojure backend alive between Emacs restarts by running this backend out of Emacs, and connect to it from Emacs.

To accomplish that, you should cd to your ejc-sql project folder (typically ~/.emacs.d/elpa/ejc-sql-<version>) and launch the nREPL via lein run.

Then run in Emacs M-x ejc-connect-existing-repl, type Host and Port from your lein run console output.

Finally, use M-x ejc-connect from any SQL buffer to connect to the exact database, as always.

List of keybindings & functions

New keybindings defined in ejc-sql-mode minor mode:

Keyboard shortcut	Command	Description
C-c C-c	ejc-eval-user-sql-at-point	Evaluate SQL/JPQL script bounded by the ejc-sql-separator or/and buffer boundaries.
C-h t	ejc-describe-table	Describe SQL table.
C-h T	ejc-describe-entity	Describe SQL entity entity - function, procedure or type.
C-c e up	ejc-show-last-result	Show last result.
C-c e t	ejc-show-tables-list	Show tables list.
C-c e T	ejc-show-user-types-list	Show user types list.
C-c e s	ejc-strinp-sql-at-point	Strip SQL (trim java string tokens).
C-c e S	ejc-dress-sql-at-point	Dress SQL (to copy-paste it to java code).
C-c e p	ejc-pretty-print-sql-at-point	Pretty-print this SQL statement.
C-M-b	ejc-previous-sql	Goto previous SQL statement.
C-M-f	ejc-next-sql	Goto next SQL statement.
C-M-S-b	ejc-previous-sql	Select from point to previous SQL statement.
C-M-S-f	ejc-next-sql	Select from point to next SQL statement.

List of other interactive functions

Command	Description
ejc-connect	Connect to database for current buffer
ejc-quit-connection	Close all database connections, quit Clojure REPL.
ejc-format-sql-at-point	Format SQL
ejc-pretty-print-sql-region	Pretty-print selected SQL snippet
ejc-mark-this-sql	Mark SQL script bounded by the ejc-sql-separator or/and buffer boundaries
ejc-show-tables-list	Show tables list
ejc-show-constraints-list	Show constraints list
ejc-show-procedures-list	Show procedures list
ejc-open-log	Open log
ejc-switch-to-sql-editor-buffer	Create buffer with ejc-sql-mode
ejc-invalidate-cache	Clean your current connection cache (database owners and tables list)
ejc-direx:pop-to-buffer	Create buffer with database structure tree

Autocomplete

Autocompletion is available for the following databases:

• Oracle
• MS SQL Server
• PostgreSQL
• MySQL
• Informix
• H2

Troubleshooting

Error running timer ‘ac-update-greedy’: (error "Sync nREPL request timed out (op eval session...

Increase nrepl-sync-request-timeout, e.g.:

(setq nrepl-sync-request-timeout 60)

Requirements:

• GNU Emacs 24.
• Leiningen 2.x
• clomacs
• clojure/java.jdbc 0.5.8
• dash
• auto-complete
• spinner.el
• direx.el

License

Copyright © 2012-2017 Kostafey kostafey@gmail.com and contributors

Distributed under the General Public License 2.0+