FAQ
I want to run a query that will perform a phrase search on all fields in
the table. I thought that it seemed really logical to write the statement
like:

my $dbh = DBI->connect('dbi:ODBC:database_name') || die DBI::errstr;
my $sth = $dbh->prepare("SELECT *
FROM table_name
WHERE * LIKE '%$in{'search term'}%'
ORDER BY name
");
$sth->execute();
while (($sid, $name)=$sth->fetchrow_array){
print qq~
....truncated...
~;
}
$dbh->disconnect();


But I am wrong.... miserably, terribly wrong. Perhaps this is off topic,
since it relates more to SQL than DBI, specifically, so please send any
replies directly to mata@matatech.tzo.com. How would one run a
phrase search across all the fields in a table? I am prepared for having
to type 96 OR's if I have to do so, but I am regretting it nonetheless. ;)

Thanks in advance.

Brad Smith

Search Discussions

  • Herbold, John W. at Nov 5, 2002 at 2:18 pm
    I have done something like that. I used two connections to the database.
    The first "outer" connection retrieved the column names from the system
    table. On DB2 this was Select name from sysibm.syscolumns where tbname =
    'favorate_table'; Then using Perl I made the calls to the table looking for
    the data I wanted in each column. So there were two separate DBI
    connections, one inside the other. Please note that you might have to check
    the column type as well, to check for CHAR or number columns. This will
    tell you to put ' or not around the values.

    Let me know if my gibberish was not clear enough.

    Thanks,

    John W. Herbold Jr.
    IS Specialist/DBA


    -----Original Message-----
    From: Brad Smith
    Sent: Tuesday, November 05, 2002 7:56 AM
    To: dbi-users@perl.org
    Subject: SQL question

    I want to run a query that will perform a phrase search on all fields in
    the table. I thought that it seemed really logical to write the statement
    like:

    my $dbh = DBI->connect('dbi:ODBC:database_name') || die DBI::errstr;
    my $sth = $dbh->prepare("SELECT *
    FROM table_name
    WHERE * LIKE '%$in{'search term'}%'
    ORDER BY name
    ");
    $sth->execute();
    while (($sid, $name)=$sth->fetchrow_array){
    print qq~
    .....truncated...
    ~;
    }
    $dbh->disconnect();


    But I am wrong.... miserably, terribly wrong. Perhaps this is off topic,
    since it relates more to SQL than DBI, specifically, so please send any
    replies directly to mata@matatech.tzo.com. How would one run a
    phrase search across all the fields in a table? I am prepared for having
    to type 96 OR's if I have to do so, but I am regretting it nonetheless. ;)

    Thanks in advance.

    Brad Smith
  • Mark Thornber at Nov 5, 2002 at 2:41 pm
    or, alternatively, you can write SQL to read the system catalogue and
    output _more_ SQL to do the search required. In one extreme case I ended
    up with a cascade of three queries - mete-meta-query -> meta-query -> query
    -> result set. The advantage is one can build up the steps one at a time
    and eye-ball the output of each stage (since it will be in ascii).

    HTH
    --
    MarkT
    At 14:17 05/11/02, Herbold, John W. wrote:

    I have done something like that. I used two connections to the database.
    The first "outer" connection retrieved the column names from the system
    table. On DB2 this was Select name from sysibm.syscolumns where tbname =
    'favorate_table'; Then using Perl I made the calls to the table looking for
    the data I wanted in each column. So there were two separate DBI
    connections, one inside the other. Please note that you might have to check
    the column type as well, to check for CHAR or number columns. This will
    tell you to put ' or not around the values.

    Let me know if my gibberish was not clear enough.

    Thanks,

    John W. Herbold Jr.
    IS Specialist/DBA


    -----Original Message-----
    From: Brad Smith
    Sent: Tuesday, November 05, 2002 7:56 AM
    To: dbi-users@perl.org
    Subject: SQL question

    I want to run a query that will perform a phrase search on all fields in
    the table. I thought that it seemed really logical to write the statement
    like:

    my $dbh = DBI->connect('dbi:ODBC:database_name') || die DBI::errstr;
    my $sth = $dbh->prepare("SELECT *
    FROM table_name
    WHERE * LIKE '%$in{'search term'}%'
    ORDER BY name
    ");
    $sth->execute();
    while (($sid, $name)=$sth->fetchrow_array){
    print qq~
    .....truncated...
    ~;
    }
    $dbh->disconnect();


    But I am wrong.... miserably, terribly wrong. Perhaps this is off topic,
    since it relates more to SQL than DBI, specifically, so please send any
    replies directly to mata@matatech.tzo.com. How would one run a
    phrase search across all the fields in a table? I am prepared for having
    to type 96 OR's if I have to do so, but I am regretting it nonetheless. ;)

    Thanks in advance.

    Brad Smith


    ---
    Incoming mail is certified Virus Free.
    Checked by AVG anti-virus system (http://www.grisoft.com).
    Version: 6.0.408 / Virus Database: 230 - Release Date: 24/10/02
  • Michael A Chase at Nov 5, 2002 at 3:05 pm

    On Tue, 05 Nov 2002 08:56:25 -0500 Brad Smith wrote:

    I want to run a query that will perform a phrase search on all fields in
    the table. I thought that it seemed really logical to write the
    statement
    like:

    my $dbh = DBI->connect('dbi:ODBC:database_name') || die DBI::errstr;
    my $sth = $dbh->prepare("SELECT *
    FROM table_name
    WHERE * LIKE '%$in{'search term'}%'
    ORDER BY name
    ");
    $sth->execute();
    while (($sid, $name)=$sth->fetchrow_array){
    print qq~
    ....truncated...
    ~;
    }
    $dbh->disconnect();


    But I am wrong.... miserably, terribly wrong. Perhaps this is off topic,
    since it relates more to SQL than DBI, specifically, so please send any
    replies directly to mata@matatech.tzo.com. How would one run a
    phrase search across all the fields in a table? I am prepared for having
    to type 96 OR's if I have to do so, but I am regretting it nonetheless.
    I can get it a little on topic by mentioning that there is nothing that
    says you have to do all that typing yourself. Perl is very good at
    generating the SQL for you.

    I generally avoid * even in the select list because you can get bit rather
    badly if someone recreates the table for some reason and there are more or
    fewer columns or the order of the columns changes.

    # Untested, but I've done similar things in other systems
    $dbh -> {RaiseError} = 1; # ALWAYS check for errors

    # Build list of columns to search
    my %bExclude = map { ( lc $_, 1 ) } qw( sid, name );
    my $sth = $dbh -> prepare( SELECT * FROM table_name WHERE 1 = 0" );
    $sth -> execute();
    my @sWhereCols = grep { ! $bExclude{lc $_} } @{$sth -> {NAME}};
    $sth -> finish();

    # Build where clause and list of values
    # You could use a single value if you can use named bind variables,
    # but not all databases allow them
    my ( @sValue, @sWhere );
    foreach ( @sWhereCols ) {
    push @sValue, "%" . $phrase . "%";
    push @sWhere, " OR $_ LIKE ?";
    }
    $sWhere[0] =~ s/ OR/WHERE/;

    # Search
    my ( $iSid, $sName );
    $sth = $dbh -> prepare( join "\n",
    "SELECT sid, name",
    " FROM table_name",
    @sWhere,
    " ORDER BY name, sid"
    );
    $sth -> execute( @sValue );
    $sth -> bind_columns( \( $iSid, $sName ) );
    while ( $sth -> fetch ) {
    # Do something with $iSid and $sName
    }

    --
    Mac :})
    ** I normally forward private questions to the appropriate mail list. **
    Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
    Give a hobbit a fish and he eats fish for a day.
    Give a hobbit a ring and he eats fish for an age.
  • Jared Still at Nov 8, 2002 at 1:17 am
    Well, this doesn't exactly answer your question...

    Why are you searching 90+ columns anyway?

    Is this a large table? Will the query be run often?

    If the answer to both of the above is yes, be prepared
    to have a lengthy discussion with your DBA.

    Jared ( a DBA )
    On Tuesday 05 November 2002 05:56, Brad Smith wrote:
    I want to run a query that will perform a phrase search on all fields in
    the table. I thought that it seemed really logical to write the statement
    like:

    my $dbh = DBI->connect('dbi:ODBC:database_name') || die DBI::errstr;
    my $sth = $dbh->prepare("SELECT *
    FROM table_name
    WHERE * LIKE '%$in{'search term'}%'
    ORDER BY name
    ");
    $sth->execute();
    while (($sid, $name)=$sth->fetchrow_array){
    print qq~
    ....truncated...
    ~;
    }
    $dbh->disconnect();


    But I am wrong.... miserably, terribly wrong. Perhaps this is off topic,
    since it relates more to SQL than DBI, specifically, so please send any
    replies directly to mata@matatech.tzo.com. How would one run a
    phrase search across all the fields in a table? I am prepared for having
    to type 96 OR's if I have to do so, but I am regretting it nonetheless. ;)

    Thanks in advance.

    Brad Smith
  • Jeff Boes at Nov 8, 2002 at 2:14 pm

    On Tue, 2002-11-05 at 08:56, Brad Smith wrote:
    I want to run a query that will perform a phrase search on all fields in
    the table.
    I think the best approach would be to construct a view that normalizes
    this table. Given:

    create table foo (pkey integer primary key, aaa text, bbb text, ccc
    text);


    I would create a view that looked like this:

    create view regular_foo as
    select pkey, 'aaa' as col_type, aaa as col
    from foo
    union
    select pkey, 'bbb' as col_type, bbb as col
    from foo
    union
    select pkey, 'ccc' as col_type, ccc as col
    from foo;


    Now you can search all the text columns via

    select * from regular_foo where col like '%pattern%';


    --
    Jeff Boes vox 616.226.9550 ext 24
    Database Engineer fax 616.349.9076
    Nexcerpt, Inc. http://www.nexcerpt.com
    ...Nexcerpt... Extend your Expertise

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedNov 5, '02 at 1:56p
activeNov 8, '02 at 2:14p
posts6
users6
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase