Re: Search function - Mailing list pgsql-novice

On Jun 23, 2005, at 8:08 AM, Keith Worthington wrote:

>
>>> Hi All,
>>>
>>> I am working on an application that has a search dialog.  The dialog
>>> is automatically populated with all of the available fields.  It
>>> gets the field names from the views that were used on the form that
>>> the search dialog was launched from.
>>>
>>> The issue that is slowly getting unmanageable is handling the
>>> different data types.  If it is a date do this, if it is a string do
>>> that and if it is a boolean do something else.
>>>
>>> I would like to remove this complexity from the application.
>>>
>>> I am hoping that there is a way given the view/column names that I
>>> can either
>>> 1) dynamically build the WHERE clause
>>> 2) dynamically build the whole query
>>> 3) dynamically build the whole query, run it and return the results
>>>
>>> Has anyone tried something like this before?
>>>
>>> --
>>> Kind Regards,
>>> Keith
>> Keith,
>> I don't know what interface you are using to build your application,
>> but this sounds like a good case for a database abstraction layer.
>> If your interface language allows such a database abstraction layer
>> (Class::DBI in perl, PEAR DB in PHP, others in other languages), you
>> could almost certainly benefit from it.  Then, your application need
>> only interact with the database abstraction layer, not the database
>> itself.  You can, of course, move down a level and access the
>> database directly, but the point is that you don't need to most of
>> the time.
>> So, what interface are you using to build your application?
>> Sean
>
> Sean,
>
> We are using Visual Basic v6 to build the application.
>
> I am not sure that I understand the concept of an abstraction layer..

Keith,

In perl, Class::DBI works like this:

Each database table is treated as an object.  It has methods such as
"search", "insert", "delete", etc.  You can assign your own methods to
it, as well.  So, if you have a table like "FOO" with columns bar and
baz, you can do:

my $foo = FOO->search(bar => '231', baz => 'washington')

This will create the SQL "SELECT * FROM FOO WHERE bar=231 and baz =
'washington'" and return the result as a set of FOO objects.  You can
then access the information in a FOO object by doing something like:

$foo->bar  which returns 231
$foo->baz  which returns 'washington'

This isn't really the neat part.  The neat part is that you can
construct much more complex where clauses using helpers so that you can
take the input from your form and easily construct your where clause
dynamically like:

my $foo2 = FOO->search_where(bar => {'>' , 120}, baz =>
['cincinatti','washington','baltimore'])

which will make a SQL query like:

select * from "FOO" where bar > 120 AND BAZ IN
('cincinatti','washington','baltimore');

You can make the queries nearly arbitrarily complex, but the important
point is that you take the input from your form and just plug it into
the methods and it generates the query for you, including binding
variables, etc.  There are many other tools in perl to take form input
and generate SQL.  Perhaps visual basic has such tools, also?
Generating SQL is not the same as database abstraction, but generating
SQL is a necessary part of database abstraction.

See here for some random google hits on the topic:
http://joseph.randomnetworks.com/archives/2004/07/08/what-is-a-
database-abstraction-layer/
http://www.perl.com/pub/a/2002/11/27/classdbi.html

Sean