+8 votes

Together with the support for server-side JS modules, the Efficy API is extended with quite some interesting new methods, see Efficy ServerJS script reference

1° sqlQueryValue: a single line is enough to retrieve the first column of the first row of a dataset. If zero records are fetched, a default value (third argument) is returned. It replaces the custom library method QueryExecuter.FirstColumn

var k_Company = Efficy.sqlQueryValue("select K_COMPANY from COMPANIES where F_EXTKEY = :p1", extkey, 0);    

2° sqlQueryDataset: a single line to retrieve a dataset by an SQL query, optionally pass the storeid. If the dataset is not empty, the dataset is already set to the first record.

var ds = Efficy.sqlQueryDataset("select MAIN, D_START, D_END, JOBTITLE from CONT_COMP where K_CONTACT=:p1 and K_COMPANY=:p2", [k_Contact, k_Company].join("\n"));

There are many more, but these are in my opinion the most used in projects. So start using them ASAP.

Edit1: To illustrate the returned datatypes, I made this code example using sqlQueryValue on the most common datatypes used by Efficy.

function showTypeof() {
    // DB = SQL Server 2017
    var obj = {
        "k_Company": Efficy.sqlQueryValue("select top 1 K_COMPANY from COMPANIES", "", 0),
        "k_User": Efficy.sqlQueryValue("select top 1 K_USER from COMPANIES", "", 0),
        "name": Efficy.sqlQueryValue("select top 1 NAME from COMPANIES", "", 0),
        "d_create": Efficy.sqlQueryValue("select top 1 D_CREATE from COMPANIES", "", 0),
        "opened": Efficy.sqlQueryValue("select top 1 OPENED from COMPANIES", "", 0),
        "memo": Efficy.sqlQueryValue("select top 1 MEMO from COMPANIES", "", 0)
    }

    for (var name in obj) {
        Efficy.log("typeof " + name + " is '" + typeof obj[name] + "' for JScript and value converted to string is '" + obj[name] + "'. The actual Delphi type is " + varTypeAsText(obj[name]));

        if (typeof obj[name] == "date") {
            obj[name + "_toString"] = dateTimeToStr(obj[name]);
        }
    }
}

/**
* Returns the type name as text for the typical Efficy datatypes
* http://docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/HelpUpdate2/EN/html/delphivclwin32/Variants_VarType.html
*/
function varTypeAsText(v) {
    switch (VarType(v)) {
        case 3: return "VarSmallInt"; break;    // Represents a Variant that holds Smallint data.
        case 5: return "VarDouble"; break;      // Represents a Variant that holds a floating point value.
        case 7: return "VarDate"; break;        // Represents a Variant that holds a TDateTime value.
        case 8: return "VarOleStr"; break;      // Represents a Variant that has been assigned a COM interface type string value.
    }
}

This returns the following log lines in SchedulerAdmin.exe

typeof k_Company is 'number' for JScript and value converted to string is '2'. The actual Delphi type is VarDouble
typeof k_User is 'number' for JScript and value converted to string is '2'. The actual Delphi type is VarSmallInt
typeof name is 'string' for JScript and value converted to string is 'Coca Cola'. The actual Delphi type is VarOleStr
typeof d_create is 'date' for JScript and value converted to string is 'Fri May 20 09:57:02 UTC+0200 2016'. The actual Delphi type is VarDate
typeof opened is 'string' for JScript and value converted to string is '1'. The actual Delphi type is VarOleStr
typeof memo is 'string' for JScript and value converted to string is ''. The actual Delphi type is VarOleStr
typeof d_create_toString is 'string' for JScript and value converted to string is '20/05/2016 9:57:02'. The actual Delphi type is VarOleStr
asked in WorkFlow / Serverscript by (7.4k points)
edited by

1 Answer

0 votes

Hello Kristof,

Thanks a lot for sharing those new very useful function and a big thanks to the R&D for the implementation.

I do have a question : how can I specify the type of the value returned by Efficy.sqlQueryValue ? I would like to be sure that, for exemple, i get an integer and not a string.

Documentation says that the returned type is a "variant" but I must confess my lack of knowledge for this type of value.

Thanks a lot,

Loïc

answered by (538 points)
I edited my post to include an answer to your questions about the returned variant types. The hybrid situation of Efficy scripting that mixes JScript and Delphi together makes it complex, especially with dates.
1,249 questions
1,521 answers
1,859 comments
328 users