Hi FME’ers,

It’s that time of year when we ask: where does the time go? Our children start a new year at school (and look how old they are now!), in the northern hemisphere the sun sets earlier and earlier, and at Safe HQ I do my annual lookup for how to spell “sneak peek”!

Yes, FME2016 is coming, and it’s time for me to start talking about new functionality you’ll find. In particular, what updates will have an immediate impact on how you work.

This post is going to highlight some useful updates to our SQL editor dialogs.

EvangelistBanner7

SQL Dialogs

The SQL editor is a dialog that opens wherever FME allows you to inject SQL commands into a translation; the SQLExecutor is the most obvious example (and all the screenshots that follow will be from that transformer):

SQLExecutor2016-1

Here’s what the next editor dialog looks like:

SQLExecutor2016-2

The Run button is an obvious addition, but there are also a couple of hidden features that are worth knowing about.

EvangelistBanner7

The Run Button

You’ll quickly realize that the Run button makes this dialog slightly different to any other in FME. Rather than being executed as part of the workspace, it actually runs the SQL statement immediately.

The original idea was to test your SQL select statements. Like so:

SQLExecutor2016-3

Using that I can be sure that whatever select statement I am using, it will work correctly when the workspace is run; in short, the table exists and I have permission to access it.

But we thought this could also be a useful tool for pre-processing, like creating or emptying a table, like so:

SQLExecutor2016-4

In this case I am truncating the table. Then when I run the workspace it will be populated from empty.

So the important point there is that the action occurs and is committed! Whatever you run here WILL affect your table immediately. That’s why we have a precautionary dialog to want you:

SQLExecutor2016-5

It’s also important to remember that the run button is merely a convenience to do a quick, one-off action. If I wanted to truncate the table every translation, I just let it run during the translation itself. I don’t need to open this particular dialog and click run every time.

I’m told the run button works for databases of the following formats: PostGIS, MS SQL Server, Oracle, MySQL.

EvangelistBanner7

Right-Click Options

So much for the run button. The other improvements I wanted to highlight are various shortcuts to SQL functionality. For example, in the editor dialog there is a section that lists the tables available in the database. If I right-click that table I am presented with the following menu:

SQLExecutor2016-6

Basically the context menu now includes options to automatically add create, drop, duplicate, truncate, cross join, and select statements – including the table name – directly into the SQL editor window.

What’s more, if I select multiple tables then my truncate statement (for example) includes all of the selected tables:

SQLExecutor2016-7

Notice it even adds the command to identify the delimiter character!

Additionally I can select a column or columns from the table

SQLExecutor2016-8

The SQL from the above would be:

FME_SQL_DELIMITER ;
SELECT name FROM public.city_parks;

And there is also an option to paste just the table or column name directly into the SQL – in case you are building your own statements and not using one of our predefined ones.

EvangelistBanner7

Other Database Stuff

There are a couple of other enhancements I can mention re databases in general.

As you’ll know, right-clicking a feature type in Workbench pops up a context menu with the option to Inspect the data – literally open it up in the Data Inspector.

SQLExecutor2016-9

In previous versions, this option didn’t work with database formats. It does now! The Data Inspector remembers what format, database, and tables are being read, and populates the open dataset dialog with those values.

You’ll have also noticed there is an inspect option in the SQL Editor context menus too!

The one final database improvement I wanted to mention: named connections now have their own section in the Navigator window in Workbench:

SQLExecutor2016-10

EvangelistBanner7

So, there are some database updates for 2016. Hopefully, by being aware of them, anything different about the interface won’t catch you out when you upgrade – in particular I hope you won’t be clicking the SQL editor run button just to see what it does!!!

I’ll be posting more articles about upcoming 2016 functionality shortly. The list of what I’m hoping to post is shown below.

NewBlogSignature

Forthcoming (I hope) Evangelist articles:

About FME Data Inspector Database FME Evangelist SQL SQLCreator SQLExecutor Usability

Mark Ireland

Mark, aka iMark, is the FME Evangelist (est. 2004) and has a passion for FME Training. He likes being able to help people understand and use technology in new and interesting ways. One of his other passions is football (aka. Soccer). He likes both technology and soccer so much that he wrote an article about the two together! Who would’ve thought? (Answer: iMark)

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts