| ArcSDEDAtaStore test case for a master-child joining
This test will create an sde layer (table + spatial table) as master and a
business table as child:
-----------------------------------------------
| GT_SDE_TEST_MASTER |
-----------------------------------------------
| ID(int) | NAME (string) | SHAPE (Point) |
-----------------------------------------------
| 1 | name1 | POINT(1, 1) |
-----------------------------------------------
| 2 | name2 | POINT(2, 2) |
-----------------------------------------------
| 3 | name3 | POINT(3, 3) |
-----------------------------------------------
---------------------------------------------------------------------
| GT_SDE_TEST_CHILD |
---------------------------------------------------------------------
| ID(int) | MASTER_ID | NAME (string) | DESCRIPTION(string |
---------------------------------------------------------------------
| 1 | 1 | child1 | description1 |
---------------------------------------------------------------------
| 2 | 2 | child2 | description2 |
---------------------------------------------------------------------
| 3 | 2 | child3 | description3 |
---------------------------------------------------------------------
| 4 | 3 | child4 | description4 |
---------------------------------------------------------------------
| 5 | 3 | child5 | description5 |
---------------------------------------------------------------------
| 6 | 3 | child6 | description6 |
---------------------------------------------------------------------
</re>
The following are rules that may help you in correctly specifying an SQL
query that will work with the ArcSDE Java API. This rules was collected
empirically based on some of the tests of this test suite. Be aware that
ArcSDE Java API only supports "queries" of the following form:
SELECT <list of qualified column names>
FROM <list of qualified table names>
WHERE <any where clause supported by the RDBMS>
[ORDER BY <qualified column names>]
Rules to create SQL QUERIES:
-
Use full qualified table names. Queries that usually would work against the
underlying RDBMS will not work through the ArcSDE Java API if you do not fully
qualify table names.
-
Do not use table aliases, or SHAPE field is fetched as int instead of as geometry.
-
Specifying a GROUP BY clause seems incompatible with using the SHAPE field. If
you specify a GROUP BY clause, ArcSDE will return the plain SHAPE field (int) instead
of a geometry.
-
And the <strong>most important</strong> one: <strong>SET THE SPATIAL COLUMN AS
THE LAST ONE</strong>. This is most likely a bug in the ArcSDE Java API, since if you
do not set the shape field as the last one in the select items list an IndexOutOfBoundsException
is thrown by
SeRow.fetch()
author:
Gabriel Roldan, Axios Engineering
version:
$Id: SDEJavaApiJoinTest.java 27863 2007-11-12 20:34:34Z desruisseaux $
since:
2.3.x |