Search

SQL Server [Deprecated]

Version 23.4.8843

SQL Server [Deprecated]

SQL Server 端口支持从 SQL Server 数据库存储和检索数据。

概览

建立连接 到目标数据库后，端口读取数据库中存在的表/视图列表。通过选择可用表并从该表中选择列来创建输入映射或输出映射。端口使用选定的表和列生成 XML 架构结构。

生成输入映射后，端口会在收到与输入映射的 XML 结构匹配的 XML 文件时自动将数据插入数据库。有关更多详细信息，请参考 输入映射 部分。

生成输出映射后，端口将从数据库中提取数据，并使用提取的值填充与输出映射具有相同 XML 结构的 XML 文件。有关更多详细信息，请参考 输出映射 部分。

输入／输出映射是用于存储过程的一种特殊映射，它既需要输入（参数），又需要生成输出。有关更多详细信息，请参考输入／输出映射部分。

端口配置

本节包含所有可配置的端口属性。

设置

数据库连接

与建立数据库连接有关的设置。

  • 端口 Id 端口的静态、唯一标识符。
  • 端口类型 显示端口类型及其用途的描述。
  • 端口描述 一个可选字段，用于提供端口及其在流中的角色的自由格式描述。
  • 配置格式 将连接设置指定为属性列表或连接字符串
  • 连接字符串 连接字符串格式的数据库凭据。仅当配置格式 为“连接字符串”时适用，并用于代替其他连接字段。
  • 连接字符串类型 是否使用 Windows 身份验证或 SQL Server 身份验证与 SQL Server 进行身份验证。如果设置为 Windows 身份验证，则使用运行知行之桥的 Windows 用户的凭据。仅当设置格式 为“属性列表”时适用。
  • 服务器 托管数据库的服务器的主机名或 IP 地址。
  • 数据库 要连接的数据库的名称。
  • 用户名 有权访问数据库的用户凭据。仅当连接字符串类型 为“SQL Server 身份验证”时适用。
  • 密码 与指定 User 关联的密码凭证。仅当连接字符串类型 为“SQL Server 身份验证”时适用。

映射

在数据库表和 XML文档结构之间建立映射关系。一旦创建了这些映射，就可以将 XML 数据插入数据库中，并且可以将从数据库中检索到的数据作为 XML输出。

  • 输入映射 输入映射在 XML 文档结构和数据库插入（或更新）之间创建关系。当与输入映射的结构匹配的 XML 文件到达 Database 端口时，端口将使用输入文件中的值对数据库进行插入／更新。有关生成输入映射的更多信息，请参见输入映射部分。
  • 输出映射 输出映射在 XML 文档结构和数据库 select 语句之间创建关系。当端口从数据库接收数据时，它将使用与输出映射相同的 XML 结构填充 XML 文件。然后将此 XML 文件推送到知行之桥工作流中的下一个端口。有关生成输出映射的更多信息，请参见输出映射部分。
  • 输入/输出 映射 输入/输出 映射用于既期望输入（参数）又产生输出的存储过程。这些映射这些映射建立两种 XML 文档结构，一种用于提供存储过程参数的输入文件，另一种用于返回存储过程结果的输出文件。

自动化

自动化设置

与端口自动处理文件有关的设置。

  • 发送 与输入映射匹配的 XML 文件是否将自动插入数据库中。
  • 重试间隔 重试失败的插入／更新前等待的时间。
  • 最大重试次数 在端口出现错误之前，端口重试失败的插入／更新的最大次数。
  • 接收 数据是否应自动从目标数据库中检索并生成与配置的输出映射匹配的 XML 文件。
  • 接收执行间隔 自动尝试从数据库检索数据之间的时间间隔。
  • 分 每次执行等待的分钟数。仅当执行间隔被设置为 Minute 时适用。
  • 第几分钟/小时 每小时计划的分钟偏移量。仅当执行间隔被设置为 Hourly 时适用。例如，如果将此值设置为 5，将在 1:05、2:05、3:05 自动执行脚本。
  • 时 在指定的某一天内进行消息处理的时间。仅当执行间隔被设置为 Daily 或 Weekly 或 Monthly 时适用。
  • 天 进行消息处理的某一天。仅当执行间隔被设置为 Weekly 或 Monthly 时适用。
  • Cron 表达式 表示 cron 表达式的任意字符串，用于确定何时执行。仅当执行间隔被设置为 Advanced 时适用。

高级设置

性能

与端口资源分配相关的设置。

  • 最大线程数 从线程池中消耗用于处理此端口上的文件的最大工作线程数。 如果设置，这将覆盖 设置 > 自动化 页面上的默认设置。
  • 最大文件数 分配给端口的每个线程发送的最大文件数。 如果设置，这将覆盖 设置 > 自动化 页面上的默认设置。

其它设置

先前类别中未包含的设置。

  • 命令超时时间 命令执行超时持续时间（以秒为单位）。
  • 平面文件映射方案 根据文档的文件名控制将使用哪个输入映射来处理传入的平面文件。 支持全局匹配，并且可以通过用分号分隔每个映射来定义多个映射。 例如，将此字段设置为 ‘INVOICE=Invoice;ORDER=Order;’ 将导致端口使用 Invoice 输入映射来处理文件名中带有 “INVOICE” 的任何文件，并且名称中带有 “ORDER” 的任何其他文件将使用 Order 输入映射进行处理。 比较不区分大小写。
  • 本地文件名格式 用于为端口输出的消息分配文件名的方案。 可以在文件名中动态使用宏来包含标识符和时间戳等信息。 有关详细信息，请参阅宏。
  • 保留消息副本 来自已处理文件的日志是否将包含文件本身的副本。
  • 保存至 Sent 文件夹 端口处理的文件是否应复制到端口的已发送文件夹。
  • 输出文件格式 从数据库中提取的记录的格式是 XML 还是 CSV。

特殊设置

特殊设置 适用于特定用例。

  • 其他设置 允许在以分号分隔的列表中配置隐藏的端口设置，例如setting1=value1;setting2=value2。 正常的端口用例和功能不需要使用这些设置。

建立连接

使用 SQL Server 身份验证建立连接需要以下连接属性：

  • 服务器
  • 数据库
  • 用户名
  • 密码

这些可以配置为单独的属性或数据库连接字符串。

Windows 身份验证可用于代替 SQL Server 身份验证，在这种情况下，运行知行之桥的 Windows 用户帐户用于向 SQL Server 进行身份验证。

输入映射

输入映射表示对目标数据库中一个或多个表的 INSERT / UPDATE。输入映射是通过可视化的编辑映射面板创建的，然后在内部建模为 XML，由端口处理，将输入映射的 XML 结构匹配的文件将自动转换为 INSERT 或 UPDATE 语句。

输入映射为 XML部分详细介绍了将 XML 转换为数据库查询的过程；从概念上将，端口只是从 XML 元素中读取值，并在执行 INSERT / UPDATE 语句时使用它们。

创建输入映射

单击“添加映射”按钮(+)创建新的映射。从数据库中可用表的列表中选择目标表／视图，以打开映射编辑器面板。在映射编辑器中，选择应包含在 INSERT / UPDATE 中的表列。对于简单的 INSERT，这就是生成映射所需的全部。

创建输入映射后，可以通过单击映射名称旁边的“代码”按钮(</>)来查看其 XML 表示形式。

UPSERT

UPSERT 设置控制端口如何决定使用 INSERT 还是 UPDATE。最常见的情况是将 UPSERT by: 设置为表的主键。使用此配置，端口将从输入的 XML 中读取主键值，查询数据库以查看该键是否已存在于表中，如果存在则更新记录，如果不存在则插入新记录。 UPSERT by: 可以设置为主键以外的列，但这可能会导致从数据库返回多条记录。在这种情况下，端口更新返回的第一条记录。

如果 Perform this query to select the UPSERT key: 设置为 SELECT 查询，则端口会将此查询的结果与输入 XML 进行比较，以确定是 INSERT 还是 UPDATE。例如，如果将此字段设置为以下查询：

SELECT CustomerName WHERE CustomerAddress = 101 Main Street

然后端口将首先对数据库运行此查询，然后在输入的 XML 中查找 CustomerName 元素，最后将 XML 值与数据库返回的值进行比较。如果值匹配，则端口将更新该记录，否则将插入新记录。

INSERT

如果在输入映射中禁用了 UPSERT，则端口将自动将查询操作设置为 INSERT。端口将尝试为每个输入 XML 文件插入新记录，而不查询数据库以查看记录是否已存在。

LOOKUP

某些列具有可用的 LOOKUP 选项。端口检测到这些列与数据库中的另一个表具有外键关系。如果启用了 LOOKUP，端口将在 INSERT 之前查询此外部表，并使用 INSERT 中返回的值。 LOOKUP by: 字段确定将用于查询外部表的列。

例如，假设将采购订单 (PO) 插入到数据库中，其中“PO”表中的字段之一是内部 CustomerID。假设输入 XML 将有一个 CustomerName 元素，但没有客户的内部 ID。但是，数据库中的另一个表“Customers”同时具有名称和 ID，因此可用于根据名称查找 ID 值。端口将识别 ‘PO’ 表的 CustomerID 列是外键，并为该列启用 LOOKUP。将 LOOKUP by: 设置为 CustomerName 将指示端口使用如下语句查询“Customers”表：

SELECT CustomerID FROM Customers WHERE CustomerName = @CustomerName

在此查询中，@CustomerName 表示来自输入 XML 的 CustomerName 元素中的值。

使用此配置，端口将首先从“客户”中检索 CustomerID，然后在插入“PO”表时使用该值。

输入映射为 XML

输入映射用 XML 表示，具有以下结构：父元素标识要插入的表，每个子元素对应于目标表中的一列。输入映射在 XML 元素和数据库列之间建立连接，以便端口可以从传入的 XML 文档中读取值，并在 INSERT 或 UPDATE 查询中使用。

以下 XML 是 “Students” 表的输入映射示例：

<Items>
    <Students action="insert">
        <StudentID key="true" />
        <Name/>
        <Grade type="int"/>
        <GPA/>
    </Students>
</Items>

在上面的示例中，StudentID, Name, Grade, GPA 是 “Students” 表中列。当端口处理与该结构匹配的 XML 文件时，将自动将XML中的值插入 “Students” 表。以下是上述映射的示例 XML 输入文件：

<Items>
    <Students>
        <StudentID>12554</StudentID>
        <Name>Ferris Bueller</Name>
        <Year>11</Year>
        <GPA>1.8</GPA>
    </Students>
    <Students>
        <StudentID>12921</StudentID>
        <Name>Hermione Granger</Name>
        <Year>9</Year>
        <GPA>4.0</GPA>
    </Students>
</Items>

当端口处理上述输入文件时，将在 “Students” 表中插入或更新两个记录，每个 Students 元素一个。注意，如果数据库可以通过 AUTOINCREMENT 自动生成主键，则表的主键不需要包含在输入 XML 中。

批量输入

Database 端口支持批处理，可以在插入大量数据时提高性能。批处理通过两个字段 事务容量和批处理容量. 当这些字段设置为正整数时，端口将在单个操作（批处理）中插入 [BatchInputSize]个记录，并在单个连接（事务处理）中插入 [TransactionSize] 个记录。

XML 输入文件可以包含比单个事务大小更多的记录，在这种情况下，端口将在多个事务中插入数据。

如果在批量插入过程中发生错误，端口将回滚事务并在失败的事务中缓存第一条记录的索引。重试输入文件时，仅将缓存索引之后的记录插入数据库。

插入子表

输入映射支持通过单个映射插入到多个表中。当创建具有多个表的输入映射时，这些表之间应具有外键关系，以使一个表是另一表的子表。否则，请使用多个输入映射插入到没有外键关联的表中。

要插入子表中，请首先创建一个以父表为目标的输入映射。然后，使用左上角的+添加按钮从输入映射编辑器中添加子表。从表列表中选择适当的表，设计器将自动调整表的层次结构。选择子表中应在输入映射中的列。

REF

子表中的某些列将带有蓝色的 “REF” 标记，端口已检测到这些列对应于父表中的主键。在插入到父表之后，插入到子表之前，端口将在父表中查询刚插入的记录的主键。然后，端口将该值插入子表中。

当父表的主键由数据库生成时（例如 AUTOINCREMENT 键），这很有用。在这种情况下，此列不会作为输入 XML 传入，是端口直到将记录插入到父表中之后才知道该列的值。

在输入映射 XML 中，“ref” 属性表示一列可以稍后引用，或者应从映射中的另一列读取该列的值。为了引用数据库生成的列的值（例如 AUTOINCREMENT 主键），列元素应保留值 LAST_INSERT_ID 的 ‘ref’ 属性：

<film action="upsert">
    <film_id key="true" upsert="film_id" ref="@@LAST_INSERT_ID" />

这表明 film_id 值将在插入后检索，并且可以稍后在映射中引用（通常在子表中）。要在以后引用此值，请将 “ref” 元素设置为 AUTOINCREMENT 列的名称：

<film_id key="true" ref="@film_id" />

在上面的示例中，film_id 是父表中主键的名称，也是子表中列的名称。“ref” 语法指示端口在插入后从父表中检索 film_id，并将该检索到的值用作子表中的 film_id 列。

LOOKUP

子表的 LOOKUP 逻辑与父表相同。有关更多详细信息，请参见创建输入映射中的 LOOKUP 部分。

子表 XML

插入父表和子表时，输入映射 XML 结构将是多层的。以下 XML 是两个有外键关联的表的输入映射示例：

<film action="upsert">
    <film_id key="true" upsert="film_id" ref="@@LAST_INSERT_ID" />
    <description />
    <language_id />
    <rating />
    <release_year type="date" />
    <title />
    <film_actor>
        <actor_id key="true" />
        <film_id key="true" ref="@film_id" />
        <last_update type="datetime" />
    </film_actor>
</film>

在上面的示例中，“film” 是父表，“film_actor” 是子表。film_id 列是 “film” 表的主键，是 “film_actor” 表中的外键。当 SQLServer 端口处理与该结构匹配的输入 XML 文件时，它将更新 “film” 和 “film_actor” 表，并确保链接两个记录的键相同。

输出映射

输出映射表示来自目标数据库中一个或多个表的 SELECT 查询。它们是通过可视化的映射编辑器面板创建的，然后在内部建模为 XML。从数据库返回的结果用于填充与输出映射”结构匹配的 XML 文档。将数据库响应转换为 XML 的过程在输出映射为 XML 部分中进行了详细说明。

创建输出映射

单击“添加映射”按钮(+)创建新的映射。从数据库中可用表的列表中选择目标表/视图，以打开映射编辑器面板。在映射编辑器中，选择应在 SELECT 语句中返回的表列。对于简单的 SELECT，这就是生成映射所需的全部。

创建输入映射后，可以通过单击映射名称旁边的“执行”按钮（黑色播放按钮）来执行它代表的查询。另外，可以通过单击映射名称旁边的“代码”按钮(</>)来查看映射的 XML 表示形式。

输出过滤

映射编辑器包括一个“过滤器”面板，用于定义限制数据库返回的记录的规则。每个规则对应于结果 SELECT 查询中的 WHERE 语句，并将指定的列与指定的值进行比较； 仅具有匹配的列值的记录将被获取。

AND 和 OR 逻辑运算符组合在一起组成规则，并且在应用许多规则时，可以将规则分组在一起以保留操作顺序。

输出映射为 XML

输出映射用 XML 表示，具有以下结构：父元素标识要从中进行 SELECT 的表，每个子元素对应于目标表中的一列。输出映射在数据库列和 XML 元素之间建立连接，以便端口可以从数据库结果中读取值并生成 XML 文档。

以下 XML 是 “Students” 表的输出映射示例：

<Items>
    <Students selectQuery="SELECT * FROM `Students` WHERE `GPA` = 4.0">
        <StudentID key="true" />
        <Name/>
        <Grade type="int"/>
        <GPA/>
    </Students>
</Items>

在以上示例中， StudentID, Name, Grade, 和 GPA 是 “Students” 表中的列。执行输出映射后，端口将运行指定的 selectQuery 并为返回的每个记录生成一个 XML 文档。以下是上述映射的示例 XML 输出文件：

<Items>
    <Students>
        <StudentID>12921</StudentID>
        <Name>Hermione Granger</Name>
        <Year>9</Year>
        <GPA>4.0</GPA>
    </Students>
</Items>

批量输出

当查询输出时，端口可以配置为支持将记录一起批量输出到单个文档中。 最大记录数 设置可用于控制单个消息中应包含多少条记录，批处理大小 设置可用于指示单个批处理组中应包含多少个批处理消息。

重写 SELECT 查询

输出映射 XML 显示将针对目标数据库运行的 SELECT 查询。端口基于输出映射中的设置创建此查询，但是如果需要对查询进行更详细的控制，则可以手动覆盖该查询。

只需编辑与数据库表共享名称的元素的 “selectQuery” 属性，并将其设置为任意 SELECT 查询。

只处理新添加或变更记录

可以将输出映射配置为仅检索最近更新或添加的记录。端口可以使用两种方法来检测哪些记录是新记录或已修改记录。

第一种方法需要一个 DateTime 列，该列表示记录的最后修改时间；第二种方法需要一个列，该列显式存储一个值，该值表示是否应处理记录。

使用最后修改时间

在“映射编辑器”中，展开面板底部的高级设置。如果检测到 DateTime 列，则该选项可用于只处理新添加或变更记录。将此设置为适当的 DateTime 列，该列表示记录的最后修改时间。

使用自定义列

可以将输出映射配置为更新表中的列，获取从数据库中成功提取的记录。可以将其与过滤器规则结合使用，该过滤器规则仅 SELECT 尚未被获取的记录（或应再次获取）。

要在处理后启用列更新，请展开映射编辑器底部的高级设置。启用只处理新添加或变更记录，然后指定应更新哪一列以及应在此列中更新的值。

例如，输出映射可能被配置为在提取记录后将 Processed 列更新为 “ 1”。然后，可以应用筛选规则，该规则将记录限制为仅 Processed不等于 1.

在此示例中，当端口处理输出模板时，它将基于模板创建 SELECT 语句。该模板将执行 “SELECT Id, AccountId, Amount FROM INVOICE”，并基于该模板为结果集中的每个记录创建一个 XML 文件，并将这些文件放在此端口的 Output 文件夹中。

多表查询

输出映射支持使用单个映射查询多个表。当创建具有多个表的输出映射时，这些表之间应具有外键关系，即一个表是另一表的子表。否则，请使用多个输出映射，从无外键关联的表中进行选择。

要查询子表，首先创建针对父表的输出映射。然后，使用左上角的+添加按钮从输出映射编辑器中添加子表。从表列表中选择适当的表，设计器将自动填充表的层次结构。选择子表中应包含在输出映射中的列。

接下来，应该将 WHERE 筛选器应用于子表映射，以反映与父表的外键关系。

主-从表的外键关系

通常，从父表和子表中进行 SELECT 时，仅当外键与父表中的主键匹配时，才应从子表中提取记录。

例如，假设有一个输出映射从父表 “PurchaseOrders” 和子表 “PurchaseOrderLineItems” 中提取采购订单数据。从 “PurchaseOrders” 中提取的每个记录（订单）也应提取与该订单关联的订单项。这两个表通过共享列关联，例如，PONumber 列是 “PurchaseOrders” 表的主键，而外键则是 “PurchaseOrdersLineItems” 表。仅当订单项的 PONumber 与从父表中提取的订单的 PONumber 相匹配时，才应从子表中提取订单项记录。

要在知行之桥输出映射中建立此关系，请将新的过滤器添加到子表映射中，并进行如下配置：

  • 在第一个（左侧）下拉列表中，选择子表的列，该列是父表的外键。在上面的示例中，这将是 “PurchaseOrderLineItems” 表的 PONumber 列。
  • 在中间（操作员）下拉列表中，选择 “equal”
  • 在最后一个（右侧）输入框中，首先使用右侧的下拉箭头选择 “REF”。之后，输入框应具有 $ 前缀，表明它正在引用父表。
  • 使用最后一个（右侧）下拉列表选择应与子表的外键匹配的父表列。在上面的示例中，这将是 “PurchaseOrders” 表的 PONumber 列。

主从输出 XML

包含父表和子表的输出映射的 XML 具有层次结构，可以反映父子关系。从父表到 SELECT 的查询和从子表到 SELECT 的查询都将包含在 XML 中：

<PurchaseOrders selectQuery="SELECT * FROM `PurchaseOrders` ">
  <PONumber key="true"/>
  <AccountId/>
  <Amount/>
  <AppliedAmount/>
  <Balance/>
  <BillingCity/>
  <BillingCountry/>
  <BillingLine1/>
  <BillingPostalCode/>
  <BillingState/>
  <InvoiceLineItem selectQuery="SELECT * FROM `PurchaseOrderLineItems` WHERE `PONumber` = ${PONumber}">
    <Id key="true"/>
    <PONumber/>
    <ItemAmount/>
    <ItemName/>
    <ItemQuantity/>
  </InvoiceLineItem>
</PurchaseOrders>

子表查询中的 ‘${PONumber}’ 语法表示该值引用父表中的一列 (PONumber)。

如果从 “PurchaseOrderLineItems” 表中提取了多个记录，则 InvoiceLineItem 元素（及其所有子元素）将在输出 XML 中多次出现。

输入/输出映射

一些数据库操作需要输入并且生成输出。输入/输出映射提供了一种为同一数据库操作为输入文件和输出文件设置 XML 模式的方法。

创建输入/输出映射

输入/输出映射使用与输入映射和输出映射相同的方法创建。每个映射针对一个特定的存储过程，映射编辑器向导可以查看存储过程的预期参数。

输入参数

存储过程的输入参数由数据库系统定义，因此不需要在映射编辑器中进行配置来选择相关的输入参数。系统公布的任何参数都显示在映射编辑器中，并且不能取消选择或以其他方式配置这些参数。

输入/输出映射为 XML

一旦创建了输入/输出映射，就会生成一个 XML 模板来匹配所需的输入参数，并生成一个 XML 模板来处理来自存储过程的输出。

输入 XML

输入文件的 XML 模板遵循与输入映射的 XML 模板相同的原则；匹配模板 XML 结构的文件将被解释为数据库操作的输入。有关更多详细信息，请参阅 Input Mappings as XML 部分。

输出 XML

存储过程的输出不是由数据库系统预先定义的。因此，可能需要提供输入参数的测试集，以便端口可以调用过程并检测输出的结构。

要提供一组示例输入，请使用映射编辑器右上角的 显示示例输入 按钮。在结果模态中提供一组测试输入后，端口将显示它接收到的输出的 XML 结构。复制此 XML 结构或使用 保存样本数据 按钮在应用程序的其他方面使用此 XML 结构，例如 XML Map 端口模板。