Key words

1. searching databases
2. dynamic where clause
3. javascript
4. form fields
5. form processing

summary

It is common to have search form when you are retrieving rows from a database. If the number of fields you are searching on are more than a few, it is cumbersome to check every field and correspondingly alter the where clause. These javascript routines allow you construct these where clauses on the fly. In this article I am going to show you the source code for these routines and how to use them in your form processing.

source code

//************************************************
// Given a set of form fields return a dynamic where clause
//
// inputs: 	
//    form: js form object reference
//    fieldNames: comma separated field names on that form
//    columnNames: comma separated column names in your where clause
//    colTypes: whether a field is an "int" or a "string"
//    nullColumnValue: What input string value should be considered a null
//
//  Notes:
//     nullColumnValue determines if a given field on the form has any valid input value. 
//     if the value of a field matches this string then the field is not included in the where clause
//     Example of a nullColumnValue is "none". This is typically used in list boxes
//
//      When you use list boxes, the value attribute is used to get the value and not the option
//************************************************
function getFieldValuesAsAWhereClause(form,fieldNames,columnNames, colTypes, nullColumnValue)
{
   if (fieldNames == "")
   {
      return "";
   }
   fieldNameArray = fieldNames.split(",");
   columnNameArray = columnNames.split(",");
   colTypesArray = colTypes.split(",");

   if (fieldNameArray.length != columnNameArray.length)
   {
	alert("Error: Number of fieldNames and columnNames do not match");
	return;
   }
   whereClause = "";
   for(i=0;I lt fieldNameArray.length;i++)
   {
      fieldName = fieldNameArray[i];
	columnName = columnNameArray[i];
	colType = colTypesArray[i];

//	alert("Going through:" + fieldName + ":" + columnName + ":" + colType);

      fieldObj = eval("form." + fieldName );
      fieldValue = fieldObj.value;
	fieldType = fieldObj.type;


	if (fieldType != "button" && fieldType != "hidden")
	{
		curWhereClauseField = getWhereClauseForField(columnName,fieldValue,colType,nullColumnValue);
		if (curWhereClauseField == "")
			continue;
		if (whereClause == "")
		{
			whereClause = curWhereClauseField;
		}
		else
		{
			whereClause += " and " + curWhereClauseField;
		}
	}
   }
	return whereClause;
}

function getWhereClauseForField(columnName, colValue, colType,nullColumnValue)
{
//	alert("wcff");
	if (colValue == "")
	{
		return "";
	}
	if (colValue == nullColumnValue)
	{
		return "";
	}
	else
	{
//		alert("ColValue:" + colValue);
		if (colValue.indexOf('*') != -1)
		{
			// there is a wild card in it
			return columnName + " like '" + colValue + "'";
		}
		else if (colValue.indexOf('%') != -1)
		{
			return columnName + " like '" + colValue + "'";
		}
		if (colType == "string")
		{
			return columnName + " = '" + colValue + "'";
		}
		else
		{
			return columnName + " = " + colValue;
		}
	}
}

How to use

Example code

function search()
{

   // 1
   //Remember some arguments that the server passes
   //Uses aspire tags
   customerName = "{{customerName}}";
   costCenterCode = "{{costCenterCode}}";
   laneSetId = "{{lanesetId}}";

   // 2
   //save fields for managing view state
   fieldValues=getFieldValues(document.mainform,
                  "TripInstanceIdTextField,StatusTypeListBox,awcTextArea,RouteNameListBox");

   // 3
   //Get the whereclause for the fiedls
   additionalWhereClause = getFieldValuesAsAWhereClause(document.mainform
                  ,"TripInstanceIdTextField,StatusTypeListBox,RouteNameListBox"
                  ,"ti.trip_instance_id,ti.current_status,ti.route_id_number"
                  ,"int,int,string"
                  ,"None");
   // 4
   //Read an additional where clause text box
   //the user may enter. This is optional
   nawc = document.mainform.awcTextArea.value;

   // 5
   //construct a final whereclause	
   if (additionalWhereClause != "")
        awc = " and " + additionalWhereClause;
   else
       awc="";			
	
   if (nawc != "")
       awc = awc + " and " + nawc;
	
   // 6
   url = "/aspire/servlet/DisplayServlet?url=tripSummaryURL"
             + "&customerName=" + escape(customerName)
             + "&costCenterCode=" + costCenterCode
             + "&laneSetId=" + laneSetId
             + "&whereclause=" + escape(awc)
             + "&fieldValues=" + escape(fieldValues)
	
    // 7	 
    document.location = url;	
}

Commentary on the code

Let's turn our attention to item 3 in the above code

   // 3
   //Get the whereclause for the fiedls
   additionalWhereClause = getFieldValuesAsAWhereClause(document.mainform
                  ,"TripInstanceIdTextField,StatusTypeListBox,RouteNameListBox"
                  ,"ti.trip_instance_id,ti.current_status,ti.route_id_number"
                  ,"int,int,string"
                  ,"None");

The field names here are "TripInstanceIdTextField,StatusTypeListBox,RouteNameListBox". Each field will correspond to a column name in the where clause of the select statement. These names depend on how you construct the select statement in the properties file. The "int,int,string" is indicating that these fields are of types int, int and string respectively. This information is used to put the values inside of quotes are not. In addition if the string value contains a '%', "like" is used to compare as opposed to "=". The value of "None" indicates that in the list box if an item of value "None" is chosen then don't include that list box in the where clause. The above will produce a where clause that look like

and ti.trip_instance_id = 13
and ti_current_status=5
and route_id_number = 'routenumber1'

Build information

The current release of Aspire is at build 18.5. The javascript utilities identified here are only going to be available with the next complete release. Meanwhile if you would like to use these functions, just copy the source code for the functions and put it in an appropriate js file. Then include that js file in your html page. In the source code I have replaced the less than sign with "lt". So put the less than sign back when you copy the code. Just search for "lt" to see where this substitituion occurred.