Hi, After migrating to mapserver 5.6.5, one of our two ms oracle spatial queries no longer works. Here's the query that works:
Using this Sql to retrieve the data: SELECT ID, ID, SITE_ID, SITE_CODE, CITY, ARRAY_CODE, NAME, SHAPE FROM (select s.id as id,s.site_id as site_id,upper(s.site_code) as site_code,s.shape,nvl(s.city,'n/a') as city,sa.array_code as array_code,nvl(s.site_name,'n/a') as name from site s, site_affiliation sa where s.site_id = sa.site_id and sa.end_date is null and s.site_type_code = 'CONGPS') [Fri Oct 1 18:19:50 2010].881984 msOracleSpatialLayerWhichShapes. Bind values: srid:8307 minx:-11 4.139531 miny:34.485321 maxx:-112.696645 maxy:35.928207 [Fri Oct 1 18:19:50 2010].882013 msOracleSpatialLayerWhichShapes getting ordinate definition. [Fri Oct 1 18:19:50 2010].883487 msOracleSpatialLayerWhichShapes converting to OCIColl. [Fri Oct 1 18:19:50 2010].883626 msOracleSpatialLayerWhichShapes bind by name and object. [Fri Oct 1 18:19:50 2010].883632 msOracleSpatialLayerWhichShapes name and object now bound. [Fri Oct 1 18:19:51 2010].26025 msOracleSpatialLayerNextShape on layer 0x13665770, row_num: 0 <snip> [Fri Oct 1 18:19:51 2010].732440 msOracleSpatialLayerNextShape on layer 0x13665770, row_num: 8245 [Fri Oct 1 18:19:51 2010].732662 msOracleSpatialLayerNextShape on layer 0x13665770, Fetched 0 more rows (8250 total) [Fri Oct 1 18:19:51 2010].733278 msOracleSpatialLayerResultGetShape was called. Using the record = 5185 of 1. (shape: 0 should equal pkey: 6274) [Fri Oct 1 18:19:51 2010].733287 msOracleSpatialLayerResultGetShape: Fetching result from DB start: 8192 end:8191 record: 5185 [Fri Oct 1 18:19:51 2010].794501 mapserv request processing time (loadmap not incl.): 1.034s This query does not: [Fri Oct 1 18:19:38 2010].152059 msOracleSpatialLayerWhichShapes. Using this Sql to retrieve the data: SELECT ID, ID, SITE_ID, SITE_CODE, CITY, ARRAY_CODE, NAME, INTERVAL, LAST, SINCE, SHAPE FROM (select s.id as id,s.site_id as site_id,upper(s.site_code) as site_code,s.shape,nvl(s.city,'n/a') as city,sa.array_code as array_code,nvl(s.site_name,'n/a') as name,src.sample_interval_in_seconds as interval,to_char(round(((SYSDATE - last_updated) * 24 * 60),0),'999999999') as last, src.start_date as since from site s, realtime_gps_data_src src, site_affiliation sa where src.end_date is null and src. site_id = s.site_id and s.site_id = sa.site_id) [Fri Oct 1 18:19:38 2010].152065 msOracleSpatialLayerWhichShapes. Bind values: srid:8307 minx:-114.864628 miny:33.584442 maxx:-113.421743 maxy:35.027328 [Fri Oct 1 18:19:38 2010].152107 msOracleSpatialLayerWhichShapes getting ordinate definition. [Fri Oct 1 18:19:38 2010].153257 msOracleSpatialLayerWhichShapes converting to OCIColl. [Fri Oct 1 18:19:38 2010].153386 msOracleSpatialLayerWhichShapes bind by name and object. [Fri Oct 1 18:19:38 2010].153392 msOracleSpatialLayerWhichShapes name and object now bound. [Fri Oct 1 18:19:38 2010].222071 msOracleSpatialLayerNextShape on layer 0x18b9bdd0, row_num: 0 <snip> [Fri Oct 1 18:19:38 2010].226113 msOracleSpatialLayerNextShape on layer 0x18b9bdd0, row_num: 167 [Fri Oct 1 18:19:38 2010].226464 msOracleSpatialLayerNextShape on layer 0x18b9bdd0, Fetched 0 more rows (167 total) [Fri Oct 1 18:19:38 2010].226984 msOracleSpatialLayerResultGetShape was called. Using the record = 92 of 1. (shape: 415777808 should equal pkey: 2507) [Fri Oct 1 18:19:38 2010].227002 msOracleSpatialLayerResultGetShape: Fetching result from DB start: 0 end:-1 record: 92 [Fri Oct 1 18:19:38 2010].883468 msOracleSpatialLayerResultGetShape: OracleSpatial error. ORA-03113 : end-of-file on communication channel Process ID: 19745 Session ID: 59 Serial number: 36067 [Fri Oct 1 18:19:38 2010].883478 msOracleSpatialLayerResultGetShape(): OracleSpatial error. msOracleSpatialLayerResultGetShape could not fetch specified record. In mapserver 5.2.0, the second query above worked: [Mon Oct 4 16:58:41 2010].791000 msOracleSpatialLayerFreeItemInfo was called. [Mon Oct 4 16:58:41 2010].791007 msOracleSpatialLayerGetItems was called. [Mon Oct 4 16:58:41 2010].792022 msOracleSpatialLayerInitItemInfo was called. [Mon Oct 4 16:58:42 2010].87922 msOracleSpatialLayerGetShape was called. Using the record = 62392. [Mon Oct 4 16:58:42 2010].87940 msOracleSpatialLayerGetShape. Sql: SELECT ID, SITE_ID, SITE_CODE, CITY, ARRAY_CODE, NAME, INTERVAL, LAST, SINCE, SHAPE FROM (select s.id as id,s.site_id as site_id,upper(s.site_code) as site_code,s.shape,nvl(s.city,'n/a') as city,sa.array_code as array_code,nvl(s.site_name,'n/a') as name,src.sample_interval_in_seconds as interval,to_char(round(((SYSDATE - last_upda ted) * 24 * 60),0),'999999999') as last, src.start_date as since from site s, realtime_gps_data_src src, site_affiliation sa where src.end_date is null and src.site_id = s.site_id and s.site_id = sa.site_id) WHERE ID = 62392 I've seen the RFC-52 note regarding msLayerResultsGetShape() in the mapserver 5.4 to 5.6 migration guide at http://trac.osgeo.org/mapserver/browser/branches/branch-5-6/mapserver/MIGRATION_GUIDE.TXT http://trac.osgeo.org/mapserver/browser/branches/branch-5-6/mapserver/MIGRATION_GUIDE.TXT but we're calling the mapserv cgi, which is transparent to this change according to this note. We're considering migrating back to ms 5.4, but any help would be greatly appreciated, thanks. -- View this message in context: http://osgeo-org.1803224.n2.nabble.com/msOracleSpatialLayerResultGetShape-could-not-fetch-specified-record-tp5601515p5601515.html Sent from the Mapserver - User mailing list archive at Nabble.com. _______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users
