Blackfish SQL
|
Many administrative tasks are supported by stored procedures in the DB_ADMIN class. The DB_UTIL class provides stored procedures for numeric, string, and date/time operations.
DB_ADMIN is a group of stored procedures for performing a variety of database administration tasks. For example:
These methods can be called from SQL using the CALL statement. They can be called without creating a METHOD alias because the Blackfish SQL recognizes the methods in DB_ADMIN as built-in methods.
The following sections provide the syntax and a brief description of each DB_ADMIN method.
ALTER_DATABASE(string properties)
Alters specified database properties. properties is a comma-separated list of settings for the columns from the DatabaseColumns class. Each property is specified as follows: <COLUMN_NAME>=<VALUE>. See the online help for information on the DatabaseColumns class.
ALTER_MIRROR(string mirrorName, string properties)
Alters an existing mirror configuration. mirrorName is a value from the SysMirrors.NAME column. properties is a comma-separated list of settings for the columns from the SysMirrors class. Each property is specified as follows: <COLUMN_NAME>=<VALUE>. See the online help for information on the SysMirrors class.
ALTER_MIRROR_SCHEDULE(INT32 mirrorId, string properties)
Alters an existing mirror schedule item. mirrorName is value from the SysMirrorSchedule.NAME column. properties A comma separated list of settings for the columns from the SysMirrorSchedule class. Each property is specified as follows: <COLUMN_NAME>=<VALUE>. See the online help for information on the SysMirrorSchedule class.
CHANGE_PASSWORD(string oldpassword, string newPassword)
Changes the password for the currently connected user.
CLOSE_CONNECTION(INT32 connectionId, INT64 birthTimeMilliseconds)
Closes an open connection. Can be used to close unwanted connections. connectionId From the ID column returned by GET_CONNECTIONS. birthTimeMilliseconds from the BIRTH column returned by GET_CONNECTIONS. Returns true if successful See GET_CONNECTIONS to obtain a table of connections to close.
CLOSE_OTHER_CONNECTIONS()
Closes all other open connections. Administrator rights are required to execute this method.
COPY_STREAMS(string otherFilename, string adminUser, string adminPass, boolean doOverwrite, boolean doIgnoreErrors)
Copies all tables and indexes from the current database to another database. COPY_USERS method should be called first if users have been added to the database. otherFilename is the file name of the destination database. adminUser is the user with ADMIN privileges in the destination database. adminPass is the password of the ADMIN user in the destination database. overwrite allows the tables to be overwritten. If false this will cause an error. ignoreErrors causes errors to be ignored when recovering a corrupt database.
COPY_USERS(string otherFilename, string adminUser, string adminPass, boolean doCopyEncryption, boolean replaceExistingUsers)
Copies all users from the current database to another specified database. otherFilename is the file name of the destination database. adminUser user with ADMIN privileges in the destination database. adminPass password of the ADMIN user in the destination database. copyEncryption if the current database is encrypted then encrypt the target database with the same key. replaceExistingUsers if true replace the existing users in the target database.
CREATE_MIRROR(string properties)
Creates a new mirror with the configuration properties provided. properties A comma-separated list of settings for the columns from the SysMirrors class. Each property is specified as follows: <COLUMN_NAME>=<VALUE>. Returns a unique ID for new new mirror. See the online help for information on the SysMirrors class.
CREATE_MIRROR_SCHEDULE(string mirrorName, string properties)
Creates a new mirror synchronization schedule. mirrorName the name of the mirror to add the mirror schedule item for. properties a comma-separated list of settings for the columns in the SysMirrorSchedule table. Each property is defined by specified as follows: <COLUMN_NAME>=<VALUE>. Returns a unique INT64 identifier for the new schedule item. See the online help for information on the SysMirrorSchedule class.
DROP_MIRROR(string mirrorName)
Drops an existing mirror configuration. mirrorName is a value from the SysMirrors.NAME column. See the online help for information on the SysMirrors class.
DROP_MIRROR_SCHEDULE(INT32 mirrorID)
Drops an existing mirror schedule item. mirrorID is a value from the SysMirrorSchedule.ID column See the online help for information on the SysMirrorSchedule class.
ENCRYPT(string adminPassword, string masterKeySeed)
Encrypts an empty database. adminPass password of the user performing this command. masterKeySeed a random sequence of 16 characters that is used internally as the master password. Once provided, it does not needed to be provided for access to the database. This should be very random sequence of characters.
GET_ALL_LICENCES( )
Returns a result table with zero or more rows of all licenses that could be found. The columns for this result table are defined in LicenseColumns class. See the online help for information on the LicenseColumns class.
GET_COLUMN_PRIVILEGES(string catalogPattern, string schemaPattern, string tablePattern, string columnPattern)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means means that the schema name should not be used to narrow the search. tablePattern specifies the LIKE table search patttern. null means means that the table name should not be used to narrow the search. columnPattern specifies the LIKE column search patttern. null means means that the column name should not be used to narrow the search. Returns a result table with column privileges for the specified table(s). The columns for this result table are defined in the ColumnPrivilegeColumns class. See the online help for information on the ColumnPrivilegeColumns class.
GET_COLUMNS(string catalogPattern, string schemaPattern, string tablePattern, string columnPattern)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means means that the schema name should not be used to narrow the search. tablePattern specifies the LIKE table search patttern. null means means that the table name should not be used to narrow the search. columnPattern specifies the LIKE column search patttern. null means means that the column name should not be used to narrow the search. Returns a result table with metadata for the columns of the specified table(s). The columns for this result table are defined in the ColumnsColumns class. See the online help for information on the ColumnsColumns class.
GET_CONNECTIONS()
Returns a result table of the open connections for the current server connection. The columns for this result table are defined in the ConColumns class. See the online help for information on the ConColumns class.
GET_DATABASE_PRIVILEGES(boolean forRoles)
forRoles if true, grantee is a role; if false, grantee is a user.
Returns a result table with database access rights for each user or role using the following columns:
GET_DATABASE_PRODUCT_NAME()
Returns the product name of the server as a string.
GET_DATABASE_PRODUCT_VERSION()
Returns the product version of the server as a string
GET_DATABASE_PROPS()
Returns a result table with the properties for the current database. The columns for this result table are defined in the DatabaseColumns class. See the online help for information on the DatabaseColumns class.
GET_DATABASE_STATUS()
Returns a result table with one row of status information about the current database. The columns for this result table are defined in the DatabaseStatusTable class. See the online help for information on the DatabaseStatusTable class.
GET_DATABASE_STATUS_LOG_FILTER()
Returns an INT32 filter that controls what kind of logging information is logged to the status log file for all current database connections. The meaning of the bit masks is found in LogFilterCodes class. See the online help for information on the LogFilterCodes class.
GET_DATATYPES()
Returns a result table with a row for each supported data type in the database server. The columns for this result table are defined in the DataTypesColumns class. See the online help for information on the DataTypesColumns class.
GET_FOREIGN_KEYS(catalogPattern, schemaPattern, tablePattern)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means means that the schema name should not be used to narrow the search. tablePattern specifies the LIKE table search patttern. null means means that the table name should not be used to narrow the search. Returns a result table with a row for each foreign key in the specified table(s). The columns for this result table are defined in the ForeignKeyColumnsColumns class. See the online help for information on the ForeignKeyColumnsColumns class.
GET_FOREIGN_KEY_COLUMNS(string catalogPattern, string schemaPattern, string tablePattern, string foreignKeyPattern, string primaryCatalogPattern, string primarySchemaPattern, string primaryTablePattern, string primaryIndexPattern)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means means that the schema name should not be used to narrow the search. tablePattern specifies the LIKE table search patttern. null means means that the table name should not be used to narrow the search. foreignKeyPattern specifies the LIKE foreign key search patttern. null means means that the table name should not be used to narrow the search. primaryCatalogPattern specifies the LIKE primary catalog search pattern. Not used. Reserved for future use. primarySchemaPattern specifies the LIKE primary schema search patttern. null means means that the primary schema name should not be used to narrow the search. primaryTablePattern specifies the LIKE primary table search patttern. null means means that the table name should not be used to narrow the search. primaryIndexPattern specifies the LIKE primary table index search patttern. null means means that the table name should not be used to narrow the search. Returns a result table with a row for each foreign key column pairs in the specified table(s). The columns for this result table are defined in the ForeignKeyColumns class. See the online help for information on the ForeignKeyColumns class.
GET_INDEXES(string catalogPattern, string schemaPattern, string tablePattern)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means means that the schema name should not be used to narrow the search. tablePattern specifies the LIKE table search patttern. null means means that the table name should not be used to narrow the search. Returns a result table with the indexes of the specified table(s). The columns for this result table are defined in the IndexesColumns class. See the online help for information on the IndexesColumns class.
GET_INDEX_COLUMNS(string catalogPattern, string schemaPattern, string tablePattern, string indexPattern)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means means that the schema name should not be used to narrow the search. tablePattern specifies the LIKE table search patttern. null means means that the table name should not be used to narrow the search. indexPattern specifies the LIKE index search patttern. null means means that the table name should not be used to narrow the search. Returns a result table with the column information of the specified table index(es). The columns for this result table are defined in the IndexColumnsColumns class. See the online help for information on the IndexColumnsColumns class.
GET_KEYWORDS()
Returns a result table with the reserved keywords in this database.
GET_LICENSE()
Returns a result table with one row of license information for the best deployment license found. The columns for this result table are defined in the LicenseColumns class. See the online help for information on the LicenseColumns class.
GET_LICENSE_SEARCH_DIRS()
Returns a result table with a row for each directory that is searched for license files.
GET_LOCKS()
Returns a result table of all the currently held table and row locks for all connections to the current database. The columns for this result table are defined in the LockColumns class. See the online help for information on the LockColumns class.
GET_MIRROR_ID()
Returns the INT64 mirror id of the current mirror if this is a mirror.
GET_MIRRORS(mirrorName, checkStatus)
name is the name of the mirror or null to get all mirrors. checkStatus is set to TRUE to provide additional columns on the status of the mirror. Status checking requires more work to be performed, but provides additional information on a mirror.
Returns a result table with a row for each mirror. The columns for this result table are defined in the MirrorStatusColumns class. See the online help for information on the MirrorStatusColumns class.
GET_NEWEST_STATUS_LOG_ID()
Returns the INT32 ID of the newest log file that can be retrieved using the GET_STATUS_LOG() method.
GET_OLDEST_STATUS_LOG_ID()
Returns the INT32 ID of the oldest log file that can be retrieved using the GET_STATUS_LOG() method.
GET_PROCEDURES(string catalogPattern, string schemaPattern, string procedurePattern, string type)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means means that the schema name should not be used to narrow the search. procedurePattern specifies the LIKE procedure search patttern. null means means that the procedure name should not be used to narrow the search. procedureType a procedure type; must be either PROCEDURE or FUNCTION or null for any procedure type. Returns a result table with metadata for the known stored procedures. The columns for this result table are defined in the ProcedureColumns class. See the online help for information on the ProcedureColumns class.
GET_PROCEDURE_COLUMNS(string catalogPattern, string schemaPattern, string procedurePattern, string parameterPattern)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means that the schema name should not be used to narrow the search. procedureNamePattern specifies the LIKE procedure search patttern. null means that the procedure name should not be used to narrow the search. columnNamePattern specifies the LIKE column search patttern. null means that the column name should not be used to narrow the search. Returns a result table with the parameters of the specified procedure(s). The columns for this result table are defined in the ProcedureParametersColumns class. See the online help for information on the ProcedureParametersColumns class.
GET_PROCEDURE_PRIVILEGES() Returns a result table with descriptions of the access rights for each procedure. The result table has the following columns:
GET_ROLES()
Returns a result table with the roles in the database. The columns for this result table are defined in the RolesColumns class. See the online help for information on the RolesColumns class.
GET_ROLE_GRANTS(boolean forRoles)
forRoles set to true, grantee is a role; set to false, grantee is a user.
The result table has the following columns:
GET_SCHEMAS(string catalogPattern)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. Returns a result table with the schemas in the database.
GET_STATUS_LOG_FILTER()
Returns the INT32 filter that controls the type of logging information to be logged to the status log for this connection. The meaning of the bit masks is found in LogFilterCodes class. See the online help for information on the LogFilterCodes class.
GET_STATUS_LOG(INT32 log_id, INT64 offset)
id is the ID of the log file being retrieved. offset is the offset into the log file from the start of the file. Returns the status log for the current databas as a stream.
GET_STATUS_LOGS()
Returns a result table with id of the existing logs for this database. The columns for this result table are defined in the StatusLogColumns class. See the online help for information on the StatusLogColumns class.
GET_TABLE_PRIVILEGES(string catalogPattern, string schemaPattern, string tablePattern)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means means that the schema name should not be used to narrow the search. tablePattern specifies the LIKE table search patttern. null means means that the table name should not be used to narrow the search. Returns a result table privilege descriptions for the selected table(s). The columns for this result table are defined in the TablePrivilegeColumns class. See the online help for information on the TablePrivilegeColumns class.
GET_TABLES(string catalogPattern, string schemaPattern, string tablePattern, string type)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means means that the schema name should not be used to narrow the search. tablePattern specifies the LIKE table search patttern. null means means that the table name should not be used to narrow the search. types comma separated list of TABLE, VIEW, SYSTEM_TABLE or null. Returns a result table with metadata for the specified table(s). The columns for this result table are defined in the TableColumns class. See the online help for information on the TableColumns class.
GET_THIS_MIRROR(boolean checkStatus) checkStatus TRUE to provide additional columns on the status of the mirror. Like GET_MIRRORS except that it returns information for only the mirror this procedure is executed against. Returns a result table with a row for this mirror. The columns for this result table are defined in the MirrorStatusColumns class. See the online help for information on the MirrorStatusColumns class.
GET_TRIGGERS(string catalogPattern, string schemaPattern, string tablePattern, string trigger)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means means that the schema name should not be used to narrow the search. tablePattern specifies the LIKE table search patttern. null means means that the table name should not be used to narrow the search. triggerPattern specifies the LIKE trigger search patttern. null means means that the trigger name should not be used to narrow the search. Returns a result table of the triggers of the specified table(s).
The result table has the following columns:
GET_USERS()
Returns a result table with the users in the database.
GET_VIEWS(string catalogPattern, string schemaPattern, string view)
catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means means that the schema name should not be used to narrow the search. viewNamePattern specifies the LIKE view search patttern. null means that the view name should not be used to narrow the search. Returns a result table with the definitions of the specified view(s). The columns for this result table are defined in the ViewsColumns class. See the online help for information on the ViewsColumns class.
SET_DATABASE_STATUS_LOG_FILTER(INT32 filter)
Sets the filter that controls the type of logging information to be entered in the status log file for all current database connections.
SET_PRIMARY_MIRROR(INT64 txTerminationTimeout, boolean forceTransactionAbort, boolean forceSwitch)
Sets the current mirror to the primary mirror. txTerminationTimeout is milliseconds to wait for existing transactions to terminate. forceTxTermination causes existing transactions to abort after txTerminationTimeout milliseconds have elapsed. forceSwitch causes this mirror to become the primary mirror even if other mirrors could not be synchronized with this change.
SET_STATUS_LOG_FILTER(INT32 filter)
Sets the filter that controls the type of logging information to be logged to the status log file for the current connection.
SYNCH_MIRROR(string mirrorName)
Updates the mirror specified for mirrorName with the most recent log files of its update mirror if necessary.
VALIDATE_PRIMARY_MIRROR()
Validates a primary mirror so that write transactions can be performed against it.
VERIFY(string catalogPattern, string schemaPattern, string tablePattern, INT32 displayOptions, INT32 errorCount, out INT32 errorsEncountered, out String output)
Verifies one or more tables in the database. catalogPattern specifies the LIKE catalog search pattern. Not used. Reserved for future use. schemaPattern specifies the LIKE schema search patttern. null means means that the schema name should not be used to narrow the search. tablePattern specifies the LIKE table search patttern. null means means that the table name should not be used to narrow the search. displayOptions one or more of the StreamVerifierDisplay bit settings ORed together that instruct the verifier to display progress messages as the stream is verified. errorCount specifies the number of errors that can be ignored before an exception is thrown. errorsEncountered is an output parameter that returns the number of errors that were encountered. output is an output parameter that can be used to return a string with diagnostic output from the verifier.
VERIFY(string tablePattern, INT32 displayOptions, INT32 errorCount, out INT32 errorsEncountered, out String output)
Verifies one or more tables in the database. tablePattern specifies the LIKE table search patttern. null means means that the table name should not be used to narrow the search. displayOptions one or more of the StreamVerifierDisplay bit settings ORed together that instruct the verifier to display progress messages as the stream is verified. errorCount specifies the number of errors that can be ignored before an exception is thrown. errorsEncountered is an output parameter that returns the number of errors that were encountered. output is an output parameter that can be used to return a string with diagnostic output from the verifier.
DB_UTIL is a set of SQL stored procedures for performing numeric, string and date/time operations on data stored in database tables. These functions are implemented as Java UDFs in DB_UTIL.
Examples
The following statement computes the square root of the column COL1:
SELECT DB_UTIL.SQRT(COL1) FROM TABLE1;
The following statement computes some timestamps that are equal to the timestamp COL2 plus five hours.
SELECT DB_UTIL.TIMESTAMPADD('SQL_TSI_HOUR',5,COL2) FROM TABLE1;
ACOS(expression)
Returns the arccosine in radians of a number.
ASIN(expression)
Returns the arcsine in radians of a number.
ATAN(expression)
Returns the arctangent in radians of a number.
ATAN2(y, x)
Returns the arctangent of the quotient of its two arguments. The angle returned is a numeric value in radians between PI and -PI and represents the counterclockwise angle between the positive X axis and the point (x, y). Note that the y value is passed in first.
CEILING(expression)
Returns the smallest integer that is greater than or equal to the argument. The return is of the same data type as the input.
COS(expression)
Returns the cosine of an angle.
COT(expression)
Returns the cotangent of an angle.
DEGREES(expression)
Converts an angle in radians to degrees.
EXP(expression)
Returns the exponential value of expression.
FLOOR(expression)
Returns the largest integer that is equal to or less than expression. The return is of the same data type as the input.
LOG(expression)
Returns the natural logarithm of a number.
LOG10(expression)
Returns the base 10 logarithm of a number.
MOD(expression1, expression2)
Returns the remainder for expression divided by expression, where both expressions evaluate to integers of type SHORT, INTs or LONGs. The return is of the same data type as the input.
PI()
Returns the constant PI.
POWER(expression1, expression2)
Returns the value of expression1 raised to the power of expression2.
RADIANS(expression)
Converts an angle in degrees to radians.
RAND()
Generates a random floating point number.
RAND(expression)
Generates a random floating point number using expression as a seed integer.
ROUND(expression1, expression2)
Rounds expression1 to expression2 number of decimal places.
SIGN(expression)
Returns –l if the value of expression is negative, zero if expression is zero, and 1 if expression is positive. The return is of the same data type as the input.
SIN(expression)
Returns the sine in radians of an angle.
SQRT(expression)
Returns the square root of a number.
TAN(expression)
Returns the tangent of an angle given in radians.
TRUNCATE(expression1, expression2)
Truncates the value of expression1 to expression2 decimal places.
ASCII(string)
Returns an integer representing the ASCII code value of the leftmost character in string.
TO_CHAR(ascii_code)
Returns the char equivalent of the ASCII code argument.
DIFFERENCE(string1, string2)
Returns an integer in the range 0 through 4 indicating how many of the four digits returned by the function SOUNDEX for string1 are the same as those returned for string2. A return value of 4 indicates that the SOUNDEX codes are identical.
INSERT_STRING(string1, start, length, string2)
Returns a character string formed by deleting length characters from string1 beginning at start and then inserting string2 into string1 at start.
LEFT_STRING(string, count)
Returns the leftmost count characters from string.
REPEAT(string, count)
A character string formed by repeating string string count times.
REPLACE (string1, string2, string3)
Returns a character string formed by replacing all occurrences of string2 in string1 with string3.
RIGHT_STRING(string, count)
Returns a string formed by taking the right-hand count characters from string.
SOUNDEX (string)
Returns a string that represents the sound of the words in string; the return is data source-dependent and could be a four-digit SOUNDEX code, a phonetic representation of each word, or some other form.
SPACE(count)
Returns a character string consisting of count spaces.
DAYNAME(date)
Returns the day of the week as a string from the given date.
DAYOFWEEK(date)
Returns the day of the week as as a number: 1=Sunday, 7=Saturday.
DAYOFYEAR(date)
Returns the day of the year as a number: 1=January 1.
MONTHNAME(date)
Returns a string representing the month component of the given date.
QUARTER(date)
Returns the quarter as a number from the given date: 1=January through March, 2=April through June.
TIMESTAMPADD(interval, count, timestamp)
Returns a timestamp calculated by adding count number of intervals to timestamp.
interval can be any one of the following and must be enclosed in single quotes: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, or SQL_TSI_YEAR.
timestamp can be any of the following SQL data types: DATE, TIME, TIMESTAMP.
TIMESTAMPDIFF(interval, timestamp1, timestamp2)
Returns a number representing the number of intervals by which timestamp2 is greater than timestamp1.
interval can be any one of the following and must be enclosed in single quotes: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, or SQL_TSI_YEAR.
timestamp1 and timestamp2 can be any of the following SQL data types: DATE, TIME, TIMESTAMP.
WEEK(date)
Returns an integer from 1 to 53 representing the week of the year in date. 1=the first week of the year.
Copyright(C) 2009 Embarcadero Technologies, Inc. All Rights Reserved.
|
What do you think about this topic? Send feedback!
|