+1 vote

Finally, the serverjs API exposes a native method to convert an Efficy dataset to JSON. Below an example on how it can be used.

The query is defined in a macro and passed to a serverscript by the queryHandle

/serverjs/queries/tojson.js:

/**
 * Converts a HTML tag defined <%RunQuery()%> into a JSON string
 * <%RunScript(queryToJson, queryHandle="%%GetQueryHandle(query='$DETAIL$')", metaData="false", fields="status")%>
 */
function queryToJson() {
    var queryHandle = strToIntDef(Arguments.values("queryHandle"), 0),
        metaData = (Arguments.values("metaData").toUpperCase().charAt(0) == "F" ? false : true),
        fields = Arguments.values("fields"),
        contexts  = Arguments.values("contexts"),
        maxRecords = strToIntDef(Arguments.values("maxRecords"), 0),
        offSet = strToIntDef(Arguments.values("offSet"), 0),
        ds = Efficy.getQueryDataSet(queryHandle);

    return dataSetToJson(ds, metaData, fields, contexts, maxRecords, offSet);   
}

macros/MacroQueriesCustom.txt:

JSONQuery..myOppos {[<%LoadServerJs("queries/tojson")%><%RunQuery(id="$DETAIL$", SQL="
    select OPPORTUNITIES.K_OPPORTUNITY as k_Opportunity,
    OPPORTUNITIES.NAME as opportunity,
    OPPORTUNITIES.BUDGET as budget,
    (OPPORTUNITIES.budget*OPPORTUNITIES.success/100) as adjBudget,
    OPPORTUNITIES.SUCCESS as success,
    OPPORTUNITIES.D_TARGET as 'target date',
    case :param1 when 'NL' then os.STATUS_NL when 'FR' then os.STATUS_FR else os.STATUS end as status
    from <#TABLE NAME=R_OPPORTUNITIES> OPPORTUNITIES
    inner join <#TABLE NAME=LK_OPPO_STATUS> os on OPPORTUNITIES.STATUS = os.K_OPPO_STATUS
    where OPPORTUNITIES.MANAGER = :param2
    and <#SECURITYCLAUSE TABLENAME=OPPORTUNITIES>
    ORDER BY OPPORTUNITIES.D_TARGET"
    PARAM1="%%GetLanguage()",
    PARAM2="%%GetUSerKey()"
)%><%RunScript(queryToJson, queryHandle="%%GetQueryHandle(query='$DETAIL$')", metaData="false", 
    fields="k_Opportunity,opportunity,budget,adjBudget,success,target date,status", contexts="JS")%>
]}

GET request:

dialog?entity=none&_macrofile=MacroQueries.txt&_macro=JSONQuery&detail=myOppos

Response:

[ {"k_Opportunity": 10839, "opportunity": "First oppo for Holthuis Pamela", "budget": 1488, "adjBudget": 148.8, "success": 10, "target date": 42804, "status": "1. Eerste contact"}, {"k_Opportunity": 10848, "opportunity": "First oppo for Smeekens Gordon", "budget": 7929,"adjBudget": 1585.8, "success": 20, "target date": 42804, "status": "2. Demonstratie"},
...
asked in WorkFlow / Serverscript by (7.4k points)

1 Answer

0 votes
function dsToJson(Dataset){

    var result = new Array();

    var formatAttribute = function(_attr){

        _attr = _attr.replace(/\W/, ""); //-----(tout ce qui n'est pas [a-zA-Z0-9_])
        return _attr.toLowerCase();
    };

    var formatValue = 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;
    };

    if (!Dataset || Dataset.IsEmpty) return result;
    Dataset.First();

    while (!Dataset.EoF){
        var obj = {};

        for (var i=0; i<Dataset.Fields.count; i++) {
            var attrName = formatAttribute(Dataset.Fields(i).FieldName);
            obj[attrName] = formatValue(Dataset, Dataset.Fields(i));
        }
        result.push(obj);

        Dataset.Next();
    }
    return result;
}

flies away***

joke appart : this is pretty dope.

How do you get the list of field names that a query get if this query doesn't return any record? I would love to know that. I am pretty sure you can since the queries in Efficy can do it even with a native sql query that has a select * statement in it...

answered by (989 points)
I recognize the content of the formatValue function, it was part of the query Executer lib I made years ago ;-)

The good thing is that we don't need these custom functions anymore.

Even if a query doesn't return any record, it will still return a dataset object containing the meta data of each column. It's the same in SQL Server Management studio or Oracle SQL Developer, you still see the column headers. But maybe I don't understand your question?
The point is I never realized before that the metadata were not linked to the dataset Records so that I could get it with no record.... thanks.

I indeed used one of your feedbacks to improve my code^^.

I ll study the toJson code but my first impression is that it takes too much parameters for what I need to usually do^^
Half of the parameters are optional, meaning that you don't have to provide a value
1,249 questions
1,521 answers
1,859 comments
328 users