I'd like to get more information on when the SQL Creator can be used.
Question
Hi, I'm new to FME. I need to query an SQL database to read records based on query with multiple joins and then write to CSV after some processing. Is a Reader the best for this or Can I use SQL creator to extract the records. Which is better option.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+24- Influencer
Hello @nisa , it will depend at which phase of the process you want to read the information in.
In the image below, I have used the featurereader as I put together the where clause based on other actions in the workbench.
I have found this approach very useful but have used the SQL Creator useful when just needing to read something from the database and stored for later use.
http://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_Transformers/Transformers/sqlcreator.htm
Hello @nisa , it will depend at which phase of the process you want to read the information in.
In the image below, I have used the featurereader as I put together the where clause based on other actions in the workbench.
I have found this approach very useful but have used the SQL Creator useful when just needing to read something from the database and stored for later use.
http://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_Transformers/Transformers/sqlcreator.htm
Thanks @deanhowell As I understand with Feature Reader cannot perform table joins as in SQL. My workbench first queries an SQL Azure database using complex join and then uses the features generated for further processing. I'd like to confirm that the SQL Creator can do this.
+36Thanks @deanhowell As I understand with Feature Reader cannot perform table joins as in SQL. My workbench first queries an SQL Azure database using complex join and then uses the features generated for further processing. I'd like to confirm that the SQL Creator can do this.
You can use the SQLExecutor to preform SQL statements and receive the resulting rows as Features. I have used this a lot on a Postgres database with multiple joins and spatial selectors.
In this case I don't receive any features but let the database do all the work:
UPDATE maintable p SET owner = r.owner FROM zones r WHERE owner IS NULL AND ST_CONTAINS(r.geom,ST_PointOnSurface(p.geom));Also a SQLCreator can do this. In my opinion an SQLCreator is a Creator + SQLExecuter.
I don't have experience with SQL Azure databases yet but expect no issues here.