Skip site navigation (1) Skip section navigation (2)

Re: Urgent. Help needed

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 (view raw or flat)
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
>

In response to

pgsql-jdbc by date

Next:From: Justin BertramDate: 2009-12-22 19:34:19
Subject: Possible oversight in org.postgresql.xa.PGXAConnection.commitPrepared(Xid xid)
Previous:From: MILTOS MILTIADOUSDate: 2009-12-22 09:07:47
Subject: Re: Urgent. Help needed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group