[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Keyword querying a SQL database

Posted on 1998-08-06
8
Medium Priority
?
242 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

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.

Question has a verified solution.

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

This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
Strategic internal linking is often considered an SEO power technique, especially for content marketing. Do you need to hire an SEO agency to optimize you internal linking? No, this article will help you understand the basics of internal linking and…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses
Course of the Month19 days, 15 hours left to enroll

872 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