It looks like all the clojure.contrib.sql functions that interact with
tables fail to wrap table names in backticks. As a result, any table
names that are reserved words (like "order") or contain non-
alphanumeric characters (like "foo-bar") will cause SQL errors:
(require '[clojure.contrib.sql :as sql])
(def db {:classname "com.mysql.jdbc.Driver" :subprotocol
"mysql" :subname "//localhost/db" :user "user" :password "secret"})
(sql/with-connection db (sql/drop-table "foo-bar")) ; throws error [1]
(sql/with-connection db (sql/do-commands "DROP TABLE `foo-bar`")) ;
succeeds and returns (0)
Here's the source code:
http://github.com/richhickey/clojure-contrib/blob/master/src/main/clojure/clojure/contrib/sql.clj
Example: #L124: (format "DROP TABLE %s" (as-str name)) should use
"DROP TABLE `%s`"
Is there some kind of JDBC nonsense that I'm not aware of? Are
backticks a special feature of MySQL that can't be read by all JDBC-
compatible RDBMSs? I'm not a database person, but it seems to me that
either backticks should be placed around all table, database, and
column names, or a warning should be added to the docs about only
using non-reserved alphabetic table names.
The workaround is to build one's own prepared statements.
- TimMc
[1] Here is the error I see:
Update counts:
Statement 0: EXECUTE_FAILED
BatchUpdateException:
Message: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '-bar' at line 1
SQLState: 42000
Error Code: 1064
java.lang.Exception: transaction rolled back: You have an error in
your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near '-bar' at line 1
(NO_SOURCE_FILE:0)
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clojure@googlegroups.com
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
clojure+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
