Andrus Adamchik wrote:
> Recently I discovered and fixed some problems in handling stored
> procedures by PostgresAdapter -
> http://objectstyle.org/jira/secure/ViewIssue.jspa?key=CAY-299
>
> However your issue doesn't seem related. The error is coming from
> PostgreSQL side, not Cayenne (PostgreSQL has an annoying habit of
> calling all database objects "relations" adding to confusion).
>
> A random suggestion - can you try removing schema part from procedure
> definition. "public" schema should resolve implicitly.
I worked arround the problem so everything (kind of) works.
This is what I did...
My java code with some fairly ugly patchwork:
----------------------
Person person;
ProcedureQuery query= new ProcedureQuery("anketirani()");
HashMap result;
Integer id_person = new Integer(-1);
int safety_count= 50; // if it doesn't work 50 times, it
probably won't work the 51st, either
do {
result= (HashMap) kontekst.performQuery(query).get(0);
if (result != null)
id_person = (Integer)(result).get("id");
} while (id_person == null && safety_count-- > 0);
if (result != null) {
person = (Person) DataObjectUtils.objectForPK(kontekst,
Person.class, id_person);
}
-----------------------
First of all, notice the do-while loop: during concurrent runs, it
sometimes (once in several attempts) happens that rezultat.get("sifra")
returns null even though most of the time it works! Is this the
doesn't-return-first-row-bug you were referring to, Andrus?
Also,notice that I use "anketirani()" instead of "anketirani" as the
procedure name. In the postgre logs I see a "SELECT * FROM anketirani()
AS result" query which now retrieves the row I need becouse of the added
braces. I made the same modifications in the map file:
----------------------
[...] <procedure name="anketirani()" returningValue="true">
<procedure-parameter name="id" type="INTEGER" direction="out"/>
</procedure> [...]
----------------------
My stored procedure:
-----------------------
CREATE OR REPLACE FUNCTION anketirani()
RETURNS int4 AS
$BODY$
DECLARE
id integer := -1;
BEGIN
SELECT INTO id [...] LIMIT 1 FOR UPDATE;
UPDATE person SET poll_time = now() WHERE id_person = id;
RETURN id;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
----------------------
It's all one big workarround: I hate having to use the do-while loop
like that instead of really solving the problem, but it works, and more
importantly, works very reliably from what I was able to test here.
Any suggestions are very welcome, of course. Oh, right: specifying the
"public" schema or specifying no schema at all makes no difference at all.
Tomislav
This archive was generated by hypermail 2.0.0 : Mon Apr 11 2005 - 06:57:59 EDT