getModularConnectorDatasourceCreateStatement
You are looking at an older version of the documentation. The latest version is found here.
This procedure forms the createConnection
and createDatasource
statements with default and dummy parameters for modular connections.
Parameters
Parameter | Description |
---|---|
string_connectorName | The modular connector's name for which the CREATE statement should be formed; mandatory |
Usage
SQL
CALL "UTILS.getModularConnectorDatasourceCreateStatement"(
"connectorName" => 'string_connectorName'
);;
Definition
SQL
CREATE VIRTUAL PROCEDURE getModularConnectorDatasourceCreateStatement (IN connectorName string)
RETURNS (
createStatements string
) OPTIONS (Annotation 'Generates the installation calls with the necessary properties for the specified modular connector')
AS
BEGIN
DECLARE string baseConnector =
WITH RCTE AS (
SELECT NULL AS name, connectorName AS connector, 1 AS Lvl
UNION ALL
SELECT d.name, d.connector, Lvl+1 AS Lvl FROM SYSADMIN.DVConnectors d
JOIN RCTE rc On d.name = rc.connector AND (rc.name IS NULL OR rc.name <> d.name)
)
SELECT r.connector
FROM RCTE r
JOIN SYSADMIN.DVConnectors d ON d.name = r.name
ORDER BY lvl DESC LIMIT 1;
SELECT
'EXEC SYSADMIN.createConnection(name => '''
|| x.name
|| '_src'', jbossCLITemplateName => '''
|| baseConnector
|| ''', connectionOrResourceAdapterProperties => '''
|| CAST(x.connectionOrResourceAdapterProperties AS string)
|| ''') ;;'
|| UNESCAPE('\n\n') ||
'EXEC SYSADMIN.createDataSource(name => '''
|| x.name
|| '_src'', translator => '''
|| x.translator
|| ''', modelProperties => '''
|| CAST(x.modelProperties AS string)
|| ''', translatorProperties => '''
|| CAST(x.translatorProperties AS string)
|| ''') ;;'
AS createDatasourceStatement
FROM
(
SELECT
b.id
, b.name
, b.connector
, b.translator
, ROW_NUMBER() OVER (PARTITION BY connid ORDER BY b.id, a.kind, a.name) AS countme
, STRING_AGG( (a.name || '=' || COALESCE(a.defaultValue, '<' || a.description || '>', '<' || a.displayname || '>', '')), ',' ) FILTER ( WHERE a.kind = 'model' )
OVER (PARTITION BY b.id) AS modelProperties
, STRING_AGG( (a.name || '=' || COALESCE(a.defaultValue, '<' || a.description || '>', '<' || a.displayname || '>', '')), ',' ) FILTER ( WHERE a.kind = 'translator' )
OVER (PARTITION BY b.id) AS translatorProperties
, STRING_AGG( (a.name || '=' || COALESCE(a.defaultValue, '<' || a.description || '>', '<' || a.displayname || '>', '')), ',' ) FILTER ( WHERE a.kind = 'connector' )
OVER (PARTITION BY b.id) AS connectionOrResourceAdapterProperties
FROM SYSADMIN.DVConnectors b
LEFT JOIN SYSADMIN.DVConnectorPropDefinitions a ON b.id = a.connid
WHERE b.name = connectorName
) x
LEFT JOIN SYSADMIN.DVConnectors c ON x.connector = c.name
WHERE x.countme=1 ;
END;
Example
Here is a simple example:
SQL
CALL "UTILS.getModularConnectorDatasourceCreateStatement"(
"connectorName" => 'dhl'
);;
It gives the following output with the createConnection
and createDatasource
statements with default parameters:
SQL
CALL SYSADMIN.createConnection(name => 'dhl_src', jbossCLITemplateName => 'ws', connectionOrResourceAdapterProperties => '') ;;
CALL SYSADMIN.createDataSource(name => 'dhl_src', translator => 'dhl', modelProperties => 'APPNAME=<Your User Name for the DHL Track and Trace API>,PASSWORD=<Your Password for the DHL Track and Trace API>', translatorProperties => '') ;;