[mapserver-users] msOracleSpatialLayerResultGetShape() could not fetch specified record

[pjamason2]

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.