Adding value to the map in sql statement dynamically

[Ganesh Neelekani]

Hello Team,

I am new to clojure and I wanted to write a function where input from map keys are passed as parameter as input to the sql query.

(def query-body1 "

select first_name, last_name

from ${table_name}

where 

  person_id=${person_id}

order by ${id}

")

(def query-body2 "

select first_name, last_name

from ${table_name}

where 

  person_id=${person_id},

  and first_name=${first_name},

  and last_name=${last_name},

order by ${id}

")

(functiona-name query-body1  (:table_name "Employee", :person_id 123, id: "ABC"))

(functiona-name query-body2 (:table_name "Employee", :person_id 123, :first_name "John", :last_name "David", id: "ABC"))

output should be 

select first_name, last_name

from Employee

where 

  person_id=123,

  and first_name=John,

  and last_name=David,

order by ${id}

How can I achieve this?

Thanks,

Ganesh N

[alpeware llc]

Welcome to Clojure!

You could just define a function taking a map as an argument and return the query as a string using destructering [0]

(defn people [{:keys [table person id]}]

  (str "SELECT * FROM " table " WHERE person_id = " person " ORDER BY " id))

You may want to have a look at Sean Cornfield's Honey SQL as well [1].

Hope this helps.

[0] https://clojure.org/guides/destructuring

[1] https://github.com/seancorfield/honeysql

--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clo...@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+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
---
You received this message because you are subscribed to the Google Groups "Clojure" group.
To unsubscribe from this group and stop receiving emails from it, send an email to clojure+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/clojure/3695bf0e-95df-4bce-b4a1-575c110c7994n%40googlegroups.com.

[Ganesh Neelekani]

Hello Alpaware,

Thanks for the reply, 

In the above, I just gave an example, 

Bt this will be dynamic, Any number of keys can come, How to segregate and assigned to 

particular variable? 

Thanking you.

----------------------------------------------------------------------

With regards.
Ganesh N Neelekani

To view this discussion on the web visit https://groups.google.com/d/msgid/clojure/CAHKjGjruUrYBbiLn8obCr6AEmf9fYUtdPMse--OccbRYuibG0A%40mail.gmail.com.

[alpeware llc]

The most straight forward approach is to simply define a different function for each use case you have using the same approach.

At some point you will have to decide which function to call with what input.

In more general terms, you want to think about taking an input (a map), applying some transformation (a function) and producing an output (a string describing a sql query).

You will first want to think about where the dynamic input is coming from and what is considered valid input for your function.

You then have different ways to dispatch the input to the appropriate function using Clojure's runtime polymorphism [0].

To make sure you are returning valid SQL, I would highly recommend leveraging a library such as the previously mentioned Honey SQL instead. 

There's also a Clojure Slack channel that is super helpful and may be easier to get more real time feedback for various questions [1]

Hope this makes sense.

[0] https://clojure.org/about/runtime_polymorphism

[1] https://clojure.org/community/resources

To view this discussion on the web visit https://groups.google.com/d/msgid/clojure/CAGagNV6S3fjzmHMjzFvzHW%2BN9_uFKKVASBShsm%2BpyGocqsSy7g%40mail.gmail.com.

[Brandon R]

Hey Ganesh,

Just to add to the above information, you may want to check out next.jdbc as well. If you specifically want to create functions that return complete SQL, then maybe this isn't what you want, but I just wanted to point out parameterization in next.jdbc since you mentioned you're new to Clojure, and maybe weren't aware. See here for an example, where next.jdbc replaces a variable number of question marks with the values defined after the SQL in the vector passed to the function.

- Brandon

To view this discussion on the web visit https://groups.google.com/d/msgid/clojure/CAHKjGjqg6qvN_3uSt4wUBE0xxFe_5-JFhUK0T3MDprCFW53DjA%40mail.gmail.com.

[Erik Assum]

Hi Ganesh, 

Please don’t do it this way, as you’re opening yourself to sql-injection. When doing parameterized queries in sql, please reach for prepared statements. 

As others have mentioned, next.jdbc with a sprinkle of honeysql on top should be exact what you’re looking for. 

Erik. 

-- 

i farta

20. des. 2020 kl. 16:26 skrev Ganesh Neelekani <ganeshn...@gmail.com>:



--