Initializing the Database 

Use

Use this procedure to initialize the database your application uses. The database initialization includes the execution of one or more SQL statements on a database using a particular DataSource.

When using this function, you should consider the following issues:

·         You can initialize the database manually only. JDBC Connector Service does not support database initialization at deployment time.

·         JDBC Connector Service does not offer support for statement caching.

·         By default, most of the statements are executed in a transaction and the auto-commit mode is disabled. If any of the statements fails to execute, the whole transaction rolls back.

If possible, avoid using Data Definition Language (DDL) statements with a JDBC 2.0 compatible driver. Examples of DDL statements are CREATE TABLE, CREATE INDEX, DROP TABLE, and so on.

Procedure

To initialize a database, use the following tools:

·        Visual Administrator

·        Console Administrator

Visual Administrator

...

       1.      In the Runtime tab of JDBC Connector Service, choose a DataSource from the Resources → DataSources → <applicationName> tree.

       2.      Go to the DB Initialization tab.

       3.       To specify a new SQL statement, choose Add Statement. To modify an existing statement, select it and choose Edit Statement. To delete a statement, select it and choose Remove Statement.

If the database already contains a table with the same name as the one you are trying to create with your initialization script, an SQLException is thrown and the execution of the script stops because the auto-commit mode is disabled. To prevent this, use the Ignore Error indicator. This guarantees that the next statements are executed and that the transaction is finally committed, even if some of the statements fail.

       4.      Use Save to save the changes.

If you modify the statements at runtime, the database is not re-initialized when you choose Save. This option only saves your changes. To execute the statements, choose Save and Execute.

You can import an initialization script from an XML file, as well as export the specified SQL statements to an XML file. To do this, use Import from XML and Export to XML options in the DB Initialization tab. For more information about the format of the XML file, see db-init.dtd.

Console Administrator

To view the SQL statements associated to a DataSource, use the get_init_statements command from the DBPOOL command group.

To add SQL statements and associate them to a DataSource, use the db_init command from the DBPOOL command group.

To delete the SQL statements associated to a DataSource, use the remove_init_statements command from the DBPOOL command group.

To execute the SQL statements associated to a DataSource, use the execute_init_statements from the DBPOOL command group.

For more information, see DBPOOL.