SQL classes: Query Schema Table
The following properties and methods apply to list variables that are defined from a schema, query, or table class.
Warning: SQL properties are not available in client methods.
| Object specific | |
| $allrowsfetched | Set to kTrue when all rows in the current result set have been fetched |
| $class | The class containing or associated with the item (may be empty) |
| $colcount | Returns the number of columns in the list |
| $colsinset | Returns the number of columns in the select table |
| $extraquerytext | The text to append to queries generated by $delete, $update and $select |
| $fromclause | If not empty, the table instance uses its value instead of the table list,in the FROM clause generated by $select |
| $isfixed | If true, the list has fixed length columns |
| $line | The current line in the list This changes when the user clicks on a list line, or when using a method such as $search(). |
| $linecount | The number of lines in the list You can change this property or use Set final line number to truncate the list. |
| $linemax | The maximum number of lines in the list This is set to 10,000,000 by default but you can change it to restrict the list size. |
| $rowsaffected | The number of rows affected by the last insert/delete/update operation |
| $rowsfetched | The number of rows fetched so far from the result set |
| $servertablenames | The names of the tables or views on the server, to which the table instance corresponds |
| $smartlist | If true, the list is a smart list Setting $smartlist to kTrue creates and initializes the history list. If it is already kTrue, then setting it again has no effect. Setting $smartlist to kFalse discards the history list completely. The current normal list remains unchanged, so the current contents of the normal list are preserved, but all history an filtering information is lost. If you define or redefine a list using any mechanism, or add columns to a list, its $smartlist property is set to kFalse automatically. |
| $sqlclassname | The name of the query or schema which the table uses |
| $statementobject | The statement object in use by the schema or table instance |
| $statementobjref | The statement object reference in use by the schema or table instance |
| $synchronous | If true,and $useworker is true,the worker object for the schema or table instance executes synchronously in the current thread rather than asynchronously in a separate thread. Defaults to false (meaning use another thread) |
| $useprimarykeys | If true, the table instance only uses schema columns marked as primary keys in where clauses |
| $useworker | If true,use a worker object corresponding to the DAM for $sessionobject or $sessionobjref,to execute requests for the schema or table instance.$useworker can only be assigned to true |
| Standard | $container $desc $ident $name $sessionobject $sessionobjref $sublen $subtype $type |
Warning: SQL methods, $filter, $refilter and $unfilter are not available in client methods.
| Object specific | |
| $add | $add(columnValue,columnValue,...) inserts a new list line and returns an item reference to it |
| $addcols | $addcols(cName,type,subtype,maxlen,...) adds 1 or more columns to a list or row.Specify each column with 4 parameters:cName,data type constant e.g.kCharacter (not kObject),subtype and maxlen |
| $assigncols | $assigncols(value1,value2,...) assign the column values |
| $assignrow | $assignrow(wRow[,bByName=kFalse]) assign column values from the row wRow by column number or name (pass bByName as kTrue to assign by name rather than number) |
| $clear | $clear() clears the data for character,number,integer,date or boolean variables, a list or row, or a column in a list or row; executing list.$clear() for a smart list sets $smartlist to kFalse, meaning that it is no longer a smart list. |
| $copydefinition | $copydefinition(listOrRow[,params...]) copies the definition of listOrRow to the calling list or row variable. If listOrRow contains a table instance the method passes parameters to $construct of the new instance |
| $createnames | $createnames() returns text suitable for using with a CREATE TABLE statement |
| $define | $define([var1,var2,...]) defines a list or row;the columns have the names and types of the parameters.Each parameter can also be a file class name (a string) from which all columns are used;append /S to the file class name to skip empty columns |
| $definefromsqlclass | $definefromsqlclass(sqlclass[,wRow,params...]) clears and defines the list or row using the class,and creates a table instance. Passes parameters to $construct.wRow is a row of schema column names to use (no names means use all) You can pass query/schema/table class as either an item reference to the class, or as the name of the class, in the form [library.]class, where the library defaults to the current library if omitted. If you pass a schema class, or a table class that references a schema class, then the list is defined to have all columns in the schema, unless you pass an explicit list of columns to use from the schema (via the cCol1,... parameters). Note that there is an empty parameter to separate the explicit column list from the cons-params that are passed to $construct for the table instance. Note also that this empty parameter is still required when using a query class or table class that references a query class. |
| $dodelete | $dodelete(wRow) deletes a row from the server database; called by $dodeletes() |
| $dodeletes | $dodeletes([bDisableWhere=kFalse]) deletes smart list rows with status kRowDeleted from a server database |
| $doinsert | $doinsert(wRow,iLine) inserts a row into the server database; called by $doinserts().iLine is the line number in the list to which wRow corresponds |
| $doinserts | $doinserts() inserts smart list rows with status kRowInserted into a server database |
| $doupdate | $doupdate(wRow,wOldrow,iLine) updates a row in the server database; called by $doupdates().iLine is the line number in the list to which wRow corresponds |
| $doupdates | $doupdates([bDisableWhere=kFalse]) updates smart list rows with status kRowUpdated in a server database |
| $dowork | $dowork([bDisableWhere=kFalse]) executes $dodeletes(),$doupdates(),$doinserts() in that order |
| $fetch | $fetch(iFetchCap[,bAppend=kFalse]) fetches the next iFetchCap rows from the server;for lists,appends data to the list if bAppend is kTrue |
| $filter | $filter(searchCalculation) applies a filter to a smart list and returns the number of rows rejected from the list by the filter See $unfilter below for more details on filtering |
| $first | $first([bSelOnly=kFalse,bBackwards=kFalse,condition]) sets $line to first line matching parameters;returns item reference to it.If bSelOnly,matches selected lines only;if bBackwards,matches lines in reverse;if condition is present lines must match it |
| $includelines | $includelines(iLinesToInclude) specifies which lines are present in the normal list of a smart list.iLinesToInclude is a sum of kRow... constants or kRowAll to include all lines |
| $insertnames | $insertnames([cRowName]) returns text suitable for using with an INSERT statement |
| $loadcols | $loadcols(var1,var2,...) download the column values into the variables |
| $merge | $merge(lList[,bByName=kFalse,bSelectedOnly=kFalse,bClearDestList=kFalse]) merge two lists |
| $next | $next(rRow|iRowNumber[,bSelectedOnly=kFalse,bBackwards=kFalse,condition]) sets $line to the next line after the line identified by the first argument.If iRowNumber is zero,processing starts at $line.See $first for definitions of the other parameters |
| $redefine | $redefine([var1,var2,...]) redefines a list or row variable with the names of the parameters.Each parameter can also be a file class name(a string) from which all columns are used;append /S to the file class name to skip empty columns |
| $refilter | $refilter() reapplies all the current filters to a smart list |
| $remove | $remove(rLine|iLineNumber|kListDeleteSelected|kListKeepSelected) deletes the specified lines from the list |
| $revertlistdeletes | $revertlistdeletes() causes deletes made to the smart list to be reversed |
| $revertlistinserts | $revertlistinserts() causes inserts made to the smart list to be removed |
| $revertlistupdates | $revertlistupdates() causes updates made to the smart list to be reversed |
| $revertlistwork | $revertlistwork() causes all deletes, inserts and updates made to the smart list to be reversed |
| $savelistdeletes | $savelistdeletes() causes deletes made to the smart list to be made permanent |
| $savelistinserts | $savelistinserts() causes inserts made to the smart list to be made permanent |
| $savelistupdates | $savelistupdates() causes updates made to the smart list to be made permanent |
| $savelistwork | $savelistwork() causes all deletes, inserts and updates made to the smart list to be made permanent |
| $search | $search(calculation[,bFromStart=kTrue,bOnlySelected=kFalse,bSelectMatches=kTrue,bDeselectNonMatches=kTrue,bSetCurLineWhenNotSelecting=kTrue]) searches the list $search searches a list using the specified calculation; this method has the same function as the Search list command. The search calculation can use $ref.colname or list_name.colname to refer to a list column. With bSelectMatches or bDeselectNonMatches the first line number whose selection state is changed is returned (or 0 if no selection states are changed), otherwise the first line number which matches the selection is returned (or 0 if no line is found). This method does not change any CRB values, the current line is changed if neither bSelectMatches or bDeselectNonMatches is used. bOnlySelected restricts the search to selected lines. If bFromStart is kTrue, Omnis searches all of the lines in the list, starting at line 1; otherwise, Omnis starts the search at line ($line + 1). |
| $select | $select([cText,...]) issues a SELECT statement to the server. If passed, select concatenates 'cText' and any following arguments to the select statement |
| $selectdistinct | $selectdistinct([cText,...]) issues a SELECT DISTINCT statement to the server. If passed, select concatenates 'cText' and any following arguments to the select statement |
| $selectfetch | $selectfetch([bDistinct=kFalse,iMaxRows=1,bAppend=kTrue,cText,...]) issues SELECT or SELECT DISTINCT statement to server when $useworker is true.If passed,concatenates 'cText' and any following arguments to the statement |
| $selectnames | $selectnames() returns text suitable for using with a SELECT statement |
| $sort | $sort(calculation,bDescending=kFalse,...) sorts the list You can specify up to 9 sort fields, including the sort order flag. The sort fields or calculations can use $ref.colname or list_name.colname to refer to a list column. The sort order flag defaults to kFalse (that is, the sort is normally ascending). For calculated sorts, the calculation is evaluated for line 1 of the list to determine the comparison type (Character, Number or Date). |
| $sqlerror | $sqlerror(iErrortype,iErrorcode,cErrortext) called when an error occurs during $select(),$selectfetch(),$fetch(),$update(),$delete() and $insert() |
| $totc | $totc(expression[,bSelectedOnly=kFalse]) returns the total of the expression evaluated for either all lines in the list,or if bSelectedOnly is kTrue,all selected lines in the list |
| $undodeletes | $undodeletes() restores any deleted rows to a smart list defined from a table |
| $undoinserts | $undoinserts() removes any inserted rows from a smart list defined from a table |
| $undoupdates | $undoupdates() restores any updated rows in a smart list defined from a table, to their original values |
| $undowork | $undowork() executes $undoinserts(),$undoupdates(),$undodeletes() in that order |
| $unfilter | $unfilter([iLevel]) removes a filter or filters from a smart list Filtering uses the row present indicator of the history list to filter out rows. In other words, after applying a filter, Omnis has updated $rowpresent to kTrue for each row matching the search criterion and kFalse for the others. Filtering applies only to the rows in the normal list, that is, rows where $rowpresent is kTrue, with the result that repeated filtering can be used to further restrict the lines in the list. Each history row contains a filter level, initially zero. When you apply the first filter, Omnis sets the filter level of all rows excluded by the filter to one; that is, for each row in the normal list, for which $rowpresent becomes kFalse, $filterlevel becomes one. Similarly for the nth filter applied, Omnis sets $filterlevel for the newly excluded rows to n. You can apply up to 15 filter levels. Whenever a row is made present, for whatever reason, the filter level is set back to zero, and whenever the row is made not present, for any reason other than applying a filter, the filter level is also set back to zero. When called with no parameters, $unfilter() removes the latest filter applied. Otherwise, $unfilter removes filters back to the level indicated by the parameter. Thus $unfilter(0) removes all filters, $unfilter(1) removes all but the first, and so on. |
| $updatenames | $updatenames([cOldrowName][,cRowName='',bExcludeWhere=kFalse,wOldrow=#NULL,wRow=#NULL]) returns text suitable for using with an UPDATE statement. If wOldrow is supplied,only columns in the new data (wRow or $cinst) are included |
| $wherenames | $wherenames([cOperator][,cRowName]) returns text suitable for using as a WHERE clause |
| Standard | $addafter $addbefore $appendlist $count $insertlist $makelist $sendall |
| $cols | $filters | $history | $ivars |