borland Packages Class Hierarchy dx.sql.dataset Package
java.lang.Object +----com.borland.dx.sql.dataset.Database
Constructors Properties Methods Event Listeners
Implements Designable, Serializable
The Database
component is a required element of any application accessing data stored
on a SQL server. It encapsulates a database connection through JDBC to the SQL server and
also provides lightweight transaction support.
When used with a QueryDataSet
or ProcedureDataSet
component, data is retrieved from the external database into a local cache (DataSet
) on the user's system. All row data for a particular DataSet
is cached on the user's system as a single unit. Changes made to the local copy of the data in the DataSet
are internally recorded as deletes, inserts and updates. When all changes to the DataSet
are complete, you then save the changes back to the original database by calling the saveChanges()
method with one or more DataSet
components.
You can connect several QueryDataSet
or ProcedureDataSet
components to a Database
component, however, some SQL servers allow only one active query at a time on a connection. Check your server documentation to see if this is applicable to the SQL server you are accessing.
The Database
component has an associated connection
property that stores the connection properties of userName
, password
, and connectionUrl
of the database.
These properties are stored in the ConnectionDescriptor
object. When the necessary properties for the connection have been supplied, the connection
can be opened explicitly or automatically. When explicitly connecting, use the
openConnection()
method. The connection is opened automatically when you explicitly open the DataSet
, or when a UI control requests data that is obtained through the database connection.
The information stored in the ConnectionDescriptor
object is accessible through the user interface by inspecting the connection
property of a Database
object. This displays the Connection property editor.
If all needed properties have been set, a connection is attempted when any of the following situations occur:
openConnection()
method call is made
QueryDataSet
that relies on data from this connection requests it
When attempting to make the connection to the Database
, the appropriate driver needed to access the remote server is loaded. If the remote server driver information is not available in the system registry, you can specify the driver using the addDriver()
method, in the ConnectionDescriptor
object, or in the Connection property editor.
It is strongly recommended that you include all DataExpress components (database connections, queries, DataStores
, and so on) in a DataModule
. The DataModule
is a specialized container for data access components and their properties. Consolidation of these components in a single container clarifies an application's design and increases the reusability of the data access components.
The isolation level, specified by the transactionIsolation
property, is used when saving data changes back to the external database table.
When you need special transaction logic, use the saveChanges(com.borland.dx.dataset.DataSet[], boolean, boolean, boolean)
method. By setting its final parameter resetPendingStatus
to false, this method offers the flexibility of not resetting the pending resolved status bits through the call to the saveChanges
method. When you want to reset the pending resolved status bit, call the resetPpendingStatus
method. This allows you, for example, to save changes made to several DataSets
in a single transaction, and to rollback all changes while still retaining all the changed data in both DataSets
.
When designing your application that involves prompting for a password, set the promptPassword
property to true, then call the openConnection()
method for your database when you want the username/password dialog to appear. If you want the username/password dialog to appear as soon as your application loads, call the openConnection()
method at the end of the main frame's jbInit()
method.
If the user cancels the password dialog, your application can detect a DataSetException
of type CONNECTION_DESCRIPTOR_NOT_SET and take the appropriate action. The application could either terminate or disable data-access functions.
When you no longer need a Database
connection, you should explicitly call the Database.closeConnection()
method in your application. This ensures that Database
classes which hold references to JDBC connections are automatically closed when the Database
object is garbage collected.
When debugging JDBC connection issues, you can add the following line of code to your application before the line of code that executes your query or stored procedure:
java.sql.DriverManager.setLogStream(System.out);
This generates (verbose) output from the JDBC driver that shows what is happening and in
what sequence. Examining this output may help determine the source of JDBC connection
related problems in your application.
To turn off the debugging output, use the following code:
java.sql.DriverManager.setLogStream(null);
If you're connecting to data using ODBC drivers under the MicroSoft Windows operating system, enable ODBC logging through the Control Panel program. The calls that take place are displayed, enabling you to track what is being sent to the ODBC driver.
For more information and examples on connecting to a database server, see "Connecting to a database" in the Database Application Developer's Guide.
public Database()Creates a
Database
object and sets the transaction isolation level to ReadCommitted
.
public final boolean getAutoCommit() public final void setAutoCommit(boolean enable)Specifies whether
autoCommit
is enabled (true) or not (false). If true, each SQL statement is executed and implicitly committed as an individual transaction. If false, all SQL statements are executed in a single transaction that is explicitly terminated by a commit()
or rollback()
.
Where a SQL statement returns a result set that is stored in a QueryDataSet
or a ProcedureDataSet
, the statement completes when the last row of the result set has been retrieved.
This property defaults to true. On failure, it throws a DataSetException
or a SQLException
as applicable.
public ConnectionDescriptor getConnection() public void setConnection(ConnectionDescriptor connectionDescriptor)The
connection
property is a complex property, containing all the information needed by the Database
to establish a JDBC connection (including a Universal Resource Locator, a user name, and a password). The values are stored in a ConnectionDescriptor
object.
You can read from or write to this property at any time, however, the new ConnectionDescriptor
will not be applied until the next time the Database
is explicitly opened.
public String getDatabaseName() public void setDatabaseName(String name)The name used to get a JDBC connection from JNDI.
public char getIdentifierQuoteChar() public void setIdentifierQuoteChar(char quoteChar)Specifies the server's quote character. Setting this property to '\0' indicates that quoted identifiers should not be used in generated queries.
public final java.sql.Connection getJdbcConnection() public final void setJdbcConnection(Connection connection)Specifies the connection object to use. Use the setter method when a JDBC connection has already been established and you want DataExpress components to share that connection. To use an explicit
Connection
object when connecting to a Database
:
java.sql.Connection
object
Connection
object
Database
Connection
can have. If the connection is closed while the DataSet
is still using it, unknown behavior can result. Additionally, there may be issues with servers that only allow one active query per Connection
.
Use the getter of this property to obtain the JDBC Connection
object.
public final int getMaxStatements()Read-only property that returns the maximum number of statements that the server supports.
public final synchronized DatabaseMetaData getMetaData()Read-only property that returns the metadata for the
Database
object. Metadata is that
information that describes the database, for example, a listing of column names and data types. On failure,
this method throws a SQLException
.
For more information on metadata, see "Working with columns" in the Database Application Developer's Guide.
public boolean isOpen()Read-only property that returns whether the database connection is open.
public final synchronized RuntimeMetaData getRuntimeMetaData() public final synchronized void setRuntimeMetaData(RuntimeMetaData runtimeMeta)This property is used internally by other
com.borland
classes. You should never use this property directly.
public final int getSQLDialect() public final void setSQLDialect(int dialect)Specifies the SQL dialect that your server is based on. This is not required, but may be useful. Valid values for
dialect
are defined in SQLDialect
.
public final int getTransactionIsolation() public final synchronized void setTransactionIsolation(int level)Specifies the transaction isolation level for the
Database
object.
A single transaction is used when making changes to the external database table.
On failure, this method throws a SQLException
.
Accepted values for the transaction isolation are listed under java.sql.connection
.
The setTransactionIsolation()
method checks to see if the driver supports transactions and
if so, if the specified isolation level is set. If it isn't, a higher level of isolation (more restrictive) is attempted according to the following table:
Level failed | Next level attempted |
---|---|
TRANSACTION_NONE | none |
TRANSACTION_READ_UNCOMMITTED | TRANSACTION_READ_COMMITTED |
TRANSACTION_READ_COMMITTED | TRANSACTION_REPEATABLE_READ |
TRANSACTION_REPEATABLE_READ | TRANSACTION_SERIALIZABLE |
TRANSACTION_SERIALIZABLE | DataSetException thrown |
If the connection is open, this property may be set at a higher level than you selected. If a supported isolation level is not found, a DataSetException
of TransactionIsolationLevelNotSupported
is thrown. This method
also throws a SQLException
as appropriate.
You can change the transaction isolation level only on a newly opened connection--attempting to
do so in the middle of a transaction will generate a SQLException
.
These transaction isolation rules apply when working with the Database
component in the JBuilder UI designer as well as in manually in code.
When working in the UI Designer, the Exception
generated when setting the transaction isolation level on an open connection is suppressed, however, no code is written for the changed setting. To set the transaction isolation level in the UI Designer, set it before the connection is opened.
java.sql.DatabaseMetaData#supportsTransactionIsolationLevel
public boolean isUseCaseSensitiveId() public void setUseCaseSensitiveId(boolean caseSensitive)Controls whether an identifier in an SQL string that doesn't have quotes is treated as case sensitive by the database.
If this property is not set, it defaults to the metadata value reported by the JDBC driver.
public boolean isUseCaseSensitiveQuotedId() public void setUseCaseSensitiveQuotedId(boolean caseSensitive)Controls whether a quoted identifier in an SQL string is treated as case sensitive by the database.
If this property is not set, it defaults to the metadata value reported by the JDBC driver.
public boolean isUseSchemaName() public void setUseSchemaName(boolean useSchemaName)Determines whether the user name (that was used to connect to the
Database
) should be included with all metadata discovery requests sent to the server and defaults to false. This property does not apply to queries executed against the server.
For more information on metadata, see "Obtaining Metadata" in the Database Application Developer's Guide.
public boolean isUseSetObjectForStreams() public void setUseSetObjectForStreams(boolean useSetObjectForStreams)Controls which method is used to save a
BinaryStream
value when resolving data. If true, the setObject()
method is used. If false, setBinaryStream()
is used on the PreparedStatement
. This property defaults to false for the ODBC bridge and true for all other drivers.
public boolean isUseSetObjectForStrings() public void setUseSetObjectForStrings(boolean useSetObjectForStrings)Controls which method is used to save a String value when resolving data. If true, the
setObject()
method is used. If false, setString()
is used on the PreparedStatement
. This property defaults to true.
public boolean isUseSpacePadding() public void setUseSpacePadding(boolean useSpacePadding)Controls if a CHAR field should be space padded or not. This can sometimes help to work around certain database driver bugs.
public boolean isUseStatementCaching() public void setUseStatementCaching(boolean useStatementCaching)Controls whether the JDBC statements should be reused.
public boolean isUseTableName() public void setUseTableName(boolean useTableName)Determines whether the table name should be prepended to all field names in all queries executed against SQL server data. This property defaults to false.
public boolean isUseTransactions() public void setUseTransactions(boolean useTransactions)Controls if
saveChanges
should use transactions. By default, it will use transactions if the associated JDBC driver returns true for its implementation of DatabaseMetaData.supportsTransactions()
.
public final void addDriver(String driver)Adds the specified
driver
name to the jdbc.drivers property in the system property list (if it hasn't already been registered) and instructs JDBC to load the driver prior to attempting the database connection.
Currently, note that the JDBC-ODBC driver is always added to the property setting.
driver
public static void addDriver(String driver, boolean multiple)Adds the specified
driver
name to the jdbc.drivers property in the system property list (if it hasn't already been registered) and instructs JDBC to load the driver prior to attempting the database connection.
Currently, note that the JDBC-ODBC driver is always added to the property setting.
driver
multiple
public static void addDrivers(String driver)Adds the specified
driver
name(s) to the jdbc.drivers property in the system property list (if they haven't already been registered) and instructs JDBC to load the driver(s) prior to attempting the database connection.
Currently, note that the JDBC-ODBC driver is always added to the property setting.
driver
public final synchronized void closeConnection()Closes an active database connection by setting the JDBC
Connection
object's transaction mode to AutoCommit
, invoking the Connection
object's close()
method, then resetting the Connection
object to null. On failure, this method throws a SQLException
.
You should explicitly call this method in your application
when you no longer need a Database
connection to assure that all connection objects (Database
, Connection
and so on) are properly released when garbage collection occurs.
public void commit()Commits changes back to the server.
public final synchronized CallableStatement createCallableStatement(String query)Opens a database connection, parses a SQL stored procedure query string that can contain zero or more "?" parameters, prepares, and returns it in a
CallableStatement
. A SQL statement with IN and/or OUT parameters can be pre-compiled and stored in a CallableStatement
object.
Check the documentation for your driver software to see if callable statements are supported by your driver.
public final synchronized PreparedStatement createPreparedStatement(String query)Opens a database connection, parses a query string, prepares it, and returns it in a
PreparedStatement
object. This PreparedStatement
object can then be used to efficiently execute the statement, instead of the Statement
object which must be compiled at each execution. A PreparedStatement
object is used in situations where the exact same query is executed frequently. A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement
object.
To the end user of your application, there is no difference between using a pre-compiled PreparedStatement
object or a non-compiled equivalent. The developer however, may find slight differences when exception events are thrown. Precompiling a query into a PreparedStatement
can offer improved performance however not all queries can be precompiled and not all drivers support precompilation. Check the documentation for your driver software to see if precompilation is possible and the conditions under which it is possible.
On failure, this method throws a SQLException
or DataSetException
as appropriate.
public final synchronized Statement createStatement()Opens the
Connection
to the database and returns a Statement
object. The Statement
object returned can be used for executing a static query (a query without parameters). On failure, this method throws a SQLException
.
public int executeStatement(String statementString)Executes the SQL statement specified by the
statementString
parameter. Use this method to execute arbitrary SQL statements, including DDL, that do not yield a ResultSet
.
This method does not execute parameterized SQL statements. Equivalent methods that permit parameter passing are available as static methods of the
QueryProvider
(executeStatement()
) and ProcedureProvider
(callProcedure()
)
components. For more information on which of these methods to use, see "Executing queries" in the About section of the QueryDescriptor
class.
For example,
executeStatement("create table my_table ( name varchar(20) )");
protected void finalize()Closes the connection to the database server and releases all objects for garbage collection.
public String makeTableIdentifier(String catalogName, String schemaName, String tableName)Internal utility function which returns an identifier based on the
catalogName
, schemaName
, and tableName
passed to it.
public final synchronized void openConnection()Connects to a driver using the
ConnectionDescriptor
object property values of userName
, password
and connectionURL
. Each registered driver is loaded until one is found that can process the specified URL. The transaction isolation level is set and DatabaseMetaData
object is obtained. The DatabaseMetaData
object contains information about the Database, for example, the list of column names, data types and views in a database.
On failure, this method throws a DataSetException
.
public void resetPendingStatus(DataSet[] dataSets, boolean markResolved)Resets the pending status bits. The
saveChanges(com.borland.dx.dataset.DataSet[], boolean, boolean, boolean)
method allows you to call saveChanges
without resetting the pending resolved status bits. In such cases, use the resetPendingStatus
method to reset the pending resolved status bits when appropriate. This feature is useful when you want special transaction logic, for example, when changes made to several DataSets
should be saved in a single transaction. This method allows you to rollback all the changes and still have all the updates in the DataSets
.
dataSets
DataSet
objects to reset status bits of.
markResolved
public StorageDataSet resultSetToDataSet(StorageDataSet dataSet, ResultSet result)The data from the result set of a JDBC query or prepared statement is added to the specified
DataSet
. The DataSet
must be closed before calling this method. This method may add columns to the DataSet
. On error, this method throws a DataSetException
.
dataSet
StoragedataSet
that the ResultSet
data will be added to.
result
ResultSet
.
public StorageDataSet resultSetToDataSet(ResultSet result)Creates and returns a
StorageDataSet
object that contains data from the result set of a JDBC query or prepared statement. On failure, this method throws a SQLException
or a DataSetException
as appropriate.
result
ResultSet
.
public void rollback()Rolls back changes made to data since the last
commit()
or rollback()
operation.
public final void saveChanges(DataSet dataSet)Saves changes made to the local copy of the data specified by
DataSet
back to the data source. Changes to the local data are done through DataSet
methods, either
programmatically or using a data-aware control.
dataSet
DataSet
that contains the modified data.
public final void saveChanges(DataSet[] dataSets)Saves changes made to data contained in the
DataSet
components listed in the array. Changes to the local data are done programmatically or using a data-aware control.
dataSets
DataSet
components that contain modified data to save back to the data source.
public final void saveChanges(DataSet[] dataSets, boolean doTransactions)Saves changes made to one or more
DataSet
objects to the database data source.
dataSets
DataSet
components that contain modified data to save back to the data source.
doTransactions
public final void saveChanges(DataSet[] dataSets, boolean doTransactions, boolean postEdits, boolean resetPendingStatus)Saves changes made to one or more
DataSet
changes to the database.
dataSets
DataSet
components to save changes for.
doTransactions
postEdits
resetPendingStatus
saveChanges
method. For more information see the resetPendingStatus()
method.
public boolean storesLowerCaseIdentifiers()Returns whether the database stores identifiers in lowercase (true) or not (false).
public boolean storesUpperCaseIdentifiers()Returns whether the database stores identifiers in uppercase (true) or not (false).
public final void addConnectionUpdateListener(ConnectionUpdateListener listener) public final void removeConnectionUpdateListener(ConnectionUpdateListener listener)