このプロシージャは、 createConnection
createDatasource
ステートメントを、モジュラー接続用のデフォルト・パラメータとダミー・パラメータで形成します。
Parameters
Parameter | Description |
---|---|
| The modular connector's name for which the |
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'
);;
createConnection
createDatasource
ステートメントにデフォルトのパラメータを指定すると、次のように出力されます:
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 =>
''
) ;;