getModularConnectorDatasourceCreateStatement

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

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;

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

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