Monday, 27 April 2009

Using the Data Query view in FDO Toolbox (part 2): CRUD operations

Read Part 1 here.

One of the new features in the recently released FDO Toolbox is the CRUD (Create, Read, Update, Delete) feature. This feature finally exposes the full read/write capabilities of the FDO API to whatever providers that support such capabilities (nearly all bundled FDO providers support the full quartet of IInsert, ISelect, IUpdate and IDelete commands).

Part 1 covered the R part of CRUD (and the many ways to do it). This part will cover the rest of the quartet.

Creating (Inserting) new features

If the underlying FDO provider supports the IInsert FDO command, then you can use FDO Toolbox to insert new features into that particular data store. There are two ways to insert a new feature, you can either right click the feature class (to insert into) from the Object Explorer and choose Insert New Feature:


Or you can go into Data Query, select the feature class (to insert into) and click New Feature.
Either command will take you to the following dialog which will allow you to enter the attributes and geometry of this new feature:

Any property/attribute with a greenish background is an optional attribute (ie. In FDO terms, it is a nullable property). To enter geometry values, specify the FGF textual form of the geometry.

Unlike SQL INSERT commands, FDO does not actually support computed expressions for insert values. For example, you can't use the CurrentDate() expression function as a value for a datetime property. This is a limitation of the FDO API, that may hopefully be addressed in the future.

If the FDO provider supports transactions, the Use Transactions check box will be available for you. Once you have entered this information, click Insert to insert the new feature.

Updating existing features

If the underlying FDO provider supports the IUpdate FDO command, then you can use FDO Toolbox to update existing features in a particular data store.

To update existing features, perform a query using the Data Query view in Standard Mode, and then right click one of the results and choose Update this Feature. It will perform some checks to determine if the resulting update will only affect the selected result. If the checks pass, you will get a dialog similar to the Insert one:

Once again, the greenish fields are optional and geometry values must be in FGF text format. Again, the Use Transaction check box is available if the FDO provider supports transactions.

Unlike SQL UPDATE commands, FDO also does not actually support computed expressions for update values. For example, you can't use the CurrentDate() expression function as a value for a datetime property. Or you cannot set a string property (eg. Name) equal to the expression Translate(Name, 'a', 'o'). This is once again, a limitation of the FDO API, that may hopefully be addressed in the future.

Make your changes and click Update to update that feature. You can verify the feature has been updated by re-running the same query, the result that was edited will have been updated.

Deleting features

If the underlying FDO provider supports the IDelete FDO command, then you can use FDO Toolbox to delete existing features in a particular data store.

To delete existing features, perform a query using the Data Query view in Standard Mode, and then right click one of the results and choose Delete this Feature. It will perform some checks to determine if the resulting delete operation will only affect the selected result. If the checks pass, you will get asked for confirmation.

Click Yes to delete the selected feature. You can verify the feature has been deleted, by re-running the same query, the selected feature will no longer be in the query result set.

Bulk Updating features


WARNING: This is a dangerous operation. If you are not careful with the update filter specified, it could cause irreversible data changes! An empty filter, will cause bulk updates to be applied to all the data!

If the underlying FDO provider supports the IUpdate FDO command, then you can use FDO Toolbox to bulk update a series of features. When used carefully and properly, this is a useful and powerful way to change or correct certain attributes in a feature class.

To perform a bulk update, right click a feature class object in the Object Explorer and choose Bulk Update. Because of the dangerous nature of this operation, you will be asked for confirmation.


Click Yes and you will get a dialog like the one below:

The beauty of this dialog, is that only the checked properties will be applied in the bulk update, this allows for a great degree of flexibility in your bulk updates.

If the FDO provider supports transactions, the Use Transactions check box will be available.

Specify the update filter for this bulk update. This is important, because an empty filter will apply your updated values to all the features in the feature class! You can test the update filter by clicking Test. This will check how many features the bulk update operation will affect.

Once you are comfortable with the test results, click Update to do the real thing. Once again, due to the dangerous nature of this operation, you will be asked for confirmation. Say Yes to perform the bulk update.

Bulk Deleting features

If bulk update is Little Boy, then bulk delete is the Tsar Bomba. With that in mind...

WARNING: This is an extremely dangerous operation! If you are not careful with the delete filter specified, it could cause irreversible data loss! An empty filter, will cause the delete operation to delete everything in the feature class! You have been warned ;-)

If the underlying FDO provider supports the IDelete FDO command, then you can use FDO Toolbox to bulk delete a series of features from a data store.

To perform a bulk delete, right click the feature class object in the Object Explorer and choose Bulk Delete. Due to the potential for butchering your data, you will be asked for confirmation.


Click Yes and you will get the bulk delete dialog:


If the FDO provider supports transactions, the Use Transaction check box is available.

Unless you want to delete everything, specify a delete filter, just like the bulk update dialog, you can test to see how many features would be deleted by clicking the Test button.


Once you are certain everything is alright, click Delete to perform the actual delete operation. Just in case you have second thoughts, you will be asked for confirmation again. Say Yes to finally start performing the bulk delete operation. The time it takes for the operation to complete depends on number of features affected (using the Test function)

And that wraps up the CRUD feature of FDO Toolbox. Watch this space for further showcases of FDO Toolbox's features.

3 comments:

Warren J. Medernach, GISP said...

Hey Jackie, I realize you've moved on to managing Maestro, but I was hoping I might ask a question about FDO Toolbox.

I am attempting to create a Bulk Copy procedure with a filter, but the filters don't seem to 'stick'. When I view the saved XML it is not there either.

Can you let me know what the syntax is to manually try to add an Expression Mapping? I'm assuming something like:

< ExpressionMappings>
< MyExpression>
< unsure of syntax here />
< / MyExpression>
< / ExpressionMappings>

Thanks for your time.
Warren M

Jackie Ng said...

There is an XML schema definition (BulkCopyTask.xsd) that describes the structure of a bulk copy task.

If XSDs are alien to you, the simple answer is that the ExpressionMappings element can have any number of ExpressionMapping elements. Each ExpressionMapping element requires a unique alias (to identify this expression) and a target property where values from this expression are written to.

Optionally you can specify whether to use NULL in case of failed conversion (nullOnFailedConversion. Default value: true) or whether to truncate the value to be inserted if the target property is of a lower precision (truncate. Default value: true)

Here's an example with 1 expression mapping that maps the CurrentDate() expression to a property named "MyTargetProperty", because I haven't explicitly specified any conversion attributes, they will default to "true":

< ExpressionMappings>
< ExpressionMapping
alias="MyFdoExpr"
target="MyTargetProperty">
< Expression>
CurrentDate()
< /Expression>
< /ExpressionMapping>
< / ExpressionMappings>

Hope that helps.

Warren J. Medernach, GISP said...

Perfect!!
Thanks so much Jackie.

Warren