I used views in SQL Server Spatial as source dataset/format in FME. Inside the views I created geometry column. Now, I need to make Stored Procedure instead of view.
My question is: What are differences by using SQLCreator as "Reader" instead of real Reader? What about performance and so on? Is there some better solution or?
the SQLCreator is useful when you need to access data in an SQL enabled database using e.g. compound statements, without having to create views. The SQLCreator, does, however, not support all spatial formats, notably no support for ESRI Geodatabase. The SQLCreator is also useful to explicitely CAST certain data types into something that FME supports. An example would be Oracle RAW columns that can be cast into text this way.
For performance, it is my experience that nothing beats testing with your own data and your own formats.
David
I need SQLCreator explicitly for MS SQL Server to work with stored procedures. But, he doesn't "see" geometry I created in SP. This is probably one of unsupported formats.
Thank you!
consider casting the geometry to WKB (http://technet.microsoft.com/en-us/library/bb933881(v=sql.105).aspx) in the stored procedure (or in the SQLCreator). You can then reconstruct the geometry in FME using a GeometryReplacer on the WKB blob.
I do this all the time to work around the limitation in the SQLCreator and SDE tables, works really well.
David
+2
consider casting the geometry to WKB (http://technet.microsoft.com/en-us/library/bb933881(v=sql.105).aspx) in the stored procedure (or in the SQLCreator). You can then reconstruct the geometry in FME using a GeometryReplacer on the WKB blob.
I do this all the time to work around the limitation in the SQLCreator and SDE tables, works really well.
David
Super tip, this .STAsBinary() , David! Remember to use a non-spatial format in this case. Though, still "Microsoft SQL Server Spatial Reader: Query failed, possibly due to a malformed statement " when using SQLCreator on MSSQL (FME 2017).
Super tip, this .STAsBinary() , David! Remember to use a non-spatial format in this case. Though, still "Microsoft SQL Server Spatial Reader: Query failed, possibly due to a malformed statement " when using SQLCreator on MSSQL (FME 2017).
You're right, thanks for the heads up!
Another consideration: Readers and Writers cannot be used inside custom transformers, but transformers that read and/or write (SQLCreator, SQLExecutor, FeatureReader, FeatureWriter) can be used inside custom transformers.
This is a big distinction if you are trying to increase the performance of your translation using parallel processing. If your stored procedure is parameterized so that it only returns a partition of the source data, in certain situations you can completely isolate the extraction, transformation, and loading of independent partitions inside independent fmeworker processes.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
