Database Code
  Home arrow Database Code arrow An ASP include that exposes a class to gen...
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
ASP Web Hosting  
ASP.NET Web Hosting 
Dedicated Servers 
Actuate Whitepapers 
Moblin 
Windows Web Hosting
 
IBM® developerWorks 
Sun Developer Network 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
DATABASE CODE

An ASP include that exposes a class to generate an SQL statements
By: aspfree
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 2 stars2 stars2 stars2 stars2 stars / 2
    2000-03-05

    Table of Contents:

    Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT

    Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

    This includes support advanced search Symbols like (+,-,&,|,!) to help search database. By Salim Naim


    </HEAD>
    <BODY>

    <font face=Arial size=2 color=green><b>Start time&nbsp;:&nbsp;<%=starttime%></b></font><br>
    <font face=Arial size=2 color=red><b>Finish time&nbsp;:&nbsp;<%=finishtime%></b></font><br>
    <br>

    <table bgcolor=Black border=0 cellpadding=1 cellspacing=0>
    <tr><td><font face=Arial size=1 color=white><b>Database Search</b></font></td></tr>
    <tr><td>
    <table width=100% bgcolor=White border=0 cellpadding=2 cellspacing=0><form>
    <tr>
    <td><input type=input name=str size=40 value="<%=server.HTMLEncode(Request.Querystring("str"))%>"></td>
    <td><input type=submit value="Search"></td>
    </tr>
    <tr><td colspan=2>
    <font face=Arial size=1 color=red><b>
    + : include filter the search result must include the keyword.<br>
    - : exclude filter, search result must not contain keyword.<br>
    & : logical AND the search must contain keyword1 AND keyword2.<br>
    | : logical OR the search must contain keyword1 OR keyword2.<br>
    ! : logical NOT the search must contain keyword1 BUT NOT keyword2.<br>
    </b></font>
    </td></tr>
    </form></table>
    </td></tr></table>

    <p><font face=Arial size=2 color=black><u>Generated SQL Statement</u></font><br>
    <blockquote><font face=Arial size=1 color=blue><%=strSQL%></font></blockquote>
    </p>


    </BODY>
    </HTML>
     

    Page Two -- The include page..

    Search.asp

    <script language="JSCRIPT" RUNAT=Server>
    //------------------------------------------------------------------------------
    //This is a generic Search keyword to SQL statement include.
    //the library takes search keywords and produces a equivalent SQL statement.
    //the search syntax accounts for advance symbols
    // + : include filter (the search result must include the keyword.
    // - : exclude filter, search result must not contain keyword.
    // & : logical AND the search must contain keyword1 AND keyword2
    // | : logical OR the search must contain keyword1 OR keyword2
    // ! : logical NOT the search must contain keyword1 BUT NOT keyword2
    //------------------------------------------------------------------------------
    //Written By: Salim Naim 26/2/2000
    //Note: I'm more than happy to fix any Bugs or answer any questions regarding the
    //sample,i can be reached at snaim@zfp.com or salimn@yahoo.com
    //------------------------------------------------------------------------------
    //Warning: This sample is assuming some knowlegde of the Structured Query Language(SQL)
    //------------------------------------------------------------------------------

    //------------------------------------------------------------------------------------
    //function: _addword
    //Description: the keyword object.
    //------------------------------------------------------------------------------------
    function _word()
    {
    this.phrase="";
    this.type=""; 
    }

    //------------------------------------------------------------------------------------
    //function: _addword
    //Description: the helper function that adds a keyword object to the KeywordList
    //------------------------------------------------------------------------------------
    function _addword(phrase,type)
    {
    var obj = new _word()
    obj.phrase = phrase;
    obj.type = type;
    return(obj);
    }

    //------------------------------------------------------------------------------------
    //function: KeywordList
    //Description: keyword link list.
    //------------------------------------------------------------------------------------
    function KeywordList()
    {
    this.count=0;
    //the word is an object 
    this.word = new _word();
    this.add = AddKeyword;
    return this;
    }


    //------------------------------------------------------------------------------------
    //function: AddKeyword
    //Description: Add unique keywords into the search keyword list.
    //------------------------------------------------------------------------------------

    function AddKeyword(word,type)
    {
    if(word.length > 0 ){
    for(var i=0;i<this.count;i++)
    if(this.word[i].phrase == word)
    return; 
    this.word[this.count++] = _addword(word,type); 
    }
    }

    //------------------------------------------------------------------------------------
    //function: normalizeWord
    //Description: this function normalizes words inorder to increase hit results.
    //------------------------------------------------------------------------------------
    function normalizeWord(keyword)

    var esc = keyword.toLowerCase(); 
    var kw = "";

    for(var i=0;i < esc.length;i++){
    var ch = esc.charAt(i);
    if(ch == '%')
    i += 2;
    else
    kw += ch;
    }

    var len = kw.length; 
    if (kw.charAt(len-1) == "s" && kw.charAt(len-2) != "s"){
    kw = kw.substring(0,len-1);
    len--;
    }
    //acounnt for the "
    if (kw.charAt(len-1) == "\""){
    kw = kw.substring(0,len-1);
    len--;

    if (kw.charAt(0) == "\""){
    kw = kw.substring(1,len);
    len--;


    if(kw == "in" && len == 2){
    kw = "";
    len -=2;
    }
    if(kw.substring(len-2,len) == "ly"){
    kw = kw.substring(0,len-2);
    len -= 2;
    }
    if(kw.substring(len-2,len) == "ed"){
    kw = kw.substring(0,len-1);
    len --;
    }
    if(kw.substring(len-2,len) == "er"){
    kw = kw.substring(0,len-1);
    len --;
    }
    if(kw.substring(len-2,len) == "ie"){
    kw = kw.substring(0,len-2) + y;
    len --;
    }
    if(kw.substring(len-3,len) == "ing" && len > 5){
    kw = kw.substring(0,len-3);
    len -= 3;
    if(isVowel(kw.charAt(len-2)) && !isVowel(kw.charAt(len-3))){
    kw += "e";
    len++;
    }
    }
    if(kw.charAt(len-1) == "e")
    if(!isVowel(kw.charAt(len-3))){
    kw = kw.substring(0,len-1);
    len--;
    }
    if(len > 1 && (kw.charAt(len-1) == kw.charAt(len-2))){
    kw = kw.substring(0,len-1);
    len--;

    return(kw); 
    }

    //------------------------------------------------------------------------------------
    //function: isStopword
    //Description: this function excluded words that should not be classified as keywords.
    //------------------------------------------------------------------------------------
    function isStopword(word)
    {
    var wd = word.toLowerCase();
    if(wd == "a" || wd == "an" || wd == "and" ||
    wd == "or" || wd == "the" || wd == "is" || wd == "this" || wd == "all")
    return(true);
    else
    return(false);

    }

    //------------------------------------------------------------------------------------
    //function: parseKeywords
    //Description: this function adds unique filenames to the list of database fields that
    // we want to search.
    //------------------------------------------------------------------------------------
    //Note: this is function was adopted from the BOOK using javascript, modifications have
    // been added inorder to add keyword search symbols
    //------------------------------------------------------------------------------------
    function parseKeywords(str)
    {
    var list = new KeywordList();
    var inword = false;
    var word = "";
    var len = str.length; 
    var firstcharacter = true;
    var Wordtype = ""; 

    if(len <=0 )
    return

    for(var i=0;i<len;i++){
    var ch = str.charAt(i);
    //check the first character in the keyword
    if(firstcharacter){ 
    if(isSymbol(ch))
    Wordtype = getSymbolType(ch); 
    firstcharacter = false;


    if(isWhitespace(ch) || isDelimiter(ch)){ 
    if(inword){ 
    if(!isStopword(word))
    list.add(normalizeWord(word),Wordtype);

    word = "";
    inword = false;
    firstcharacter = true 

    }
    else{ 
    word += ch;
    inword = true;
    }

    if( i+1 == len && inword)
    if(!isStopword(word)){ 
    list.add(normalizeWord(word),Wordtype);
    }

    return list
    }

    //------------------------------------------------------------------------------------
    //function: AddFieldName
    //Description: this function adds unique filenames to the list of database fields that
    // we want to search.
    //------------------------------------------------------------------------------------
    function AddFieldName(fieldname)
    {
    if(fieldname.length > 0 ){
    for(var i=0;i<this.count;i++)
    if(this.fields[i] == fieldname)
    return; 
    this.fields[this.count++] = fieldname; 
    }
    }

    //------------------------------------------------------------------------------------
    //function: MakeSQLString
    //Description: this function makes the SQL statement based on the list of
    // keyword being parsed by the string parser.
    //------------------------------------------------------------------------------------
    //Note: this is an internal function use by the SearchSQL class
    //------------------------------------------------------------------------------------
    function MakeSQLString(vbstr)

    { var strSQL = ""; 
    var fieldname = ""; 
    var szlogic = "";
    var szNot;
    var fDonefirst = false;
    var ifieldstosearch=0;

    //okay the string getting passed from
    //VbScript is a variant, create a String object
    //with this parameter because we expect a String.
    var str = new String(vbstr);

    if (!(str.length > 0))
    Response.Write("must pass a search string");

    //Now Parse the keywords 
    var list = parseKeywords(str);

    //start of statement
    if(this.columns.length > 0)
    strSQL += "SELECT " + this.columns + " FROM " + this.tablename;
    else
    strSQL += "SELECT * FROM " + this.tablename;

    //account for everything accept filter because filters
    //should be the last thing that controls the SQL statement

    if (list.count > 0)
    strSQL += " WHERE ("

    //now loop through the keywords in the search string. 
    for(var i=0;i<this.count;i++){
    //inner loop through the fieldnames that we should
    //search in the database.
    for(var j=0;j<list.count;j++){
    ifieldstosearch++;
    //exclude the filter for now.
    if(!(list.word[j].type == "filerminus" || list.word[j].type == "fileradd" )){
    //get the phrase and setup the SQL logic
    //symbols.
    szPhrase = list.word[j].phrase 
    szNot = "" 

    //now the filter can be annoying if the number of fields
    //getting searched are more than 3,so change logic to OR
    //so that we can have a better chance of a hit.
    if(ifieldstosearch>2) 
    szlogic = " OR "
    else
    szlogic = " AND "

    //now check the frist character of the 
    //keyword is it an OR || NOT
    if(szPhrase.length > 0){
    if(isSymbol(szPhrase.charAt(0))){ 
    if(szPhrase.charAt(0) == '|')
    szlogic = " OR "; //OR logic 
    else if(szPhrase.charAt(0) == '!'){ 
    szNot = " NOT "; //NOT LOGIC

    //trim that character out. 
    szPhrase = szPhrase.substring(1,szPhrase.length-1); 

    }
    //now the first time that we add the clause to the SQL
    //we have to make sure that we don't add the LOGIC
    if(!fDonefirst){
    fieldname += " " + this.fields[i] + szNot + " LIKE \'%" + szPhrase + "%\'";
    fDonefirst =true;
    }
    else 
    fieldname += szlogic + this.fields[i] + szNot + " LIKE \'%" + szPhrase + "%'";

    }

    }
    //now done with the main search statement
    //lets start with the filters. 
    fieldname += ") "


    //filters.
    //filter are usually :
    //+{keyord} : to make sure that the result contains the keyword
    //-{keyowrd} : to make sure that the result excludes the keyword
    for(var i=0;i<this.count;i++){
    for(var j=0;j<list.count;j++){
    //only work with the filters.
    if(list.word[j].type == "filerminus" || list.word[j].type == "fileradd"){
    szPhrase = list.word[j].phrase 
    //trim the symbol
    szlogic = ""
    if(szPhrase.length > 0){
    //Now filters are easy usually they are a Logic AND
    //incase of - then they should be a NOT AND
    if(isSymbol(szPhrase.charAt(0))){
    if(szPhrase.charAt(0) == '-')
    szlogic = " NOT " 
    szPhrase = szPhrase.substring(1,szPhrase.length-1); 

    }
    //add to critera
    fieldname += " AND " + this.fields[i] + szlogic + " LIKE \'%" + szPhrase + "%'"; 
    }



    //Now build the SQL statement
    //and return it. 
    strSQL += fieldname; 
    return(strSQL);
    }

    //------------------------------------------------------------------------------------
    // function: SearchSQL
    //Description: this is the master class that we instantiate for our search include
    //------------------------------------------------------------------------------------
    //Note: this class is created via the helper function CreateSearchClass
    //------------------------------------------------------------------------------------
    function SearchSQL()
    {
    this.tablename = "";
    this.innerjoin = false;
    this.fields = new Object();
    this.AddField = AddFieldName;
    this.count=0;
    this.execute = MakeSQLString;
    this.columns = "";
    }
    //------------------------------------------------------------------------------------
    // function: CreateSearchClass
    //Description: this function creates the SearcgSQL class.
    //---------------------------------------------------------------------------------
    function CreateSearchClass(){return new SearchSQL()}

    //helper functions
    function getTime()
    {
    var s="";
    var c=":";
    d = new Date();
    s += d.getHours() + c;
    s += d.getMinutes() + c;
    s += d.getSeconds() + c;
    s += d.getMilliseconds(); 
    return(s)
    }

    function isWhitespace(ch){
    if(ch == ' ' || ch == '\n' || ch == '\r' || ch == '\t' || ch == '\f' ||
    ch == '\v' || ch == '\b')
    return(true)
    else
    return(false); 
    }

    function isSymbol(ch)
    {
    if(ch == '-' || ch == '+' || ch == '&' || ch == '|' || ch == '!')
    return(true);
    else
    return(false);
    }

    function getSymbolType(ch)
    {
    var szType = ""; 
    if(ch == '-')
    szType = "filerminus";
    else if(ch == '+') 
    szType = "fileradd";
    else if(ch == '&') 
    szType = "logicand"; 
    else if(ch == '|') 
    szType = "logicor"; 
    else if(ch == '!') 
    szType = "logicnot"; 
    else 
    szType = "normal";

    return(szType);
    }

    function isDelimiter(ch)
    {
    if(ch == ',' || ch == '?' || ch == '.' || ch == '\\' || ch == '/')
    return(true);
    else
    return(false);
    }

    function isVowel(ch)
    {
    if(ch == 'a' || ch == 'e' || ch == 'i' || ch == 'o' || ch == 'u' || ch == 'y')
    return(true);
    else
    return(false);
    }
    </script>


    DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

    More Database Code Articles
    More By aspfree

     

    IBM® developerWorks developerWorks - FREE Tools!


    Build Forge Express demo: Enabling software delivery excellence for small and midsized businesses

    This demonstration gives you an overview of IBM® Rational® Build Forge Express Edition, a global offering that provides a framework to automate and execute software processes. Rational Build Forge provides a software assembly line that can support all of your tools, technologies, and platforms so you can achieve a repeatable, reliable, and traceable build and release process.
    FREE! Go There Now!


    NEW! Best practices for software analysis: An introduction to the IBM Rational Software Analyzer application

    This whitepaper presents the benefits of successfully introducing static analysis into your organization using IBM Rational Software Analyzer. Additionally, it identifies some common pitfalls that can hinder the effective use of static analysis tooling as well as presents 10 simple strategies designed to help you quickly realize the value of static analysis using Rational Software Analyzer.
    FREE! Go There Now!


    NEW! Discovering the value of WebSphere Process Server

    WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies.
    FREE! Go There Now!


    NEW! Evaluate IBM Rational Software Analyzer V7.0

    Download a free trial version of IBM Rational Software Analyzer Developer Edition V7.0 to identify bug defects earlier in the software development cycle. Rational Software Analyzer is an extensible software development solution that reduces the expense of bug-fixes by enabling static analysis code reviews and bug identification very early in the development cycle.
    FREE! Go There Now!


    NEW! Hello World: Learn how to install and use the Rational Asset Manager Eclipse client

    In this tutorial, you can learn how to install and configure the IBM Rational Asset Manager Eclipse client, explore the different views in the Asset Management perspective, learn various search techniques, work with existing assets, and submit a new asset.
    FREE! Go There Now!


    NEW! Improve your build process with IBM Rational Build Forge, Part 2: Automate builds for a real-world Tomcat project

    Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available.
    FREE! Go There Now!


    NEW! Info 2.0: Harnessing the power of Web 2.0 and Enterprise Mashups

    Listen to this webcast to get an overview of Info 2.0 and a technical demo of how to quickly build an enterprise mashup. IBM's Info 2.0 technology leverages emerging Web 2.0 technologies such as mashups, feeds, AJAX, and JSON in order to simplify assembly of information using feeds and services. Come learn about the technical elements of Info 2.0 including the Feed Generation framework, Mashup Engine, and mashup assembly components. Learn how to pull information from databases, departmental information, and the Web to create mashups critical to your company’s success. We will also discuss best practices to help you get started.
    FREE! Go There Now!


    NEW! Innovate don't duplicate! Asset reuse strategies for success

    Asset Reuse is a key strategy for companies looking to create innovative solutions to solve complex software development problems. Searching for, identifying, updating, using and deploying software assets can be a difficult challenge. Listen to this webcast, to learn about strategies and tools that you can leverage for a successful project, including Rational Asset Manager, Rational Software Architect and WebSphere Service Registry and Repository.
    FREE! Go There Now!


    NEW! Try the IBM SOA Sandbox for Connectivity

    Visit IBM developerWorks to try the IBM SOA Sandbox for connectivity. The SOA Sandbox for connectivity provides a trial environment with the tooling and components to help you explore how to effectively connect your infrastructure and integrate all of the people, processes and information in your company. Use the hosted sandbox to explore SOA techniques that streamline connecting existing IT assets together, as well as learn how to connect them to new business logic.
    FREE! Go There Now!


    NEW! Try the IBM SOA Sandbox for People

    Visit IBM developerWorks to try the IBM SOA Sandbox for people. The SOA Sandbox for people provides a trial environment with the necessary tooling and components required to enable consistent human and process interaction and collaboration, showing how you can improve user experience and business productivity.
    FREE! Go There Now!



    All FREE IBM® developerWorks Tools!

    DATABASE CODE ARTICLES

    - Deployment of the MobiLink Synchronization M...
    - MobiLink Synchronization Wizard in SQL Anywh...
    - Finding Matching Records in Data Access Pages
    - Using the AccessDataSource Control in VS 2005
    - A Closer Look at ADO.NET: The Command Object
    - A Closer Look at ADO.NET: The Connection Obj...
    - Using ADO to Communicate with the Database, ...
    - Code Snippets: Counting Records
    - Constraints In Microsoft SQL Server 2000
    - Multilingual entries into a DB and to be dis...
    - Getting A List of Tables From SQL Server
    - SQL Server Database Creator - .NET Version
    - ADO Recordset Paging
    - Two combos, one textbox example
    - Discussion & Listserv Module by Mike Eck...





    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway