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.
