Difference between revisions of "Built-in functions and Stored Procedures"
DrewJensen (Talk | contribs) m |
DrewJensen (Talk | contribs) |
||
| Line 1: | Line 1: | ||
The HSQLdb database engine included with Base supports the following functions and stored procedures for use within queries. | The HSQLdb database engine included with Base supports the following functions and stored procedures for use within queries. | ||
| + | |||
| + | The Query component in Base allows for SQL commands that return a result set. i.e. SELECT and CALL | ||
| + | The Query component in Base supports a GUI query designer, and a text based query editor. | ||
| + | The Query component in Base also supports the use of named replaceable parameters in these statements, in most cases. | ||
| + | This feature is controlled by the option 'Escape Processing'. | ||
| + | Escape Processing is always set to TRUE when using the GUI query designer. | ||
| + | Escape Processing is on by default in the text based query editor, but may be turned off by selecting the 'Run SQL directly' tool button. | ||
| + | |||
| + | In the tables below each function is marked as being available or not in these three modes of the query component. | ||
| + | Yes in the Designer column means that it may be used in the GUI query designer. | ||
| + | Yes in the Parser column means that it may be used in the text base query editor with escape processing enabled. | ||
| + | No in the Parser column mens that the function is only available in the text based query editor with escape processing disabled. | ||
| + | |||
| + | The TOOLS > SQL window allows any valid SQL statement to be run, but it does not return any results to the user. So the main purpose is to issue Data Definition Language ( DDL ) commands. i.e. CREATE TABLE, ALTER TABLE. | ||
| + | A secondary use is to issue Data Manipulation Language ( DML ) commands. i.e. UPDATE, DELETE, INSERT commands. | ||
| + | All functions listed below are available for use in the SQL window. | ||
| + | |||
{|border="1" | {|border="1" | ||
| Line 186: | Line 203: | ||
| YES || YES || YES | | YES || YES || YES | ||
|- | |- | ||
| − | |TRIM( | + | |TRIM( LEADING TRAILING BOTH FROM <COLUMN> ) |
| YES || YES || YES | | YES || YES || YES | ||
|- | |- | ||
Revision as of 21:18, 25 August 2007
The HSQLdb database engine included with Base supports the following functions and stored procedures for use within queries.
The Query component in Base allows for SQL commands that return a result set. i.e. SELECT and CALL The Query component in Base supports a GUI query designer, and a text based query editor. The Query component in Base also supports the use of named replaceable parameters in these statements, in most cases. This feature is controlled by the option 'Escape Processing'. Escape Processing is always set to TRUE when using the GUI query designer. Escape Processing is on by default in the text based query editor, but may be turned off by selecting the 'Run SQL directly' tool button.
In the tables below each function is marked as being available or not in these three modes of the query component. Yes in the Designer column means that it may be used in the GUI query designer. Yes in the Parser column means that it may be used in the text base query editor with escape processing enabled. No in the Parser column mens that the function is only available in the text based query editor with escape processing disabled.
The TOOLS > SQL window allows any valid SQL statement to be run, but it does not return any results to the user. So the main purpose is to issue Data Definition Language ( DDL ) commands. i.e. CREATE TABLE, ALTER TABLE. A secondary use is to issue Data Manipulation Language ( DML ) commands. i.e. UPDATE, DELETE, INSERT commands. All functions listed below are available for use in the SQL window.
| Function | Desinger | Parser | Direct |
| ABS(d) | YES | YES | YES |
| ACOS(d) | YES | YES | YES |
| ASIN(d) | YES | YES | YES |
| ATAN(d) | YES | YES | YES |
| ATAN2(a,b) | YES | YES | YES |
| BITAND(a,b) | YES | YES | YES |
| BITOR(a,b) | YES | YES | YES |
| CEILING(d) | YES | YES | YES |
| COS(d) | YES | YES | YES |
| COT(d) | YES | YES | YES |
| DEGREES(d) | YES | YES | YES |
| EXP(d) | YES | YES | YES |
| FLOOR(d) | YES | YES | YES |
| LOG(d) | YES | YES | YES |
| LOG10(d) | YES | YES | YES |
| MOD(a,b) | YES | YES | YES |
| PI() | YES | YES | YES |
| POWER(a,b) | YES | YES | YES |
| RADIANS(d) | YES | YES | YES |
| RAND() | YES | YES | YES |
| ROUND(a,b) | YES | YES | YES |
| ROUNDMAGIC(d) | YES | YES | YES |
| SIGN( d ) | YES | YES | YES |
| SIN(d) | YES | YES | YES |
| SQRT( d ) | YES | YES | YES |
| TAN( A ) | YES | YES | YES |
| TRUNCATE(a,b) | YES | YES | YES |
| Function | Desinger | Parser | Direct |
| VAR_POP | |||
| VAR_SAMP | |||
| STDDEV_POP | |||
| STDDEV_SAMP |
| Function | Desinger | Parser | Direct |
| ASCII(s) | YES | YES | YES |
| BIT_LENGTH(str) | YES | YES | YES |
| CHAR(c) | YES | YES | YES |
| CHAR_LENGTH(str) | YES | YES | YES |
| CONCAT(str1,str2) | YES | YES | YES |
| DIFFERENCE(s1,s2) | YES | YES | YES |
| HEXTORAW(s1) | YES | YES | YES |
| INSERT(s,start,len,s2) | YES | YES | YES |
| LCASE(s) | YES | YES | YES |
| LEFT(s,count) | YES | YES | YES |
| LENGTH(s) | YES | YES | YES |
| LOCATE(search,s,[start]) | YES | YES | YES |
| LTRIM(s) | YES | YES | YES |
| OCTET_LENGTH(str) | YES | YES | YES |
| RAWTOHEX(s1) | YES | YES | YES |
| POSITION (... IN ..) | YES | YES | YES |
| REPEAT(s,count) | YES | YES | YES |
| REPLACE(s,replace,s2) | YES | YES | YES |
| RIGHT(s,count) | YES | YES | YES |
| RTRIM(s) | YES | YES | YES |
| SOUNDEX(s) | YES | YES | YES |
| SPACE(count) | YES | YES | YES |
| SUBSTR(s,start[,len]) | YES | YES | YES |
| SUBSTRING(s,start[,len]) | YES | YES | YES |
| SUBSTRING(... FROM ... FOR ...) | YES | YES | YES |
| TRIM( LEADING TRAILING BOTH FROM <COLUMN> ) | YES | YES | YES |
| UCASE(s) | YES | YES | YES |
| LOWER(s) | YES | YES | YES |
| UPPER(s) | YES | YES | YES |
| Function | Desinger | Parser | Direct |
| CURDATE() | YES | YES | YES |
| CURTIME() | YES | YES | YES |
| DATEDIFF(string, datetime1, datetime2) | YES | YES | YES |
| DAYNAME(date) | YES | YES | YES |
| DAYOFMONTH(date) | YES | YES | YES |
| DAYOFWEEK(date) | YES | YES | YES |
| DAYOFYEAR(date) | YES | YES | YES |
| HOUR(time) | YES | YES | YES |
| MINUTE(time) | YES | YES | YES |
| MONTH(date) | YES | YES | YES |
| MONTHNAME(date) | YES | YES | YES |
| NOW() | YES | YES | YES |
| QUARTER(date) | YES | YES | YES |
| SECOND(time) | YES | YES | YES |
| WEEK(date) | YES | YES | YES |
| YEAR(date) | YES | YES | YES |
| CURRENT_DATE | YES | YES | YES |
| CURRENT_TIME | YES | YES | YES |
| CURRENT_TIMESTAMP | YES | YES | YES |
| TO_CHAR( <date column>, format String ) | YES | YES | YES |
| Function | Desinger | Parser | Direct |
| DATABASE() | YES | YES | YES |
| USER() | NO | NO | YES |
| CURRENT_USER | YES | YES | YES |
| IDENTITY() | YES | YES | YES |
