Thread: Stored procedures

Here is a full example of a java program showing the data from a set returning
function:

-------------------------
--IN YOUR DATABASE
CREATE TABLE people (name TEXT);
INSERT INTO people VALUES ('john');
INSERT INTO people VALUES ('peter');
INSERT INTO people VALUES ('joe');

CREATE FUNCTION getPeople() RETURNS SETOF people AS '
DECLARE  rec RECORD;
BEGIN  FOR rec IN     SELECT name FROM people  LOOP     RETURN NEXT rec;  END LOOP;     RETURN;
END;' LANGUAGE 'plpgsql';

-------------------
--ListPeople.java
import java.sql.*;
public class ListPeople {  public static void main(String[] args) {        try {
Class.forName("org.postgresql.Driver");       Connection  
con=DriverManager.getConnection("jdbc:postgresql:franco?user=admin");        Statement stmt=con.createStatement();
 ResultSet rs=stmt.executeQuery("SELECT * FROM getPeople()");        while (rs.next()) {
System.out.println(rs.getString("name"));       }     }     catch (Exception e) {        System.out.println("Exception:
"+e.getMessage());    }  } 
}

On Friday 28 March 2003 19:31, Zodiac wrote:
> Hello!
> Can anybody tell me one thing.
> How can i call stored procedures in my java-programm?
>
> Thanks for any help.

Thank you for help.
Just one more question. Have i direct access to stored procedure?
For example, i have procedure which returns Integer and i wanna to have
ability to write such code " int var = ANY_CALL". Where ANY_CALL is a my
procedure call.
I meant must i do "executeQuery" only and after then parse Statement
variable?

Thank you.

As far as I know, you always work with a ResultSet.
If you know your stored procedures will always return an Integer and you don't
wanna deal with the executeQuery and stuff every time, you could create a
class with methods explicitly for accesing your stored procedures, for
example:

assuming you have a pg function returning an INT, called countPeople(), you
could do

public class MyStoredProcs {  private static int executeAnyProc(Connection conn, String procName) throws
SQLException{     Statement stmt=conn.createStatement();     ResultSet rs=stmt.executeQuery("SELECT * FROM
"+procName+"()");    rs.next();     return rs.getInt(1);           } 
  public static int countPeople() throws SQLException{     return executeAnyProc("countPeople");  }
};

You could add methods to access every stored procedure in your database (even
returning other data types), and you would use it like this in your code:

...  int count=MyStoredProcs.countPeople();  // do something with the value  if (count>100) {
...


hope this is what you were looking for.

On Saturday 29 March 2003 17:35, Zodiac wrote:
> Thank you for help.
> Just one more question. Have i direct access to stored procedure?
> For example, i have procedure which returns Integer and i wanna to have
> ability to write such code " int var = ANY_CALL". Where ANY_CALL is a my
> procedure call.
> I meant must i do "executeQuery" only and after then parse Statement
> variable?
>
> Thank you.