1
/*-------------------------------------------------------------------------
3
* Copyright (c) 2004-2005, PostgreSQL Global Development Group
6
* $PostgreSQL: pgjdbc/org/postgresql/test/jdbc2/StatementTest.java,v 1.17.2.2 2006/02/01 18:52:30 jurka Exp $
8
*-------------------------------------------------------------------------
10
package org.postgresql.test.jdbc2;
12
import org.postgresql.jdbc2.AbstractJdbc2Connection;
13
import org.postgresql.test.TestUtil;
14
import junit.framework.*;
20
public class StatementTest extends TestCase
22
Connection con = null;
24
public StatementTest(String name )
28
protected void setUp() throws Exception
32
con = TestUtil.openDB();
33
TestUtil.createTempTable(con, "test_statement",
35
TestUtil.createTempTable(con, "escapetest",
36
"ts timestamp, d date, t time, \")\" varchar(5), \"\"\"){a}'\" text ");
37
TestUtil.createTempTable(con, "comparisontest","str1 varchar(5), str2 varchar(15)");
38
Statement stmt = con.createStatement();
39
stmt.executeUpdate(TestUtil.insertSQL("comparisontest","str1,str2","'_abcd','_found'"));
40
stmt.executeUpdate(TestUtil.insertSQL("comparisontest","str1,str2","'%abcd','%found'"));
44
protected void tearDown() throws Exception
47
TestUtil.dropTable( con, "test_statement" );
48
TestUtil.dropTable( con, "escapetest" );
49
TestUtil.dropTable(con,"comparisontest");
53
public void testClose() throws SQLException
55
Statement stmt = null;
56
stmt = con.createStatement();
62
this.fail( "statements should not be re-used after close" );
64
catch (SQLException ex)
70
* Closing a Statement twice is not an error.
72
public void testDoubleClose() throws SQLException
74
Statement stmt = con.createStatement();
79
public void testMultiExecute() throws SQLException
81
Statement stmt = con.createStatement();
82
stmt.execute("SELECT 1; SELECT 2");
84
ResultSet rs = stmt.getResultSet();
85
assertTrue(rs.next());
86
assertEquals(1, rs.getInt(1));
89
assertTrue(stmt.getMoreResults());
90
rs = stmt.getResultSet();
91
assertTrue(rs.next());
92
assertEquals(2, rs.getInt(1));
97
public void testEmptyQuery() throws SQLException
99
Statement stmt = con.createStatement();
101
assertNull(stmt.getResultSet());
102
assertTrue(!stmt.getMoreResults());
105
public void testUpdateCount() throws SQLException
107
Statement stmt = con.createStatement();
110
count = stmt.executeUpdate("INSERT INTO test_statement VALUES (3)");
111
assertEquals(1, count);
112
count = stmt.executeUpdate("INSERT INTO test_statement VALUES (3)");
113
assertEquals(1, count);
115
count = stmt.executeUpdate("UPDATE test_statement SET i=4");
116
assertEquals(2, count);
118
count = stmt.executeUpdate("CREATE TEMP TABLE another_table (a int)");
119
assertEquals(0, count);
122
public void testEscapeProcessing() throws SQLException
124
Statement stmt = con.createStatement();
127
count = stmt.executeUpdate("insert into escapetest (ts) values ({ts '1900-01-01 00:00:00'})");
128
assertEquals(1, count);
130
count = stmt.executeUpdate("insert into escapetest (d) values ({d '1900-01-01'})");
131
assertEquals(1, count);
133
count = stmt.executeUpdate("insert into escapetest (t) values ({t '00:00:00'})");
134
assertEquals(1, count);
136
ResultSet rs = stmt.executeQuery( "select {fn version()} as version" );
137
assertTrue(rs.next());
139
// check nested and multiple escaped functions
140
rs = stmt.executeQuery( "select {fn version()} as version, {fn log({fn log(3.0)})} as log" );
141
assertTrue(rs.next());
142
assertEquals(Math.log(Math.log(3)), rs.getDouble(2), 0.00001);
144
stmt.executeUpdate("UPDATE escapetest SET \")\" = 'a', \"\"\"){a}'\" = 'b'");
146
// check "difficult" values
147
rs = stmt.executeQuery("select {fn concat(')',escapetest.\")\")} as concat" +
148
", {fn concat('{','}')} " +
149
", {fn concat('\\'','\"')} " +
150
", {fn concat(\"\"\"){a}'\", '''}''')} " +
152
assertTrue(rs.next());
153
assertEquals(")a", rs.getString(1));
154
assertEquals("{}", rs.getString(2));
155
assertEquals("'\"", rs.getString(3));
156
assertEquals("b'}'", rs.getString(4));
158
count = stmt.executeUpdate( "create temp table b (i int)" );
159
assertEquals(0, count);
161
rs = stmt.executeQuery( "select * from {oj test_statement a left outer join b on (a.i=b.i)} ");
162
assertTrue(!rs.next());
163
// test escape escape character
164
rs = stmt.executeQuery("select str2 from comparisontest where str1 like '|_abcd' {escape '|'} ");
165
assertTrue(rs.next());
166
assertEquals("_found",rs.getString(1));
167
rs = stmt.executeQuery("select str2 from comparisontest where str1 like '|%abcd' {escape '|'} ");
168
assertTrue(rs.next());
169
assertEquals("%found",rs.getString(1));
173
public void testPreparedFunction() throws SQLException
175
PreparedStatement pstmt = con.prepareStatement("SELECT {fn concat('a', ?)}");
177
ResultSet rs = pstmt.executeQuery();
178
assertTrue(rs.next());
179
assertEquals("a5", rs.getString(1));
182
public void testNumericFunctions() throws SQLException
184
Statement stmt = con.createStatement();
186
ResultSet rs = stmt.executeQuery("select {fn abs(-2.3)} as abs ");
187
assertTrue(rs.next());
188
assertEquals(2.3f, rs.getFloat(1), 0.00001);
190
rs = stmt.executeQuery("select {fn acos(-0.6)} as acos ");
191
assertTrue(rs.next());
192
assertEquals(Math.acos(-0.6), rs.getDouble(1), 0.00001);
194
rs = stmt.executeQuery("select {fn asin(-0.6)} as asin ");
195
assertTrue(rs.next());
196
assertEquals(Math.asin(-0.6), rs.getDouble(1), 0.00001);
198
rs = stmt.executeQuery("select {fn atan(-0.6)} as atan ");
199
assertTrue(rs.next());
200
assertEquals(Math.atan(-0.6), rs.getDouble(1), 0.00001);
202
rs = stmt.executeQuery("select {fn atan2(-2.3,7)} as atan2 ");
203
assertTrue(rs.next());
204
assertEquals(Math.atan2(-2.3,7), rs.getDouble(1), 0.00001);
206
rs = stmt.executeQuery("select {fn ceiling(-2.3)} as ceiling ");
207
assertTrue(rs.next());
208
assertEquals(-2, rs.getDouble(1), 0.00001);
210
rs = stmt.executeQuery("select {fn cos(-2.3)} as cos, {fn cot(-2.3)} as cot ");
211
assertTrue(rs.next());
212
assertEquals(Math.cos(-2.3), rs.getDouble(1), 0.00001);
213
assertEquals(1/Math.tan(-2.3), rs.getDouble(2), 0.00001);
215
rs = stmt.executeQuery("select {fn degrees({fn pi()})} as degrees ");
216
assertTrue(rs.next());
217
assertEquals(180, rs.getDouble(1), 0.00001);
219
rs = stmt.executeQuery("select {fn exp(-2.3)}, {fn floor(-2.3)}," +
220
" {fn log(2.3)},{fn log10(2.3)},{fn mod(3,2)}");
221
assertTrue(rs.next());
222
assertEquals(Math.exp(-2.3), rs.getDouble(1), 0.00001);
223
assertEquals(-3, rs.getDouble(2), 0.00001);
224
assertEquals(Math.log(2.3), rs.getDouble(3), 0.00001);
225
assertEquals(Math.log(2.3)/Math.log(10), rs.getDouble(4), 0.00001);
226
assertEquals(1, rs.getDouble(5), 0.00001);
228
rs = stmt.executeQuery("select {fn pi()}, {fn power(7,-2.3)}," +
229
" {fn radians(-180)},{fn rand(-2.3)},{fn round(3.1294,2)}");
230
assertTrue(rs.next());
231
assertEquals(Math.PI, rs.getDouble(1), 0.00001);
232
assertEquals(Math.pow(7,-2.3), rs.getDouble(2), 0.00001);
233
assertEquals(-Math.PI, rs.getDouble(3), 0.00001);
234
rs.getDouble(4); // for random all we can test is that it returns something
235
assertEquals(3.13, rs.getDouble(5), 0.00001);
237
rs = stmt.executeQuery("select {fn sign(-2.3)}, {fn sin(-2.3)}," +
238
" {fn sqrt(2.3)},{fn tan(-2.3)},{fn truncate(3.1294,2)}");
239
assertTrue(rs.next());
240
assertEquals(-1, rs.getInt(1));
241
assertEquals(Math.sin(-2.3), rs.getDouble(2), 0.00001);
242
assertEquals(Math.sqrt(2.3), rs.getDouble(3), 0.00001);
243
assertEquals(Math.tan(-2.3), rs.getDouble(4), 0.00001);
244
assertEquals(3.12, rs.getDouble(5), 0.00001);
247
public void testStringFunctions() throws SQLException
249
Statement stmt = con.createStatement();
250
ResultSet rs = stmt.executeQuery("select {fn ascii(' test')},{fn char(32)}" +
251
",{fn concat('ab','cd')}" +
252
",{fn lcase('aBcD')},{fn left('1234',2)},{fn length('123 ')}" +
253
",{fn locate('bc','abc')},{fn locate('bc','abc',3)}");
254
assertTrue(rs.next());
255
assertEquals(32,rs.getInt(1));
256
assertEquals(" ",rs.getString(2));
257
assertEquals("abcd",rs.getString(3));
258
assertEquals("abcd",rs.getString(4));
259
assertEquals("12",rs.getString(5));
260
assertEquals(3,rs.getInt(6));
261
assertEquals(2,rs.getInt(7));
262
assertEquals(0,rs.getInt(8));
264
if (TestUtil.haveMinimumServerVersion(con, "7.3")) {
265
rs = stmt.executeQuery("SELECT {fn insert('abcdef',3,2,'xxxx')}" +
266
",{fn replace('abcdbc','bc','x')}");
267
assertTrue(rs.next());
268
assertEquals("abxxxxef",rs.getString(1));
269
assertEquals("axdx",rs.getString(2));
272
rs = stmt.executeQuery("select {fn ltrim(' ab')},{fn repeat('ab',2)}" +
273
",{fn right('abcde',2)},{fn rtrim('ab ')}" +
274
",{fn space(3)},{fn substring('abcd',2,2)}" +
275
",{fn ucase('aBcD')}");
276
assertTrue(rs.next());
277
assertEquals("ab",rs.getString(1));
278
assertEquals("abab",rs.getString(2));
279
assertEquals("de",rs.getString(3));
280
assertEquals("ab",rs.getString(4));
281
assertEquals(" ",rs.getString(5));
282
assertEquals("bc",rs.getString(6));
283
assertEquals("ABCD",rs.getString(7));
286
public void testDateFunctions() throws SQLException
288
Statement stmt = con.createStatement();
289
ResultSet rs = stmt.executeQuery("select {fn curdate()},{fn curtime()}" +
290
",{fn dayname({fn now()})}, {fn dayofmonth({fn now()})}" +
291
",{fn dayofweek({ts '2005-01-17 12:00:00'})},{fn dayofyear({fn now()})}" +
292
",{fn hour({fn now()})},{fn minute({fn now()})}" +
293
",{fn month({fn now()})}" +
294
",{fn monthname({fn now()})},{fn quarter({fn now()})}" +
295
",{fn second({fn now()})},{fn week({fn now()})}" +
296
",{fn year({fn now()})} ");
297
assertTrue(rs.next());
298
// ensure sunday =>1 and monday =>2
299
assertEquals(2,rs.getInt(5));
302
public void testSystemFunctions() throws SQLException
304
Statement stmt = con.createStatement();
305
ResultSet rs = stmt.executeQuery("select {fn ifnull(null,'2')}" +
307
assertTrue(rs.next());
308
assertEquals("2",rs.getString(1));
309
assertEquals(TestUtil.getUser(),rs.getString(2));
311
if (TestUtil.haveMinimumServerVersion(con, "7.3")) {
312
rs = stmt.executeQuery("select {fn database()} ");
313
assertTrue(rs.next());
314
assertEquals(TestUtil.getDatabase(),rs.getString(1));
318
public void testWarningsAreCleared() throws SQLException
320
Statement stmt = con.createStatement();
321
// Will generate a NOTICE: for primary key index creation
322
stmt.execute("CREATE TEMP TABLE unused (a int primary key)");
323
stmt.executeQuery("SELECT 1");
324
// Executing another query should clear the warning from the first one.
325
assertNull(stmt.getWarnings());
330
* The parser tries to break multiple statements into individual
331
* queries as required by the V3 extended query protocol. It can
332
* be a little overzealous sometimes and this test ensures we
333
* keep multiple rule actions together in one statement.
335
public void testParsingSemiColons() throws SQLException
337
Statement stmt = con.createStatement();
338
stmt.execute("CREATE RULE r1 AS ON INSERT TO escapetest DO (DELETE FROM test_statement ; INSERT INTO test_statement VALUES (1); INSERT INTO test_statement VALUES (2); );");
339
stmt.executeUpdate("INSERT INTO escapetest(ts) VALUES (NULL)");
340
ResultSet rs = stmt.executeQuery("SELECT i from test_statement ORDER BY i");
341
assertTrue(rs.next());
342
assertEquals(1, rs.getInt(1));
343
assertTrue(rs.next());
344
assertEquals(2, rs.getInt(1));
345
assertTrue(!rs.next());
348
public void testUnbalancedParensParseError() throws SQLException
350
Statement stmt = con.createStatement();
352
stmt.executeQuery("SELECT i FROM test_statement WHERE (1 > 0)) ORDER BY i");
353
fail("Should have thrown a parse error.");
354
} catch (SQLException sqle) { }