Combining SQL and Params

Classic Servoy
21

Can you post the code you use to fill the global and call the method? Than we can try if we get the error as well.

22

Just to be clear (not the most efficient way to help out sometimes!), the function is meant to be used with this call:

var stringSQL = globals.sqlParse(databaseManager.getSQL(foundset), databaseManager.getSQLParameters(foundset))
23

I have the following code in a global

//Combines query and argument array into 1 string, mostly useful for testing
   var _query = arguments[0];
   var _args = arguments[1];

   if (_args.length != utils.stringPatternCount(_query, "?")) { //number of params and number of ?'s doesn't match
      return "-ERROR- args: " + _args.length + "; query: " + utils.stringPatternCount(_query, "?") + ";";
   }

   var _val;

   //Loop through array and replace question marks by values
   for (var i=0; i < _args.length; i++) {
var a = _args[i]
      switch(typeof _args[i])    {
         case "string":
            _val = "'" + _args[i] + "'";
            break;
         case "object": //date
            _val = "'" + utils.dateFormat(_args[i], "yyyy-MM-dd HH:mm:ss") + "'";
            break;
         default: //number, integer
            _val = _args[i];
      }
      _query = _query.replace(/\?{1}/, _val);
   }

   //Format the query a little to please the eye
   _query = _query.replace(/[\t\n]/g, "").replace(/(WHERE|AND|OR|ORDER|GROUP)/gi, "\n$1");

   return _query;

I call it like this;

forms.quotes_list.controller.find()
var quoteSearch = plugins.DateUtils.getSearchStringDateBetween(globals.searchStartdateQ,globals.searchEnddateQ)
quote_date = quoteSearch;
controller.search(true,false)

if(globals.gHideSubQuotes == 1){
controller.find()
sub_quote = 'null'
controller.search(false,true)

}

//now get the sql and param for foundset
var vSql = databaseManager.getSQL(foundset)
var vParam = databaseManager.getSQLParameters(foundset)

//get SQL
var vSQL = globals.gSqlParse(vSql,vParam);
24

I’m stumped! Looks perfect. I have it working here in 4.1.2.