DBI v2 - The Plan and How You Can Help [DRAFT]

[Tim Bunce]

[I'm about to post this to dbi-announce, dbi-users, and perl6-language.
I'm posting this as a draft here first. I'd appreciate any comments
you may have. Any at all. Thanks. -- Tim.]

Once upon a time I said:

http://groups-beta.google.com/group/perl.dbi.users/msg/caf189d7b404a003?dmode=source&hl=en

and wrote

http://search.cpan.org/~timb/DBI/Roadmap.pod

which yielded:

https://donate.perlfoundation.org/index.pl?node=Fund+Drive+Details&selfund=102

(A little over $500 of that I effectively put in myself.)

My *sincere* thanks to all those who donated to the fund, especially
individuals. I had hoped for more corporate response with less from
individuals and I'm touched by the personal generosity shown.

I've not drawn any money from it yet and doubt that I will myself.
(I'm considering suggesting that the Perl Foundation make payments
from the fund to people making specific contributions to the DBI.
I'm thinking especially of work on a comprehensive test harness.
But I'll see how the developments below pan out before making
specific arrangements.)


So, that lead to:

http://groups-beta.google.com/group/perl.dbi.dev/browse_frm/thread/ef14a9fc0a37441f/fb8fe20a86723da0#fb8fe20a86723da0

Which sums up fairly well where I'm at: DBI v1 will rumble on for Perl 5
and DBI v2 will be implemented for Perl 6.


At this point I'd like to make a slight digression to highlight the
amazing work going on in the Perl 6 community at the moment.
Especially Autrijus' Pugs project which has brought Perl 6 to life.
Literally. Take a look at:

http://pugscode.org/talks/yapc/slide1.html
http://use.perl.org/~autrijus/journal

and especially:

http://use.perl.org/~autrijus/journal/24898

Yes, that really is Perl 6 code using the DBI being executed by Pugs.

That's great, and I was truly delighted to see it because it takes the
pressure off the need to get a DBI working for Perl 6 - because it
already is working for Perl 6. At least for Pugs. (The Ponie project
is also likely to provide access to Perl 5 DBI from Perl 6 by enabling
future versions of Perl 5 to run on Parrot.)


I have recently come to an arrangement that will enable me to put some
worthwhile development time into DBI (still very much part-time, but
enough to give it focus and move forward).

My initial goals are:

1. to work on a more detailed specification for the DBI v2 API that
takes advantage of appropriate features of Perl 6.

2. to work on a more detailed specification for the DBDI API
http://groups-beta.google.com/group/perl.perl6.internals/msg/cfcbd9ca7ee6ab4

3. to work on tools to automate building Parrot NCI interfaces to
libraries (such as database client libraries, obviously :)

But I'm hoping you'll join in and help out.

I've kept an eye on Perl 6 and Parrot developments but I'm no expert in
either. What I'd like *you* to do is make proposals (ideally fairly
detailed proposals, but vague ideas are okay) for what a Perl 6 DBI API
should look like.

Keep in mind that the role of the DBI is to provide a consistent
interface to databases at a fairly low level. To provide a *foundation*
upon which higher level interfaces (such as Class::DBI, Tangram, Alzabo
etc. in Perl 5) can be built.

So, if you have an interest in the DBI and Perl 6, put your thinking
cap on, kick back and dream a little dream of how the DBI could be.
How to make best use of the new features in Perl 6 to make life easier.

Then jot down the details and email them to me.

I'm about to fly off for two weeks vacation (in a few hours), blissfully
absent of any hi-tech gear beyond a mobile phone. When I get back I'll
gather up your emails and try to distill them into a coherent whole.

Have fun!

Tim.

[Sam Tregar]

Post by Tim Bunce
http://groups-beta.google.com/group/perl.dbi.users/msg/caf189d7b404a003?dmode=source&hl=en

Minor note: these URLs are too long to easily copy and paste. Maybe
use a URL shortening sevrice like tinyurl.com.

Post by Tim Bunce
I've not drawn any money from it yet and doubt that I will myself.
(I'm considering suggesting that the Perl Foundation make payments
from the fund to people making specific contributions to the DBI.
I'm thinking especially of work on a comprehensive test harness.
But I'll see how the developments below pan out before making
specific arrangements.)

How much development will $500 buy? It seems like the funding drive
was a failure. Maybe skip talking about it or move it to a separate
email? (Yes, I'm treating this like marketting.)

Post by Tim Bunce
Which sums up fairly well where I'm at: DBI v1 will rumble on for Perl 5
and DBI v2 will be implemented for Perl 6.

My first reaction to this was not good, mostly because I expect Perl 6
to take a long time to reach a state when I can use it for real work.
However, on further consideration I think this is a reasonable
approach. Perl 5 doesn't need a DBI 2 - DBI 1 is working great!
Since Perl 6 will need a new DBI then it might as well be a better
one.

Post by Tim Bunce
So, if you have an interest in the DBI and Perl 6, put your thinking
cap on, kick back and dream a little dream of how the DBI could be.
How to make best use of the new features in Perl 6 to make life easier.
Then jot down the details and email them to me.

Well, since you asked, my list:

- Asynchronous queries (coroutines? threads?)

- High-level support for cursors

- Ditch magic tied attributes and route all access through method
calls. This would make sub-classing (and programming) DBI easier,
in my opinion.

- Default RaiseError to 1 (pretty minor but I see a lot of pain
related to missed errors)

- Integrated transactions and Perl 6 try/catch semantics? It seems
like they're often used to address the same problem but I'm not
sure what the right integration is.

-sam

[Dean Arnold]

Post by Sam Tregar

Post by Tim Bunce
I've not drawn any money from it yet and doubt that I will myself.
(I'm considering suggesting that the Perl Foundation make payments
from the fund to people making specific contributions to the DBI.
I'm thinking especially of work on a comprehensive test harness.
But I'll see how the developments below pan out before making
specific arrangements.)

How much development will $500 buy? It seems like the funding drive
was a failure. Maybe skip talking about it or move it to a separate
email? (Yes, I'm treating this like marketting.)

Er, marketting in what way ? Developing DBI/DBDs costs time & resources.
Many major for-profit orgs are leveraging
DBI (and Perl in general) to make lots (and I again mean *lots*)
of money. Asking them to pony up some modest contributions
(or at least staff time) *for their own benefit and security*
doesn't seem like "marketting" any more than this weekend's Live 8
concert is "marketting" for starving children in Africa (ok, maybe
thats not a PC metaphor, but hopefully it gets the point across).
The fact they choose to "live off the fat of the land" is more than
a bit frustrating. If anything, perhaps DBI and DBD authors should
use a MySQL type dual license to cover the development/support costs.

Post by Sam Tregar

Post by Tim Bunce
Which sums up fairly well where I'm at: DBI v1 will rumble on for Perl 5
and DBI v2 will be implemented for Perl 6.

My first reaction to this was not good, mostly because I expect Perl 6
to take a long time to reach a state when I can use it for real work.
However, on further consideration I think this is a reasonable
approach. Perl 5 doesn't need a DBI 2 - DBI 1 is working great!

Well, maybe.

While Perl5, DBI, and threading may never be able to mix properly,
it would be nice to be able to exploit them in
large scale/complex apps, which I fear Perl6 may not be capable of
for 2+ years. And Perl5 will live on long after Perl6 has fully
ripened.

Post by Sam Tregar
- Asynchronous queries (coroutines? threads?)

Threads. If you've ever done much Java/JDBC work, you'll
realize how much simpler a solution to async it is.
(Ignoring the rest of Java/JDBC's undesirable traits)

Post by Sam Tregar
- High-level support for cursors

Seconded.

Post by Sam Tregar
- Ditch magic tied attributes and route all access through method
calls. This would make sub-classing (and programming) DBI easier,
in my opinion.

I'd vote against "ditch", maybe provide accessor/mutator i/fs
for those that desire them. Some of us still prefer a declarative
approach. (In fact, I'd vote for a fully declarative i/f
to *everything*, but that would likely scare the OO crowd away,
it certainly keeps them away from SQL::Preproc !^)

Post by Sam Tregar
- Default RaiseError to 1 (pretty minor but I see a lot of pain
related to missed errors)

I'd think the native exception handling would be the proper paradigm.
At which point things like RaiseError, PrintError, HandleError, etc
aren't really needed.

Post by Sam Tregar
- Integrated transactions and Perl 6 try/catch semantics? It seems
like they're often used to address the same problem but I'm not
sure what the right integration is.

Not certain what you mean by "integrated transactions" ?
I'd suggest a peek at JDBC. An explicit indication of "inside transaction"
or "outside transaction" would be nice.

Post by Sam Tregar
-sam

I think some of the following have been raised before, but to reiterate:

1. A standard conformance test suite for DBDs: a very big
(and likely mundane) chore, but really needed/valuable.

2. Full thread safe support.

3. A std. i/f for multistatement result sets.

4. Std. 2PC i/f (XA) ?

5. A better set of metadata for DBDs to report the functionality
they support. E.g., today, the only way to find out if a DBD
is threadsafe (at runtime) seems to be try it and hope for the best.

6. Std. i/f support for exotic datatypes (intervals, UDTs, ARRAY, etc.)

7. Better docs for DBD developers (hopefully PDBI2E will address this ?)

8. (I know I've argued against this in the past, but I've
had a change of heart) DBI inclusion as a Perl CORE module. Database
access is too prevalent these days for it to continue as an afterthought,
and it would likely increase test coverage/exposure.

Now, if only I had time to pitch and resolve some of these (tho I
may, of neccesity, help solve Perl5 thread issues in the near future).

My 0.02,

Dean Arnold
Presicient Corp.

[Sam Tregar]

Post by Dean Arnold
Er, marketting in what way ?

I see the email as an attempt to attract new developers to the
project. Hence it is, in some respects, an attempt to "market" DBI v2
to developers.

Post by Dean Arnold
Developing DBI/DBDs costs time & resources. Many major for-profit
orgs are leveraging DBI (and Perl in general) to make lots (and I
again mean *lots*) of money. Asking them to pony up some modest
contributions (or at least staff time) *for their own benefit and
security* doesn't seem like "marketting" any more than this
weekend's Live 8 concert is "marketting" for starving children in
Africa (ok, maybe thats not a PC metaphor, but hopefully it gets the
point across). The fact they choose to "live off the fat of the
land" is more than a bit frustrating. If anything, perhaps DBI and
DBD authors should use a MySQL type dual license to cover the
development/support costs.

Relax. I'm just suggesting that Tim not talk about a fund drive that
more-or-less failed when trying to drum up volunteers for the new
project. It will be obvious to everyone who reads this that $500
isn't enough to help in a significant way. That may serve to subtract
from the otherwise positive tone of his plea.

-sam

[Dean Arnold]

Post by Sam Tregar

Post by Dean Arnold
Er, marketting in what way ?

I see the email as an attempt to attract new developers to the
project. Hence it is, in some respects, an attempt to "market" DBI v2
to developers.

<My rant redacted.>

Post by Sam Tregar
Relax. I'm just suggesting that Tim not talk about a fund drive that
more-or-less failed when trying to drum up volunteers for the new
project. It will be obvious to everyone who reads this that $500
isn't enough to help in a significant way. That may serve to subtract
from the otherwise positive tone of his plea.
-sam

[ I'll take the role of "bad cop" here...
I don't intend this as personal attack.
Fortunately, Tim's on vacation, so he can dress me down
after he gets back. ]

These forums are intended to ask for, and provide assistance to,
users and developers of the Perl DBI. One way (a very
important way) to provide that assistance is for those who
profit the most from the Perl DBI,
to provide funding and/or other resources. If it's unseemly
for Mssr. Bunce to ask for community assistance here (esp. when
so many ask for assistance from *him*), then where should he ask ?
If the issue is the perceived "shaming", I can only posit that
asking nicely obviously didn't work.

This issue needs to be addressed. Those who profit from DBI need
to be made aware that resources are needed to continue to
provide a quality product, if only so the websites that
provide their profits don't start succumbing to code rot.

My rant is intended more as incitement to the community at large
(the "silent majority").
If you work for an org that relies on DBI to keep the customers
coming thru the doors (or URLs, as the case may be), you need to
elevate this issue to your management and get some commitment from
them to help out, if only in a "Googlesque" manner (i.e.,
letting some staff contribute 10-20% of their paid time
to the effort).

Its all well and good for us to give Tim a "honey-do" list
for DBIv2. Whats needed is some serious commitment from
developers, and esp. users who apparently feel entitled to the
fruits of Tim's (and others) labors. After all, this isn't about
maintaining yet another module that parses zipcodes. We're talking
about the maintenance and development of a fundamental
component of enterprise infrastructure.

Now I'll take my lithium and go watch fireworks.

Dean

[Sam Tregar]

Post by Dean Arnold

Post by Sam Tregar
Relax. I'm just suggesting that Tim not talk about a fund drive that
more-or-less failed when trying to drum up volunteers for the new
project. It will be obvious to everyone who reads this that $500
isn't enough to help in a significant way. That may serve to subtract
from the otherwise positive tone of his plea.

These forums are intended to ask for, and provide assistance to,
users and developers of the Perl DBI. One way (a very
important way) to provide that assistance is for those who
profit the most from the Perl DBI,
to provide funding and/or other resources. If it's unseemly
for Mssr. Bunce to ask for community assistance here (esp. when
so many ask for assistance from *him*), then where should he ask ?
If the issue is the perceived "shaming", I can only posit that
asking nicely obviously didn't work.

I didn't mean to imply that asking for money was a bad idea. I think
it's a fine idea. I just didn't think that was the main point of the
email. Here's the message I got from that email:

There's a new project starting that needs help. I tried to fund it
but we only got $500, and I'm not sure how to spend it. The project
will produce a new version of DBI for Perl 6. Send in your
suggestions and join the project!

I think it would be a stronger message, in a marketing sense, like
this:

There's a new project starting that needs help. The project will
produce a new version of DBI for Perl 6. Send in your suggestions
and join the project!

That's it.

-sam

[Ronald J Kimball]

Post by Sam Tregar
I didn't mean to imply that asking for money was a bad idea. I think
it's a fine idea. I just didn't think that was the main point of the
There's a new project starting that needs help. I tried to fund it
but we only got $500, and I'm not sure how to spend it. The project
will produce a new version of DBI for Perl 6. Send in your
suggestions and join the project!

https://donate.perlfoundation.org/index.pl?node=Fund+Drive+Details&selfund=1
02

Total Contributions: $1320.25

I suggest you go back and reread Tim's email. What he said was that he
donated a little over $500 himself, not that the total raised was $500.


Ronald

[Ronald J Kimball]

Post by Sam Tregar
I didn't mean to imply that asking for money was a bad idea. I think
it's a fine idea. I just didn't think that was the main point of the
There's a new project starting that needs help. I tried to fund it
but we only got $500, and I'm not sure how to spend it. The project
will produce a new version of DBI for Perl 6. Send in your
suggestions and join the project!

https://donate.perlfoundation.org/index.pl?node=Fund+Drive+Details&selfund=1
02

Total Contributions: $1320.25

I suggest you go back and reread Tim's email. What he said was that he
donated a little over $500 himself, not that the total raised was $500.


Ronald

[Dean Arnold]

Post by Dean Arnold

Post by Sam Tregar
- High-level support for cursors

Seconded.

<snip>

Post by Dean Arnold
1. A standard conformance test suite for DBDs: a very big
(and likely mundane) chore, but really needed/valuable.
2. Full thread safe support.
3. A std. i/f for multistatement result sets.
4. Std. 2PC i/f (XA) ?
5. A better set of metadata for DBDs to report the functionality
they support. E.g., today, the only way to find out if a DBD
is threadsafe (at runtime) seems to be try it and hope for the best.
6. Std. i/f support for exotic datatypes (intervals, UDTs, ARRAY, etc.)
7. Better docs for DBD developers (hopefully PDBI2E will address this ?)
8. (I know I've argued against this in the past, but I've
had a change of heart) DBI inclusion as a Perl CORE module. Database
access is too prevalent these days for it to continue as an afterthought,
and it would likely increase test coverage/exposure.

Forgot another item:

9. A standard resultset/rowset object. High level cursor support
(esp. scrollable cursors) probably requires it anyway, but
separating the resultset from the executable statement seems
to be a popular idea (see OLEDB/ADO/JDBC/etc....and, in some sense,
Class::DBI, for that matter)

Dean

[Eric]

Hi,

Is something like connection pooling considered outside the scope of
DBI? If not that would be a big one for me. Apache::DBI only goes so far.
I guess 2. would be a sensible requirement for this as well.

Thanks,

Eric

Post by Dean Arnold

Post by Dean Arnold

Post by Sam Tregar
- High-level support for cursors

Seconded.

<snip>

Post by Dean Arnold
1. A standard conformance test suite for DBDs: a very big
(and likely mundane) chore, but really needed/valuable.
2. Full thread safe support.
3. A std. i/f for multistatement result sets.
4. Std. 2PC i/f (XA) ?
5. A better set of metadata for DBDs to report the functionality
they support. E.g., today, the only way to find out if a DBD
is threadsafe (at runtime) seems to be try it and hope for the best.
6. Std. i/f support for exotic datatypes (intervals, UDTs, ARRAY, etc.)
7. Better docs for DBD developers (hopefully PDBI2E will address this ?)
8. (I know I've argued against this in the past, but I've
had a change of heart) DBI inclusion as a Perl CORE module. Database
access is too prevalent these days for it to continue as an afterthought,
and it would likely increase test coverage/exposure.

9. A standard resultset/rowset object. High level cursor support
(esp. scrollable cursors) probably requires it anyway, but
separating the resultset from the executable statement seems
to be a popular idea (see OLEDB/ADO/JDBC/etc....and, in some sense,
Class::DBI, for that matter)
Dean

[David Nicol]

Post by Dean Arnold

Post by Sam Tregar
- Asynchronous queries (coroutines? threads?)

Threads. If you've ever done much Java/JDBC work, you'll
realize how much simpler a solution to async it is.
(Ignoring the rest of Java/JDBC's undesirable traits)

A couple quarters ago I submitted a proposal to write a general
pragma delivering sugar to simply wrap any module with message-passing,
including worker process/thread pooling and testing against DBI to the
TPF, I guess the proposal has expired by now. Anyway this can be
solved in a general way that is larger than DBI, and get it solved in
DBI "for free."

Post by Dean Arnold
5. A better set of metadata for DBDs to report the functionality
they support. E.g., today, the only way to find out if a DBD
is threadsafe (at runtime) seems to be try it and hope for the best.

This is another problem that would better be solved at a higher level than
for DBI exclusively. There are other projects with plug-in architectures,
and there are meta.yaml files -- the problem turns into, who names the
functionalities and what are the standard names of the levels of support,
and back-documenting the massive base of CPAN modules that wouldn't
have function level listings in their metadata files. Support for
modules indicating
in a better fashion what versions of Perl they require would be very
closely related.

Maybe the next META.yaml comittee meeting might produce a document describing
a way for modules to identify the levels at which they support names
functionalities
and the next DBI standard could mandate a set of functionalities
that would have to be listed in conforming database drivers.

Whew!

[Dean Arnold]

Post by David Nicol

Post by Dean Arnold

Post by Sam Tregar
- Asynchronous queries (coroutines? threads?)

Threads. If you've ever done much Java/JDBC work, you'll
realize how much simpler a solution to async it is.
(Ignoring the rest of Java/JDBC's undesirable traits)

A couple quarters ago I submitted a proposal to write a general
pragma delivering sugar to simply wrap any module with message-passing,
including worker process/thread pooling and testing against DBI to the
TPF, I guess the proposal has expired by now. Anyway this can be
solved in a general way that is larger than DBI, and get it solved in
DBI "for free."

I'm already implementing such a wrapper for DBI (DBIx::Threaded);
not a pragma, and very specific to DBIv1, but hopefully it solves
at least 85-90% of the problem. (tho async cancel/abort isn't
solvable at this point)

BTW: the Pots::* modules already do what I *think* you're
proposing (again, not as pragmas, and Perl5 based)

Dean

[David Nicol]

I'm already implementing [a message-passing async] wrapper for DBI
(DBIx::Threaded); not a pragma, and very specific to DBIv1, but hopefully it solves
at least 85-90% of the problem. (tho async cancel/abort isn't
solvable at this point)
BTW: the Pots::* modules already do what I *think* you're
proposing (again, not as pragmas, and Perl5 based)
Dean

I'm going to forward you the Asynchronous pragma proposal off this list for your
consideration of the syntax. Elizabeth Mattijsen liked it, for what
that's worth.

I don't see what the advantage of using the Pots modules is over directly using
Thread::queue -- it doesn't appear to me to save any coding, although using
Pots modules would enforce some structure, which can be helpful.

[Darren Duncan]

Tim et al,

Following are some ideas I have for the new DBI, that were thought
about greatly as I was both working on Rosetta/SQL::Routine and
writing Perl 6 under Pugs. These are all language-independent and
should be implemented at the Parrot-DBI level for all Parrot-hosted
languages to take advantage of, rather than just in the Perl 6
specific additions. I believe in them strongly enough that they are
in the core of how Rosetta et al operates (partly released, partly
pending).

0. There were a lot of good ideas in other people's replies to this
topic and I won't repeat them here, for the most part.

1. Always use distinct functions/methods to separate the declaration
and destruction of a resource handle / object from any of its
activities. With a database connection handle, both the
open/connect() and close/disconnect() are $dbh methods; the $dbh
itself is created separately, such as with a DBI.new_connection()
function. With a statement handle, the prepare() is also a $sth
method like with execute() et al; the $sth itself is created
separately, such as with a $dbh.new_statement() method. If new
handle types are created, such as a separate one for cursors, they
would likewise be declared and used separately.

With this separation, you can re-use the resource handles more
easily, and you don't have to re-supply static descriptive
configuration details each time you use it, but rather only when the
handle is declared. At the very least, such static details for a
connection handle include what DBI implementor/driver module to use;
as well, these details include what database product is being used,
and locating details for the database, whether internet address or
local service name or on-disk file name and so on. This can
optionally include the authorization identifier / user name and
password, or those details can be provided at open() time instead if
they are likely to be variable.

2. Always separate out any usage stages that can be performed apart
from the database itself. This allows an application to do those
stages more efficiently, consuming fewer resources of both itself and
the database.

For example, a pre-forked Apache process can declare all of the
database and statement handles that it plans to use, and do as much
of the prepare()-type work that can be done internally as possible,
prior to forking; all of that work can be done just once, saving CPU,
and only one instance of it consumes RAM. All actual invocations of
a database, the open()/connect() and execute() happen after forking,
and at that point all of the database-involving work is consolidated.

Or even when you have a single process, most of the work you have to
do, including any SQL generation et al, can be more easily be
pre-performed and the results cached for multiple later uses. Some
DBI wrappers may do a lot of work with SQL generation et al and be
slow, but if this work is mainly preparatory, they can still be used
in a high-speed environment as that work tends to only need doing
once. Most of the prep work of a DBI wrapper can be done effectively
prior to ever opening the database connection.

3. Redefine prepare() and execute() such that the first is expressly
for activities that can be done apart from a database (and hence can
also be done for a connection handle that is closed at the time)
while all activities that require database interaction are deferred
to the second.

Under this new scheme, when a database has native prepared statements
support that you want to leverage, the database will be invoked to
prepare said statements the first time you run execute(), and then
the result of this is cached by DBI or the driver for all subsequent
execute() to use. In that case, any input errors detected by the
database will be thrown at execute() time regardless of their nature;
only input errors detected by the DBD module itself would be thrown
at prepare() time. (Note that module-caught input errors are much
more likely when the module itself is handling SQL in AST form,
whereas database-caught input errors are much more likely when SQL is
always maintained in the program as string form.) Note also that the
deferal to execute() time of error detection is what tends to happen
already with any databases that don't have native prepared statement
support or for whom the DBI driver doesn't use them; these won't be
affected by the official definition change.

Now I realize that it may be critically important for an application
to know at prepare() time about statically-determinable errors, such
as mal-formed SQL syntax, where error detection is handled just by
the database. For their benefit, the prepare()+execute() duality
could be broken up into more methods, either all used in sequence or
some alternately to each other, so users get their errors when they
want them. But regardless of the solution, it should permit for all
database-independent preparation to be separated out.

4. All host parameters should be named (like ":foo") rather than
positional (like "?"), meeting with the SQL:2003 standard. The named
format is a lot easier to use and flexible, making programmers a lot
less error prone, more powerful, and particularly more resource
efficient when the same parameter is conceptually used multiple times
in a SQL statement (it only has to be bound once). If anyone wants
to use positional format, it could easily be emulated on top of this.
Or, if native positional support is still important, then it should
be a parallel option that can be used at the same time as named in
any particular SQL statement. See the native API of SQLite 3 for one
example that (I believe) supports both in parallel. This also means
that execute() et al should take arguments in a hash rather than an
array.

5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being
applicable at once) are: 1. the DBI driver module to use; 2. the
internet server IP address or domain name and port; 3. the locally
defined server device socket; 4. the locally defined service (eg,
ODBC or SQL*Net) name; 5. the file system file name; 6. the file
system directory name; 7. some other detail if any for fully in-RAM
databases; 8. the authorization identifier / user name; 9. the
password; 10. some other authorization credential, or channel
encryption details, or whatever else; 11. what kind of database or
what database product is being used, if known. If the DBI driver
talks to a client-configurable DBI proxy server, then, it should be
possible to nest a set of the above settings (eg, as a hash-ref) as
one part of the main settings given to the proxy client.

6. DBI drivers should always be specified by users with their actual
package name, such as 'DBD::SQLite', and not some alternate or
abbreviated version that either leaves the 'DBD::' out or is spelled
differently. Similarly, the DBI driver loader should simply try to
load exactly the driver name it is given, without munging of any
type. This approach is a lot more simple, flexible and lacks the
cludges of the current DBI. DBI driver implementers can also name
their module anything they want, and don't have to name it 'DBD::*'.
A DBI driver should not have to conform to anything except a specific
API by which it is called, which includes its behaviour upon
initialization, invocation, and destruction.

7. Error conditions should *always* be thrown as exceptions by DBI;
no exception thrown means that the request succeeded, even if its
result was nothing/undef. This is a lot simpler to implement or use
than any alternative. If people don't like that, then some wrapper
should be employed to block the exceptions. Or, if it is really
important to have a non-exception alternative, then that should be an
alternative, with thrown exceptions being the default behaviour.

8. Split off the proxy server/client stuff into a separate
distribution; they are conceptually add-ons anyway and could benefit
from independent development. Split off any SQL parser utilities
(eg, SQL::Nano, SQL::Statement) into a separate distribution, since
only a small fraction of potential drivers would use them, and they
are better off to just require them separately. Split off all
bundled DBI drivers (DBD::File, etc) into separate distributions,
unless they exist soley to provide an example of how to make a DBI
driver and are not actually useful in themselves. The DBI
distribution should focus simply on defining an interface, and let
anything that will help with implementing the drivers to be optional
and separate.

9. As Sam Vilain suggested, prepare() type methods should accept both
SQL strings and any type of object as input, so that drivers have the
option to directly accept AST forms; particularly useful when the
drivers themselves would otherwise have to parse the SQL into an AST
anyway.

And now ...

Here's an example of some things that implementing some of the above
suggestions will let an application do (code may not compile as is):

method init($self) {
$self.db = DBI.new_connection( driver => 'DBD::SQLite', host => 'test' );

my $sth1 = $self.db.new_statement(
"select * from baz where abc = :bar or def = :bar" );
$sth1.prepare();
my $sth2 = $self.db.new_statement(
"insert into baz (abc, def) values (:p_abc, :p_def)" );
$sth2.prepare();

$self.routines = (
'get_all_baz' => -> ($bar) {
$sth1.execute( { bar => $bar } );
return $sth1.fetch_all_hashref();
},
'add_one_baz' => -> ($abc, $def) {
$sth2.execute( { p_abc => $abc, p_def => $def } );
},
);
}

method main($self) {
try {
$self.db.open( user => 'jane', pass => 'k34l5jr' );

try {
$self.routines.{'add_one_baz'}.('hello','world');

my $results = $self.routines.{'get_all_baz'}.('world');

my $sth3 = $self.db.new_statement(
"delete from baz where def = :foo" );
$sth3.prepare();
$sth3.execute( { foo => 'blarch' } );
};
$! and say "dag nabit!";

$self.db.close();
};
$! and say "dog gone!";
}

In the above example, only main() actually invokes a database; init()
does load the DBI driver, though. You can also invoke main() as many
times as you want, and you can run init() prior to forking without
trouble.

What I've said in this email is not exhaustive and I may add or amend
items later; but, its a good start. Feedback is welcome of course.

Thank you. -- Darren Duncan

[Sam Vilain]

3. Redefine prepare() and execute() such that the first is expressly for
activities that can be done apart from a database (and hence can also be
done for a connection handle that is closed at the time) while all
activities that require database interaction are deferred to the second.

That would be nice, but there are some DBDs for which you need the database
on hand for $dbh.prepare() to work. In particular, DBD::Oracle.

I think that what you are asking for can still work, though;

# this module creates lots of SQL::Statement derived objects, without
# necessarily loading DBI.
use MyApp::Queries <%queries>;

# not connect, so doesn't connect
my $db = DBI.new( :source("myapp") );

# prepare the objects as far as possible
my %sths;
for %queries.kv -> $query_id, $query_ast_or_template {
%sths{$query_id} = $db.prepare($query_ast_or_template);
}

# connect
$db.connect;

# now proceed as normal
my $sth = %sths<some_query_id>;
$sth.execute( :param("foo"), :this("that") );

So, effectively the prepare can happen at any time, and it's up to the
DBD to decide whether to actually do anything with it immediately or not.
ie, on Pg the STHs would be built before the DB is connected, and on Oracle
they are built the first time they are used (and then cached).

Now I realize that it may be critically important for an application to
know at prepare() time about statically-determinable errors, such as
mal-formed SQL syntax, where error detection is handled just by the
database. For their benefit, the prepare()+execute() duality could be
broken up into more methods, either all used in sequence or some
alternately to each other, so users get their errors when they want
them. But regardless of the solution, it should permit for all
database-independent preparation to be separated out.

OK, so we have these stages;

1. (optional) generate an AST from SQL
2. (optional) generate SQL string from an AST
3. generate a handle for the statement, sans connection
4. prepare handle for execution, with connection
5. execute statement

I think these all fit into;

1. SQL::Statement.new(:sql("..."));
2. $statement.as_sql;
3. $dbh.prepare($statement) or $dbh.prepare($statement, :nodb);
4. $dbh.prepare($statement) or $sth.prepare while connected
5. $sth.execute

In particular, I don't think that the DB driver should automatically
get a chance to interfere with SQL::Statement; if they want to do that,
then they should specialise SQL::Statement. IMHO.

Perhaps you have some other examples that don't fit this?

5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source".

I interpret this as asking that the detailed parameters to the DBI
connection are expanded into named options rather than simply bundled into
a string.

That, I agree with, and I guess it would be useful occasionally to be
able to specify all that rather than just setting it up once and labelling
those connection parameters with a "source" that comes from ~/.dbi.
Particularly for writing gui dialogs for interactive database utilities.

Either way, you don't want most applications dealing with this complexity
at all, really.

6. DBI drivers should always be specified by users with their actual
package name, such as 'DBD::SQLite', and not some alternate or
abbreviated version that either leaves the 'DBD::' out or is spelled
differently. Similarly, the DBI driver loader should simply try to load
exactly the driver name it is given, without munging of any type. This
approach is a lot more simple, flexible and lacks the cludges of the
current DBI. DBI driver implementers can also name their module
anything they want, and don't have to name it 'DBD::*'. A DBI driver
should not have to conform to anything except a specific API by which it
is called, which includes its behaviour upon initialization, invocation,
and destruction.

Is this useful?

I can't see a reason that the DBI.new() / DBI.connect() call shouldn't be
flexible in what it accepts;

$dbh = DBI.new( :driver<Rosetta> ); # means DBD::Rosetta
$dbh = DBI.new( :driver<Rosetta::Emulate::DBD> ); # specify full package
$dbh = DBI.new( :driver(Rosetta::Emulate::DBD) ); # pass type object
$dbh = DBI.new( :driver(DBD::SQLite.new(:foo<bar>)) ); # pass driver object

Sam.

[Darren Duncan]

Okay, considering that using the same name prepare() like this may
confuse some people, here is a refined solution that uses 3 methods
instead; please disregard any contrary statements that I previously
made:

# Opt 1: A user that wants the most control can do this (new feature):

my $sth1 = $dbh.compile( $sql_or_ast ); # always sans connection
$sth1.prepare(); # always with connection, even if DBD doesn't use it
$sth1.execute(); # always with connection

# Opt 2: If they want less control, they do this (same as old DBI):

my $sth2 = $dbh.prepare( $sql_or_ast ); # combines Opt 1's comp/prep
$sth2.execute(); # same as Opt 1

# Opt 3: Alternately, there is this (akin to my older suggestion):

my $sth3 = $dbh.compile( $sql_or_ast ); # same as Opt 1
$sth3.execute(); # combines Opt 1's prep/exec

# Opt 4: Even less control (akin to old DBI's "do"):

$dbh.execute( $sql_or_ast ); # combines Opt 1's comp/prep/exec

In this model, when you use just prepare() and execute(), they behave
identically to the old DBI, including that they require an open
connection. So no mystery there.

The new feature is if you decide to use compile(); you then give that
method the arguments you would have given to prepare(), and you
invoke prepare() on the result with no arguments; each DBD would
decide for itself how the work is divided between compile() and
prepare() with the limitation that compile() is not allowed to access
the database; ideally the DBD would place as much work there as is
possible, which would vary between Oracle/Pg/etc.

Invoking just compile() then execute() will cause the execute() to do
what prepare() normally does against a database, and cache the
prepared handle.

In option 4, I renamed the old DBI's do() to execute() for
consistency with the other examples; but this execute() is different
in that it caches the prepared statement handle. In any event, with
all 4 examples, execute() gives you the same result regardless of
what is called before it.

Post by Sam Vilain
In particular, I don't think that the DB driver should automatically
get a chance to interfere with SQL::Statement; if they want to do that,
then they should specialise SQL::Statement. IMHO.

I am operating under the assumption here that while the new DBI is
designed to effectively support wrapper modules, the wrapper modules
would also be altered from their current DBI-1-geared designs to
accomodate DBI-2.

But still, what do you mean by "interfere"?

Post by Sam Vilain

Post by Darren Duncan
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source".

I interpret this as asking that the detailed parameters to the DBI
connection are expanded into named options rather than simply bundled into
a string.
That, I agree with, and I guess it would be useful occasionally to be
able to specify all that rather than just setting it up once and labelling
those connection parameters with a "source" that comes from ~/.dbi.
Particularly for writing gui dialogs for interactive database utilities.

I see the act of storing all the data as a single string at any time
to be a messy affair to be avoided. The application doesn't have to
know about the complexity to pass around a hash of values any more
than it does with a string; but when the application wants to know
the details, dealing with a hash is easier.

Post by Sam Vilain
Either way, you don't want most applications dealing with this complexity
at all, really.

I am operating under the assumption that this system should work if
there are no external config files that the DBI/DBD would read, and
the application would provide that information; if its in a file, the
application would read it in, or would explicitly tell DBI where it
is. Or at least it should be possible for this to happen, even if a
DBD defaults to look in a default location when it doesn't get the
equivalent from the application.

Post by Sam Vilain

Post by Darren Duncan
6. DBI drivers should always be specified by users with their
actual package name, such as 'DBD::SQLite', and not some alternate
or abbreviated version that either leaves the 'DBD::' out or is
spelled differently. Similarly, the DBI driver loader should
simply try to load exactly the driver name it is given, without
munging of any type. This approach is a lot more simple, flexible
and lacks the cludges of the current DBI. DBI driver implementers
can also name their module anything they want, and don't have to
name it 'DBD::*'. A DBI driver should not have to conform to
anything except a specific API by which it is called, which
includes its behaviour upon initialization, invocation, and
destruction.

Is this useful?
I can't see a reason that the DBI.new() / DBI.connect() call shouldn't be
flexible in what it accepts;
$dbh = DBI.new( :driver<Rosetta> ); # means DBD::Rosetta
$dbh = DBI.new( :driver<Rosetta::Emulate::DBD> ); # specify full package
$dbh = DBI.new( :driver(Rosetta::Emulate::DBD) ); # pass type object
$dbh = DBI.new( :driver(DBD::SQLite.new(:foo<bar>)) ); # pass driver object

My main point here is that DBI should not have to know any details
about particular drivers that are written to it, except in a generic
sense that may apply to any driver. The driver should know about DBI
details, but the reverse should never be true.

Unless there is a design flaw in DBI, we should not have to update
that module just because a new driver came into existence whose name
has not yet been hard-coded into DBI.

See this block for example, from DBI.pm v1.48:

my $dbd_prefix_registry = {
ad_ => { class => 'DBD::AnyData', },
ado_ => { class => 'DBD::ADO', },
amzn_ => { class => 'DBD::Amazon', },
best_ => { class => 'DBD::BestWins', },
csv_ => { class => 'DBD::CSV', },
db2_ => { class => 'DBD::DB2', },
dbi_ => { class => 'DBI', },
dbm_ => { class => 'DBD::DBM', },
df_ => { class => 'DBD::DF', },
f_ => { class => 'DBD::File', },
file_ => { class => 'DBD::TextFile', },
ib_ => { class => 'DBD::InterBase', },
ing_ => { class => 'DBD::Ingres', },
ix_ => { class => 'DBD::Informix', },
jdbc_ => { class => 'DBD::JDBC', },
msql_ => { class => 'DBD::mSQL', },
mysql_ => { class => 'DBD::mysql', },
mx_ => { class => 'DBD::Multiplex', },
nullp_ => { class => 'DBD::NullP', },
odbc_ => { class => 'DBD::ODBC', },
ora_ => { class => 'DBD::Oracle', },
pg_ => { class => 'DBD::Pg', },
proxy_ => { class => 'DBD::Proxy', },
rdb_ => { class => 'DBD::RDB', },
sapdb_ => { class => 'DBD::SAP_DB', },
solid_ => { class => 'DBD::Solid', },
sponge_ => { class => 'DBD::Sponge', },
sql_ => { class => 'SQL::Statement', },
syb_ => { class => 'DBD::Sybase', },
tdat_ => { class => 'DBD::Teradata', },
tmpl_ => { class => 'DBD::Template', },
tmplss_ => { class => 'DBD::TemplateSS', },
tuber_ => { class => 'DBD::Tuber', },
uni_ => { class => 'DBD::Unify', },
xbase_ => { class => 'DBD::XBase', },
xl_ => { class => 'DBD::Excel', },
yaswi_ => { class => 'DBD::Yaswi', },
};

I mean, what's up with that? I assume DBI 1 has this for legacy app
backwards compatability, but DBI version 2 should never have to
accomodate such abhorrent computer programming practices in its core.
By having users specify the full driver class name, DBI won't have to
do any such explicit mapping.

By the way, most driver names are quite short already, so its not
like abbreviations are necessary.

-- Darren Duncan

[Sam Vilain]

Post by Darren Duncan
Okay, considering that using the same name prepare() like this may
confuse some people, here is a refined solution that uses 3 methods

I think I'm beginning to like it.

Allow me to suggest one or two further refinements...

Post by Darren Duncan
my $sth1 = $dbh.compile( $sql_or_ast ); # always sans connection
$sth1.prepare(); # always with connection, even if DBD doesn't use it
$sth1.execute(); # always with connection

To me, the "compiled" form of the STH is related to the driver, but
re-usable between connections; you should be able to use something like;

my $sth1 = DBD::SQLite.compile( $sql_or_ast );
$sth1 = DBI.compile( :statement($sql_or_ast), :driver<SQLite> );

This would give you a STH which is divorced from the actual DB connection
instance. Because you constructed it like this, without reference to a
(possibly unconnected) connection object, then $sth1.prepare is not
available.

You'd then need to use something like;

$sth1.prepare($dbh);
$dbh.prepare($sth1);

Note I also think what you wrote should work, too.

Post by Darren Duncan
The new feature is if you decide to use compile(); you then give that
method the arguments you would have given to prepare(), and you invoke
prepare() on the result with no arguments; each DBD would decide for
itself how the work is divided between compile() and prepare() with the
limitation that compile() is not allowed to access the database; ideally
the DBD would place as much work there as is possible, which would vary
between Oracle/Pg/etc.

Agreed.

Post by Darren Duncan

Post by Sam Vilain
In particular, I don't think that the DB driver should automatically
get a chance to interfere with SQL::Statement; if they want to do that,
then they should specialise SQL::Statement. IMHO.

I am operating under the assumption here that while the new DBI is
designed to effectively support wrapper modules, the wrapper modules
would also be altered from their current DBI-1-geared designs to
accomodate DBI-2.
But still, what do you mean by "interfere"?

Well, when you parse the statement into an AST, the flavour of SQL will
affect how it is parsed and what is allowed. Eg, Oracle has significant
features in some comments (query hints). It also has quirky and somewhat
useless keywords like CONNECT BY.

So, when you ask a DBH connected to a driver to parse something, then it
will use that driver's SQL dialect, if one exists, but I still want to be
able to deal with SQL ASTs without implying a SQL flavour.

Post by Darren Duncan

Post by Sam Vilain
Either way, you don't want most applications dealing with this complexity
at all, really.

I am operating under the assumption that this system should work if
there are no external config files that the DBI/DBD would read, and the
application would provide that information; if its in a file, the
application would read it in, or would explicitly tell DBI where it is.
Or at least it should be possible for this to happen, even if a DBD
defaults to look in a default location when it doesn't get the
equivalent from the application.

Absolutely, that must work. But it would still be nice to be able to
config this without digging through the application to see where the
password is written.

Post by Darren Duncan
Unless there is a design flaw in DBI, we should not have to update that
module just because a new driver came into existence whose name has not
yet been hard-coded into DBI.
my $dbd_prefix_registry = {
ad_ => { class => 'DBD::AnyData', },

[...]

Post by Darren Duncan
yaswi_ => { class => 'DBD::Yaswi', },
};
I mean, what's up with that? I assume DBI 1 has this for legacy app
backwards compatability, but DBI version 2 should never have to
accomodate such abhorrent computer programming practices in its core.

Such a great word, abhorrent. So fitting for this case. It sure does
look like an (over&premature&misguided)-optimisation to avoid using the
full module name in an internal hash or something like that. But then
maybe (I&we&none(Gaia)) are missing some context there.

Sam.

[Darren Duncan]

Post by Sam Vilain
I think I'm beginning to like it.
Allow me to suggest one or two further refinements...

Post by Darren Duncan
my $sth1 = $dbh.compile( $sql_or_ast ); # always sans connection
$sth1.prepare(); # always with connection, even if DBD doesn't use it
$sth1.execute(); # always with connection

FYI, I'm not stuck on the name 'compile'; the method could be called
something else. In fact, I still think 'prepare' is more broadly
descriptive considering the wide range of things that could be
happening inside that method; I see it as ultimately getting ready
for the execute() anyway. The problem is that the word 'prepare' is
commonly associated with something different, so we're stuck having
to find a different word.

Post by Sam Vilain
To me, the "compiled" form of the STH is related to the driver, but
re-usable between connections; you should be able to use something like;
my $sth1 = DBD::SQLite.compile( $sql_or_ast );
$sth1 = DBI.compile( :statement($sql_or_ast), :driver<SQLite> );
This would give you a STH which is divorced from the actual DB connection
instance.

Yes, that is true. However, I like for all application calls to go
through the Interface module (DBI) since that is what the Interface
is for. It is also more practical to go by way of the DBI module
because it can do things for you like certain kinds of validation on
application input and driver output, so the application or driver
respectively doesn't have to do those tests for robustness. Each DBI
driver can worry less about that its input is correct and focus more
on its actual work. (FYI, a common input/output validation is one of
the main things that the 'Rosetta' module does for its own drivers
and users. More so in next release v0.46.)

Post by Sam Vilain
Because you constructed it like this, without reference to a
(possibly unconnected) connection object, then $sth1.prepare is not
available.

I wouldn't worry about that; the proposed DBI would elegantly handle
(throw an exception) attempts to invoke methods that depend on an
open connection when there is none. People who like to check first
will also have a $dbh.is_open() method available to them. I also see
the situation as no worse than the current DBI v1 where you can
invoke such things as fetchrow_arrayref() on a $sth that hasn't been
executed yet.

Oh, and I have another DBI v2 suggestion to add, also following Rosetta design:

1. A $sth should not contain any methods for fetching the result of
an executed statement; rather, execute() should return an object when
successful that represents its result; you invoke fetching methods on
that object. For example:

my $rlh = $sth->execute();
my $rowset = $rlh->fetchrow_arrayref();

This approach is a lot more flexible.

Post by Sam Vilain
Well, when you parse the statement into an AST, the flavour of SQL will
affect how it is parsed and what is allowed. Eg, Oracle has significant
features in some comments (query hints). It also has quirky and somewhat
useless keywords like CONNECT BY.
So, when you ask a DBH connected to a driver to parse something, then it
will use that driver's SQL dialect, if one exists, but I still want to be
able to deal with SQL ASTs without implying a SQL flavour.

You still can. The point of an "abstract syntax tree" is that the
original SQL dialect isn't very important to what it contains (and
this is how SQL::Routine is); by contrast, a "concrete syntax tree"
cares very much about the original syntax and preserves it to the
last detail. I don't see a problem here.
...
Except that those AST-like-modules which embed raw SQL fragments
(like where-clauses) as a matter of course aren't actually that
abstract and could pose a problem; lots of DBI wrappers are this way,
unfortunately (but not mine).

Post by Sam Vilain

Post by Darren Duncan
I am operating under the assumption that this system should work if
there are no external config files that the DBI/DBD would read, and
the application would provide that information; if its in a file,
the application would read it in, or would explicitly tell DBI
where it is. Or at least it should be possible for this to happen,
even if a DBD defaults to look in a default location when it
doesn't get the equivalent from the application.

Absolutely, that must work. But it would still be nice to be able to
config this without digging through the application to see where the
password is written.

Er, I hope you didn't interpret this as the application source code.
My point was that the database connection details can be part of the
general application configuration file, which is still data and not
code. This way, each application can have distinct database settings
if it wants to.

Post by Sam Vilain
Such a great word, abhorrent. So fitting for this case. It sure does
look like an (over&premature&misguided)-optimisation to avoid using the
full module name in an internal hash or something like that. But then
maybe (I&we&none(Gaia)) are missing some context there.

Well, I have also decided that I can accept one specific type of
flexability on the 'whole module name' thing. That is, if we assume
that DBI drivers will commonly have 'DBD::' at the start of their
names, users can optionally leave that prefix off, but they still
have to spell the rest of the name out in full and with the correct
letter case. In this case, when trying to load a driver, DBI would
first try the pristine module name it was given, and if a module
fails to load by that name, then it will try adding 'DBD::' to the
front as an alternative; failing those two, it will croak. But the
point is, aside from the text 'DBD::', which is reasonable as it is a
general case, there are no hard coded driver names in DBI.

Another suggestion:

2. Unless there is a strong impetus for there being a separate root
namespace for DBI drivers, I suggest a better naming scheme would be
to put everything under DBI:: instead. For example,
DBI::Driver::SQLite or DBI::D::SQLite, or DBI::DBD::SQLite. If this
idea is adopted, then that auto-prefix thing I previously mentioned
would be changed accordingly. I think that having the standard
driver namespace being outside DBI::* is an antiquated notion that
bears little resemblence to what is standard in most frameworks. All
this said, I'm not stuck on a change here, so do as you will.

-- Darren Duncan

[Adam Kennedy]

Post by Darren Duncan
4. All host parameters should be named (like ":foo") rather than
positional (like "?"), meeting with the SQL:2003 standard. The named
format is a lot easier to use and flexible, making programmers a lot
less error prone, more powerful, and particularly more resource
efficient when the same parameter is conceptually used multiple times in
a SQL statement (it only has to be bound once). If anyone wants to use
positional format, it could easily be emulated on top of this. Or, if
native positional support is still important, then it should be a
parallel option that can be used at the same time as named in any
particular SQL statement. See the native API of SQLite 3 for one
example that (I believe) supports both in parallel. This also means
that execute() et al should take arguments in a hash rather than an array.

Yes, native positional support is still important.

positions make it very easy to do SQL math.

To express it in overly simplistic code

$foo = [ "a = ?", "foo" ];
$bar = [ "b = ?", "bar" ];

$baz = "$foo and $bar";
# $baz now is [ "a = ? and b = ?", "foo", "bar" ];

Bearing mind a situation with an arbitrary number and complexity of
these sql fragments to be added together, doing this sort of thing using
named placeholders would be a nightmare.

and we'd just end up with every second person implementing a ? to :p1,
:p2 scheme in their database layers. If you want positional
placeholders, it needs to be an additional feature, not a replacement
for positional placeholders.

[Sam Vilain]

Post by Adam Kennedy
Yes, native positional support is still important.
positions make it very easy to do SQL math.
To express it in overly simplistic code
$foo = [ "a = ?", "foo" ];
$bar = [ "b = ?", "bar" ];
$baz = "$foo and $bar";
# $baz now is [ "a = ? and b = ?", "foo", "bar" ];
Bearing mind a situation with an arbitrary number and complexity of
these sql fragments to be added together, doing this sort of thing using
named placeholders would be a nightmare.

Interesting. You are demonstrating reasons it is useful to deal with
SQL fragments in non-templated form.

In Tangram what you describe is written like this;

my $table = $storage->remote("Table");
my $foo = $table->{a} == "foo";
my $bar = $table->{b} == "bar";
my $baz = $foo & $bar;

$foo expresses "a = 'foo'", $bar "b = 'bar'", and baz
"a = 'foo' AND b = 'bar'". Currently this is not done with placeholders.

--- Full demo script:

use Tangram;
use YAML;

my $schema = Tangram::Schema->new(Load <<YAML);
classes:
MyTable:
fields:
string: [ a, b ]
YAML

my @dsn = (...);

# print the schema for informational purposes
Tangram::Relational->deploy($schema);

eval { Tangram::Relational->retreat($schema, @dsn); };
Tangram::Relational->deploy($schema, @dsn);
my $storage = Tangram::Storage->connect($schema, @dsn);

my $table = $storage->remote("MyTable");
my $foo = $table->{a} == "foo";
my $bar = $table->{b} == "bar";
my $baz = $foo & $bar;

print Dump { "1. foo" => $foo, "2. bar" => $bar, "3. baz" => $baz };

[Adam Kennedy]

Post by Sam Vilain

Post by Adam Kennedy
Yes, native positional support is still important.
positions make it very easy to do SQL math.
To express it in overly simplistic code
$foo = [ "a = ?", "foo" ];
$bar = [ "b = ?", "bar" ];
$baz = "$foo and $bar";
# $baz now is [ "a = ? and b = ?", "foo", "bar" ];
Bearing mind a situation with an arbitrary number and complexity of
these sql fragments to be added together, doing this sort of thing
using named placeholders would be a nightmare.

Interesting. You are demonstrating reasons it is useful to deal with
SQL fragments in non-templated form.
In Tangram what you describe is written like this;

(SNIP)

Well, that was a simplistic example from which I had intended the
implication to be that it could be ANY two arbitrary pieces of sql. It
could just have easily been...

$foo = [
"ifelse(null(column), 0, DB2LOB.length(column)) > ? OR column5 is in
( ?, ?, ?, ? )",
100000, "alpha", "beta", "gamma", "delta"
];

$bar = [
"str2date(?, ?) between now() and str2date(?, ?)",
"20050401", "YYYYMMDD", "20040123", "YYYYMMDD"
];

$baz = [ "DB2XML.xpath_match(column3, ?)", "//foo" ];

my $where = join( 'and', $foo, $bar, $baz );

my $sql = DB->table->sql_select . 'where' . $where;

Who's to know what could be in the SQL fragments. We've all seen some of
the crazy and non-standard stuff various databases have.

(note: The SQL expressions were made up, but are of similar complexity
to known existing syntaxes)

Adam K

[Sam Tregar]

Post by Ronald J Kimball
Total Contributions: $1320.25
I suggest you go back and reread Tim's email. What he said was that he
donated a little over $500 himself, not that the total raised was $500.

That's good to know. It doesn't really alter my assessment though -
the funding drive was a failure and won't significantly affect the
future of the DBI v2 project.

-sam

[Jonathan Leffler]

Late to the ball - and only picking up on one issue...

Post by Darren Duncan
2. Always separate out any usage stages that can be performed apart
from the database itself. This allows an application to do those
stages more efficiently, consuming fewer resources of both itself and
the database.
For example, a pre-forked Apache process can declare all of the
database and statement handles that it plans to use, and do as much
of the prepare()-type work that can be done internally as possible,
prior to forking; all of that work can be done just once, saving CPU,
and only one instance of it consumes RAM. All actual invocations of
a database, the open()/connect() and execute() happen after forking,
and at that point all of the database-involving work is consolidated.
Or even when you have a single process, most of the work you have to
do, including any SQL generation et al, can be more easily be
pre-performed and the results cached for multiple later uses. Some
DBI wrappers may do a lot of work with SQL generation et al and be
slow, but if this work is mainly preparatory, they can still be used
in a high-speed environment as that work tends to only need doing
once. Most of the prep work of a DBI wrapper can be done effectively
prior to ever opening the database connection.

I dunno which DBMS support prepare without a database connection, but I
would expect all the mainstream databases to require a database connection.
IBM DB2 does; IBM Informix Dynamic Server (IDS) does; someone else commented
on this and said Oracle does; I would expect both Sybase and MS SQL Server
to need the DB connection too. Probably PostgreSQL; not sure about MySQL.
The only systems that might not need the DB connection are those that aren't
using a separate SQL-based database server.

I'm also far from convinced that there's any significant benefit in
separating the 'create a database handle' from the 'connect to database
server' part. The overhead of creating the handle - as distinct from make
the connection to the DBMS - is negligible. There's nothing of any
significance that can be done with the unconnected handle either - at least,
for the mainstream SQL DBMS. So, the pre-fork creation of an unconnected
handle provides negligible savings. Similar comments apply to the statement
handles - in IDS, at any rate, there's nothing useful that can be done for
statements until you've got a database handle. Or, at the least, you'd be
moving away from the ODBC paradigm, and the ESQL/C paradgm too.

As a general comment on DBI v2; we need to beware of the second-system
effect (Brooks "Mythical Man Month").

In particular, the DBI must not mandate impossible levels of support from
the drivers. It will benefit you nothing if the DBI is immaculate and
wonderful and incredibly all-singing and all-dancing, but no-one can write a
driver for it because the requirements cannot be met by the actual DBMS that
Perl + DBI needs to work with.

[Darren Duncan]

Post by Jonathan Leffler
I dunno which DBMS support prepare without a database connection,
but I would expect all the mainstream databases to require a
database connection. IBM DB2 does; IBM Informix Dynamic Server
(IDS) does; someone else commented on this and said Oracle does; I
would expect both Sybase and MS SQL Server to need the DB connection
too. Probably PostgreSQL; not sure about MySQL. The only systems
that might not need the DB connection are those that aren't using a
separate SQL-based database server.

See my subsequent email/s where I left prepare() requiring an open
database connection, and a separate function (for example,
'compile'), is split out and handles any prep work that can be done
without an open connection.

Since you mention that some systems don't have the separate SQL-based
database server, there are indeed some drivers that can take
advantage of a separate 'compile' then, so they benefit from a split;
the worst that happens with the others is a no-op. Some drivers
gain, and no one loses anything.

Post by Jonathan Leffler
I'm also far from convinced that there's any significant benefit in
separating the 'create a database handle' from the 'connect to
database server' part. The overhead of creating the handle - as
distinct from make the connection to the DBMS - is negligible.
There's nothing of any significance that can be done with the
unconnected handle either - at least, for the mainstream SQL DBMS.
So, the pre-fork creation of an unconnected handle provides
negligible savings. Similar comments apply to the statement handles
- in IDS, at any rate, there's nothing useful that can be done for
statements until you've got a database handle. Or, at the least,
you'd be moving away from the ODBC paradigm, and the ESQL/C paradgm
too.

A main benefit of separating out the handle creation and database
connection is the secondary effect whereby you can pre-associate
statement handles with it that are expensive to create, such as
because they generate SQL. Generating SQL and other such things is
separate from the database and the speed advantage from separating
this out as I described is not lost by the fact of what database
servers don't support, since the databases are never given that work.

A primary effect benefit is any time you want to open the same
connection more than once; you can configure it once when making the
handle, and not again no matter how many times you open/close/open
the connection, or re-open a connection that dies. Moreover, every
open from the first to subsequent ones are done in the same way.

Certainly, one can make a DBI wrapper that adds some of those
advantages, but given their simplicity and universal desirability,
its better to build them in; any addition of complexity over the
current DBI is negligible, or it may in fact be less complex than the
current DBI.

Post by Jonathan Leffler
As a general comment on DBI v2; we need to beware of the
second-system effect (Brooks "Mythical Man Month").
In particular, the DBI must not mandate impossible levels of support
from the drivers. It will benefit you nothing if the DBI is
immaculate and wonderful and incredibly all-singing and all-dancing,
but no-one can write a driver for it because the requirements cannot
be met by the actual DBMS that Perl + DBI needs to work with.

What you say is fair enough, but I never proposed anything impossible
or difficult; everything that I am proposing here is easy and simple.

-- Darren Duncan

[Jochen Wiedmann]

Post by Jonathan Leffler
I dunno which DBMS support prepare without a database connection, but I
would expect all the mainstream databases to require a database connection.

+1

Post by Jonathan Leffler
I'm also far from convinced that there's any significant benefit in
separating the 'create a database handle' from the 'connect to database
server' part.

+1


Not to mention the effect, that one major charm of DBI is its
simplicity: Connect, Execute for updates, inserts, or deletes and
Connect, Execute, Fetch for select. I can't see an advantage in overly
extending the interface.


Jochen

[Michael Peppler]

Post by Jochen Wiedmann

Post by Jonathan Leffler
I dunno which DBMS support prepare without a database connection, but I
would expect all the mainstream databases to require a database connection.

+1

Post by Jonathan Leffler
I'm also far from convinced that there's any significant benefit in
separating the 'create a database handle' from the 'connect to database
server' part.

+1
Not to mention the effect, that one major charm of DBI is its
simplicity: Connect, Execute for updates, inserts, or deletes and
Connect, Execute, Fetch for select. I can't see an advantage in overly
extending the interface.

Personally I tend to agree with you. I haven't read the whole thread,
but I'm not yet convinced that the DBI needs to change that much.
Certainly the Sybase driver won't be able to support many of the
proposed functionality, or won't benefit from the changes (i.e. no speed
gain, no improved flexibility, etc).

Michael

[Kiran Kumar]

We could have an option to do Bulk Inserts ..

[Adam Kennedy]

Post by Jonathan Leffler
In particular, the DBI must not mandate impossible levels of support from
the drivers. It will benefit you nothing if the DBI is immaculate and
wonderful and incredibly all-singing and all-dancing, but no-one can write a
driver for it because the requirements cannot be met by the actual DBMS that
Perl + DBI needs to work with.

I concur. Like CPAN as a whole, DBI's strength is in it's complete and
near universal coverage of all databases, and insanely great (and
occasisionally greatly insane) drivers that do strange and wonderful things.

If we start sacrificing drivers by raising the bar too high, DBI as a
whole suffers. Anyone proposing new features for DBI needs to be
extremely careful of CYJ syndrome.

Can't You Just (or sometimes Could You Just) syndrome is described here.

http://c2.com/cgi/wiki?CouldYouJust
http://www.oreillynet.com/pub/wlg/3593
http://c2.com/cgi/wiki?JustIsaDangerousWord

Go read them now. I'll wait...

This sort of behaviour can play a big part in ending up with second
system problems.

I have an increasing suspicion that having open design processes like
the Tim's call for comments plays a big part in it as well.

People are free to comment on things that 1) They won't have to
implement themselves and (in some cases, but not you Duncan) 2) They
think they know what they are talking about, but really have no idea
what it means underneath the surface.

In any case, I still propose that DBI2 split the driver interface into
Roles. The main "DBI2::Role::Transport" role does ONLY what DBI does
best now. That is, connecting to the database, preparing and sending
queries, and fetching the results.

Forget the current ->tables interface. Drivers can OPTIONALLY implement
the DBI2::Role::Schema interface to handle interrogation of the database
schema. The current way we handle it is really messy by my standards,
and could use a dedicated interface.

As you invent major new features for DBI2, implement them roles.
DBI2::Role::Transational, DBI2::Role::RFC90210 (the super flashy Beverly
Hills feature), etc etc.

Exactly what these roles should be I don't want to state with any
certainty. That's the sort of thing that Tim, with his complete
understanding of the issues, should be doing on his own.

But I _would_ certainly like to see schema/table stuff separated from
the base connection/query functionality.

While I'm on the topic of DBI, one other feature I'd like to see would
be something like better support for large objects or various types.

In my personal DBI wrapper I've been using plain SCALAR refs as
parameters to signify a BLOB, so I can pass by reference without a copy
of a potentially large memory chunk, and then having to do
driver-specific translation to bind variables (Oracle) or what have you.

Some base way of default method defining a BLOB object (whether provided
in memory, or pointing at a file handle to pull the data from at commit
time) in would be really nice. Even if the way I have to pass the blobs
to each driver differs, I'd like to be at least be able to say,

This is a DBI2::Data::BLOB object (or something functionally equivalent).

Adam K

[Darren Duncan]

Post by Adam Kennedy
In any case, I still propose that DBI2 split the driver interface
into Roles. The main "DBI2::Role::Transport" role does ONLY what DBI
does best now. That is, connecting to the database, preparing and
sending queries, and fetching the results.
Forget the current ->tables interface. Drivers can OPTIONALLY
implement the DBI2::Role::Schema interface to handle interrogation
of the database schema. The current way we handle it is really messy
by my standards, and could use a dedicated interface.
As you invent major new features for DBI2, implement them roles.
DBI2::Role::Transational, DBI2::Role::RFC90210 (the super flashy
Beverly Hills feature), etc etc.
Exactly what these roles should be I don't want to state with any
certainty. That's the sort of thing that Tim, with his complete
understanding of the issues, should be doing on his own.

That sounds like a great idea and a good starting place on which to
build certain design issues.

In fact, I have already been implementing something similar to that
in my 'Rosetta' database access library for over a year now. It has
a concept of "feature support lists" where each Rosetta Engine/driver
must take a standard fine-ish-grained checklist and programmatically
declare which list items it officially supports.

An application can see what an Engine/driver claims to support before
trying to use it, and will know whether or not the Engine/driver
can meet its needs or not. An Engine's/driver's declaration can be
read by invoking the features() method of some Rosetta Interface
objects.

An additional use for this feature is that a comprehensive common
test suite for all Engines/drivers can consult features() prior to
running its tests so that it can "skip" any tests that an
Engine/driver doesn't claim to support; it will only invoke and
pass/fail features that the Engine/driver claims to support.

See the Rosetta::Details documentation section 'FEATURE SUPPORT
VALIDATION' for the main summary of "feature support lists".

Post by Adam Kennedy
But I _would_ certainly like to see schema/table stuff separated
from the base connection/query functionality.

I agree. When you get down to it, schema/table/etc reverse
engineering is a very complicated and involved process. Fetching
lists of tables or columns etc should no more be built in than SQL
parsing or generating. In short, anything that can normally be
fetched or changed using ordinary SQL statements should be left out
of the DBI core; let wrappers do that stuff through the SQL pipe that
DBI provides. This said, it is still very useful for DBI to provide
a function for fetching a list of auto-detectable data sources, so
that should stay.

On a similar note, utility functions like quote() should be left out
of the DBI core, and left to either a separate module or someone's
wrapper, since it's firmly related to SQL generation. If people want
DBI itself to handle stuff like that for them, they should use host
parameters for the literals in question.

Post by Adam Kennedy
While I'm on the topic of DBI, one other feature I'd like to see
would be something like better support for large objects or various
types.

I second that. Have API methods for fetching or storing by-the-chunk
pieces of LOBs that one could not do with a traditional bind_param()
etc since the whole thing won't fit in RAM at once.

-- Darren Duncan

[Darren Duncan]

I have an additional reply to the following ...

Post by Adam Kennedy
In any case, I still propose that DBI2 split the driver interface
into Roles. The main "DBI2::Role::Transport" role does ONLY what DBI
does best now. That is, connecting to the database, preparing and
sending queries, and fetching the results.
Forget the current ->tables interface. Drivers can OPTIONALLY
implement the DBI2::Role::Schema interface to handle interrogation
of the database schema. The current way we handle it is really messy
by my standards, and could use a dedicated interface.
As you invent major new features for DBI2, implement them roles.
DBI2::Role::Transational, DBI2::Role::RFC90210 (the super flashy
Beverly Hills feature), etc etc.
Exactly what these roles should be I don't want to state with any
certainty. That's the sort of thing that Tim, with his complete
understanding of the issues, should be doing on his own.

One of the reasons I like this idea is that it lets DBI define
multiple Roles that may be mutually exclusive and accomplish the same
task in different ways.

For example, the idea of whether the interface will accept SQL AST
objects in addition to or instead of SQL strings. The standard
DBI2::Role::Transport will take SQL strings and only SQL strings, as
the current DBI does. There can be an additional Role for each AST.

In that situation, there are several case scenarios depending on the
type of database being used and what the driver maker is willing to
support.

1. The first case is the common database server that always takes its
input as string SQL. In this case, the standard string-taking Role
will be the most native fit for it, and any AST-supporting Roles
would have to be, in some fashion, implemented as wrappers on top of
this, converting their contents to string SQL for the database.

It is assumed that the AST in question would come with its own set of
SQL generators, either in the same distribution or a separate
distribution that is an extension to it. If the DBI driver writer
chooses to implement the relevant AST-taking Role, it would
presumably use the AST's own SQL generator to implement that support,
so the driver writer hardly has any work to do of their own. Or, if
the DBI driver writer does not choose to implement the Role, then
users of said AST would use it as or with a DBI wrapper that invokes
the SQL generating functionality itself and passes the result to
DBI2::Role::Transport.

2. The second case is a usually-embedded database server or library
that does not take string SQL as its native input. In this case,
some sort of AST-supporting Role would be the most native fit for it,
since it is probably easier for that DBI driver to extract the
information it needs from the AST than to parse string SQL; the
DBI2::Role::Transport Role would have to be, in some fashion,
implemented as a wrapper on top of this, which parses string SQL into
an AST. Most likely, an externally destributed SQL parser would be
the best choice, so it can be improved independently of the driver,
as with any SQL generators. (SQL::Statement is an example of this
already being done.)

Now, if support for a string-SQL interface is mandatory for all DBI
drivers, then it is possible that all AST-taking Roles can be
implemented on top of the string-taking Role. As is the case with
DBI v1. But, for the sake of those drivers that want ASTs
internally, having a way for them to get that input natively, through
officially sanctioned appropriate Roles, that lets those drivers save
themselves a lot of work and the system is potentially much faster
and easier to improve, without sacrificing compatability.

A separate advantage of this Roles thing is backwards compatability
due to a multi-versioned API. If we wish to implement a significant
API or implementation change in DBI after DBI 2 is launched, we can
be free to do so without breaking older programs because they will by
definition be writing against a specific API version. We just have
to maintain a Role for each distinct version that maps what the
application expects to how things work in the background.

I liken this to a new feature in Mac OS X starting with 10.4 Tiger;
versioned KPIs (kernal programming interfaces); from now on, it is a
lot easier for Apple to release significantly changed kernals in
newer Mac OS X versions without breaking any applications or drivers,
because those specify a versioned API which continues to exist as a
wrapper. See http://arstechnica.com/reviews/os/macosx-10.4.ars/4 for
what I'm talking about.

However it's done, I'm thinking something akin to that would be good for DBI.

-- Darren Duncan

[Sam Vilain]

Post by Adam Kennedy
But I _would_ certainly like to see schema/table stuff separated from
the base connection/query functionality.
While I'm on the topic of DBI, one other feature I'd like to see would
be something like better support for large objects or various types.

Perhaps you have some extremely innovative solution to meet these two
demands that doesn't involve building or using SQL parse trees?

Sam.

[Jonathan Leffler]

Still late to the party - another one bullet point item...

Post by Darren Duncan
4. All host parameters should be named (like ":foo") rather than
positional (like "?"), meeting with the SQL:2003 standard. The named
format is a lot easier to use and flexible, making programmers a lot
less error prone, more powerful, and particularly more resource
efficient when the same parameter is conceptually used multiple times
in a SQL statement (it only has to be bound once). If anyone wants
to use positional format, it could easily be emulated on top of this.
Or, if native positional support is still important, then it should
be a parallel option that can be used at the same time as named in
any particular SQL statement. See the native API of SQLite 3 for one
example that (I believe) supports both in parallel. This also means
that execute() et al should take arguments in a hash rather than an
array.

Can you explain which parts of the SQL:2003 mandate this notation? I've had
a moderately good poke around my copy of ISO/IEC 9075-2:2003
(SQL/Foundation) and cannot find this. I'd like a few section numbers listed
which describe this.

The various places I've looked include: 19.6 (prepare statement), 9
(Additional Common Rules), 6.4 (<value specification> and <target
specification>). I could have missed something in these places - or I could
be looking in the wrong place.

The IDS (IBM Informix Dynamic SQL) syntax has a number of places where
:<digits> can appear with a meaning other than placeholder, and there are
also a number of places where :<identifier> can appear with a meaning other
than placeholder. So, it would be extremely difficult to add :<identifier>
notation into IDS. (One of the bits I had to remove from DBD::Informix was
code from DBD::Oracle that simulated :<identifier> notation - because it
breaks too much else.)

This ties in with my previous comment, too; don't try to demand too much of
the drivers, or the driver writers. As long as you've got a good surrogate
system in DBI that can simulate those accurately for DBMS that only support
'?' (positional) placeholders, then DBI v2 can do what the heck it likes.
But as soon as it is inaccurate - translates things that should not be
translated - or cannot do the translation automatically, then you will lose
drivers (or, more accurately, driver writers).

I'm all in favour of looking at the big picture and trying to see where you
want to go. However, you must also keep an eye out for the marshes between
where you are and where you want to go; don't let DBI be sunk by ignoring
the realities of the available DBMS.

[Darren Duncan]

Post by Jonathan Leffler
Can you explain which parts of the SQL:2003 mandate this notation?
I've had a moderately good poke around my copy of ISO/IEC
9075-2:2003 (SQL/Foundation) and cannot find this. I'd like a few
section numbers listed which describe this.
The various places I've looked include: 19.6 (prepare statement), 9
(Additional Common Rules), 6.4 (<value specification> and <target
specification>). I could have missed something in these places - or
I could be looking in the wrong place.

Yes, I can quote some, in different places. Look at these:

SQL:2003, 4.29 "Host parameters" (pp90,91,92)
SQL:2003, 5.4 "Names and identifiers" (pp151,152)
SQL:2003 Foundation page 152 says:
<host parameter name> ::= <colon><identifier>

See that last line in particular. I don't see how it could be more clear.

This is my main source for the SQL:2003 documentation:

http://www.wiscorp.com/SQLStandards.html

Or specifically:

http://www.wiscorp.com/sql/sql_2003_standard.zip (warning, large file)

That page is run by someone leading / high up in the SQL standards group.

Note that those urls are printed in my SQL::Routine::Language POD
file on CPAN, and the lines with specific pages and section names are
in source/reference comments/documentation for the
build_identifier_host_parameter_name() function
in my SQL::Routine::SQLBuilder module. I do tend to give sources to
back up anything important I do, which is good for third party
validation.

Post by Jonathan Leffler
The IDS (IBM Informix Dynamic SQL) syntax has a number of places
where :<digits> can appear with a meaning other than placeholder,
and there are also a number of places where :<identifier> can appear
with a meaning other than placeholder. So, it would be extremely
difficult to add :<identifier> notation into IDS. (One of the bits
I had to remove from DBD::Informix was code from DBD::Oracle that
simulated :<identifier> notation - because it breaks too much else.)

A variety of databases, such as Oracle, already have support for just
the format I described, which I believe was also in SQL:1999. In
fact, I think that Oracle's own extensions of earlier SQL standards
had a lot of influence on later SQL standards such as this, though I
can think of multiple differences too.

Post by Jonathan Leffler
This ties in with my previous comment, too; don't try to demand too
much of the drivers, or the driver writers. As long as you've got a
good surrogate system in DBI that can simulate those accurately for
DBMS that only support '?' (positional) placeholders, then DBI v2
can do what the heck it likes. But as soon as it is inaccurate -
translates things that should not be translated - or cannot do the
translation automatically, then you will lose drivers (or, more
accurately, driver writers).

I don't see how :foo is significantly more difficult than ? to
simulate accurately.

Post by Jonathan Leffler
I'm all in favour of looking at the big picture and trying to see
where you want to go. However, you must also keep an eye out for
the marshes between where you are and where you want to go; don't
let DBI be sunk by ignoring the realities of the available DBMS.

At the same time, don't let today's technology limit what you do in
preparation for tomorrow. DBI v2 is meant to be a forward-looking
plan, as Perl 6 is, so we have to consider things that are reasonable
for inclusion; even if it isn't common for native database support
now, that isn't to say that native support won't come later, and when
it does, we'll already be ready, or they'll even take what DBI does
as a cue for what they can add.

-- Darren Duncan

[Jochen Wiedmann]

Post by Jonathan Leffler
Can you explain which parts of the SQL:2003 mandate this notation? I've had
a moderately good poke around my copy of ISO/IEC 9075-2:2003
(SQL/Foundation) and cannot find this. I'd like a few section numbers listed
which describe this.

I second that one too. Besides, I am not convinced that a marriage of
DBI and SQL is a good idea at all. Note, that there are quite some
drivers which aren't using SQL. And, besides, most drivers have an
internal SQL processing engine too. What's the gain in processing SQL
twice or more?


Jochen

[Jonathan Leffler]

Post by Darren Duncan

Post by Jonathan Leffler
Can you explain which parts of the SQL:2003 mandate this notation?
I've had a moderately good poke around my copy of ISO/IEC
9075-2:2003 (SQL/Foundation) and cannot find this. I'd like a few
section numbers listed which describe this.
The various places I've looked include: 19.6 (prepare statement), 9
(Additional Common Rules), 6.4 (<value specification> and <target
specification>). I could have missed something in these places - or
I could be looking in the wrong place.

SQL:2003, 4.29 "Host parameters" (pp90,91,92)
SQL:2003, 5.4 "Names and identifiers" (pp151,152)
<host parameter name> ::= <colon><identifier>
See that last line in particular. I don't see how it could be more clear.

Oh - hmmm...Embedded SQL...Yes, even Informix supports that notation in
Embedded SQL/C (ESQL/C); I'd forgotten about it because it is not part of
what goes to the (Informix) server.

EXEC SQL EXECUTE :prepared_stmt USING :hostvar1:indvar1, :hostvar2:indvar2,
...;

This is not what DBI deals with - it deals more nearly with the CLI syntax,
where that is not, as far as I know, permitted. However, I'm not yet sure
about all the contexts in which a <host parameter name> can appear - it is
the sort thing that is quite hard to determine from the standard,
unfortunately. Even with my heavily hyperlinked grammar - generated from the
BNF in the standard - it is fairly hard to tell.

Consider my objection temporarily on ice - I'm not wholly convinced, but
clearly the onus is on me to show why it should not be mandated by DBI v2.

Post by Darren Duncan
http://www.wiscorp.com/SQLStandards.html

Thanks. I have copies of the official standard - but I didn't have the
presentations, etc.

If anyone wants to use my hyperlinked HTML version of the standard SQL
syntax, let me know and I'll make it available for download on a website. I
updated it last week, so the version I have at home isn't current (though
most people wouldn't notice the change - it was very minor and solely
related to formatting).

[Darren Duncan]

Post by Jonathan Leffler
This is not what DBI deals with - it deals more nearly with the CLI
syntax, where that is not, as far as I know, permitted.

My impression of DBI is that it is a stand-in of sorts for a SQL CLI,
and does or should do all of the same sorts of things. Each time you
run a SQL statement, it is like typing said statement into a CLI.
(In fact, I seem to recall that DBI ships with a simple SQL CLI
program that runs on top of it, and that has an almost 1:1 mapping.)
DBI abstracts the CLI a bit by providing things like connect()
methods rather than having users execute 'CONNECT TO ...' SQL, but
that doesn't really change what I've said. So DBI is basically the
same as CLI but that it is easier for programmers to use by replacing
an input terminal with functions. In that context, the :foo syntax
corresponds to what bind_var() etc maps to.

Post by Jonathan Leffler
If anyone wants to use my hyperlinked HTML version of the standard
SQL syntax, let me know and I'll make it available for download on a
website. I updated it last week, so the version I have at home
isn't current (though most people wouldn't notice the change - it
was very minor and solely related to formatting).

Please do that. So far I have had the drudgery of manually scrolling
through a 1000+ page PDF document to look up things. Your version
should be much faster. This said, does it include what page in the
original the info came from, so I can correlate them in reference
documentation?

-- Darren Duncan

[Jonathan Leffler]

Oh drat - not the DBI connection string discussion again!

Post by Darren Duncan
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being
applicable at once) are: 1. the DBI driver module to use; 2. the
internet server IP address or domain name and port; 3. the locally
defined server device socket; 4. the locally defined service (eg,
ODBC or SQL*Net) name; 5. the file system file name; 6. the file
system directory name; 7. some other detail if any for fully in-RAM
databases; 8. the authorization identifier / user name; 9. the
password; 10. some other authorization credential, or channel
encryption details, or whatever else; 11. what kind of database or
what database product is being used, if known. If the DBI driver
talks to a client-configurable DBI proxy server, then, it should be
possible to nest a set of the above settings (eg, as a hash-ref) as
one part of the main settings given to the proxy client.

Only 1, 8 and 9 (driver name, user ID, password) apply to Informix databases
- and you've missed out the other bit that applies - the database name, and
optionally database server name hosting it (either 'dbase' or '***@server').
These are not the same as 2, 3, 4, 5, 6, 7, 10, or 11, and I can't think how
any of them (except perhaps 10) could be applied to an IDS connection.

Oh, and if you're going to enhance the connection, please ensure you cover
challenge-response protocols (where you send an initial username/password
and the authentication server comes back with a question such as "what is
the number on your RSA key fob at the moment", and the hapless user has to
type that information in, and the connection management code has to deal
with this - callbacks and the like.

So, as was discussed emphatically and exhaustively (in January 2005 in
dbi-dev under 'Proposing an API for the extension for simplifying database
connections'), this is not readily going to fly with the existing DBMS -
specifically, not with IDS. Closed-source DBMS are *not* necessarily going
to adapt to meet the needs of Perl and DBI. You can argue that's their loss
- you may even be right. But you'll be limiting the acceptability of Perl +
DBI in some respects. You'll also be annoying the hell out of me if you
can't define a connection string that will work with Informix (I'm not too
worried about the challenge-response stuff, though Informix can handle
that).

Oh - and DBI v2 should have support for scrollable cursors.

[Darren Duncan]

On 7/4/05, Darren Duncan
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being
applicable at once) are: 1. the DBI driver module to use; 2. the
internet server IP address or domain name and port; 3. the locally
defined server device socket; 4. the locally defined service (eg,
ODBC or SQL*Net) name; 5. the file system file name; 6. the file
system directory name; 7. some other detail if any for fully in-RAM
databases; 8. the authorization identifier / user name; 9. the
password; 10. some other authorization credential, or channel
encryption details, or whatever else; 11. what kind of database or
what database product is being used, if known. If the DBI driver
talks to a client-configurable DBI proxy server, then, it should be
possible to nest a set of the above settings (eg, as a hash-ref) as
one part of the main settings given to the proxy client.
Only 1, 8 and 9 (driver name, user ID, password) apply to Informix databases -

Re-read that paragraph. It says 'not all being applicable at once'.

and you've missed out the other bit that applies - the database
name, and optionally database server name hosting it (either 'dbase'
or 11, and I can't think how any of them (except perhaps 10) could
be applied to an IDS connection.

I think that 2 thru 4 cover this, though I used more generic language.

Oh, and if you're going to enhance the connection, please ensure you
cover challenge-response protocols (where you send an initial
username/password and the authentication server comes back with a
question such as "what is the number on your RSA key fob at the
moment", and the hapless user has to type that information in, and
the connection management code has to deal with this - callbacks and
the like.

I mentioned those generically in 8 thru 10; the latter is "some other
authorization credential etc".

So, as was discussed emphatically and exhaustively (in January 2005
in dbi-dev under 'Proposing an API for the extension for simplifying
database connections'), this is not readily going to fly with the
existing DBMS - specifically, not with IDS. Closed-source DBMS are
*not* necessarily going to adapt to meet the needs of Perl and DBI.
You can argue that's their loss - you may even be right. But you'll
be limiting the acceptability of Perl + DBI in some respects.
You'll also be annoying the hell out of me if you can't define a
connection string that will work with Informix (I'm not too worried
about the challenge-response stuff, though Informix can handle that).

If a database only accepts input in string form, then the DBI driver
can generate one out of the decomposed information it is given. No
trouble here, really; no trouble at all. The whole point of a DBI
driver is to map between the DBI interface and how the database
product natively does things. It is true that some things can only
be done by changing the database product itself, but other things can
be done in the DBI driver, with no support from closed-source DBMS
necessary.

-- Darren Duncan

[Jochen Wiedmann]

Post by Jonathan Leffler
Oh drat - not the DBI connection string discussion again!

;-)

[Jeffrey W. Baker]

Post by Jonathan Leffler
Oh drat - not the DBI connection string discussion again!

Post by Darren Duncan
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being
applicable at once) are: 1. the DBI driver module to use; 2. the
internet server IP address or domain name and port; 3. the locally
defined server device socket; 4. the locally defined service (eg,
ODBC or SQL*Net) name; 5. the file system file name; 6. the file
system directory name; 7. some other detail if any for fully in-RAM
databases; 8. the authorization identifier / user name; 9. the
password; 10. some other authorization credential, or channel
encryption details, or whatever else; 11. what kind of database or
what database product is being used, if known. If the DBI driver
talks to a client-configurable DBI proxy server, then, it should be
possible to nest a set of the above settings (eg, as a hash-ref) as
one part of the main settings given to the proxy client.

Only 1, 8 and 9 (driver name, user ID, password) apply to Informix databases
- and you've missed out the other bit that applies - the database name, and
These are not the same as 2, 3, 4, 5, 6, 7, 10, or 11, and I can't think how
any of them (except perhaps 10) could be applied to an IDS connection.

There are certainly database-specific things to be worked around. An
improvement to the current DSN scheme would be a URI, as discussed in
the past. The leading dbi: on every DSN is redundant, so a URI might

Post by Jonathan Leffler
Oh, and if you're going to enhance the connection, please ensure you cover
challenge-response protocols (where you send an initial username/password
and the authentication server comes back with a question such as "what is
the number on your RSA key fob at the moment", and the hapless user has to
type that information in, and the connection management code has to deal
with this - callbacks and the like.

Seconded, with request for support of SSL client certificate
authentication.

-jwb

[Juerd]

Post by Jeffrey W. Baker

Post by Jonathan Leffler
Oh drat - not the DBI connection string discussion again!

There are certainly database-specific things to be worked around. An
improvement to the current DSN scheme would be a URI, as discussed in
the past. The leading dbi: on every DSN is redundant, so a URI might

I think URIs are the right way to go, and one of the very few things PHP
(though be it with PEAR) did right.

http://pear.php.net/manual/en/package.database.db.intro-dsn.php

It would be fun if we could just steal that design and build on top of
it, for compatibility, but also because other people have already
thought about it and proven that it works.


Juerd

[Jared Still]

I haven't been following this too closely, so my apologies
if already mentioned.

This connect string is very much like the new Easy Connect
Naming method in Oracle 10g.

eg. sqlplus scott/***@server:port/service

Note that it is not 'instance', but 'service'.

Jared

[Jonathan Leffler]

Post by Darren Duncan
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being
applicable at once) are: 1. the DBI driver module to use; 2. the
internet server IP address or domain name and port; 3. the locally
defined server device socket; 4. the locally defined service (eg,
ODBC or SQL*Net) name; 5. the file system file name; 6. the file
system directory name; 7. some other detail if any for fully in-RAM
databases; 8. the authorization identifier / user name; 9. the
password; 10. some other authorization credential, or channel
encryption details, or whatever else; 11. what kind of database or
what database product is being used, if known. If the DBI driver
talks to a client-configurable DBI proxy server, then, it should be
possible to nest a set of the above settings (eg, as a hash-ref) as
one part of the main settings given to the proxy client.
Only 1, 8 and 9 (driver name, user ID, password) apply to Informix

databases -
Re-read that paragraph. It says 'not all being applicable at once'.

Yes - I realized that.

and you've missed out the other bit that applies - the database

Post by Darren Duncan
name, and optionally database server name hosting it (either 'dbase'
or 11, and I can't think how any of them (except perhaps 10) could
be applied to an IDS connection.

I think that 2 thru 4 cover this, though I used more generic language

I don't. The server name in IDS is not a host name or domain name, nor is it
a port number (so 2 is not applicable), nor is it the locally named socket
(so 3 is not applicable), nor is it the locally defined service (so 4 is not
applicable).

All of these are identified by, but are distinct from, the IDS server name.
There is a mechanism (configuration file) that hides all the gory details
from the user. Users should not need to know sordid details like port
numbers, or whether the host is on an IPv4 or IPv6 network, etc.

Further, within an IDS instance, there are multiple databases that can be
separately connected to - '***@server1', '***@server1', '
***@server2', '***@server2'. You must be able to specify the database
within the server instance.

Now, in fact, server1 and server2 could be alternative names for the same
hunk of disk space, supervised by the same IDS instance but with different
connection properties - such as encrypted vs unencrypted - but that is
probably just too confusing. Equally, and more normally, server1 could be on
a wholly different machine from server2.

[Concrete example: I have many IDS instances running on my machine at work.
One instance has 4 names:
anubis_17, anubis_17_tcp, anubis_17_str, anubis_17_shm. The first two are
both network connections - albeit usually using loopback since I usually
work on the same machine. The third uses a STREAMS pipe; the fourth uses
shared memory. All allow me to connect to the same set of databases, which
includes 'sysmaster', 'sysutils', 'stores', 'logged', 'unlogged',
'mode_ansi'. Each of those databases has its own independent set of system
catalogs. I can connect to '***@anubis_17' or '***@anubis_17_shm' and
edit the same data - it's the same database, identified by different server
names and different connection properties. I also have another IDS instance,
running an older version of IDS, with server names anubis_23, anubis_23_tcp,
anubis_23_str, anubis_23_shm - and the same set of databases, but only
because some of them are standard and the others I keep there to make
testing DBD::Informix easier. I also have an entry configured for a database
server called 'smartpts' that is based in Lenexa, KS - I work in Menlo Park,
CA - about 1800 miles away. I can run $db1 = DBI->connect('
dbi:Informix:***@anubis_18'); $db2 = DBI->connect('
dbi:Informix:***@smartpts'); to connect to those databases. I can also, in
general, do distributed queries between the two databases without explicitly
connecting to the other. And note that I can switch between TCP and SHM
connections on the local machine (which is
anubis.menlo.ibm.com<http://anubis.menlo.ibm.com>)
simply by changing the server name - I don't have to do any other
modifications to the connection string.]

Please note that for Informix, the database name (optionally with server
name) is all you need to specify. (There's an environment variable that
specifies the default server name if you omit it.) Specifying a 'host name'
won't work; there is no mechanism for connecting by specifying a host name -
except as part of the configuration file. Specifying a port number (or
service name) won't work; there is no mechanism for connecting by specifying
a port number or service name - except as a part of the configuration file.

I'm sorry that life is more complex that you wish to recognize - but that is
how life is.

Oh, and if you're going to enhance the connection, please ensure you

Post by Darren Duncan
cover challenge-response protocols (where you send an initial
username/password and the authentication server comes back with a
question such as "what is the number on your RSA key fob at the
moment", and the hapless user has to type that information in, and
the connection management code has to deal with this - callbacks and
the like.

I mentioned those generically in 8 thru 10; the latter is "some other
authorization credential etc".

No - you don't seem to understand. The challenge-response protocol can ask
someone for the RSA key fob number this time, their mother's maiden name the
next time, their employee number the time after that, and nothing on the
fourth occasion. You cannot predict what the extra information requested is
going to be - so you can't provide the extra information in the initial
connection attempt because you don't know which extra information is going
to be needed. That's what provides the security - the unpredictability of
the question, so that it is hard to pre-programme the answer.

Don't get hung up on this - challenge-response authentication protocols are
not in everyday use in very many places, but they are likely to become more
prevalent in future. In particular, the RADIUS protocol (RFC2865 plus sundry
supporting RFCs) supports challenge-response. And the PAM (Pluggable
Authentication Modules) support it - look them up sometime.

So, as was discussed emphatically and exhaustively (in January 2005

Post by Darren Duncan
in dbi-dev under 'Proposing an API for the extension for simplifying
database connections'), this is not readily going to fly with the
existing DBMS - specifically, not with IDS. Closed-source DBMS are
*not* necessarily going to adapt to meet the needs of Perl and DBI.
You can argue that's their loss - you may even be right. But you'll
be limiting the acceptability of Perl + DBI in some respects.
You'll also be annoying the hell out of me if you can't define a
connection string that will work with Informix (I'm not too worried
about the challenge-response stuff, though Informix can handle that).

If a database only accepts input in string form, then the DBI driver
can generate one out of the decomposed information it is given.

Only if the decomposed form contains the information that is needed in a
form that can be used.

OK - bullet 10 says:

10. some other authorization credential, or channel encryption details, or
whatever else;

I ruled out 10a (some other authorization credential) as a way of dealing
with challenge-response protocols; it does not prevent it from being usable
in some circumstances. 10b is of some value - though IDS hides that
information from the average user (the same configuration file that defines
what the server means and how to connect to it includes the information on
whether encryption is to be used; another configuration file typically
defines the detailed encryption options). DBD::Informix won't be able to
accommodate arbitrary choices on encryption; both the client and the server
have to be set up compatibly, both expecting to use encryption.

That leaves, I suppose, 10c 'or whatever else'. If that is intended to be a
wholly open-ended set of options, then we can, if necessary, cover the
critical part of the IDS connection information in 'whatever else' - but it
feels kind of odd that the database name is 'whatever else'. The IDS server
- that I can live with DBI not handling; it can simply be regarded as a
funny part of the IDS database naming convention. So, as long as the rules
allow '***@server' as a valid name for a database, DBD::Informix can hide
the server from the rest of DBI. But you must allow users to specify just a
database name and the driver name 'dbi:Informix:dbase' or '
dbi:Informix:***@server', or a segregated equivalent: {Driver=>'Informix',
Database=>'dbase'} or {Driver=>'Informix', Database=>'***@server'}.

I note in passing that database names and server names can be up to 128
characters each; don't impose arbitrary limits there, either, though that
isn't part of the Perl philosophy so I don't expect it to be an issue.

Now, if some idiot tries to connect to IDS with connection information such
as {Driver=>'Informix',
Host=>'yucatan.example.com<http://yucatan.example.com>',
Port=>1526}, DBD::Informix will have to reject the connection attempt -
insufficient information. If {Database=>'***@server'} is part of the
connection string, DBD::Informix can ignore the extraneous matter, or reject
the connection attempt because the extraneous matter is irrelevant and
probably incorrect. Now, at one level, I neither know nor care whether a
database name as found in IDS is of any relevance elsewhere - it is of
relevance to the DBMS that I deal with. As such, DBI should be able to
accommodate it. If DBI won't accommodate it, it won't be because the
designers were not made aware of the issue. However, I believe other DBMS
can support multiple things called databases within the scope of a single
DBMS instance.

Your proposal does not contain one, arguably two, critical pieces of
information -- the Informix-style database server name and the
Informix-style database name. A single Informix host machine can be running
multiple instances of IDS. I'd like to be able to manufacture the necessary
background information for you - but I can't. The server name and the
database name are both needed, and are not covered explicitly by the items
on your list. It doesn't matter how much you try to bend it - the
information you are planning on providing does NOT include all the
information necessary to connect to an IDS database except under the
catch-all clause in item 10, which is not a satisfactory place for it to be
handled.


No

trouble here, really; no trouble at all. The whole point of a DBI
driver is to map between the DBI interface and how the database
product natively does things. It is true that some things can only
be done by changing the database product itself, but other things can
be done in the DBI driver, with no support from closed-source DBMS
necessary.

Please - listen to me. I'm an expert on IDS. I know whereof I speak. I don't
have a mandate to make changes to IDS because of possible future
requirements from Perl and DBI. I regard the details you are trying to
expose as retrograde steps - however, if there was a need from our customers
for the retrograde setup, I'd support adding it. But I'm not getting any
pressure on that - there are many other much more important problems and
features to add.

The problem that I'm trying to get across to the people involved in the
design of DBI v2 is the same as the problem that I had to explain to back in
January. The model you wish to impose on every DBMS in the world does not
fit every DBMS in the world - there are real, practical, in-use
counter-examples which demonstrate that the way you (collectively - not you
specifically, Duncan) would like to be able to do it will not work for some
DBMS. I'm sorry that (a small portion of) the real world is raining on your
parade - but something has to give, and the bit that is still unfinished is
DBI v2 - IDS has been working the way it currently does for upwards of a
decade, and DBI v1 accommodates Informix quite adequately; I don't see any
good reason for DBI v2 to break it. IDS goes to lengths to conceal from the
people using it (as opposed to the administrators setting it up) all the
ghastly details you are revelling in exposing. Further, it not only conceals
those details, it uses some extra information that you've not yet accounted
for. No; you probably don't need to worry about the extra information - but
it is there and is hidden by the configuration file.

Please, please, please provide a mechanism that will work for Informix as
well as the other DBMS.

OK; I'll answer questions about what would or would not work for Informix,
and help design the mechanism. I'm not going to expound any more on the
basic problem - but I will vote against any proposal that cannot be managed
by Informix. And you can't say you were not warned.

[Darren Duncan]

Jonathan, while you are well-meaning in your comments, you are
mis-reading what I have said multiple times and are therefore making
a straw man argument against it.

Post by Darren Duncan
5. All details used to construct a connection handle should be
completely decomposed rather than shoved into an ungainly "data
source". Examples of what should be distinct (not all being
applicable at once) are: <snip>

That's the key part of my proposal right there. I don't care about
exactly what break-down of pieces we collectively come up with, as
long as they are unambiguous, reasonably atomic, account for all the
possible needs, and have reasonable names. That 11-point list of
mine was only meant to be an example of what breaking up a "data
source" string can give us; it was not meant to be exhaustive and
there is no point in arguing at length against what it missed and how
horrible it is. No doubt, an IDS and/or components thereof should
also be in the list of connection parameters. I'm not arguing
against what one needs for Informix, either explicitly or implicitly.

All this said, thank you for all the information about Informix how
you set up your systems, and how many levels of hierarchy there can
be with a database; eg, a server has 1+ databases, each of which has
1+ catalogs (and presumably each of those has 1+ schemas and each of
those 1+ tables and other schema objects). I will probably find it
helpful when I plan various DBI-using tasks.

-- Darren Duncan

[Adam Kennedy]

Post by Jonathan Leffler
No - you don't seem to understand. The challenge-response protocol can ask
someone for the RSA key fob number this time, their mother's maiden name the
next time, their employee number the time after that, and nothing on the
fourth occasion. You cannot predict what the extra information requested is
going to be - so you can't provide the extra information in the initial
connection attempt because you don't know which extra information is going
to be needed. That's what provides the security - the unpredictability of
the question, so that it is hard to pre-programme the answer.

Ah but you can know in advance! :) You may not know the actual result
per instance, but you CAN know the decision process you'll need to go
through. Which you can provide as a parameter in the form of a CODE
reference. :) i.e. a callback

But that's a minor point and overall I completely agree with your
general ideas.

Adam K

[Dean Arnold]

RE: Placeholders: since DBIv1 already supports both forms of
PH's, I see no reason to deprecate or abandon either form.
Furthermore, to my knowledge, none of (ODBC, JDBC, ADO.NET)
has abandonded or deprecated the ? form, so I don't see
the need for DBI to.

RE: LOBs and "SQL Parse Trees": having recently implemented
LOB support for a JDBC driver (and soon for a DBD), I can assure
you that SQL parse trees are unneeded to support them. For databases
robust enough to support LOBs, they'll almost always provide
sufficient metadata info and/or SQL syntax to manipulate them;
only databases which don't support LOBs have that difficulty.
Furthermore, a quick review of the current DBI will indicate that
Mssr. Bunce has already implemented some stub methods for
generalized support.

RE: SQL Parse Trees (or other non-SQL query input)

Since none of (ODBC, JDBC, ADO.NET) seems compelled to
impose this concept on driver writers, I don't see why
DBI should be the vanguard.

However, implementing a subclass of DBI to support it
seems technically feasible, so I'd suggest that
those of you championing this requirement implement one
on DBI v1. Feel free to use DBIx::Chart to bootstrap
your project. As the proponents of this notion
are so generous with their requirements for those of us
who develop DBI drivers and/or contribute
development efforts to the DBI itself, I'm sure they won't
object if I provide a few requirements:

1. For DBI drivers which support them, your subclass
must support
- arbitrary numbers and levels of JOINs (including
various outer, and non-equijoins)
- arbitrarily nested subqueries (including correlated)
- HAVING and GROUP BY clauses
- ORDER and LIMIT clauses
- updatable cursors
- database-specific SQL extensions

2. It must function with current versions of 40% of DBD's
created or updated on CPAN since Jan. 1, 2003. Said 40%
must include
- DBD::ODBC
- DBD::Oracle
- DBD::Pg
- DBD::MySQL
- DBD::CSV
- one 'exotic' driver (e.g.,
DBD::iPod or DBD::Amazon, but excluding DBD::Google,
whose syntax is too simplistic for a meaningful test)

(FWIW: Past experience (e.g., execute_array()) leads me to believe
Mssr. Bunce's requirements are likely much higher than 40%, so
"choose wisely, grasshopper")

BTW: If you need a list of DBD's meeting said requirement, let me know,
I just pulled one down.

3. It cannot require any changes to either DBI or the
selected DBD's.

4. It must produce a database-independent conforming set of error codes
(feel free to use SQLSTATE aka $h->state)

5. It must be uploaded to CPAN, and list, and demonstrably function against,
the DBD's selected in requirement (2) above.

Once you've implemented the subclass, you'll have empirical proof
of the feasibility, and, more importantly, you'll be able to port
the subclass to DBIv2, without any additional burden on DBI
developers.

Regards,
Dean Arnold
Presicient Corp.

[Darren Duncan]

Post by Dean Arnold
RE: SQL Parse Trees (or other non-SQL query input)
Since none of (ODBC, JDBC, ADO.NET) seems compelled to
impose this concept on driver writers, I don't see why
DBI should be the vanguard.

I should emphasize that I never expected to be able to send any type
of ASTs over the pipe to the database. They would still be
interpreted by the database driver for Perl and/or a wrapper thereon,
into the database native format. Its just that, to an application,
it would appear that the ASTs were going over the pipe, as to their
effect, even though they weren't behind the scenes.

Post by Dean Arnold
However, implementing a subclass of DBI to support it
seems technically feasible, so I'd suggest that
those of you championing this requirement implement one
on DBI v1.

I agree. Already in progress. In a week or two I should announce
"Rosetta/SQL::Routine Developer Preview #3" and look forward to
people trying it out and giving me the usual "how do I" questions.
But wait until then before making any in-depth analysis, since I have
some large updates to post first.

Post by Dean Arnold
As the proponents of this notion
are so generous with their requirements for those of us
who develop DBI drivers and/or contribute
development efforts to the DBI itself, I'm sure they won't

I agree.

Post by Dean Arnold
1. For DBI drivers which support them, your subclass
must support
- arbitrary numbers and levels of JOINs (including
various outer, and non-equijoins)
- arbitrarily nested subqueries (including correlated)
- HAVING and GROUP BY clauses
- ORDER and LIMIT clauses
- updatable cursors
- database-specific SQL extensions

Rosetta supports the definition of all of the above right now, except
that the last 2 are incomplete (for now). I still have to add "the
last mile", which is joining the definition to the implementation,
though that is what DR3 is for. See the top of
SQL::Routine::Language for a summary of what I claim to support.

Post by Dean Arnold
2. It must function with current versions of 40% of DBD's
created or updated on CPAN since Jan. 1, 2003. Said 40%
must include
- DBD::ODBC
- DBD::Oracle
- DBD::Pg
- DBD::MySQL
- DBD::CSV
- one 'exotic' driver (e.g.,
DBD::iPod or DBD::Amazon, but excluding DBD::Google,
whose syntax is too simplistic for a meaningful test)

My expectation is to support all DBDs that are themselves known to
work under the newest Perls and DBIs. Databases that already support
a feature when it is invoked as a SQL string will be supported for
that feature by Rosetta prior to those that don't and for which the
features need to be emulated.

Post by Dean Arnold
BTW: If you need a list of DBD's meeting said requirement, let me know,
I just pulled one down.

Sure, send it over.

Post by Dean Arnold
3. It cannot require any changes to either DBI or the
selected DBD's.

Yes.

Post by Dean Arnold
4. It must produce a database-independent conforming set of error codes
(feel free to use SQLSTATE aka $h->state)

Already done, though the current list of codes is short and pending updates.

Post by Dean Arnold
5. It must be uploaded to CPAN, and list, and demonstrably function against,
the DBD's selected in requirement (2) above.

I can only personally test against databases that are easy to install
on Mac OS X 10.3.9 and are free-as-in-beer (eg, SQLite, MySQL); for
others, I will need assistence to prove that they work.

Post by Dean Arnold
Once you've implemented the subclass, you'll have empirical proof
of the feasibility, and, more importantly, you'll be able to port
the subclass to DBIv2, without any additional burden on DBI
developers.

I accept your challenge.

-- Darren Duncan

[Dean Arnold]

Post by Darren Duncan

Post by Dean Arnold
BTW: If you need a list of DBD's meeting said requirement, let me know,
I just pulled one down.

Sure, send it over.

[ ] DBD-ADO-2.94.tar.gz 31-Jan-2005 02:40 41k GZIP compressed docume>
[ ] DBD-ASAny-1.13.tar.gz 31-Oct-2003 15:00 30k GZIP compressed docume>
[ ] DBD-Amazon-0.10.tar.gz 23-May-2005 15:41 58k GZIP compressed docume>
[ ] DBD-AnyData-0.08.tar.gz 19-Apr-2004 03:16 20k GZIP compressed docume>
[ ] DBD-CSV-0.22.tar.gz 31-Mar-2005 18:06 36k GZIP compressed docume>
[ ] DBD-Chart-0.81.tar.gz 26-Jan-2005 19:59 212k GZIP compressed docume>
[ ] DBD-DB2-0.78.tar.gz 19-Sep-2004 10:34 75k GZIP compressed docume>
[ ] DBD-File-0.34.tar.gz 21-Jun-2005 01:14 8k GZIP compressed docume>
[ ] DBD-Google-0.11.tar.gz 04-Mar-2004 18:51 20k GZIP compressed docume>
[ ] DBD-Informix-2005.01..> 14-Mar-2005 19:01 267k GZIP compressed docume>
[ ] DBD-Ingres-0.51.tar.gz 12-Jan-2004 06:18 46k GZIP compressed docume>
[ ] DBD-InterBase-0.43.t..> 25-Feb-2004 04:30 78k GZIP compressed docume>
[ ] DBD-LDAP-0.06.tar.gz 12-Mar-2004 21:48 25k GZIP compressed docume>
[ ] DBD-Log-0.22.tar.gz 27-May-2005 06:51 14k GZIP compressed docume>
[ ] DBD-MaxDB-7_5_00_26...> 18-Apr-2005 08:38 79k GZIP compressed docume>
[ ] DBD-Mimer-1.00.tar.gz 25-Nov-2003 15:51 71k GZIP compressed docume>
[ ] DBD-Mock-0.27.tar.gz 11-Jul-2005 11:36 34k GZIP compressed docume>
[ ] DBD-Multiplex-1.96.t..> 25-Jan-2005 17:30 9k GZIP compressed docume>
[ ] DBD-ODBC-1.13.tar.gz 08-Nov-2004 10:15 95k GZIP compressed docume>
[ ] DBD-Oracle-1.16.tar.gz 22-Oct-2004 05:17 230k GZIP compressed docume>
[ ] DBD-Pg-1.43.tar.gz 23-Jun-2005 08:09 128k GZIP compressed docume>
[ ] DBD-PgPP-0.05.readme 09-May-2004 08:06 3k
[ ] DBD-PgPP-0.05.tar.gz 13-May-2004 12:56 16k GZIP compressed docume>
[ ] DBD-PgSPI-0.02.tar.gz 06-Dec-2004 00:30 21k GZIP compressed docume>
[ ] DBD-Redbase-0.22.tar.gz 21-Oct-2003 22:51 28k GZIP compressed docume>
[ ] DBD-SQLite-1.09.tar.gz 20-Jun-2005 11:42 464k GZIP compressed docume>
[ ] DBD-SQLite2-0.33.tar.gz 10-Sep-2004 11:50 355k GZIP compressed docume>
[ ] DBD-Sprite-0.56.tar.gz 12-Jun-2005 21:52 86k GZIP compressed docume>
[ ] DBD-Sybase-1.05.tar.gz 19-Dec-2004 05:01 183k GZIP compressed docume>
[ ] DBD-TSM-0.04.readme 22-Mar-2005 16:05 2k
[ ] DBD-TSM-0.04.tar.gz 23-Jun-2005 16:32 9k GZIP compressed docume>
[ ] DBD-Teradata-1.20.ta..> 17-Sep-2004 19:27 36k GZIP compressed docume>
[ ] DBD-Trini-0.01.tar.gz 15-Jul-2003 03:18 21k GZIP compressed docume>
[ ] DBD-Unify-0.31.tgz 16-Mar-2004 11:07 31k GZIP compressed tar ar>
[ ] DBD-XBase-0.241.tar.gz 21-Nov-2003 09:25 109k GZIP compressed docume>
[ ] DBD-Yaswi-0.01.tar.gz 21-Feb-2005 19:46 4k GZIP compressed docume>
[ ] DBD-iPod-0.01.tar.gz 06-Jan-2005 02:41 13k GZIP compressed docume>
[ ] DBD-mysql-3.0002.tar.gz 11-Jul-2005 12:49 127k GZIP compressed docume>
[ ] DBD-mysql-AutoTypes-..> 02-Mar-2004 06:03 3k GZIP compressed docume>
[ ] DBD-mysql-SimpleMySQ..> 28-Apr-2004 16:39 4k GZIP compressed docume>
[ ] DBD-mysqlPP-0.04.tar.gz 24-Jan-2003 06:14 7k GZIP compressed docume>

- Dean

[Sam Vilain]

I should emphasize that I never expected to be able to send any type of
ASTs over the pipe to the database. They would still be interpreted by
the database driver for Perl and/or a wrapper thereon, into the database
native format. Its just that, to an application, it would appear that
the ASTs were going over the pipe, as to their effect, even though they
weren't behind the scenes.

Indeed. I think the principle "bug" to "fix" is getting away from this
notion that all you need to do is do a little bit of template-based
query building, use the DBI and magically expect all database portability
problems to go away.

And then, recommend an approach that *is* portable. Take your excellent
Rosetta infrastructure, pull the API to pieces, simplify the
documentation, then condone it as another simple and effective way to
write new database driven applications. And hopefully simplify the DBDs
that necessarily need to do SQL parsing along the way.

So, everyone who is still happy to code to a particular database's SQL
language can continue to do so, but we'll eventually move the Cargo Cult
away from the situation we're in today where there is a vague promise of
portability but so many caveats that it's practically impossible to
write portable code.

Sam.

[Sam Vilain]

Post by Dean Arnold
RE: LOBs and "SQL Parse Trees": having recently implemented
LOB support for a JDBC driver (and soon for a DBD), I can assure
you that SQL parse trees are unneeded to support them. For databases

Great!

Perhaps you can shed some light on how to do it for this, then.

SQL command;

INSERT INTO FOO (?, ?, ?, ?);

Column 3 is a BYTEA column in Pg and needs special peppering to work.

or this;

SELECT
*
FROM
FOO
WHERE
SOME_DATE_COLUMN > ?

SOME_DATE_COLUMN is the database native date type. On Oracle you'll
need to convert the ? to a 'TO_DATE(?)'.

Sam.

[Jonathan Leffler]

I've dropped perl6-language off the addressee list - this is pretty much
internals of DBI or DBD::WhatNot and not Perl language per se.

Post by Sam Vilain

Post by Dean Arnold
RE: LOBs and "SQL Parse Trees": having recently implemented
LOB support for a JDBC driver (and soon for a DBD), I can assure
you that SQL parse trees are unneeded to support them. For databases

Great!
Perhaps you can shed some light on how to do it for this, then.
SQL command;
INSERT INTO FOO (?, ?, ?, ?);
Column 3 is a BYTEA column in Pg and needs special peppering to work.
or this;
SELECT
*
FROM
FOO
WHERE
SOME_DATE_COLUMN > ?
SOME_DATE_COLUMN is the database native date type. On Oracle you'll
need to convert the ? to a 'TO_DATE(?)'.

DBD::Informix deals with both of these correctly in a variety of ways. The
DATE column is the easier - Informix Dynamic Server (IDS) is very good about
converting strings to DATE values - and to most other types. Also, since
Informix describes the types of the columns of the INSERT statement - and
can describe the input parameters of the SELECT statement (using DESCRIBE
INPUT) in the more recent versions of IDS - it can arrange the necessary
conversion.

The BYTEA example - corresponding to BYTE in IDS - is trickier. The string
you supply is converted into the relevant C structure - it happens to be a
loc_t in Informix ESQL/C - and then passed to the database. For INSERT, this
is easy because the types are described and the code in DBD::Informix can
tell that it needs to treat that properly. In other places, you have to use
the Informix type codes to convey the information to DBD::Informix. From
'perldoc DBD::Informix':


$upd = 'UPDATE SomeTable SET TextCol = ? WHERE Pkey = ?';
$sth = $dbh->prepare($upd);
$sth->bind_param(1, $blob_val, { ix_type => IX_TEXT });
$sth->bind_param(2, $pkey);
$sth->execute;

Internally, DBD::Informix knows that it must do the Perl string to Informix
loc_t mapping when this is specified.

Yes, it is a bit of work for the driver - but, for at least some drivers, it
is doable.

[Dean Arnold]

Post by Jonathan Leffler
I've dropped perl6-language off the addressee list - this is pretty much
internals of DBI or DBD::WhatNot and not Perl language per se.

Post by Sam Vilain

Post by Dean Arnold
RE: LOBs and "SQL Parse Trees": having recently implemented
LOB support for a JDBC driver (and soon for a DBD), I can assure
you that SQL parse trees are unneeded to support them. For databases

Great!
Perhaps you can shed some light on how to do it for this, then.
SQL command;
INSERT INTO FOO (?, ?, ?, ?);
Column 3 is a BYTEA column in Pg and needs special peppering to work.

What sort of "peppering" ? DBI provides SQL_BLOB, and SQL_CLOB
type descriptors (as well as SQL_BLOB_LOCATOR and SQL_CLOB_LOCATOR), so
presumably DBD::Pg (or any other DBD supporting LOBs) provides the
logic to map from

$sth->bind_param(3, $somelob, SQL_CLOB);

to whatever it needs to send on the wire. No different than, e.g.,
binding an integer or decimal(15,4). If some drivers don't support that,
thats a driver conformance issue, not a requirement for a new interface
mechanism.

Post by Jonathan Leffler

Post by Sam Vilain
or this;
SELECT
*
FROM
FOO
WHERE
SOME_DATE_COLUMN > ?
SOME_DATE_COLUMN is the database native date type. On Oracle you'll
need to convert the ? to a 'TO_DATE(?)'.

Er, why ? I haven't used DBD::Oracle lately, but assuming you
$sth->bind_param(1, '2005-07-13', SQL_DATE),
I'd assume DBD::Oracle would be smart enough to communicate that
to Oracle (either by munging the query text, or providing type codes
in the client request structure). I certainly handle that sort of
thing in DBD::Teradata, and I suspect DBD::ODBC would as well.

Post by Jonathan Leffler
DBD::Informix deals with both of these correctly in a variety of ways. The
DATE column is the easier - Informix Dynamic Server (IDS) is very good about
converting strings to DATE values - and to most other types. Also, since
Informix describes the types of the columns of the INSERT statement - and
can describe the input parameters of the SELECT statement (using DESCRIBE
INPUT) in the more recent versions of IDS - it can arrange the necessary
conversion.
The BYTEA example - corresponding to BYTE in IDS - is trickier. The string
you supply is converted into the relevant C structure - it happens to be a
loc_t in Informix ESQL/C - and then passed to the database. For INSERT, this
is easy because the types are described and the code in DBD::Informix can
tell that it needs to treat that properly. In other places, you have to use
the Informix type codes to convey the information to DBD::Informix. From
$upd = 'UPDATE SomeTable SET TextCol = ? WHERE Pkey = ?';
$sth = $dbh->prepare($upd);
$sth->bind_param(1, $blob_val, { ix_type => IX_TEXT });
$sth->bind_param(2, $pkey);
$sth->execute;
Internally, DBD::Informix knows that it must do the Perl string to Informix
loc_t mapping when this is specified.
Yes, it is a bit of work for the driver - but, for at least some drivers, it
is doable.

Have you considered updating DBD::Informix to use the SQL_BLOB/CLOB type codes ?

Regards,
Dean Arnold
Presicient Corp.

[Jonathan Leffler]

Post by Darren Duncan
[...]

Post by Jonathan Leffler
The BYTEA example - corresponding to BYTE in IDS - is trickier. The

string

Post by Jonathan Leffler
you supply is converted into the relevant C structure - it happens to be

a

Post by Jonathan Leffler
loc_t in Informix ESQL/C - and then passed to the database. For INSERT,

this

Post by Jonathan Leffler
is easy because the types are described and the code in DBD::Informix

can

Post by Jonathan Leffler
tell that it needs to treat that properly. In other places, you have to

use

Post by Jonathan Leffler
the Informix type codes to convey the information to DBD::Informix. From
$upd = 'UPDATE SomeTable SET TextCol = ? WHERE Pkey = ?';
$sth = $dbh->prepare($upd);
$sth->bind_param(1, $blob_val, { ix_type => IX_TEXT });
$sth->bind_param(2, $pkey);
$sth->execute;
Internally, DBD::Informix knows that it must do the Perl string to

Informix

Post by Jonathan Leffler
loc_t mapping when this is specified.
Yes, it is a bit of work for the driver - but, for at least some

drivers, it

Post by Jonathan Leffler
is doable.

Have you considered updating DBD::Informix to use the SQL_BLOB/CLOB type codes ?

It recognizes those too - AFAICR. I just prefer to use Informix type names
for Informix types. And there are also BLOB and CLOB type LOBs in Informix -
which are distinctly different from the BYTE and TEXT LOBs, and even less
supported by DBD::Informix.

[Sam Vilain]

Post by Dean Arnold

Post by Sam Vilain
Column 3 is a BYTEA column in Pg and needs special peppering to work.

What sort of "peppering" ? DBI provides SQL_BLOB, and SQL_CLOB
type descriptors (as well as SQL_BLOB_LOCATOR and SQL_CLOB_LOCATOR), so
presumably DBD::Pg (or any other DBD supporting LOBs) provides the
logic to map from
$sth->bind_param(3, $somelob, SQL_CLOB);

Post by Sam Vilain
SOME_DATE_COLUMN is the database native date type. On Oracle you'll
need to convert the ? to a 'TO_DATE(?)'.

Er, why ? I haven't used DBD::Oracle lately, but assuming you
$sth->bind_param(1, '2005-07-13', SQL_DATE),
I'd assume DBD::Oracle would be smart enough to communicate that

That bind_param peppering is precisely what I'm talking about, thanks
for demonstrating my point. This requirement to use "bind_param" to
explicitly tell the DBI which placeholders correspond to which types
is rarely mentioned on any introductions to DBI, and as a result, very
few people carry this out in practice or are prepared to do the
necessary re-work to code bases to perform it.

So, DBD drivers need to hope that the database driver is smart enough to
pull apart the query, match it against the schema and automatically
setup the type correctly. Perhaps many C database access libraries
provide enough information to do this, and pardon my ignorance for never
having written or worked on a DBD to this level - but I'm guessing that
such query introspection isn't always possible.

And, in a sense, requiring that the DBD is able to introspect the query
and DTRT is an "extra restriction" that DBD authors need to conform to,
setting the bar for conformance so high that it is practically impossible
to write portable database access code.

Please note that I'm not suggesting that we do away with the existing
interface, for people who don't care about writing portable database
code. But I'd like to be able to write, for instance;

use v6;
use DBI-2;

my $query = SQL {
SELECT
*
FROM
FOO
LEFT JOIN BAR
ON BAR.FOOID = FOO.ID
};

if ($one) {
$query &&= SQL::WhereClause {
ONE = $one
};
}

my $dbh = DBI.connect(:source("myapp"));
my $sth = $dbh.prepare($query);
my $resultset = $sth.execute();

for =$resultset -> @row {
...
}

So what's happening here?

Well, the SQL construct marks the beginning of a segment of code that
happens to be in an alternate grammar, corresponding to some level of
ANSI SQL. This builds an object which corresponds to that query. In
fact, this can happen at compile time! The SQL { } is actually a
closure that returns a SQL object when it is called.

The later SQL::WhereClause is the same; the variable isn't merely
interpolated, but is closed over, and included as if it were a
placeholder. The &&= assignment operator uses the overloaded &&
operator on the SQL object, which sees it is being given a query
fragment, and adds it into the appropriate point on the SQL AST.

This should all be quite transparent - of course, an optional (but
portable) method of writing database queries. And it's all deliciously
Perlish, yielding less fussing around with buggy code stitching together
queries, and more clean expression of queries using a standard language.

Of course it will be entirely possible to layer support for this sort of
thing atop any DBI interface; but this *is* a version 2, we do have
prototypes for the mechanics of all this stuff - and Done Right™, it
could actually fulfil the goal of DBI - being able to write
Database-driven applications that are truly independant of the database
product in use. DBI v1 cuts the porting time down to next to nothing;
but we can get it even closer!

Sorry to "bang on" about this for so long, I'm sure you're all getting
sick of it by now- I had hoped that the original suggestion was just
going to be acknowledged as a valid way to enhance portability and
flexibility and considered without too much rehashing of what to some
is an old topic.

Sam.

[Jochen Wiedmann]

Post by Sam Vilain
Of course it will be entirely possible to layer support for this sort of
thing atop any DBI interface;

Exactly my point. Please be so kind as to implement your ideas in a
DBI extension. Time and community will prove whether you are right by
using your extension or not.

Fact is, that there are quite some drivers which will never be able to
adhere to your wishes. Blocking them implementing a DBI2 driver seems
(to me) to be a larger problem, compared with the need of using a DBI2
subclass and not DBI2 directly.


Jochen

[Greg Sabino Mullane]

it could actually fulfil the goal of DBI - being able to write
Database-driven applications that are truly independant of the database
product in use. DBI v1 cuts the porting time down to next to nothing;
but we can get it even closer!

I don't think the goal of DBI is to acheive total database independence.
I'm not entirely convinced that applications need to be totally independent
of the underlying database: at the least, the main SQL abstraction layer
needs to happen at some other layer than the DBI.

I can't ever imagine rewriting my SQL statements (some of which are very
complex and yes, customized for a particular RDBMS backend) to fit into
some abstract description language. Nor would I trust some program
(DBI or otherwise) to have the AI necessary to port my SQL statements
from one database to another. It might mostly work, but it would certainly
not be optimized. If I build an app that needs to run against different backends,
I hard-code the SQL differences into the app. Perhaps if every DB was
SQL-1999 compliant, that would not be necessary, but SQL compliance is a
goal of very few RDBMS's these days. :)

In short, even if it is possible (which seems unlikely), DBI is not the
place for it.

- --
Greg Sabino Mullane ***@turnstep.com
PGP Key: 0x14964AC8 200507141317
https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

[Reidy, Ron]

-----Original Message-----
From: Sam Vilain [mailto:***@vilain.net]
Sent: Tuesday, July 12, 2005 5:04 PM
To: Dean Arnold
Cc: dbi-***@perl.org; dbi-***@perl.org; perl6-***@perl.org
Subject: Re: DBI v2 - The Plan and How You Can Help

Post by Sam Vilain

Post by Dean Arnold
RE: LOBs and "SQL Parse Trees": having recently implemented
LOB support for a JDBC driver (and soon for a DBD), I can assure
you that SQL parse trees are unneeded to support them. For databases

or this;
SELECT
*
FROM
FOO
WHERE
SOME_DATE_COLUMN > ?
SOME_DATE_COLUMN is the database native date type. On Oracle you'll
need to convert the ? to a 'TO_DATE(?)'.

No you do not. The SQL engine will perform an implicit commit of the data.

-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.

Post by Sam Vilain
Sam.

This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone [phone], ext. 0), and then delete it from your system.

[Reidy, Ron]

Sorry, instead of implicit 'commit', I mean to say implicit conversion.

-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.


-----Original Message-----
From: Reidy, Ron
Sent: Wednesday, July 13, 2005 9:06 AM
To: Sam Vilain; Dean Arnold
Cc: dbi-***@perl.org; dbi-***@perl.org; perl6-***@perl.org
Subject: RE: DBI v2 - The Plan and How You Can Help


-----Original Message-----
From: Sam Vilain [mailto:***@vilain.net]
Sent: Tuesday, July 12, 2005 5:04 PM
To: Dean Arnold
Cc: dbi-***@perl.org; dbi-***@perl.org; perl6-***@perl.org
Subject: Re: DBI v2 - The Plan and How You Can Help

Post by Sam Vilain

Post by Dean Arnold
RE: LOBs and "SQL Parse Trees": having recently implemented
LOB support for a JDBC driver (and soon for a DBD), I can assure
you that SQL parse trees are unneeded to support them. For databases

or this;
SELECT
*
FROM
FOO
WHERE
SOME_DATE_COLUMN > ?
SOME_DATE_COLUMN is the database native date type. On Oracle you'll
need to convert the ? to a 'TO_DATE(?)'.

No you do not. The SQL engine will perform an implicit commit of the data.

-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.

Post by Sam Vilain
Sam.

This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone [phone], ext. 0), and then delete it from your system.


This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone [phone], ext. 0), and then delete it from your system.