+1 vote

Dear developper community

Just before Kristof tells me why it's not good^^ let s take a minute to celebrate my new Achievement : I finally found a way to convert Dataset to Array of Objects in javascript. please note that this is the result of delphi Recordset objects researches on the web plus lot of tests.

I give you the result of my code as a tribute for the great Efficy developpers community^^.

this is how it works : in my example I try to get all the STATUS_FR and K_OPPO_STATUS from the LK_OPPO_STATUS table ENALBED and with K_OPPO_STATUS lesser than 5
do you remember doing something like this :

var
    result = new Array(),
    qh = 0,
    context = Database.OpenTemporaryContext(),
    sql = "select * from <#TABLE NAME=LK_OPPO_STATUS> where DISABLED = :param1 and K_OPPO_STATUS < :param2",
    params = [0, 5].join("\n"),
    ds = Database.ExecuteSystemSQLQuery(qh, context, sql, params, true, true, 1);

while (!ds.EoF){

    result.push({
        status: ds.FieldByName("STATUS").AsString,
        k_oppo_status: ds.FieldByName("K_OPPO_STATUS").AsFloat      
    });

    ds.Next();
}

return result; 

that gave you :

[  
   {  
      "status_fr":"01. Prise de contact",
      "k_oppo_status":1
   },
   {  
      "status_fr":"02. Qualification",
      "k_oppo_status":2
   },
   {  
      "status_fr":"03. Démonstration",
      "k_oppo_status":3
   },
   {  
      "status_fr":"04. Offre",
      "k_oppo_status":4
   }
]

this time my friends is over! now you can do :

return jsonSelectSQL("select * from <#TABLE NAME=LK_OPPO_STATUS> where DISABLED = :param1 and K_OPPO_STATUS < :param2", 0, 5);

Note that at this point YOU don't need to specify anything about the fields names or number of fields.

which will give you something like

[  
   {  
      "k_oppo_status":1,
      "k_sort":1,
      "k_label":0,
      "status":"01. Initial Contact",
      "disabled":"0",
      "status_fr":"01. Prise de contact",
      "status_nl":"01. Prise de contact",
      "status_es":"01. Prise de contact",
      "status_de":"01. Prise de contact",
      "status_ja":"",
      "status_pl":"",
      "status_tr":"",
      "status_ar":"",
      "f_pending":1
   },
   {  
      "k_oppo_status":2,
      "k_sort":2,
      "k_label":0,
      "status":"02. Qualification",
      "disabled":"0",
      "status_fr":"02. Qualification",
      "status_nl":"02. Accroche",
      "status_es":"02. Accroche",
      "status_de":"02. Accroche",
      "status_ja":"",
      "status_pl":"",
      "status_tr":"",
      "status_ar":"",
      "f_pending":1
   },
   {  
      "k_oppo_status":3,
      "k_sort":3,
      "k_label":0,
      "status":"03. Démonstration",
      "disabled":"0",
      "status_fr":"03. Démonstration",
      "status_nl":"03. Démonstration",
      "status_es":"03. Démonstration",
      "status_de":"03. Démonstration",
      "status_ja":"",
      "status_pl":"",
      "status_tr":"",
      "status_ar":"",
      "f_pending":1
   },
   {  
      "k_oppo_status":4,
      "k_sort":4,
      "k_label":0,
      "status":"04. Offre",
      "disabled":"0",
      "status_fr":"04. Offre",
      "status_nl":"04. Offre",
      "status_es":"04. Offre",
      "status_de":"04. Offre",
      "status_ja":"",
      "status_pl":"",
      "status_tr":"",
      "status_ar":"",
      "f_pending":1
   }
]

note that if you strictly want the same result as before you can do :

return jsonSelectSQL("select STATUS_FR, K_OPPO_STATUS from <#TABLE NAME=LK_OPPO_STATUS> where DISABLED = :param1 and K_OPPO_STATUS < :param2", 0, 5);

this is the code of jsonSelectSQL :

function jsonSelectSQL(){

    var 
        ds = selectSQL.apply(null, arguments),
        result = new Array(),
        fields = new Array();

    ds.First();
    if (!ds.EoF) {
        for (var i=0; i<ds.Fields.count; i++){
            fields.push(ds.Fields(i).FieldName);
        }
    }
    while (!ds.EoF){
        var obj = {};

        for (var i=0; i<fields.length; i++) {
            var temp = ds.FieldByName(fields[i]).Value;
            switch(typeof(temp)){
                case "string":
                    obj[fields[i].toLowerCase()] = temp;
                    break;
                case "number": 
                    obj[fields[i].toLowerCase()] = Number(temp);
                    break;
                default: 
                    obj[fields[i].toLowerCase()] = temp;
                    break;
            }
        }
        result.push(obj);

        ds.Next();
    }
    return result;
}   

please note that you will need this code as well :

//------(simple closure to don't have to remember which StoreId to use)
var nextStoreId = (function(){
    var storeId = 0;
    return function(){return storeId += 1;};
})();

//------(call this with sql string and parameters and you will get the standard dataset)
function selectSQL(){
    var 
        args = arrayFrom(arguments),
        qh = 0, 
        context = Database.OpenTemporaryContext(),
        sql = args[0],
        params = args.splice(0,1),
        ds = Database.ExecuteSystemSQLQuery(qh, context, sql, params.join("\n"), true, true, nextStoreId());

    return ds;
}

//-----(convert the classic arguments arraylike object as a regular javascript array)
function arrayFrom(_arg){

    var result = new Array();

    for (var i=0; i<_arg.length; i++){
        result.push(_arg[i]);
    }

    return result;
}   

//-----(home made splice prototype polyfill, feel free to use yours)
Array.prototype.splice = function(){
    var args = arguments;

    var result = new Array();
    var i = 0;

    if (args[0] + args[1] > this.length) throw new Error("custom Splice error, invalid parameters for this array");
    while (i < args[0]){
        result.push(this[i]);
        i++;
    }

    if (args[2])    for (var j=0; j<args[2].length; j++) result.push(args[2][j]);

    i = args[0] + args[1];

    while (i<this.length){
        result.push(this[i]);
        i++;
    }

    for (i=0; i<this.length; i++) this[i] = undefined;
    this.length = 0;
    for (i=0; i<result.length; i++) this.push(result[i]);

    return this;
}   

since a lot of code can be necessary I invite to use my previous achievement : be able to make some include files in serverscripts files : I have it updated so this in the header of your serverscript file will make the trick :

eval(function (_files){
    var root = "C:/inetpub/wwwroot/efficy/" + ((typeof(Request) !== "undefined")? Request.CustomBaseURL: "../customs/Test/");
    var result = "";
    for (var i=0; i<_files.length; i++) result += (StrLoadBinaryFile(root + _files[i])) + "\r\n";
    return result;
}([
    "serverscripts/NL_server_tools.js"
    ,"serverscripts/NL_server.js"
    ,"serverscripts/json2.js"
]));

in this example I chose to regroup all Efficy specific functions in NLserver.js
and all the pure javascripts tools as the splice polyfill in NL
server_tools.js
libjson is the copy of the standard libjson library used sometimes in some scripts. (JSON.parse and stringify)

Cheers. feel free to improve this code or ask for all the librairies if you need it
please share your code afterward.

asked in WorkFlow / Serverscript by (989 points)

1 Answer

+1 vote
Best answer

Hi Louis.

Thank you for sharing your developments with the community, this is highly appreciated.
In 2012 I made the 4th version of the QueryExecuter helper including the support of the GetArray that return an array of row objects. The difference is that the casting in based on the Delphi datatype, not on the Javascript typeof. This allows you to for instance cast dates to the Delphi float number, this typically works easier.

A usage example:

var query = new QueryExecuter();
var arr = query.GetArray("Select * from ACC_ACCOUNTS where K_USER=:p1", Database.CurrentUserId());

I hope you can be inspired by this code to even improve yours.

function QueryExecuter() {
          // KP: 01/06/2012
          // Version: 4.0 (support for GetArray())
          //*************************
          this.QueryHandle;
          this.QueryContext;
          this.StoreID;
          this.dsResult;
          this.UseSoap;
          QueryExecuter.prototype.GetArray = function (SQL, Params) {
                   try {                      
                             this.FillDsResult(SQL, Params, false);
                             var arr = new Array();
                             var ds = this.dsResult;
                             if (!ds || ds.IsEmpty) return arr;
                             ds.First;
                             while (!ds.Eof) {
                                      var obj = {}
                                      for (var i=0; i <= ds.Fields.count - 1; i++) {
                                                var ObjName = this.FormatObjName(ds.Fields(i).FieldName);
                                                obj[ObjName] = this.FieldValue(ds, ds.Fields(i));
                                      }
                                      arr.push(obj);
                                      ds.Next;
                             }
                             return arr;                        
                   } catch (ex) {
                             this.CatchEx(ex, SQL, Params);
                   }
          }
          QueryExecuter.prototype.ExecuteSQL = function (SQL, Params, Keep) {
                   try {
                             this.FillDsResult(SQL, Params, Keep);
                             if (!this.dsResult.IsEmpty) this.dsResult.First;
                             return this.dsResult
                   } catch (ex) {
                             this.CatchEx(ex, SQL, Params);
                   }
          }
          QueryExecuter.prototype.FieldByName = function (SQL, Params, FieldName) {
                   try {
                             this.FillDsResult(SQL, Params, false);
                             if (this.dsResult.IsEmpty) return "";
                             this.dsResult.First;
                             return this.dsResult.FieldByName(FieldName).AsString;
                   } catch (ex) {
                             this.CatchEx(ex, SQL, Params, FieldName);
                   } finally {
              if (this.UseSoap && this.dsResult) this.dsResult.Free;
            }
          }        
          QueryExecuter.prototype.FirstColumn = function (SQL, Params, DefaultValue) {
                   var FieldName;

                   try {
                             this.FillDsResult(SQL, Params, false);
                             if (!this.dsResult || this.dsResult.IsEmpty) return DefaultValue;
                             this.dsResult.First;
                             return this.FieldValue(this.dsResult, this.dsResult.Fields(0));
                   } catch (ex) {
                             this.CatchEx(ex, SQL, Params, FieldName);
                   } finally {
      if (this.UseSoap && this.dsResult) this.dsResult.Free;
    }
          }
          QueryExecuter.prototype.ExecSQL = function (SQL, Params) {
    if (this.UseSoap) {
      try { 
        Database.ExecuteSQLQuery(SQL, Params)
        Database.ExecuteBatch;
      } catch(ex) {
        if (ex.message.indexOf("RemoveProviderInformation: DataSet does not contain any fields!") != 0)        
        throw ex
      }       
    } else {    
      Database.ExecSQL(SQL, Params, false)        
    }
          }        

          //Helpers
          QueryExecuter.prototype.FieldValue = function(ds, Field) {
                   var FieldName = Field.FieldName;
                   var DataType = parseInt(Field.DataType, 10);
                   if (DataType == 3) // Int
                             return ds.FieldByName(FieldName).AsInteger;
                   else if (DataType == 6 || DataType == 37) // Float
                             return ds.FieldByName(FieldName).AsFloat;
                   else if (DataType == 11)// Datetime
                             return ds.FieldByName(FieldName).AsFloat;
                   else // Anything else
                             return ds.FieldByName(FieldName).AsString;
          }        
          QueryExecuter.prototype.FormatObjName = function (str) {
                   var CapitaliseAfterUnderScore = function(str) {
                             var res = "";
                             var strArr = str.split("_");
                             for (var i=0; i<strArr.length; i++) {
                                      var s = strArr[i]
                                      if (res) res += "_"
                                      res += s.substr(0,1).toUpperCase() + s.substr(1).toLowerCase()
                             }
                             return res;
                             str = str.substr(0,1).toUpperCase() + str.substr(1);
                             return str;     
                   }
                   var illegalChars = /\W/; // allow letters, numbers, and underscores                
                   str = str.replace(illegalChars,"");
                   str = CapitaliseAfterUnderScore(str);
                   return str;
          }
          QueryExecuter.prototype.FillDsResult = function(SQL, Params, Keep) {
                   if (this.UseSoap) {
                             var dsId = Database.ExecuteSQLQuery(SQL, Params);
                     Database.ExecuteBatch;           
                     this.dsResult = Database.CreateDataSet;   
      Database.GetObject(dsId, this.dsResult);    
                   } else {
                             if (Keep)
                                      this.QueryContext = Database.OpenConsultContext(ntQuer);
                             else
                                      this.QueryContext = Database.OpenTemporaryContext();
                                      this.dsResult = Database.ExecuteSystemSQLQuery(this.QueryHandle, this.QueryContext, SQL, Params, true, true, this.StoreID)
                   }
          }        
          QueryExecuter.prototype.CatchEx = function(ex, SQL, Params, FieldName) {
                   var msg = "QueryExecuter.CatchEx: " + ex.message;
                   if (SQL) msg += "SQL: " + SQL
                   if (!Params) Params = "";             
                   Params = "\'" + String(Params).replace(new RegExp( "\\n", "g" ), "\';\'") + "\'"
                   msg += ", Params: " + Params;
                   if (FieldName) msg += ", FieldName: " + FieldName;
    if (this.UseSoap && this.dsResult) this.dsResult.Free;
                   throw new Error(msg);
          }
}
answered by (7.4k points)
selected by
Hi Kristof,

Once again i am thanksfull and disappointed to discover that after I ve coded my own version.
we should totally make a shared developpers serverside framework to implement those tools
basically we just have to normalize architecture and comments to be able to generate a technical documentation (docxygen like or other)
1,249 questions
1,519 answers
1,859 comments
328 users