Get the data type of the field attached to an element

[ahmad]

Hi,

I have written a function which forms the search string by looping through all the elements in my find form and surround the field values by % so that the user can perform LIKE searches without entering the %

Code: Select all

  frmObjFind= globals.find_commonScripts('getFrmObjFind');

  frmObjFind.controller.find();
 
   var i;
   for (i=0; i < frmObjFind.elements.length; i++) {
      objName = frmObjFind.elements[i].getName();
      // the name of the element is set exactly same as the field name

      if (frmObjFind[objName] != "" && frmObjFind[objName] != null ) {
         frmObjFind[objName] = "%" +  frmObjFind[objName] + "%";
      }
   
   totRec = frmObjFind.controller.search();
   }


This works great for the text fields. But the problem comes when search is done on an Integer field

Question:
How to get the data provider attached to an element and how to get the data type of that data provider.

If I can get this I can change my function so that it won't add any % for the Integer fields.

Since I want to generalise all the finds in the database into one common function I don't like to do any hard coding like if (field = this & that etc..)

Expecing your kind reply

Thanks

[david]

This is my function for your situation. It is called from a loop to set up multiple find requests if necessary. In addition to setting up a "like" request by adding the percent symbol (%), it adds the hash symbol (#) and the not symbol (!) based on whether the user specifies this search as case insensitive and/or an omit:

Code: Select all

var return_value;

var theValue = arguments[0];
var theField = arguments[1];
var theCase = arguments[2];
var theOmit = arguments[3];

//check theValue for operators
var operator = theValue.replace(/\./g,'');
var operator = operator.replace(/\>/,'');
var operator = operator.replace(/\</,'');
var operator = operator.replace(/\=/,'');


//check to see if search string is a number
var aNumber = theValue.replace(/\.\.\./,'');
aNumber = aNumber.replace(/\>/,'');
aNumber = aNumber.replace(/\</,'');
aNumber = aNumber.replace(/\=/,'');
aNumber = utils.stringToNumber(aNumber);

if (aNumber != theValue && aNumber != operator)
{
   if (theValue == operator)
   {
      return_value = "%" + theValue + "%";
      if (!theCase)
      {
         return_value = "#" + return_value;
      }
      if (theOmit)
      {
         return_value = "!" + return_value;
      }

   }
   else
   {
      return_value = theValue;
      if (theOmit)
      {
         return_value = "!" + return_value;
      }
   }

}
else
{
   return_value = theValue;
   if (theOmit)
   {
      return_value = "!" + return_value;
   }
}

return return_value;


- David

[IT2Be]

If you want to check for a value you can also use my tools plugin where you have 3 functions: isNumber, isInteger, isTextOnly...

[ahmad]

Thanks very much David & ITBE,

I can either use the method by David or use ITBE's plugin to check whether the value is integer or text.

This will work until I don't search a number value in the text field

For eg: if the Item# = A2500 and I search for 2500, my search string will be now considered as a number though it's a text and it won't be surrounded with % and hence no results.

Anyway for the time being I pass an array of field names for Integer & date fields and hence no % added for these fields (hard coding)

It would be nice if Servoy can give us the data provider attached to any element on the form and also provide a function which returns the data type of a field

Cheers

[IT2Be]

t would be nice if Servoy can give us the data provider attached to any element on the form and also provide a function which returns the data type of a field

As for your first remark Ahmad: an element that can have a dataprovider attached can already return the dataprovider name by using elementname.getDataProviderID.

[jcompagner]

guys

Code: Select all

var jstable = databaseManager.getTable(controller.getServerName(), controller.getTableName());
var jscolumn = jstable.getColumn('message_key');
var type = jscolumn.getType();
// see http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Types.html
var number =  (type == java.sql.Types.INTEGER || type == java.sql.Types.DECIMAL || type == java.sql.Types.NUMERIC)
application.output(number)

[bcusick]

Thanks, Johan!

Here's some code that will put it all together:

Code: Select all

for ( var i = 0 ; i < elements.length ; i++ )
{
   var name = elements[i].getName()
   var text = false
   
   var jstable = databaseManager.getTable(controller.getServerName(), controller.getTableName());
   var jscolumn = jstable.getColumn(name);
   var type = jscolumn.getType();
   // see http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Types.html
   var number =  (type == java.sql.Types.INTEGER || type == java.sql.Types.DECIMAL || type == java.sql.Types.NUMERIC)
   var date =  (type == java.sql.Types.DATE || type == java.sql.Types.TIMESTAMP)
   if(!number && !date)
   {
      var text = true
   }

   application.output(name + ": " + text + ", " + number + ", " + date)
}

Hope this helps inspire!

NOTE: This will only work for elements with the NAME property filled in, and you need to name the element the SAME THING as the database column. You can do it other ways by having a mapping table, etc - but this is the most straight-forward way.

Bob