Thread: How do I capture the message from user defined function
Hello, This is my first time to write a program to use JDBC. I'm trying to get a message which has been raised within my user-defined function. The message is created by using "raise notice". This message is not error so I'm using "notice" level. If anyone could advise me, I would appreciate very much. Regards, ---------------------------------- Etsuko Shimabukuro etsukos@meta-bit.com tel: +81-3-3664-4160 fax: +81-3-3664-4165 ----------------------------------
On Mon, 3 Feb 2003, etsuko shimabukuro wrote: > > I'm trying to get a message which has been raised within my user-defined > function. > The message is created by using "raise notice". This message is not error > so I'm using "notice" level. > Try using the getWarnings() method of the Connection object. Kris Jurka
Dear Kris, Thank you for your email. I tried this getWarnings(), but it didn't capture the "notice" level of the messages. Regards, Etsuko -----Original Message----- From: Kris Jurka [mailto:books@ejurka.com] Sent: Monday, February 03, 2003 15:59 To: etsuko shimabukuro Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] How do I capture the message from user defined function On Mon, 3 Feb 2003, etsuko shimabukuro wrote: > > I'm trying to get a message which has been raised within my user-defined > function. > The message is created by using "raise notice". This message is not error > so I'm using "notice" level. > Try using the getWarnings() method of the Connection object. Kris Jurka
etsuko shimabukuro wrote:
>
> Dear Kris,
>
> Thank you for your email.
> I tried this getWarnings(), but it didn't capture the "notice" level of the
> messages.
>
> Regards,
> Etsuko
>
The following code works for me on both 7.2.3 and 7.3.1 servers with the
latest jdbc driver. If you are running on the 7.3 series you should
also check the value of client_min_messages in postgresql.conf is set to
an appropriate value for you to receive notice messages.
Kris Jurka
import java.sql.*;
public class Raise {
public static void main(String args[]) throws Exception {
Class.forName("org.postgresql.Driver");
Connection conn =
DriverManager.getConnection("jdbc:postgresql://localhost:5432/jurka","jurka","");
Statement stmt = conn.createStatement();
stmt.executeUpdate("CREATE OR REPLACE function myraise() returns int
as 'BEGIN RAISE NOTICE ''Hello''; RETURN 0; END' LANGUAGE 'PLPGSQL'");
ResultSet rs = stmt.executeQuery("SELECT myraise()");
SQLWarning warning = conn.getWarnings();
System.out.println(warning);
}
}
Hi Kris,
Thank you for your advise.
Yes, it worked !
Sorry, I didn't println the warning message properly.
Appreciate your help very much.
Regards,
Etsuko
-----Original Message-----
From: jurka [mailto:jurka]On Behalf Of Kris Jurka
Sent: Monday, February 03, 2003 16:30
To: etsuko shimabukuro
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] How do I capture the message from user defined function
etsuko shimabukuro wrote:
>
> Dear Kris,
>
> Thank you for your email.
> I tried this getWarnings(), but it didn't capture the "notice" level of
the
> messages.
>
> Regards,
> Etsuko
>
The following code works for me on both 7.2.3 and 7.3.1 servers with the
latest jdbc driver. If you are running on the 7.3 series you should
also check the value of client_min_messages in postgresql.conf is set to
an appropriate value for you to receive notice messages.
Kris Jurka
import java.sql.*;
public class Raise {
public static void main(String args[]) throws Exception {
Class.forName("org.postgresql.Driver");
Connection conn =
DriverManager.getConnection("jdbc:postgresql://localhost:5432/jurka","jurka"
,"");
Statement stmt = conn.createStatement();
stmt.executeUpdate("CREATE OR REPLACE function myraise() returns int
as 'BEGIN RAISE NOTICE ''Hello''; RETURN 0; END' LANGUAGE 'PLPGSQL'");
ResultSet rs = stmt.executeQuery("SELECT myraise()");
SQLWarning warning = conn.getWarnings();
System.out.println(warning);
}
}