Calling Stored Procedures

Greenplum supports two classes of stored procedures:

• To execute parameterized stored procedure calls, use CallableStatement objects.
• To execute stored procedures as SQL statements using the EXEC syntax, use Statement objects.

Using Callable Statement Objects

You can use the generic execute method of the CallableStatement class to execute any stored procedure as a parameterized query.

To return the stored procedure's results, call getResultSet. To return a count of updated rows, call getUpdateCount.

The following example shows how to execute the SelectEntries stored procedure:

CallableStatement cstmt = conn.prepareCall("SelectEntries");
cstmt.setString("ObjectName", "Account");
boolean ret = cstmt.execute();   
if (!ret) {
  int count=cstmt.getUpdateCount();
  if (count!=-1) {
    System.out.println("Affected rows: "+count);
  }
}
else {
  ResultSet rs=cstmt.getResultSet();
  while(rs.next()){
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnLabel(i) +"="+rs.getString(i));
    }
  }
}

Using Statement Objects

You can use the execute method of the Statement class to execute any stored procedure as an SQL statement.

To return the stored procedure's results, call getResultSet. To return a count of updated rows, call getUpdateCount.

The following example shows how to execute the SelectEntries stored procedure:

Statement stmt = conn.createStatement();
boolean ret = stmt.execute("EXEC SelectEntries ObjectName = 'Account'");

if (!ret) {
  int count=stmt.getUpdateCount();
  if (count!=-1) {
    System.out.println("Affected rows: "+count);
  }
}
else {
  ResultSet rs=stmt.getResultSet();
  while(rs.next()) {
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnLabel(i) +"="+rs.getString(i));
    }
  }
}