1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
/*-------------------------------------------------------------------------
*
* Copyright (c) 2004-2008, PostgreSQL Global Development Group
*
* IDENTIFICATION
* $PostgreSQL: pgjdbc/org/postgresql/test/jdbc2/RefCursorTest.java,v 1.8 2008/01/08 06:56:31 jurka Exp $
*
*-------------------------------------------------------------------------
*/
package org.postgresql.test.jdbc2;
import org.postgresql.test.TestUtil;
import java.sql.*;
import junit.framework.TestCase;
/*
* RefCursor ResultSet tests.
* This test case is basically the same as the ResultSet test case.
*
* @author Nic Ferrier <nferrier@tapsellferrier.co.uk>
*/
public class RefCursorTest extends TestCase
{
private Connection con;
public RefCursorTest(String name)
{
super(name);
}
protected void setUp() throws Exception
{
// this is the same as the ResultSet setup.
con = TestUtil.openDB();
Statement stmt = con.createStatement();
TestUtil.createTable(con, "testrs", "id integer primary key");
stmt.executeUpdate("INSERT INTO testrs VALUES (1)");
stmt.executeUpdate("INSERT INTO testrs VALUES (2)");
stmt.executeUpdate("INSERT INTO testrs VALUES (3)");
stmt.executeUpdate("INSERT INTO testrs VALUES (4)");
stmt.executeUpdate("INSERT INTO testrs VALUES (6)");
stmt.executeUpdate("INSERT INTO testrs VALUES (9)");
// Create the functions.
stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getRefcursor () RETURNS refcursor AS '"
+ "declare v_resset refcursor; begin open v_resset for select id from testrs order by id; "
+ "return v_resset; end;' LANGUAGE 'plpgsql';");
stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getEmptyRefcursor () RETURNS refcursor AS '"
+ "declare v_resset refcursor; begin open v_resset for select id from testrs where id < 1 order by id; "
+ "return v_resset; end;' LANGUAGE 'plpgsql';");
stmt.close();
con.setAutoCommit(false);
}
protected void tearDown() throws Exception
{
con.setAutoCommit(true);
Statement stmt = con.createStatement ();
stmt.execute ("drop FUNCTION testspg__getRefcursor ();");
stmt.execute ("drop FUNCTION testspg__getEmptyRefcursor ();");
TestUtil.dropTable(con, "testrs");
TestUtil.closeDB(con);
}
public void testResult() throws SQLException
{
CallableStatement call = con.prepareCall("{ ? = call testspg__getRefcursor () }");
call.registerOutParameter(1, Types.OTHER);
call.execute();
ResultSet rs = (ResultSet) call.getObject(1);
assertTrue(rs.next());
assertTrue(rs.getInt(1) == 1);
assertTrue(rs.next());
assertTrue(rs.getInt(1) == 2);
assertTrue(rs.next());
assertTrue(rs.getInt(1) == 3);
assertTrue(rs.next());
assertTrue(rs.getInt(1) == 4);
assertTrue(rs.next());
assertTrue(rs.getInt(1) == 6);
assertTrue(rs.next());
assertTrue(rs.getInt(1) == 9);
assertTrue(!rs.next());
call.close();
}
public void testEmptyResult() throws SQLException
{
CallableStatement call = con.prepareCall("{ ? = call testspg__getEmptyRefcursor () }");
call.registerOutParameter(1, Types.OTHER);
call.execute();
ResultSet rs = (ResultSet) call.getObject(1);
assertTrue(!rs.next());
call.close();
}
public void testMetaData() throws SQLException
{
if (!TestUtil.haveMinimumServerVersion(con, "7.4"))
return;
CallableStatement call = con.prepareCall("{ ? = call testspg__getRefcursor () }");
call.registerOutParameter(1, Types.OTHER);
call.execute();
ResultSet rs = (ResultSet) call.getObject(1);
ResultSetMetaData rsmd = rs.getMetaData();
assertNotNull(rsmd);
assertEquals(1, rsmd.getColumnCount());
assertEquals(Types.INTEGER, rsmd.getColumnType(1));
assertEquals("int4", rsmd.getColumnTypeName(1));
rs.close();
call.close();
}
public void testResultType() throws SQLException
{
CallableStatement call = con.prepareCall("{ ? = call testspg__getRefcursor () }", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
call.registerOutParameter(1, Types.OTHER);
call.execute();
ResultSet rs = (ResultSet) call.getObject(1);
assertEquals(rs.getType(), ResultSet.TYPE_SCROLL_INSENSITIVE);
assertEquals(rs.getConcurrency(), ResultSet.CONCUR_READ_ONLY);
assertTrue(rs.last());
assertEquals(6, rs.getRow());
}
}
|
