ArcSDE - exception when calling getBounds with schema specified
|
|
roar.branden
|
Hi, I'm having a problem with a getBounds call againts a FeatureSource which references an ArcSDE feature layer. We are using Oracle as our ArcSDE server. The problem seems to be related to table name and the use of schema. I'm using a user that has access to several schemas, so I have to reference the table with schema. This is my code: ReferencedEnvelope getBoundsForSamplePoints(List<Integer> samplePointIds) throws IOException { List<Filter> sampleIdFilterList = new ArrayList<Filter>(); for (Integer sampleId : samplePointIds) { sampleIdFilterList.add(filterFactory.equals(filterFactory.property("SAMPLE_POINT_ID"), filterFactory.literal(sampleId))); } FeatureSource<SimpleFeatureType, SimpleFeature> points = arcStore.getFeatureSource("NIVA_GEOMETRY.SAMPLE_POINTS"); Query query = new DefaultQuery("NIVA_GEOMETRY.SAMPLE_POINTS", filterFactory.or(sampleIdFilterList)); return points.getBounds(query); } It ends up with an ArcSdeException: [SDE error -42][Error desc=USER SPECIFIED WHERE CLAUSE IS INVALID.][Extended desc=] In the ArcSDE log-files at the server I find this message: SELECT MIN(a.SHAPE.minx), MIN(a.SHAPE.miny), MAX(a.SHAPE.maxx), MAX(a.SHAPE.maxy), MIN(a.SHAPE.minz), MAX(a.SHAPE.maxz) FROM NIVA_GEOMETRY.SAMPLE_POINTS a where ((NIVA_GEOMETRY.SAMPLE_POINTS.SAMPLE_POINT_ID = 11602 OR NIVA_GEOMETRY.SAMPLE_POINTS.SAMPLE_POINT_ID = 10041)) and a.SHAPE.entity != 0 and a.SHAPE.numpts != 0 [Fri Oct 16 13:28:25 2009] [4683] [GK9X12J-L] SDE_Oracle Error: 904 ORA-00904: "NIVA_GEOMETRY"."SAMPLE_POINTS"."SAMPLE_POINT_ID": ugyldig identifikator As you can see the name NIVA_GEOMETRY.SAMPLE_POINTS is replaced with an "a" almost everywhere. The only exception being the where-clause. How can I change it here as well? Or could I use a different approach? I'm using GeoTool 2.6. Best regards Roar Brænden ------------------------------------------------------------------------------------------------------------ Meld deg på vårt nyhetsbrev på www.niva.no ------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ Geotools-gt2-users mailing list [hidden email] https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users |
||||||||||||||||
Wu Yongfeng
|
Hi：
I have test with ArcSDE 9.3 for PostgreSQL, everything works okey. 2009/10/16 <[hidden email]>
------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ Geotools-gt2-users mailing list [hidden email] https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users |
||||||||||||||||
Gabriel Roldan
|
In reply to this post
by roar.branden
Looks like an SDE bug to me, as it is ArcSDE the one creating the SQL
queries. From the poing of view of GeoTools and the ESRI ArcSDE Java API what you're doing looks right. What version of Arcsde/Oracle are you running? may be you want to try and ArcSDE service pack or something like that. Regards, Gabriel [hidden email] wrote: > > Hi, > > I'm having a problem with a getBounds call againts a FeatureSource which > references an ArcSDE feature layer. We are using Oracle as our ArcSDE > server. The problem seems to be related to table name and the use of > schema. I'm using a user that has access to several schemas, so I have > to reference the table with schema. > > This is my code: > ReferencedEnvelope getBoundsForSamplePoints(List<Integer> > samplePointIds) *throws* IOException { > List<Filter> sampleIdFilterList = *new* > ArrayList<Filter>(); > *for* (Integer sampleId : samplePointIds) { > > sampleIdFilterList.add(/filterFactory/.equals(/filterFactory/.property("SAMPLE_POINT_ID"), > /filterFactory/.literal(sampleId))); > } > > FeatureSource<SimpleFeatureType, SimpleFeature> points = > arcStore.getFeatureSource("NIVA_GEOMETRY.SAMPLE_POINTS"); > Query query = *new* > DefaultQuery("NIVA_GEOMETRY.SAMPLE_POINTS", > /filterFactory/.or(sampleIdFilterList)); > > *return* points.getBounds(query); > } > > It ends up with an ArcSdeException: > [SDE error -42][Error desc=USER SPECIFIED WHERE CLAUSE IS > INVALID.][Extended desc=] > > > > In the ArcSDE log-files at the server I find this message: > > SELECT MIN(a.SHAPE.minx), MIN(a.SHAPE.miny), MAX(a.SHAPE.maxx), > MAX(a.SHAPE.maxy), MIN(a.SHAPE.minz), MAX(a.SHAPE.maxz) FROM > NIVA_GEOMETRY.SAMPLE_POINTS a where > ((NIVA_GEOMETRY.SAMPLE_POINTS.SAMPLE_POINT_ID = 11602 OR > NIVA_GEOMETRY.SAMPLE_POINTS.SAMPLE_POINT_ID = 10041)) and > a.SHAPE.entity != 0 and a.SHAPE.numpts != 0 > [Fri Oct 16 13:28:25 2009] [4683] [GK9X12J-L] SDE_Oracle Error: 904 > ORA-00904: "NIVA_GEOMETRY"."SAMPLE_POINTS"."SAMPLE_POINT_ID": ugyldig > identifikator > > As you can see the name NIVA_GEOMETRY.SAMPLE_POINTS is replaced with an > "a" almost everywhere. The only exception being the where-clause. How > can I change it here as well? Or could I use a different approach? > > I'm using GeoTool 2.6. > > Best regards > Roar Brænden > > ------------------------------------------------------------------------------------------------------------ > Meld deg på vårt nyhetsbrev på www.niva.no > > > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------------ > Come build with us! The BlackBerry(R) Developer Conference in SF, CA > is the only developer event you need to attend this year. Jumpstart your > developing skills, take BlackBerry mobile applications to market and stay > ahead of the curve. Join us from November 9 - 12, 2009. Register now! > http://p.sf.net/sfu/devconference > > > ------------------------------------------------------------------------ > > _______________________________________________ > Geotools-gt2-users mailing list > [hidden email] > https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users -- Gabriel Roldan OpenGeo - http://opengeo.org Expert service straight from the developers. ------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ Geotools-gt2-users mailing list [hidden email] https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users |
||||||||||||||||
|
roar.branden
|
In reply to this post
by Wu Yongfeng
Hi, Can you figure out the final sql? In my example I get this sql: SELECT MIN(a.SHAPE.minx), MIN(a.SHAPE.miny), MAX(a.SHAPE.maxx), MAX(a.SHAPE.maxy), MIN(a.SHAPE.minz), MAX(a.SHAPE.maxz) FROM NIVA_GEOMETRY.SAMPLE_POINTS a where ((NIVA_GEOMETRY.SAMPLE_POINTS.SAMPLE_POINT_ID = 11602 OR NIVA_GEOMETRY.SAMPLE_POINTS.SAMPLE_POINT_ID = 10041)) and a.SHAPE.entity != 0 and a.SHAPE.numpts != 0 which is an illegal sql for Oracle. What I'm wondering about is wether this is a legitimate sql for MySQL, or does the ArcSDE-library for MySQL make a different sql. Best regards Roar Brænden
Hi： I have test with ArcSDE 9.3 for PostgreSQL, everything works okey. 2009/10/16 <roar.branden@...> Hi, I'm having a problem with a getBounds call againts a FeatureSource which references an ArcSDE feature layer. We are using Oracle as our ArcSDE server. The problem seems to be related to table name and the use of schema. I'm using a user that has access to several schemas, so I have to reference the table with schema. This is my code: ReferencedEnvelope getBoundsForSamplePoints(List<Integer> samplePointIds) throws IOException { List<Filter> sampleIdFilterList = new ArrayList<Filter>(); for (Integer sampleId : samplePointIds) { sampleIdFilterList.add(filterFactory.equals(filterFactory.property("SAMPLE_POINT_ID"), filterFactory.literal(sampleId))); } FeatureSource<SimpleFeatureType, SimpleFeature> points = arcStore.getFeatureSource("NIVA_GEOMETRY.SAMPLE_POINTS"); Query query = new DefaultQuery("NIVA_GEOMETRY.SAMPLE_POINTS", filterFactory.or(sampleIdFilterList)); return points.getBounds(query); } It ends up with an ArcSdeException: [SDE error -42][Error desc=USER SPECIFIED WHERE CLAUSE IS INVALID.][Extended desc=] In the ArcSDE log-files at the server I find this message: SELECT MIN(a.SHAPE.minx), MIN(a.SHAPE.miny), MAX(a.SHAPE.maxx), MAX(a.SHAPE.maxy), MIN(a.SHAPE.minz), MAX(a.SHAPE.maxz) FROM NIVA_GEOMETRY.SAMPLE_POINTS a where ((NIVA_GEOMETRY.SAMPLE_POINTS.SAMPLE_POINT_ID = 11602 OR NIVA_GEOMETRY.SAMPLE_POINTS.SAMPLE_POINT_ID = 10041)) and a.SHAPE.entity != 0 and a.SHAPE.numpts != 0 [Fri Oct 16 13:28:25 2009] [4683] [GK9X12J-L] SDE_Oracle Error: 904 ORA-00904: "NIVA_GEOMETRY"."SAMPLE_POINTS"."SAMPLE_POINT_ID": ugyldig identifikator As you can see the name NIVA_GEOMETRY.SAMPLE_POINTS is replaced with an "a" almost everywhere. The only exception being the where-clause. How can I change it here as well? Or could I use a different approach? I'm using GeoTool 2.6. Best regards Roar Brænden ------------------------------------------------------------------------------------------------------------ Meld deg på vårt nyhetsbrev på www.niva.no ------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ Geotools-gt2-users mailing list [hidden email] https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users ------------------------------------------------------------------------------------------------------------ Meld deg på vårt nyhetsbrev på www.niva.no ------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ Geotools-gt2-users mailing list [hidden email] https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users |
||||||||||||||||||||||||||
|
roar.branden
|
In reply to this post
by Gabriel Roldan
We are using ArcSDE 9.3 and Oracle 11g. I looked in the documentation for ArcSDE Java and in SeSqlConstruct it says: public SeSqlConstruct(java.lang.String[] tables, java.lang.String where) Creates an SqlConstruct given an array of table names and a where clause. The ArcSDE software will construct the from clause with the same order of table names as defined in the array. The where clause must use the SQL syntax supported by the underlying RDBMS; It is not interprted by SDE
From this statement I understand it so
that I have to replace the table-names in the query with those that are
issued in SeSqlConstruct. In the log-files I saw that it was "a".
Beneath I have some code that presume that the alias will be "a"
and uses that in the where-clause.
Looks like an SDE bug to me, as it is ArcSDE the one creating the SQL queries. From the poing of view of GeoTools and the ESRI ArcSDE Java API what you're doing looks right. What version of Arcsde/Oracle are you running? may be you want to try and ArcSDE service pack or something like that. Regards, Gabriel [hidden email] wrote: > > Hi, > > I'm having a problem with a getBounds call againts a FeatureSource which > references an ArcSDE feature layer. We are using Oracle as our ArcSDE > server. The problem seems to be related to table name and the use of > schema. I'm using a user that has access to several schemas, so I have > to reference the table with schema. > > This is my code: > ReferencedEnvelope getBoundsForSamplePoints(List<Integer> > samplePointIds) *throws* IOException { > List<Filter> sampleIdFilterList = *new* > ArrayList<Filter>(); > *for* (Integer sampleId : samplePointIds) { > > sampleIdFilterList.add(/filterFactory/.equals(/filterFactory/.property("SAMPLE_POINT_ID"), > /filterFactory/.literal(sampleId))); > } > > FeatureSource<SimpleFeatureType, SimpleFeature> points = > arcStore.getFeatureSource("NIVA_GEOMETRY.SAMPLE_POINTS"); > Query query = *new* > DefaultQuery("NIVA_GEOMETRY.SAMPLE_POINTS", > /filterFactory/.or(sampleIdFilterList)); > > *return* points.getBounds(query); > } > > It ends up with an ArcSdeException: > [SDE error -42][Error desc=USER SPECIFIED WHERE CLAUSE IS > INVALID.][Extended desc=] > > > > In the ArcSDE log-files at the server I find this message: > > SELECT MIN(a.SHAPE.minx), MIN(a.SHAPE.miny), MAX(a.SHAPE.maxx), > MAX(a.SHAPE.maxy), MIN(a.SHAPE.minz), MAX(a.SHAPE.maxz) FROM > NIVA_GEOMETRY.SAMPLE_POINTS a where > ((NIVA_GEOMETRY.SAMPLE_POINTS.SAMPLE_POINT_ID = 11602 OR > NIVA_GEOMETRY.SAMPLE_POINTS.SAMPLE_POINT_ID = 10041)) and > a.SHAPE.entity != 0 and a.SHAPE.numpts != 0 > [Fri Oct 16 13:28:25 2009] [4683] [GK9X12J-L] SDE_Oracle Error: 904 > ORA-00904: "NIVA_GEOMETRY"."SAMPLE_POINTS"."SAMPLE_POINT_ID": ugyldig > identifikator > > As you can see the name NIVA_GEOMETRY.SAMPLE_POINTS is replaced with an > "a" almost everywhere. The only exception being the where-clause. How > can I change it here as well? Or could I use a different approach? > > I'm using GeoTool 2.6. > > Best regards > Roar Brænden > > ------------------------------------------------------------------------------------------------------------ > Meld deg på vårt nyhetsbrev på www.niva.no > > > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------------ > Come build with us! The BlackBerry(R) Developer Conference in SF, CA > is the only developer event you need to attend this year. Jumpstart your > developing skills, take BlackBerry mobile applications to market and stay > ahead of the curve. Join us from November 9 - 12, 2009. Register now! > http://p.sf.net/sfu/devconference > > > ------------------------------------------------------------------------ > > _______________________________________________ > Geotools-gt2-users mailing list > [hidden email] > https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users -- Gabriel Roldan OpenGeo - http://opengeo.org Expert service straight from the developers. ------------------------------------------------------------------------------------------------------------ Meld deg på vårt nyhetsbrev på www.niva.no ------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ Geotools-gt2-users mailing list [hidden email] https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users |
||||||||||||||||||||||||||
| Free Embeddable Forum Powered by Nabble | Help |
