Class: LegacySqlUtils

LegacySqlUtils()

new LegacySqlUtils()

Class containing utilities for SQL
Deprecated:
  • use SqlMaskingUtils
Source:

Methods

binDataLength(pField) → {String}

returns the function which determines the length of binary data, depending on db type
Parameters:
Name Type Description
pField String name of the checked field
Source:
Returns:
Type
String

concatConditions(pArray, pOperator) → {String}

builds a condition out of multiple conditions
Parameters:
Name Type Description
pArray Array req Array containing the conditions
pOperator String req Operator that concatenates the conditions (AND/OR)
Source:
Returns:
concatenated Condition
Type
String

currentDate() → {String}

returns the function for current date depending on database
Source:
Returns:
expression
Type
String

dayFromDate(pDate, pAlias) → {String}

returns the day of a date
Parameters:
Name Type Description
pDate Datetime req the date
pAlias String req database alias
Source:
Returns:
Type
String

getConcatSymbol(pAlias) → {String}

returns the concat symbol depending on database type
Parameters:
Name Type Description
pAlias String opt database alias
Source:
Returns:
Concat Symbol
Type
String

getPlacerholderCondition(pfield, pfind, pIgnoreCase, pPlaceHolder) → {String}

returns the current search string incl placeholders
Parameters:
Name Type Description
pfield String req the search field
pfind String req the search string
pIgnoreCase String opt (true/false)
pPlaceHolder String opt (Platzhalter config)
Source:
Returns:
Type
String

getSingleColumnType(pTableName, pColumnName, pAlias) → {String}

returns a type of column in the database
Parameters:
Name Type Description
pTableName String req name of a table (e.g. "EVENT") OR if pColumnName is not passed table.column (e.g. "EVENT.STATUS")
pColumnName String opt name of column (e.g. "STATUS") if in pTableName only tablename is passed
pAlias String opt Alias to the database where the type should be loaded; default is current alias
Source:
Returns:
type of column such as SQLTYPES.xyz
Type
String

getSqlFormattedDate(pColumn, pAlias, pWithTime, pFormat) → {String}

returns SQLSystax for a date.
Parameters:
Name Type Description
pColumn String req Column name
pAlias String opt Database alias
pWithTime Boolean opt if true, then add time hh:mm:ss
pFormat String opt type of the format, e.g. yyyy-MM-dd; possible placeholers are: -dd -MM -yyyy
Source:
Returns:
sqlstr, fully functional select for sql for different types of databases
Type
String

getSqlInStatement(pFieldname, pData, pQuoteSymbol) → {String}

Builds a SQL IN condition, while accounting for the 1000 elements maximum Single conditions are concatenated with OR, which can be devastating for performance!
Parameters:
Name Type Description
pFieldname String req name of the field with table alias z.B ORGREL.RELATIONID
pData Array.<String> | Array.<Array.<String>> req Data as ID Array
pQuoteSymbol String opt symbol for quoting values, Strings i.e.: ' default is no symbol
Source:
Returns:
SQL condition: where VALS in (1,2,3)
Type
String

getSQLOperator(pVal) → {string}

returns a SQL operator depending on an integer value, i.e. $local.operator
Parameters:
Name Type Description
pVal int
Source:
Returns:
Type
string

isDuplicat(pTable, pColumns, pTypes, pValues, pExcludeFields, pAlias) → {Integer}

Checks if a new entry already exists
Parameters:
Name Type Description
pTable String req Databasetable(z.B. "comm")
pColumns Array req colums, like sqlInsert
pTypes Array req die datatypes, like sqlInsert
pValues Array req values, like sqlInsert
pExcludeFields Array opt columns, that should not be checked
pAlias String opt Database alias
Source:
Returns:
Type
Integer

makeCondition(pValue, pCondition, pWhere, pSQLType, pPreparedValues) → {String}

Setzt eine Condition zusammen und liefert sie zurück builds a conditions and returns it
Parameters:
Name Type Description
pValue Object req Filtervalue
pCondition String req variable in which the condition should be written
pWhere String req additional condition
pSQLType Integer opt SQLTYPES type of pValue
pPreparedValues Array opt Value for the condition, if it's a prepared statement
Source:
Returns:
Type
String

monthFromDate(pDate, pAlias) → {String}

returns the month of a date
Parameters:
Name Type Description
pDate Datetime req the date
pAlias String req database alias
Source:
Returns:
Type
String

sqlPageData(pSql, pBlockSize, pCallback, pDbAlias, pTimeout, pStartOffset) → {bool}

calls a given function for N blocks of sql-data as long as records are available or the paging-process is manually canceled
Parameters:
Name Type Description
pSql Object | String req sql statement that shall be executed String: SQL-query in a simple text form Object: prepared-sql-query: [sqlStr, [[value1, type1], [valueN, typeN]]]
pBlockSize Number req Amount of records that shall be read per block. (you need to specify an ORDER BY in your SQL-query) "0" <=> all records
pCallback
pDbAlias String opt Database-Aliasname, where the SQL-Statement shall be executed; default is the current dbalias
pTimeout Number opt Timeout in milliseconds; When it's reached the SQL-Statement will abort; default is in PREFERENCES configured
pStartOffset Number opt Position where to begin with the data-reading-process; default is 0
Source:
Returns:
returns whether the function read all available data or not: false if the callback-function returned false, otherwise true
Type
bool
Example
var varValues = [];//you've got access to variables declared with 'var'
let letValues = [];//you've got access to variables declared with 'let'
var count = 0;//you cannot overwrite a variable of 'sqlPageData' by accident

var sql = "select ORGNAME from ORG";
var blockSize = 5 * 1000;

var allRows = +db.cell("select count(*) from ORG");

sqlPageData(sql, blockSize, function (pData, pRunNo){
    var j = pData.length;//pData is the current block with data
    logging.log(pRunNo.toString() + "#" + j);//pRunNo is the amount how often the func. has been already called
    //you can calculate the progress easily by: progress = (blockSize* (pRunNo-1) + pData.length) / (allRows - startOffset)
    //example in per cent:
    var startOffset = 0;//we did not pass any startOffset to sqlPageData - this is equivalent to zero
    var progress = (blockSize* (pRunNo-1) + pData.length) / (allRows - startOffset);
    logging.log("progess: " + eMath.roundDec(progress * 100, 2, eMath.ROUND_CEILING) + "%");

    for (var i = 0; i < j; i++)
    {
        varValues.push(pData[i][0]);
        letValues.push(pData[i][0]);
    }

    count += pRunNo * 100;
    logging.log("count:" + count);//you cannot overwrite a variable of 'sqlPageData' by accident
});

logging.show(letValues);//contains orgnames
logging.show(varValues);//contains orgnames

yearFromDate(pDate, pAlias) → {String}

returns the year of a date
Parameters:
Name Type Description
pDate Datetime req the date
pAlias String req database alias
Source:
Returns:
Type
String