PC Pro 2003 March/DPPCPRO0303.ISO/Components/Microsoft ASP/_SETUP.1/MSDBQuery.inc

Text File | 1998-11-20 | 21.3 KB | 609 lines

<!--#INCLUDE FILE="MSGlobal.inc"-->
<!--#INCLUDE FILE="Adojavas.inc"-->
<SCRIPT RUNAT=SERVER LANGUAGE=JSCRIPT>
////////////////////////////////////////////////////////////////////
//                                                                //
//   Fusion ASP Components                                        //
//   Custom JScript Object: MSDBQuery                                //
//                                                                //
////////////////////////////////////////////////////////////////////
/*
Object:       MSDBQuery

Version:      1.0  10/2/97

Written By:   Application Methods, Inc.
              6300 Southcenter Blvd.
              Seattle, WA 98188
              [phone]
              http://www.appmethods.com

Description:  The MSDBQuery object is responsible for assembling an SQL query from
   four parameters and creating a cursor that can be used by database aware
   objects that require a cursor.  The parameters are broken up so that the individual
   portions may be modified or built upon for the final SQL query that the cursor will
   be based upon.

Note:   Generation of a number of hidden fields is necessary to pass information
   on to other components.  All hidden fields created by this object are prefixed
   with 'amaspHidden_' or 'amaspComponent_'.  Devlopers should never create
   any form element with these prefixes or unexpected results will occur.

Properties:
   name - the name of the query component
   dataSource - The name of the MSDBConnection object the query object is bound to
   usePreviousQuery - Uses previous query if one found?
   select - The "SELECT" portion of a SQL query
   from - the "FROM" portion of a SQL query
   where - the "WHERE" portion of a SQL query
   orderBy - the "ORDER BY" portion of a SQL query

Methods:
   buildSQL         - Builds SQL, adding passed constraints to the where clause and concatenating the 4 parts
   buildWhere       - Builds the SQL "where" clause from user entered property and key fields passed in via the Request object
   closeCursor      - Performs checks for null and closes recordset object
   emitProperties   - Writes out all properties of the object to the page on which the object exists.
   getCurrentCursor - Returns either the current recordset if it exists or a new, initialized recordset (calling openCursor and initializeCursor)
   getQueryParams   - Resets the object's query parameters (select, from, where, order by)
                      to those stored in client object from a previous query if "Use Previous Query"
                      is set to true.  Otherwise, copies it's query parameters to the client object's
                      select, from, where and order by properties for potential use by some other DBQuery
                      object on another page
   getURLStr        - Return value of old URL string from a previous list page
   initializeCursor - Checks the isCursorInitialized property.  If False moves recordset to next record and sets property to true
   isCursorEnd      - Detects end of record set
   isCursorStart    - Detect beginning of record set
   isNavigating     - Determines whether or not we are currently in the midst of navigating a recordset
   moveNext         - Advances the cursor forward the number of records indicated.
   movePrevious     - Advances the cursor backwards the number of records indicated.
   openCursor       - Creates a new recordset
   setCursor        - Replaces the current cursor for both this.cursor and Session(this.name) with the recordset object passed into the method
   setURLStr        - Stores value of old URL string from a previous list page

USAGE:

 <SERVER>

 test = new DatabaseConnection("INFORMIX","","ol_tahoma","informix","informix","sysmaster",true);
 test.connect();

 // Pass in parameters for SQL statement:
 //    select * from myTable where customerID > 7 order by lastname
 theQuery = new MSDBQuery("MSDBQuery1", "MSDBConnection1", "*", "myTable", "customerID > 7", "lastname");

 theQuery.getCurrentCursor();
 // Initialize the cursor, which will advance it to the first row of data
 if (theQuery.initializeCursor())
    write("The cursor was was advanced to the first row.");
 else
    write("The cursor contained no rows and could not be advanced");

 write("Customer last name is: " + theQuery.cursor.lastname);

 </SERVER>



=====================================================================*/

//
// MSDBQuery Object Constructor
//
function MSDBQuery (name, dataSource, usePreviousQuery, select, from, where, orderBy) {

   // create a version of the request object that has none of the default properties
   var strippedRequest = new NewRequest();
   var newReq = new Object();
   newReq = strippedRequest.newRequest;

   // Properties
   this.name = (name != null) ? name : "";
   this.newReq = new Object();
   this.newReq = newReq;
   this.usePreviousQuery = (usePreviousQuery == "true") ? true : false;
   this.select = (select != null) ? select : "";
   this.from = (from != null) ? from : "";
   this.where = (where != null) ? where : "";
   this.orderBy = (orderBy != null) ? orderBy : "";
   this.dataSource = dataSource;

   this.listActive = false;
   this.URLString = "";
   this.oldURLString = "";        // Holds the old URLString from a previous list page
   this.matchType = "";

   // get the value of the variable
   // called match_type (sent from the AdHoc button)
   this.matchType = Request("amaspHidden_matchType") + "";

   this.cursor = null;      // Initialize cursor property to null
   this.cursorPosition = 0;
   this.sql = null;         // Set current sql property to null
   this.isCursorInitialized = false;
   this.matchStart = "";
   this.matchAll = "";
   this.matchParm = "";

   this.sessionQueryName = "DBQuery";    // will not work with multiplie queries on a page

   // Methods
   this.buildSQL = quBuildSQL;
   this.buildWhere = quBuildWhere;
   this.getCurrentCursor = quGetCurrentCursor;
   this.initializeCursor = quInitializeCursor;
   this.openCursor = quOpenCursor;
   this.getQueryParams = quGetQueryParams;
   this.closeCursor = quCloseCursor;
   this.emitProperties = quEmitProperties;
   this.moveNext = quMoveNext;
   this.movePrevious = quMovePrevious;
   this.isNavigating = quIsNavigating;
   this.setCursor = quSetCursor;
   this.isCursorEnd = quIsCursorEnd;
   this.isCursorStart = quIsCursorStart;
   this.getURLStr = quGetURLStr;
   this.setURLStr = quSetURLStr;

   if (this.isNavigating()) {
           // current cursor is located in Session(this.name) & we need to move it per request params
            i = 0;
            this.cursorPosition = parseInt(Request.QueryString("amaspHidden_NavPosition"), 10);
            count = parseInt(Request.QueryString("amaspHidden_NavCount"), 10);

            // Don't navigate if the current cursor position is the same as the last cursor position
            if (this.cursorPosition == Session(this.sessionQueryName + "CursorPosition"))
                count = 0;
            else
                Session(this.sessionQueryName + "CursorPosition") = this.cursorPosition;

            if (isNaN(count))
                count=0;
            direction = Request.QueryString("amaspHidden_NavDir") + "";
            if (direction == void(0)+"")
                direction = "";
            else
                direction = direction.toUpperCase();

            while ((i < count) && !Session(this.sessionQueryName).EOF && !Session(this.sessionQueryName).BOF) {
                if (direction == "NEXT")
                   Session(this.sessionQueryName).MoveNext();    // Navigate to proper position
               else if (direction == "PREVIOUS")
                   Session(this.sessionQueryName).MovePrevious();
               i++;
           } // end while
   } // end if
   else
       Session(this.sessionQueryName + "CursorPosition") = 0;

    this.setURLStr();  // store old URL string temporarily for use by DBNav

}  // END Query constructor

// Method - quEmitProperties()
// Emits all the object's properties.  Used as a debuging tool to verify correct
// manipulation of the object.

function quEmitProperties ()
{
      debug("Writing MSDBQuery properties...");
      write("\n<BR><B>MSDBQuery Properties:</B>");
      write("\n<BR>usePreviousQuery = " + this.usePreviousQuery);
      write("\n<BR>select = " + this.select);
      write("\n<BR>from = " + this.from);
      write("\n<BR>where = " + this.where);
      write("\n<BR>orderBy = " + this.orderBy);
      write("<BR>");
 }   //End quEmitProperties;


//
// Build the SQL statement from each user-entered piece
//
function quBuildSQL () {

   this.getQueryParams();

   this.matchParm = "";   // reset all SQL building params to blank
   this.matchAll = "";
   this.matchStart = "";

   // Start building the basic SQL statement.
   var sql = "";
   sql = "select " + this.select + " ";
   sql += "from " + this.from + " ";

   // Construct the where clause
   var tempWhere = this.buildWhere();
   if (tempWhere.length > 0)
      tempWhere = "where " + tempWhere;

   sql += tempWhere;

   if (this.orderBy != "")
      sql += " order by " + this.orderBy;

   return sql;
}  // END quBuildSQL


//
// Builds the SQL 'where' clause from user entered expressions
// and key fields passed in from another page (if any)
//
function quBuildWhere() {

   // make sure the where clause is a string.
   var where = this.where + "";

   var URLString = "";
   var pre = "amaspHidden_";       // Designator required to hide fields from the DBQuery object
   var fieldPre = "amaspField_";   // Designator required for NewRequest object
   var myDataType  = "";
   var myDelim = "";
   var myDate;


      // Set the baseline match Parm
   if ((this.matchType != "contains") && (this.matchType != "starts with"))
      this.matchParm = " = ";

   // Set the match parm to look for any instance of the string in
   // the field
   if (this.matchType == "contains")
   {
      this.matchParm = " like ";
      this.matchAll = "%"
      this.matchStart = "%";
   }

   // Set the match parm to look for a string starting with
   // the requested value
   if (this.matchType == "starts with")
   {
      this.matchParm = " like ";
      this.matchStart = "%";
   }

   // If a where clause exists, and keyFieldCount > 0
   // Wrap the existing where clause in parens.
   if (where.length > 0)
      where = "(" + where + ")";

   // Loop through the stripped request object, filtering out
   // any other properties that must be filtered out of the where clause and the match
   // type param.
   for (var i in this.newReq)
   {
      // Ignore request object properties that contain certain special strings
      // beginning with a prefix defined by the variable "pre" above
      if ((i.indexOf(pre) < 0)) {
            // Look in new request object for hidden datatype indicator for this field
            myDataType = this.newReq[pre + i + "_dataType"];

            if (myDataType == null)  {
               myDataType = "string";
               debug("***Warning! Data type not found for field : " + i + ". Using default type string.");
            }

            // if where already exists then append the new where with an AND clause
            if (where.length > 0) where += " AND " ;
            if (myDataType == "string") {
                // Only String types may be matched in ways other than exact match.
                where +=  i + this.matchParm + wrapDelim(this.connectionType,
                         myDataType, this.matchAll + this.newReq[i] + this.matchStart);
            }
            else {
                where +=  i + " = " + wrapDelim(this.connectionType, myDataType, this.newReq[i]);
            }


            // Build URL string
            // Includes name of field, it's URL encoded value and it's data type.
            // This value get's passed via the client object to the DBNav component for
            // returning to the UP page properly.
            if (URLString.length > 0)
               URLString += "&" + fieldPre + i + "=" + escape(this.newReq[i]) + "&" + pre + i + "_dataType=" + escape(myDataType);
            else
               URLString = fieldPre + i + "=" + escape(this.newReq[i]) + "&" + pre + i + "_dataType=" + escape(myDataType);
            this.URLString = URLString + "&amaspHidden_matchType=" + escape(this.matchType);

         } // end "if ((i.indexOf(".x") < 0) && ... "
   } // end for loop

   return where;

}  // END quBuildWhere


//
// Returns the current cursor, if already established,
// otherwise it attempts to open the cursor and return it.
//
function quGetCurrentCursor() {

   this.connectionType = "";
   this.ODBCDatabaseType = "";
   // Session properties are set by DBConnection object in it's constructor method for use here
   // !!! NOTE: This code relies upon the DBConnection constructor being called before this method
   // Temp Removed for good reason but kept for safe keeping
   //if ((Session("amaspHidden_databaseType") != null) && (Session("amaspHidden_databaseType") != "null"))
   //   this.connectionType = Session("amaspHidden_databaseType");
   //else if ((Application("databaseType") != null) && (Application("databaseType") != "null"))
   //   this.connectionType = Application("databaseType");

   //if ((Session("amaspHidden_ODBCDatabaseType") != null) && (Session("amaspHidden_ODBCDatabaseType") != "null"))
   //   this.ODBCDatabaseType = Session("amaspHidden_ODBCDatabaseType");
   //else if ((Application("ODBCDatabaseType") != null) && (Application("ODBCDatabaseType") != "null"))
   //   this.ODBCDatabaseType = Application("ODBCDatabaseType");

   // Session properties are set by DBConnection object in it's constructor method for use here
   // !!! NOTE: This code relies upon the DBConnection constructor being called before this method
      this.connectionType   = Session("databaseType");
      this.ODBCDatabaseType = Session("ODBCDatabaseType");

     // If navigating, don't recreate a new query.  Instead, use the one stored
      // in the Session variable which has maintained its current position for the
      // purpose of continuous navigation across client requests.

    if (this.cursor == null)
          return this.openCursor();
      else
          return this.cursor;

}  // END quGetCurrentCursor


//
// Advances the cursor once to point to the first record in the set.
// Sets the "isCursorInitialized" property to true if successfully initialized.
//
function quInitializeCursor() {
   if (this.cursor != null)
      if (!this.isCursorInitialized)
          // Check for an empty recordset
         if (!this.cursor.EOF && !this.cursor.BOF)
            this.isCursorInitialized = true;

   return (this.isCursorInitialized);

}  // END quInitializeCursor


//
// Attempts to open the cursor on the database with an SQL
// statement generated by the "buildSQL" method
//
function quOpenCursor( sql ) {
//   debug("Note: Opening MSDBQuery cursor...");

   var ODBCDatabaseType = "";

   // Temp Removed for good reason but kept for safe keeping
   //if ((Session("amaspHidden_ODBCDatabaseType") != null) && (Session("amaspHidden_ODBCDatabaseType") != "null"))
   ODBCDatabaseType = Session("ODBCDatabaseType");

      if ((sql == null) || (sql == void(0))) {
         sql = this.buildSQL();
         this.sql = sql;
   }

    if (!this.isNavigating()) {
        this.cursor = Server.CreateObject("ADODB.Recordset");
        // adOpenDynamic (== 2) defined in Adojavas.inc - opens a dynamic cursor
        this.cursor.Open(sql, Session(this.dataSource), adOpenDynamic);
      Session(this.sessionQueryName) = void(0);  // eliminate the old variable
//        killit(this.sessionQueryName);
      Session(this.sessionQueryName) = this.cursor; // store recordset in Session variable
   } else
       this.cursor = Session(this.sessionQueryName);    // if navigating, copy Session cursor into this.cursor
   //}

   return this.cursor;

}  // END quOpenCursor



//
// Close the query's cursor object
// Note:  Care must be used with this method.  Closing a cursor that's already closed
// will crash the web server.
//
function quCloseCursor() {

//   debug("Note: Closing MSDBQuery cursor...");

   // Check to make sure we're not closing a cursor that isn't open or is already closed.
   if ((this.cursor != null) && (!this.isNavigating())) {
//      this.cursor.Close();
       this.cursor = null;
       this.isCursorInitialized = false;
   }

}  // END quCloseCursor



//
// Sets and returns query parameters to be used for this query
// if "Use Previous Query" has been set to true
//
function quGetQueryParams() {

   // Constant representing the "hidden" prefix for special values passed in via URL
   // or form variables to Request object
   var PRE = "amaspHidden_";

   // Initialization code
   // If set to use the previous query from a list, initialize with values set in the Request object
   // by the previous page's query object.
   // Also sets the "listActive" flag to true so other objects, such as the DBNav object,
   // can detect if the query from a previous list is being navigated.

   if (this.usePreviousQuery && (this.newReq[PRE + "listActive"] != null) &&
         (this.newReq[PRE + "listActive"] == "true") ) {
      this.listActive = true;
      this.select = Session("select");
      this.from = Session("from")
      this.where = Session("where")   // set it to where + "hidden" where (from passed in key fields)
      this.orderBy = Session("orderBy")
   } else {
      Session("select") = this.select;
      Session("from") = this.from;
      Session("where") = this.buildWhere();
      Session("orderBy") = this.orderBy;
   }

} // END quGetQueryParams


//
// Advances the cursor forward the number of records indicated
// Returns the number of records actually moved.  Returns 0 if at EOF.
//
function quMoveNext( count ) {

    var i = 0;

    // Error check the input value
    if (!IsNum(count))
        count = 0;

    while(this.cursor != null && !this.cursor.eof && (i < count)) {
        this.cursor.MoveNext();
        i++;
    }

    return i;    // return the number of records actually moved

} // end quMoveNext


//
// Moves the cursor backward the number of records indicated
//
function quMovePrevious( count ) {

    var i = 0;

    // Error check the input value
    if (!IsNum(count))
        count = 0;

    while(this.cursor != null && !this.cursor.BOF && (i < count)) {
        this.cursor.MovePrevious();
        i++;
    }

    return i;    // return the number of records actually moved

} // end quMovePrevious


//
// Determines whether or not we are currently in the midst of navigating a recordset.
//
function quIsNavigating() {

    // If Request("amaspHidden_NavDir") was not present on the most recent request,
    // then we must not be navigating, or are at the very start of navigating.  In
    // either such case, return "false" to indicate we are not in the midst of navigating
    // the recordset.
    if ((Request("amaspHidden_NavDir")+"" == (void(0)+"")) || Request("amaspHidden_NavDir") == null)
        return false;
    else
        return true;

} // end quIsNavigating


//
// Replaces the current cursor for both this.cursor and Session(this.name) with
// the recordset object passed into the method.
//
function quSetCursor ( theRecordset ) {

    // Make sure the recordset exists
    if( (theRecordset != null) && (theRecordset != void(0)) ) {
        this.cursor = theRecordset;
        Session(this.sessionQueryName) = theRecordset;
    }

} // end quSetCursor


//
// Detect end of record set
//
function quIsCursorEnd () {

    if (this.cursor.EOF)
        return true;
    else
        return false;

} // end quIsCursorEnd


//
// Detect end of record set
//
function quIsCursorStart () {

    if (this.cursor.BOF)
        return true;
    else
        return false;

} // end quIsCursorStart


function quSetURLStr () {

    var oldvalue = "";

  // If the URL query string hasn't been set, set it based
   // on URL query string currently stored in Session("URLStr")
   if ( !this.isNavigating()) {
       // If Session("URLStr") is blank or Request("amaspHidden_URLStr") is blank, initialize Session("URLStr")
       // Need to blank on Request("amaspHidden_URLStr") is blank to make sure that coming back from a detail
       // nav to a list nav doesn't keep around the detail key field params and throw off list navigation.
      if ((Session("URLStr") == "") || (Session("URLStr") == "null") || (Session("URLStr") == null) ||
              (Request.QueryString("amaspHidden_URLStr")+"" == "") )
         Session("URLStr") = "amaspHidden_URLStr=";
      // keep value around before we overwrite it below
      this.oldURLStr = Session("URLStr") + "";
   } else
       this.oldURLStr = Request.QueryString("amaspHidden_URLStr");

    this.buildWhere();   // has a side effect that sets property this.URLString
   // overwrite old URLStr value in Session object
   Session("URLStr") = this.URLString;


   return this.oldURLStr;

} // end quSetURLStr


//
// Return value of old URL string from a previous list page
//
function quGetURLStr() {

    return this.oldURLStr;

} // end quGetURLStr

</SCRIPT>

<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>
Sub killit (str)

    On Error Resume Next
    Session(str).Close
    set Session(str) = Nothing
    Response.write("killed")

End Sub
</SCRIPT>