Database Functions |
  
|
Database Functions |
|
Referring to a specific column of a database
Before describing the functions of selecting values from databases, it is necessary to explain how you can refer to a specific column in a database.
The syntax for accessing a database column is as follows:
DB_name.column_name
For example, the base.column_1 entry means that you need to refer to the column_1 column in the base database.
Returning the number of records in a database
If necessary, you can return the number of records in a database using the DB_name.# expression in the Variable Editor.
List of functions
Following functions for working with databases are available in T-FLEX CAD:
•Functions for selecting values from internal databases
•Functions for selecting values from external databases
•Functions for working with ranges of cells in databases
Functions for selecting values from internal databases
rec(condition) - Get the number of record fulfilling the specified condition, where the condition is an expression that evaluates to true or false. The expression can contain references to database columns as operands.
For example:
rec(BASE.F1 > 10 && BASE.F2 < 5)
Such a function call means: searching for a record number in the internal BASE database that meets the following conditions: the value of the F1 column in this record must be greater than 10 and (&&) the value of the F2 column is less than 5. The search starts from the first record. If a record does not meet the specified condition, then the next record is taken. If there is no record in the database that meets this condition, then the function returns 0, otherwise - the number of the found record. The syntax of the condition is fully consistent with the syntax of an arithmetic expression. Instead of the "&&" operator, you can use the "," sign. Therefore, the function call can be written differently:
rec (BASE.F1 > 10, BASE.F2 < 5)
With this record, the function works faster because if the first condition is false, then the second condition is not evaluated. It is also recommended that when accessing the database in the first condition, use a column whose value is different for different records.
For example, consider a BASE database with columns F1 and F2:
F1 |
F2 |
1 |
6 |
2 |
6 |
3 |
6 |
4 |
6 |
5 |
6 |
Function call:
rec(BASE.F1> 4, BASE.F2 == 6)
will run faster than the following:
rec(BASE.F2 == 6, BASE.F1> 4)
frec(argument_1, argument_2, argument_3, argument_4) - Get the number of the record, the value in the specified column of which most closely matches the one specified in the call of this function, where
•argument_1 is the column in the database for which the search is performed (necessarily of real or integer type);
•argument_2 is the desired value;
•argument_3 is the search criterion. Possible values:
•0 - the nearest value is found;
•-1 - the nearest lower value is found;
•1 - the nearest higher value is found.
•argument_4 is a parameter indicating the ordering of data in the column being searched for. Possible values:
•0 - the values are arranged in an unordered manner and the search is carried out in all records of the base;
•1 - the column is ordered in ascending or descending order. As soon as the difference between the desired value and the value in the current column of the base is greater than in the previous one, the search stops.
The parameters argument_3 and argument_4 are optional. If they are not specified, then the default values are used.
•argument_3 = 0 (search for the nearest value);
•argument_4 = 0 (unordered column).
For example, frec(BASE.F1, 2.5, 1, 1)
This call means: get the number of the record for which the F1 column contains the nearest higher value than 2.5 (the values in the F1 column are ordered). For our example, the function will return 3.
If there is no record in the database that satisfies the search condition, the function returns 0. For example, frec(BASE.F1, 0.5, -1, 1).
val(record_number, database_column, offset) - Get the value of the column from the record with the specified number, where
•record_number - any arithmetic expression whose value is an integer,
•database_column - referring to the column,
•offset (optional) - the offset of the column number from which the value will be selected. The offset is added to the number of the column specified by the second parameter. The offset can be set by a variable, it can take both positive and negative values. If the offset value is 0, then the function refers to the column specified by the second parameter. The same will happen if the third parameter is not used.
Examples:
val(4, BASE.F2)
This call means: select the value from record 4 from the F2 column of the BASE database.
val(4, BASE.F2, 3)
This call means: select the value from record 4 from the column, the third to the right relative to the column F2 of the BASE database.
val(n, BASE.F2)
val(n<1?error("record_number < 1"):n,BASE.F2)
If there is no record with the specified number in the database, then the function displays the error message "Invalid record number".
Erroneous calls to the val function can be monitored without calling it. To do this, write the following line in the variable editor:
n<1 > > n>BASE.#? error("wrong number n"):val(n,BASE.F2),
where BASE.# - special designation of the number of records in the BASE database.
find(database_column, condition) - Function returns the value of the specified column from a record that satisfies the condition. If such a record does not exist, the function returns an error message Invalid record number.
This function is the union of the val() and rec() functions.
Examples:
find(BASE.A, BASE.F1 > 4)
find(BASE.A, BASE.F2 == 6 && BASE.F1 > 4)
Functions for selecting values from external databases
dbf(arg1, arg2, arg3) - Allows you to select data from external dBASE databases, where
•arg1 - the name of the database. The database name can be specified as a text constant, variable, or expression.
•arg2 - the name of the column from which to select. The column name can be specified as a text constant, variable, or expression.
•arg3 - selection condition. When setting a condition, you can use text constants, variables and expressions.
For example:
dbf("DBF_NAME", "COL1", "COL2 == 30")
This means: select a value from the DBF_NAME database from the COL1 column, provided that the value of the COL2 column is 30.
Examples:
dbf($NAME, "COL1", "COL2 <= {GRM_VAR}")
dbf($NAME, "COL1", "TEXT = \"GRM_TEXT\"")
dbf("<*>DBF_NAME", "COL1","COL2 > 30")
In the last example, the database name can be preceded by a sequence of characters <*>. It means that the database with the name DBF_NAME will be searched for in the same directory as the drawing. That is, when changing the path, you will not need to change the name of the database in the Variable Editor in all calls to the dbf () function.
It is recommended that when creating drawings related to external databases, specify the database name using the "<*>" character sequence. This will make it easier for you to work with these drawings later.
The filter condition when calling the dbf() function is a boolean expression that corresponds to the filter concept in a classic relational database. Condition (filter) syntax conforms to dBASE requirements. Its constituent parts can be database column names, constants, logical and arithmetic operations, dBASE functions. In addition, the filters can use T-FLEX CAD system variables, as well as Variable Editor logical operations. When variables are used in a filter, they are enclosed in curly braces.
The return type of the function corresponds to the type of the database column and can be either text or real.
Due to the slower process of retrieving information from an external database, use the dbf( ) function only in cases where it is impossible to use the database by reference based on the "* .xls" and "* .txt" formats.
The dbfwin() function has the same parameters as the dbf() function. This function is used for the correct reading of Russian-language texts (transcoding the text into Windows format).
mdb (arg1, arg2, arg3, arg4, arg5) - Allows you to select data from external databases in MDB format. where
•arg1 - the name of the database. The database name can be specified as a text constant, variable, or expression.
•arg2 - the name of the table in the database. Can be specified as a text constant, variable, or expression.
•arg3 - the name of the column from which to select. The column name can be specified as a text constant, variable, or expression.
•arg4 - selection condition. When setting a condition, you can use text constants, variables and expressions.
•arg5 - the value returned if no entry with the specified condition was found. The value can be either textual or real, which is defined by the user, and must correspond to the type of the variable. This parameter is optional.
For example:
mdb("C:\\Example\\T-FLEX_USER.mdb", "USER", "Name", "Code={code}")
or
mdb("T-FLEX_USER.mdb", "USER", "Name", "Code={code}", "Not found").
This means: select the value from the USER table of the T-FLEX_USER database from the Name column, provided that the value of the Code column is equal to the value of the code variable (in our case, 15). The second option adds a fifth parameter that returns the value "Not found" if there is no value 15 in the Code column. If the database file is located in the same folder with the T-FLEX file, then you do not need to specify the full path to the file.
It should be noted that the fourth operand of the function, which specifies the condition for selecting records, can be written in the form of an SQL query and must correspond to the "WHERE" clause of the "SELECT" command. If text variables are used when writing a condition, the expression will look like this:
mdb("С:\\T-FLEX_USER.mdb", "USER", "Name", "Position=\"{$Pos}\"").
Due to the slower process of obtaining information from an external database, use the mdb() function only in cases where it is impossible to use the database by reference based on the "* .xls" and "* .txt" formats.
Functions for working with ranges of cells in databases
Functions for working with ranges of cells should be used when working with databases requires functionality similar to the functionality of a spreadsheet, for example, in Excel.
db_sum(interval) - Calculates the value of the sum of cells in an interval.
db_mid(interval) - Calculates the average of the cells in an interval.
db_max(interval) - Calculates the maximum cell value in an interval.
db_min(interval) - Calculates the minimum cell value in an interval.
The interval can be set as follows:
1.Specifying the name of the database column. In the picture below, the sum and the average value for the column are calculated.
2.Specifying the name of the column and the initial line number. The picture below calculates the sum and the average for column "aa" starting from the second row.
3.Specifying the start and end of the line. In the picture below, the sum and average value from the 1st to the 4th row is calculated.
