Re: Urgent. Help needed - Mailing list 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@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>