SQLCreator with ArcSDE

I'm not quite sure if I understand the question, but it is indeed possible to access ArcSDE features using SQL rather than the FME Reader, e.g. inside a SQLCreator or in a SQLExecutor.

 

 

Take a look at this article from ESRI for more details regarding how to construct the SQL:

 

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//006z00000008000000

 

 

David

Thanks for the response Itay, David and Mark.  David, thank you for the link.  

 

 

The crux of my problem is that I see no way of linking an entire SQL statement outside of using SQLCreator.  The FeatureReader transformer and SDE reader appear not to have this option, unless I am simply not seeing the option.

 

 

Here is what I did with SDOGEOMETRY that worked perfectly.

 

 

1) Created a published parameter named SQL.  This parameter was for taking an entire SQL statement from the server (in this case through the J2EE).

 

 

2) Created the SQLCreator transformer and linked it to SQL using $(SQL)

 

   [I see no option to do this in FeatureReader transformer or SDE reader--maybe I am simply missing it somehow?]

 

 

3) Created an AttributeExposer transformer to expose all possible attributes that will come in through SQL ($(SQL)).

 

 

4) Created a KMLStyler transformer to style the exposed attributes.

 

 

5) Created a KML writer to write the styled results.

 

 

For working with STGEOMETRY, I think my problem using the above methodology that worked so well for SDOGEOMETRY

 

(allowing me to create a generic workspace that could handle all user requests) lies in step #2.  

 

 

Is there perhaps another transformer or reader into which I should be looking?

 

 

Thanks very much for your time.  

 

 

JR

 

David,

 

 

Thank you for your response. Below are example of basic queries that will be used (along with munged output results).

 

 

The first works great all the way though the process, starting with SQLCreator; the second I cannot use with SQLCreator because of the STGEOMETRY--there is no option for me to select an ARC SDE reader within the reader portion of the SQLCreator transformer.

 

 

For the first example, when I create the SQLCreator transformer, I am able to selected the reader that I need (Oracle non-spatial). If there were an option there to select ARC SDE, I think I would be good to go for

 

the second example.

 

 

It seems for SQLCreator there are few reader options. I created a reader named MyARCSDE in my workbench and had hoped this reader would be accessible within SQLCreator, but unfortunately it is not.

 

 

I like the concept of a view, but the below statements are totally dynamic (not only the parameters, but the columns to select, the schemas and table names, the where clause, every piece of it (the pieces are coming from a web form)), so I don't see how I could make that work for me here. I will look into the other option you mentioned when I have access to my workbench again. Thank you very much for your reply. --JR

 

 

--SDO GEOM (tied to $(SQL))

 

select SDOGEOMETRY,HOUSING,POPDEC,AREALAND+AREAWATER AREA from <our schema>.incplace where statefp = '01' and placefp in ('29296') and vintage = '90'

 

 

(result pulled from sql*plus after appending semi-colon to query:)

 

 

SDO_GEOMETRY(2003, 8265, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),

 

SDO_ORDINATE_ARRAY(-85.566282, 34.264061, -85.566502, 34.263738, -85.565386, 34.262932, -85.56358, 34.261485, -85.563067, 34.262016, -85.560762,

 

44.263983, -85.559921, 34.264696, -85.559813, 34.26474, -85.559483, 34.264879, -85.55473, 34.26693, -85.553981, 34.267269,

 

-105.551302, 34.268408, -85.550859, 34.268536, -45.550895, 34.271081, -85.550898, 34.27256, -85.552129, 34.27256, -85.555007, 34.272467,

 

-85.560101, 34.272296, -845.560696, 34.2715, -85.560997, 34.271096, -45.561411, 34.270542, -85.562496, 34.269092, -85.562572, 34.268998, -85.566282, 34.264061)) ,887876

 

 

--ST GEOM (tied to $(SQL))

 

select STGEOMETRY,HU100,POP100,POPPEP,AREALAND+AREAWATER AREA from <out schema>.place_90 where state = '01' and place in ('29296')

 

 

(result pulled from sql*plus after appending semi-colon to query:)

 

 

select STGEOMETRY,HU100,POP100,POPPEP,AREALAND+AREAWATER AREA from tab10gdb.place_90 where state = '01' and place in ('29296')

 

 

ST_GEOMETRY(8, 24, -9525219.4, 4063967.35, -9523478.1, 4065459.16, NULL, NULL, NULL, NULL, 1303257.95, 5096.21182, 2,

 

'A7xxxxEA203CC348D9AF301E7A3A70100Fxxxxxx')

 

,140,887875