From: | Dave Cramer <pg(at)fastcrypt(dot)com> |
---|---|
To: | MILTOS MILTIADOUS <mmiltiadous0(at)gmail(dot)com> |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Urgent. Help needed |
Date: | 2009-12-22 18:56:29 |
Message-ID: | 491f66a50912221056o3b028dfia21f6fe6d5d06c7a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Sorry for top posting, but is there an error message ?
Dave
On Tue, Dec 22, 2009 at 4:07 AM, MILTOS MILTIADOUS
<mmiltiadous0(at)gmail(dot)com> wrote:
> Hello everyone.
>
> I am in the final stage of completing my postgraduate thesis LBS &
> mobile GIS application.
> I have a web server application (restlet style), a google android
> client and a postgres/postgis DB. I am using postgres plus ver 8.3.
> The web server and the postgres server are installed on a IBM lenovo
> T500 notebook, 4G RAM, 2,80 GHz intel core duo, on windows XP PRO O/S
>
> For a couple days now, I am trying to call a pl/pgsql stored function
> using jdbc api which returns a setof rowtype (user defined).
> I have to point out that the specific function is running perfectly in
> pg ADMIN III.
>
> I am calling the following function -->
>
> CREATE OR REPLACE FUNCTION myLocationWrapperFunction(IN is_thematic
> boolean, IN poi_type integer, IN requires_table_name boolean, IN
> accuracy integer,
> IN lon double precision, IN lat double precision, IN user_profile_id
> integer) RETURNS SETOF locationBestResults AS $$
>
> DECLARE
> ..
> ..
> ..
> <function body>
> ..
> ..
> ..
> RETURN;
> END;
> $$ LANGUAGE 'plpgsql' VOLATILE STRICT;
>
> According what I search and study till now, I am trying to call the
> function which is found in an xml document on server side using JDBC
> API (jar jdbc3 ver 6xxx) methods Statement or PrepareStatement. The
> function executes normally various insertations on DB tables, calls
> nested functions, creates temporary table, and in localhost (testing
> environment) I receive back an HTTP POST status 200 (OK) but without
> content (empty entity result).
>
> The define rowtype is :
>
> CREATE TYPE locationBestResults AS(
> poi_name varchar,
> address_street varchar,
> address_num integer,
> address_zip integer,
> phone_num varchar,
> image bytea,
> image_title varchar,
> description varchar,
> doc varchar,
> link varchar,
> status varchar
> );
>
> I imagine the problem has to do with the correct syntax of the current
> function. I using the following query to call the function in PG ADMIN
> III:
>
> select * from myLocationWrapperFunction(false, 0, false, 5,
> 33.0415, 34.6723, 71);
>
> In xml document the function is written as
>
> <query setProfile="false" thematic_myLocation="false" >SELECT * FROM
> myLocationWrapperFunction(false, '$poi_type$', false,
> '$positional_accuracy$', '$longitude$', '$latitude$',
> '$observer_profileid$');</query>
>
> I am using StringTemplate to catch and save the input parameters in a
> string template which are send via HTTP POST client request (parameter
> body).
>
>
> Also, I tried a lot of other methods as
>
> <query setProfile="false" thematic_myLocation="false" >SELECT
> poi_name,address_street,address_num,address_zip,
> phone_num,image,image_title,description,doc,link,status
> FROM myLocationWrapperFunction(false, '$poi_type$', false,
> '$positional_accuracy$', '$longitude$', '$latitude$',
> '$observer_profileid$');</query>
>
> Additionaly,
>
> I tried to cast ouput result columns data types without effect
>
>
> Thank u.
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Bertram | 2009-12-22 19:34:19 | Possible oversight in org.postgresql.xa.PGXAConnection.commitPrepared(Xid xid) |
Previous Message | MILTOS MILTIADOUS | 2009-12-22 09:07:47 | Re: Urgent. Help needed |