cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From grantdmckenzie <grantdmcken...@gmail.com>
Subject PostgreSQL Stored Procedure only returning one row
Date Thu, 27 Jun 2013 22:05:20 GMT
Hello all,

I used Modeler (3.0.2) to "Reegineer Database Schema" from a Postgres 9.1
database. This database simply has one table, one stored procedure (function
in postgres language) and one type (return set type from the stored
procedure).  I was able to successfully map dataobjects to tables and can do
a basic select query on the single table I have.  

When I try to get data via the stored procedure, only 1 row is returned,
when it should be 50. The code snippet is below:
-----
                ObjectContext context = DataContext.createDataContext();
		context.commitChanges();
		
		ProcedureQuery query = new ProcedureQuery("w_nearby");
		query.addParameter("$1", -119.82466289999999);
		query.addParameter("$2", 34.4413761);
		
		QueryResponse response = context.performGenericQuery(query);
-----

Logger spits out the following:
-----
Jun 27, 2013 2:59:04 PM org.apache.cayenne.access.QueryLogger logQueryStart
INFO: --- will run 1 query.
Jun 27, 2013 2:59:04 PM org.apache.cayenne.access.QueryLogger logConnect
INFO: Opening connection: jdbc:postgresql://<serverdetails>:5432/<database>
	Login: logindetails
	Password: *******
Jun 27, 2013 2:59:04 PM org.apache.cayenne.access.QueryLogger
logConnectSuccess
INFO: +++ Connecting: SUCCESS.
Jun 27, 2013 2:59:04 PM org.apache.cayenne.access.QueryLogger
logBeginTransaction
INFO: --- transaction started.
Jun 27, 2013 2:59:04 PM org.apache.cayenne.access.QueryLogger logQuery
INFO: {? = call public.w_nearby(?, ?, ?, ?, ?, ?)} [bind:
1:-119.82466289999999, 2:34.4413761, 3:'[OUT]', 4:'[OUT]', 5:'[OUT]',
6:'[OUT]', 7:'[OUT]']
Jun 27, 2013 2:59:04 PM org.apache.cayenne.access.QueryLogger logSelectCount
INFO: === returned 1 row. - took 2 ms.
-----

My Stored Procedure (Function) looks like this (there are PostGIS calls in
there, but it shouldn't make a difference):
-----
CREATE OR REPLACE FUNCTION w_nearby(double precision, double precision)   
RETURNS SETOF w_loc AS  
$BODY$  
DECLARE rec record;  
d_lng ALIAS FOR $1;
d_lat ALIAS FOR $2;

BEGIN  
 FOR rec IN (select w_id, name, ST_Y(ST_GeomFromText(ST_AsText(geog))),
ST_X(ST_GeomFromText(ST_AsText(geog))), st_distance(geog,
st_geographyfromtext('POINT('||d_lng||' '||d_lat||')')) as dist from poibase
where name is not null and ST_DWithin(geog,
st_geographyfromtext('POINT('||d_lng||' '||d_lat||')'), 1000) order by dist
limit 50) LOOP  
  RETURN NEXT rec;  
 END LOOP;  
END;  
$BODY$ 
	LANGUAGE plpgsql;
-----

Any help would be greatly appreciated.  Are there any known issues with
postgresql functions?  Thanks.



--
View this message in context: http://cayenne.195.n3.nabble.com/PostgreSQL-Stored-Procedure-only-returning-one-row-tp4025419.html
Sent from the Cayenne - User mailing list archive at Nabble.com.

Mime
View raw message