documentation.adoc

var type = db.getColumnTypes("PERSON", ["FIRSTNAME"])[0]
var persons = db.table(["select FIRSTNAME, LASTNAME from PERSON where FIRSTNAME = ?", [[usereInput, type]]]);
var persons = newSelect("FIRSTNAME, LASTNAME")
                .from("PERSON")
                .where("PERSON.FIRSTNAME", userInput)
                .table();
var personSelect = new SqlBuilder()
                    .select("FIRSTNAME, LASTNAME")
                    .from("PERSON")
                    .where("PERSON.PERSONID", persId);
var personSelect = newSelect("FIRSTNAME, LASTNAME")
                    .from("PERSON")
                    .where("PERSON.PERSONID", persId);
var personCond = new SqlBuilder()
                    .where("PERSON.PERSONID", persId);
var personCond = newWhere("PERSON.PERSONID", persId);
new SqlBuilder().select("COL1")
                .from("TAB1")
                .where("TAB1.COL2", "myVal")
                .and("TAB1.COL3", "myVal");
new SqlBuilder().select("COL1")
                .from("TAB1")
                .and("TAB1.COL2", "myVal")
                .and("TAB1.COL3", "myVal");
newWhere("TAB1.COL2", "myVal")
    .and("TAB1.COL3", "myVal");
var cond = newWhere();

if (someCondition...)
{
    cond.and("TAB1.COL3", "myVal");
}

if (someOtherCondition...)
{
    cond.and("TAB1.COL2", "myVal2");
}
var value = null;

var cond = newWhere("TAB1.COL2", null)
                .and("TAB1.COL3", value);
// execution stops with error
var value = null;

var cond = newWhereIfSet("TAB1.COL2", null)
                    .and("TAB1.COL3", value);

// code is executed and cond.build will result in
// ["TAB1.COL3 = ?", [['myVal', SQLTYPES.VARCHAR]]];

// As you can see, TAB1.COL2 is ignored completely.
var costData = newSelect("CAMPAIGNCOSTID, CAMPAIGNSTEP_ID, CAMPAIGNSTEP.NAME, CATEGORY, NET")
                    .from("CAMPAIGNCOST")
                    .leftJoin("CAMPAIGNSTEP", newWhere("CAMPAIGNSTEP_ID = CAMPAIGNSTEPID"))
                    .where("CAMPAIGNCOST.CAMPAIGN_ID", campaignId)
                    .table();
var activitySelect = newSelect("SUBJECT, INFO, personLink.OBJECT_ROWID")
    .from("ACTIVITY")
    .join(
        newSelect("OBJECT_ROWID")
            .from("ACTIVITYLINK")
            .where("ACTIVITYLINK.OBJECT_TYPE", "Person"),
        null, "personLink", "outer", "apply"); //it would also be possible to omit pPrefix and then write "outer apply"
var count = newSelect("count(*)")
                .from("CLASSIFICATION")
                .whereIfSet("CLASSIFICATION.CLASSIFICATIONTYPE_ID", "$field.CLASSIFICATIONTYPEID")
                .cell(true, "0");
mySqlBuilder.pageSize(400);
var data;
while (mySqlBuilder.hasMoreRows())
{
    data = mySqlBuilder.nextTablePage();
    ...
}
mySqlBuilder.pageSize(400)
    .forEachPage(function (pData)
    {
        ...
    });
newWhereIfSet("AB_OBJECTRELATION.AB_OBJECTRELATIONID", objectRelationId)
        .updateData(true, "AB_OBJECTRELATION", ["INFO"], null, [vars.get("$field.INFO")]);
newWhere("SALESORDER.SALESORDERID", "$field.SALESORDERID")
    .updateFields({"ORDERSTATUS" : "1"});
new SqlBuilder()
    .insertData("AB_OBJECTRELATION", ["AB_OBJECTRELATIONID", "INFO"], null, [util.getNewUUID(), vars.get("$field.INFO")]);
new SqlBuilder().insertFields({
        "ACTIVITY_ID" : pActivityId,
        "OBJECT_ROWID" : pRowId,
        "OBJECT_TYPE" : pObjectType
    }, "ACTIVITYLINK", "ACTIVITYLINKID");
var name = 'Admin'
var cond = newWhere("PERSON.FIRSTNAME is not null") // a simple String
                .and(newWhere("PERSON.LASTNAME", name)) // another SqlBuilder (note: only the condition is used from it)
                .or(["FIRSTNAME is null and LASTNAME = ?", [["Smith",  SQLTYPES.VARCHAR]]]); // a prepared statement array

//cond.build() will result in  ["( (PERSON.FIRSTNAME = 'John'  and PERSON.LASTNAME = 'Admin' ) or (PERSON.FIRSTNAME = 'Peter' ) and PERSON.LASTNAME = 'Smith' ) ", [['John', SQLTYPES.VARCHAR], ['Admin', SQLTYPES.VARCHAR], ['Peter', SQLTYPES.VARCHAR], ['Smith', SQLTYPES.VARCHAR]]];
var cond = newWhere(newWhere("PERSON.FIRSTNAME", "John")
                        .or("PERSON.FIRSTNAME", "Paul"))
                .and("PERSON.FIRSTNAME", "Peter");

// (  ( PERSON.FIRSTNAME = 'John'  or PERSON.FIRSTNAME = 'Paul'  )  and PERSON.FIRSTNAME = 'Peter' ) ;
var cond = newWhere("PERSON.FIRSTNAME", "John")
                .or("PERSON.FIRSTNAME", "Paul")
                .and("PERSON.FIRSTNAME", "Peter");

// ( PERSON.FIRSTNAME = 'John'  or PERSON.FIRSTNAME = 'Paul'  and PERSON.FIRSTNAME = 'Peter' );
var cond = newWhere("PERSON.FIRSTNAME", "John")
                .and("PERSON.LASTNAME", "Admin")
                .or("PERSON.FIRSTNAME", "Peter")
                .and("PERSON.LASTNAME", "Smith");

//cond.build() will result in  ["( (PERSON.FIRSTNAME = 'John'  and PERSON.LASTNAME = 'Admin' ) or (PERSON.FIRSTNAME = 'Peter' ) and PERSON.LASTNAME = 'Smith' ) ", [['John', SQLTYPES.VARCHAR], ['Admin', SQLTYPES.VARCHAR], ['Peter', SQLTYPES.VARCHAR], ['Smith', SQLTYPES.VARCHAR]]];
newWhere("PERSON.FIRSTNAME", "$field.FIRSTNAME")
var aSqlBuilderContainingFullSelect = newSelect("LASTNAME")
                                        .from("PERSON")
                                        .where("PERSON.FIRSTNAME", "John");
var cond = newWhere("TABLE.FIELD", aSqlBuilderContainingFullSelect);

// cond.build() results in ["( TABLE.FIELD =  ( select LASTNAME from PERSON where PERSON.FIRSTNAME = ?  ) ) ", [['John', SQLTYPES.VARCHAR]]]
var aSqlBuilderContainingFullSelect = ["select LASTNAME from PERSON where PERSON.FIRSTNAME = ?", [['John', SQLTYPES.VARCHAR]]];
var cond = newWhere("TABLE.FIELD", aSqlBuilderContainingFullSelect);

// cond.build() results in ["( TABLE.FIELD =  ( select LASTNAME from PERSON where PERSON.FIRSTNAME = ?  ) ) ", [['John', SQLTYPES.VARCHAR]]]
var cond = newWhere("PERSON.FIRSTNAME", ["Peter", "Paul", "Mary"], SqlBuilder.IN()) // SqlBuilder.IN() == "in ?"
// Note that also SqlBuilder.NOT_IN() is possible here
// This is an example condition process containing some possible ways to use the SqlBuilder.
// newWhereIfSet() is used to create a new condition.
// The 'IfSet' ignores the condition "CONTACT.ORGANISATION_ID" = "$param.OrgId_param" if "$param.OrgId_param" is null
var cond = newWhereIfSet("CONTACT.ORGANISATION_ID", "$param.OrgId_param")
                .andIfSet("CONTACT.CONTACTID", "$param.ContactId_param"); // if "$param.ContactId_param" is null, this line is ignored
// --> If both parameters are null (not filled), then the SqlBuilder condition will not contain any condition!

// Now we add a parameter containing a json Array
// we have to parse it
var excludedContacts = JSON.parse(vars.getString("$param.ExcludedContactIds_param"));

// and add it to the condition using a "not in" operator
cond.andIfSet("CONTACT.CONTACTID", excludedContacts, SqlBuilder.NOT_IN());

// if excludedContacts is an empty array (or null), no condition is added as we used "andIfSet()"
// if you need a different behaviour you may wrap it by an if

// same for onlyShowContactIds, but by using an "in" operator.
// onlyShowContactIds contains an array.
var onlyShowContactIds = JSON.parse(vars.get("$param.OnlyShowContactIds_param"));
cond.andIfSet("CONTACT.CONTACTID", onlyShowContactIds, SqlBuilder.IN());

// The conditionProcess doesn't support Prepared Statements. Thus, .build() is currently not possible.
// using .toString() resolves all conditions and builds the finished select as string.
result.string(cond.toString());
new SqlBuilder(...)
          .where(...)
newSelect(...)
    .from(...)
    (.join(...))
    .where(...)
    ...
var myData = newSelect(...)
                .from(...)
                .join(...)
                .where(...)
                .table()