Обсуждение: Re: Urgent. Help needed
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.
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 >
MILTOS MILTIADOUS wrote: > [...] I receive back an HTTP POST status 200 (OK) but without > content (empty entity result). I'm guessing this is a strange way of saying "the query returns no rows"? Can you test with a standalone JDBC app that gets rid of all the intervening layers? > <query setProfile="false" thematic_myLocation="false" >SELECT * FROM > myLocationWrapperFunction(false, '$poi_type$', false, > '$positional_accuracy$', '$longitude$', '$latitude$', > '$observer_profileid$');</query> Why are you quoting integer parameter values? Why are you doing string substitution yourself, rather than allowing PreparedStatement to handle parameters? That aside, you might want to turn on query logging and compare the exact queries for the case that works vs. the case that doesn't. If you are not using PreparedStatement I can't really see why there would be any differences - you're just executing a literal string. -O