span8
斯潘4
有很多方法可以“让数据库完成工作”,we'll take a look at the ways to use FME to perform a more effective write to Oracle.
使用FME写入数据库时,如果违反表上的约束,则可能发生错误。约束是限制数据库中列的可能值的规则。Typical constraints include not null,独特的,and keys (primary and foreign).The exercise outlined below will address a method to deal with a failure on load caused by writing to tables joined by a foreign key.
尽管本练习使用Oracle数据库,所讨论的概念和方法可应用于其他数据库,如PostgreSQL/邮政地理信息系统,和SQL Server.
本练习的目标是显示写入具有约束的数据库表时所具有的选项,such as a foreign key constraint.在这个例子中,您将加载水管网(wmans),并创建具有“管道制造商”属性的资源表(wasset)。这两个表通过一个外键约束(称为wmain_wasset)进行关联。
运行工作区
1.打开工作区:databasewrite-start.fmw。运行它。
工作区正在写入Oracle中的两个联接表。The parent table (WASSETS) has MANUFACTURERID as a primary key and the child table (WMAIN) has a primary key of MAINID with MANUFACTURERID as a foreign key.
The workspace fails throwing the following error:
|错误执行语句'insert into“wmain”(“mainid”,"ENABLED",...)值(:“mainid”,:"ENABLED",“没有成功;error was `ORA-02291: integrity constraint (WMAIN_WASSETS) violated - parent key not found'.(serverType=`ORACLE8I',serverName=`',用户名= ',password=`***',dbname=`')
发生此错误的原因是违反了wmain表上的外键约束(称为wmain-wasset)。To satisfy the constraint,you have to write to the parent table before writing the child table that has the foreign key.
The following examples illustrate different ways you can address this problem and write to the related tables.
Example 1 - Using FeatureHolder
featureholder用于保存指向子表(wmain)的功能。Since there are only a few features being written to the parent table (WASSETS),它能够在特性持有者释放特性之前完成写入。这实际上是工作流的阶段。亚搏在线
1.Before running this example,您需要清理数据库表。使用sqlcreator转换器删除并重新创建表。Open the transformer properties,选择SQL语句并选择“运行…”,然后选择“取消”退出Transformer属性。
2。Add a特征拥有者before the WMAIN table.
3.运行工作区并检查日志文件和结果。
FeatureHolder对于功能数量较少的工作区很有用。If there are a large number of records being written to the table and held in the FeatureHolder,它将影响整体性能。
Example 2 - Using Connection Runtime Order
You can control the order of features exiting a transformer using the Connection Runtime Order option.This example uses connection runtime order to ensure parent records are inserted first.
1.Before running this example,您需要清理数据库表。使用sqlcreator转换器删除并重新创建表。Open the transformer properties,选择SQL语句并选择“运行…”,然后选择“取消”退出Transformer属性。
2。从上一步中删除FeatureHolder Transformer。
3.Set the Connection Runtime Order by right clicking on one of the output connectors that exit the AttributeManager.确保先连接Wasset。这将确保在相应的wmain记录之前插入wasset记录,确保满足wmain约束。
4。Set the Features Per Transaction to 1.You have to use a transition interval of 1 to guarantee that the WASSETS records has been committed before the WMAIN record is inserted.
5。Run the translation.
As you can see,setting a transaction interval of 1 has a big impact on performance as a commit is carried out for every feature that is written.It does ensure that the records are written into the parent table prior to writing into the child table.
示例3-使用FeatureWriter
The FeatureWriter transformer is an alternative way of writing data.将写操作移到工作区工作流中可以使您在编写功能类型的顺序以及如何预处理和后处理数据方面具有更大亚搏在线的灵活性。
In this example you'll use SQL calls to disable and then enable the WMAIN_WASSETS foreign key constraint.In between you'll load the data.
1.如上面的示例2所述,使用sqlcreator删除并创建表,这样您就可以从一个干净的石板开始。
2。禁用或删除Oracle编写器。
3.Add a SQLCreator for the Oracle database and add the following SQL:
FME_SQL_DELIMITER ;ALTER TABLE "WMAIN" disable CONSTRAINT WMAIN_WASSETS;
在打开任何FME读卡器之前执行sqlCreator,因此,我们可以保证在开始编写任何特性之前删除约束。
4。Add a FeatureWriter to the workspace canvas.Open the FeatureWriter parameters dialog.Select the Oracle Spatial format and select your dataset.
5。导入Oracle表,wmain和wasset使用Oracle非空间格式。
6.如步骤7所示连接FeatureWriter。The order of the features types is not important since we disabled the constraint.
7.Add a SQLExecutor for your Oracle database and add the following SQL:
fme_sql_delimiter;alter table“wmain”启用约束wmain_wasset;
8.运行工作区并检查结果。
What are the pros & cons of each example?Perhaps each approach is more appropriate for different scenarios and table constraints such as 1:M M:N etc.
哪个性能最好?你能用性能来换取简单吗?
Which example might work best for an update 亚搏在线workflow?
还有其他方法吗?
What if there is an error,哪种方法最容易恢复?
Making use of Oracle Sequences when working with Oracle Tables
这个属性计算器可用于加载前检查支持的条件。这允许用户查看和更正可能导致写入失败的任何功能。
?2019安全亚搏在线软件公司|合法的