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