0

Let's say I had a table called, "someTable" and I want to delete the row where column1 = "data1", column2 = "data2", etc...

This is the code I have so far.

(let [db-host "asdfgh.abc.roott.net"
  db-port 1234      
  db-name "ABCDE"]

(def db {:classname "oracle.jdbc.driver.OracleDriver" ; must be in classpath
       :subprotocol "oracle:thin"
       :subname (str "@" db-host ":" db-port ":" db-name)
       ; Any additional keys are passed to the driver
       ; as driver-specific properties.
       :user "user"
       :password "password"}
                               ))
(sql/with-connection db
(sql/delete-rows "tableName" [{column1, data1} {column2, data2}]))

I get an error... Why?

user3745942
  • 193
  • 1
  • 2
  • 7

2 Answers2

1

delete-rows expects the name of the table and the SQL WHERE clause. The latter is usually given as a vector consisting of a parameterized SQL string (i.e. one where actual values are represented by ?) and the values to be used for said parameters.

(sql/with-connection db
  (sql/delete-rows "tableName" ["column1 = ? AND column2 = ?" data1 data2]))

When this is run, the ? will be replaced with data1 and data2 respectively (properly escaped, by the way, which is the reason why this should be used in the first place) and the query run against the server.

In your case, you're not supplying a string as the first element which will either result in some kind of ClassCastException or in the driver trying to execute an invalid query.

user3745942
  • 193
  • 1
  • 2
  • 7
xsc
  • 5,983
  • 23
  • 30
1

The clojure.contrib.sql is outdated and no longer maintained. You should use clojure.java.jdbc instead - see Github repository, reference documentation, and community maintained documentation.

With the updated library, you don't need with-connection, you can simply say:

(sql/delete! db :tableName ["column1 = ? and column2 = ?" data1 data2])
Sean Corfield
  • 6,297
  • 22
  • 31