Solved

Keyword querying a SQL database

Posted on 1998-08-06
8
233 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
cookies analysis tools 2 91
Web Reply Form - PHP with Upload 4 66
What language/protocol is the Angular Chat? 2 73
push logos in footer up higher 5 20
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to dynamically set the form action using jQuery.

776 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