You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Andrus Adamchik <an...@objectstyle.org> on 2013/07/03 08:32:46 UTC
Re: PostgreSQL Stored Procedure only returning one row
My exposure to PostgreSQL is very limited these days. I was recently researching an unrelated PostgreSQL stored proc issue [1], and all I can say is that functions/stored procedures fully work with Cayenne, but mapping them can be quirky and may require some experimentation. Unfortunately, this is not a very specific answer :-/
Andrus
[1] http://stackoverflow.com/questions/16921942/porting-apache-cayenne-from-oracle-to-postgresql
On Jun 28, 2013, at 1:05 AM, grantdmckenzie <gr...@gmail.com> wrote:
> 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.
>