SQLCommands (ecmsdk-api 3.7.0 API)

java.lang.Object
- oracle.ifs.admin.sql.SQLCommands

```
public class SQLCommands
extends Object
```
The SQLCommands class provides methods which perform basic SQL commands. These commands allow clients to create database objects, such as tablespaces and users, query and set database parameters, and more.

Version:

1.8.1

Author:

Luis Saenz

Constructor Summary

Constructors
Constructor and Description
`SQLCommands()` Creates a new SQLCommands instance without creating a Connection to the database.
`SQLCommands(Connection con)` Creates a new SQLCommands instance, given an existing Connection to a database.
`SQLCommands(Connection con, boolean isPostgres)` Creates a new SQLCommands instance, given an existing Connection to a database.

Method Summary

All Methods Instance Methods Concrete Methods Deprecated Methods
Modifier and Type	Method and Description
`void`	`changeUserPassword(String name, String password)` Changes a database user's password.
`void`	`createTablespace(String name, int size, String init, String next, String file, boolean reuse)` Creates a tablespace in the database, writing the tablespace file in the directory specified by `file`.
`void`	`createTablespace(String name, String size, String next)` Creates a tablespace in the database without the dbf file name This will work only if the DB_CREATE_FILE_DEST parameter is initialized.
`String`	`createTempUser(String prefix)` Creates a temporary database user (schema) named T with password (identified by) T (same as the name), where T = `prefix`i where i = {0..n} and n = `Integer.MAX_VALUE - 1` Note: this algorithm will fail if there are already n database users named: `prefix`i.
`void`	`createUser(String name, String pass)` Creates a user in the database without specifying a default tablespace.
`void`	`createUser(String name, String pass, String tbs, String tempTbs)` Creates a user in the database.
`void`	`dropTablespace(String name, boolean includeContents, boolean cascade)` Drops a tablespace from the database, optionally using the "including contents" and "cascade constraints" SQL directives.
`void`	`dropUser(String name, boolean cascade)` Drops a user from the database, optionally deleting every object the user owns.
`String`	`getCharacterSet()` Returns the value of the NLS_CHARACTERSET parameter from the NLS_DATABASE_PARAMETERS table.
`String`	`getCurrentInstance()` Returns the current instance (service name, or ORACLE_SID) of this Oracle databasse connection.
`String`	`getDefaultTablespaceDirectory()` Returns the directory where the default tablespaces are stored, or null if no default tablespaces are found.
`String`	`getLanguage()` Returns the value of the NLS_LANGUAGE parameter from the NLS_DATABASE_PARAMETERS table.
`int`	`getOnlineNonSystemRollbackSegs()` Returns the number of online non-system rollback segments.
`int`	`getParameterValueAsInt(String param)` Returns the value of a parameter from the `v$parameter` table.
`String`	`getParameterValueAsString(String param)` Returns the value of a parameter from the `v$parameter` table.
`Enumeration`	`getRows(String column, String table, boolean ascending)` Returns an Enumeration containing the row values as Strings in the specified column of the specified table, optionally in in ascending order.
`Enumeration`	`getRowsWhereColumnEquals(String column, String table, String keyColumn, String keyValue)` Returns the row values of a given column in a given table where a certain column in that row has a certain value.
`Enumeration`	`getRowsWhereColumnMatches(String column, String table, String keyColumn, String keyValue)` Returns the row values of a given column in a given table where a certain column in that row matches a certain value.
`Statement`	`getStatement()` Returns the Statement used internally by this SQLCommands instance.
`String`	`getTablespaceFile(String tablespace)` Returns the full filename (including absolute path) of the tablespace file for a given tablespace, or null if the tablespace does not exist.
`Enumeration`	`getTablespaceNames()` Returns an Enumeration containing the names of every tablespace in the database, in ascending order.
`Enumeration`	`getUserNames()` Returns an Enumeration containing the names of every user in the database, in ascending order.
`Enumeration`	`getUserNames(String userName)` Returns an Enumeration containing the names of every user in the database filtered based on the username.
`String`	`getValueWhereKeyEquals(String table, String column, String key, String keyVal)` Executes a SQL query of the following form: `SELECT column FROM table WHERE key = keyVal;`
`void`	`grantConnectResource(String name)` Grants connect and resource priviledges to a user in the database.
`void`	`grantConnectResource(String name, String superUserName)` Grants connect and resource priviledges to a user in the database.
`void`	`grantPriviledgeToUser(String name, String priviledge)` Grants a priviledge to a user in the database.
`boolean`	`isContextInstalled()` Returns true if context seems to be installed in the database.
`boolean`	`isFullTextIndexingEnabled()` Gets whether full-text indexing is enabled.
`boolean`	`isIfsSchemaContextEnabled(String schema)` Deprecated. use S_Media.getContextEnabledState
`boolean`	`isPartitioningInstalled()` Returns true if the partitioning option seems to be installed in the database.
`boolean`	`isRoleGrantedToUser(String username, String role)` Determines if a user `username` has been granted the role `role`.
`void`	`setConnection(Connection con)` Sets the Connection to the database.
`void`	`setContextPreference(String preference, String attribute, String value)` Sets a context preference attribute to a String value.
`boolean`	`tablespaceExists(String tablespace)` Returns true if the given tablespace exists in the database.
`void`	`unlockAlterUserPassword(String name, String password)` Changes a database user's password and unlocks the user
`boolean`	`userExists(String name)` Returns true if the given user exists in the database.

Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

- Constructor Detail
  - SQLCommands
```
public SQLCommands()
```
    Creates a new SQLCommands instance without creating a Connection to the database.
  - SQLCommands
```
public SQLCommands(Connection con)
            throws SQLException
```
    Creates a new SQLCommands instance, given an existing Connection to a database.
    
    Parameters:
    
    con - an existing Connection to a database.
    
    Throws:
    
    SQLException - if an error occurs when using the new Connection
  - SQLCommands
```
public SQLCommands(Connection con,
                   boolean isPostgres)
            throws SQLException
```
    Creates a new SQLCommands instance, given an existing Connection to a database.
    
    Parameters:
    
    con - an existing Connection to a database.
    
    isPostgres - indicates whether the database type is Postgres.
    
    Throws:
    
    SQLException - if an error occurs when using the new Connection
- Method Detail
  - setConnection
```
public void setConnection(Connection con)
                   throws SQLException
```
    Sets the Connection to the database.
    
    Parameters:
    
    con - the Connection to use.
    
    Throws:
    
    SQLException - if an error occurs when trying to create a Statement from the new Connection.
  - getStatement
```
public Statement getStatement()
```
    Returns the Statement used internally by this SQLCommands instance.
    Note: Care should be taken NOT to close this Statement, as closing the Statement is handled by this instance of SQLCommands.
    
    Returns:
    
    the Statement used internally by this SQLCommands instance, or null if no Connection has been established.
  - createTablespace
```
public void createTablespace(String name,
                             int size,
                             String init,
                             String next,
                             String file,
                             boolean reuse)
                      throws SQLException
```
    Creates a tablespace in the database, writing the tablespace file in the directory specified by file.
    Note: tablespaces are always created with UPPERCASE names; however, the tablespace filename is user-defined in this method.
    Note: if the tablespace file already exists, it is reused and the file's previous contents are lost.
    
    Parameters:
    
    name - the name of the tablespace.
    
    size - the size (in megabytes) of the tablespace.
    
    init - the initial extent size
    
    next - the next extent size
    
    file - the filename (including absolute path) of the tablespace file.
    
    Throws:
    
    SQLException - if an error occurs.
  - createTablespace
```
public void createTablespace(String name,
                             String size,
                             String next)
                      throws SQLException
```
    Creates a tablespace in the database without the dbf file name This will work only if the DB_CREATE_FILE_DEST parameter is initialized.
    Note: tablespaces are always created with UPPERCASE names; however, the tablespace filename is generated by the database
    
    Parameters:
    
    name - the name of the tablespace.
    
    size - the size of the tablespace.
    
    next - the next extent size
    
    Throws:
    
    SQLException - if an error occurs.
  - dropUser
```
public void dropUser(String name,
                     boolean cascade)
              throws SQLException
```
    Drops a user from the database, optionally deleting every object the user owns.
    
    Parameters:
    
    name - the user to drop (case-insensitive).
    
    cascade - true if this method should delete every object owned by the user.
    
    Throws:
    
    SQLException - if an error occurs.
  - changeUserPassword
```
public void changeUserPassword(String name,
                               String password)
                        throws SQLException
```
    Changes a database user's password.
    
    Parameters:
    
    name - the user whose password to change (case-insensitive).
    
    password - the user's new password.
    
    Throws:
    
    SQLException - if an error occurs.
  - unlockAlterUserPassword
```
public void unlockAlterUserPassword(String name,
                                    String password)
                             throws SQLException
```
    Changes a database user's password and unlocks the user
    
    Parameters:
    
    name - the user whose password to change (case-insensitive).
    
    password - the user's new password.
    
    Throws:
    
    SQLException - if an error occurs.
  - dropTablespace
```
public void dropTablespace(String name,
                           boolean includeContents,
                           boolean cascade)
                    throws SQLException
```
    Drops a tablespace from the database, optionally using the "including contents" and "cascade constraints" SQL directives.
    
    Parameters:
    
    name - the tablespace to drop (case insensitive).
    
    includeContents - true if "include contents" should be used.
    
    cascade - true if "cascade constraints" should be used.
    
    Throws:
    
    SQLException - if an error occurs.
  - createUser
```
public void createUser(String name,
                       String pass)
                throws SQLException
```
    Creates a user in the database without specifying a default tablespace.
    
    Parameters:
    
    name - the user to create (case insensitive).
    
    pass - the password to assign to the user.
    
    Throws:
    
    SQLException - if an error occurs.
  - getCurrentInstance
```
public String getCurrentInstance()
                          throws SQLException
```
    Returns the current instance (service name, or ORACLE_SID) of this Oracle databasse connection.
    
    Throws:
    
    SQLException - if an error occurs.
  - createUser
```
public void createUser(String name,
                       String pass,
                       String tbs,
                       String tempTbs)
                throws SQLException
```
    Creates a user in the database.
    
    Parameters:
    
    name - the user to create (case-insensitive).
    
    pass - the password to assign to the user.
    
    tbs - the default tablespace to assign to the user.
    
    tempTbs - the temporary tablespace to assign to the user.
    
    Throws:
    
    SQLException - if an error occurs.
  - createTempUser
```
public String createTempUser(String prefix)
                      throws SQLException
```
    Creates a temporary database user (schema) named T with password (identified by) T (same as the name),
    where T = prefixi
    where i = {0..n}
    and n = Integer.MAX_VALUE - 1
    Note: this algorithm will fail if there are already n database users named: prefixi.
    
    Parameters:
    
    prefix - the prefix to use when naming the temporary user.
    
    Returns:
    
    the name of the temporary user that was created.
    
    Throws:
    
    SQLException - if a database error occurs, or if this algorithm fails as described above.
  - isIfsSchemaContextEnabled
```
@Deprecated
public boolean isIfsSchemaContextEnabled(String schema)
                                               throws SQLException
```
    Deprecated. use S_Media.getContextEnabledState
    
    Returns whether the iFS schema is enabled for interMedia. Executes the following SQL statement:
    SELECT MAX(indexed) FROM odm_media;
    Note: this implementation depends on the structure of the iFS repository tables, which are subject to change. In the future, this method may not function properly and will need to be updated.
    
    Returns:
    
    true if the iFS schema has been enabled for interMedia.
    
    Throws:
    
    SQLException - if an error occurs.
  - grantConnectResource
```
public void grantConnectResource(String name)
                          throws SQLException
```
    Grants connect and resource priviledges to a user in the database.
    
    Parameters:
    
    name - the user to grant the priviledges to (case-insensitive).
    
    Throws:
    
    SQLException - if an error occurs.
  - grantConnectResource
```
public void grantConnectResource(String name,
                                 String superUserName)
                          throws SQLException
```
    Grants connect and resource priviledges to a user in the database.
    
    Parameters:
    
    name - the user to grant the priviledges to (case-insensitive).
    
    superUserName - the superuser performing this operation.
    
    Throws:
    
    SQLException - if an error occurs.
  - grantPriviledgeToUser
```
public void grantPriviledgeToUser(String name,
                                  String priviledge)
                           throws SQLException
```
    Grants a priviledge to a user in the database.
    
    Parameters:
    
    name - the user to grant the priviledge to (case insensitive).
    
    priviledge - the priviledge to grant.
    
    Throws:
    
    SQLException - if an error occurs.
  - getParameterValueAsInt
```
public int getParameterValueAsInt(String param)
                           throws SQLException
```
    Returns the value of a parameter from the v$parameter table. This table includes all paramters from the "init.ora" file.
    
    Parameters:
    
    param - the name of the parameter to get the value of.
    
    Returns:
    
    the value of a parameter from the v$paramter table, converted to an int.
    
    Throws:
    
    SQLException - if a database error occurs, or if the given parameter does not exist in the v$parameter table, or if the value cannot be converted to an int.
  - getParameterValueAsString
```
public String getParameterValueAsString(String param)
                                 throws SQLException
```
    Returns the value of a parameter from the v$parameter table. This table includes all paramters from the "init.ora" file.
    
    Parameters:
    
    param - the name of the parameter to get the value of.
    
    Returns:
    
    the value of a parameter from the v$paramter table as a String.
    
    Throws:
    
    SQLException - if a database error occurs, or if the given parameter does not exist in the v$parameter table.
  - getTablespaceFile
```
public String getTablespaceFile(String tablespace)
                         throws SQLException
```
    Returns the full filename (including absolute path) of the tablespace file for a given tablespace, or null if the tablespace does not exist.
    Note: tablespace names are case sensitive.
    
    Parameters:
    
    tablespace - the name of the tablespace to get the file for.
    
    Returns:
    
    the full filename (including absolute path) of the tablespace file for a given tablespace, or null if the tablespace does not exist.
    
    Throws:
    
    SQLException - if a database error occurs.
  - getDefaultTablespaceDirectory
```
public String getDefaultTablespaceDirectory()
                                     throws SQLException
```
    Returns the directory where the default tablespaces are stored, or null if no default tablespaces are found.
    Note: the returned path includes the final file_separator character: i.e. "C:\ORACLE\ORADATA\ONI\" on Windows NT,
    "/data/oracle/oradata/oni/" on Solaris.
    
    Returns:
    
    the directory where the 'USERS' tablespace or the 'SYSTEM' tablespace is stored (if 'USERS' is not found), or null if neither of these tablespaces are found.
    
    Throws:
    
    SQLException - if a database error occurs.
  - getCharacterSet
```
public String getCharacterSet()
                       throws SQLException
```
    Returns the value of the NLS_CHARACTERSET parameter from the NLS_DATABASE_PARAMETERS table.
    
    Returns:
    
    the value of the NLS_CHARACTERSET parameter, or null if it has no value.
    
    Throws:
    
    SQLException - if a database error occurs.
  - getLanguage
```
public String getLanguage()
                   throws SQLException
```
    Returns the value of the NLS_LANGUAGE parameter from the NLS_DATABASE_PARAMETERS table.
    
    Returns:
    
    the value of the NLS_LANGUAGE parameter, or null if it has no value.
    
    Throws:
    
    SQLException - if a database error occurs.
  - setContextPreference
```
public void setContextPreference(String preference,
                                 String attribute,
                                 String value)
                          throws SQLException
```
    Sets a context preference attribute to a String value.
    
    Parameters:
    
    preference - the context preference to modify.
    
    attribute - the specific attribute to set.
    
    value - the value to set the parameter to.
    
    Throws:
    
    SQLException - if a database error occurs.
  - isContextInstalled
```
public boolean isContextInstalled()
                           throws SQLException
```
    Returns true if context seems to be installed in the database. The way this method checks for context is pretty simple: it just queries the view 'CTX_VERSION' in dba_views. If the view exists, then context is assumed to be installed.
    
    Returns:
    
    true if context seems to be installed.
    
    Throws:
    
    SQLException - if a database error occurs.
  - isFullTextIndexingEnabled
```
public boolean isFullTextIndexingEnabled()
                                  throws SQLException
```
    Gets whether full-text indexing is enabled.
    
    Returns:
    
    whether full-text indexing is enabled
    
    Throws:
    
    SQLException - if the operation fails
    
    (Unpublished)
  - isPartitioningInstalled
```
public boolean isPartitioningInstalled()
                                throws SQLException
```
    Returns true if the partitioning option seems to be installed in the database.
    The way this method checks for partitioning is pretty simple: it just queries the parameter 'partitioning' in v$option. If the value is 'true', then partitioning is assumed to be installed.
    
    Returns:
    
    true if partitioning seems to be installed.
    
    Throws:
    
    SQLException - if a database error occurs.
  - tablespaceExists
```
public boolean tablespaceExists(String tablespace)
                         throws SQLException
```
    Returns true if the given tablespace exists in the database. This method simply checks for the existance of the tablespace in the dba_tablespaces table.
    Note: the tablespace parameter is converted to uppercase.
    
    Parameters:
    
    tablespace - the tablespace to check the existence of.
    
    Returns:
    
    true if the given tablespace exists in the database.
    
    Throws:
    
    SQLException - if a database error occurs.
  - getOnlineNonSystemRollbackSegs
```
public int getOnlineNonSystemRollbackSegs()
                                   throws SQLException
```
    Returns the number of online non-system rollback segments. For iFS, this number must be at least 1.
    
    Returns:
    
    the number of online non-system rollback segments.
    
    Throws:
    
    SQLException - if a database error occurs.
  - isRoleGrantedToUser
```
public boolean isRoleGrantedToUser(String username,
                                   String role)
                            throws SQLException
```
    Determines if a user username has been granted the role role.
    Note: the given username and role name are always converted to UPPERCASE.
    
    Returns:
    
    true if the user has been granted the given role.
    
    Throws:
    
    SQLException - if a database error occurs.
    
    (Unpublished)
  - getTablespaceNames
```
public Enumeration getTablespaceNames()
                               throws SQLException
```
    Returns an Enumeration containing the names of every tablespace in the database, in ascending order.
    
    Returns:
    
    an Enumeration containing the names of every tablespace in the database, or null if no tablespaces are found.
    
    Throws:
    
    SQLException - if a database error occurs.
  - getUserNames
```
public Enumeration getUserNames()
                         throws SQLException
```
    Returns an Enumeration containing the names of every user in the database, in ascending order.
    
    Returns:
    
    an Enumeration containing the names of every user in the database, or null if no users are found.
    
    Throws:
    
    SQLException - if a database error occurs.
  - getUserNames
```
public Enumeration getUserNames(String userName)
                         throws SQLException
```
    Returns an Enumeration containing the names of every user in the database filtered based on the username.
    
    Parameters:
    
    userName - user name string used to filter the list of users
    
    Returns:
    
    an Enumeration containing the names of every user in the database, or null if no users are found.
    
    Throws:
    
    SQLException - if a database error occurs.
  - getRows
```
public Enumeration getRows(String column,
                           String table,
                           boolean ascending)
                    throws SQLException
```
    Returns an Enumeration containing the row values as Strings in the specified column of the specified table, optionally in in ascending order.
    
    Returns:
    
    an Enumeration containing row values as Strings for the specified column of the specified table.
    
    Throws:
    
    SQLException - if a database error occurs.
  - getRowsWhereColumnEquals
```
public Enumeration getRowsWhereColumnEquals(String column,
                                            String table,
                                            String keyColumn,
                                            String keyValue)
                                     throws SQLException
```
    Returns the row values of a given column in a given table where a certain column in that row has a certain value. Executes the following SQL command:
    SELECT column FROM table WHERE keyColumn = keyValue
    
    Returns:
    
    an Enumeration containing row values as Strings.
    
    Throws:
    
    SQLException - if a database error occurs.
  - getRowsWhereColumnMatches
```
public Enumeration getRowsWhereColumnMatches(String column,
                                             String table,
                                             String keyColumn,
                                             String keyValue)
                                      throws SQLException
```
    Returns the row values of a given column in a given table where a certain column in that row matches a certain value. Executes the following SQL command:
    SELECT column FROM table WHERE keyColumn like %keyValue%
    
    Returns:
    
    an Enumeration containing row values as Strings.
    
    Throws:
    
    SQLException - if a database error occurs.
  - getValueWhereKeyEquals
```
public String getValueWhereKeyEquals(String table,
                                     String column,
                                     String key,
                                     String keyVal)
                              throws SQLException
```
    Executes a SQL query of the following form:
    SELECT column FROM table WHERE key = keyVal;
    
    Parameters:
    
    table - the table to select from.
    
    column - the column to select.
    
    key - the column to use as the key for the where clause.
    
    keyVal - the String value to use in the where clause.
    
    Returns:
    
    a String representation of the result of the SQL query.
    
    Throws:
    
    SQLException - if a database error occurs.
  - userExists
```
public boolean userExists(String name)
                   throws SQLException
```
    Returns true if the given user exists in the database.
    Note: the name parameter is converted to uppercase.
    
    Parameters:
    
    name - the name of the user to check for.
    
    Returns:
    
    true if the given user exists in the database.
    
    Throws:
    
    SQLException - if a database error occurs.

Class SQLCommands

Constructor Summary

Method Summary

Methods inherited from class java.lang.Object

Constructor Detail

SQLCommands

SQLCommands

SQLCommands

Method Detail

setConnection

getStatement

createTablespace

createTablespace

dropUser

changeUserPassword

unlockAlterUserPassword

dropTablespace

createUser

getCurrentInstance

createUser

createTempUser

isIfsSchemaContextEnabled

grantConnectResource

grantConnectResource

grantPriviledgeToUser

getParameterValueAsInt

getParameterValueAsString

getTablespaceFile

getDefaultTablespaceDirectory

getCharacterSet

getLanguage

setContextPreference

isContextInstalled

isFullTextIndexingEnabled

isPartitioningInstalled

tablespaceExists

getOnlineNonSystemRollbackSegs

isRoleGrantedToUser

getTablespaceNames

getUserNames

getUserNames

getRows

getRowsWhereColumnEquals

getRowsWhereColumnMatches

getValueWhereKeyEquals

userExists