001: /*
002: * $Id: TestPrepareStatement.java,v 1.8 2005/05/02 22:32:02 ahimanikya Exp $
003: * =======================================================================
004: * Copyright (c) 2002-2004 Axion Development Team. All rights reserved.
005: *
006: * Redistribution and use in source and binary forms, with or without
007: * modification, are permitted provided that the following conditions
008: * are met:
009: *
010: * 1. Redistributions of source code must retain the above
011: * copyright notice, this list of conditions and the following
012: * disclaimer.
013: *
014: * 2. Redistributions in binary form must reproduce the above copyright
015: * notice, this list of conditions and the following disclaimer in
016: * the documentation and/or other materials provided with the
017: * distribution.
018: *
019: * 3. The names "Tigris", "Axion", nor the names of its contributors may
020: * not be used to endorse or promote products derived from this
021: * software without specific prior written permission.
022: *
023: * 4. Products derived from this software may not be called "Axion", nor
024: * may "Tigris" or "Axion" appear in their names without specific prior
025: * written permission.
026: *
027: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
028: * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
029: * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
030: * PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
031: * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
032: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
033: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
034: * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
035: * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
036: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
037: * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
038: * =======================================================================
039: */
040:
041: package org.axiondb.functional;
042:
043: import java.io.InputStream;
044: import java.sql.PreparedStatement;
045: import java.sql.ResultSet;
046: import java.sql.Statement;
047: import java.util.Properties;
048:
049: import junit.framework.Test;
050: import junit.framework.TestSuite;
051:
052: import org.axiondb.engine.BaseDatabase;
053: import org.axiondb.engine.MemoryDatabase;
054: import org.axiondb.jdbc.AxionConnection;
055:
056: /**
057: * @version $Revision: 1.8 $ $Date: 2005/05/02 22:32:02 $
058: * @author Ahimanikya Satapathy
059: */
060: public class TestPrepareStatement extends AbstractFunctionalTest {
061:
062: //------------------------------------------------------------ Conventional
063:
064: public TestPrepareStatement(String testName) {
065: super (testName);
066: }
067:
068: public static Test suite() {
069: return new TestSuite(TestPrepareStatement.class);
070: }
071:
072: //--------------------------------------------------------------- Lifecycle
073:
074: public void setUp() throws Exception {
075: InputStream in = BaseDatabase.class.getClassLoader()
076: .getResourceAsStream("org/axiondb/axiondb.properties");
077: if (in == null) {
078: in = BaseDatabase.class.getClassLoader()
079: .getResourceAsStream("axiondb.properties");
080: }
081: Properties prop = new Properties();
082: prop.load(in);
083: prop.setProperty("database.commitsize", "2");
084: MemoryDatabase db = new MemoryDatabase("testdb", prop);
085:
086: _conn = new AxionConnection(db);
087: _stmt = _conn.createStatement();
088: //super.setUp();
089: }
090:
091: public void tearDown() throws Exception {
092: super .tearDown();
093: }
094:
095: //------------------------------------------------------------------- Tests
096:
097: public void testBindVariableInFunction() throws Exception {
098: PreparedStatement stmt = _conn
099: .prepareStatement("select upper(?)");
100:
101: try {
102: stmt.executeQuery();
103: fail("Expected Exception: unbound variable");
104: } catch (Exception e) {
105: // expected
106: }
107:
108: stmt.setString(1, "test");
109: assertResult("TEST", stmt.executeQuery());
110: stmt.setString(1, "test2");
111: assertResult("TEST2", stmt.executeQuery());
112: stmt.close();
113: }
114:
115: public void testBindVariableAsSubSelect() throws Exception {
116: createTableFoo();
117: populateTableFoo();
118: PreparedStatement pstmt = _conn
119: .prepareStatement("select NUM, (select UPPER(?)) from FOO where UPPER(STR) = (select UPPER(?))");
120: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
121: pstmt.setString(1, "will become upper");
122: pstmt.setString(2, String.valueOf(i));
123: _rset = pstmt.executeQuery();
124: assertNotNull("Should have been able to create ResultSet",
125: _rset);
126: assertTrue("ResultSet should contain more rows", _rset
127: .next());
128: assertEquals(i, _rset.getInt(1));
129: assertEquals("WILL BECOME UPPER", _rset.getString(2));
130: assertTrue("ResultSet shouldn't think value was null",
131: !_rset.wasNull());
132: assertTrue("ResultSet shouldn't have any more rows", !_rset
133: .next());
134: _rset.close();
135: pstmt.clearParameters();
136: }
137: pstmt.close();
138: }
139:
140: private void create_table_x() throws Exception {
141: Statement stmt = _conn.createStatement();
142: stmt.execute("drop table if exists x ");
143: stmt.execute("create table x(id int, name varchar(3))");
144: assertEquals(1, stmt
145: .executeUpdate("insert into x values(1,'aaa')"));
146: assertEquals(1, stmt
147: .executeUpdate("insert into x values(2,'aaa')"));
148: assertEquals(1, stmt
149: .executeUpdate("insert into x values(3,'bbb')"));
150: assertEquals(1, stmt
151: .executeUpdate("insert into x values(4,'bbb')"));
152: stmt.close();
153: }
154:
155: public void testMultipleTableInsert() throws Exception {
156: _stmt.execute("create table x ( id int, name varchar(10) )");
157: _stmt.execute("create table y ( id int, name varchar(10) )");
158: _stmt.execute("create table z ( id int, name varchar(10) )");
159: _stmt.execute("insert into x values ( 1, 'Amy' )");
160: _stmt.execute("insert into x values ( 2, 'Mike' )");
161: _stmt.execute("insert into x values ( 3, 'Teresa' )");
162:
163: PreparedStatement pstmt = _conn
164: .prepareStatement("insert ALL "
165: + " when S.id < ? then into y values(S.id,S.name)"
166: + " when S.id > ? then into z values(S.id, S.name) (select * from x) as S");
167: pstmt.setInt(1, 2);
168: pstmt.setInt(2, 1);
169: assertEquals(3, pstmt.executeUpdate());
170:
171: ResultSet rset = _stmt.executeQuery("select count(*) from y");
172: assertTrue(rset.next());
173: assertEquals(1, rset.getInt(1));
174: rset.close();
175:
176: rset = _stmt.executeQuery("select count(*) from z");
177: assertTrue(rset.next());
178: assertEquals(2, rset.getInt(1));
179: rset.close();
180:
181: _stmt.execute("truncate table y");
182: _stmt.execute("truncate table z");
183:
184: rset = _stmt.executeQuery("select count(*) from y");
185: assertTrue(rset.next());
186: assertEquals(0, rset.getInt(1));
187: rset.close();
188:
189: rset = _stmt.executeQuery("select count(*) from z");
190: assertTrue(rset.next());
191: assertEquals(0, rset.getInt(1));
192: rset.close();
193:
194: rset = _stmt.executeQuery("select count(*) from x");
195: assertTrue(rset.next());
196: assertEquals(3, rset.getInt(1));
197: rset.close();
198:
199: pstmt.setInt(1, 3);
200: pstmt.setInt(2, 0);
201: assertEquals(5, pstmt.executeUpdate());
202:
203: rset = _stmt.executeQuery("select count(*) from y");
204: assertTrue(rset.next());
205: assertEquals(2, rset.getInt(1));
206: rset.close();
207:
208: rset = _stmt.executeQuery("select count(*) from z");
209: assertTrue(rset.next());
210: assertEquals(3, rset.getInt(1));
211: rset.close();
212:
213: pstmt.close();
214: }
215:
216: public void testInsertSelect() throws Exception {
217: _stmt.execute("create table x ( id int, name varchar(10) )");
218: _stmt.execute("create table y ( id int, name varchar(10) )");
219: _stmt.execute("insert into x values ( 1, 'Amy' )");
220: _stmt.execute("insert into x values ( 2, 'Mike' )");
221: _stmt.execute("insert into x values ( 3, 'Teresa' )");
222:
223: PreparedStatement pstmt = _conn
224: .prepareStatement("insert into y "
225: + " select id, (? || name) from x");
226: pstmt.setString(1, "Mr.");
227: assertEquals(3, pstmt.executeUpdate());
228:
229: ResultSet rset = _stmt
230: .executeQuery("select name from y where name='Mr.Amy'");
231: assertTrue(rset.next());
232: assertEquals("Mr.Amy", rset.getString(1));
233: rset.close();
234:
235: pstmt.close();
236: }
237:
238: public void testMultipleTableInsert2() throws Exception {
239: _stmt.execute("create table x ( id int, name varchar(10) )");
240: _stmt.execute("create table y ( id int, name varchar(10) )");
241: _stmt.execute("create table z ( id int, name varchar(10) )");
242:
243: PreparedStatement srcpstmt = _conn
244: .prepareStatement("insert into x values ( ?, ? )");
245: srcpstmt.setInt(1, 1);
246: srcpstmt.setString(2, "Amy");
247: assertEquals(1, srcpstmt.executeUpdate());
248:
249: srcpstmt.setInt(1, 2);
250: srcpstmt.setString(2, "Mike");
251: assertEquals(1, srcpstmt.executeUpdate());
252:
253: srcpstmt.setInt(1, 3);
254: srcpstmt.setString(2, "Teresa");
255: assertEquals(1, srcpstmt.executeUpdate());
256: srcpstmt.close();
257:
258: PreparedStatement pstmt = _conn.prepareStatement("insert ALL "
259: + " when S.id < ? then into y "
260: + " when S.id > ? then into z (select * from x) as S");
261: pstmt.setInt(1, 2);
262: pstmt.setInt(2, 1);
263: assertEquals(3, pstmt.executeUpdate());
264: pstmt.close();
265:
266: ResultSet rset = _stmt.executeQuery("select count(*) from y");
267: assertTrue(rset.next());
268: assertEquals(1, rset.getInt(1));
269: rset.close();
270:
271: rset = _stmt.executeQuery("select count(*) from z");
272: assertTrue(rset.next());
273: assertEquals(2, rset.getInt(1));
274: rset.close();
275:
276: }
277:
278: public void testBasicSubSelect() throws Exception {
279: create_table_x();
280: Statement stmt = _conn.createStatement();
281:
282: // insert...select...
283: stmt.execute("create table y(id int, name varchar(3))");
284: assertEquals(4, stmt
285: .executeUpdate("insert into y select * from x"));
286:
287: // exists with sub-select
288: PreparedStatement pstmt = _conn
289: .prepareStatement("select * from x where exists "
290: + " (select id from x where id = ?)");
291: ResultSet rset;
292:
293: for (int i = 1; i < 5; i++) {
294: pstmt.setInt(1, i);
295: rset = pstmt.executeQuery();
296: assertTrue(rset.next());
297: }
298:
299: pstmt.close();
300:
301: // in with sub-select
302: pstmt = _conn.prepareStatement("select * from x where id in"
303: + " (select id from x where id = ?)");
304: for (int i = 1; i < 5; i++) {
305: pstmt.setInt(1, i);
306: rset = pstmt.executeQuery();
307: assertTrue(rset.next());
308: }
309:
310: pstmt.close();
311:
312: // A correlated subquery is a subquery that contains a
313: // reference to a table that also appears in the outer query
314:
315: // scalar sub-select column visibility test
316: pstmt = _conn.prepareStatement(" select x.id, (select "
317: + " (select s.name from y s where y.id = x.id) "
318: + " from y, x where y.id = x.id and y.id = ?) "
319: + " from x");
320: pstmt.setInt(1, 2);
321: rset = pstmt.executeQuery();
322: assertTrue(rset.next());
323:
324: try {
325: pstmt.setInt(2, 2);
326: fail("Expected Exception: Bind variable not found");
327: } catch (Exception e) {
328: // expected
329: }
330:
331: pstmt.close();
332:
333: pstmt = _conn
334: .prepareStatement("SELECT UPPER((SELECT distinct name FROM x where id = ?)) FROM y;");
335: pstmt.setInt(1, 2);
336: rset = pstmt.executeQuery();
337: assertTrue(rset.next());
338: pstmt.close();
339:
340: // sub-select as FromNode
341: // pstmt = _conn.prepareStatement("select * from (select * from x where id not in
342: // (select * from x where x.id = ?)) s where s.id = ?");
343: // pstmt.setInt(1, 2);
344: // pstmt.setInt(2, 2);
345: // rset = pstmt.executeQuery();
346: // assertTrue(rset.next());
347: // pstmt.close();
348:
349: rset.close();
350: stmt.close();
351: }
352:
353: public void test_upsert_via_pstmt() throws Exception {
354: create_table_x();
355: Statement stmt = _conn.createStatement();
356:
357: // insert...select...
358: stmt.execute("drop table if exists y ");
359: stmt.execute("create table y(id int, name varchar(4))");
360: assertEquals(
361: 2,
362: stmt
363: .executeUpdate("insert into y select * from x where name = 'aaa'"));
364:
365: PreparedStatement pstmt = null;
366: try {
367: pstmt = _conn
368: .prepareStatement("upsert into bogus as D "
369: + " using x as S on(S.id = D.id and S.id = ?) "
370: + " when matched then update set D.name = '_' || S.name when not matched then "
371: + " insert (D.id, D.name) values (S.id, S.name)");
372: pstmt.setInt(1, 1);
373: pstmt.execute();
374: fail("Expected table not found Exception");
375: } catch (Exception e) {
376: // expected
377: } finally {
378: if (pstmt != null) {
379: pstmt.close();
380: }
381: }
382:
383: pstmt = _conn
384: .prepareStatement("upsert into y as D "
385: + " using x as S on(S.id = D.id and S.id = ?) "
386: + " when matched then update set D.name = '_' || S.name when not matched then "
387: + " insert (D.id, D.name) values (S.id, S.name)");
388:
389: pstmt.setInt(1, 1);
390: pstmt.execute();
391:
392: assertEquals(4, pstmt.getUpdateCount());
393: pstmt.close();
394:
395: // inner join in sub-query : shd return zero since y is empty
396: stmt.execute("delete from y");
397: assertEquals(
398: 0,
399: stmt
400: .executeUpdate(" merge into y as D using (select x.id, x.name from x,y)"
401: + " as S on(s.id = D.id)"
402: + " when matched then update set D.name = S.name"
403: + " when not matched then insert (D.id, D.name) values (s.id, s.name)"));
404:
405: stmt.executeUpdate("insert into y values(4,'fff')");
406: assertEquals(
407: 4,
408: stmt
409: .executeUpdate(" merge into y as D using (select x.id, x.name from x)"
410: + " as S on(s.id = D.id)"
411: + " when matched then update set D.name = S.name"
412: + " when not matched then insert (D.id, D.name) values (s.id, s.name)"));
413: stmt.close();
414: }
415:
416: public void testUpsertExceptionWhenClause() throws Exception {
417: create_table_x();
418: Statement stmt = _conn.createStatement();
419:
420: // insert...select...
421: stmt.execute("drop table if exists y ");
422: stmt.execute("create table y(id int, name varchar(3))");
423:
424: stmt.execute("drop table if exists z ");
425: stmt.execute("create table z(id int, name varchar(3))");
426:
427: assertEquals(
428: 2,
429: stmt
430: .executeUpdate("insert into y select * from x where name = 'aaa'"));
431:
432: PreparedStatement pstmt = _conn
433: .prepareStatement("upsert into y as D using (select id, name from x) as S on(S.id = D.id)"
434: + " when matched then update set D.name = S.name when not matched then "
435: + " insert (D.id, D.name) values (S.id, S.name) "
436: + " exception when S.id < ? then Insert into z");
437:
438: pstmt.setInt(1, 3);
439: assertEquals(2, pstmt.executeUpdate());
440:
441: pstmt.setInt(1, 3);
442: assertEquals(0, pstmt.executeUpdate());
443:
444: pstmt.close();
445: stmt.close();
446: }
447:
448: public void testUpdateInsertInto() throws Exception {
449: createTableFoo();
450: populateTableFoo();
451: _stmt
452: .execute("create table EXPFOO ( NUM integer, STR varchar2, NUMTWO integer )");
453:
454: PreparedStatement pstmt = _conn
455: .prepareStatement("UPDATE FOO SET FOO.NUM = FOO.NUM + ? "
456: + " EXCEPTION WHEN FOO.NUM < ? THEN INSERT INTO EXPFOO");
457:
458: pstmt.setInt(1, 1);
459: pstmt.setInt(2, 4);
460: assertEquals(2, pstmt.executeUpdate());
461: ResultSet rset = _stmt
462: .executeQuery("select count(*) from expfoo");
463: rset.next();
464: assertEquals(4, rset.getInt(1));
465:
466: pstmt.setInt(1, 1);
467: pstmt.setInt(2, 2);
468: assertEquals(4, pstmt.executeUpdate());
469: rset = _stmt.executeQuery("select count(*) from expfoo");
470: rset.next();
471: assertEquals(6, rset.getInt(1));
472: pstmt.close();
473: }
474:
475: public void testBasicUpdateSelectUsingIndexInnerJoin()
476: throws Exception {
477: Statement stmt = _conn.createStatement();
478: stmt.execute("drop table if exists emp ");
479: stmt.execute("drop table if exists tmp ");
480: stmt.execute("create table emp(id int, name varchar(3))");
481: stmt.execute("create table tmp(tid int, tname varchar(7))");
482: stmt.execute("create btree index tmp_idx on tmp(tid)");
483: assertEquals(1, stmt
484: .executeUpdate("insert into emp values(1,'aaa')"));
485: assertEquals(1, stmt
486: .executeUpdate("insert into emp values(2,'aaa')"));
487: assertEquals(1, stmt
488: .executeUpdate("insert into emp values(3,'aaa')"));
489: assertEquals(1, stmt
490: .executeUpdate("insert into tmp values(1,'bbb')"));
491: assertEquals(1, stmt
492: .executeUpdate("insert into tmp values(2,'bbb')"));
493: assertEquals(1, stmt
494: .executeUpdate("insert into tmp values(3,'bbb')"));
495:
496: assertEquals(3, stmt.executeUpdate("UPDATE tmp "
497: + "SET tmp.tname = (S.name || 'Test') "
498: + "FROM tmp T inner join emp S on T.tid = S.id"));
499:
500: assertResult("aaaTest", "select tname from tmp where tid=1");
501: assertResult("aaa", "select name from emp where id=1");
502: }
503:
504: public void testUpdateInsertInto2() throws Exception {
505: createTableFoo();
506: populateTableFoo();
507: _stmt
508: .execute("create table EXPFOO ( NUM integer, STR varchar2, NUMTWO integer )");
509:
510: PreparedStatement pstmt = _conn
511: .prepareStatement("UPDATE FOO SET FOO.NUM = S1.NUM + ? "
512: + " FROM FOO S1 WHERE S1.NUM = FOO.NUM EXCEPTION WHEN S1.NUM < ? THEN "
513: + " INSERT INTO EXPFOO");
514:
515: pstmt.setInt(1, 1);
516: pstmt.setInt(2, 4);
517: assertEquals(2, pstmt.executeUpdate());
518: ResultSet rset = _stmt
519: .executeQuery("select count(*) from expfoo");
520: rset.next();
521: assertEquals(4, rset.getInt(1));
522:
523: pstmt.setInt(1, 1);
524: pstmt.setInt(2, 2);
525: assertEquals(4, pstmt.executeUpdate());
526: rset = _stmt.executeQuery("select count(*) from expfoo");
527: rset.next();
528: assertEquals(6, rset.getInt(1));
529: pstmt.close();
530: }
531: }
|