Lisp - CLSQL - feature request?

Author

CLSQL - feature request?

sirola@fisica.unige.it

2004-05-18, 11:32 am

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

or, maybe,

(clsql:query "select A, B from C where A=%(bind1) AND D=%(bind2)" ; query
'((bind1 . 1) (bind2 . 2)) ; bindings

am I wrong? If not, does anyone know if this is a planned feature?
Thanks in advance,
e.

--
Enrico Sirola

Simon András

2004-05-18, 11:32 am

sirola@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

Kevin M. Rosenberg

2004-05-18, 11:32 am

On 2004-05-18, sirola@fisica.unige.it <sirola@fisica.unige.it> wrote:
> imo). I did not notice any support for placeholders / query
> parameters, i.e. something like
> [...]
> am I wrong? If not, does anyone know if this is a planned feature?
> Thanks in advance,

Prepared statements are a planned feature after the upcoming 3.0
release. The goal of this release is full backward CommonSQL
compatibility as well as major documentation revision.

At this point, the CLSQL backends which support prepared statements
are ODBC, AODBC, PostgreSQL, PostgreSQL-socket, and Oracle. SQLite
does not support prepared statements. IIRC, MySQL 5 will have prepared
statements.

For further discussion and reports of new features, you may want to
join the clsql-devel mailing list[1].

Kevin

[1] http://lists.b9.com/mailman/listinfo/clsql-devel

Zach Beane

2004-05-18, 11:32 am

asimon@math.bme.hu (Simon András) writes:

> sirola@fisica.unige.it writes:
>
>
> (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

Friedrich Dominicus

2004-05-18, 11:32 am

asimon@math.bme.hu (Simon András) writes:

> sirola@fisica.unige.it writes:
>
>
> (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.

Simon András

2004-05-18, 11:32 am

Zach Beane <xach@xach.com> writes:

> asimon@math.bme.hu (Simon András) writes:
>
>
> 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

sirola@fisica.unige.it

2004-05-18, 11:32 am

Hi Kevin,
first of all, thanks a lot for your quick reply,
[color=darkred]
[color=darkred]
Kevin> Prepared statements are a planned feature after the
Kevin> upcoming 3.0 release. The goal of this release is full
Kevin> backward CommonSQL compatibility as well as major
Kevin> documentation revision.

ok

Kevin> At this point, the CLSQL backends which support prepared
Kevin> statements are ODBC, AODBC, PostgreSQL, PostgreSQL-socket,
Kevin> and Oracle. SQLite does not support prepared
Kevin> statements. IIRC, MySQL 5 will have prepared statements.

that would be great. Do you mean actually prepared statements are in
the CVS?
Bye,

--
Enrico Sirola

sirola@fisica.unige.it

2004-05-18, 11:32 am

enrico.sirola@statpro.com

2004-05-18, 12:35 pm

hello Simon,
[color=darkred]

Simon> sirola@fisica.unige.it writes:[color=darkred]

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

sirola@fisica.unige.it

2004-05-18, 12:35 pm

Kevin M. Rosenberg

2004-05-18, 3:42 pm

On 2004-05-18, sirola@fisica.unige.it <sirola@fisica.unige.it> wrote:
>
> Kevin> At this point, the CLSQL backends which support prepared
> Kevin> statements are ODBC, AODBC, PostgreSQL, PostgreSQL-socket,
> Kevin> and Oracle. SQLite does not support prepared
> Kevin> statements. IIRC, MySQL 5 will have prepared statements.
>
> that would be great. Do you mean actually prepared statements are in
> the CVS?

The public source control system for CLSQL is subversion, not
CVS. Also, I make releases fairly often so what is in svn is often in
the released version.

As for your question, yes, prepared statements are availabe in some
backends, but they take different forms and are not well tested.

The goal of a post-3.0 release will be to present a high-level
interface to prepared statements which will hide the differences
betwee the backends.

--
Kevin Rosenberg
kevin@rosenberg.net

Kevin M. Rosenberg

2004-05-18, 3:42 pm

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
kevin@rosenberg.net

sirola@fisica.unige.it

2004-05-18, 3:42 pm

>>>>> "Kevin" == Kevin M Rosenberg <kevin@news.swcp.com> writes:

Kevin> As for your question, yes, prepared statements are availabe
Kevin> in some backends, but they take different forms and are not
Kevin> well tested.

Kevin> The goal of a post-3.0 release will be to present a
Kevin> high-level interface to prepared statements which will hide
Kevin> the differences betwee the backends.

ok, thanks a lot again
e.

Will Hartung

2004-05-18, 3:42 pm

"Kevin M. Rosenberg" <kevin@rosenberg.net> wrote in message
news:slrncajv98.i6j.kevin@tiger.med-info.com...
> On 2004-05-18, sirola@fisica.unige.it <sirola@fisica.unige.it> wrote:

> At this point, the CLSQL backends which support prepared statements
> are ODBC, AODBC, PostgreSQL, PostgreSQL-socket, and Oracle. SQLite
> does not support prepared statements. IIRC, MySQL 5 will have prepared
> statements.

Yeah, but is there really any actual benefit here?

For something like Oracle, (from the OCI [Oracle C Interface] point of
view), you prepare the statement and then pass pointers to the parameters in
memory.

Now what the actual wire protocol is for the query is a real puzzler. You'd
like to think that the query consists of the actual query string plus a
splash of binary data (perhaps in network order) so the DB can process it
directly without having to go through an ASCII conversion.

Even better would be for the wire protocol to be that when the statement is
prepared, it's actually sent to the DB, and cached there, optomized once,
etc., then when you perform the query, you only send the actual parameters
(in binary), thus gaining some performance for paying the preparation price.

Now, as I understand it, for PostgreSQL, this is all moot, as its wire
protocol is simply the completely ASCII rendered SQL. So, even with a
prepared statement you have to pay the "price" of converting the binary data
into ascii before you ship it over to the DB (though I believe it will reply
with binary data).

So, its not clear to me at all that prepared statements give any actual
PERFORMANCE benefit, at least on PostgreSQL, unless they're pre-optimizing
the SQL on the backend as well, as assume that the "prepared" statement
coming over is the same.

For ODBC, it's not really important as the actual back end ODBC driver does
all of the work for the DB anyway, so who know what it's actually doing.

Also, most of the Lisp SQL interfaces do the same thing, essentially
recreating the complete SQL ASCII statement for each request.

On the other hand, for an actual binary SQL protocol (ala Oracle OCI), then
you need to marshall the binary data into and out of the Lisp process
anyway, so I dunno if even then we'd get much of a performance benefit.

Not to say that there isn't a semantic benefit to being able to simply build
a SQL statement and set parameters over and over, making it easier on the
developer. But one of the primary motivations behing prepared statements was
simply performance, not necessarily semanatics, so I'm curious if a CL based
SQL layer will actually be able to benefit from prepared statements at the
performance level.

Regards,

Will Hartung
(willh@msoft.com)

Will Hartung

2004-05-18, 3:42 pm

"Zach Beane" <xach@xach.com> wrote in message
news:m37jv9or00.fsf@unnamed.xach.com...
> asimon@math.bme.hu (Simon András) writes:
>
>
> 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
(willh@msoft.com)

D. Richard Hipp

2004-05-18, 3:42 pm

"Kevin M. Rosenberg" <kevin@rosenberg.net> wrote in message news:<slrncajv98.i6j.kevin@tiger.med-info.com>...
>
> Prepared statements are a planned feature after the upcoming 3.0
> release. The goal of this release is full backward CommonSQL
> compatibility as well as major documentation revision.
>
> [...] SQLite does not support prepared statements.
>

Support for prepared statements was added to SQLite on 2003-Aug-06
and released with version 2.8.7 on 2003-Dec-04.

Kevin M. Rosenberg

2004-05-18, 6:31 pm

On 2004-05-18, D. Richard Hipp <drh@hwaci.com> wrote:
> Support for prepared statements was added to SQLite on 2003-Aug-06
> and released with version 2.8.7 on 2003-Dec-04.

Thanks for the notice, it'll be nice to support them as well in CLSQL.

--
Kevin Rosenberg
kevin@rosenberg.net

Dmitri Ivanov

2004-05-19, 12:32 pm

Hello Will,
"Will Hartung" <willh@msoft.com> wrote:

WH> | ...snip...|
WH> For ODBC, it's not really important as the actual back end ODBC
WH> driver does all of the work for the DB anyway, so who know what
WH> it's actually doing.
WH>
WH> Also, most of the Lisp SQL interfaces do the same thing,
WH> essentially recreating the complete SQL ASCII statement for each
WH> request.

Recreating an SQL statement is not always possible due to the maximum length
limitation imposed by the DBMS or driver. The pathological case is when the
SQL statement for passing binary data cannot be constructed at all due to
the lack of the corresponding data type text representation.

WH> On the other hand, for an actual binary SQL protocol (ala Oracle
WH> OCI), then you need to marshall the binary data into and out of the
WH> Lisp process anyway, so I dunno if even then we'd get much of a
WH> performance benefit.
WH>
WH> Not to say that there isn't a semantic benefit to being able to
WH> simply build a SQL statement and set parameters over and over,
WH> making it easier on the developer. But one of the primary
WH> motivations behing prepared statements was simply performance, not
WH> necessarily semanatics, so I'm curious if a CL based SQL layer will
WH> actually be able to benefit from prepared statements at the
WH> performance level.

The answer is 'Yes'. The following rude benchmarks come from testing YSQL on
LW over the Access ODBC driver:

;; Without preparing
(time
(dotimes (i 1000)
(sql:with-transaction ()
(sql:insert-records :into [tab2]
:attributes '([id] [text2_])
:values (list i (format nil "~A" (random 1000)))))))
; user time = 5.015
; system time = 0.703
; Elapsed time = 0:00:06
; Allocation = 3887584 bytes standard / 5264677 bytes fixlen
; 0 Page faults

;; With preparing
(time
(sql:with-prepared-statement (stmt [insert :into [tab2]
:attributes '([id] [text2_])
:values '([? :type integer] [? :type string])])
(sql:with-transaction ()
(dotimes (i 1000)
(sql:execute-command stmt
:values (list i (format nil "~A" (random 1000))))))))
; user time = 0.890
; system time = 0.093
; Elapsed time = 0:00:01
; Allocation = 647984 bytes standard / 1573990 bytes fixlen
; 0 Page faults

FWIW, the example below demonstrates the syntactical sugar for dealing with
parameters in YSQL.

(setq pa (sql:prepare-statement
[insert :into [tab]
:attributes '([Text_] [Boolean_] [Currency_] [DateTime_]
[Byte_] [Short_] [Long_] [Single_] [Double_])
:values '([? :name text_ :type string]
[? :name boolean_ :type :boolean]
[? :name Currency_ :type :numeric]
[? :name DateTime_ :type :timestamp]
[? :name Byte_ :type unsigned-byte]
[? :name Short_ :type (integer -32767 32767)]
[? :name Long_ :type integer]
[? :name Single_ :type single-float]
[? :name Double_ :type double-float]) ]
:database a))
(sql:execute-command pa
:values `("From prepared" 0 111.11 ,(get-universal-time)
255 222 3333 4.44 55.5555))
--
Sincerely,
Dmitri Ivanov
lisp.ystok.ru

sirola@fisica.unige.it

2004-05-19, 12:32 pm

>>>>> "Kevin" == Kevin M Rosenberg <kevin@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

sirola@fisica.unige.it

2004-05-27, 11:44 am

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

Sponsored Links