······@fisica.unige.it writes:
> Hello,
> I'm learning CL in these days (well, months actually - i do that in my
> spare time, because I use c++/python for my job) and getting more and
> more confident with the language, anyway i'm still a cl
> newbie. Yesterday I experimented a bit with CLSQL (which is very nice
> imo). I did not notice any support for placeholders / query
> parameters, i.e. something like
>
> (clsql:query "select A, B from C where A=?" ; query
> '(1)) ; bindings
(clsql:query (format nil "select A, B from C where A=~d" ; query
1)) ; bindings
The good thing about this is that you can use format's own language to
select the query string depending on the arguments.
E.g.
(clsql:query
(format nil "select name~:[~;,secret~] from people where age >= ~d"
logged-in minimal-age))
The downside is that if you do, you may not be able to decipher it
five minutes later.
Andras
······@math.bme.hu (Simon Andr�s) writes:
> ······@fisica.unige.it writes:
>
>> Hello,
>> I'm learning CL in these days (well, months actually - i do that in my
>> spare time, because I use c++/python for my job) and getting more and
>> more confident with the language, anyway i'm still a cl
>> newbie. Yesterday I experimented a bit with CLSQL (which is very nice
>> imo). I did not notice any support for placeholders / query
>> parameters, i.e. something like
>>
>> (clsql:query "select A, B from C where A=?" ; query
>> '(1)) ; bindings
>
> (clsql:query (format nil "select A, B from C where A=~d" ; query
> 1)) ; bindings
>
> The good thing about this is that you can use format's own language to
> select the query string depending on the arguments.
The OP's scheme has the advantage of letting the database driver or
backend do whatever quoting is necessary to embed the data into the
query. Without this, you have to be careful about what's in the values
you interpolate textually into the string.
Zach
Zach Beane wrote:
> ······@math.bme.hu (Simon Andr�s) writes:
>
>
>>······@fisica.unige.it writes:
>>
>>
>>>Hello,
>>>I'm learning CL in these days (well, months actually - i do that in my
>>>spare time, because I use c++/python for my job) and getting more and
>>>more confident with the language, anyway i'm still a cl
>>>newbie. Yesterday I experimented a bit with CLSQL (which is very nice
>>>imo). I did not notice any support for placeholders / query
>>>parameters, i.e. something like
>>>
>>>(clsql:query "select A, B from C where A=?" ; query
>>> '(1)) ; bindings
>>
>>(clsql:query (format nil "select A, B from C where A=~d" ; query
>> 1)) ; bindings
>>
>>The good thing about this is that you can use format's own language to
>>select the query string depending on the arguments.
>
>
> The OP's scheme has the advantage of letting the database driver or
> backend do whatever quoting is necessary to embed the data into the
> query. Without this, you have to be careful about what's in the values
> you interpolate textually into the string.
>
Ok, but TRT would be to use something akin to the LW SQL syntax, which,
if memory does not fail me, at a certain point was also part of MaiSQL.
Cheers
Marco
"Zach Beane" <····@xach.com> wrote in message
···················@unnamed.xach.com...
> ······@math.bme.hu (Simon Andr�s) writes:
>
> > ······@fisica.unige.it writes:
> >
> >> Hello,
> >> I'm learning CL in these days (well, months actually - i do that in my
> >> spare time, because I use c++/python for my job) and getting more and
> >> more confident with the language, anyway i'm still a cl
> >> newbie. Yesterday I experimented a bit with CLSQL (which is very nice
> >> imo). I did not notice any support for placeholders / query
> >> parameters, i.e. something like
> >>
> >> (clsql:query "select A, B from C where A=?" ; query
> >> '(1)) ; bindings
> >
> > (clsql:query (format nil "select A, B from C where A=~d" ; query
> > 1)) ; bindings
> >
> > The good thing about this is that you can use format's own language to
> > select the query string depending on the arguments.
>
> The OP's scheme has the advantage of letting the database driver or
> backend do whatever quoting is necessary to embed the data into the
> query. Without this, you have to be careful about what's in the values
> you interpolate textually into the string.
The other detail is that FORMAT can be pretty slow to create the final
query, though you can precompile those as well.
Regards,
Will Hartung
(·····@msoft.com)
Zach Beane <····@xach.com> writes:
> ······@math.bme.hu (Simon Andr�s) writes:
>
> > ······@fisica.unige.it writes:
> >
> >> Hello,
> >> I'm learning CL in these days (well, months actually - i do that in my
> >> spare time, because I use c++/python for my job) and getting more and
> >> more confident with the language, anyway i'm still a cl
> >> newbie. Yesterday I experimented a bit with CLSQL (which is very nice
> >> imo). I did not notice any support for placeholders / query
> >> parameters, i.e. something like
> >>
> >> (clsql:query "select A, B from C where A=?" ; query
> >> '(1)) ; bindings
> >
> > (clsql:query (format nil "select A, B from C where A=~d" ; query
> > 1)) ; bindings
> >
> > The good thing about this is that you can use format's own language to
> > select the query string depending on the arguments.
>
> The OP's scheme has the advantage of letting the database driver or
> backend do whatever quoting is necessary to embed the data into the
> query. Without this, you have to be careful about what's in the values
> you interpolate textually into the string.
Right, I should've mentioned this, and the fact that it's no
replacement for prepared statements. But it's handy nonetheless.
Andras
······@math.bme.hu (Simon Andr�s) writes:
> ······@fisica.unige.it writes:
>
>> Hello,
>> I'm learning CL in these days (well, months actually - i do that in my
>> spare time, because I use c++/python for my job) and getting more and
>> more confident with the language, anyway i'm still a cl
>> newbie. Yesterday I experimented a bit with CLSQL (which is very nice
>> imo). I did not notice any support for placeholders / query
>> parameters, i.e. something like
>>
>> (clsql:query "select A, B from C where A=?" ; query
>> '(1)) ; bindings
>
> (clsql:query (format nil "select A, B from C where A=~d" ; query
> 1)) ; bindings
>
alternative you can do
(clsql:enable-sql-reader-syntax and run:
(let ((num 1))
(clsql:select [*] :from [test] :where [= [int_val] num]))
Regards
Friedrich
--
Please remove just-for-news- to reply via e-mail.
hello Simon,
>>>>> "Simon" == Simon Andr�s <······@math.bme.hu> writes:
Simon> ······@fisica.unige.it writes:
>> Hello, I'm learning CL in these days (well, months actually - i
>> do that in my spare time, because I use c++/python for my job)
>> and getting more and more confident with the language, anyway
>> i'm still a cl newbie. Yesterday I experimented a bit with
>> CLSQL (which is very nice imo). I did not notice any support
>> for placeholders / query parameters, i.e. something like
>>
>> (clsql:query "select A, B from C where A=?" ; query '(1)) ;
>> bindings
Simon> (clsql:query (format nil "select A, B from C where A=~d" ;
Simon> query 1)) ; bindings
Simon> The good thing about this is that you can use format's own
Simon> language to select the query string depending on the
Simon> arguments.
I was writing about prepared queries, i. e. you have a function to
tell the DB in your query there are, for example, 2 placeholders and
then use that query for a zillion times supplying the query parameters
only. Query parameters substitution is usually carried on by the DB
server-side. See the answer from Kevin Rosenberg. My example did not
stress enough this idea because the query was called once. Normally, a
call would probabably be something like
(clsql:query "select A, B from C where A=?" ; query
'(1 2 3 4 5 6)) ; parameters
which would mean to call "select A, B from C where A=?" substituting ?
with 1, 2, ...., 6.
Thanks anyway,
e.
--
Enrico Sirola
·············@statpro.com wrote:
> hello Simon,
>
>
>>>>>>"Simon" == Simon Andr�s <······@math.bme.hu> writes:
>
>
> Simon> ······@fisica.unige.it writes:
> >> Hello, I'm learning CL in these days (well, months actually - i
> >> do that in my spare time, because I use c++/python for my job)
> >> and getting more and more confident with the language, anyway
> >> i'm still a cl newbie. Yesterday I experimented a bit with
> >> CLSQL (which is very nice imo). I did not notice any support
> >> for placeholders / query parameters, i.e. something like
> >>
> >> (clsql:query "select A, B from C where A=?" ; query '(1)) ;
> >> bindings
>
> Simon> (clsql:query (format nil "select A, B from C where A=~d" ;
> Simon> query 1)) ; bindings
>
> Simon> The good thing about this is that you can use format's own
> Simon> language to select the query string depending on the
> Simon> arguments.
>
> I was writing about prepared queries, i. e. you have a function to
> tell the DB in your query there are, for example, 2 placeholders and
> then use that query for a zillion times supplying the query parameters
> only. Query parameters substitution is usually carried on by the DB
> server-side. See the answer from Kevin Rosenberg. My example did not
> stress enough this idea because the query was called once. Normally, a
> call would probabably be something like
>
> (clsql:query "select A, B from C where A=?" ; query
> '(1 2 3 4 5 6)) ; parameters
>
> which would mean to call "select A, B from C where A=?" substituting ?
> with 1, 2, ...., 6.
Apart from the server-side/prepared-query issues, you have a simple
problem of doing a loop over a list here. What is the problem with this?
(defmacro query** (query &rest args)
`(dolist (single-query-args ,args)
(clsql:query (apply #'format nil query single-query-args))))
Cheers
--
Marco
Hi Marco,
sorry for the delay
> Apart from the server-side/prepared-query issues, you have a simple
> problem of doing a loop over a list here. What is the problem with
> this?
> (defmacro query** (query &rest args)
> `(dolist (single-query-args ,args)
> (clsql:query (apply #'format nil query single-query-args))))
apart for the server-side/prepared-query issues, your solutions is
satisfying. The only problems are performances, if you have to loop
over a lot of inserts/updates. With the prepared queries, the DB
compiles the query only once, while with the above solution it sees
each query as a different one.
Cheers,
e.
--
Enrico Sirola
hello Simon,
>>>>> "Simon" == Simon Andr�s <······@math.bme.hu> writes:
Simon> ······@fisica.unige.it writes:
>> Hello, I'm learning CL in these days (well, months actually - i
>> do that in my spare time, because I use c++/python for my job)
>> and getting more and more confident with the language, anyway
>> i'm still a cl newbie. Yesterday I experimented a bit with
>> CLSQL (which is very nice imo). I did not notice any support
>> for placeholders / query parameters, i.e. something like
>>
>> (clsql:query "select A, B from C where A=?" ; query '(1)) ;
>> bindings
Simon> (clsql:query (format nil "select A, B from C where A=~d" ;
Simon> query 1)) ; bindings
Simon> The good thing about this is that you can use format's own
Simon> language to select the query string depending on the
Simon> arguments.
I was writing about prepared queries, i. e. you have a function to
tell the DB in your query there are, for example, 2 placeholders and
then use that query for a zillion times supplying the query parameters
only. Query parameters substitution is usually carried on by the DB
server-side. See the answer from Kevin Rosenberg. My example did not
stress enough this idea because the query was called once. Normally, a
call would probabably be something like
(clsql:query "select A, B from C where A=?" ; query
'(1 2 3 4 5 6)) ; parameters
which would mean to call "select A, B from C where A=?" substituting ?
with 1, 2, ...., 6.
Thanks anyway,
e.
--
Enrico Sirola
In comp.lang.lisp, you wrote:
> I was writing about prepared queries, i. e. you have a function to
> tell the DB in your query there are, for example, 2 placeholders and
> then use that query for a zillion times supplying the query parameters
> only. Query parameters substitution is usually carried on by the DB
My goal with prepared statements is to speed up execute, as you have
mentioned. I did some preliminary tests with postgresql since it's
prepared statements don't require new API calls -- one uses regular
SQL statements to prepare and execute prepared statements.
In my initial testing, I found an 18% speed increase when using a form
such as:
(progn
(execute-command
"PREPARE my_prep_stmt (int) AS SELECT A FROM TBL WHERE B=$1")
(dotimes (i 1000) (QUERY "EXECUTE my_prep_stmt (1)")))
versus
(dotimes (i 1000) (QUERY "SELECT A FROM TBL WHERE B=1"))
--
Kevin Rosenberg
·····@rosenberg.net
>>>>> "Kevin" == Kevin M Rosenberg <·····@news.swcp.com> writes:
Kevin> In my initial testing, I found an 18% speed increase when
Kevin> using a form such as:
I don't have any benchmark with me, but I'm sure you get a big
benefit (at least with MS SQL Server 7 + ODBC) if you have to perform
a lot of small inserts - I did a bit of benchmarking some time ago
(I was using python + mxODBC but a think most of the work is done by
the odbc layer).
Cheers,
e.
--
Enrico