Mapping SQL to BPM Components

This sections explains how JDBC/SQL objects and types map to BPM components and PBL types when cataloging SQL tables, views and procedures.

When cataloging SQL objects, Studio creates a new BPM component for each table, view and store procedure you select. These auto-generated components inherit from the standard Fuego.Sql.SqlObject component (refer to the Oracle BPM Components Reference for details).

Renamed elements and attributes

The naming conventions on Java and PBL dictate that component names should start with uppercase letters and attribute names should start with lowercase letters. All names should use "camel-case" to separate words and not use underscores ("_").

When cataloging SQL objects, the attributes of generated components will follow Java conventions and the original SQL names are converted if necessary.

For example, for cataloged SQL column named purchase_order, the corresponding BPM component attribute is named purchaseOrder.

JDBC column types to PBL types

JDBC column types are mapped to PBL types as defined in the following table:

JDBC Type	PBL Type
CHAR	String
LONGVARCHAR	String
VARCHAR	String
CLOB	String
TIME	Time
DATE	Time
TIMESTAMP	Time
BIT	Bool
TINYINT	Int(8)
SMALLINT	Int(16)
INTEGER	Int
BIGINT	Int(64)
NUMERIC	Decimal
DECIMAL	Decimal
FLOAT	Real(32)
DOUBLE	Real
REAL	Real
BINARY	Binary
LONGBINARY	Binary
VARBINARY	Binary
BLOB	Binary
OTHER	Java.Lang.Object

Some database provide non standard column types. The following table shows how vendor-specific types map to PBL types:

DB Vendor	Vendor Type	JDBC Type	PBL Type
MS-SQL	NVARCHAR	VARCHAR	String
MS-SQL	NCHAR	CHAR	String
MS-SQL	NTEXT	CHAR	String
DB2	CHARACTER	CHAR	String
Oracle	DATE	DATE or TIMESTAMP	Time

Oracle DATEs can store dates with or without the time component. The type mapping depends on the Use Timestamp for Date columns option on the External Resource configuration.