1
package org.postgresql.test.jdbc2;
3
import org.postgresql.test.TestUtil;
4
import java.sql.CallableStatement;
5
import java.sql.Connection;
6
import java.sql.ResultSet;
7
import java.sql.Statement;
10
import junit.framework.TestCase;
13
* RefCursor ResultSet tests.
14
* This test case is basically the same as the ResultSet test case.
16
* @author Nic Ferrier <nferrier@tapsellferrier.co.uk>
18
public class RefCursorTest extends TestCase
20
private Connection con;
22
public RefCursorTest(String name)
27
protected void setUp() throws Exception
29
// this is the same as the ResultSet setup.
30
con = TestUtil.openDB();
31
Statement stmt = con.createStatement();
33
TestUtil.createTable(con, "testrs", "id integer");
35
stmt.executeUpdate("INSERT INTO testrs VALUES (1)");
36
stmt.executeUpdate("INSERT INTO testrs VALUES (2)");
37
stmt.executeUpdate("INSERT INTO testrs VALUES (3)");
38
stmt.executeUpdate("INSERT INTO testrs VALUES (4)");
39
stmt.executeUpdate("INSERT INTO testrs VALUES (6)");
40
stmt.executeUpdate("INSERT INTO testrs VALUES (9)");
43
// Create the functions.
44
stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getRefcursor () RETURNS refcursor AS '"
45
+ "declare v_resset; begin open v_resset for select id from testrs order by id; "
46
+ "return v_resset; end;' LANGUAGE 'plpgsql';");
47
stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getEmptyRefcursor () RETURNS refcursor AS '"
48
+ "declare v_resset; begin open v_resset for select id from testrs where id < 1 order by id; "
49
+ "return v_resset; end;' LANGUAGE 'plpgsql';");
53
protected void tearDown() throws Exception
55
Statement stmt = con.createStatement ();
56
stmt.execute ("drop FUNCTION testspg__getRefcursor ();");
57
stmt.execute ("drop FUNCTION testspg__getEmptyRefcursor ();");
58
TestUtil.dropTable(con, "testrs");
59
TestUtil.closeDB(con);
62
public void testResult() throws Exception
64
CallableStatement call = con.prepareCall("{ ? = call testspg__getRefcursor () }");
65
call.registerOutParameter(1, Types.OTHER);
67
ResultSet rs = (ResultSet) call.getObject(1);
69
assertTrue(rs.next());
70
assertTrue(rs.getInt(1) == 1);
72
assertTrue(rs.next());
73
assertTrue(rs.getInt(1) == 2);
75
assertTrue(rs.next());
76
assertTrue(rs.getInt(1) == 3);
78
assertTrue(rs.next());
79
assertTrue(rs.getInt(1) == 4);
81
assertTrue(rs.next());
82
assertTrue(rs.getInt(1) == 6);
84
assertTrue(rs.next());
85
assertTrue(rs.getInt(1) == 9);
87
assertTrue(!rs.next());
93
public void testEmptyResult() throws Exception
95
CallableStatement call = con.prepareCall("{ ? = call testspg__getRefcursor () }");
96
call.registerOutParameter(1, Types.OTHER);
99
ResultSet rs = (ResultSet) call.getObject(1);
100
assertTrue(!rs.next());