# About Developing Business Components for Foreign Datasources

With the 3.2 release of JDeveloper, Business Components for Java can run against any standards-based database. In previous releases you were restricted to using an Oracle8i database for all design-time work; if you wanted to use a foreign datasource at runtime, you had to make sure the schemas matched exactly, as well as keep in mind various limitations. This is still a valid way to use and switch to foreign datasources, and is covered in the topic, Using a Foreign Datasource by Matching Schemas. However, most users will probably want to connect directly to the datasource.

## Scenarios for Development

There are a number of different configurations for connecting Business Components for Java to a datasource.

- Oracle8i - For use with Oracle8i

- SQL92 - For use with any standards-based database
- OLite - For use with an Oracle8i Lite database
- Switchable - For use with both Oracle and non-Oracle databases
- Custom - For use with a foreign database with custom data type mapping

The following sections detail these scenarios in more detail.

### Oracle8i Scenario

This scenario is the default configuration if you have Oracle8i. This is the most efficient configuration and allows you to take advantage of Oracle SQL constructs and JBO Domains, which leverage Oracle's efficient JDBC datatypes.

When you choose this configuration, these required libraries are added:

- JBO Oracle Domains

- Oracle JDBC 8.1.7

### SQL92 (Generic) Scenario

This scenario allows you to run against any database that conforms to the SQL92 standard. In this sense, this development scenario is the most generic. This configuration can be used to run against Oracle8i as well, but it will not perform as well as the switchable scenario detailed below. In this scenario you cannot use any domain objects (you must use a Java type map). This will slow down performance. However, you gain the ability to run against any standards-based database.

### OLite Scenario

The OLite scenario is used exclusively for running against the Oracle8i Lite database. The SQL92 configuration is very similar to the this configuration and shares some of the same limitations.

### Switchable Scenario

The switchable scenario is useful if you are running both Oracle8i and SQL92-based databases. This configuration allows you to take advantage of domain types, which are faster and more efficient than a Java type map, but you are limited by the SQL92 dialect.

This configuration is useful in that you can switch the target database at design time and runtime.

Note: In this scenario you must manually ensure that are using the appropriate domain libraries. In the table above, notice that there are two domain types, Oracle and Generic. When you are running against Oracle8i you must use the JBO Oracle Domains. When running against any other database you must switch your domain type to JBO Generic Domains. For more information on switching datasources, see the topic Ways to Switch Datasources.

### Custom scenario

If you have a database that uses enhanced datatypes not described adequately by the SQL92 standard, you will need to design a custom type map that maps the database types to Java types. Like the SQL92 generic scenario, you are precluded from using domain types. There are fewer SQL limitations in this scenario, but you should still be aware of them.

## Understanding Connections, SQL Dialect, Type Maps, and Domains

For every development scenario there are four interconnected parts: JDBC connection, SQL dialect, type map, and domains. JDeveloper is configured to work with Oracle8i by default. To work with a different database, you must configure JDeveloper to use the appropriate values. These values are displayed in the following table for comparison.

Scenario | Connection URL | SQL Dialect | Type Map | Domains
Oracle8i | jdbc:oracle:thin | Oracle | Oracle | JBO Oracle Domains
SQL92 | jdbc:odbc:db_name | SQL92 | Java | None*
OLite | jdbc:polite:POLite | OLite | Java | None*
Switchable | jdbc:oracle:thin jdbc:odbc:db_name | SQL92 | Oracle | JBO Oracle Domains JBO Generic Domains
Custom | jdbc:odbc:db_name | SQL92 | Custom | None*

### Connection Type

Before you begin your project you must select a connection type. When you do this, the Business Components framework chooses default values for the SQL dialect and type map. These values appear in the drop down boxes in the Business Components Project Wizard.

The Connection URL for foreign datasources is jdbc.odbc.db_name where db_name is the name given to the ODBC datasource. If you have not changed it, this datasource name will be provided the default name. For example, SQL Server uses jdbc.odbc.mqis.

### SQL Dialect

BC4J entity objects take responsibility for managing the data content of relational tables. If the database connection is known to be an Oracle one, BC4J can perform several optimizations for you. If however, the application is required to execute against arbitrary SQL datasources, these optimizations cannot be used. We call the style of SQL generated on the application's behalf the SQL dialect.

Business Components for Java contains three SQL dialects:

- Oracle - Used only in the default Oracle8i scenario

- OLite - Used only in the OLite scenario
- SQL92 - Used in the SQL92 and switchable scenarios

After you choose your connection type, the framework knows which SQL dialect to use by default. Any business components you create will reflect the appropriate SQL style. For example, if you choose the SQL92 dialect, the binding will be ?-style instead of :1-style.

Note: The SQL dialect that the business component application designer uses in his custom designed view objects must also match the dialect of the generated SQL.

### Type Maps

The OLite and SQL92 scenarios do not permit the use of domains (except for Date and Number). When domains are not available, Java types require that the database information is converted to a Java representation and back again. This is done using a type map.

Business Components for Java contains two type maps:

- Oracle - Database columns are mapped to oracle.jbo.domain types

- Java - Database columns are mapped to Java types

If you have a database that uses enhanced datatypes not described adequately by the SQL92 standard, you will need to design a custom type map that maps the database types to Java types.

### Domains

Domain types are faster and more efficient than Java type maps, because they map directly to the database types. The business components framework supplies two implementations of oracle.jbo.domain types:

- JBO Oracle Domains - These map efficiently via Oracle's JDBC driver

- JBO Generic Domains - These domains are mirrored implementations of the JBO Oracle Domains

Related Topics:

Limitations of Developing for Foreign Databases
Ways to Develop Business Components
About Type Maps