[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Keyword querying a SQL database

Posted on 1998-08-06
8
Medium Priority
?
241 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 100 total points
ID: 1858868
please then close this question...

0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video teaches users how to migrate an existing Wordpress website to a new domain.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Suggested Courses

649 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