getModularConnectorDatasourceCreateStatement

このプロシージャは、 createConnectioncreateDatasourceステートメントを、モジュラー接続用のデフォルト・パラメータとダミー・パラメータで形成します。

Parameters

Parameter	Description
`string_connectorName`	The modular connector's name for which the `CREATE` statement should be formed; mandatory

Usage

CALL "UTILS.getModularConnectorDatasourceCreateStatement"(
 "connectorName" => 'string_connectorName'
);;

Definition

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

簡単な例を挙げましょう：

CALL "UTILS.getModularConnectorDatasourceCreateStatement"(
 "connectorName" => 'dhl'
);;

createConnectioncreateDatasourceステートメントにデフォルトのパラメータを指定すると、次のように出力されます：

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 => '') ;;