CODEWITHSUNDEEP
mysqlclojure
Sonicsmooth
Sonicsmooth

Reputation: 2777

How to get case sensitive column names from MySQL query using funcool/clojure.jdbc

When retrieving items from a MySQL table using funcool/clojure.jdbc, all the column names are lower case, even though the table has been created with mixed case column names. I have found various answers that indicate double quoting, backticking, etc., ought to do something, but they don't. The client side is Windows. The MySQL server side is Linux.

For example, this screenshot from HeidSQL shows a few column names:

enter image description here

I can query the column names in the Clojure REPL with show columns from tablename:

arena-rest.sql> (map :field (fetch "show columns from EEComponents"))
("Number" "Category" "Footprint" "Footprint path" ...

However when I query the data, I get lowercase field names in the return map:

arena-rest.sql> (fetch "select Number from EEComponents")
[{:number "120-00001"} {:number "190-99999"} {:number "180-00002"} 
{:number "180-00003"}]

I expect a response like

[{:Number "120-00001"} {:Number "190-99999"} {:Number "180-00002"} 
{:Number "180-00003"}]

I have tried select "Number" from EEComponents, select Number AS "Number"... etc. including using backticks, but no luck.

I have found some SO questions/answers which indicate that it is normal behavior for all SQL implementations to lowercase column names in returned items, however when I run the query using another means, such as directly in the MySQL command line, the case is preserved in the returned data:

enter image description here

So I'm not sure whether it's in the java or the clojure part. Some searches indicate the column case ought to be preserved in the Java ResultSet. So I'm thinking this is local to the funcool/clojure.jdbc wrapper.

My clojure project is:

(defproject arena-rest "0.1.0-SNAPSHOT"
  :description "FIXME: write description"
  :url "http://example.com/FIXME"
  :dependencies [[org.clojure/clojure "1.9.0"]
                 [clj-http "3.9.1"]
                 [cheshire "LATEST"]
                 [crouton "LATEST"]
                 [mysql/mysql-connector-java "8.0.12"]
                 [funcool/clojure.jdbc "0.9.0"]
                 [org.clojure/tools.trace "0.7.9"]
                 [org.flatland/ordered "1.5.6"]]
  :main arena-rest.core)

My clojure file starts like this:

(ns arena-rest.sql
  (:require [jdbc.core :as jdbc]
            [clojure.string :as str]
            [clojure.pprint :refer [pprint]]
            [clojure.repl :refer [doc]] ... and other stuff

So, is it possible to return properly cased column names using funcool/clojure.jdbc, or do I need to use another one such as org.clojure/java.jdbc ?

Upvotes: 1

Views: 424

Answers (1)

Taylor Wood
Taylor Wood

Reputation: 16194

This is the relevant code that's determining how to coerce column names in result sets, which is converting the strings to lower-case by default.

Try (fetch "select Number from EEComponents" {:identifiers identity}) to leave the strings as-is, or {:identifiers keyword} to turn them into keywords.

(I'd also consider using https://github.com/clojure/java.jdbc)

Upvotes: 2

Related Questions