Bulk Edits Oracle: Update a Database Using an FME Server App

Liz Sanderson
Liz Sanderson
  • Updated

FME Version

  • FME 2019.x

Introduction

FME Server can create Server Apps that allow users to run a workspace and enter parameters without the need to log in. These apps allow users to update datasets on the fly while out in the field using their mobile phones. In this article, we show you how to setup Oracle and FME Server to respond to bulk feature database edits. If a small number of edits are being made, a single insert can also be set. This is described in the following article Single edits Oracle: Update a Database Using an FME Server App.

 

Requirements

  • FME Server 2019 or later

  • FME Desktop 2019 or later

  • Access to an Oracle Spatial connection

 

Step-by-Step Instructions

Part 1: FME Desktop

Before we begin, we will need to open the two workspaces in FME Desktop to change the parameters to our own Oracle database.

 

1. Open FME Desktop

Note: If you completed the Single Edits Oracle article, you can skip to Part 1 - Step 4 as you will already have the table loaded into your database.

 

First, we need to create a table in our database. Open CreateTableInOracle.fmwt and expand the Oracle writer in the Navigator window. Double-click on Connection to open the connection parameters.

updateoracle.png

 

In the Edit ‘Oracle [ORACLE_SPATIAL]’ dialog, click the drop-down next to Connection and click Add Database Connection.

createconnection.png

 

Then add your Oracle database connection parameters. For more information on setting up an Oracle database connection, please see the article Viewing and Inspecting Oracle Data . Once the connection has been added, ensure it is selected, and click OK.

 

2. Change Table Parameters

There is one more thing to change before we can run this workspace. Double-click on the Oracle Writer feature type to open the Feature Type parameters. Change the Table Qualifier name to the username of your database. For this example, we are using the SUPPORT username. Once that is updated, click OK.

updatetablequalifier.png

 

3. Run the workspace

Save the workspace and then run it. This will create a new table in your Oracle database called Tree_Survey.

 

Go into your Oracle database, or inspect the output in FME to confirm that the table was written correctly.

 

4. Open Second Workspace

Close the first workspace and then open BulkUploadTreeSurvey.fmw. This workspace reads in an Excel spreadsheet containing an entire neighborhood’s worth of a tree survey. The workspace will also read in the database table that we just created. Then using both data sources, the workspace will determine whether or not the data already exists in the database. Then based on the result, it will INSERT, UPDATE, or DELETE the Excel data in the database.

 

Let’s go through what this workspace does:

a. Reads in the Excel spreadsheet containing all of the tree surveys for each neighborhood

b. Reads in the Tree_Survey database table created with CreateTableInOracle.fmwt. Then tests Tree_ID from the Excel spreadsheet exists in the database. If Tree_ID doesn’t exist, it will move on to the INSERT section. If the Tree_ID exists, it will move on to the UPDATE or DELETE section.

c. Tests to see if the tree has been removed, based on the Removed attribute. If yes, then it triggers the DELETE workflow. If no, then it moves to UPDATE.

workflow1.png

 

d. Sets the database operation with the FME specific attribute fme_db_operation. Additionally, it cleans up any unneeded attributes.

e. Writes out to the same Oracle database table as the FeatureReader, and sets the database operation based on the fme_db_operation in the earlier AttributeManagers.

f. Cleans up attributes in preparation to write out to a log file.

g. Writes out the Database Operation, Date and which attributes were written to the database in a Microsoft Excel file. This file can be used to monitor changes to the database.

workflow2.png

 

5. Update FeatureReader

In this second workspace, open the FeatureReader parameters and change the Connection to the Oracle connection you created in the previous workspace. Additionally, click the ellipsis next to Feature Types to Read and select the Tree_Survey in your database. Click OK to close the Feature Reader.

featurereader.png

 

When you update the FeatureReader ensure that the output port yourusername.TREE_SURVEY is connected to the FeatureJoiner Right input port.

featurereaderconnection.png

 

6. Update Oracle Writer

Next, we will need to update the connection parameters for the Oracle Writer. In the Navigator window, change the Oracle connection to your connection, the same way as the previous workspace.

setoracleconnection.png

 

Once the connection is created, open up the Oracle Writer Feature Type and change the Table Qualifier to the username you used in the previous workspace, in this example, we are using SUPPORT. Also change Table Name, if necessary.

updatetablequalifier.png

 

7. Inspect Bulk Parameters

The Oracle Spatial Object has parameters to help with bulk transactions. These parameters have already been set up in this workspace, but let’s inspect them so you can modify them if you wish.

 

In the Navigator window, expand the Oracle [ORACLE_SPATIAL] writer, then expand the Parameters section, then expand Advanced.

To increase bulk transactions to the database, you can modify the Bulk Write Size or the Features Per Transaction parameters. Currently, we have it set to 500 and 2000, respectively, which is a good number since each of the Excel spreadsheets contains anywhere from 1000 to 4000 records.

bulkparams.png

 

8. Test the Workspace

Before we publish this workspace to FME Server, it is a good idea to test it locally. Ensure that Prompt for User Parameters is enabled and then run the workspace.

 

In the Translation Parameter Values dialog, select any ONE feature type from the Excel file. You can choose more than one, but it will cause the workspace to run slower.

You can ignore the Microsoft Excel File parameter, this will become relevant in FME Server.

Uncheck Save As User Parameter Default Values

parameters.png

 

Once the parameters are entered, click Run. This will trigger an INSERT in the database since this is new data. You can inspect the database to confirm this update. As well, you can also view the Microsoft Excel TreeLog, which will include the parameters we just set, the database operation INSERT, and today’s date and time.

visualpreview.png

 

 

If you want to test DELETE and UPDATE, rerun the entire workspace and select the SAME Survey to Read parameter as before.

 

8. Publish to FME Server

Once you have confirmed that this workspace runs successfully with your database connection, it is time to publish it to FME Server.

 

Before publishing to FME Server, you need to decide if you want your Excel log file to be available to the user running the Server App. If you do not, you can either leave the Excel file to be updated locally on your machine OR you can set it up to update in the FME Server Resources folder.

 

To update the file to save it to FME Server, expand the TreeDatabase-LogReport writer in the Navigator window. Then double-click on the Destination Microsoft Excel File parameter to open up the edit dialog. In the edit dialog, click on the drop-down next to Destination Microsoft Excel File, then choose User Parameter > FME Server Parameters > FME_SHAREDRESOURCE_DATA. Then after $(FME_SHAREDRESOURCE_DATA) type in \ServerApps\TreeDatabase-LogReport.xlsx

 

$(FME_SHAREDRESOURCE_DATA)\ServerApps\TreeDatabase-LogReport.xlsx

 

serverresource.png

 

Click OK. Save the workspace, and then click on the Publish icon in the top toolbar.

 

In the Publish to FME Server dialog, create an FME Server connection if you do not already have one. Then ensure this workspace is saved to a Repository called ServerApps. Be sure to upload your Oracle connection. Then register with the Job Submitter service if you want your log file private and/or the Data Download service if you want the log file to be public. The Data Download service will download the log Excel file every time the app is run.

 

Part 2: FME Server

With the workspace published, we can create an FME Server App. An FME Server App allows users to run an FME Server workspace without logging in. It also allows the user to run the workspace on their mobile phone. For more information on FME Server Apps, please see the article Getting Started with FME Server Workspace Apps

 

1. Log into FME Server

Log into FME Server with an account that has privileges to create FME Server Apps. Once logged in, go to the Server Apps section and then click on Create.

createnewapp.png

 

Additionally, you can create an FME Server App through the Run Workspace Page Advanced section.

 

2. Create FME Server App

On the Create Server App page, give your app a name and short description. Then select the Repository and Workspace.

 

Choose which Service you want to use. Remember if you want your log file public, use the Data Download service, if you want it private, use the Job Submitter.

 

Next, check the Expiration date. By default, apps are set to expire in 10 years. It might be a good idea to set this expiration date to a shorter time. This expiration time can be changed at any time.

 

Finally, ensure that User Can Upload is enabled. This allows the user to upload a new Excel file to the app.

serverappcreation.png

 

Now expand the Parameters section, and double-check the defaults for the app. If your parameters have values next to them, click on the Reset button. By setting all the values to blank, this will ensure that the user doesn’t miss a parameter when they enter their own values. Also ensure that all of the desired parameters are being shown in the app, which is indicated by the green checkmark next to each parameter.

appparams.png

 

Finally, you can expand the Customize Appearance section and add color, text, or images to your final app page.

 

Once you are satisfied with your app, click OK. You will get a URL that links to your app.

 

3. Test App

Click on the URL that was provided at the end up the app setup.

appurl.png

Note: To share the FME Server App with anyone, the FME Server must be accessed externally, not internally using localhost.

 

If you navigated away from the page with the URL, you can access it again by going to Server Apps on the side menu and then clicking on the URL icon next to your app name.

 

To test the Server App, enter the following parameters:

Survey to Read: Kitsilano_Trees

Source Microsoft Excel File(s): Browse to your downloads, locate OracleBulkTreeSurvey and the Data folder and then select Vancouver_Trees.xlsx

 

To upload a file, click on the Click to Browse box, then in the popup dialog, click the Upload button and then browse to your file. Once the file is uploaded, click OK.

uploadfile.png

 

Once your parameters are set, click on Run.

appcompleted.png

 

You can also change the parameters to trigger a DELETE or UPDATE by rerunning the app and selecting the same Survey to Read.

 

Data Attribution

The data shown here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.