# HG changeset patch # User Tim Englich # Date 1264514155 0 # Node ID 0ee3c0ed40e4a45eee2f7e2d76155cf8598c858a # Parent 5b536542ef569a6653eb2727f324b00a6369ce44 Added two Sample-Application demonstrating Bugs in the ArcSDE geo-backend/trunk@630 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 5b536542ef56 -r 0ee3c0ed40e4 geo-backend/ChangeLog --- a/geo-backend/ChangeLog Mon Jan 25 12:12:31 2010 +0000 +++ b/geo-backend/ChangeLog Tue Jan 26 13:55:55 2010 +0000 @@ -1,3 +1,10 @@ +2010-01-26 Tim Englich + + * src/test/java/de/intevation/gnv/geobackend/base/query/ToCharSample.java : + Added Sample for bug in ArcSDE 9.3 using to_char-Function with SpatialQuery + * src/test/java/de/intevation/gnv/geobackend/base/query/GroupBySample.java : + Added Sample for Bug in ArcSDE using group by with SpatialQuery + 2009-01-25 Sascha L. Teichmann Another attempt to fix gnv/issue34 diff -r 5b536542ef56 -r 0ee3c0ed40e4 geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/GroupBySample.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/GroupBySample.java Tue Jan 26 13:55:55 2010 +0000 @@ -0,0 +1,123 @@ +package de.intevation.gnv.geobackend.base.query; + +import com.esri.sde.sdk.client.SDEPoint; +import com.esri.sde.sdk.client.SeColumnDefinition; +import com.esri.sde.sdk.client.SeConnection; +import com.esri.sde.sdk.client.SeException; +import com.esri.sde.sdk.client.SeFilter; +import com.esri.sde.sdk.client.SeLayer; +import com.esri.sde.sdk.client.SeQuery; +import com.esri.sde.sdk.client.SeQueryInfo; +import com.esri.sde.sdk.client.SeRow; +import com.esri.sde.sdk.client.SeShape; +import com.esri.sde.sdk.client.SeShapeFilter; +import com.esri.sde.sdk.client.SeSqlConstruct; + +public class GroupBySample { + + + public void executeQuery(SeConnection con, String[] pLayername, + String pSpatialColumnName, String pWhere, + SDEPoint[] g, String[] pReturnFields, String byClause) { + + try { + // get the layer for querying + + SeShapeFilter[] filters = null; + if (g != null){ + SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName); + SeShape shape = new SeShape(); + shape.setCoordRef(lLayer.getCoordRef()); + + + + shape.generatePolygon(g.length, 1, null, g); + SeShapeFilter filter = new SeShapeFilter(pLayername[0], + pSpatialColumnName, shape, SeFilter.METHOD_AI); + filters = new SeShapeFilter[1]; + filters[0] = filter; + } + + SeQuery spatialQuery = null; + SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere); + spatialQuery = new SeQuery(con); + + SeQueryInfo queryInfo = new SeQueryInfo(); + queryInfo.setColumns(pReturnFields); + + if (byClause != null){ + queryInfo.setByClause(byClause); + } + + queryInfo.setConstruct(sqlCons); + spatialQuery.prepareQueryInfo(queryInfo); + + /* + * Set spatial constraints + */ + if (filters != null){ + spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, + filters); + } + spatialQuery.execute(); + + SeRow row; + int lCount; + for (lCount = 0; (row =spatialQuery.fetch()) != null; lCount++) { + // one time execution + if (lCount == 0) { + // analyze cols of result set + SeColumnDefinition[] lCols = row.getColumns(); + + } + short lNumCols = row.getNumColumns(); + + for (int i = 0; i < lNumCols; i++) { + System.out.println(row.getObject(i)); + } + + } + spatialQuery.close(); + + } catch (Exception e){ + e.printStackTrace(); + } + + } + + + /** + * @param args + */ + public static void main(String[] args) { + + try { + String[] layerNames = new String[] { "median.meshpoint", "median.mesh" }; + String spatialColumnName = "SHAPE"; + String where = "median.meshpoint.meshid = median.mesh.meshid"; + String[] returnFields = new String[] { "sourceid" }; + String byClause = "group by sourceid"; + SDEPoint[] g = new SDEPoint[5]; + g[0] = new SDEPoint(52, 8); + g[1] = new SDEPoint(52, 9); + g[2] = new SDEPoint(53, 9); + g[3] = new SDEPoint(53, 8); + g[4] = new SDEPoint(52, 8); + + String server = ""; + String port = ""; + String database = ""; + String username = ""; + String credentials = ""; + SeConnection con = new SeConnection(server, port, + database, username, + credentials); + new GroupBySample().executeQuery(con, layerNames, spatialColumnName, + where, g, returnFields, byClause); + + + } catch (SeException e) { + e.printStackTrace(); + } + } +} diff -r 5b536542ef56 -r 0ee3c0ed40e4 geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/ToCharSample.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/ToCharSample.java Tue Jan 26 13:55:55 2010 +0000 @@ -0,0 +1,132 @@ +package de.intevation.gnv.geobackend.base.query; + +import com.esri.sde.sdk.client.SDEPoint; +import com.esri.sde.sdk.client.SeColumnDefinition; +import com.esri.sde.sdk.client.SeConnection; +import com.esri.sde.sdk.client.SeException; +import com.esri.sde.sdk.client.SeFilter; +import com.esri.sde.sdk.client.SeLayer; +import com.esri.sde.sdk.client.SeQuery; +import com.esri.sde.sdk.client.SeQueryInfo; +import com.esri.sde.sdk.client.SeRow; +import com.esri.sde.sdk.client.SeShape; +import com.esri.sde.sdk.client.SeShapeFilter; +import com.esri.sde.sdk.client.SeSqlConstruct; + +public class ToCharSample { + + + public boolean executeQuery(SeConnection con, String[] pLayername, + String pSpatialColumnName, String pWhere, + SDEPoint[] g, String[] pReturnFields, String byClause) { + + try { + // get the layer for querying + + SeShapeFilter[] filters = null; + if (g != null){ + SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName); + SeShape shape = new SeShape(); + shape.setCoordRef(lLayer.getCoordRef()); + + + + shape.generatePolygon(g.length, 1, null, g); + SeShapeFilter filter = new SeShapeFilter(pLayername[0], + pSpatialColumnName, shape, SeFilter.METHOD_AI); + filters = new SeShapeFilter[1]; + filters[0] = filter; + } + + SeQuery spatialQuery = null; + SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere); + spatialQuery = new SeQuery(con); + + SeQueryInfo queryInfo = new SeQueryInfo(); + queryInfo.setColumns(pReturnFields); + + if (byClause != null){ + queryInfo.setByClause(byClause); + } + + queryInfo.setConstruct(sqlCons); + spatialQuery.prepareQueryInfo(queryInfo); + + /* + * Set spatial constraints + */ + if (filters != null){ + spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, + filters); + } + spatialQuery.execute(); + + SeRow row; + int lCount; + for (lCount = 0; (row =spatialQuery.fetch()) != null; lCount++) { + // one time execution + if (lCount == 0) { + // analyze cols of result set + SeColumnDefinition[] lCols = row.getColumns(); + + } + short lNumCols = row.getNumColumns(); + + for (int i = 0; i < lNumCols; i++) { + System.out.println(row.getObject(i)); + } + + } + spatialQuery.close(); + return true; + } catch (Exception e){ + e.printStackTrace(); + return false; + } + + } + + + /** + * @param args + */ + public static void main(String[] args) { + + try { + + String[] layerNames = new String[] { "MEDIAN.TRACK" }; + String spatialColumnName = "SHAPE"; + String where = "MEDIAN.TRACK.CRUISEID = 47"; + String[] returnFields = new String[] { "MEDIAN.TRACK.TRACKID KEY", + "to_char(STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| NAME VALUE", + "NAME" }; + String byClause = "ORDER BY STARTDATE, ENDDATE, NAME"; + SDEPoint[] g = new SDEPoint[5]; + g[0] = new SDEPoint(52, 8); + g[1] = new SDEPoint(52, 9); + g[2] = new SDEPoint(53, 9); + g[3] = new SDEPoint(53, 8); + g[4] = new SDEPoint(52, 8); + + String server = ""; + String port = ""; + String database = ""; + String username = ""; + String credentials = ""; + + SeConnection con = new SeConnection(server, port, + database, username, + credentials); + boolean success = new ToCharSample().executeQuery(con, layerNames, spatialColumnName, + where, g, returnFields, byClause); + + if (success){ + System.out.println("Terminated successful."); + }else{ + System.out.println("Terminated with an Exception."); + } + } catch (SeException e) { + e.printStackTrace(); + } + } +}