5. Database Configurations
A database configuration groups together database related information such as the type of database, driver properties, login parameters, connection pool settings, and other pertinent information. A database configuration is only used at runtime for persisting business objects. It is not used to configure the mapping tool to connect to a database. VBSF allows you to define multiple database configurations for different target databases or different types of clients.
You must create at least one database configuration per application schema. To create a database configuration click on the "DB Configurations" folder in the tree, and chose the Object->New menu option. When prompted, enter a name for the configuration being created and click OK. A DB Configuration object is then created and added under "DB Configurations" folder.
To edit the DB Configuration select it in the tree so its corresponding form will be displayed on the right hand side of the split pane as shown below. The DB Configuration form is divided into three tabbed panels: Database, General, and Advanced.
The Database panel is used to enter database and driver type information.
The Database panel is used to specify the following options:
Driver. Enter the fully qualified class name of the database driver.
The default is the JDBC/ODBC bridge. The JDBC driver may be left blank if assignment of a JDBC driver
occurs at runtime via the Database.loadDriver() method.
Service. Select the starting class name of any of the supported database services. Currently the only implemented database service is the JDBC service with the starting class name com.objectmatter.bsf.db.jdbc.DBAConnection.
DB Type. Select from any of the following types: AUTO-DETECT, GENERIC, MSACCESS, SQLSERVER, ORACLE, SYBASE, INFORMIX, DB2, OPENINGRES, INTERBASE, and SOLID. The default is AUTO-DETECT which attempts to detect the database type at runtime and if unsuccessful sets the type to GENERIC.
Isolation Level. Select from any of the following transaction levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default and recommended setting is READ COMMITTED.
Properties. Enter any required specific driver properties using property=value pairs separated with spaces, tabs, or lines (carriage returns). When specifying properties, the username and password settings entered in the General tab (if any) are ignored, therefore, properties must be supplied for the username and password.
The General options panel is used to specify the database security properties, pre-defined login information, and transaction settings.
Properties Panel
The properties panel allows you to specify database the following options.
Default. Check this option if this is the default db configuration. This setting is used at runtime to determine which database configuration to use when when a db configuration name is not specified. If multiple database configurations are marked as default, the first one encountered is the one actually used at runtime.
Exposed. Check this option if a client is allowed to inspect all settings in this database configuration.
Updateable. Check this option if a client is allowed to modify any settings in this database configuration.
Expose DB metadata. Check this option if a client is allowed to inspect the DatabaseMetaData pertaining to the current client connection.
Expose connection. Check this option if a client is allowed to obtain a reference of the underlying JDBC connection object for direct manipulation.
Client can supply connection. Check this option if a client is allowed to supply a JDBC connection object for use by the persistence runtime.
Login Panel
The login panel allows you to specify whether the client can login to the database by specifying the URL and using a database level username and password, or whether the persistence layer should login all clients using a pre-determined URL, username and password.
Client logins. Check this option if clients will be allowed to supply the database URL, and database level username and passwords.
URL. Enter a predefined URL to connect to if clients are not allowed to login, or if a client does not specify a URL. Enter a URL is mandatory if a connection pool is setup.
User name. Enter a predefined username to use to connect to the database if clients are not allowed to login, or if a client does not specify a username and password.
Password. Enter a predefined password to connect to the database if clients are not allowed to login, or if a client does not specify a username and password.
| About Security |
|---|
| One of the advantages of implementing a
middleware based application is that client-wide security measures can be implemented at
the application server level. VBSF can help ensure the security of your data by
encapsulating the database. A major security risk of using type 3 and 4 JDBC drivers from applet clients is that the database must be exposed to the client. This might not be a problem in an intranet application, but could pose major security risks in an internet application. If a database is exposed to the world there is always a chance that someone could guess or deduct a database level password and gain access to the database. This means a client could retrieve sensitive data, or worst, issue destructive SQL. VBSF, on the other hand, allows you to create applications that fully encapsulate the underlying database. Clients are not required to log into the database using database level username and passwords. A server object can log-in and obtain a connection for the client under a predefined username and password with access restrictions at the database level. VBSF also supports establishing a dynamically growable connection pool upon startup, which has the added benefit of improved performance. More importantly, all object-relational mapping schema is defined at the server. Clients only see the object representations of the underlying data, therefore only the operations defined in the business classes can be performed. A client cannot execute arbitrary SQL commands that could potentially retrieve or alter sensitive data. |
The transactions panel allows you to specify options related to transactions.
Leave auto-commit enabled. Check this option to leave the default auto-commit mode enabled. Most applications require transaction control, so under most circumstance this option should be left unchecked.
Disable automatic transactions. Check this option to disable the automatic transaction feature of VBSF. When the automatic transaction feature is enabled, the persistence layer checks to see if a transaction is in progress each time a database update method is called. If no transaction is in progress, then one is started automatically. If the persistence layer started the transaction, it will automatically commit it once the database update is complete, or roll it back if an error occurs. The automatic transaction feature applies only when a transaction is not started by the client application before a database update command is issued. When a transaction is explicitly started by a client, the persistence layer does not issue any start or commit transaction commands. This behavior is independent of how this option is set, so you can always manually control transactions.
Because objects should always be written to the database in the context of a transaction, automatic transactions are provided as convenience and as a "safety net". It is recommended that you leave the automatic transaction feature enabled, unless you have very specific reasons not to do so (e.g. when using another middleware product to manage connections and transactions). If you disable automatic transactions and you do not explicitly start and commit transactions, objects WILL NOT be written to the database even though the persistence layer will report that all is normal by returning the correct number of rows updated and by never throwing any exceptions.
Automatic transactions are enabled by default.
Automatic discard of cache. Check this option to discard the cache every time a transaction is committed or rolled-back. Even if you only use list methods, when an object is passed back to the Database class for the purpose of updating it, it is automatically cached. Enabling this option, will automatically remove all objects that were automatically cached by VBSF during an update session. In the Enterprise Edition this option applies only to the transactional cache. The Global shared cache is not affected. In general, this option is not recommended in the Enterprise Edition.
Automatic undo on rollback. Check this option to enable automatic undos on rollbacks. When automatic transactions are enabled, the persistence layer automatically performs a rollback after a BODBException if the the transaction was started by the persistence layer. (Note: If you manually control transactions, you must manually perform a rollback after a database exception.) When a rollback is automatically performed, you can optionally undo all changes to all objects involved in the transaction. In other words, if this option is enabled, when a rollback is automatically performed the state of all objects involved in the transaction will revert back to the state they had when they were loaded from the database, or as of the last successful update in the database. This option is disabled by default.
The Advanced options panel is used to specify time-outs, specify SQL statement options, set up a connection pool, and provide specific information about boolean support.
Time-out periods
Enter any desired login and query time-out periods in seconds, with zero meaning unlimited. Please note that many databases do not support setting either or both options. In that case you will receive an exception when you attempt to login or query the database.
Statements
VBSF automatically adjusts the values of the fields in this panel at runtime from the information obtained by the driver, so it is usually not necessary to change these values. The Maximum Active Statements is the number of maximum active statements that the driver and database can maintain active per connection, with zero meaning unlimited. This information is necessary for the prepared statement caching mechanism built into VBSF. If this number is zero (the default), then VBSF will get the actual maximum amount of active statements from the driver. Setting this field to a number greater than zero, or to -1, overrides the information provided by the driver. You would normally only want to override the number provided by the driver to set the number of statements to one, or to disable statement caching altogether by setting the number to -1.
A connection pool is a set of opened connections that are re-used by clients. Setting up a connection pool improves performance by eliminating client connection times. All initial connections in the pool are opened during server start up. Pool connections are opened using the URL, username, and password specified in the General options panel. To specify a connection pool simply enter the initial number of connections the pool will contain. The pool will dynamically grow if necessary up to the maximum number of connections specified. If the maximum is left at at zero, then the pool will grow as necessary with no upper limit. If the maximum number of connections is specified without a time-out period and the maximum number is reached at runtime, then clients will be denied a connection until the next connection in the pool is freed.
A time-out wait period and time-out threshold can also be defined. The time-out period defines the amount of time in milliseconds that a client can wait for a connection to become available if all the connections in the pool are in use. If a connection becomes available within this time period, the client will be assigned the connection without an exception being thrown. If the period expires without a connection becoming available, then the client will receive an exception. A value of zero, the default, means that the client does not wait and receives an exception immediately.The time-out threshold is the number of connections in the pool required for the time-out period to become effective when no more connections are available but before attempting to add a new connection to the pool. The default is zero. This setting is only meaningful if the time-out period is greater than zero. If the maximum number of connections is greater than zero and the time-out threshold is zero, then the time-out will apply only when the maximum number of connections is reached. If the maximum number of connections is greater than zero and the time-out threshold is greater than zero but less than the maximum number of connections, then the time-out will apply at the time-out threshold but before the maximum number of connections is reached, so VBSF will wait for a connection to become free during the time-out period and add a new one to the pool only if a connection did not become free during the timeout period. If the maximum number of connections is zero (unlimited) and the time-out threshold is greater than zero, then when the number of connections in the pool reaches the time-out threshold, VBSF will wait for a connection to become free before adding a new one to the pool. This last setting allows an application to ensure that connections never run out while still controlling the pool growth by means of forcing a wait period before allocating a new connection to the pool.
The table below provides several examples of different parameters that may be
specified for the connection pool and
provides more details as to how VBSF interprets those parameters:
initial# connections,# connections, |
VBSF Behavior |
| 1,0,0,0 | One initial connection object is created in the pool and the pool can grow to an unlimited number of objects. No timeout or timeout threshold is specified. |
| 5,25,0,0 | Five
initial connection objects are created in the pool and the pool can grow to
25 objects. Once the pool size limit of 25 is reached the client will
receive a BOMaxConnectionsReachedException.
Additional clients that request connections will receive the same
exception until a connection becomes available again. No timeout or
timeout threshold is specified. |
| 5,25,100,0 | Five
initial connection objects are created in the pool and the pool can grow to
25 objects. Once the pool size limit of 25 is reached the client will wait
for 100msec. If a connection becomes available within 100msec the client
will receive the connection. If no connection becomes available the client
will receive a BOMaxConnectionsReachedException.
No timeout threshold is specified. |
| 5,25,100,10 | Five
initial connection objects are created in the pool and the pool can grow to
25 objects. Once the pool size reaches 10 objects (the timeout threshold)
a client will wait for 100msec to see if an existing in-use connection
becomes available. If a connection
becomes available within 100msec the client will receive that connection.
If no connection becomes available during the 100msec wait period a new
connection will be added to the pool and returned to the client.
Once the pool size limit
of 25 is reached the client will wait for 100msec. If a connection becomes
available within 100msec the client will receive the connection. If no
connection becomes available the client will receive a |
| 5,0,100,10 | Five initial connection objects are created in the pool and the pool can grow to an unlimited number of objects. Once the pool size reaches 10 objects (the timeout threshold) a client will wait for 100msec to see if an existing in-use connection becomes available. If a connection becomes available within 100msec the client will receive that connection. If no connection becomes available during the 100msec wait period a new connection will be added to the pool and returned to the client. Since the pool can grow forever this setting allows an application to ensure that connections never run out while still controlling the pool growth by means of forcing a wait period before allocating a new connection to the pool. |
Determining the correct pool parameters for an application is based a lot on
trial and error. Start with values that appear appropriate for the expected
application load and monitor the response of the system under different loads.
You can monitor the status of connection pools by turning on VBSF debugging at the MONITOR level.
You may also specify the name of a test table and an optional test period to enable checking and automatic replacement of all the connections in the pool for stale connections. If both a test table and a test period are specified, then VBSF will periodically perform a SELECT COUNT(*) FROM TestTable query in all the unused connections in the pool. If the query fails for any reason, an attempt is made to replace it with a new connection. If a new connection cannot be obtained (e.g., the database is still down), then the connection is marked as bad, and another attempt to replace it will be performed during the next test cycle. Whenever the database comes back online, the pool will be automatically rebuilt in the next test cycle. For best performance, the test table should be dedicated for this role. That is, it should only have one dummy column and no rows.
The
test period defines the amount time between test cycles in milliseconds. The default test
period is 5 minutes (300000 msec). The minimum test period is 1 minute (60000 msec). If
a non-zero number below the minimum is specified in this field, then VBSF will
automatically revert to a default test period of 5 minutes at runtime. If the
test period is blank or zero and a test table is specified, then the automatic
checking feature is disabled. In that case your application must explicitly
start a test cycle by using the Server.checkAllPoolConnections()
method. For additional details see the API Reference for this method.
Boolean support
This section is used to define options for databases which require special handling of boolean values. Below is an explanation of each field:
Recognizes booleans. Check this option if your database can accept 'true'/'false' strings in SQL. This is the case for MS Access. (Note: VBSF automatically detects MS Access and adjusts this value at runtime.)
Translate to integer. Check this option if your database does not support boolean column data types and the JDBC driver is incapable of automatically performing the conversion automatically. This is the case, for example, with InstantDB.
True value. If is Translate to integer is checked, enter the integer value that corresponds to a boolean value of true.
False value. If is Translate to integer is checked, enter the integer value that corresponds to a boolean value of false.