shelling-out to powershell to access a DB2

core.clj

(ns online.db2.core
  (:require
    [babashka.process :refer [process]]
    [clojure.string :as str]
    [clojure.test :refer [deftest is]]
    [common-file :refer [create-temp-file-with-content]]
    [instaparse.core :as insta]
    [selmer.parser :refer [render]]))


;; there's no babashka-pod for accessing a DB2
;; but by shelling-out it is possible to execute queries


(defn ->ps-query [username pwd query]
  (render "
$DBDSN=\"DSN=TESTDB0T;UID={{username}};PWD={{pwd}};\"

$DBConnection=New-Object System.Data.Odbc.OdbcConnection
$DBConnection.ConnectionString=$DBDSN
$DBConnection.Open()

$DBCommand=New-Object System.Data.Odbc.OdbcCommand
$DBCommand.Connection=$DBConnection
$DBCommand.CommandText='{{query}}'

$table = new-object \"System.Data.DataTable\"
$table.Load($DBCommand.ExecuteReader())

$DBConnection.close()

# https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/format-table?view=powershell-7.3
# $table | ft -AutoSize
$table" {:username username :pwd pwd :query query}))



(defn ->map [ps-query-output]
  (let [stripped (str/replace ps-query-output #"\\r" "")
        splitted (str/split stripped #"\n\s*?\n")
        filtered (filter #(not (str/blank? %)) splitted)
        lines-per-row (map #(map str/trim (str/split-lines %)) filtered)
        maps (map (fn [row]
                    (reduce (fn [acc [k v]]
                              (assoc acc (keyword k) v))
                      {}
                      (map #(str/split % #"\s*:\s*") row)))
               lines-per-row)]
    maps))

(deftest ->map-test
  (is (= (->map "\r
                            \r
                            CLIENTID                    : 700\r
                            EMPLOYERID                  : \r
                            \r
                            CLIENTID                    : 665795\r
                            LASTCLIENTOPEN              : \r
                            EMPLOYERID                  : \r
                            \r
                            CLIENTID                    : 665801\r
                            EMPLOYERID                  : \r
                            \r
                            \r
                            \r
                            ")
        [{:CLIENTID "700" :EMPLOYERID nil}
         {:CLIENTID "665795" :EMPLOYERID nil :LASTCLIENTOPEN nil}
         {:CLIENTID "665801" :EMPLOYERID nil}])))


(defn execute! [username password query]
  (let [query (->ps-query username password query)
        query-file (create-temp-file-with-content query :suffix ".ps1")
        cmd (str "powershell " query-file)
        proc-result (process cmd)]
    #_(-> proc-result :err slurp println)
    (->map (-> proc-result :out slurp))))

(comment
 (def mapped-result (execute! username password query-as-str))
 )

For this to work (on windows) the ODBC-drivers for IBM/DB2 have to be installed. I don't know where to get them - my company provides them for me.

Instead of ->map, I also tried instaparse - but that didn't work. The call did not return:

  (insta/parser
    "
    records = record*
    record  = <nl> <nl+> field+ <nl+>
    field   = name <ws> <':'> <ws> value? <nl>
    name    = #'\\S*'
    value   = #'\\S.*'
    ws      = ' '*
    nl      = #'\\r?\\n'
    ")