Re: Urgent. Help needed - Mailing list pgsql-jdbc

From MILTOS MILTIADOUS
Subject Re: Urgent. Help needed
Date
Msg-id 9548ecab-b2b5-422e-86a0-830b24e74a9d@k17g2000yqh.googlegroups.com
Whole thread Raw
Responses Re: Urgent. Help needed
Re: Urgent. Help needed
List pgsql-jdbc
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.

pgsql-jdbc by date:

Previous
From:
Date:
Subject: Re: OutOfMemory hibernate scroll with 2M records | Postgresql 8.4 DB
Next
From: Dave Cramer
Date:
Subject: Re: Urgent. Help needed