Dynamic Relational Datasource Configuration

A callback TDV procedure, that is implemented in SQL Script or as a custom Java procedure (CJP) and owned by TDV administrators, can be registered with a relational data source to apply runtime customizations to the data source properties configuration before a connection is used by the TDV server to establish a new session with the data source. All customizations are transient. Their scope is limited to the specification of the JDBC properties to be used by data source connections.

As an example, an administrator could define a SQL Script procedure that determines the user associated with an incoming request accessing a relational data source and use TDV domain specific language (DSL) statements to adjust its JDBC connection properties to reflect what type of access - ReadWrite or ReadOnly - should apply to the data source connection used by the user request.

Dynamic Data Source Configuration Example

PROCEDURE callbackprocedure()
BEGIN
declare datasource_path varchar(4000);
declare adapter_name varchar(400);
declare user_name varchar(1000);
declare dsl_statement varchar(4000);
declare properties varchar(1000);

CALL GetEnvironment(System.DATASOURCE_PATH, datasource_path);
CALL GetEnvironment(System.DATASOURCE_ADAPTER_NAME, adapter_name);
CALL GetEnvironment(System.REQUEST_USER_NAME, user_name);
CALL Log(datasource_path || '' || adapter_name || '' || user_name);

IF (user_name = 'admin') then
 SET properties ='"ApplicationIntent":"ReadWrite"';
ELSEIF (user_name = 'test') then
 SET properties ='"ApplicationIntent":"ReadOnly"';
END IF ;
SET dsl_statement = 'ALTER DATASOURCE ' || datasource_path /*'/shared/automation/oracle_19t*/
|| ' NO INTROSPECTION '
|| ' BASED ON ADAPTER ''' || adapter_name /*'Oracle 19c (Thin Driver)'*/ || ''''
|| ' SET NATIVE PROPERTIES {"connProperties": {' || properties || '}}';

CALL Log(dsl_statement);

EXECUTE IMMEDIATE dsl_statement;

CALL Log('Executed DSL');

END