Thread: Stored Functions

Stored Functions

From

"Yolanda Phillips"

Date:

22 August 2003, 07:47:57

Good day,

I urgently need help!!

I've just upgraded to postgresql-7.3.4. Using pg73jdbc3.jar driver. This is
because I really need to use stored functions.

This is the plpgsql function:

CREATE OR REPLACE FUNCTION LoadGlobals(INT) RETURNS refcursor AS '
DECLARE
   v_global_id ALIAS FOR $1;
   ref refcursor;
BEGIN

    OPEN ref FOR
        SELECT global_values_desc, section_title, menu_url, include_file_url
        FROM GLOBAL_VALUES
        WHERE global_values_id = v_global_id;
    --IF NOT FOUND THEN --If nothing was returned in the last query
      --RAISE EXCEPTION ''Invalid global id.'';
    --ELSE
      RETURN ref;
    --END IF;

END;
' LANGUAGE 'plpgsql';

The JDBC code:

private static final String driver = "org.postgresql.Driver";
  private static final String user = "someuser";
  private static final String pass = "password";
  private static final String dbURL =
"jdbc:postgresql://10.123.0.00/somedb";

 conn.setAutoCommit(false);
 CallableStatement proc = conn.prepareCall("{? = call LoadGlobals (?) }");
 proc.registerOutParameter(1, Types.OTHER);
 proc.setInt(2, sectionId);
 proc.execute();
 results = (ResultSet)proc.getObject(1);
 System.out.println("ResultSet: " + results);

 ...

I get the following error:
  SQL Exception: No class found for refcursor

Any suggestions?



Sincerely,


Yolanda Phillips
Web Applications Developer

Re: Stored Functions

From

"srikanth nagaraju"

Date:

22 August 2003, 07:59:48

I think setting AUTOCOMMIT is not supported in the PostgreSQL 7.3.2 and
above versions.

Here is the Note from the PostgreSQL 7.3.2 Administrators Guide:
Note: As of PostgreSQL 7.3, setting autocommit to false is not
well-supported. This is a new feature and is not yet handled by all
client libraries and applications. Before making it the default setting
in your installation, test carefully.

Set the AUTOCOMMIT (if you are not using the ROLLBACK!!) to true and
try.
Let me know if this doesn't helps.

Cheers,
------------------
Srikanth
ESN: 6-872-8711
Ph: +91-80-5538301 Extn: 2644
-----------------------------------------------------


> -----Original Message-----
> From: Yolanda Phillips [mailto:yphillips@dlkconsulting.co.za]
> Sent: Friday, August 22, 2003 1:13 PM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC]Stored Functions
> Importance: High
>
> Good day,
>
> I urgently need help!!
>
> I've just upgraded to postgresql-7.3.4. Using pg73jdbc3.jar driver.
This
> is
> because I really need to use stored functions.
>
> This is the plpgsql function:
>
> CREATE OR REPLACE FUNCTION LoadGlobals(INT) RETURNS refcursor AS '
> DECLARE
>    v_global_id ALIAS FOR $1;
>    ref refcursor;
> BEGIN
>
>     OPEN ref FOR
>         SELECT global_values_desc, section_title, menu_url,
> include_file_url
>         FROM GLOBAL_VALUES
>         WHERE global_values_id = v_global_id;
>     --IF NOT FOUND THEN --If nothing was returned in the last query
>       --RAISE EXCEPTION ''Invalid global id.'';
>     --ELSE
>       RETURN ref;
>     --END IF;
>
> END;
> ' LANGUAGE 'plpgsql';
>
> The JDBC code:
>
> private static final String driver = "org.postgresql.Driver";
>   private static final String user = "someuser";
>   private static final String pass = "password";
>   private static final String dbURL =
> "jdbc:postgresql://10.123.0.00/somedb";
>
>  conn.setAutoCommit(false);
>  CallableStatement proc = conn.prepareCall("{? = call LoadGlobals (?)
}");
>  proc.registerOutParameter(1, Types.OTHER);
>  proc.setInt(2, sectionId);
>  proc.execute();
>  results = (ResultSet)proc.getObject(1);
>  System.out.println("ResultSet: " + results);
>
>  ...
>
> I get the following error:
>   SQL Exception: No class found for refcursor
>
> Any suggestions?
>
>
>
> Sincerely,
>
>
> Yolanda Phillips
> Web Applications Developer
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

**************************Disclaimer************************************

Information contained in this E-MAIL being proprietary to Wipro Limited is
'privileged' and 'confidential' and intended for use only by the individual
 or entity to which it is addressed. You are notified that any use, copying
or dissemination of the information contained in the E-MAIL in any manner
whatsoever is strictly prohibited.

***************************************************************************

Re: Stored Functions

From

Oliver Jowett

Date:

22 August 2003, 08:25:49

On Fri, Aug 22, 2003 at 01:29:02PM +0530, srikanth nagaraju wrote:
>
> I think setting AUTOCOMMIT is not supported in the PostgreSQL 7.3.2 and
> above versions.
>
> Here is the Note from the PostgreSQL 7.3.2 Administrators Guide:
> Note: As of PostgreSQL 7.3, setting autocommit to false is not
> well-supported. This is a new feature and is not yet handled by all
> client libraries and applications. Before making it the default setting
> in your installation, test carefully.

This refers to setting 'autocommit = false' in postgresql.conf, not the JDBC
setAutoCommit() method (which works fine).

-O

Re: Stored Functions

From

Barry Lind

Date:

22 August 2003, 17:30:37

Yolanda,

This functionality is only in the current development driver, it was
added since the 7.3 version of the driver.  The current development
driver should run fine against a 7.3 database however.

--Barry

Yolanda Phillips wrote:
> Good day,
>
> I urgently need help!!
>
> I've just upgraded to postgresql-7.3.4. Using pg73jdbc3.jar driver. This is
> because I really need to use stored functions.
>
> This is the plpgsql function:
>
> CREATE OR REPLACE FUNCTION LoadGlobals(INT) RETURNS refcursor AS '
> DECLARE
>    v_global_id ALIAS FOR $1;
>    ref refcursor;
> BEGIN
>
>     OPEN ref FOR
>         SELECT global_values_desc, section_title, menu_url, include_file_url
>         FROM GLOBAL_VALUES
>         WHERE global_values_id = v_global_id;
>     --IF NOT FOUND THEN --If nothing was returned in the last query
>       --RAISE EXCEPTION ''Invalid global id.'';
>     --ELSE
>       RETURN ref;
>     --END IF;
>
> END;
> ' LANGUAGE 'plpgsql';
>
> The JDBC code:
>
> private static final String driver = "org.postgresql.Driver";
>   private static final String user = "someuser";
>   private static final String pass = "password";
>   private static final String dbURL =
> "jdbc:postgresql://10.123.0.00/somedb";
>
>  conn.setAutoCommit(false);
>  CallableStatement proc = conn.prepareCall("{? = call LoadGlobals (?) }");
>  proc.registerOutParameter(1, Types.OTHER);
>  proc.setInt(2, sectionId);
>  proc.execute();
>  results = (ResultSet)proc.getObject(1);
>  System.out.println("ResultSet: " + results);
>
>  ...
>
> I get the following error:
>   SQL Exception: No class found for refcursor
>
> Any suggestions?
>
>
>
> Sincerely,
>
>
> Yolanda Phillips
> Web Applications Developer
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>