← Back to branch summary

~ubuntu-branches/ubuntu/oneiric/libpgjava/oneiric

« back to all changes in this revision

Viewing changes to src/interfaces/jdbc/org/postgresql/test/jdbc2/ServerPreparedStmtTest.java

  • Committer: Bazaar Package Importer
  • Author(s): Arnaud Vandyck
  • Date: 2005-04-21 14:25:11 UTC
  • mfrom: (1.2.1 upstream) (2.1.1 warty)
  • Revision ID: james.westby@ubuntu.com-20050421142511-wibh5vc31fkrorx7
Tags: 7.4.7-3
Built with sources...

Show diffs side-by-side

added added

removed removed

Lines of Context:
src/interfaces/jdbc/org/postgresql/test/jdbc2/ServerPreparedStmtTest.java
 
1
package org.postgresql.test.jdbc2;
 
2
 
 
3
import org.postgresql.PGStatement;
 
4
import org.postgresql.test.TestUtil;
 
5
import java.sql.Connection;
 
6
import java.sql.PreparedStatement;
 
7
import java.sql.ResultSet;
 
8
import java.sql.Statement;
 
9
 
 
10
import junit.framework.TestCase;
 
11
 
 
12
/*
 
13
 *  Tests for using server side prepared statements
 
14
 */
 
15
public class ServerPreparedStmtTest extends TestCase
 
16
{
 
17
        private Connection con;
 
18
 
 
19
        public ServerPreparedStmtTest(String name)
 
20
        {
 
21
                super(name);
 
22
        }
 
23
 
 
24
        protected void setUp() throws Exception
 
25
        {
 
26
                con = TestUtil.openDB();
 
27
                Statement stmt = con.createStatement();
 
28
 
 
29
                TestUtil.createTable(con, "testsps", "id integer, value boolean");
 
30
 
 
31
                stmt.executeUpdate("INSERT INTO testsps VALUES (1,'t')");
 
32
                stmt.executeUpdate("INSERT INTO testsps VALUES (2,'t')");
 
33
                stmt.executeUpdate("INSERT INTO testsps VALUES (3,'t')");
 
34
                stmt.executeUpdate("INSERT INTO testsps VALUES (4,'t')");
 
35
                stmt.executeUpdate("INSERT INTO testsps VALUES (6,'t')");
 
36
                stmt.executeUpdate("INSERT INTO testsps VALUES (9,'f')");
 
37
 
 
38
                stmt.close();
 
39
        }
 
40
 
 
41
        protected void tearDown() throws Exception
 
42
        {
 
43
                TestUtil.dropTable(con, "testsps");
 
44
                TestUtil.closeDB(con);
 
45
        }
 
46
 
 
47
        public void testPreparedStatementsNoBinds() throws Exception
 
48
        {
 
49
                PreparedStatement pstmt = con.prepareStatement("SELECT * FROM testsps WHERE id = 2");
 
50
        ((PGStatement)pstmt).setUseServerPrepare(true);
 
51
        if (TestUtil.haveMinimumServerVersion(con,"7.3")) {
 
52
                        assertTrue(((PGStatement)pstmt).isUseServerPrepare());
 
53
                } else {
 
54
                        assertTrue(!((PGStatement)pstmt).isUseServerPrepare());
 
55
                }
 
56
 
 
57
        //Test that basic functionality works
 
58
                ResultSet rs = pstmt.executeQuery();
 
59
                assertTrue(rs.next());
 
60
        assertEquals(2, rs.getInt(1));
 
61
        rs.close();
 
62
 
 
63
        //Verify that subsequent calls still work
 
64
                rs = pstmt.executeQuery();
 
65
                assertTrue(rs.next());
 
66
        assertEquals(2, rs.getInt(1));
 
67
        rs.close();
 
68
 
 
69
        //Verify that using the statement to execute a different query works
 
70
                rs = pstmt.executeQuery("SELECT * FROM testsps WHERE id = 9");
 
71
                assertTrue(rs.next());
 
72
        assertEquals(9, rs.getInt(1));
 
73
        rs.close();
 
74
 
 
75
        ((PGStatement)pstmt).setUseServerPrepare(false);
 
76
        assertTrue(!((PGStatement)pstmt).isUseServerPrepare());
 
77
 
 
78
        //Verify that using the statement still works after turning off prepares
 
79
                rs = pstmt.executeQuery("SELECT * FROM testsps WHERE id = 9");
 
80
                assertTrue(rs.next());
 
81
        assertEquals(9, rs.getInt(1));
 
82
        rs.close();
 
83
 
 
84
                pstmt.close();
 
85
        }
 
86
 
 
87
        public void testPreparedStatementsWithOneBind() throws Exception
 
88
        {
 
89
                PreparedStatement pstmt = con.prepareStatement("SELECT * FROM testsps WHERE id = ?");
 
90
        ((PGStatement)pstmt).setUseServerPrepare(true);
 
91
        if (TestUtil.haveMinimumServerVersion(con,"7.3")) {
 
92
                        assertTrue(((PGStatement)pstmt).isUseServerPrepare());
 
93
                } else {
 
94
                        assertTrue(!((PGStatement)pstmt).isUseServerPrepare());
 
95
                }
 
96
 
 
97
        //Test that basic functionality works
 
98
        pstmt.setInt(1,2);
 
99
                ResultSet rs = pstmt.executeQuery();
 
100
                assertTrue(rs.next());
 
101
        assertEquals(2, rs.getInt(1));
 
102
        rs.close();
 
103
 
 
104
        //Verify that subsequent calls still work
 
105
                rs = pstmt.executeQuery();
 
106
                assertTrue(rs.next());
 
107
        assertEquals(2, rs.getInt(1));
 
108
        rs.close();
 
109
 
 
110
        //Verify that using the statement to execute a different query works
 
111
                rs = pstmt.executeQuery("SELECT * FROM testsps WHERE id = 9");
 
112
                assertTrue(rs.next());
 
113
        assertEquals(9, rs.getInt(1));
 
114
        rs.close();
 
115
 
 
116
        ((PGStatement)pstmt).setUseServerPrepare(false);
 
117
        assertTrue(!((PGStatement)pstmt).isUseServerPrepare());
 
118
 
 
119
        //Verify that using the statement still works after turning off prepares
 
120
                rs = pstmt.executeQuery("SELECT * FROM testsps WHERE id = 9");
 
121
                assertTrue(rs.next());
 
122
        assertEquals(9, rs.getInt(1));
 
123
        rs.close();
 
124
 
 
125
                pstmt.close();
 
126
        }
 
127
 
 
128
        // Verify we can bind booleans-as-objects ok.
 
129
        public void testBooleanObjectBind() throws Exception
 
130
        {
 
131
                PreparedStatement pstmt = con.prepareStatement("SELECT * FROM testsps WHERE value = ?");
 
132
        ((PGStatement)pstmt).setUseServerPrepare(true);
 
133
        if (TestUtil.haveMinimumServerVersion(con,"7.3")) {
 
134
                        assertTrue(((PGStatement)pstmt).isUseServerPrepare());
 
135
                } else {
 
136
                        assertTrue(!((PGStatement)pstmt).isUseServerPrepare());
 
137
                }
 
138
 
 
139
        pstmt.setObject(1, new Boolean(false), java.sql.Types.BIT);
 
140
                ResultSet rs = pstmt.executeQuery();
 
141
                assertTrue(rs.next());
 
142
        assertEquals(9, rs.getInt(1));
 
143
        rs.close();
 
144
        }
 
145
 
 
146
        // Verify we can bind booleans-as-integers ok.
 
147
        public void testBooleanIntegerBind() throws Exception
 
148
        {
 
149
                PreparedStatement pstmt = con.prepareStatement("SELECT * FROM testsps WHERE id = ?");
 
150
        ((PGStatement)pstmt).setUseServerPrepare(true);
 
151
        if (TestUtil.haveMinimumServerVersion(con,"7.3")) {
 
152
                        assertTrue(((PGStatement)pstmt).isUseServerPrepare());
 
153
                } else {
 
154
                        assertTrue(!((PGStatement)pstmt).isUseServerPrepare());
 
155
                }
 
156
 
 
157
        pstmt.setObject(1, new Boolean(true), java.sql.Types.INTEGER);
 
158
                ResultSet rs = pstmt.executeQuery();
 
159
                assertTrue(rs.next());
 
160
        assertEquals(1, rs.getInt(1));
 
161
        rs.close();
 
162
        }
 
163
 
 
164
        // Verify we can bind booleans-as-native-types ok.
 
165
        public void testBooleanBind() throws Exception
 
166
        {
 
167
                PreparedStatement pstmt = con.prepareStatement("SELECT * FROM testsps WHERE value = ?");
 
168
        ((PGStatement)pstmt).setUseServerPrepare(true);
 
169
        if (TestUtil.haveMinimumServerVersion(con,"7.3")) {
 
170
                        assertTrue(((PGStatement)pstmt).isUseServerPrepare());
 
171
                } else {
 
172
                        assertTrue(!((PGStatement)pstmt).isUseServerPrepare());
 
173
                }
 
174
 
 
175
        pstmt.setBoolean(1, false);
 
176
                ResultSet rs = pstmt.executeQuery();
 
177
                assertTrue(rs.next());
 
178
        assertEquals(9, rs.getInt(1));
 
179
        rs.close();
 
180
        }
 
181
 
 
182
        public void testPreparedStatementsWithBinds() throws Exception
 
183
        {
 
184
                PreparedStatement pstmt = con.prepareStatement("SELECT * FROM testsps WHERE id = ? or id = ?");
 
185
        ((PGStatement)pstmt).setUseServerPrepare(true);
 
186
        if (TestUtil.haveMinimumServerVersion(con,"7.3")) {
 
187
                        assertTrue(((PGStatement)pstmt).isUseServerPrepare());
 
188
                } else {
 
189
                        assertTrue(!((PGStatement)pstmt).isUseServerPrepare());
 
190
                }
 
191
 
 
192
        //Test that basic functionality works
 
193
        //bind different datatypes
 
194
        pstmt.setInt(1,2);
 
195
        pstmt.setString(2,"2");
 
196
                ResultSet rs = pstmt.executeQuery();
 
197
                assertTrue(rs.next());
 
198
        assertEquals(2, rs.getInt(1));
 
199
        rs.close();
 
200
 
 
201
        //Verify that subsequent calls still work
 
202
                rs = pstmt.executeQuery();
 
203
                assertTrue(rs.next());
 
204
        assertEquals(2, rs.getInt(1));
 
205
        rs.close();
 
206
 
 
207
                pstmt.close();
 
208
        }
 
209
 
 
210
        public void testSPSToggle() throws Exception
 
211
        {
 
212
                // Verify we can toggle UseServerPrepare safely before a query is executed.
 
213
                PreparedStatement pstmt = con.prepareStatement("SELECT * FROM testsps WHERE id = 2");
 
214
        ((PGStatement)pstmt).setUseServerPrepare(true);
 
215
        ((PGStatement)pstmt).setUseServerPrepare(false);
 
216
        }
 
217
 
 
218
        public void testBytea() throws Exception
 
219
        {
 
220
                // Verify we can use setBytes() with a server-prepared update.
 
221
                try {
 
222
                        TestUtil.createTable(con, "testsps_bytea", "data bytea");
 
223
                        
 
224
                        PreparedStatement pstmt = con.prepareStatement("INSERT INTO testsps_bytea(data) VALUES (?)");
 
225
                        ((PGStatement)pstmt).setUseServerPrepare(true);
 
226
                        pstmt.setBytes(1, new byte[100]);
 
227
                        pstmt.executeUpdate();
 
228
                } finally {
 
229
                        TestUtil.dropTable(con, "testsps_bytea");
 
230
                }
 
231
        }
 
232
 
 
233
        // Check statements are not transformed when they shouldn't be.
 
234
        public void TODO_FAILS_testCreateTable() throws Exception {
 
235
                // CREATE TABLE isn't supported by PREPARE; the driver should realize this and
 
236
                // still complete without error.
 
237
                PreparedStatement pstmt = con.prepareStatement("CREATE TABLE testsps_bad(data int)");
 
238
                ((PGStatement)pstmt).setUseServerPrepare(true);
 
239
                pstmt.executeUpdate();
 
240
                TestUtil.dropTable(con, "testsps_bad");
 
241
        }
 
242
 
 
243
        public void TODO_FAILS_testMultistatement() throws Exception {
 
244
                // Shouldn't try to PREPARE this one, if we do we get:
 
245
                //   PREPARE x(int,int) AS INSERT .... $1 ; INSERT ... $2    -- syntax error
 
246
                try {
 
247
                        TestUtil.createTable(con, "testsps_multiple", "data int");
 
248
                        PreparedStatement pstmt = con.prepareStatement("INSERT INTO testsps_multiple(data) VALUES (?); INSERT INTO testsps_multiple(data) VALUES (?)");
 
249
                        ((PGStatement)pstmt).setUseServerPrepare(true);                 
 
250
                        pstmt.setInt(1, 1);
 
251
                        pstmt.setInt(2, 2);
 
252
                        pstmt.executeUpdate(); // Two inserts.
 
253
 
 
254
                        pstmt.setInt(1, 3);
 
255
                        pstmt.setInt(2, 4);
 
256
                        pstmt.executeUpdate(); // Two more inserts.
 
257
                        
 
258
                        ResultSet check = con.createStatement().executeQuery("SELECT COUNT(*) FROM testsps_multiple");
 
259
                        assertTrue(check.next());
 
260
                        assertEquals(4, check.getInt(1));
 
261
                } finally {
 
262
                        TestUtil.dropTable(con, "testsps_multiple");
 
263
                }
 
264
        }
 
265
}