DB - General Database Update Module
| value = DB ( dbsel tagarg arg ... ) |
| value | The returned value depends on the arguments | |
| dbsel | A database selection expression (typically of the form: dim:(selection criteria)) | |
| tagarg | Zero or more of the tagged arguments described below. | |
| arg | Zero or more other arguments. |
| Action::actdim | The dimension specifying the action to be be performed. Any subsequent arguments of this dimension are inserted into the context as-is. | |
| Table::tbldim | The dimension specifying the table to be affected. Any subsequent arguments of this dimension are inserted into the context as-is. |
This module provides a general framework for specifying database updates in a non-database specific fashion. It simply repackages its arguments and calls user-defined intersections to perform the actual database updates.
The actions performed by this module are as follows: first the point IntMod:DB is inserted into the context; then an empty list (Dim:List) point is created; each argument is evaluated, if the result is a point on Dim:Alpha or either the action or table dimensions then those points are inserted into the context otherwise the point is appended to the list. After all arguments are evaluated the list is inserted into the context, finally the intersection [UV4:DB] is evaluated and the result of the evaluation is returned as the module result.
The real work is performed within the [UV4:DB] binding that is evaluated. This is up to the implementor and the target database(s) to be updated.
By convention, a DB call that results in an insertion should return the primary key of the new row as its value.
The V4 xlib application library implements the SQL Insert, Update and Delete operations using this module. See the examples below.
| DB(employee:() name:"John Smith" dob:1/1/1980 department:it salary:50000) | Inserts a new row into the employee table | |
| DB(item:(itemcode:abcd) listPrice:49.99) | Updates the item with code 'abcd' with a new list price of 49.99 | |
| DB(customer:(dateLastOrder:<1/1/2000)) | Deletes all customers with no orders since 1/1/2000 |
DBConnect - Connect to an external Dataset
| odbccon = DBConnect ( tagarg tagarg... ) |
| odbccon | A point on the odbc dimension representing the connection. | |
| tagarg | Two or more of the tagged arguments below. |
| Access::method | Determines the access method to the database. The supported methods are ODBC (default) for an ODBC connection and mySQL for direct linkage to a mySQL database. | |
| AutoCommit::logical | Sets the auto-commit-of-transactions mode for the connection. The default is Logical:No for no auto-commit. Various interfaces seem to like different settings. Most want no auto-commit (the default). The IBM iSeries ODBC interface to the AS400 likes to have auto-commit enabled. | |
| Commit::logical | Commits (if logical is TRUE) or rolls back (if logical is FALSE) the prior transactions. This option may only be used when AutoCommit is set to FALSE. | |
| Connection::spt | An alpha string giving optional connection information. (Same as the Host tag.) | |
| Dim::dpt | A dimension of type XDB. The module returns a point on this dimension corresponding to the connection. This is a mandatory argument. | |
| DSN::spt | An alpha string specifying the dataset to connect to. | |
| Host::spt | An alpha string giving optional connection information. (Same as the Connection tag.) | |
| Id::conid | Assigns the identifier conid to the connection. This may be referenced in other DBConnect, DBXct and DBGet modules. | |
| Password::spt | An optional string argument with the user's password. A zero-length string is ignored. | |
| Port::number | Overrides the default TCP/IP port number used for connecting to a database engine (e.g. mySQL). | |
| User::spt | An optional string argument with the user's name. A zero-length string is ignored. |
| Active? | Returns Logical:true is the connection is currently active (i.e. in process of returning rows from a prior DBGet) and Logical:false if it is idle. | |
| Open? | Returns TRUE if the specified connection (see Id option) has already been opened. The module fails otherwise. | |
| Reset? | Resets the connection with the database server. This can be used to clear mySQL out-of-synch errors. |
The DBConnect module makes a new connection to the specified data set. The User and Password tags are needed only if the data set requires them.
If the Access is via ODBC then the DSN option is a 'data source name' that must be predeclared on the host system. Instructions for doing this can be found here.
If the Access is via mySQL then the DSN option is the name of a mySQL database.
|
DBConnect(DSN::MyData User::Freddy Password::Secret) |
DBError - Returns Last External Database Error Message as Alpha Point
| errpt = DBError ( ) | |
| point = DBError ( tagarg ) |
| errpt | The last error message generated by any of the dbXXX modules. | |
| point | The resulting point | |
| tagarg | One or more of the tagged arguments below. |
| Error? | Returns the last error. |
This module returns the last error generated by any of the dbXXX modules.
| DBError(Error?) | Returns the last error | |
| DBError() | Same as the prior example |
DBFree - Free an External Database Connection or Statement
| logres = DBFree ( odbc ) | |
| logres = DBFree ( tagarg tagarg ) |
| logres | The module always returns Logical:True. | |
| odbc | An XDB point such as one returned by the DBConnect module. | |
| tagarg | One or more of the tagged arguments below. |
| Connection::conid | Free (close) the connection previously opened with DBConnect(Id:stmtid). | |
| Dim::dim | Use the statement associated with the connection associated with dim. | |
| Statement::stmtid | Free the statment previously defined with DBGet(Id:stmtid). |
| All? | Frees all connections and statements currently active. |
This module frees up the resources taken by an XDB statement or connection. If the DBC argument is given then the entire connection is dropped.
|
DBFree(Stmt::ODim*) |
Dbg - Debugging Module
| value = Dbg ( tagarg ... ) |
| value | The returned value depends on the arguments. | |
| tagarg | One or more of the tags decribed below. |
| Break::list | Sets a breakpoint with all of the options (described above) contained in list. | |
| Break::option | Sets a breakpoint with one of the following options: AutoStep, Break, Context, Continue, Evaluations, Fail, Location, Once, Point, Results, Silent, Skip, Stack, Time | |
| Delete::bpnum | Delete the specified break point number. | |
| Do::point | Evaluate the point at the breakpoint. | |
| Fail::point | Evaluate the point and iff the evaluation fails then re-evaluate the point with the specified Trace options. | |
| If::point | Evaluate the point at the breakpoint and only break if the result is Logical:True. | |
| Line::num | Set the breakpoint at line num of the specified Source file. | |
| Log::num | This is the same as the 'Set Trace Lognum' command. | |
| Module::modname | Break at this V4 module. | |
| Progress::num | This is the same as the 'Set Trace Progress num' command. | |
| Return::point | Return point as the value for the the point at the specified break. | |
| Source::name | This is the same as 'Debug Source name'. | |
| Source::num | This is the same as 'Debug Source num'. | |
| Trace::list | Sets the V4 trace flag to all of the options within the list. When used in conjuction with the Do or Fail tags it set the V4 trace flag only for the duration of the Dbg call. | |
| Trace::tag::list | This option permits finer control over the V4 trace flag. If tag is Test then it tests the trace options in list and returns Logical:True if they are all set, Logical:False otherwise. If Plus then adds the specified options to the existing V4 trace value. If Minus then removes the specified options. If Set then it sets the V4 trace to the specified options (same as Trace::list). | |
| Trace::option | Set the trace option for this scope of this breakpoint command. The allowed option values are: AddContext, All, Arith, AutoContext, Bind, BindEval, Binding, Compares, Dimension, Dimensions, Errors, EvalPt, EvalToContext, Frame, IsctFail, Lists, Log, Macro, NBLBindings, None, ODBC, Off, Optimize, Point, Progress, Recursion, Tally, TallyBind, TimeStamp, V4IS, XDB. | |
| Value::point | Return point as the value for the the point at the specified break. (Same as the Return option.) | |
| Web::list | Sets up process for debugging via web client. list is a 4-element list: (messageId url port sesKey) where messageId is an id returned by the Message() module that is listening for messages, url and port are the to a v4WebSocket instance and sesKey is the process' session key. |
| Break? | Generates an immediate V4 breakpoint. |
This module duplicates all of the functionality of the Debug command.
DBGet - Retrieve Statement RowSet from Datasource
| odbcstmt = DBGet ( tagres tagres ) |
| odbcstmt | An odbc point corresponding to the results of the processing of the SQL statement. | |
| tagarg | One or more of the tagged arguments below. |
| Begin::isct | Evaluate the isct after the data has been retrieved but before any of the data is returned. For example, this can be used to initialize based on the number and names of the returned columns. | |
| Cache::log | This option is used to override the automatic caching of retrieved records. If log is TRUE then the current row is saved. If FALSE then then current row is not saved. | |
| Close::log | If log is TRUE then both the statement and connection associated with this XDB request are closed after the last row is returned. If FALSE (default) then only the statement is released, the connection is kept open. | |
| Connection::id | Perform the access via the connection defined by id. This identifier must have been set with the Id option in the DBConnect call that opened the connection. | |
| Dim::dim | Use this dimension to find the associated connection. The dim must have been used in a prior call to DBConnect. | |
| Do::qisct | Evalute the quoted intersection after each row is obtained. Normally this module returns a list of XDB points which reference the rows of the selected table. With this option, the result of evaluating qisct is the returned unless the result is UV4:none. | |
| End::qisct | An optional quoted intersection point. If given then this point is evaluated after the last row is returned. | |
| Id::stmtid | Identifies this selection as stmdid. This statement may be then referenced within a subsequent DBGet or DBInfo call via the Statement tag. | |
| ListOf::option | Returns various lists depending on option. If option is Ids then a list of prior DBGet statements associated with the current dimension are returned (i.e. the Id values). If option is Rows then a list of the rows associated with this selection are returned. If option is Save then all of the rows to be selected are automatically saved within V4 and may be accessed at any later time with in the current V4 process. | |
| ListOf::log | Specifies whether or not V4 is to keep a list of the rows returned. If log is FALSE (default) then no list is maintained. If TRUE then a list is saved and may be referenced with the ListOf? opttion. | |
| SQL::spt | An alpha string to be processed as an SQL statement against the dataset. If no DBC tag is given then the first connection made is defaulted. This is a mandatory argument. | |
| Statement::stmtid | This call is referencing a prior DBGet identified with the Id option, not creating a new set of rows. | |
| Table::name | Returns a rowset of all the columns within the table name. For mySQL databases this consists of the following information: fieldName dataType isNULLOK isKey defaultValue otherParams. | |
| Target::dim | Specifies a target dimension for the resulting row points. If this option is not given then V4 uses an internal value to represent each point. If a dim is given, then this DBGet call returns points on that dimension. See also the Value option. | |
| Type::tabletype | Instead of returning a rowset from a table, this option returns a rowset of tables that are of type tabletype. This option may be used in conjunction with the Table tag. | |
| Value::colnum | Specifies the column number (colnum) to be used as the value for each row. A Target must also be given to use this option. The results of this call will be a list of points on the target dimension corresponding to the value in the colnum column of each row. If this option is not specified then the values of the points in the list will start with 1 and increment by one for each subsequent point. With this option, the values correspond to the value of colnum for the row being returned. |
| ListOf? | When used with dimension, returns a list of statement identifiers associated with a dimension (see examples below). When used with a statement id, it returns a list of all points selected in that statement. | |
| ListOf? | This is the same as specifying a ListOf:Save option. | |
| Num? | Returns the number of rows in the rowset. Note that most ODBC drivers do not implement this capability. If the feature is not supported then -1 is returned. | |
| Table? | Returns a rowset of all tables within the datasource. | |
| Unique? | Not yet implemented. |
This module passes the specified SQL command to the handler associated with the specified data set (connection). The resulting point represents the results of the command. In most cases the point must be coerced to a list to access the individual rows of the resulting table.
If the result of this module is used as a list to another module then the statement and connection are automatically freed when then end-of-list is reached.
The Table and Type arguments may be specified instead of the SQL argument to return a rowset of tables within the current dataset. The columns returned are TABLE_QUALIFIER (usually the actual file containing the dataset), TABLE_OWNER (the owner of the table), TABLE_NAME (the name of the table), TABLE_TYPE (the type of table), and REMARKS (a description of the table). Any column value that does not apply is returned as a NULL value.
| [CusList] DBGet(SQL::"Select Name,ID,State From Customers") | binding to select the name, id, and state from the Customers table | |
| DBConnect(Dim:ODBCX DSN::TestDatabase) | connect to database | |
| Enum([CusList] @EchoT(dbVal(ODBCX* Dim:Alpha 1) dbVal(ODBCX* Dim:State 3)) | echos the name and state for all rows selected | |
| Enum(DBGet(ODBCX Table?) @EchoT(dbVal(O* Dim:Alpha Int*),"x"/Int:1..5)) | lists all tables, views, etc. in the datasource | |
| Enum(DBGet(Dim:O Table::"%info%" Type::Table) @EchoT(dbVal(O* Dim:Alpha Int*),"x"/Int:1..5)) | lists just tables matching the pattern "*INFO*" |
Example 25 - Show all tables and columns in mySQL database
|
[showMYSQLTables] Do(DBConnect(DSN:dataSetName Id::mySQL Access::mySQL User::userName Password::userPassword) EchoT("Field" "Type" "NULL OK" "Key" "Default" "Other") Enum(DBInfo(Connection::mySQL Table?) @Do(Echo("Table " Alpha*) Enum(DBGet(Connection::mySQL Table::Alpha*) @Do(EchoT(dbVal(mySQL Dim:Alpha 1) dbVal(mySQL Dim:Alpha 2) dbVal(mySQL Dim:Logical 3) dbVal(mySQL Dim:Alpha 4) dbVal(mySQL Dim:Alpha 5),"(null)" dbVal(mySQL Dim:Alpha 6)) )) )) ) |
DBInfo - Return Information on Current Connection or Statement
| result = DBInfo ( tagres tagarg ) |
| result | The result of the call, based on Tagged Result below | |
| tagres | Tagged point indicating wanted result, see list below. | |
| tagarg | One or more of the tagged arguments below. |
| Column::npt | The column number to return information on. Mandatory unless Columns? specified as tagged result. | |
| Dim::dim | Use the statement associated with the connection associated with dim. |
| ColCap? | The column caption (field name). | |
| Columns? | The number of columns returned from the last DBGet processing. | |
| Connection? | Returns the full connection string when a connection is made with the DBConnect(Connection) option. | |
| Length? | The length of the column. | |
| Precision? | The precision of the column | |
| Scale? | ||
| Table? | Returns a list of tables in the specified database. The list entries are points on Dim:Alpha. | |
| Type? | The internal type code of the column. See an ODBC manual for the meanings. |
The DBInfo module returns information about the results of the last DBConnect or DBGet module processing.
| Dim mysql XDB | Creates an external database dimension | |
| DBConnect(Dim:mysql DSN::table Access::mySQL User::root Password::"secret" Id::mysql) | Connects to the mySQL database 'table' | |
| DBInfo(Connection::mysql Table?) | Returns a list of all the tables in the above database | |
| EnumCL(Col:=DBInfo(Columns?) @DBInfo(Column::Col* ColCap?)) | returns a list of column names from last DBGet() |
dbVal - Retrieve Column Value from Current Row
| value = dbVal ( xdb Dim:dim column ) | |
| value = dbVal ( xdb dfltval column ) | |
| value = dbVal ( xdb column ) |
| value | The value of the specified column | |
| xdb | An XDB point corresponding to a row in a processed statement. This may be the current value of an XDB dimension, a reference to the dimension itself, or a statement Id declared in the DBGet() call. | |
| Dim:dim | An optional dimension for the result. If no Dimension is given then the dimension is automatically determined from the internal XDB column type. | |
| column | The column number to retrieve. | |
| dfltval | If the second argument is a point not on Dim:Dim then the returned point is coerced to a point on the dimension of dfltval. Additionally, dflt is returned as the value of the module if the XDB driver returns a null value for this row/column. |
The DBVal module returns a column value for the current row of the last SQL processing vial DBGet.
V4 attempts to coerce the XDB value into the destination dimension if the data types do not directly match. If the dimension has an acceptor (UV4:Acceptor) then it is invoked for the XDB value. Any ODBC value going into an Alpha point is converted to its corresponding character value. Dictionary and External dictionary points are converted. If an invalid value is presented then '#0' is returned. For logical points, V4 converts numbers greater than 0 to true, all others to false. A string beginning with the any of the characters (0nNfF) results in false, (1yYtT) results in true, all others with an error. For numeric points, V4 attempts to convert strings to the corresponding number.
Null values in the XDB driver result in module failure unless the second argument is a default value.
| dbVal(dXDB* Dim:Year 3) | returns the value in column 3 under the Year dimension | |
| dbVal(Dim:dXDB Dim:Year 3) | same as above, automatically determines dXDB* |
DBXct - Executes an SQL Statement
| result = DBXct ( odbc tagarg ) |
| result | The result of the module. See the Rows tag below. | |
| xdb | An XDB point corresponding to a relational database (see DBConnect). | |
| tagarg | One or more of the tagged arguments below. |
| Connection::id | Executes the SQL through the connection previously opened with DBConnect(Id:id). | |
| Dim::dim | An XDB dimension previously connected to a database with a DBConnect module call. | |
| SQL::spt | An alpha string to be processed as an SQL statement against the dataset. If no DBC tag is given then the first connection made is defaulted. This is a mandatory argument. |
| Close? | To automatically close the XDB connection & free up all resources. This option can be used instead of a separate call to DBFree. | |
| Key? | Returns the primary key of the inserted record. This option is currently supported only with inserts to tables with auto-increment primary keys within mySQL. | |
| Rows? | If given then this module returns the number of rows selected/updated. Otherwise Logical:True is returned. |
This module is used to execute any valid SQL statement. The statement is automatically released (freed) upon completion, the connection is not.
| Dim dXDB XDB | Declare generic XDB dimension | |
| [SQLXct Alpha.. DSN..] | Executes ODBC/SQL Command on dataset DSN* */ | |
| Do(Context(DBConnect(Dim:ODBCX DSN::DSN*)) Value::DBXct(SQL::Alpha*) DBFree(ODBCX*)) | Connect, execute, free dataset | |
| Context(DBConnect(Dim:ODBCX DSN::DSN*)) | When executing multiple statements on a dataset | |
| Enum(xxx.. @DBXct(SQL::Alpha*)) | it is more efficient to connect once, execute multiple | |
| DBFree(ODBCX*) | and then release the connection |
Def - Test Successful Evaluation of Intersection
| value = Def ( point [[defvalue] undefval] ) | |
| value = Def ( point tagargs ) | |
| value = DefQ ( arguments same as Def ) | |
| value = NDefQ ( arguments same as Def ) |
| value | The point returned as the result. | |
| point | A point to be tested. This is either an intersection or dim* point. | |
| defvalue | The point to be returned if isct is defined. | |
| undefvalue | The point to be returned if isct is not defined. |
| Else::value | The value to be returned if the first argument is not defined. | |
| ElseIf::logical | If the first argument is not defined then logical is evaluated. If the result is Logical:True then the following Then argument is evaluated. If it is Logical:False then the following Else argument is evaluated. Multiple ElseIf tags may be chained. | |
| Then::value | The value to be returned if the first argument is defined. |
The Def module tests its first argument to see if it can be successfully evaluated. If it can then the second argument is returned otherwise the third argument is returned. If only two arguments are given then the value of point is returned if it evaluates, otherwise the second argument is returned. If only one argument is given then Def attempts to evaluate it and returns Logical:True if the evaluation succeeded, Logical:False otherwise.
The Then, ElseIf, and Else tags may be used to explicitly define results or chain multiple conditionals. If tags are present but the tag to be taken as the result is missing then UV4:none is returned.
If the argument to Def is a reference to a point in the context (dim*) then Def will return Logical:True if the dimension has a point in the current context and Logical:False otherwise.
DefQ differs from Def in that the first argument is not evaluated as part of the normal module argument evaluation process.
nDefQ is the logical compliment of DefQ (i.e. nDefQ() = Not(DefQ()))
| DefQ(UMonth*) | returns Logical:True if UMonth in context otherwise Logical:False | |
| DefQ(UMonth* Then::Logical:True Else::Logical:False) | This is identical to the prior example | |
| DefQ(UMonth* Then::UMonth*) | returns UMonth* if defined, fails if not | |
| DefQ(UMonth* UMonth:{now}) | returns UMonth* if defined, current month if not | |
| UMonth*,UMonth:{now} | same as above | |
| UMonth*,UMonth:{now}* | returns UMonth* if defined, if not then return current and put into context | |
| DefQ(Cus.Name "unknown) | same as Cus.Name,"unknown" | |
| Def(MakeI(....) Then::1 Else::2) | evaluates the MakeI and then evaluates the result returning 1 if defined, 2 if not. | |
| DefQ(UDate* Then::{UDate* + 10}) | returns UV4:none if Dim:UDate not in context |
Dim - Create, Alter, or Retrieve Dimension Attributes
| result = Dim ( dim tagarg ... ) | |
| dim = Dim ( name tagarg ... ) | |
| dim = Dim ( point ) |
| result | The result depends on the arguments to the module. | |
| tagarg | One or more tagged arguments. | |
| dim | The newly created dimension. | |
| name | The name of the dimension to be created. | |
| point | If a single argument is given then the dimension of that argument is returned. If the single argument is a reference to a shell point in context then the dimension of the shell point's context value is returned. |
| Acceptor::logical | Sets the attribute to TRUE or FALSE. | |
| ADPoint::point | Sets the optional additional Acceptor/Displayer point. | |
| All::num | Set the All-value to num. | |
| AsIs::logical | Sets or resets the Asis attribute of a dimension. See the Dimension for more discussion and examples. | |
| Attributes::string | Sets the Attributes option to string. | |
| BaseDate::point | Sets the base date attribute for a UWeek dimension. point must be a point on a UDate dimension. | |
| BindEval::logical | Sets the attribute to TRUE or FALSE. | |
| Binding::num | Sets the Binding number. | |
| Calendar::name | Sets the Calendar type. | |
| Create::logical | Sets the auto-create option on the dimension. The default for Dictionary points is TRUE. | |
| Decimals::num | Set the number of decimal places for a numeric dimension. | |
| Description::string | Sets the dimension description. | |
| Displayer::logical | Sets the Displayer option for the dimension. | |
| DisplayerTRACE::logical | Sets the DisplayerTRACE option for the dimension. | |
| DotDotToList::logical | Sets the option. | |
| DotIndex::logical | Sets the option. | |
| DotList::logical | Sets the option. | |
| Duplicate::logical | Sets the option (default is TRUE). | |
| Entries::dim | Defines the list entry dimension. | |
| FileName::logical | Sets whether or not an Alpha dimension is to be parsed as file names. | |
| Format::string | Sets the formatting string for the dimension. | |
| Hidden::logical | Sets the 'hidden' flag for the dimension. If set to TRUE then the dimension will appear to be undefined (i.e. any reference will result in a 'Point not defined...' error. Furthermore, any future output of the dimension will be displayed with 'UV4:hidden'. | |
| History::logical | Sets the 'history' flag for the dimension. If TRUE then only historical dates will be allowed, if FALSE then any time period is allowed. | |
| IC::logical | Sets the option (default is TRUE). | |
| IsA::dim | Sets the (parent) Is-A dimension for the dimension. | |
| JSON::logical | Sets/resets the JSON attribute on an Alpha/Bigtext dimension. If set then the Context ADV command will immediately parse the JSON string. | |
| MMDDYY::logical | Sets the option. | |
| MMYY::logical | Sets the option. | |
| Multiple::logical | Sets the option. | |
| None::num | Sets the None value for the dimension to num. | |
| NoPrefix::logical | Sets the NoPrefix option (controls display of prefix dimension name when points are output). | |
| Normalize::logical | Sets the option. | |
| Overload::num | Sets the option. | |
| Periods::num | Set the number of periods per year for a UPeriod dimension to num. | |
| Point::mode | Sets the point creation mode to either NEW or POINT. | |
| Range::logical | Sets the option. | |
| RDB::mode | Sets the handling of relational database numerics to NORMAL or RAW. | |
| References::type | Returns a list of all dimension references of type which must be one of: Defined, Dim, Lexical, Point, Used, or Value. Defined returns the reference in which a dimension is defined. Dim returns references where a dimension is referenced as Dim:dimension. Lexical returns references where a dimension is referenced in a lexical command (ex: If not Dimension dimension Then ...). Point returns references of the form "Point dimension values...". Used returns all references that are not of type Defined (i.e. Dim, Lexical, Point, Used). | |
| Structure::logical | Sets the option (default is TRUE). | |
| TimeZone::zone | Sets the timezone for a Calendar dimension. The value must be an integer from -12 to +12. If not specified then the zone is assumed to be 'local'. | |
| Type::pnttype | The point type of the dimension. This is mandatory when creating a new dimension. | |
| UOMId::num | Sets the unit-of-measure id for the dimension. | |
| Value::type | Enables the use of 'dim:[...]' and 'dim:(...)' notation. If type is List then a list of the enclosed points is passed to the handler. If type is Tree then the enclosed points are parsed as a boolean expression and a V4 tree of the parsed result is passed to the handler. See the Dim command for more information on this option. | |
| XML::logical | This option enables the passing of HTML/XML strings from the web via the dimension. If TRUE then XML strings may be passed. If FALSE then the leading '<' of any tags are replaced with '<'. | |
| YMDOrder::list | Sets the year-month-day order to the list specified. | |
| YMDOrder::ymd | Sets the year-month-day order to ymd. |
| Acceptor? | Returns the current value of the attribute. | |
| ADPoint? | Returns the ADPoint for the dimension or fails if none is set. | |
| All? | Returns the All value or fails if none is set. | |
| AsIs? | Returns the current setting of the As-Is attributes. | |
| Attributes? | Returns the Attributes string or fails if none is set. | |
| BaseDate? | Returns the BaseDate value (as a point on Dim:UDate) for the UWeek dimension, fails for other dimension types. | |
| BindEval? | Returns the current BindEval setting (TRUE or FALSE). | |
| Binding? | Returns the current Binding number. | |
| Calendar? | Returns the calendar type as a point on Dim:UV4 or fails if dimension is not a calendar. | |
| Create? | Returns the current value (TRUE or FALSE). | |
| Decimals? | ?Dim:NId num | |
| Description? | Returns the dimension description. | |
| Displayer? | Returns the Displayer value (TRUE or FALSE). | |
| DisplayerTRACE? | Returns the DisplayerTRACE value (TRUE or FALSE). | |
| DotDotToList? | Returns the current value (TRUE or FALSE). | |
| DotIndex? | Returns the current value (TRUE or FALSE). | |
| DotList? | Returns the current value (TRUE or FALSE). | |
| Duplicate? | Returns the current value (TRUE or FALSE). | |
| Entries? | Returns the dimension of list entries or fails if not set. | |
| FileName? | Returns the current setting (TRUE or FALSE). | |
| Format? | Returns the formatting string or fails if none set. | |
| Hidden? | Returns the state of the Hidden flag for the dimension. | |
| History? | Returns the setting of the History attribute. | |
| IC? | Returns the current value (TRUE or FALSE). | |
| IsA? | Returns the parent dimension or fails if none set. | |
| Listof?? | Returns the dimension attributes of the prior argument. | |
| MMDDYY? | Returns the option (TRUE or FALSE) or fails if not applicable. | |
| MMYY? | Returns the option (TRUE or FALSE) or fails if not applicable. | |
| Multiple? | Returns the current value (TRUE or FALSE). | |
| None? | Returns the value or fails if not applicable. | |
| NoPrefix? | Returns the NoPrefix setting (TRUE or FALSE). | |
| Normalize? | Returns the current value (TRUE or FALSE). | |
| Overload? | Returns the current value. | |
| Periods? | Returns the number of periods per year for a UPeriod dimension. | |
| Point? | Returns the mode as a point on Dim:UV4. | |
| Range? | Returns the current value (TRUE or FALSE). | |
| RDB? | Returns the RDB mode as a point on Dim:UV4. | |
| References? | Returns a list of all references to this dimension. This requires that the Set Trace Dimensions command be enabled. The returned value is a list of numbers that refer to more information. See the SourceFile option within the V4 module for more information. | |
| Structure? | Returns the current value (TRUE or FALSE). | |
| TimeZone? | Returns the timezone or fails if not applicable or not set (i.e. local). | |
| Type? | Returns the point type of the dimension as a point on Dim:UV4. | |
| UOMId? | Returns the UOM id or fails if not applicable. | |
| Value? | Returns UV4:none, UV4:List or UV4:Tree depending on the state of the Value flag for the dimension. | |
| XML? | Returns TRUE or FALSE depending on the current setting of the option. | |
| YMDOrder? | Returns the year-month-day order (or list) for the dimension or fails if not-applicable or not set. |
This module may be used to create dimensions, alter the runtime attributes of a dimension, or return any of the attributes set for a dimension.
If the first argument is a dimension then the module either sets an attribute or returns an attribute depending on the tagged arguments. Otherwise, the first argument is taken as the name of a new dimension to be created and subsequent arguments define the attributes. In this case the Type argument is mandatory and determines the point type of the dimension.
For the most part, the arguments match the options of the Dimension command.
Div - Divide Two Numbers
| resnum = Div ( npt1 npt2 tagarg ) | |
| resnum = DDiv ( npt1 npt2 tagarg ) |
| resnum | The resulting numerical point. Note that the dimension of the result is always the dimension of the second argument to the module for Div. The result is a point on the Num dimension for DDiv. | |
| npt1 | A numerical point. | |
| npt2 | A numerical point. | |
| tagarg | One or more optional tagged arguments described below |
| Error::num | Return num if the division results in an error (e.g. divide by zero). | |
| Round::num | Rounds the result to num number of decimal places if positive or to num significant digits if negative. |
Div divides the first argument by the second. If both arguments are a 32-bit integer value then an integer divide occurs and the result is the dimension of the second argument. Otherwise a floating point division is performed and the result is a point on the Num dimension. DDiv always performs a floating point division and results in a number on the Num dimension.
| Div(5 2) | Int:2 | |
| Div(5 2.0) | Num:2.5 | |
| DDiv(1000000 3) | Num:333333.333333333 | |
| DDiv(1000000 3 Round::2) | Num:333333.33 | |
| DDiv(1000000 3 Round::-3) | Num:333000 |
Do - Evaluate Points (in List)
| lastpt = Do ( tag isct isct ... ) | |
| lastpt = Do ( list ) |
| lastpt | The value of the last evaluated intersection. | |
| tag | Optional tagged arguments. | |
| isct | Do evaluates each argument and returns, as its value, the last point evaluated. | |
| list | A list of points to be evaluated. Again, the last point evaluated is returned as its result. |
| Context::point | For each enumeration point, V4 evaluates and inserts the point into the current context. If the point is UV4:none then nothing is done (i.e. UV4:none is not placed into the context). This point can be inserted into the context by quoting it: Context::@UV4:none. | |
| ContextP::point | This is identical to the Context tag except that if point evaluates to a list then each point in the list is inserted into the context. | |
| Continue::idname | Continue with the next point in the Enum with Id:idname. | |
| End::point | Evaluates the point, if the result is false, processing of the Do arguments continues. If it is true then processing immediately stops. | |
| Hold::point | Enters the point into the current context and prevents any nested calls from changing the point for that dimension. | |
| Id::name | Associates name with the current evaluation of the Do module. This name may be later referenced with the Continue and End tag values to alter the normal flow of the Do processing. See below for more details. | |
| Optimize::logical | Enables (if logical is TRUE) or disables (if FALSE) optimization of evaluations within the scope of the Do module. | |
| Out::fileid | Specifies that all subsequent data-stream output is to be directed to the output identified by fileid.This redirection is effective from the time the Out tag is evaluated until either another Out tag overrides it or until the V4 runtime exits from the scope of the current Do module. | |
| Value::point | Normally the Do module returns its last argument as its value. The Value tag can be used to override this. | |
| While::point | If this tag is specified then point is evaluated before subsequent point. If it evaluates to Logical:True then processing continues, if Logical:False then the Do processing terminates and returns the last processed point. If no points have been processed then the module fails. | |
| XML::point | The value of point is used as the begin/end XML tag for this Do. If point evaluates to a 0 length string then no begin/end tags are output. If the value is UV4:none then the option is ignored. |
| Continue? | Continue with the next point in the immediate parent Enum module. | |
| Optimize? | Toggles optimizations for subsequent evaluations within the module. |
Do evaluates all of it argument points or lists. Do returns the last point evaluated. The Context and Hold shell dimension points may be used to alter the context for the duration of the Do. Context::cpoint sets a context point which may be overridden in a nested evaluation. Hold::hpoint differs in that once the point has been added to the context, no other point in that dimension may be added to the context. Any attempts in nested evaluations will be ignored- no error is generated. Note that the tagged arguments are processed left to right with the other arguments. If an intersection is to the left of a tagged argument, it will be evaluated before the context is set.
If any of the points evaluated by the module result either the Continue? or Continue::name then the processing of the module is immediately terminated. If Continue? is detected then the module returns the last argument index processed. If Continue::name is detected and name is identical to the Id name then the module returns the argument index. If the names do not match then the Continue::name point is returned unchanged. (Note how the Enum module handles these points.)
If any of the points evaluated by the module result either the End? or End::name then the processing of the module is immediately terminated. If End? is detected then the module returns the End? point as its value. If End::name is detected and name is identical to the Id name then the module returns the End? point. If the names do not match then the End::name point is returned unchanged. (Note how the Enum module handles these points.)
The Optimize? tag triggers a particular optimization that can be very effective in reducing the CPU time required to perform a complex V4 analysis. The optimization consists of partially evaluating intersections of the form dim.point ([dim* point]) and if the partial evaluation results in a module call replacing the original intersection with the module call. For example if the first level evaluation of 'Customer.Name' results in 'AggVal(Customer* Dim:Name Int:3)' then replace 'Customer.Name' with 'AggVal(Customer* Dim:Name Int:3)'. The CPU reduction can be dramatic. Note that there are tradeoffs. One is trading speed for the loss of the contextual evaluation. In many cases it is well worth it. Also the assumed trapping of a failed intersection may not occur. Just be aware!
Optimizations can be toggled on or off. For example enabling optimizations for a Tally or Enum and then toggling off for output of results can get you the best of both worlds - fast scanning of large databases and contextual evaluations for output.
|
Bind [SalesByState] Enum([StateList] @EchoT([MonthsInYear Year:94] ) . . . Do(Hold::Year:95 [SalesByState]) |
The binding value for [SalesByState] generates a matrix where the columns are the months of the year for 1994. However, the Do puts a hold on Year:95 and then evaluates [SalesByState] forcing all references to Year to be 1995. The result is a matrix with columns for the months of 1995, not 1994!
Example 26 - When Optimize? May not be Appropriate
|
[Student.. Id UV4:IsctFail] Str("?Student-" Student* "?") [AnOptimizedBinding] Do(Optimize? ... Student.Id ... |
The example above demonstrates a possible problem with the Optimize? tag. The evaluation of the UV4:IsctFail binding may not occur on the failure of Student.Id if Student.Id has been replaced with a lower level result.
Drawer - Add, Remove, Retrieve Points from a Drawer
| value = Drawer ( drawerpt tagarg... ) | |
| value = Drawer ( tagarg... ) |
| value | The returned value depends on the arguments. | |
| drawerpt | A drawer point previously created with a call to this module. | |
| tagarg | One or more of the tagged arguments or tagged results below. |
| Add::point | Adds the point to the specified drawerpt. If the point already resides within the drawer then no action is occurs. In either case, the point is returned as the value of the module. | |
| Create::dim | Creates a new drawer point and returns it as a point on dimension dim. | |
| Delete::point | Removes point from the previously specified drawer. If point is not found in the drawer then the module fails. You may use the three-colon tag option to override this error. | |
| Dim::dimension | Returns all points within the drawer belonging to the dimension. If multiple points are found then they are returned as a list. If a single point is found then just that point is returned. | |
| Empty::logical | Returns TRUE or FALSE depending on whether or not the drawer is empty or not. See the examples below. | |
| Empty::All | Removes all points from the drawer specified by drawerpt. | |
| Filter::isct | Evaluates the isct for each point in the drawer. If the result is Logical:TRUE then the point is removed from the drawer and appended to a list. The list of all filtered points is then returned. If no points are filtered then an empty list is returned. An error is generated if the drawer is empty. | |
| Has::point | Returns TRUE if the drawer contains (has) the specified point. | |
| Head::point | Adds point to the head of the drawer. This option always adds the point even if it already exists within the drawer. | |
| ListOf::drawerpt | Returns all points in drawerpt as a list on Dim:List. | |
| Number::drawerpt | Returns the number of points in drawerpt. | |
| Pop::position | Removes a point from the previously specified drawer and returns it as the value of the module. The position value may be FIFO to remove the head of the drawer or LIFO to remove the tail of the drawer. | |
| Push::point | Adds point to the tail or end of the drawer. The point is always added, even if it is already within the drawer. | |
| Tail::point | Adds point to the tail or end of the drawer. The point is always added, even if it is already within the drawer. |
| Create? | Create a new new drawer point and return it as the value of the module. The result is a point on the Dim:UV4 dimension. | |
| Delete? | Removes the drawer from the current runtime environment. | |
| Empty? | Returns TRUE if the drawer is empty, FALSE otherwise. | |
| Head? | Returns the point currently at the head of the drawer. | |
| ListOf?? | Returns all points in the previously specified drawer as a list. | |
| Number? | Returns the number of points currently in the drawer previously specified. | |
| Pop? | Removes the tail of the drawer and returns it as the value (same as Pop:LIFO). | |
| Tail? | Returns the point at the tail or end of the drawer. |
This module is used to create and manipulate drawers - containers that hold points. Any number of drawers may be created and each drawer can hold a large (but not infinite) number of points. Drawers are points and a drawer may contain other drawers.
Currently drawers are implemented as runtime objects (i.e. they are not permanent nor can they be stored in areas.) This limitation may be changed in a future release of V4.
Points within a drawer are sequenced in the order of addition to that drawer. The Head and Tail arguments may be used to force points to either the head (start) or tail (end) of a drawer. The Pop argument may be used to treat a drawer as a simple FIFO or LIFO stack.
The Add , Empty, Head, Pop?, Remove and Tail tags can all be chained.
The In module may be used to determine if a point is within a drawer.
| EBind [D1] Drawer(Create?) | Bind [D1] to a new drawer (do not use the Bind command or a new drawer will be created with each evaluation of [D1] | |
| Drawer([D1] Add::123) | Add point 123 to drawer [D1] | |
| Drawer([D1] Add::hohoho) | Adds hohoho to the drawer | |
| Drawer([D1] Add::123) | Does nothing - 123 already within the drawer | |
| Drawer([D1] Tail::123) | Adds 123 to the end of the drawer | |
| Drawer([D1] ListOf?) | returns (123 hohoho 123) | |
| {hohoho == [D1]} | returns Logical:True (hohoho is in the drawer) | |
| Drawer([D1] Empty::All) | removes all points from the drawer | |
| Drawer([xyz] Empty::TRUE) | returns TRUE if the drawer is empty | |
| Drawer([xyz] Empty::FALSE) | returns TRUE if the drawer is not empty | |
| Drawer([xyz] Empty::All Add::point1 Add::point2) | empties the drawer and adds the two points |
DTInfo - Get Information on a Date and/or Time
| value = DTInfo ( timept [adj adj ...] [ tagres tagval ...] ) |
| value | The resulting date/time point. | |
| timept | A date, month, or time point. | |
| adj | One or more adjustments to the date (see below). | |
| tagres | One of the tagged result points below. | |
| tagval | Optional tagged value. |
| Age::base | Calculates the age, in years, between the first argument date and the base date. The result is the integer age in years. | |
| BaseDate::udate | A base or starting date to be used with UWeek?. If not specified then the first day of the year is used. | |
| BaseMonth::umonth | A base month to be used with UPeriod? and UQuarter?. If not specified then the first month of the year is the base. | |
| By::amount | Used with Number or To to set the increment of elements in the list to amount. | |
| Calc::exp | Perform a relative date calculation on the date. See here for details. | |
| Day::n | Adjust the date to the specified day of the month. This may be an absolute or delta number. For example Day::3 will adjust timept to the third day of the month it references. Day::+3 will add three days to the timept. If the argument is greater than 31 then the last day of the month will be used. | |
| DayOfWeek::day | Adjusts the date to the nearest date (before or after) such that the day of the week is day. | |
| DayOfWeek::+/-day | Adjusts the date to the nearest date (after if '+', before if '-') such that the day of the week is day. | |
| DayOfYear::day | Adjusts the date to the dayth day of the year where 1 is the first day and 365/366 is the last. If day exceeds the number of days in the year then the date rolls over into the next year. Values less than one roll back through prior years. | |
| Hour::+/-hh | Adjust the first argument up or down by the specified number of hours. | |
| Hour::hh | Adjust the first argument to the specified hour (hh). This may only be done with date-time and calendar points. | |
| LunarAltitude::geo | Returns the lunar altitude at the location specified by geo (a point on a Geocoordinate dimension) at the date(time) of the first argument. The result varies between -180 and +180 degrees. Moon rise and moon set correspond to zero degrees. If the first argument is a UDate or DateTime point then it is with respect to GMT, not local time. | |
| LunarPhase::degree | Returns the date and/or time when the phase of the moon matches degree. If degree is positive then the date-time is after then date-time of the first argument. If it is negative then it is the nearest date-time before the first argument. The phases of the moon vary from 0° (new moon), to 90° (first-quarter), to 180° (full) to 270° (last quarter). The result takes on the dimension of the first argument. Note that if the result is a UDate or DateTime point then the result is with respect to GMT, not local time. | |
| Minute::+/-mm | Adjust the first argument up or down by the specified number of minutes. | |
| Minute::mm | Adjust the first argument to the specified minutes (mm). This may only be done with date-time and calendar points. | |
| Month::n | Similar to Day except that it adjusts the month. | |
| Number::num | Return a list of size num of date-times starting with the first argument. See also the By tag. | |
| POSIX::n | Converts POSIX system date-time value to V4 date-time format. This must be the first argument to the module. | |
| Round::n | Adjusts the timept to the nearest n minutes. timept must be a date-time or time point. The argument need not be less than 60. For example 180 rounds to the nearest 3-hour period. | |
| Second::ss | Adjust the first argument to the specified seconds (ss). This may only be done with date-time and calendar points. | |
| Second::+/-ss | Adjust the first argument up or down by the specified number of seconds. | |
| SolarLongitude::degree | Returns the date and/or time when the solar longitude matches degree. If degree is positive then the date-time is after then date-time of the first argument. If it is negative then it is the nearest date-time before the first argument. The longitude of the sun varies from 0° (spring equinox), to 90° (begin of summer), to 180° (fall equinox) to 270° (begin of winter). The result takes on the dimension of the first argument. Note that if the result is a UDate or DateTime point then the result is with respect to GMT, not local time. | |
| To::dt | Generates a list of date-times from the first argument date-time to dt. | |
| UTime::time | Returns the time difference as a UTime point (i.e. seconds) between the first argument and time. The first argument and time must be of the same point type. This option is available for points of type UDate, DateTime, UTime, and Calendar. Note that the returned difference is always a positive value. | |
| Year::n | Similar to Day and Month except that it adjusts the year. |
| Day? | Return the day of the month (1-31) | |
| DaylightSavings? | Returns Logical:True if the timept refers to a date within the current U.S. definition of daylight savings time. Note that this only refers to a time period, not a location. | |
| DayOfWeek? | Return the day of the week. Monday is 1, Sunday is 7. | |
| DayOfYear? | Returns the day of the year (1 through 365). | |
| Easter? | Returns the date (on Dim: UDate) for the year specified or implied. | |
| Hour? | Return the hour as a number from 0 to 23. | |
| LunarPhase? | Returns the lunar phase (from 0 to 360 degrees) corresponding to the first argument. If the first argument is a UDate or DateTime point then it is with respect to GMT, not local time. | |
| Minute? | Return the minute as a number from 0 to 59. | |
| Month? | Returns the month number (1 through 12). | |
| Month? | Return the month as a number from 1 to 12. | |
| Passover? | Returns the date of Passover (on Dim: UDate) for the year specified or implied. | |
| Period? | Returns the period number (1 through 13). | |
| Second? | Return the second as a number from 0 to 59. | |
| SolarLongitude? | Returns the solar longitude (from 0 to 360 degrees) corresponding to the first argument. If the first argument is a UDate or DateTime point then it is with respect to GMT, not local time. | |
| UDate? | Return the universal date. | |
| UMonth? | Return the universal month. | |
| UPeriod? | Return the universal period. | |
| UQuarter? | Return the universal quarter. | |
| UTime? | Return the universal time | |
| UWeek? | Return the universal week. | |
| UYear? | Return the year as a four digit year. | |
| Week? | Returns the week number (1 through 52). | |
| YomKippur? | Returns the date (on Dim: UDate) for the year specified or implied. |
This module is used to convert a date, time, or month to another format as governed by valuetype. Dimensions of type UDate, UMonth, and UDT can use the {now} construct to obtain information about now on the current system. The following chart shows the permitted variations-
Table 9 - Supported Conversions within DTInfo()
| UDaTE | UMonth | UDT | UWeek | UQtr | UPeriod | Integer | UTime | |
| Day? | ok | ok | ||||||
| DayOfWeek? | ok | ok | ||||||
| Hour? | ok | ok | ||||||
| Minute? | ok | ok | ||||||
| Month? | ok | ok | ok | |||||
| Second? | ok | ok | ||||||
| UDate? | ok | ok | converts | |||||
| UMonth? | ok | ok | ok | |||||
| UPeriod? | ok | ok | ok | |||||
| UQuarter? | ok | ok | ok | |||||
| UTime? | ok | ok | ||||||
| UWeek? | ok | ok | ok | |||||
| UYear? | ok | ok | ok | ok | ok | ok |
| DTInfo(UDate:951201 UMonth?) | returns UMonth:9512 (this could also be done as UMonth:=UDate:951201) | |
| DTInfo(UDate:{now} UYear?) | returns the current year (this could also be done as UYear:=UDate:{now}) | |
| DTInfo(UDT:970101::11:24 Minutes::15) | returns 970101:11:30 | |
| DTInfo(UDate:970803 Month::+5 UMonth?) | returns UMonth:9801 | |
| DTInfo(UDate:{current} Month::1 Day::1 UDate?) | returns the first day of the current year | |
| (DTInfo(UMonth* Day::1) DTInfo(UMonth* Day::15) DTINfo(UMonth* Day::31))/UMonth:=UYear:{now} | returns a list of dates corresponding to the 1st, 15th, and last day of each month in the current year. | |
| DTInfo(UDate:{now} DayOfWeek::-1) | returns nearest date on or before today that is a Monday | |
| Enum(UYear:1990..2010 @EchoT(DTInfo(UYear* Month::11 Day::22 DayOfWeek::+4))) | Echos date of Thanksgiving between 1990 and 2010 | |
| DTInfo(UDate:11/2/07 Age::UDate:11/2/00) | Int:7 | |
| DTInfo(UDate:11/1/07 Age::UDate:11/2/00) | Int:6 (has not yet reached birthday) |