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

-- 
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
--- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/clojure/3695bf0e-95df-4bce-b4a1-575c110c7994n%40googlegroups.com.

Re: Adding value to the map in sql statement dynamically

[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

On Sun, Dec 20, 2020, 9:26 AM Ganesh Neelekani 
wrote:

> 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
>
> --
> 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
> ---
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/clojure/3695bf0e-95df-4bce-b4a1-575c110c7994n%40googlegroups.com
> 
> .
>

-- 
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
--- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/clojure/CAHKjGjruUrYBbiLn8obCr6AEmf9fYUtdPMse--OccbRYuibG0A%40mail.gmail.com.

Re: Adding value to the map in sql statement dynamically

[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




On Sun, Dec 20, 2020 at 9:12 PM alpeware llc  wrote:

> 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
>
> On Sun, Dec 20, 2020, 9:26 AM Ganesh Neelekani 
> wrote:
>
>> 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
>>
>> --
>> 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
>> ---
>> 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+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/clojure/3695bf0e-95df-4bce-b4a1-575c110c7994n%40googlegroups.com
>> 
>> .
>>
> --
> 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
> ---
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/clojure/CAHKjGjruUrYBbiLn8obCr6AEmf9fYUtdPMse--OccbRYuibG0A%40mail.gmail.com
> 
> .
>

-- 
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
--- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/clojure/CAGagNV6S3fjzmHMjzFvzHW%2BN9_uFKKVASBShsm%2BpyGocqsSy7g%40mail.gmail.com.

Re: Adding value to the map in sql statement dynamically

[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

On Sun, Dec 20, 2020, 10:43 AM Ganesh Neelekani 
wrote:

> 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
>
>
>
>
> On Sun, Dec 20, 2020 at 9:12 PM alpeware llc  wrote:
>
>> 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
>>
>> On Sun, Dec 20, 2020, 9:26 AM Ganesh Neelekani 
>> wrote:
>>
>>> 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
>>>
>>> --
>>> 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
>>> ---
>>> 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+unsubscr...@googlegroups.com.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/clojure/3695bf0e-95df-4bce-b4a1-575c110c7994n%40googlegroups.com
>>> 
>>> .
>>>
>> --
>> 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
>> ---
>> 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+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/clojure/CAHKjGjruUrYBbiLn8obCr6AEmf9fYUtdPMse--OccbRYuibG0A%40mail.gmail.com
>> 
>> .
>>

Re: Adding value to the map in sql statement dynamically

[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

On Sun, Dec 20, 2020 at 9:15 AM alpeware llc  wrote:

> 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
>
> On Sun, Dec 20, 2020, 10:43 AM Ganesh Neelekani 
> wrote:
>
>> 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
>>
>>
>>
>>
>> On Sun, Dec 20, 2020 at 9:12 PM alpeware llc  wrote:
>>
>>> 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
>>>
>>> On Sun, Dec 20, 2020, 9:26 AM Ganesh Neelekani <
>>> ganeshneelek...@gmail.com> wrote:
>>>
 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

 --
 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
 ---
 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+unsubscr...@googlegroups.com.
 To view this discussion on the web visit
 https://groups.google.com/d/msgid/clojure/3695bf0e-95df-4bce-b4a1-575c110c7994n%40googlegroups.com
 
 .

>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "Clojure" group.
>>> To post to this group, send email to clojur

Re: Adding value to the map in sql statement dynamically

[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 :
> 
> 
> 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
> -- 
> 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
> --- 
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/clojure/3695bf0e-95df-4bce-b4a1-575c110c7994n%40googlegroups.com.

-- 
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
--- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/clojure/62035614-F32E-45EE-875F-99ACD8EA6CD1%40assum.net.