Custom Interface Types / Database Types
Overview
Fabric supports creating a new custom interface type based on a JDBC driver's specifications or overriding existing database types without the need for product enhancement.
Once saved, the database type appears in the Interface type list and can be selected when creating a new Interface.
How Do I Create a New Custom Interface Type/Database Type?
A new database type can be created from a template to customize a built-in database type or when a new database type is very similar to an existing type. When creating a database type from a template based on an existing database type (e.g. Oracle), make sure to save it according to your needs: either with the same name (Oracle) in order to override default configurations, or with a different name, which will be handled as a new database type, alongside the original.
You can also create a new database type, from start, which is not based on a template.
Create a Database Type from a Template
To create a new custom interface type/database type based on a template, do the following:
Go to Project Tree > Shared Objects, right-click Database Types and select New Database Type From Template and then select the DB type, for example Oracle.
Edit the required properties. For example, add the following connection property for Oracle:
oracle.jdbc.ReadTimeout=10000
- Save the database type.
Go to Project Tree > Implementation > Shared Objects > Custom Interface Types/Database Types.
Right-click on the folder icon (left), next to Custom Interface Types/Database Types.
Click on New Custom DB Interface Type in the opened context menu.
Choose one of the existing DB interface types from the available list. Press Enter to confirm or Escape to cancel.
Name the new custom interface.
Edit the required properties. For example, add the following connection property for Oracle:
oracle.jdbc.ReadTimeout=10000
- Save the custom interface type.
Create a Database Type from Start
To create a new custom interface type/database type from start, do the following:
Go to Project Tree > Shared Objects, right-click Database Types and select New Database Type.
Populate the Driver's Settings:
Name, a unique Database type name.
Class Name, URL Template and Default Port (optional). Settings from the JDBC driver.
Go to Project Tree > Implementation > Shared Objects > Custom Interface Types/Database Types.
Right-click on the folder icon (left), next to Custom Interface Types/Database Types.
Click on New Custom DB Interface Type in the opened context menu.
Choose New Custom Type option. Press Enter to confirm or Escape to cancel.
Name the new custom type. Note that the field is prepopulated with Type name, which you should change.
Optional: Edit the Pool Properties and Fabric Properties fields.
- Note that the Pool Properties and Fabric Properties should be modified only by advanced users. During an initial setup, use the default values.
Optional: Populate the Studio – Metadata settings (to be used by Fabric Studio only):
- SQL Query for Tables List and SQL Query for Column List, with SQL queries to retrieve the lists of tables and columns from the schema.
- SQL Query for FKs List and SQL Query for PKs List, with SQL queries to retrieve the list of foreign keys (FK) and primary keys (PK) of the tables. These keys can be used by the Auto Discovery Wizard when creating a new Logical Unit.
- Note that if the JDBC driver does not enable access to metadata and the above settings have not been populated, you can still work with this DB and run queries on it. However, the Fabric Studio's Auto Discovery Wizard, DB Query and Query Builder schemas will not work above it. The implementor must write the Root functions and all SQL queries manually.
- Populating the above queries can be also done to customize the list of tables or columns to be returned by the schema. For example, when the schema includes many tables and only a small subset of tables is required.
- Set Query Builder: Identifiers Case Sensitivity as either:
- Insensitive (default).
- SensitiveUpperCase.
- SensitiveLowerCase.
Set JDBC Connection Properties as follows:
- A Name and a Value for parameters to be used as a part of JDBC connection properties.
- When setting the Enable Interface Override, the parameters are displayed under the Connection Properties section of the Interface window, parameter values can be updated.
- When setting the Connection Property, the connection properties map is sent to the JDBC driver.
Save the customer interface type/database type.
Example of JDBC Connection Properties Definition
- Create a new MySQL2 Database type that includes the useSSL JDBC Connection Property and add it to the URL Template as shown below:
&abc=[useSSL]
Create a new Interface using the MySQL2 Database type. Note that useSSL JDBC Connection Property is added to the Interface window under Connection Properties section and the following is added to the Connection String:
abc=false
- If needed, update the value of useSSL property. When updated, it is also reflected in the Connection String.
JDBC Drivers Management
When introducing a new database type, the JDBC driver is saved under:
[Fabric Project's Directory]/[Project Name]/lib/[new database type]
Each database type is kept in a separate folder in order to avoid overlapping or conflicts with other drivers (dynamically loaded).
Note that since Fabric 8.0 the JDBC drivers are part of the Fabric Deployment process.
Custom Interface Types / Database Types
Overview
Fabric supports creating a new custom interface type based on a JDBC driver's specifications or overriding existing database types without the need for product enhancement.
Once saved, the database type appears in the Interface type list and can be selected when creating a new Interface.
How Do I Create a New Custom Interface Type/Database Type?
A new database type can be created from a template to customize a built-in database type or when a new database type is very similar to an existing type. When creating a database type from a template based on an existing database type (e.g. Oracle), make sure to save it according to your needs: either with the same name (Oracle) in order to override default configurations, or with a different name, which will be handled as a new database type, alongside the original.
You can also create a new database type, from start, which is not based on a template.
Create a Database Type from a Template
To create a new custom interface type/database type based on a template, do the following:
Go to Project Tree > Shared Objects, right-click Database Types and select New Database Type From Template and then select the DB type, for example Oracle.
Edit the required properties. For example, add the following connection property for Oracle:
oracle.jdbc.ReadTimeout=10000
- Save the database type.
Go to Project Tree > Implementation > Shared Objects > Custom Interface Types/Database Types.
Right-click on the folder icon (left), next to Custom Interface Types/Database Types.
Click on New Custom DB Interface Type in the opened context menu.
Choose one of the existing DB interface types from the available list. Press Enter to confirm or Escape to cancel.
Name the new custom interface.
Edit the required properties. For example, add the following connection property for Oracle:
oracle.jdbc.ReadTimeout=10000
- Save the custom interface type.
Create a Database Type from Start
To create a new custom interface type/database type from start, do the following:
Go to Project Tree > Shared Objects, right-click Database Types and select New Database Type.
Populate the Driver's Settings:
Name, a unique Database type name.
Class Name, URL Template and Default Port (optional). Settings from the JDBC driver.
Go to Project Tree > Implementation > Shared Objects > Custom Interface Types/Database Types.
Right-click on the folder icon (left), next to Custom Interface Types/Database Types.
Click on New Custom DB Interface Type in the opened context menu.
Choose New Custom Type option. Press Enter to confirm or Escape to cancel.
Name the new custom type. Note that the field is prepopulated with Type name, which you should change.
Optional: Edit the Pool Properties and Fabric Properties fields.
- Note that the Pool Properties and Fabric Properties should be modified only by advanced users. During an initial setup, use the default values.
Optional: Populate the Studio – Metadata settings (to be used by Fabric Studio only):
- SQL Query for Tables List and SQL Query for Column List, with SQL queries to retrieve the lists of tables and columns from the schema.
- SQL Query for FKs List and SQL Query for PKs List, with SQL queries to retrieve the list of foreign keys (FK) and primary keys (PK) of the tables. These keys can be used by the Auto Discovery Wizard when creating a new Logical Unit.
- Note that if the JDBC driver does not enable access to metadata and the above settings have not been populated, you can still work with this DB and run queries on it. However, the Fabric Studio's Auto Discovery Wizard, DB Query and Query Builder schemas will not work above it. The implementor must write the Root functions and all SQL queries manually.
- Populating the above queries can be also done to customize the list of tables or columns to be returned by the schema. For example, when the schema includes many tables and only a small subset of tables is required.
- Set Query Builder: Identifiers Case Sensitivity as either:
- Insensitive (default).
- SensitiveUpperCase.
- SensitiveLowerCase.
Set JDBC Connection Properties as follows:
- A Name and a Value for parameters to be used as a part of JDBC connection properties.
- When setting the Enable Interface Override, the parameters are displayed under the Connection Properties section of the Interface window, parameter values can be updated.
- When setting the Connection Property, the connection properties map is sent to the JDBC driver.
Save the customer interface type/database type.
Example of JDBC Connection Properties Definition
- Create a new MySQL2 Database type that includes the useSSL JDBC Connection Property and add it to the URL Template as shown below:
&abc=[useSSL]
Create a new Interface using the MySQL2 Database type. Note that useSSL JDBC Connection Property is added to the Interface window under Connection Properties section and the following is added to the Connection String:
abc=false
- If needed, update the value of useSSL property. When updated, it is also reflected in the Connection String.
JDBC Drivers Management
When introducing a new database type, the JDBC driver is saved under:
[Fabric Project's Directory]/[Project Name]/lib/[new database type]
Each database type is kept in a separate folder in order to avoid overlapping or conflicts with other drivers (dynamically loaded).
Note that since Fabric 8.0 the JDBC drivers are part of the Fabric Deployment process.
