Skip site navigation (1) Skip section navigation (2)

Re: Search function

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: KeithW(at)NarrowPathInc(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Search function
Date: 2005-06-23 13:45:35
Message-ID: 82fc2f7bf7f7337eb7e4f057bcd6c76f@mail.nih.gov (view raw or flat)
Thread:
Lists: 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



In response to

pgsql-novice by date

Next:From: Steve TucknottDate: 2005-06-23 16:01:11
Subject: Grouped item in a subquery
Previous:From: Keith WorthingtonDate: 2005-06-23 12:03:19
Subject: Re: Search function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group