Copyright © 2001, SNAP Innovation GmbH. All rights reserved.
The information contained in this document applies to:
- PrimeBase SQL Database Server 4.0, build #4009 and higher
- PrimeBase Virtual Machine 4.0, build #4009 and higher
Last major update to this document on March 2nd, 2001.
1. Introduction
2. Basic Elements
BOOLEAN, CHAR, CURSOR, DATE, DECIMAL, FLOAT, GENERIC, INTEGER, LONGBIN, LONGCHAR, MONEY, OBJNAME, REAL10, REAL12, SMFLOAT, SMINT, TIME, TIMESTAMP, TINYINT, UNICODE, VARBIN, VARCHAR
Integer, Decimal, Money, Floating-point, Boolean, Character String, Binary, Unicode, Date, Time, Datetime
Boolean Operators
Comparison Operators
Arithmetic Operators
String Concatenation Operator
Date and Time Calculations
Bit-wise Operators
Unary Operators
Data Type Conversions
Cursor-based Reference
Boolean Operators
Comparison Operators
Outer Join Operators
BETWEEN Operator
IN Operator
LIKE Comparison
IS NULL Test
Subqueries
Exists Test
Full Text Search
Arithmetic Operators
Data Type Conversion
Unary Negation
Basic Elements
Column References
Aggregate Functions
Generic Functions
Character Functions
Mathematical Functions
Set Operators (UNION, MINUS, INTERSECT)
SELECT Clause
FROM Clause
WHERE Clause
GROUP BY Clause
HAVING Clause
ORDER BY Clause
3. Data Manipulation Statements (SQL)
SELECT
FETCH
INSERT
UPDATE
DELETE
BEGIN
COMMIT
ROLLBACK
4. Appendices
This documentation describes the standard SQL commands of PrimeBase and additionally functions that can directly be used in SQL statements, such as aggregate functions, but also proprietary extensions such as mathematical functions, string functions, etc.
Further extensions such as CURSORs, functions related to them as well as all other aspects of the fundamental programming language of the PrimeBase system (PrimeBaseTalk, or short PBT) are described in the "PrimeBase Talk Programmer's Guide" (pbpbtpg.html).
In this document we refer to the PrimeBase SQL dialect as PrimeBaseTalk (PBT), also because the SQL specific/compliant parts of PrimeBase SQL are fully embedded into and available in PrimeBaseTalk.
For information on the Data Definition Language commands in PrimeBase see the "PrimeBase SQL Database Server Reference Manual" (pbdsrefman.html).
Many of the examples in reference manual make use of a sample database, called the Golfers database. The tables and columns of the Golfers database are explained in detail in Appendix A.
So if you want to try any of these examples yourself you may want to copy and paste the Create Script into a file execute them in the PrimeBase Automation Client (PBAC) or the PrimeBase SQL Database Server console using the EXECUTE FILE command:
EXECUTE FILE "golfersdb.sql" LOCATION "c:\"; <press ENTER> go <press ENTER>Also make sure you open the Golfers database in PBAC or the server console prior to trying any of the examples contained in this reference manual. You do this by issuing the following:
OPEN DATABASE Golfers; <press ENTER> go <press ENTER>
In PrimeBaseTalk a data type specification can optionally include a size and scale specification where appropriate. The first <int_expr> is the size and the second <int_expr> is the scale.
Each column or literal value has an associated datatype. The data types of the operands will determine the meaning of arithmetic operators and function calls. When operands of different types are combined in an arithmetic operation, one operand is converted automatically to the type with higher precedence. The precedence order from highest to lowest is: (DECIMAL, MONEY, REAL, INTEGER, WORD, BOOLEAN, DATE, TIME, DATETIME, VARCHAR, CHAR, OBJNAME, VARBIN, BIN, UNICODE)
Data types are used to define the base types of domains or table columns. They are also used to explicitly convert a value to a new type.
<data_type> ::= ( BOOLEAN | SMINT | ... ) [ '[' [ <int_expr> ] [ ',' [ <int_expr> ] ] ]
The following table lists the data types supported by PrimeBase databases, and PrimeBaseTalk programming language.
NOTES:
(1) REAL is an alternative, equivalent and PrimeBase-specific name for the SMFLOAT datatype. A system constant named $real does not exist though, but can be created if needed the same way as any other PrimeBaseTalk variable is created. Declaring variables (and functions) starting with a '$' character is however a PrimeBase-specific functionality as well.
(2) DATETIME is an alternative name for the TIMESTAMP datatype.
(3) When a size is specified for CHAR values, values are always exactly this size. CHAR values are space padded in order to fill the required length. Trailing spaces are never significant in comparisons. If no size is specified a CHAR value is the same as a VARCHAR value of unspecified size.
(4) Size, in the case of DECIMAL and MONEY values, is the number of digits including one for the sign. For example, a DECIMAL[10,2] value has a maximum of 9 digits. If not specified, the maximum of 300 is used.
(5) Scale is the maximum number of digits after the decimal point. Excessive digits are truncated, e.g. DECIMAL[8,3] 123.4567 -> 123.456, and DECIMAL[8,3] -123.4567 -> -123.458 . If no scale is specified, the maximum of 60 is used.
(6) VARBIN, VARCHAR and UNICODE may have any size from 0 to 62 Kbytes. If size is not specified, the size is 62 Kbytes.
(7) TINYINT is a PrimeBase-specific extension of the Data Access Language (DAL) standard.
(8) The datatypes REAL10 and REAL12 are supported by PrimeBase on MC68K MacOS only. REAL10 and REAL12 are PrimeBase-specific extensions of the Data Access Language (DAL) standard.
The following data types are supported by PrimeBaseTalk only and can't be used for database columns.
Literals represent a constant value of a certain type. The characters that make up a single literal may not be separated by space, tab or end of line characters. Literal values may be placed directly in expressions and queries in PrimeBaseTalk programs.
The following are literal values and the types recognized by PrimeBaseTalk:
NOTES:
(1) A single quote can be placed in a string delimited by single quotes by placing two single quotes directly next to each other, for example: 'Jack said ''Hi!'''. The same applies to double quotes placed in a double quote delimited string.
(2) Binary strings should contain an even number of Hex digits.
(3) Use \uXXXX sequences for UNICODE characters that cannot be represented in the bit 8 character set. XXXX is the hexadecimal unicode character number. \u2122 for example is the copyright character. See the file setup/unicode/UnicodeData.txt for a complete listing.
An expression is evaluated by PrimeBaseTalk at runtime to produce a single value of a particular data type. The terms <int_expr>, <char_expr> and <bool_expr> are all expressions (in general, <expression>, below).
When expression with multiple operators are evaluated, the precedence determines the order of evaluation. Use left and right parenthesis to change the predefined evaluation order. The following table lists all operators with descending precedence.
Operators Result Datatype Comments {} () [] -> . : GENERIC column group, parenthesis, datatype size, column indicator, variable indicator ~ + - INT, SMINT, TINYINT unary operators: bit-wise negate, affirmation, numeric negation data type GENERIC data type conversion * / % INT, FLOAT, DECIMAL multiplication, division, remainder + - & ^ | GENERIC addition and concatenation, numeric subtraction, bit-wise AND, bit-wise exclusive-OR, bit-wise OR IS NULL BOOLEAN null test < <= > >= = == != <> BOOLEAN less, less or equal, greater, greater or equal, equal, not equal LIKE BETWEEN IN BOOLEAN like pattern matching, range test, in list test NOT BOOLEAN boolean negate AND BOOLEAN boolean AND OR BOOLEAN boolean OR , GENERIC comma
The operators AND, OR and NOT operate on BOOLEAN values, and return a BOOLEAN value.
Note that, unlike for example in the C programming language, both sides (operands) of the operators AND and OR are evaluated regardless of the outcome of the evaluation of the first operand.
<expression> ::= <boolean_term> | <expression> OR <boolean_term> <boolean_term> ::= <boolean_factor> | <boolean_term> AND <boolean_factor> <boolean_factor> ::= [ NOT ] <expr_predicate>
 |
Example
BOOLEAN initialized = $true;
BOOLEAN printed = $false;
IF ( initialized AND NOT printed )
{
PRINT "Now it is being printed.";
}
ELSE
{
PRINT "ERROR: Can't print, it probably has not " +
"been initialized yet.";
}
Screen output:
Now it is being printed.
|
The comparison operators and the "null-test" operator (IS NULL), all produce BOOLEAN values. Arguments may be values of any data type (as of PrimeBase 3.5 also CURSOR).
In comparisons, if one (or both) of the operands are $null, the result is the BOOLEAN value $maybe.
<expr_predicate> ::= <value_expr> | <value_expr> <comp_op> <value_expr> | <value_expr> IS [ NOT ] NULL <comp_op> ::= '=' | '<>' | '<' | '>' | '<=' | '>=' | '==' | '!='
|
Example
BOOLEAN itIsRaining = $true;
IF ( itIsRaining == $true )
{
PRINT "Too bad, it is raining.";
}
ELSE
{
PRINT "Hey, it is not raining.";
}
/* Or equivalent: */
IF ( itIsRaining )
{
PRINT "Too bad, it is raining.";
}
ELSE
{
PRINT "Hey, it is not raining.";
}
/* And a different example: */
INTEGER anIntValue = 10;
IF ( anIntValue != 10 )
{
PRINT "The value has not been properly assigned " +
"to the variable!";
}
ELSE
{
PRINT "The value has been properly assigned to the " +
"variable.";
}
Screen output:
Too bad, it is raining.
Too bad, it is raining.
The value has been properly assigned to the variable.
|
 |
WarningNote that trailing spaces are ignored when comparing strings, meaning that 'a ' == 'a ' evaluates to boolean true. |
The following arithmetic operations are supported by PrimeBaseTalk expressions: addition (+), subtraction (-), multiplication (*), division (/) and remainder (%).
<value_expr> ::= <expr_term> | <value_expr> ( '+' | '-' '&' | '^' | '|') <expr_term> <expr_term> ::= <expr_conversion> | <expr_term> ( '*' | '/' | '%' ) <expr_conversion>
|
Example
PRINT "The remainder of 10 / 3 is",
VARCHAR( 10 % 3 );
Screen Output:
The remainder of 10 / 3 is 1
|
The addition (+) operator may also be applied to string values. For operands of type CHAR, VARCHAR, LONGCHAR, UNICODE. BIN, LONGBIN the character string values are concatenated and the result has the same data type as the operands.
Also see built-in server function CONCAT().
|
ExamplePRINT "Hello" + " " + "world" + "!"; Screen Output: Hello world! |
<datetime_sum> ::= [ DATETIME ] <datetime_exp> '+' [ INT ] <int_expr>
<date_sum> ::= [ DATE ] <date_expr> '+' [ INT ] <int_expr>
<time_sum> ::= [ TIME ] <time_expr> '+' [ INT ] <int_expr>
<datetime_diff> ::= [ DATETIME ] <datetime_expr> '-' [ DATETIME ] <datetime_expr>
<date_diff> ::= [ DATE ] <date_expr> '-' [ DATE ] <date_expr>
<time_diff> ::= [ TIME ] <time_expr> '-' [ TIME ] <time_expr>
The addition (+) operator can be used for date and time calculations.
When the first operand has a DATETIME data type and the second has a INT data type, then the second operand is interpreted as a timescale value in seconds and the result is a DATETIME value increased by <int_expr>. <int_expr> can be a negative value to calculate a datetime in the past.
When the first operand has a DATE data type and the second has a INT data type, then the second operand is interpreted as a timescale value in days and the result is a DATE value increased by <int_expr>.
When the first operand has a TIME data type and the second has a INT data type, then the second operand is interpreted as a timescale value in seconds and the result is a TIME value increased by <int_expr>.
Example:
DATETIME time_in_one_hour = $now() + (60 * 60);
The subtraction (-) operator can be used to calculate date and time difference.
When both operands of a substraction are of type DATETIME or TIME, then the result is the time difference in seconds.
When both operands of a substraction are of type DATE, then the result is the time difference in days.
The bit-wise operations are those familiar to most programmers: bit-wise AND ( & ), bit-wise OR ( | ), bit-wise exclusive-OR ( ^ ).
|
ExamplePRINT "255 & 127 =", VARCHAR( 255 & 127 ); Screen Output: 255 & 128 = 127 |
The unary operators supported by PrimeBaseTalk are unary-affirmation (+), unary-negation (-), and unary bitwise NOT (~).
Permissible operands are all types of INTEGER values (INT and SMINT).
<expr_factor> ::= [ '+' | '-' | '~' ] <expr_primary>
<expr_conversion> ::= [ <data_type> ] <expr_factor><data_type> may specify size and scale as described for the various data types.
PrimeBaseTalk supports extensive data type conversion. When conversion to and from character string that are applied to literals and program variables, the format is controlled by the various Format Control Variables mentioned in Appendix A.1 Format Control Variables.
You can set the initial value of the format control variable with the 'Environment Editor' application.
String Conversion Control Variables Data types Client program Variable Environment Variable DATE, DATETIME $month 380 DATE, DATETIME $day 381 DATE, DATETIME $datefmt 382 TIME, DATETIME $ampm 383 TIME $timefmt 384 DATETIME $tsfmt 385 DECIMAL $decfmt 386 MONEY $moneyfmt 387 CHAR, VARCHAR $charset 388 UNICODE $unicodemap 389
If two data type conversions are to take place directly one after the other, the second data type conversion has to be put in brackets, otherwise an error results.
|
ExamplePRINT TINYINT ( INTEGER '10' ); Screen Output: 10 |
|
ExampleINTEGER personalID = 10; VARCHAR personalIDString = VARCHAR personalID; PRINT "The ID of this person is " + personalIDString; Screen Output: The ID of this person is 10 |
|
ExampleINTEGER a = 123; INTEGER b = 456; VARCHAR c = VARCHAR( b / a ); PRINT "b / a = " + c; PRINT "b / a + c = " + VARCHAR( INTEGER c + INTEGER c ); Screen output: b / a = 3 b / a + c = 6 |
A CURSOR based reference refers to values of the current row of a CURSOR rowset. The column may be specified using the column alias, an INTEGER literal representing the column ordinal number or a variable containing one of these two.
If the variable, <var_name>, is of type INTEGER it is the column ordinal number, and if it is of type OBJNAME or VARCHAR it is the column alias.
According to the DAL standard unqualified identifiers (like <column_alias> in the first option of <cursor_based_column_ref>) are first interpreted as DAL variables in the current scope, and then as a CURSOR based reference.
Note: The error message may be misleading here because the last thing the interpreter does is try to interpret the identifier as a column of $cursor.
<cursor_based_column_ref> ::= [ [ <cursor> ] '->' ] <column_alias> | [ <cursor> ] '->' <int_literal> | [ <cursor> ] '->' ':' <var_name>
A search condition is used to specify which rows should be selected from a set of tables. A search condition is placed in the WHERE Clause in a query specification, and may also appear with sub-query in the SELECT Clause.
<search_condition> ::= <srch_bool_term> | <search_condition> OR <srch_bool_term> <srch_bool_term> ::= <srch_bool_factor> | <srch_bool_term> AND <srch_bool_factor> <srch_bool_factor> ::= [ NOT ] <srch_predicate> <srch_predicate> ::= <srch_comparison> | <srch_outer_join> | <srch_between> | <srch_in> | <srch_like> | <srch_is_null> | <srch_quantified> | <srch_full_text> | <srch_exists> | <srch_expr>
|
Example/* ** Give us all "Pro"fessionals with earnings of more than ** $4999 */ SELECT FirstNames, Surname FROM Golfers WHERE ( Status == "Pro" ) AND ( Earnings > MONEY 4999 ); PRINTALL; Screen output: Buzz Bee Humsdee Dumsdee |
<srch_comparison> ::= <srch_expr> <comp_op> ([ * ] <srch_expr> | <subquery> ) <comp_op> ::= '=' | '<>' | '<' | '>' | '<=' | '>=' | '==' | '!='When a <srch_comparison> is performed on columns that are based on domains with collation sequences, it uses the collation sequence key weights in the comparision operation.
|
Example/* ** Give us all golfers who didn't earn exactly $10,999.99 */ SELECT FirstNames, Surname FROM Golfers WHERE Earnings != MONEY 10999.99; PRINTALL; Screen output: Buzz Bee Tumble Dry Fish Head |
<srch_outer_join> ::= <srch_expr> <outer_join_op> <srch_expr> <outer_join_op> ::= '/=' | '/=\' | '=\' | '*=' | '*=*' | '=*'The outer join operators supported by PrimeBaseTalk are left-outer (/= or *=), right-outer (=\ or =*), and symmetric-outer join (/=\ or *=*).
In a LEFT OUTER JOIN, NULL values are inserted in place of columns from the RIGHT table where no matching value from the LEFT table was found.
In a RIGHT OUTER JOIN, NULL values are inserted in place of columns from the LEFT table where no matching value from the RIGHT table was found.
In a SYMMETRIC OUTER JOIN, NULL values are inserted in place of columns from both tables where no matching value from any table was found.
Alternatively an outer join can be defined in the <from_clause> ("LEFT OUTER JOIN", "RIGHT OUTER JOIN").
The asterisk character '*' is used to indicate that the boolan value TRUE is returned by the operation when one of the operand expressions is null.
When you have columns with NULL values and you apply a search condition to that column, rows that have a NULL values in the searched column are removed from the result. When you want to keep these rows in the result you can can set the '*' indicator in after the operator. This indicator is especially useful for search condition on results of outer join operations. The outer join operation can insert NULL values in columns even when NULL values are not permitted in the base tables. Normal search conditions would remove these rows from the result set.
|
Example/* ** Give us all golfers who didn't earn exactly $10,999.99 */ SELECT FirstNames, Surname FROM Golfers WHERE Earnings != MONEY 10999.99; PRINTALL; Screen output: Buzz Bee Tumble Dry Fish Head |
<srch_between> ::= <srch_expr> [ NOT ] BETWEEN [ * ] <srch_expr> AND <srch_expr>
<srch_between> returns all rows which are greater or equal to the second expression, and less than or equal to the third expression.
|
Example/* ** Give us all user who earned between $3700 and $10999 as of yet */ SELECT FirstNames, Surname FROM Golfers WHERE Earnings BETWEEN "$3700" AND "$10999"; PRINTALL; Screen output: Buzz Bee Tumble Dry |
<srch_in> ::= <srch_expr> [ NOT ] IN ( '(' <value_list> ')' | <subquery> ) <value_list> ::= <value_expr> { ',' <value_expr> }
|
Example/* ** Give us all (well, not really) european golfers */ SELECT FirstNames, Surname FROM Golfers WHERE Nationality IN ( "french", "german", "dutch", "british" ); PRINTALL; Screen output: Buzz Bee Tumble Dry Humsdee Dumsdee |
<srch_like> ::= <srch_expr> [ NOT ] LIKE [ * ] <pattern> [ ESCAPE <escape> ]
<pattern> is a string consisting of characters that have to match the characters of <srch_expr>.
The characters '%' or '*' match any characters, including empty strings.
The character '?' matches any single character.
ESCAPE is used to enable searching of the characters that are used as wildcards ('%','_','*','?'). When a character is preceded by the <escape> character, it will be used literally.
The LIKE test is implemented for columns of type CHAR, VARCHAR, LONGCHAR and UNICODE. When a LIKE test is performed on columns that are based on domains with collation sequences, it uses the collation sequence keys to match the pattern.
For common search operations in larger tables, an index should exist on the search column and at least the first 2 or 3 characters should be provided as a search pattern.
When the LIKE search is performed on the words of a full text indexed column the matching is within the words boundaries.
|
Example/* ** Give us all golfers whose surnames end with "ee" */ SELECT FirstNames, Surname FROM Golfers WHERE Surname LIKE "%ee"; PRINTALL; Screen output: Buzz Bee Humsdee Dumsdee |
|
Example/* Find products where the name starts with 'A10*8' */ SELECT * FROM products WHERE .name LIKE 'A10\*8%' ESCAPE '\'; |
<srch_is_null> ::= <srch_expr> IS [ NOT ] NULL
|
Example/* ** Give us all golfers whose SurName column has been set ** to some value (as opposed to be $null) */ SELECT FirstNames, Surname FROM Golfers WHERE FirstNames IS NOT NULL; PRINTALL; Screen output: Buzz Bee Tumble Dry Humsdee Dumsdee Fish Head |
<srch_quantified> ::= <srch_expr> <comp_op> [ ALL | SOME | ANY ] <subquery>
<subquery> ::= '(' <query_spec> ')'
When a <subquery> is used in the <where_clause> the <select_list> of the query has a single column, that is compared with an other expression. The <where_clause> of the <subquery> can have one or more <join_condition> that references columns from the outer query. In this case the subquery is evaluated for every distinct value of the outer reference.
|
Example
/*
** Give us the oldest golfer
*/
SELECT FirstNames, Surname
FROM Golfers
WHERE DateOfBirth ==
(
SELECT MIN( DateOfBirth )
FROM Golfers
);
PRINTALL;
Screen output:
Fish Head
|
<srch_exists> ::= ( [ NOT ] EXISTS | FOR ALL ) <subquery>
<srch_full_text> ::= <text_column_ref> '=' [ ALL | ANY | SOME ] <char_literal> <char_literal> is the text sequence of words that are seperated by non-word characters. <text_column_ref> is a <column_ref> of a columns that has a full text index.
The ALL keyword indicates that all words of the <char_literal> have to be found in <column_ref>.
The ANY or SOME keyword indicates that at least one word of the <char_literal> has to be found in <column_ref>. Use the LOCATEWORDS() function to find the text offset in the text of <column_ref>.
The following arithmetic operations are supported by search expressions: addition (+), subtraction (-), multiplication (*), division (/) and remainder (%).
Addition (+) and subtraction (-) are also used for Date and Time Calculations .The bit-wise operations are: bit-wise AND ( & ), bit-wise OR ( | ), bit-wise exclusive-OR ( ^ ).
<srch_expr> ::= <srch_term> | <srch_expr> ( '+' | '-' | '&' | '^' | '|' ) <srch_term> <srch_term> ::= <srch_conversion> | <srch_term> ( '*' | '/' | '%' ) <srch_conversion>
<srch_conversion> ::= [ <data_type> ] <srch_factor>
Convert <srch_factor> to the specified data type. Data type conversion is not done automatically in a <search_condition> as it is in an <expression> (literal values are converted as required, however). The following groups may be combined without error in a search condition:
- Integers: TINYINT, SMALLINT and INTEGER.
- Decimals: DECIMAL and MONEY
- Floating point numbers: SMALLFLOAT, REAL, DOUBLE, etc.
- Character values: CHAR and VARCHAR.
Within these groups no conversion is explicitly required, however if values of different groups or values of type not mentioned above are to be combined (by operators) in a <search_condition> they must be explicitly converted.
When a string is converted in an SQL statement that is send to the server, database system variables are used to control the formatting. The TO_CHAR() function can be used in SQL statements to convert to variable formats. You can set the initial value of the format control variable with the Environment Editor
String Conversion Control Variables Data types Master Database Variable Environment Variable DATE, DATETIME MonthFormat 380 DATE, DATETIME DayFormat 381 DATE, DATETIME DateFormat 382 TIME, DATETIME AMPMFormat 383 TIME TimeFormat 384 DATETIME TimeStampFormat 385 DECIMAL DecimalFormat 386 MONEY MoneyFormat 387 CHAR, VARCHAR CharSet 388 UNICODE UnicodeSequence 389
<srch_factor> ::= [ '+' | '-' | '~' ] <srch_primary>
<srch_primary> ::= '(' <search_condition> ')' | <set_function_spec> | <column_ref> | <cursor_based_column_ref> | <literal> | <variable> | <function_call> | <composite_column>Note: <set_function_spec> is not allowed in a <where_clause> of a <query_spec> (see below).
<variable> is the name of a previously declared program variable or function argument.
<column_ref> ::= [ <table_alias> '.' | '.' ] <column_name> <composite_column> ::= '{' <search_condition> { ',' <search_condition> } '}'<column_name> is the name of a column from the <table_spec> listed in the <from_clause>. Valid names are simple and composite column names of database tables and <column_alias> names used in the <select_items> of <table_spec>. When <column_name> is not qualified by <table_alias>. or '.' PrimeBaseTalk attempts to insert the value contained in a variable with the same name first. Only when a variable with that name does not exist, the given name is assumed to be the column name itself. Because of this behaviour it is highly recommended to always use at least the '.' qualifier when specifying columns in database statements.
|
WarningVARCHAR Earnings = "1"; SELECT SurName, Earnings FROM Golfers; PRINT "First Results:"; PRINTALL; PRINT ""; SELECT SurName, .Earnings FROM Golfers; PRINT "Second Results:"; PRINTALL; Screen output: First Results: Bee 1 Dry 1 Dumsdee 1 Head 1 Second Results: Bee $ 5,000.0 Dry $ 3,999.0 Dumsdee $ 10,999.99 Head $ 3,520.0 Note: Consider the difference of 'First Results' and 'Second Results', even though the only difference between the two SELECT statements is a single '.'. |
Aggregate functions can be used in the <select_list> to return aggregated calculations for the whole rowset or for groups of a rowset. When aggregate functions are listed beside column references <column_ref> in the <select_list>, all this <column_ref> have to be listed in the <group_by_clause> too.
Aggregate functions can be used in <having_clause> to apply conditions to groups of rows.
<set_function_spec> ::= COUNT '(' '*' ')' | <distinct_set_function> | <all_set_function> <distinct_set_function> ::= ( AVG | MAX | MIN | SUM | COUNT ) '(' DISTINCT <column_ref> ')' <all_set_function> ::= ( AVG | MAX | MIN | SUM ) '(' [ ALL ] <srch_expr> ')'
When multiple <select_item> with <distinct_set_function> are used, they all have to reference the same <column_ref>.
When you apply an <all_set_function> to the rowset that is a result of a join of two tables A and B, the result may not be what you expect, since the join operation can build duplicates of the column values that you are computing.
When you need totals from two or more tables, you have to build the totals separately, before the tables are joined. Build separate views or table expressions <subquery> that summarizes the data and groups it by the referenced columns. Then join the views or table expressions to combine the results in the <select_list>.
When you apply an <all_set_function> like SUM(ALL column) or AVG(ALL column) to the result set of a UNION, INTERSECT or MINUS <set_operator> the totals are build from the first operand. In case of the UNION ALL, the totals are build from both operands.
|
Example/* ** Give us the oldest golfer */ SELECT FirstNames, Surname FROM Golfers WHERE DateOfBirth == ( SELECT MIN( DateOfBirth ) FROM Golfers ); PRINTALL; Screen output: Fish Head |
|
Example/* ** Give us the average earnings of all golfers */ SELECT AVG( Earnings ) FROM Golfers; PRINTALL; Screen output: $ 5,879.74 |
When arguments have different types, sizes or scales, the return type will have the larger types, sizes and scales.
When <cond> evaluates to TRUE, <a> is returned, else <b> is returned.
The function compares the arguments and returns the greatest value.
The function compares the arguments and returns the least value.
When <a> is not NULL <a> is returned, else <b> is returned.
Returns the user name for the current connection to the database server.
Returns the current date and time of the host clock where the database engine is executed. When a database column has a NOW default value, the current time is assigned from the host clock at insert time. When you update that column to set the current time, the NOW() function should be used to be sure that the same clock is used.
Some of the string functions return result types that are as large as their arguments. For VARCHAR results, the maximum length is 61400 characters. Return values that exceed the limit are truncated without warning.
When these functions are used in a select column list, beware that the total row size is also limited to 61400 bytes. To avoid the overflow in the row size and to be not surprized about the result type, you should explicitly type cast the result.
Example:
SELECT id, VARCHAR[20000] CONCAT(text_a, text_b) FROM table_a;In the next releases of PrimeBase functions that currently can only return VARCHAR results will return the data type of the first argument.
Function names are case insensitive. Function arguments are automatically converted to the required data type.
Returns <c> as the binary equivalent INTEGER number.
Returns the lower 8 bits of <n> as a binary equivalent character of the host character set.
Concatenates two strings <a> and <b>. When an argument is NULL, it is replaced with an empty string. It is a replacement for the '+' operator, when a domain rule prevents the '+' operator or NULL values should be ignored.
Returns <string> with the first character of each word in upper case and all other alphabet characters in lower case. Words are delimited by characters that are not alphanumeric.
Returns <string> with all alphabet characters in lower case.
Returns <string> with all alphabet characters in upper case.
Returns the words of a column.
The function is used to access the full text index word list. Use it to build full text search conditions and for listing words from the words index directory in a SELECT column list. Can currently only be used for columns that have a full text index defined for them. When the database_column is of type UNICODE, the return type is UNICODE[243], otherwise VARCHAR[243].
You can use all relational operators (==, !=, <=,<, >, >=, [NOT] BETWEEN, IS [NOT] NULL, [NOT] LIKE) to search the full text index word list.
Example:
List words that are greater than 'A' and less than 'C' and that end with 'er':
SELECT distinct WORDS(.content) AS Words FROM fulltext WHERE WORDS(.content) > 'A' AND WORDS(.content) LIKE '%er' AND WORDS(.content) < 'C';
Return the length of <string> in character units. UNICODE characters require 2 bytes per character. For other types the size is returned in byte units.
Returns the string to the left of the given pattern.
<string> - the string to be searched.
<pattern> - a pattern to be found in <string>.
<count> - an optional argument. If included, a positive <count> value causes the search of <string> to continue until <count> occurrences have been found, starting from the left side of the string. A negative <count> value causes the search to begin at the end and proceed to the start of <string>.
Returns the string to the right of the pattern.
<string> - the string to be searched.
<pattern> - a pattern to be found in <string>.
<count> - an optional argument. If included, a positive <count> value causes the search of <string> to continue until <count> occurrences have been found, starting from the left side of the string. A negative <count> value causes the search to begin at the end and proceed to the start of <string>.
Returns a substring extracted from a given string value.
<string> - a VARCHAR, CHAR or LONGCHAR value.
<position> - an INTEGER specifying the starting position within <string>. The first character of <string> has a position of 1. If <position< is negative, then it specifies a position relative to the end of <string>. The last character of <string> is designated by the value -1.
The first character of <string> has a position of 1. If <position> is negative, then it specifies a position relative to the end of <string>. The last character of <string> has a position of -1.
<length> - this argument is optional, and specifies the number of characters to extract. If length is negative, then position specifies the last character of the extracted string, and extraction proceeds to the left instead of to the right. If omitted, the function extracts the entire remainder of <string>.
Returns the offset of the pattern in the string. If the pattern could not be found, LOCATE() returns 0.
<string> - a VARCHAR, CHAR or LONGCHAR argument to be searched. When the string argument is a column that has a collation sequence, the pattern will be searched with keys of the collation, to do case insensitive and/or ignores accent matching.
<pattern> - a VARCHAR pattern to be found in <string>.
<count> - an optional argument. If included, a positive <count> value causes the search of <string> to continue until <count> occurrences have been found, starting from the right side of the string. A negative <count> value causes the search to begin at the end of <string>, and proceed from right to left.
Example:
SELECT .pathname + .filename, LOCATE(.content, 'good') FROM fulltext WHERE WORDS(.content) == 'good'; PRINTALL;
Returns the offset of the pattern in the string. If the pattern could not be found, LOCATEWORDS() returns 0.
Due in the next release!
<string> - a VARCHAR, CHAR or LONGCHAR argument to be searched. When the string argument is a column that has a collation sequence, the pattern will be searched with keys of the collation, to do case insensitive and/or ignores accent matching.
<pattern> - a VARCHAR pattern to be found in <string>.
<max_dist> - an optional argument and specifies the maximum distance between words in count of characters.
When the words of <pattern> are located in <string>, the same order of words has to be found. Non-alphanumeric characters define the word boundaries and are ignored in the comparison. Accent characters like 'Ü' will match HTML entities of the form 'Ü'.
The LOCATEWORDS function can be used to filter the set of matching rows that was returned by a full text search with conditions like <column_ref> = ALL <char_literal>. This search returns rows where all the words of the <char_literal> are contained somewhere in the text of <column_ref>.
Returns <string> with every occurrence of <search> replaced by <replacement>. The default value for <replacement> is an empty string. When it is omitted or NULL, all occurrences of <search> are removed. If <search> is NULL, <string> is returned.
Returns <string> with all occurrences of each character in <from> replaced by character in <to> that is in the same position as in <from>. When <from> is longer than <to>, then char of <from> that have not corresponding character in <to> are removed.
Returns a concatenation of sections of <text> where the nesting of <XX> </XX> tags matches the <search_path> argument.
<text> contains html or xml text that can be parsed by the function.
<search_path> is a list of tag names separated by '/'. The matching is performed using the wildcard syntax of the LIKE operator.
<escape> is used to enable searching of the characters that a used of wildcards ('%','_','*','?'). When a character is preceded with the <escape> character, it will be used literally.
This function may be changed in future releases to cover a subset of the XQL language. See http://www.w3.org/TandS/QL/QL98/pp/xql.html.
Returns a formated string that is converted from <num> with the specified <format>. The content of <format> depends on the data type of <num>. The format string has the notation of Format Control Variables:
Datatype Notation Example DATETIME $tsfmt "DDD, DD. MMM HH:MM" DATE $datefmt "MM/DD/YYYY" TIME $timefmt "HH:MM:SS:hu" DECIMAL $decfmt "[9999].9" MONEY $moneyfmt "$[9999].9" SMALLINT $format "%3.3d km/h" INTEGER $format "%05d" FLOAT $format "%10.2f"
Returns <string> with all characters of <set> removed from the left.
Starting with first character the function skips all characters that are in <set> until a character is not found in <set>.
<set> is optional and defaults to a single blank.
Returns <string> with all characters of <set> removed from the right.
Starting with last character the function skips all characters from the end, that are in <set> until a character is not found in <set>.
<set> is optional and defaults to a single blank.
Returns a left padded <string> with a specified <length>.
When <string> is shorter than <length> characters of <padding> are repeatedly appended until the string is <length> characters long.
When <string> is longer than <length>, <string> is truncated.
Returns a right padded <string> with a specified <length>.
When <string> is shorter than <length> characters of <padding> are repeatedly appended until the string is <length> characters long.
When <string> is longer than <length>, <string> is truncated.
Returns a UNICODE <string> normalized to the requested <form>.
Form: Description: 0 (F) Canonical Decomposition 1 (KD) Compatibility Decomposition 2 (C) Canonical Decomposition, followed by Canonical Composition 3 (KC) Compatibility Decomposition, followed by Canonical CompositionFor more details see http://www.UNICODE.org/UNICODE/reports/tr15/.
Returns the absolute value of the numeric argument <x>. The returned data type is the same as the argument type.
Returns the smallest INTEGER not less than <x>.
Returns the cosine of argument <x>. (An angle expressed in radians.)
Returns the hyperbolic cosine of argument <x> as FLOAT.
Returns e to the <x>'th power. e is the base of natural logarithms.
Returns the largest INTEGER value not larger than <x> as FLOAT.
Returns the natural logarithm of argument <x>.
Returns the base-10 logarithm of argument <x>.
Returns <x> raised to the power of <y>.
For positive <m> it returns <x> rounded to <m> places right of the DECIMAL point.
For negative <m> it returns <x> round off digits left of the DECIMAL point.
<m> is optional and a default of 0 is used.
Returns 1 when <x> is greater than 0.
Returns -1 when <x> is less than 0.
Returns 0 when <x> is equal 0.
The data type of the result is the same as the argument type.
Returns the sine of argument <x>.
Returns the hyperbolic sine of argument <x>.
Returns the square root of <x>.
Returns the tangent of <x> in radians.
Returns the hyperbolic tangent of <x>.
For positive <m> it returns <x> truncated to <m> places right of the DECIMAL point.
For negative <m> it returns <x> truncate <m> digits left of the DECIMAL point.
<m> is optional and a default of 0 is used.
Returns the principal value of the arc cosine of argument <x>.
<x> must be in a range -1 to +1.
Returns the principal value of the arc sine of argument <x>.
Returns the principal value of the arc tangent of argument <x>.
When <x> is between -1 and +1 it returns the arc sin of <x> in the range -pi/2 to +pi/2
Returns the principal value of the arc tangent of argument <x> / <y>.
It uses the signs of both arguments to determine the quadrant of the return value.
Use a query specification to select a set of data from the database.
<query_spec> ::= <table_expr> | <query_spec> <set_operator> <table_expr> <table_expr> ::= <select_clause> <from_clause> [ <where_clause> ] [ <group_by_clause> ] [ <having_clause> ] <set_operator> ::= [UNION [ ALL ] | INTERSECT | MINUS]
A <table_expr> consists of a FROM clause and an optional WHERE, GROUP BY and HAVING clauses.
<set_operator> is used to merge and intersect multiple <table_expr>. The <table_expr> must have the same cardinality (number of columns) and the data types of the columns must be the same for the columns in the same position of the <select_list>. Use <datatype> conversions in the <select_item> to build matching rowsets. All <set_operator> have the same precedence.
<set_operator> ::= [UNION [ ALL ] | INTERSECT | MINUS]
UNION is used to merge the rows of multiple <table_expr> into one rowset. A rowset that contains rows that are included in the first <table_expr> OR the second <table_expr>.
INTERSECT is used to build the intersection of multiple <table_expr> in one rowset. A rowset that contains rows that are included in the first <table_expr> AND the second <table_expr>.
MINUS is used to build a relational difference. A rowset that contains rows form the first <table_expr> and that are not included in the second <table_expr>.
Use the SELECT Clause to select data from tables.
<select_clause> ::= SELECT [ ALL | DISTINCT ] <select_list> <select_list> ::= <select_item> { ',' <select_item> } <select_item> ::= '*' | ( <table_alias> '.' '*' ) | <search_condition> [ [ AS ] <column_alias> ]
The <select_list> specifies which columns, constant values or expressions are to be selected from the database tables. The tables are specified in the FROM clause described below.
Columns in the SELECT list may be given an alias name, specified using the AS clause. The alias name can be used later to reference the column in the rowset. The '*' means all columns in all tables or all columns of a table if used in the form: <table_alias>.* .
The DISTINCT indicator specifies that duplicate rows will be removed from the result set to form a relational table. The PrimeBase SQL Database Server removes duplicate rows by default. In most cases queries can be processed faster when duplicate rows are removed before join operations are performed, rows are sorted and transported over the communication links.
The ALL indicator specifies that duplicate rows will remain in the result set. When duplicate rows are needed in result sets the ALL indicator has to be used.
<table_alias> refers to a table specified in the FROM clause below.
<column_alias> is the new name for a simple or calculated column. When no <column_alias> is specified and the <search_condition> is a <column_ref>, the columns are named as the columns of the base table. <column_alias> should be used to uniquely name columns of <select_clause>. When multiple <table_spec> are listed in the <from_clause> duplicate names have to be renamed. When the <search_condition> is a <composite_column>, the columns are named as the component columns of the composite colum. When the <search_condition> is a calculated expression the columns are named "expr" + n, where n is the column position in the <select_list>.
|
Example/* ** Give us golfer Name and Earnings */ SELECT A.Name, A.Earnings FROM Golfers A; PRINTALL; Screen output: Bee Buzz $ 5,000.0 Dry Tumble $ 3,999.0 Dumsdee Humsdee $ 10,999.99 Head Fish $ 3,520.0 |
Use the FROM Clause to specify which database tables data is to be selected from. An alias may be specified for each table.
When multiple <table_spec> are listed in the <from_clause>, the cartesian product of the rows from all tables is build.
<from_clause> ::= FROM <table_spec> { ',' <table_spec> } <table_spec> ::= <table_ref> [ [ AS ] <table_alias> ] | <view_ref> [ AS ] <table_alias> | <subquery> [ AS ] <table_alias> | <outer_join> [ AS ] <table_alias> | <outer_join> ::= <table_ref> [ LEFT | RIGHT ] OUTER JOIN <table_ref> ON <join_condition> [ AND <search_condition> ] <join_condition> ::= <column_ref> = <column_ref> <table_ref> ::= [ <database_alias> '!' ] [ <creator_name> '.' ] <table_name> | ':' <object_name> <view_ref> ::= [ '(' ] [ <database_alias> '!' ] [ <creator_name> '.' ] <view_name> | ':' <object_name> [ ')' ]
The alias name applies to this query specification only. In no alias is specified, the table name will be used. A <table_ref> (table reference) ,may include the explicit specification of the database and the table creator name (also known as the table owner name).
<database_alias> defines the name of the database. When multiple databases have been opened in the current program instance (session), the <database_alias> specifies which database to use. If no database is specified the currently in-use database is assumed. The "USE DATABASE <database_alias>;" command can be used to set the database that should be used when no <database_alias> is set in a <table_ref> or <view_ref>
<creator_name> defines the object owner. If the creator name is not specified, then the server first checks for a table with the user's creator name, and then for tables with creator names 'Common' and 'System'.
<object_name> is a variable of type OBJNAME containing a valid object reference may be used in place of the explicit object reference. In addition, each "name" type value may be specified using an OBJNAME variable (preceded by a colon, ':').
When a <view_ref> (view reference) is enclosed in parenthesis, the view is optimized in isolation. Without parenthesis, the view is optimized by pushing <search_condition> that are applied to simple columns of the view into the <where_clause> of the view. In cases where optimisation is not improving the access speed, parentheses will disable the optimisation process.
In a LEFT OUTER JOIN, NULL values are inserted in place of columns from the RIGHT table where no matching value from the LEFT table was found.
In an RIGHT OUTER JOIN, NULL values are inserted in place of columns from the LEFT table where no matching value from the RIGHT table was found.
|
Example/* ** Give us each golfer's SurName, FirstNames, ** Nationality, Status and Handicap */ SELECT SurName, FirstNames, Nationality, Status, Handicap FROM Golfers; PRINTALL; Screen output: Bee Buzz dutch Pro 20 Dry Tumble british Pro -3 Dumsdee Humsdee french Pro 20 Head Fish canadian Pro -2 |
The WHERE clause contains the search condition that specifies which rows will be selected from the tables.
<where_clause> ::= WHERE <search_condition>
|
Example/* ** Give us golfer Name, Earnings and Name of the club ** that golfers is a member of, and order the result ** Earnings */ SELECT A.Name, A.Earnings, B.Name FROM Golfers A, Clubs B WHERE A.MemberOfClub == B.ID ORDER BY A.Earnings; PRINTALL; Screen output: Head Fish $ 3,520.0 Astronauts Golf Club Dry Tumble $ 3,999.0 Sterling Club Hamburg Bee Buzz $ 5,000.0 Sterling Club Hamburg Dumsdee Humsdee $ 10,999.99 Astronauts Golf Club |
The GROUP BY clause is used to group sets of rows together according to the values in the columns specified. A single row is returned for each row group. Columns selected which are not in the GROUP BY list must be aggregated using one of the supported aggregate functions, such as COUNT, SUM, AVG, MIN, MAX.
<group_by_clause> ::= GROUP BY <column_ref> { ',' <column_ref>}
|
Example/* ** Give us the club ID and sum up the earnings of all ** respective members of this club */ SELECT MemberOfClub, SUM( Earnings ) FROM Golfers GROUP BY MemberOfClub; PRINTALL; Screen output: 1 $ 8,999.0 2 $ 14,519.99 |
The HAVING clause allows you to place a further restriction on grouped rows returned.
<having_clause> ::= HAVING <search_condition>
|
Example/* ** Give us the club ID and sum up the earnings of all ** respective members of this club if these exceed ** $10,000 */ SELECT MemberOfClub, SUM( Earnings ) FROM Golfers GROUP BY MemberOfClub HAVING SUM( Earnings ) > MONEY( 10000 ); PRINTALL; Screen output: 2 $ 14,519.99 |
The ORDER BY clause allows you to order the returned rows by one or more columns. Also columns that are not specified in the SELECT clause can be used to order the returned rows.
<order_by_clause> ::= ORDER BY <column_ref> [ ASC | DESC ] [ { ',' <column_ref> [ ASC | DESC ] } ]
|
Example/* ** Give us golfer's club Name, golfer's Name, Earnings; ** order by club Name first, then golfer's Handicap */ SELECT B.Name, A.Name, A.Earnings FROM Golfers A, Clubs B WHERE A.MemberOfClub == B.ID ORDER BY B.Name, A.Handicap; PRINTALL; Screen output: Astronauts Golf Club Dumsdee Humsdee $ 10,999.99 Astronauts Golf Club Head Fish $ 3,520.0 Sterling Club Hamburg Bee Buzz $ 5,000.0 Sterling Club Hamburg Dry Tumble $ 3,999.0 |
<select_stat> ::= <query_spec> [ ORDER BY <sort_spec> { ',' <sort_spec> } ] [ WHERE ( $true | $false ) ] [ INTO <cursor> ] [ FOR ( READONLY | <scroll_mode> | <update_mode> | EXTRACT ) ] ';' <sort_spec> ::= ( <column_ref> | <int_expr> ) [ ASC | DESC ] <scroll_mode> ::= SCROLLING [ <update_mode> ] <update_mode> ::= [ EXTRACT ] UPDATE [ <col_group> | OF <column_name> { ',' <column_name> } ] <col_group> ::= '(' <column_name> { ',' <column_name> } ')'
The SELECT statement returns a rowset of data from tables of a particular DBMS. If the FROM clause is omitted, the default CURSOR, $cursor, is used. <query_spec> is a query specification which is described in section 2.6.
If <int_expr> is used in <sort_spec> then it refers to the position number of the column as it appears in the SELECT list in <sort_spec>. If ASC (ascending) and DESC (descending) is omitted ASC is the default.
The OF clause in <update_mode> is alternative syntax for the normal <col_group> syntax provided for compatibility with the Data Access Language (DAL) standard.
The various select modes are described by the table below:
Mode Description Pros Cons READONLY Returns data row-by-row. Very large sets of data can be processed. Maximum of one page of data is stored on the client at any one time. The first row is available as soon as possible. No CURSOR motion other than FETCH NEXT is supported. All rows remain locked, on the server, until the last page of rows is fetched by the client. SCROLLING Returns data row-by-row. All fetched data is buffered on the client. All types of CURSOR motions are supported. The first row is available as soon as possible. Sufficient memory on the client machine to contain the entire result set is required. Rows remain locked until the last page is fetched from the server. EXTRACT The SELECT blocks until all data is retrieved from the server. All types of CURSOR motion are supported. Locks on the rows on the server are released as soon as possible. Sufficient memory on the client to contain the entire result is required. The first row is only available once the entire SELECT has been executed.
Whenever client memory permits, we recommend selecting data "FOR EXTRACT". In addition to the modes specified above, the UPDATE mode is used to select data to be used with the positioned update (UPDATE, DELETE WHERE CURRENT OF) statements later.
|
Example/* ** Give us golfer Name and Earnings */ CURSOR myCursor; SELECT A.Name, A.Earnings FROM Golfers A INTO myCursor FOR EXTRACT; PRINTALL myCursor; Screen output: Bee Buzz $ 5,000.0 Dry Tumble $ 3,999.0 Dumsdee Humsdee $ 10,999.99 Head Fish $ 3,520.0 |
<fetch_stat> ::= FETCH [ <motion> ] [ OF <cursor>] ';' <motion> ::= FIRST | LAST | ABSOLUTE <int_expr> | NEXT | PREVIOUS | RELATIVE <int_expr>
All CURSOR rowsets have a current row position. The FETCH statement is used to set the current row position of a rowset. After a SELECT statement, the current row position is "before the rowset". This means that a FETCH NEXT (or FETCH FIRST) is required to position the current row on the first row of the rowset.
Only then is it valid to use the CURSOR reference operator ('->'). After doing a FETCH NEXT on a rowset whose current position is set to the last row, the current row position will be " after the rowset". When this occurs, the global variable $sqlcode will be set to the value $sqlnotfound.
<int_expr> is the row number in the rowset. When using FETCH ABSOLUTE, the first row is 1. If the row number specified is greater than the number of rows in the rowset, then current row position will be set to after the rowset.
Note that on rowsets selected FOR EXTRACT or FOR SCROLLING permit the use of CURSOR motions other than FETCH NEXT.
The rowset has no particular order, unless ORDER BY was specified in the SELECT statement that created the rowset.
When a rowset is first generated, the current row is the one just before the actual first row, so that a FETCH NEXT statement then moves it to the first row.
There is no current row of the rowset between the execution of an SELECT and the first FETCH statement; when FETCH moves on past the last row of the rowset; and if the current row is deleted.
If you attempt to fetch past the end of the rowset with either FETCH FIRST, LAST, NEXT or PREVIOUS, a $sqlcode value of $sqlnotfound is caused; for FETCH ABSOLUTE or RELATIVE, an error code is returned.
|
ExampleCURSOR myCursor; SELECT A.Name, A.Earnings FROM Golfers A INTO myCursor FOR EXTRACT; FETCH ABSOLUTE 2 OF myCursor; PRINTROW myCursor; Screen output: Dry Tumble $ 3,999.0 |
<insert_stat> ::= INSERT [ INTO ] <table_ref> [ <col_group> ] [ <values_spec> | <query_spec> ] ';' <values_spec> ::= VALUES '(' [ <expression> | NULL ] { ',' [ <expression> | NULL ] } ')' [ <return_row> ] <return_row> ::= RETURNING [ '(' <col_group> ')' ] [ INTO <cursor> ]
Insert a row or set of rows into a specific table. Columns not listed in <col_group> in the INSERT are given default values by the server. If <col_group> is not specified, then all columns of the table are assumed in order defined. Values for each column is specified in <values_spec>. As an alternative to a list of values, <query_spec> may be used to select a set of rows from other tables in the database to insert into the table.
The RETURNING clause can be used to retrieve any values inserted into any columns of the row. This can be used to retrieve the default values inserted by the server (e.g. an automatic counter). The result is a rowset containing one row.
$rowsaffected is set after a successful INSERT, UPDATE or DELETE. The value is the number of rows effected by the query. If an error occurs $rowsaffected is set to zero.
|
Example/* ** Let's insert some test data, so we don't have to play ** with the Golfers' Database "real" data */ INSERT INTO Results ( Year, Competition, Placing, Golfer, TotalScore, Points, Winnings ) VALUES ( 1900, 2, 2, 4, 503, 3, "$600" ); /* ** Check if the data really got entered fine into the ** Results table */ SELECT Year FROM RESULTS WHERE Year == 1900; PRINTALL; /* ** Now let's remove this row again... */ DELETE FROM Results WHERE Year == 1900; /* ** Check if the data really got deleted from the ** Results table */ SELECT Year FROM RESULTS WHERE Year == 1900; PRINTALL; Screen output: 1900 |
<update_stat> ::= UPDATE <table_ref> SET <set_clause> { ',' <set_clause> } [ WHERE <search_cond> | WHERE CURRENT OF <cursor> ] ';' <set_clause> ::= <column_name> '=' ( <search_cond> | NULL )
Update a row, or set of rows of the specified table. The <set_clause> describes how a column is to be updated. No sub-queries or aggregate functions are allowed in the <search_cond> of the <set_clause>. The <search_cond> may contain references to columns of the updated row.
There are two forms of update statement: searched and positioned. When using the positioned form, the CURSOR in the WHERE CURRENT OF clause must contain a rowset which was selected FOR UPDATE.
The table reference must specify an updatable table or view. In the case of updating a view, this is only possible if the view is derived from a single table.
According to the definition of the column, the values that can be updated may be restricted. Attempts to insert a row in violation of these restrictions will result in an error.
If an expression in the <set_clause> includes a reference to a column being updated, the value used in computing the expression is the value of the column before any updates are performed on the row.
$rowsaffected is set after a successful INSERT, UPDATE or DELETE. The value is the number of rows effected by the query. If an error occurs $rowsaffected is set to zero.
|
Example/* ** Change contents of all FirstNames containing "Buzz" ** to "Buzzz" */ UPDATE Golfers SET FirstNames = "Buzzz" WHERE FirstNames == "Buzz"; /* ** Find the rows in which the FirstNames column content ** starts with "Buzz", print all Firstnames and Surnames ** of these rows */ SELECT FirstNames, Surname FROM Golfers WHERE FirstNames LIKE "Buzz%"; PRINTALL; /* ** Undo the changes, repeat the find and print part to ** ensure the undo did really work */ UPDATE Golfers SET FirstNames = "Buzz" WHERE FirstNames == "Buzzz"; SELECT FirstNames, Surname FROM Golfers WHERE FirstNames LIKE "Buzz%"; PRINTALL; Screen output: Buzzz Bee Buzz Bee |
<delete_stat> ::= DELETE [ FROM ] <table_reference> [ WHERE <search_cond> | WHERE CURRENT OF <cursor> ] ';'
Delete a row or set of rows from a particular table. Two forms of deletion are supported, positioned and searched. Searched is the most commonly used form. The <cursor> in the WHERE CURRENT OF clause must contain a rowset selected FOR UPDATE.
The following applies to the WHERE CURRENT OF <cursor> variant:
The cursor must specify an updatable table or view and must specify a rowset created by a SELECT statement including the <update_mode>.
The database may include referential-integrity constraints that prevent certain rows from being dropped. Attempts to drop these rows will result in an error.
Rows from a view may only be dropped, if the view is derived from a single table.
$rowsaffected is set after a successful INSERT, UPDATE or DELETE. The value is the number of rows effected by the query. If an error occurs $rowsaffected is set to zero.
The following applies to the WHERE <search_cond> variant:
The database may contain referential integrity constraints that prevent certain rows from being dropped. Attempts to drop these rows will result in an error.
Rows from a view may only be dropped if the view has been derived from a single table.
$rowsaffected is set after a successful INSERT, UPDATE or DELETE. The value is the number of rows effected by the query. If an error occurs $rowsaffected is set to zero.
|
Example
/*
** Let's first insert some test data, so we don't have
** to play with the Golfers' Database "real" data
*/
INSERT INTO Results
(
Year,
Competition,
Placing,
Golfer,
TotalScore,
Points,
Winnings
)
VALUES
(
1900,
2,
2,
4,
503,
3,
"$600"
);
/*
** Check if the data really got entered fine into the
** Results table
*/
SELECT Year FROM RESULTS WHERE Year == 1900;
PRINTALL;
/*
** Now let's remove this row again...
*/
DELETE FROM Results WHERE Year == 1900;
/*
** Check if the data really got deleted from the Results
** table
*/
SELECT Year FROM RESULTS WHERE Year == 1900;
PRINTALL;
Screen output:
1900
|
BEGIN
<begin_stat> ::= BEGIN [ WORK | TRANSACTION | TRANS ] [ [ FOR ] <dbms_alias> ] ';'
In PrimeBaseTalk a transaction must be explicitly begun using this statement. A transaction is a unit of work (updates to a specific DBMS). If the FOR clause is omitted, the currently in-use database is assumed.
Transactions are started and committed for each DBMS (connection) independently. There is currently no mechanism to synchronize transactions across DBMS's. If an error occurs during the transaction, it will be automatically aborted ("rolled back").
When no transaction has been started with BEGIN transaction, each SQL statement will be executed in a server controlled transaction and is commited automatically.
Data definition statements (CREATE, DROP, ALTER ) are not allowed after starting a transaction with BEGIN.
<commit_stat> ::= COMMIT [ WORK | TRANS | TRANSACTION ] [ [ FOR ] <dbms_alias> ] ';'
Commit all updates done during the current transaction. If this statement completes without error, the DBMS guarantees that the data has been written. If the FOR clause is omitted, the currently in-use database is assumed. If no transaction is currently in progress, this statement is ignored.
|
Example/* ** Begin the transaction */ BEGIN; /* ** Some test data */ INSERT INTO Results ( Year, Competition, Placing, Golfer, TotalScore, Points, Winnings ) VALUES ( 1899, 2, 2, 4, 503, 3, "$600" ); /* ** Check if the data got entered into the Results table ** already */ SELECT Year FROM RESULTS WHERE Year == 1899; PRINTALL; /* ** Now let's commit, thus write the data into the Results ** table */ COMMIT; /* ** Now let's remove this row again... transaction- ** oriented again, of course */ BEGIN; DELETE FROM Results WHERE Year == 1899; COMMIT; /* ** Check if the data really got deleted from the Results ** table */ SELECT Year FROM RESULTS WHERE Year == 1899; PRINTALL; Screen output: 1899 |
<rollback_stat> := ROLLBACK [ WORK ] [ [ FOR ] <dbms_alias> ]';'
Cancel all updates done during the current transaction to databases on the specified DBMS and begin a new transaction. If the FOR clause is omitted, the currently in-use DBMS is assumed. If no transaction is currently in progress, this statement is ignored.
|
Example/* ** Let's try something really awesome now */ BEGIN; /* ** Some test data */ INSERT INTO Results ( Year, Competition, Placing, Golfer, TotalScore, Points, Winnings ) VALUES ( 1899, 2, 2, 4, 503, 3, "$600" ); /* ** Ahm, maybe that wasn't so good an idea, let's forget ** about it */ ROLLBACK; /* ** Check that the data really didn't make it into the ** Results table */ SELECT Year FROM RESULTS WHERE Year == 1899; PRINTALL; Screen output: (none) |
In some of the examples used in this reference manual, the golfers database is used to demonstrate the various commands of PrimeBaseTalk.
A brief description of the tables in this database is given here, followed by the creation script of the database, which also insert sample data which the examples work with.
You can copy and paste this script into a texteditor and store it in a file so it can be executed using the EXECUTE FILE command from within the PrimeBase Automation Client (PBAC) or the PrimeBase SQL Database Server console for example.
The golfers database consists of six tables storing information on golfers, golf clubs, golf courses, competitions, results of competitions and scores.
The golfers table contains one entry for each golfer.
Column
Description
ID An identity number for each golfer.
This number is taken from the domain GolferID, which has a serial default defined on it.
The primary key for this table is defined on this column, as is an index.SurName The last name of the golfer.This value is taken from the domain NameType, which is ordered using a case insensitive system collating sequence. FirstNames The first names of each golfer. This value is taken from the domain NameType, whose values are defined to be case insensitive. This is done by specifying that the domain is to be ordered using the case insensitive system collating sequence. Name This is a composite column, which takes its values from the columns, SurName and FirstName. This column is a candidate key for the table, which means the composite values of the column are table-wide unique. Title This column stores the title of each golfer: for example, whether the golfer is a Mr., Mrs., Miss, etc., etc. Gender The sex of each golfer. A rule has been defined on this column, allowing only the values 'M' and 'F'. Nationality The nationality of each golfer. All values in this column are taken from the domain NameType. DateOfBirth The date of birth of each golfer. Status The status of each golf
