From: Kevin M. Rosenberg
Subject: Re: CLSQL - feature request?
Date: 
Message-ID: <slrncajv98.i6j.kevin@tiger.med-info.com>
On 2004-05-18, ······@fisica.unige.it <······@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

From: D. Richard Hipp
Subject: Re: CLSQL - feature request?
Date: 
Message-ID: <67125085.0405181043.3ce6ae18@posting.google.com>
"Kevin M. Rosenberg" <·····@rosenberg.net> wrote in message news:<····················@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.
From: Kevin M. Rosenberg
Subject: Re: CLSQL - feature request?
Date: 
Message-ID: <slrncakplj.387.kevin@tiger.med-info.com>
On 2004-05-18, D. Richard Hipp <···@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
·····@rosenberg.net
From: Will Hartung
Subject: Re: CLSQL - feature request?
Date: 
Message-ID: <2gv34pF767vnU1@uni-berlin.de>
"Kevin M. Rosenberg" <·····@rosenberg.net> wrote in message
·························@tiger.med-info.com...
> On 2004-05-18, ······@fisica.unige.it <······@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
(·····@msoft.com)
From: Dmitri Ivanov
Subject: Re: CLSQL - feature request?
Date: 
Message-ID: <c8eufp$1mh9$1@news.aha.ru>
Hello Will,
"Will Hartung" <·····@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
From: ······@fisica.unige.it
Subject: Re: CLSQL - feature request?
Date: 
Message-ID: <871xlh3i0v.fsf@statpro.com>
Hi Kevin,
first of all, thanks a lot for your quick reply,

>>>>> "Kevin" == Kevin M Rosenberg <·····@rosenberg.net> writes:

    >> 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,
    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
From: Kevin M. Rosenberg
Subject: Re: CLSQL - feature request?
Date: 
Message-ID: <slrncakius.v2n.kevin@tiger.med-info.com>
On 2004-05-18, ······@fisica.unige.it <······@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
·····@rosenberg.net
From: ······@fisica.unige.it
Subject: Re: CLSQL - feature request?
Date: 
Message-ID: <87fz9xha5o.fsf@statpro.com>
>>>>> "Kevin" == Kevin M Rosenberg <·····@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.
From: ······@fisica.unige.it
Subject: Re: CLSQL - feature request?
Date: 
Message-ID: <87vfit23en.fsf@statpro.com>
Hi Kevin,
first of all, thanks a lot for your quick reply,

>>>>> "Kevin" == Kevin M Rosenberg <·····@rosenberg.net> writes:

    >> 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,
    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