Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Unanswered: Urgent. Help needed.

    Hello everyone.

    I am in the final stage in 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,s tatus
    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.

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    So the function returns the desired results when called in PgAdmin?

  3. #3
    Join Date
    Dec 2009
    Posts
    2

    Ok

    Thank u very much for you interest but I find the solution.
    Cheers Miltos.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •