Re: Standard DBI Proposal

On Oct 28, 3:56 am, "Donal K. Fellows" <donal.k.fell...@xxxxxxxxx>
wrote:
Darren New wrote:
It would be nice, but how do you handle code like
if ("" != $_GET['name'])
$sql .= "WHERE name like '" . esc($_GET['name']) . "%' AND ";
(Yes, it's PHP. Deal. :-)

I often do this sort of thing with a dozen different form fields,
building up a query clause by clause. If I had only prepared statements,
would I need 2^N statements?

No, since the interface layer should be hiding all that stuff from
you, and you'd just be writing:

if {$GET(name) ne ""} {
# I can't remember right now if SQLite handles array vars
set paramName $GET(name)
append sql {WHERE name LIKE '$paramName%' AND }
}

Donal,

Notice the example I gave above. Most of what you do in your example
is also handled completely behind the scenes. In other words, you
define parameters ahead of time, including whether it is required, a
default to use, the type, and for strings a length (because database
chars usually have max lengths). So, on a separate page you define
what a query is and the associated params. Then you can reuse the
query anywhere. Here is the setup page (with still a lot of the magic
hidden behind):

set queryName remodel::InsertIntoMaterials

::twt::db::query::new ${queryName} {
insert into
materials
(material, unit_of_measure, units, cost)
values
($material, $unit_of_measure, $units, $cost)
}

::twt::db::query::setDataSource $queryName pg4.remodel

::twt::db::query::addParam $queryName material varchar "" false 64
::twt::db::query::addParam $queryName unit_of_measure varchar "" false
32
::twt::db::query::addParam $queryName units numeric "" false
::twt::db::query::addParam $queryName cost numeric "" false

When the 'execute' function is run from the previous example, the
passed in params are validated. Depending upon type, they are properly
quoted. This method makes it easy to use what looks line regular Tcl
syntax in defining the query.

However, I will admit my bias against building up complex SQL select
queries in a programmatic style. My opinion is that queries are
valuable documentation. Code which slowly builds up to a huge SQL
query obfuscates the intent of the query. This is not the element of
reuse in SQL. If we did this in Tcl, it would be like building up a
procedure body chunk-by-chunk just to execute it once. Everyone would
know to laugh at such a method of creating and executing code, but it
is okay, for some reason, in SQL.

SQL is not a procedural language, it isn't an OO language. For queries
which return results, it helps to provide the full query in one place.
Otherwise, since the handling of special cases will eventually devolve
into ad-hoc work-arounds, debugging a problem in the future (and there
will be problems), becomes a nightmare. You simply cannot recreate SQL
select queries in any simpler terms.

This is not true for simple, one row, insert/update/delete queries. My
example code just above would be completely unnecessary, as would the
page which inserted the params and ran the execute command, in another
system which I developed. In this other system, the only thing
required, after a mostly declarative setup would be to post an HTML
form to a processing page, for instance you would write, not a bunch
of Tcl code, but one form to do what you guys are after:

<form method="POST" action="/qw/qw.tcl">
Material:<input type="text" name="new.materials.material.0"
value=""><br>
Unit-Of-Measure:<input type="text" name="new.materials.uom.0"
value=""><br>
Units:<input type="text" name="new.materials.unit.0" value=""><br>
Cost:<input type="text" name="new.materials.cost.0" value=""><br>
<input type="submit" value="Insert Material">
</form>

The point of a system like this is to centralize the decision of what
to do with a particular parameter. Since it is part of a database
table, the requirements are always the same no matter how many times
you manipulate the parameter. For instance, if you need to update the
just inserted material, assuming the material_id is 1234:

<input type="text" name="set.materials.material.1234" value="My
Material">

Or delete some rows:

Delete 'My Material': <input type="checkbox" name="del.materials.1234"
value="1"><br>
Delete 'My Other Material': <input type="checkbox" name="del.materials.
5678" value="1">

So, in addition to handling all the boring details, you can do all of
these operations at the same time, in combination, from one form.

.

Relevant Pages

  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
  • Re: Dynamic query problem
    ... On Oct 17, 9:41 am, Andy Hull ... If we were to provide a fully featured dynamic query generator we would have ... Provided with already built queries which they can edit ... SQL and see where it is the same as for the other queries and where it ...
    (microsoft.public.access.queries)
  • Re: CONTAINS performance
    ... mark, FTS needs to be very carefully tuned to achieve second response times, ... see SQL Server 2000 BOL title "Full-text Search Recommendations" for more ... When you include the "TOP 100" in your query, you are in fact limiting the ... valid for SQL queries, they often do not apply to FTS queries because the FT ...
    (microsoft.public.sqlserver.fulltext)
  • Re: creating and executing queries in VBA.
    ... Execute it into what? ... You can't just "execute" a select query. ... When you execute sql it has to go into a report, or a form or a record set. ...
    (microsoft.public.access.modulesdaovba)