Solved

Keyword querying a SQL database

Posted on 1998-08-06
8
235 Views
Last Modified: 2013-12-25
I'm looking for a way to perform full-text keyword searching on particular columns in a SQL database (Sybase, SQL Server), with wildcards, boolean ops and natural language if possible, via ASP scripts on IIS.  

Microsoft Index Server only searches files in directories, not queries on a database.  Their suggestion is to output each database row to an individual file, then index it. Kludge!!

Cold Fusion lets you query a database via the Verity search engine that comes packaged with it.  Very nice, but we gave up CF because it's slow, buggy and lacks a decent programming language.
0
Comment
Question by:umkibbi0
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 2

Expert Comment

by:vendrig
ID: 1858861
You want to use the LIKE operator in SQL. That's for wildcard searches. ASP (at least with Access) uses % instead of * and _ instead of ?, which is a little strange. Example:

SELECT * FROM myTable WHERE (message LIKE '%this%') OR (message LIKE '%that%');

LIKE is for exact matches with wildcards. A fuzzy alternative is
SOUNDEX, which acts as a sounds like. It performs a phonetic match:
SELECT * FROM myTable WHERE SOUNDEX(name)=SOUNDEX('umkibbi');

This is nothing compared to the Verity engine in ColdFusion of course, but databases aren't made for full-text retrieval...
0
 

Author Comment

by:umkibbi0
ID: 1858862
LIKE and SOUNDEX statements will kill any RDBMS unless you don't care about how long the query takes.  I need OLTP-type response times: under five seconds for each query.  The user interface will be similar to search engines like Altavista, but performing full-text searches on a database.  I anticipate high load, so performance is of the utmost importance.
0
 
LVL 10

Expert Comment

by:MasseyM
ID: 1858863
pass the SQL command line to it...

SELECT * FROM "tablename" WHERE value = "searchstring"

That will do it...

Here is a simple script:
<%
Param = Request.QueryString("Param")
Data = Request.QueryString("Data")
CurrentYear = 0
CurrentDate = Date()
%>
<%
If IsObject(Session("YourDSN_conn")) Then
    Set conn = Session("YourDSN_conn")
Else
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "YourDSN","",""
    Set Session("YourDSN_conn") = conn
End If
%>
<%
    sql = "SELECT table.field, etc.. list all fields..., table.field "
    sql = sql & "FROM [Missionaries] "
    If cstr(Param) <> "" And cstr(Data) <> "" Then
    sql = sql & " WHERE table.field = "searchstring"
    End If
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
%>

<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>

your HTML here... substituting the database values etc...

<%
rs.MoveNext
loop%>


- Matt


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:umkibbi0
ID: 1858864
That's no better than the first answer.  "SELECT * WHERE FOO = "keyword" only works on exact matches - at least using LIKE or SOUNDEX can match a subset of the column text, but these are too slow.  RDBMS's are not designed to perform complicated keyword matches - they simply don't have the features and besides that, they don't have the performance.

What I'm looking for is a way to create a keyword index (external to the database) based on an extraction of my database tables, and be able to use a sophisticated search language to query it.  The results from this query should be primary keys that I could use to extract full records from the actual database.

By sophisticated search language I mean Boolean operators (AND,OR,NOT), word stemming (child matches "child, children"), wildcards, stop words (ignore common words "and,the,at") etc.
0
 
LVL 2

Expert Comment

by:vendrig
ID: 1858865
In other words: you want to build your own Verity Topic or Personal Librarian in a SQL database and you want it to be quick. That's not very realistic. Then it's even better to do it the Microsoft way, because you'll end up doing the same thing, only more complicated. You'd be better off looking for a COM object that can do it all, because full-text programs are optimized for inverted files and so.
0
 
LVL 4

Expert Comment

by:mitek
ID: 1858866
AND THAT'S AN EASY QUESTION ?
This question is worth millions of points :)))))
What you essentially asked is like "can I have a space shuttle to go to the moon, and for fifty bucks, and now please ?"
Then someone offered you a bike, then a deltaplane. Of course, it's not good enough ;))
Writing a good (scalable) search engine like the one described here is no easier than building a space shuttle.
I don't think you'll get an answer to this one :)))
0
 

Author Comment

by:umkibbi0
ID: 1858867
Acutually, we have found the answer on our own. It's called Microsoft SiteServer, and we have successfully indexed all text fields in our database (Sybase on Unix), and have created the scripts that use ASP and ADO to take the returned index values, then extract the rows from the database, then spit 'em out to the client.

Millions of points? Looks like we're giving out none. And I don't know about you, but "good enough" is never "good enough" as far as we're concerned. Looks like space shuttles aren't as expensive as you thought.
0
 
LVL 10

Accepted Solution

by:
MasseyM earned 50 total points
ID: 1858868
please then close this question...

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question