how to get SeRow By the select of order by in SeQueryInfo

yeli · ‎09-10-2013

SeQueryInfo info = new SeQueryInfo();
            info.setColumns(new String[]{"OBJECTID","PICTURE","DEPTNAME3","SHAPE"});

            SeSqlConstruct sqlConstruct = new SeSqlConstruct();
            sqlConstruct.setTables(new String[]{seLayer.getName()});

            sqlConstruct.setWhere(" OBJECTID > 600 ");            info.setConstruct(sqlConstruct);
            info.setByClause(" GROUP BY DEPTNAME3 ");

            SeQuery seQuery = new SeQuery(seCon,new String[]{"OBJECTID","PICTURE","DEPTNAME3","SHAPE"},sqlConstruct);
            SeCoordinateReference cr = seLayer.getCoordRef();
            SeShape inputShape = new SeShape(cr);
            inputShape.generateFromText("POLYGON((195930.27713044552 214575.8701973,195907.22848852084 214706.58554121433,195840.86256912464 214821.5346854996,195739.18408159274 214906.85306688625,195614.45695213618 214952.2500294139,195481.7251133438 214952.2500294139,195356.99798388724 214906.85306688625,195255.31949635534 214821.5346854996,195188.95357695915 214706.58554121433,195165.90493503446 214575.8701973,195188.95357695915 214445.1548533857,195255.31949635534 214330.20570910044,195356.99798388724 214244.88732771378,195481.7251133438 214199.49036518612,195614.45695213618 214199.49036518612,195739.18408159274 214244.88732771378,195840.86256912464 214330.20570910044,195907.22848852084 214445.1548533857,195930.27713044552 214575.8701973))");
            SeFilter filter = new SeShapeFilter(seLayer.getName(), seLayer.getSpatialColumn(), inputShape, SeShapeFilter.METHOD_AI);
            System.out.println(""+System.currentTimeMillis()+" " + new Date(System.currentTimeMillis()).toLocaleString());
            seQuery.setSpatialConstraints(SeQuery.SE_SPATIAL_FIRST, true, new SeFilter[]{filter});

            info.setQueryType(SeQueryInfo.SE_QUERYTYPE_ATTRIBUTE_FIRST);
            SeRow row = seQuery.fetch() ;

when recieve the last line. throws the exception

ArcSDE Error Number        : -51
Error Description          : DATABASE LEVEL ERROR OCCURRED.
Extended Error Description :

com.esri.sde.sdk.client.SeException:
at com.esri.sde.sdk.client.k.v(Unknown Source)
at com.esri.sde.sdk.client.k.d(Unknown Source)
at com.esri.sde.sdk.client.SeQuery.fetch(Unknown Source)
at com.augurit.Test.main(Test.java:55)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)

Process finished with exit code 0

How do I use right ?   please help me. thanks

VinceAngelo · ‎09-11-2013

Please edit your post to use the "code" block ("#") so that your source is legible.

You should also always include these critical details:

What release of the ArcSDE API you're using (version and service pack)

What version of ArcSDE is installed in the database (version and service pack)

What database you're using (Product, Version, patch level, and application word size)

A description of what you're trying to do.

Any relevant details, like a description of the table, as generated by 'sdetable -o describe'

If you convert your Java to SQL (without the spatial constraint), it would look like:

SELECT objectid,picture,deptname3,shape
FROM unnamed_table 
WHERE objectid > 600
GROUP BY deptname3

This is not a valid SQL expression -- You can only SELECT aggregates when a
GROUP BY operator is used, and there is no aggregator on objectid, picture,
or shape.

You should always prototype your queries in SQL before attempting them through
the Java API, and if a database error is raised, you should be looking for database
errors.

Your message title states you want an ORDER BY, but your code uses GROUP BY --
Which did you really mean to use?

- V

yeli · ‎09-11-2013

thank the tip for me

environment : arcsde 10.0 for oracle
                     oracle : 10.0.2
                     arcsde sdk :   jsde.jar(10.1)

thanks, I get the right.
modify       �?     info.setColumns(new String[]{"DEPTNAME3"});
I can execute success now.
code:

SELECT deptname3
FROM COMPONENTS_POINT
GROUP BY deptname3

but I
modify second : info.setColumns(new String[]{"DEPTNAME3","count(DEPTNAME3)"});

SELECT deptname3 ,count(DEPTNAME3)"});
FROM COMPONENTS_POINT
GROUP BY deptname3

now ,i get the error.

ArcSDE Error Number : -51
Error Description : DATABASE LEVEL ERROR OCCURRED.
Extended Error Description :

com.esri.sde.sdk.client.SeException:
at com.esri.sde.sdk.client.k.v(Unknown Source)
at com.esri.sde.sdk.client.k.d(Unknown Source)
at com.esri.sde.sdk.client.SeQuery.fetch(Unknown Source)
at com.augurit.Test2.main(Test2.java:55)

Is it not allowed to use count in the arcsde api ?

can you help me again.

My English is very poor,sorry!

Please edit your post to use the "code" block ("#") so that your source is legible.

You should also always include these critical details:

What release of the ArcSDE API you're using (version and service pack)

What version of ArcSDE is installed in the database (version and service pack)

What database you're using (Product, Version, patch level, and application word size)

A description of what you're trying to do.

Any relevant details, like a description of the table, as generated by 'sdetable -o describe'

If you convert your Java to SQL (without the spatial constraint), it would look like:
SELECT objectid,picture,deptname3,shape
FROM unnamed_table 
WHERE objectid > 600
GROUP BY deptname3
This is not a valid SQL expression -- You can only SELECT aggregates when a
GROUP BY operator is used, and there is no aggregator on objectid, picture,
or shape.

You should always prototype your queries in SQL before attempting them through
the Java API, and if a database error is raised, you should be looking for database
errors.

Your message title states you want an ORDER BY, but your code uses GROUP BY --
Which did you really mean to use?

- V

VinceAngelo · ‎09-11-2013

There's no such product as "Oracle 10.0.2". There's a 10.2.0.x family, but only 10.2.0.3
and higher are supported (for values of "support" that take retirement of 10gR2 into account).
Do not use Oracle 10.2.0.1.0 -- it does not work.

Which ArcSDE 10.0 are you using? SP5 was the ternimal release, but there were post-SP5
patches as well.

There shouldn't be much difference between 10.1 and 10.1sp1 at the API level, but using
the terminal service pack is always best (especially if you're running an API in excess of
the geodatabase).

I can use a count() function in the 'C' API at will. The real question is what database
error is being generated, and if it is generated in a straight SQL query.

- V

PS: Make sure you alias the result of functions -- "count(foo) foo_count"

yeli · ‎09-11-2013

good morning
my oracle is oracle 10.2.0.1. i don't know the version of arcsde
why the version of oracle 10.2.0.1 do not work! can you tell me?

other ,can you post the code by the arcsde c api ?
i think java api have the save function with the c api.
thanks.

There's no such product as "Oracle 10.0.2". There's a 10.2.0.x family, but only 10.2.0.3
and higher are supported (for values of "support" that take retirement of 10gR2 into account).
Do not use Oracle 10.2.0.1.0 -- it does not work.

Which ArcSDE 10.0 are you using? SP5 was the ternimal release, but there were post-SP5
patches as well.

There shouldn't be much difference between 10.1 and 10.1sp1 at the API level, but using
the terminal service pack is always best (especially if you're running an API in excess of
the geodatabase).

I can use a count() function in the 'C' API at will. The real question is what database
error is being generated, and if it is generated in a straight SQL query.

- V

PS: Make sure you alias the result of functions -- "count(foo) foo_count"

VinceAngelo · ‎09-11-2013

Oracle 10.2.0.1.0 is not supported because it doesn't work correctly with Oracle's own
connection libraries. It crashes connections frequently for no reason. It's not impossible
that the lack of a proper error message is due to 10.2.0.1.0 use. The only version of
10gR2 with the name 10.2.0.1.0 which is supported is Oracle 10gR2 Express, which
inexplicably works, despite not having been patched since its release nearly a decade
ago.

There's little point in posting 'C' code that works correctly in a stable environment of
patched software on supported database releases. First, you need to install a supported
database. Then you need to determine what version of ArcSDE is installed, and what it
will take to patch it to the most recent service pack (SP5). Then you can patch your
Java API SDK with 10.1sp1. And then you can test the previous code.

I should also point out that the ArcSDE Java API is deprecated at ArcGIS 10.2.

- V