SQL resource

You can use an SQL resource to store and retrieve data from the SQL database.

To also process that data, you can use the SQL resource as the base file for the data source of a transformer project.

To configure an SQL resource, double-click it. An SQL resource has the following properties:

Properties

General

Name - Enter the name of the SQL resource.
Description - Enter an optional text that describes the SQL resource.

Audit

Modified by - the name of the last user to make changes to the object.
Last modified - the date and time when the object was last changed.
Created by - the name of the user who created the object.
Created - the date and time when the object was created.

Custom

Server type – Select the server type of your database server.

To use Windows authentication to authenticate from Corporater BMP to the MS SQL server, select 'SQL server'.
Server – Enter the IP address or the hostname of your database server.
Port – Enter the port number for your database server.

Usually, the value is '1433'.
Instance - Applies only if you select 'SQL server' for Server type. The name of the instance to connect to.

Based on how many database server instances run on the database server, perform the following:
- Multiple instances - Enter the name of the instance to to connect to.
- One instance - Leave Instance blank.
Domain - Applies only if you select 'SQL server' for Server type. The server domain.

To use Windows authentication to authenticate from Corporater BMP to the MS SQL server, based on the operating system for your BMP application server, perform the following:
- Windows - Leave Domain blank.
- Linux - Enter corresponding domain information.
This requires a valid connection to the database. For information, see the Windows authentication on MS SQL document.
Database – Applies only if you select 'SQL server' or 'DB2' for Server type. Enter the name of the database.
SID/Service name - Applies only if you select 'Oracle' for Server type. Enter the unique name of your database instance (SID) or the alias to connect to your database instances (service name).
Default schema - Applies only if you select 'DB2' for Server type. Enter the name of the database schema that contains the target data.
URL - Enter the URL to the database.
Driver - Enter the driver for the database.

If you select 'Others' for Server type, Corporater is not responsible for the driver's installation and configuration.
User name – The user name of the user that Corporater BMP uses to log on to the server.

To use Windows authentication to authenticate from Corporater BMP to the MS SQL server, based on the operating system for your BMP application server, perform the following:
- Windows - Leave User name blank.
- Linux - Enter corresponding user information.
This requires a valid connection to the database. For information, see the Windows authentication on MS SQL document.
Password – The password of the user that Corporater BMP uses to log on to the server.

To use Windows authentication to authenticate from Corporater BMP to the MS SQL server, based on the operating system for your BMP application server, perform the following:
- Windows - Leave Password blank.
- Linux - Enter corresponding password information.
This requires a valid connection to the database. For information, see the Windows authentication on MS SQL document.
Test connection - Click the Refresh ( ) icon to test that the configuration is correct.

If you selected 'Others' for Server type, you must ensure that the information in the SQL resource configuration is correct.

Advanced

ID - a unique ID for the object.

By default, Corporater BMP assigns consecutive numbers to new objects.

You can change the ID to a value of choice that can include either letters or letter and number combinations.

The ID mustn't contain only numbers, include special characters such as ) ( . * } { + < > [ ], or match the name of a default object type in Corporater BMP.

Access control

Read accessors - click the Edit button () and authorize one or more users and user groups to view the object on Web.
Add accessors - click the Edit button() and authorize one or more users and user groups to add new objects to the object on Web.
Write accessors - click the Edit button() and authorize one or more users and user groups to edit the object on Web.
Delete accessors - click the Edit button() and authorize one or more users and user groups to delete the object on Web.
Ownership - click the Edit button () and grant ownership of the object to one or more users and user groups.

Users and user groups who own the object have full access rights to it.
Override access profile - select for the object's access control setting to outrank the object's corresponding access settings in profiles.