0001: /*
0002: * $Id: TestDQL.java,v 1.77 2005/12/20 18:32:45 ahimanikya Exp $
0003: * =======================================================================
0004: * Copyright (c) 2002-2005 Axion Development Team. All rights reserved.
0005: *
0006: * Redistribution and use in source and binary forms, with or without
0007: * modification, are permitted provided that the following conditions
0008: * are met:
0009: *
0010: * 1. Redistributions of source code must retain the above
0011: * copyright notice, this list of conditions and the following
0012: * disclaimer.
0013: *
0014: * 2. Redistributions in binary form must reproduce the above copyright
0015: * notice, this list of conditions and the following disclaimer in
0016: * the documentation and/or other materials provided with the
0017: * distribution.
0018: *
0019: * 3. The names "Tigris", "Axion", nor the names of its contributors may
0020: * not be used to endorse or promote products derived from this
0021: * software without specific prior written permission.
0022: *
0023: * 4. Products derived from this software may not be called "Axion", nor
0024: * may "Tigris" or "Axion" appear in their names without specific prior
0025: * written permission.
0026: *
0027: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
0028: * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
0029: * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
0030: * PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
0031: * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
0032: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
0033: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
0034: * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
0035: * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
0036: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
0037: * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
0038: * =======================================================================
0039: */
0040:
0041: package org.axiondb.functional;
0042:
0043: import java.sql.DriverManager;
0044: import java.sql.PreparedStatement;
0045: import java.sql.ResultSet;
0046: import java.sql.Statement;
0047:
0048: import junit.framework.Test;
0049: import junit.framework.TestSuite;
0050:
0051: import org.apache.commons.collections.Bag;
0052: import org.apache.commons.collections.HashBag;
0053: import org.axiondb.jdbc.AxionConnection;
0054:
0055: /**
0056: * Database Query Language tests.
0057: *
0058: * @version $Revision: 1.77 $ $Date: 2005/12/20 18:32:45 $
0059: * @author Chuck Burdick
0060: * @author Rodney Waldhoff
0061: * @author Dave Pekarek Krohn
0062: * @author Jonathan Giron
0063: */
0064: public class TestDQL extends AbstractFunctionalTest {
0065:
0066: //------------------------------------------------------------ Conventional
0067:
0068: public TestDQL(String testName) {
0069: super (testName);
0070: }
0071:
0072: public static Test suite() {
0073: return new TestSuite(TestDQL.class);
0074: }
0075:
0076: //--------------------------------------------------------------- Lifecycle
0077:
0078: public void setUp() throws Exception {
0079: super .setUp();
0080: }
0081:
0082: public void tearDown() throws Exception {
0083: super .tearDown();
0084: }
0085:
0086: //------------------------------------------------------------------- Tests
0087:
0088: public void testCrossproductStyleInnerJoin() throws Exception {
0089: createTableFoo();
0090: populateTableFoo();
0091: createTableBar();
0092: populateTableBar();
0093:
0094: _rset = _stmt
0095: .executeQuery("select * from FOO F, BAR B where F.NUM = B.ID");
0096: assertNotNull(_rset);
0097: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0098: assertTrue(_rset.next());
0099: assertEquals(_rset.getString("STR"), _rset
0100: .getString("DESCR"));
0101: }
0102: assertTrue(!_rset.next());
0103: _rset.close();
0104:
0105: _rset = _stmt
0106: .executeQuery("select count(*) from FOO F, BAR B where F.NUM = F.NUM");
0107: assertTrue(_rset.next());
0108: assertEquals(_rset.getInt(1), NUM_ROWS_IN_FOO * NUM_ROWS_IN_BAR);
0109: _rset.close();
0110: }
0111:
0112: public void testCrossproductStyleInnerJoinWithWhere()
0113: throws Exception {
0114: createTableFoo();
0115: populateTableFoo();
0116: createTableBar();
0117: populateTableBar();
0118:
0119: PreparedStatement pstmt = _conn
0120: .prepareStatement("select B.DESCR, F.STR from FOO F, BAR B where F.NUM = B.ID and F.NUM = ?");
0121: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0122: pstmt.setInt(1, i);
0123: assertResult(String.valueOf(i), pstmt.executeQuery());
0124: }
0125: pstmt.close();
0126: }
0127:
0128: public void testAnsiStyleInnerJoin() throws Exception {
0129: createTableFoo();
0130: populateTableFoo();
0131: createTableBar();
0132: populateTableBar();
0133:
0134: _rset = _stmt
0135: .executeQuery("select F.STR, B.DESCR from FOO F inner join BAR B on F.NUM = B.ID");
0136: assertNotNull(_rset);
0137: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0138: assertTrue(_rset.next());
0139: assertEquals(_rset.getString(1), _rset.getString(2));
0140: }
0141: assertTrue(!_rset.next());
0142: _rset.close();
0143: }
0144:
0145: public void testAnsiStyleInnerJoinWithWhere() throws Exception {
0146: createTableFoo();
0147: populateTableFoo();
0148: createTableBar();
0149: populateTableBar();
0150:
0151: PreparedStatement pstmt = _conn
0152: .prepareStatement("select STR, DESCR from FOO F inner join BAR B on F.NUM = B.ID where F.NUM = ?");
0153: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0154: pstmt.setInt(1, i);
0155: assertResult(String.valueOf(i), pstmt.executeQuery());
0156: }
0157: pstmt.close();
0158: }
0159:
0160: public void testGetRow() throws Exception {
0161: createTableFoo();
0162: populateTableFoo();
0163: ResultSet rset = _stmt.executeQuery("select * from foo");
0164: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0165: assertTrue(rset.next());
0166: assertEquals(i + 1, rset.getRow());
0167: assertTrue(rset.relative(0));
0168: assertEquals(i + 1, rset.getRow());
0169: }
0170: rset.close();
0171: }
0172:
0173: public void testGetRow2() throws Exception {
0174: createTableFoo();
0175: populateTableFoo();
0176: ResultSet rset = _stmt.executeQuery("select * from foo");
0177: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0178: assertTrue(rset.relative(1));
0179: assertEquals(i + 1, rset.getRow());
0180: assertTrue(rset.relative(0));
0181: assertEquals(i + 1, rset.getRow());
0182: }
0183: rset.close();
0184: }
0185:
0186: public void testGetRow3() throws Exception {
0187: createTableFoo();
0188: populateTableFoo();
0189: ResultSet rset = _stmt.executeQuery("select * from foo");
0190: for (int i = 0; i < NUM_ROWS_IN_FOO; i += 2) {
0191: assertTrue(rset.relative(2));
0192: assertEquals(i + 2, rset.getRow());
0193: assertTrue(rset.relative(0));
0194: assertEquals(i + 2, rset.getRow());
0195: }
0196: rset.close();
0197: }
0198:
0199: public void testGetRowWithDot() throws Exception {
0200: createTableFoo();
0201: populateTableFoo();
0202: ResultSet rset = _stmt.executeQuery("select foo.* from foo");
0203: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0204: assertTrue(rset.next());
0205: assertEquals(i + 1, rset.getRow());
0206: }
0207: rset.close();
0208: }
0209:
0210: public void testLimitByMaxRows() throws Exception {
0211: createTableFoo();
0212: populateTableFoo();
0213: _stmt.setMaxRows(NUM_ROWS_IN_FOO - 1);
0214: ResultSet rset = _stmt.executeQuery("select * from foo");
0215: for (int i = 0; i < NUM_ROWS_IN_FOO - 1; i++) {
0216: assertTrue(rset.next());
0217: assertEquals(i + 1, rset.getRow());
0218: }
0219: assertTrue(!rset.next());
0220: rset.close();
0221: }
0222:
0223: public void testSingleRowButNotEqualFromIndexBug() throws Exception {
0224: createTableFoo();
0225: populateTableFoo();
0226:
0227: // select the last row from the index using a statement
0228: {
0229: Statement stmt = null;
0230: ResultSet rset = null;
0231: try {
0232: stmt = _conn.createStatement();
0233: rset = stmt
0234: .executeQuery("select NUM, STR from FOO where NUM > "
0235: + (NUM_ROWS_IN_FOO - 2));
0236: while (rset.next()) {
0237: int num = rset.getInt(1);
0238: String str = rset.getString(2);
0239: assertEquals(String.valueOf(num), str);
0240: }
0241: } finally {
0242: try {
0243: rset.close();
0244: } catch (Exception t) {
0245: }
0246: try {
0247: stmt.close();
0248: } catch (Exception t) {
0249: }
0250: }
0251: }
0252: // select the last row from the index using a prepared statement
0253: {
0254: PreparedStatement stmt = null;
0255: ResultSet rset = null;
0256: try {
0257: stmt = _conn
0258: .prepareStatement("select NUM, STR from FOO where NUM > ?");
0259: for (int i = 0; i < 2; i++) {
0260: stmt.clearParameters();
0261: stmt.setInt(1, NUM_ROWS_IN_FOO - 2);
0262: rset = stmt.executeQuery();
0263: while (rset.next()) {
0264: int num = rset.getInt(1);
0265: String str = rset.getString(2);
0266: assertEquals(String.valueOf(num), str);
0267: }
0268: rset.close();
0269: }
0270: } finally {
0271: try {
0272: rset.close();
0273: } catch (Exception t) {
0274: }
0275: try {
0276: stmt.close();
0277: } catch (Exception t) {
0278: }
0279: }
0280: }
0281: }
0282:
0283: public void testSelectWithAutocommitTurnedOff() throws Exception {
0284: createTableFoo();
0285: populateTableFoo();
0286:
0287: _conn.setAutoCommit(false);
0288: {
0289: String sql = "select STR from FOO";
0290: _rset = _stmt.executeQuery(sql);
0291: assertNotNull("Should have been able to create ResultSet",
0292: _rset);
0293:
0294: // can't assume the order in which rows will be returned
0295: // so populate a set and compare 'em
0296: Bag expected = new HashBag();
0297: Bag found = new HashBag();
0298:
0299: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0300: assertTrue("ResultSet should contain more rows [" + i
0301: + "]", _rset.next());
0302: expected.add(String.valueOf(i));
0303: String val = _rset.getString(1);
0304: assertNotNull("Returned String should not be null", val);
0305: assertTrue("ResultSet shouldn't think value was null",
0306: !_rset.wasNull());
0307: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0308: !found.contains(val));
0309: found.add(val);
0310: }
0311: assertTrue("ResultSet shouldn't have any more rows", !_rset
0312: .next());
0313: _rset.close();
0314: assertEquals(expected, found);
0315: }
0316: _conn.commit();
0317: {
0318: String sql = "select STR from FOO";
0319: _rset = _stmt.executeQuery(sql);
0320: assertNotNull("Should have been able to create ResultSet",
0321: _rset);
0322:
0323: // can't assume the order in which rows will be returned
0324: // so populate a set and compare 'em
0325: Bag expected = new HashBag();
0326: Bag found = new HashBag();
0327:
0328: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0329: assertTrue("ResultSet should contain more rows [" + i
0330: + "]", _rset.next());
0331: expected.add(String.valueOf(i));
0332: String val = _rset.getString(1);
0333: assertNotNull("Returned String should not be null", val);
0334: assertTrue("ResultSet shouldn't think value was null",
0335: !_rset.wasNull());
0336: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0337: !found.contains(val));
0338: found.add(val);
0339: }
0340: assertTrue("ResultSet shouldn't have any more rows", !_rset
0341: .next());
0342: _rset.close();
0343: assertEquals(expected, found);
0344: }
0345: }
0346:
0347: public void test_select_str_from_foo() throws Exception {
0348: createTableFoo();
0349: populateTableFoo();
0350: String sql = "select STR from FOO";
0351: _rset = _stmt.executeQuery(sql);
0352: assertNotNull("Should have been able to create ResultSet",
0353: _rset);
0354:
0355: // can't assume the order in which rows will be returned
0356: // so populate a set and compare 'em
0357: Bag expected = new HashBag();
0358: Bag found = new HashBag();
0359:
0360: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0361: assertTrue(
0362: "ResultSet should contain more rows [" + i + "]",
0363: _rset.next());
0364: expected.add(String.valueOf(i));
0365: String val = _rset.getString(1);
0366: assertNotNull("Returned String should not be null", val);
0367: assertTrue("ResultSet shouldn't think value was null",
0368: !_rset.wasNull());
0369: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0370: !found.contains(val));
0371: found.add(val);
0372: }
0373: assertTrue("ResultSet shouldn't have any more rows", !_rset
0374: .next());
0375: _rset.close();
0376: assertEquals(expected, found);
0377: }
0378:
0379: public void test_select_str_from_foo_semicolon() throws Exception {
0380: createTableFoo();
0381: populateTableFoo();
0382:
0383: String sql = "select STR from FOO;";
0384: _rset = _stmt.executeQuery(sql);
0385: assertNotNull("Should have been able to create ResultSet",
0386: _rset);
0387:
0388: // can't assume the order in which rows will be returned
0389: // so populate a set and compare 'em
0390: Bag expected = new HashBag();
0391: Bag found = new HashBag();
0392:
0393: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0394: assertTrue("ResultSet should contain more rows", _rset
0395: .next());
0396: expected.add(String.valueOf(i));
0397: String val = _rset.getString(1);
0398: assertNotNull("Returned String should not be null", val);
0399: assertTrue("ResultSet shouldn't think value was null",
0400: !_rset.wasNull());
0401: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0402: !found.contains(val));
0403: found.add(val);
0404: }
0405: assertTrue("ResultSet shouldn't have any more rows", !_rset
0406: .next());
0407: _rset.close();
0408: assertEquals(expected, found);
0409: }
0410:
0411: public void test_select_distinct() throws Exception {
0412: createTableFoo();
0413: populateTableFoo();
0414:
0415: PreparedStatement pstmt = _conn
0416: .prepareStatement("select distinct NUMTWO from FOO where NUM >= ?");
0417: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0418: pstmt.setInt(1, i);
0419: Bag expected = new HashBag();
0420: Bag found = new HashBag();
0421: _rset = pstmt.executeQuery();
0422: assertNotNull("Should have been able to create ResultSet",
0423: _rset);
0424: for (int j = (i / 2); j < (NUM_ROWS_IN_FOO / 2); j++) {
0425: expected.add(String.valueOf(j));
0426: assertTrue("ResultSet should have more rows", _rset
0427: .next());
0428: String val = _rset.getString(1);
0429: assertNotNull("Returned String should not be null", val);
0430: assertTrue("ResultSet shouldn't think value was null",
0431: !_rset.wasNull());
0432: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0433: !found.contains(val));
0434: found.add(val);
0435: }
0436: assertTrue("ResultSet shouldn't have any more rows ",
0437: !_rset.next());
0438: _rset.close();
0439: assertEquals(expected, found);
0440: }
0441: pstmt.close();
0442: }
0443:
0444: public void testSelectDuplicateRows() throws Exception {
0445: createTableFoo(false);
0446: populateTableFoo();
0447: populateTableFoo(); // create duplicate set of rows
0448:
0449: String sql = "select all NUM, STR from FOO where NUM > 0";
0450: _rset = _stmt.executeQuery(sql);
0451: assertNotNull("Should have been able to create ResultSet",
0452: _rset);
0453:
0454: // can't assume the order in which rows will be returned
0455: // so populate a set and compare 'em
0456: Bag expected = new HashBag();
0457: Bag found = new HashBag();
0458:
0459: for (int k = 0; k < 2; k++) {
0460: for (int i = 1; i < NUM_ROWS_IN_FOO; i++) {
0461: assertTrue("ResultSet should contain more rows", _rset
0462: .next());
0463: expected.add(new Integer(i));
0464: int val = _rset.getInt(1);
0465: assertTrue("ResultSet shouldn't think value was null",
0466: !_rset.wasNull());
0467: found.add(new Integer(val));
0468: }
0469: }
0470: assertTrue("ResultSet shouldn't have any more rows", !_rset
0471: .next());
0472: _rset.close();
0473: assertEquals(expected, found);
0474: }
0475:
0476: public void testSelectDuplicateRows2() throws Exception {
0477: createTableFoo(false);
0478: populateTableFoo();
0479: populateTableFoo(); // create duplicate set of rows
0480:
0481: String sql = "select NUM, STR from FOO where NUM > 0";
0482: _rset = _stmt.executeQuery(sql);
0483: assertNotNull("Should have been able to create ResultSet",
0484: _rset);
0485:
0486: // can't assume the order in which rows will be returned
0487: // so populate a set and compare 'em
0488: Bag expected = new HashBag();
0489: Bag found = new HashBag();
0490:
0491: for (int k = 0; k < 2; k++) {
0492: for (int i = 1; i < NUM_ROWS_IN_FOO; i++) {
0493: assertTrue("ResultSet should contain more rows", _rset
0494: .next());
0495: expected.add(new Integer(i));
0496: int val = _rset.getInt(1);
0497: assertTrue("ResultSet shouldn't think value was null",
0498: !_rset.wasNull());
0499: found.add(new Integer(val));
0500: }
0501: }
0502: assertTrue("ResultSet shouldn't have any more rows", !_rset
0503: .next());
0504: _rset.close();
0505: assertEquals(expected, found);
0506: }
0507:
0508: public void test_select_literal_from_foo() throws Exception {
0509: createTableFoo();
0510: populateTableFoo();
0511:
0512: String sql = "select 'Literal' from FOO";
0513: _rset = _stmt.executeQuery(sql);
0514: assertNotNull("Should have been able to create ResultSet",
0515: _rset);
0516:
0517: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0518: assertTrue("ResultSet should contain more rows", _rset
0519: .next());
0520: String val = _rset.getString(1);
0521: assertNotNull("Returned String should not be null", val);
0522: assertTrue("ResultSet shouldn't think value was null",
0523: !_rset.wasNull());
0524: assertEquals("Returned string should equal \"Literal\".",
0525: "Literal", val);
0526: }
0527: assertTrue("ResultSet shouldn't have any more rows", !_rset
0528: .next());
0529: _rset.close();
0530: }
0531:
0532: public void test_select_str_num_from_foo() throws Exception {
0533: createTableFoo();
0534: populateTableFoo();
0535:
0536: String sql = "select STR, NUM, NUMTWO from FOO";
0537: _rset = _stmt.executeQuery(sql);
0538: assertNotNull("Should have been able to create ResultSet",
0539: _rset);
0540:
0541: // can't assume the order in which rows will be returned
0542: // so populate a bag and compare 'em
0543:
0544: Bag expectedStr = new HashBag();
0545: Bag foundStr = new HashBag();
0546: Bag expectedNum = new HashBag();
0547: Bag foundNum = new HashBag();
0548: Bag expectedNumtwo = new HashBag();
0549: Bag foundNumtwo = new HashBag();
0550:
0551: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0552: expectedNum.add(new Integer(i));
0553: expectedNumtwo.add(new Integer(i / 2));
0554: expectedStr.add(String.valueOf(i));
0555:
0556: assertTrue("ResultSet should contain more rows", _rset
0557: .next());
0558: String strVal = _rset.getString(1);
0559: assertNotNull("Returned String should not be null", strVal);
0560: assertTrue("ResultSet shouldn't think value was null",
0561: !_rset.wasNull());
0562: assertTrue("Shouldn't have seen \"" + strVal + "\" yet",
0563: !foundStr.contains(strVal));
0564: foundStr.add(strVal);
0565:
0566: int intVal = _rset.getInt(2);
0567: assertTrue("ResultSet shouldn't think value was null",
0568: !_rset.wasNull());
0569: assertTrue("Shouldn't have seen \"" + intVal + "\" yet",
0570: !foundNum.contains(new Integer(intVal)));
0571: foundNum.add(new Integer(intVal));
0572:
0573: int intVal2 = _rset.getInt(3);
0574: assertTrue("ResultSet shouldn't think value was null",
0575: !_rset.wasNull());
0576: foundNumtwo.add(new Integer(intVal2));
0577:
0578: }
0579: assertTrue("ResultSet shouldn't have any more rows", !_rset
0580: .next());
0581: _rset.close();
0582: assertEquals(expectedStr, foundStr);
0583: assertEquals(expectedNum, foundNum);
0584: assertEquals(expectedNumtwo, foundNumtwo);
0585: }
0586:
0587: public void test_select_str_from_foo_where_num_eq_literal()
0588: throws Exception {
0589: createTableFoo();
0590: populateTableFoo();
0591:
0592: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0593: String sql = "select STR from FOO where NUM = " + i;
0594: _rset = _stmt.executeQuery(sql);
0595: assertNotNull("Should have been able to create ResultSet",
0596: _rset);
0597: assertTrue("ResultSet should not be empty", _rset.next());
0598: assertEquals(String.valueOf(i), _rset.getString(1));
0599: assertTrue(!_rset.wasNull());
0600: assertEquals(String.valueOf(i), _rset.getString("STR"));
0601: assertTrue(!_rset.wasNull());
0602: assertEquals(String.valueOf(i), _rset.getString("str"));
0603: assertTrue(!_rset.wasNull());
0604: assertTrue("ResultSet shouldn't have any more rows", !_rset
0605: .next());
0606: _rset.close();
0607: }
0608: }
0609:
0610: public void test_select_str_num_from_foo_where_num_eq_literal()
0611: throws Exception {
0612: createTableFoo();
0613: populateTableFoo();
0614:
0615: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0616: String sql = "select STR, NUM from FOO where NUM = " + i;
0617: _rset = _stmt.executeQuery(sql);
0618: assertNotNull("Should have been able to create ResultSet",
0619: _rset);
0620: assertTrue("ResultSet should not be empty", _rset.next());
0621: assertEquals(String.valueOf(i), _rset.getString(1));
0622: assertTrue(!_rset.wasNull());
0623: assertEquals(String.valueOf(i), _rset.getString("STR"));
0624: assertTrue(!_rset.wasNull());
0625: assertEquals(String.valueOf(i), _rset.getString("str"));
0626: assertTrue(!_rset.wasNull());
0627: assertEquals(i, _rset.getInt(2));
0628: assertTrue(!_rset.wasNull());
0629: assertEquals(i, _rset.getInt("NUM"));
0630: assertTrue(!_rset.wasNull());
0631: assertEquals(i, _rset.getInt("num"));
0632: assertTrue(!_rset.wasNull());
0633: assertTrue("ResultSet shouldn't have any more rows", !_rset
0634: .next());
0635: _rset.close();
0636: }
0637: }
0638:
0639: public void test_select_foostr_foonum_from_foo_where_foonum_eq_literal()
0640: throws Exception {
0641: createTableFoo();
0642: populateTableFoo();
0643:
0644: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0645: String sql = "select FOO.STR, FOO.NUM from FOO where FOO.NUM = "
0646: + i;
0647: _rset = _stmt.executeQuery(sql);
0648: assertNotNull("Should have been able to create ResultSet",
0649: _rset);
0650: assertTrue("ResultSet should not be empty", _rset.next());
0651: assertEquals(String.valueOf(i), _rset.getString(1));
0652: assertTrue(!_rset.wasNull());
0653: assertEquals(String.valueOf(i), _rset.getString("STR"));
0654: assertTrue(!_rset.wasNull());
0655: assertEquals(String.valueOf(i), _rset.getString("str"));
0656: assertTrue(!_rset.wasNull());
0657: assertEquals(i, _rset.getInt(2));
0658: assertTrue(!_rset.wasNull());
0659: assertEquals(i, _rset.getInt("NUM"));
0660: assertTrue(!_rset.wasNull());
0661: assertEquals(i, _rset.getInt("num"));
0662: assertTrue(!_rset.wasNull());
0663: assertTrue("ResultSet shouldn't have any more rows", !_rset
0664: .next());
0665: _rset.close();
0666: }
0667: }
0668:
0669: public void test_select_str_from_foo_where_num_lt_literal()
0670: throws Exception {
0671: createTableFoo();
0672: populateTableFoo();
0673:
0674: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0675: String sql = "select STR from FOO where NUM < " + i;
0676: Bag expected = new HashBag();
0677: Bag found = new HashBag();
0678: _rset = _stmt.executeQuery(sql);
0679: assertNotNull("Should have been able to create ResultSet",
0680: _rset);
0681: for (int j = 0; j < i; j++) {
0682: expected.add(String.valueOf(j));
0683: assertTrue("ResultSet should not be empty", _rset
0684: .next());
0685: String val = _rset.getString(1);
0686: assertNotNull("Returned String should not be null", val);
0687: assertTrue("ResultSet shouldn't think value was null",
0688: !_rset.wasNull());
0689: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0690: !found.contains(val));
0691: found.add(val);
0692: }
0693: assertTrue("ResultSet shouldn't have any more rows", !_rset
0694: .next());
0695: _rset.close();
0696: assertEquals(expected, found);
0697: }
0698: }
0699:
0700: public void test_select_str_from_foo_where_str_gt_literal()
0701: throws Exception {
0702: createTableFoo();
0703: populateTableFoo();
0704:
0705: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0706: String sql = "select STR from FOO where STR >= '" + i + "'";
0707: Bag expected = new HashBag();
0708: Bag found = new HashBag();
0709: _rset = _stmt.executeQuery(sql);
0710: assertNotNull("Should have been able to create ResultSet",
0711: _rset);
0712: for (int j = NUM_ROWS_IN_FOO - 1; j >= i; j--) {
0713: expected.add(String.valueOf(j));
0714: assertTrue("ResultSet should not be empty", _rset
0715: .next());
0716: String val = _rset.getString(1);
0717: assertNotNull("Returned String should not be null", val);
0718: assertTrue("ResultSet shouldn't think value was null",
0719: !_rset.wasNull());
0720: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0721: !found.contains(val));
0722: found.add(val);
0723: }
0724: assertTrue("ResultSet shouldn't have any more rows", !_rset
0725: .next());
0726: _rset.close();
0727: assertEquals(expected, found);
0728: }
0729: }
0730:
0731: public void test_select_str_from_foo_where_num_gteq_literal()
0732: throws Exception {
0733: createTableFoo();
0734: populateTableFoo();
0735:
0736: PreparedStatement pstmt = _conn
0737: .prepareStatement("select STR from FOO where NUM >= ?");
0738: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0739: pstmt.setInt(1, i);
0740: Bag expected = new HashBag();
0741: Bag found = new HashBag();
0742: _rset = pstmt.executeQuery();
0743: assertNotNull("Should have been able to create ResultSet",
0744: _rset);
0745: for (int j = NUM_ROWS_IN_FOO - 1; j >= i; j--) {
0746: expected.add(String.valueOf(j));
0747: assertTrue("ResultSet should not be empty", _rset
0748: .next());
0749: String val = _rset.getString(1);
0750: assertNotNull("Returned String should not be null", val);
0751: assertTrue("ResultSet shouldn't think value was null",
0752: !_rset.wasNull());
0753: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0754: !found.contains(val));
0755: found.add(val);
0756: }
0757: assertTrue("ResultSet shouldn't have any more rows", !_rset
0758: .next());
0759: _rset.close();
0760: assertEquals(expected, found);
0761: }
0762: pstmt.close();
0763: }
0764:
0765: public void test_select_str_from_foo_where_literal_lt_num()
0766: throws Exception {
0767: createTableFoo();
0768: populateTableFoo();
0769:
0770: for (int i = 0; i < 5; i++) {
0771: String sql = "select STR from FOO where " + i + " < NUM";
0772: Bag expected = new HashBag();
0773: Bag found = new HashBag();
0774: _rset = _stmt.executeQuery(sql);
0775: assertNotNull("Should have been able to create ResultSet",
0776: _rset);
0777: for (int j = NUM_ROWS_IN_FOO - 1; j > i; j--) {
0778: expected.add(String.valueOf(j));
0779: assertTrue("ResultSet should contain more rows", _rset
0780: .next());
0781: String val = _rset.getString(1);
0782: assertNotNull("Returned String should not be null", val);
0783: assertTrue("ResultSet shouldn't think value was null",
0784: !_rset.wasNull());
0785: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0786: !found.contains(val));
0787: found.add(val);
0788: }
0789: assertTrue("ResultSet shouldn't have any more rows", !_rset
0790: .next());
0791: _rset.close();
0792: assertEquals(expected, found);
0793: }
0794: }
0795:
0796: public void test_select_str_from_foo_where_num_eq_literal_or_num_gt_literal()
0797: throws Exception {
0798: createTableFoo();
0799: populateTableFoo();
0800:
0801: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0802: String sql = "select STR from FOO where NUM = " + i
0803: + " or NUM > " + i;
0804: Bag expected = new HashBag();
0805: Bag found = new HashBag();
0806: _rset = _stmt.executeQuery(sql);
0807: assertNotNull("Should have been able to create ResultSet",
0808: _rset);
0809: for (int j = NUM_ROWS_IN_FOO - 1; j >= i; j--) {
0810: expected.add(String.valueOf(j));
0811: assertTrue("ResultSet should not be empty", _rset
0812: .next());
0813: String val = _rset.getString(1);
0814: assertNotNull("Returned String should not be null", val);
0815: assertTrue("ResultSet shouldn't think value was null",
0816: !_rset.wasNull());
0817: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0818: !found.contains(val));
0819: found.add(val);
0820: }
0821: assertTrue("ResultSet shouldn't have any more rows", !_rset
0822: .next());
0823: _rset.close();
0824: assertEquals(expected, found);
0825: }
0826: }
0827:
0828: public void test_select_str_from_foo_where_num_bewtween_literal_and_literal()
0829: throws Exception {
0830: createTableFoo();
0831: populateTableFoo();
0832:
0833: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0834: String sql = "select STR from FOO where NUM between " + i
0835: + " and " + (i + 2);
0836: Bag expected = new HashBag();
0837: Bag found = new HashBag();
0838: _rset = _stmt.executeQuery(sql);
0839: assertNotNull("Should have been able to create ResultSet",
0840: _rset);
0841: for (int j = i; j <= i + 2 && j < NUM_ROWS_IN_FOO; j++) {
0842: expected.add(String.valueOf(j));
0843: assertTrue("ResultSet should have more rows (i=" + i
0844: + ",j=" + j + ")", _rset.next());
0845: String val = _rset.getString(1);
0846: assertNotNull("Returned String should not be null", val);
0847: assertTrue("ResultSet shouldn't think value was null",
0848: !_rset.wasNull());
0849: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0850: !found.contains(val));
0851: found.add(val);
0852: }
0853: assertTrue("ResultSet shouldn't have any more rows", !_rset
0854: .next());
0855: _rset.close();
0856: assertEquals(expected, found);
0857: }
0858: }
0859:
0860: public void test_select_with_bindvar_limit() throws Exception {
0861: createTableFoo();
0862: populateTableFoo();
0863:
0864: PreparedStatement pstmt = _conn
0865: .prepareStatement("select STR from FOO limit ?");
0866: for (int i = 0; i < NUM_ROWS_IN_FOO + 1; i++) {
0867: pstmt.setInt(1, i);
0868: _rset = pstmt.executeQuery();
0869: assertNotNull("Should have been able to create ResultSet",
0870: _rset);
0871: Bag expected = new HashBag();
0872: Bag found = new HashBag();
0873: for (int j = 0; j < i && j < NUM_ROWS_IN_FOO; j++) {
0874: expected.add(String.valueOf(j));
0875: assertTrue("ResultSet should have more rows", _rset
0876: .next());
0877: String val = _rset.getString(1);
0878: assertNotNull("Returned String should not be null", val);
0879: assertTrue("ResultSet shouldn't think value was null",
0880: !_rset.wasNull());
0881: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0882: !found.contains(val));
0883: found.add(val);
0884: }
0885: assertTrue("ResultSet shouldn't have any more rows (i=" + i
0886: + ")", !_rset.next());
0887: _rset.close();
0888: assertEquals(expected, found);
0889: pstmt.clearParameters();
0890: }
0891: pstmt.close();
0892: }
0893:
0894: public void test_select_with_literal_limit() throws Exception {
0895: createTableFoo();
0896: populateTableFoo();
0897:
0898: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0899: String sql = "select STR from FOO where NUM >= " + i
0900: + " limit 3";
0901: Bag expected = new HashBag();
0902: Bag found = new HashBag();
0903: _rset = _stmt.executeQuery(sql);
0904: assertNotNull("Should have been able to create ResultSet",
0905: _rset);
0906: for (int j = i; j < i + 3 && j < NUM_ROWS_IN_FOO; j++) {
0907: expected.add(String.valueOf(j));
0908: assertTrue("ResultSet should have more rows (i=" + i
0909: + ",j=" + j + ")", _rset.next());
0910: String val = _rset.getString(1);
0911: assertNotNull("Returned String should not be null", val);
0912: assertTrue("ResultSet shouldn't think value was null",
0913: !_rset.wasNull());
0914: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0915: !found.contains(val));
0916: found.add(val);
0917: }
0918: assertTrue("ResultSet shouldn't have any more rows (i=" + i
0919: + ")", !_rset.next());
0920: _rset.close();
0921: assertEquals(expected, found);
0922: }
0923: }
0924:
0925: public void test_select_with_bindvar_offset() throws Exception {
0926: createTableFoo();
0927: populateTableFoo();
0928:
0929: PreparedStatement pstmt = _conn
0930: .prepareStatement("select STR from FOO offset ?");
0931: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0932: pstmt.setInt(1, i);
0933: Bag expected = new HashBag();
0934: Bag found = new HashBag();
0935: _rset = pstmt.executeQuery();
0936: assertNotNull("Should have been able to create ResultSet",
0937: _rset);
0938: for (int j = i; j < NUM_ROWS_IN_FOO; j++) {
0939: expected.add(String.valueOf(j));
0940: assertTrue("ResultSet should have more rows", _rset
0941: .next());
0942: String val = _rset.getString(1);
0943: assertNotNull("Returned String should not be null", val);
0944: assertTrue("ResultSet shouldn't think value was null",
0945: !_rset.wasNull());
0946: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0947: !found.contains(val));
0948: found.add(val);
0949: }
0950: assertTrue("ResultSet shouldn't have any more rows (i=" + i
0951: + ")", !_rset.next());
0952: _rset.close();
0953: assertEquals(expected, found);
0954: pstmt.clearParameters();
0955: }
0956: pstmt.close();
0957: }
0958:
0959: public void test_select_with_literal_offset() throws Exception {
0960: createTableFoo();
0961: populateTableFoo();
0962:
0963: PreparedStatement pstmt = _conn
0964: .prepareStatement("select STR from FOO where NUM >= ? offset 3");
0965: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0966: pstmt.setInt(1, i);
0967: Bag expected = new HashBag();
0968: Bag found = new HashBag();
0969: _rset = pstmt.executeQuery();
0970: assertNotNull("Should have been able to create ResultSet",
0971: _rset);
0972: for (int j = i + 3; j < NUM_ROWS_IN_FOO; j++) {
0973: expected.add(String.valueOf(j));
0974: assertTrue("ResultSet should have more rows (i=" + i
0975: + ",j=" + j + ")", _rset.next());
0976: String val = _rset.getString(1);
0977: assertNotNull("Returned String should not be null", val);
0978: assertTrue("ResultSet shouldn't think value was null",
0979: !_rset.wasNull());
0980: assertTrue("Shouldn't have seen \"" + val + "\" yet",
0981: !found.contains(val));
0982: found.add(val);
0983: }
0984: assertTrue("ResultSet shouldn't have any more rows (i=" + i
0985: + ")", !_rset.next());
0986: _rset.close();
0987: assertEquals(expected, found);
0988: }
0989: pstmt.close();
0990: }
0991:
0992: public void test_select_order_by_offset_limit() throws Exception {
0993: createTableFoo();
0994: populateTableFoo();
0995:
0996: int offset = 1;
0997: int limit = 3;
0998: PreparedStatement pstmt = _conn
0999: .prepareStatement("select NUM from FOO order by NUM desc limit "
1000: + limit + " offset " + offset);
1001: ResultSet rset = pstmt.executeQuery();
1002: for (int i = 0; i < limit; i++) {
1003: assertTrue(rset.next());
1004: assertEquals(NUM_ROWS_IN_FOO - offset - i - 1, rset
1005: .getInt(1));
1006: }
1007: assertTrue(!rset.next());
1008: rset.close();
1009: pstmt.close();
1010: }
1011:
1012: public void test_select_foo_dot_asterisk_from_foo_bar_where_num_gt_literal_and_id_eq_num()
1013: throws Exception {
1014: createTableFoo();
1015: populateTableFoo();
1016: createTableBar();
1017: populateTableBar();
1018:
1019: PreparedStatement pstmt = _conn
1020: .prepareStatement("select A.* from FOO A, BAR B where A.NUM >= ? and B.ID = A.NUM");
1021: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
1022: pstmt.setInt(1, i);
1023: _rset = pstmt.executeQuery();
1024: assertNotNull("Should have been able to create ResultSet",
1025: _rset);
1026: for (int j = NUM_ROWS_IN_FOO - 1; j >= i; j--) {
1027: int numval = 0;
1028: int num2val = 0;
1029: String strval = null;
1030: assertTrue("ResultSet should not be empty", _rset
1031: .next());
1032: {
1033: numval = _rset.getInt(1);
1034: assertTrue(
1035: "ResultSet shouldn't think value was null",
1036: !_rset.wasNull());
1037: }
1038: {
1039: strval = _rset.getString(2);
1040: assertNotNull("Returned String should not be null",
1041: strval);
1042: assertTrue(
1043: "ResultSet shouldn't think value was null",
1044: !_rset.wasNull());
1045: }
1046: {
1047: num2val = _rset.getInt(3);
1048: assertTrue(
1049: "ResultSet shouldn't think value was null",
1050: !_rset.wasNull());
1051: }
1052: assertEquals(numval, Integer.parseInt(strval));
1053: assertEquals(num2val, Integer.parseInt(strval) / 2);
1054: }
1055: assertTrue("ResultSet shouldn't have any more rows", !_rset
1056: .next());
1057: _rset.close();
1058: }
1059: pstmt.close();
1060: }
1061:
1062: public void test_select_str_descr_from_foo_bar_where_num_gt_literal_and_id_eq_num()
1063: throws Exception {
1064: createTableFoo();
1065: populateTableFoo();
1066: createTableBar();
1067: populateTableBar();
1068:
1069: PreparedStatement pstmt = _conn
1070: .prepareStatement("select STR, DESCR from FOO, BAR where NUM >= ? and ID = NUM");
1071: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
1072: Bag expected = new HashBag();
1073: Bag foundStr = new HashBag();
1074: Bag foundDescr = new HashBag();
1075: pstmt.setInt(1, i);
1076: _rset = pstmt.executeQuery();
1077: assertNotNull("Should have been able to create ResultSet",
1078: _rset);
1079: for (int j = NUM_ROWS_IN_FOO - 1; j >= i; j--) {
1080: expected.add(String.valueOf(j));
1081: assertTrue("ResultSet should not be empty", _rset
1082: .next());
1083: {
1084: String strval = _rset.getString(1);
1085: assertNotNull("Returned String should not be null",
1086: strval);
1087: assertTrue(
1088: "ResultSet shouldn't think value was null",
1089: !_rset.wasNull());
1090: assertTrue("Shouldn't have seen \"" + strval
1091: + "\" yet", !foundStr.contains(strval));
1092: foundStr.add(strval);
1093: }
1094: {
1095: String descrval = _rset.getString(2);
1096: assertNotNull("Returned String should not be null",
1097: descrval);
1098: assertTrue(
1099: "ResultSet shouldn't think value was null",
1100: !_rset.wasNull());
1101: assertTrue("Shouldn't have seen \"" + descrval
1102: + "\" yet", !foundDescr.contains(descrval));
1103: foundDescr.add(descrval);
1104: }
1105: assertEquals(foundDescr, foundStr);
1106: }
1107: assertTrue("ResultSet shouldn't have any more rows", !_rset
1108: .next());
1109: _rset.close();
1110: assertEquals(expected, foundStr);
1111: assertEquals(expected, foundDescr);
1112: }
1113: pstmt.close();
1114: }
1115:
1116: public void test_select_str_descr_from_foo_bar_where_id_eq_num_and_num_lt_literal_join_w_bind()
1117: throws Exception {
1118: createTableFoo();
1119: populateTableFoo();
1120: createTableBar();
1121: populateTableBar();
1122:
1123: String sql = "select foo.STR from FOO, BAR barro where barro.ID = NUM and STR = ?";
1124: PreparedStatement stmt = _conn.prepareStatement(sql);
1125: stmt.setString(1, "bogus");
1126: _rset = stmt.executeQuery();
1127: assertNotNull("Should have been able to create ResultSet",
1128: _rset);
1129: assertTrue("Should not have any rows", !_rset.next());
1130:
1131: stmt.close();
1132: }
1133:
1134: public void testPreparedStatement() throws Exception {
1135: createTableFoo();
1136: populateTableFoo();
1137:
1138: String sql = "select STR from FOO where NUM = ?";
1139: PreparedStatement stmt = _conn.prepareStatement(sql);
1140:
1141: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
1142: stmt.clearParameters();
1143: stmt.setInt(1, i);
1144: _rset = stmt.executeQuery();
1145: assertNotNull("Should have been able to create ResultSet",
1146: _rset);
1147: assertTrue("ResultSet should not be empty", _rset.next());
1148: assertEquals(String.valueOf(i), _rset.getString(1));
1149: assertTrue(!_rset.wasNull());
1150: assertEquals(String.valueOf(i), _rset.getString("STR"));
1151: assertTrue(!_rset.wasNull());
1152: assertEquals(String.valueOf(i), _rset.getString("str"));
1153: assertTrue(!_rset.wasNull());
1154: assertTrue("ResultSet shouldn't have any more rows", !_rset
1155: .next());
1156: _rset.close();
1157: }
1158: try {
1159: stmt.close();
1160: } catch (Exception t) {
1161: }
1162: }
1163:
1164: public void test_select_char_of_num_from_foo() throws Exception {
1165: createTableFoo();
1166: populateTableFoo();
1167:
1168: String sql = "select NUM, CHR(NUM) from FOO";
1169: _rset = _stmt.executeQuery(sql);
1170: assertNotNull("Should have been able to create ResultSet",
1171: _rset);
1172:
1173: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
1174: assertTrue("ResultSet should contain more rows", _rset
1175: .next());
1176: int num = _rset.getInt(1);
1177: String str = _rset.getString(2);
1178: assertEquals((char) num, str.charAt(0));
1179: }
1180: assertTrue("ResultSet shouldn't have any more rows", !_rset
1181: .next());
1182: _rset.close();
1183: }
1184:
1185: public void test_select_upper_of_descr2_from_bar() throws Exception {
1186: createTableBar();
1187: populateTableBar();
1188:
1189: String sql = "select DESCR2, UPPER(DESCR2) from BAR";
1190: _rset = _stmt.executeQuery(sql);
1191: assertNotNull("Should have been able to create ResultSet",
1192: _rset);
1193:
1194: for (int i = 0; i < NUM_ROWS_IN_BAR; i++) {
1195: assertTrue("ResultSet should contain more rows", _rset
1196: .next());
1197: String plainstr = _rset.getString(1);
1198: String upperstr = _rset.getString(2);
1199: assertTrue(!plainstr.equals(upperstr));
1200: assertEquals(plainstr.toUpperCase(), upperstr);
1201: }
1202: assertTrue("ResultSet shouldn't have any more rows", !_rset
1203: .next());
1204: _rset.close();
1205: }
1206:
1207: public void test_select_lower_of_descr2_from_bar() throws Exception {
1208: createTableBar();
1209: populateTableBar();
1210:
1211: String sql = "select DESCR2, LOWER(DESCR2) from BAR";
1212: _rset = _stmt.executeQuery(sql);
1213: assertNotNull("Should have been able to create ResultSet",
1214: _rset);
1215:
1216: for (int i = 0; i < NUM_ROWS_IN_BAR; i++) {
1217: assertTrue("ResultSet should contain more rows", _rset
1218: .next());
1219: String plainstr = _rset.getString(1);
1220: String lowerstr = _rset.getString(2);
1221: assertTrue(!plainstr.equals(lowerstr));
1222: assertEquals(plainstr.toLowerCase(), lowerstr);
1223: }
1224: assertTrue("ResultSet shouldn't have any more rows", !_rset
1225: .next());
1226: _rset.close();
1227: }
1228:
1229: public void test_select_concat_literal_descr2_from_bar()
1230: throws Exception {
1231: createTableBar();
1232: populateTableBar();
1233:
1234: String sql = "select DESCR2, CONCAT('TEST', DESCR2) from BAR";
1235: _rset = _stmt.executeQuery(sql);
1236: assertNotNull("Should have been able to create ResultSet",
1237: _rset);
1238:
1239: for (int i = 0; i < NUM_ROWS_IN_BAR; i++) {
1240: assertTrue("ResultSet should contain more rows", _rset
1241: .next());
1242: String plainstr = _rset.getString(1);
1243: String concat = _rset.getString(2);
1244: assertTrue(!plainstr.equals(concat));
1245: assertEquals("TEST" + plainstr, concat);
1246: }
1247: assertTrue("ResultSet shouldn't have any more rows", !_rset
1248: .next());
1249: _rset.close();
1250: }
1251:
1252: public void testNullPlusNonNullIsNull() throws Exception {
1253: createTableBar();
1254: populateTableBar();
1255:
1256: String sql = "select DESCR2, CONCAT(NULL, DESCR2) from BAR";
1257: _rset = _stmt.executeQuery(sql);
1258: assertNotNull("Should have been able to create ResultSet",
1259: _rset);
1260:
1261: for (int i = 0; i < NUM_ROWS_IN_BAR; i++) {
1262: assertTrue("ResultSet should contain more rows", _rset
1263: .next());
1264: String concat = _rset.getString(2);
1265: assertTrue(_rset.wasNull());
1266: assertNull(concat);
1267: }
1268: assertTrue("ResultSet shouldn't have any more rows", !_rset
1269: .next());
1270: _rset.close();
1271: }
1272:
1273: public void test_select_contains_literal_from_foo()
1274: throws Exception {
1275: createTableFoo();
1276: populateTableFoo();
1277:
1278: String sql = "SELECT contains('team', 'i'), str FROM foo";
1279: _rset = _stmt.executeQuery(sql);
1280: assertNotNull("Should have been able to create ResultSet",
1281: _rset);
1282:
1283: assertTrue("Should have a row", _rset.next());
1284: assertTrue("Should return false", !_rset.getBoolean(1));
1285: _rset.close();
1286: }
1287:
1288: public void test_select_contains_fieldval_from_foo()
1289: throws Exception {
1290: createTableFoo();
1291: populateTableFoo();
1292:
1293: String sql = "SELECT str, contains(lower(str), '0') FROM foo ORDER BY num";
1294: _rset = _stmt.executeQuery(sql);
1295: assertNotNull("Should have been able to create ResultSet",
1296: _rset);
1297:
1298: boolean[] results = new boolean[] { true, false };
1299:
1300: for (int i = 0; i < results.length; i++) {
1301: assertTrue("Should have a row", _rset.next());
1302: String str = _rset.getString(1);
1303: assertEquals("Should return expected result for contains("
1304: + str + ", '0')", results[i], _rset.getBoolean(2));
1305: }
1306: _rset.close();
1307: }
1308:
1309: public void test_select_from_foo_where_contains_fieldval_eq_true()
1310: throws Exception {
1311: createTableFoo();
1312: populateTableFoo();
1313:
1314: String sql = "SELECT str FROM foo WHERE contains(str, '0') = true";
1315: _rset = _stmt.executeQuery(sql);
1316: assertNotNull("Should have been able to create ResultSet",
1317: _rset);
1318:
1319: assertTrue("Should have a row", _rset.next());
1320: assertTrue("Should have only one row but also found "
1321: + _rset.getString(1), !_rset.next());
1322: _rset.close();
1323: }
1324:
1325: public void test_select_from_foo_where_contains_fieldval_unary()
1326: throws Exception {
1327: createTableFoo();
1328: populateTableFoo();
1329:
1330: String sql = "SELECT str FROM foo WHERE contains(str, '0')";
1331: _rset = _stmt.executeQuery(sql);
1332: assertNotNull("Should have been able to create ResultSet",
1333: _rset);
1334:
1335: assertTrue("Should have a row", _rset.next());
1336: assertTrue("Should have only one row but also found "
1337: + _rset.getString(1), !_rset.next());
1338: _rset.close();
1339: }
1340:
1341: public void test_select_state_where_matches_north()
1342: throws Exception {
1343: createTableStates();
1344: populateTableStates();
1345:
1346: String sql = "SELECT state FROM states WHERE matches(state, 'north') ORDER BY state";
1347: String[] expected = new String[] { "north carolina",
1348: "north dakota" };
1349: helpTestStringResults(sql, expected);
1350: }
1351:
1352: public void test_select_state_where_matches_caret_a()
1353: throws Exception {
1354: createTableStates();
1355: populateTableStates();
1356:
1357: String sql = "SELECT state FROM states WHERE matches(state, '^a') ORDER BY state";
1358: String[] expected = new String[] { "alabama", "alaska",
1359: "arizona", "arkansas" };
1360: helpTestStringResults(sql, expected);
1361: }
1362:
1363: public void test_select_state_where_state_like_al_percent()
1364: throws Exception {
1365: createTableStates();
1366: populateTableStates();
1367:
1368: String sql = "SELECT state FROM states WHERE state LIKE 'al%' ORDER BY state";
1369: String[] expected = new String[] { "alabama", "alaska" };
1370: helpTestStringResults(sql, expected);
1371: }
1372:
1373: public void test_select_state_where_state_like_a_percent_a()
1374: throws Exception {
1375: createTableStates();
1376: populateTableStates();
1377:
1378: String sql = "SELECT state FROM states WHERE state LIKE 'a%a' ORDER BY state";
1379: String[] expected = new String[] { "alabama", "alaska",
1380: "arizona" };
1381: helpTestStringResults(sql, expected);
1382: }
1383:
1384: public void test_select_word_where_word_like_b_t() throws Exception {
1385: createTableWords();
1386: populateTableWords();
1387:
1388: String sql = "SELECT word FROM words WHERE word LIKE 'b_t' ORDER BY word";
1389: String[] expected = new String[] { "bat", "bet", "bit", "bot",
1390: "but" };
1391: helpTestStringResults(sql, expected);
1392: }
1393:
1394: public void test_select_word_where_word_not_like_b_t()
1395: throws Exception {
1396: createTableWords();
1397: populateTableWords();
1398:
1399: String sql = "SELECT word FROM words WHERE word LIKE 'b%t' AND NOT word LIKE 'b_t' ORDER BY word";
1400: String[] expected = new String[] { "bait", "bent", "bolt",
1401: "bunt" };
1402: helpTestStringResults(sql, expected);
1403: }
1404:
1405: public void test_select_word_where_word_like_b() throws Exception {
1406: createTableWords();
1407: populateTableWords();
1408:
1409: String sql = "SELECT word FROM words WHERE word LIKE 'b' ORDER BY word";
1410: String[] expected = new String[] {};
1411: helpTestStringResults(sql, expected);
1412: }
1413:
1414: public void test_select_word_where_word_like_c_percent()
1415: throws Exception {
1416: createTableWords();
1417: populateTableWords();
1418:
1419: String sql = "SELECT word FROM words WHERE word LIKE 'c%' ORDER BY word";
1420: String[] expected = new String[] { "cat", "cot", "cut" };
1421: helpTestStringResults(sql, expected);
1422: }
1423:
1424: protected void helpTestStringResults(String sql, String[] expected)
1425: throws Exception {
1426: _rset = _stmt.executeQuery(sql);
1427: assertNotNull("Should have been able to create ResultSet",
1428: _rset);
1429: for (int i = 0; i < expected.length; i++) {
1430: assertTrue("Should have a row", _rset.next());
1431: assertEquals("Should get expected result", expected[i],
1432: _rset.getString(1));
1433: }
1434: assertTrue("Should have only " + expected.length + " rows",
1435: !_rset.next());
1436: _rset.close();
1437: }
1438:
1439: public void test_select_count_star_from_foo_where_num_lt_n()
1440: throws Exception {
1441: createTableFoo();
1442: populateTableFoo();
1443:
1444: PreparedStatement pstmt = _conn
1445: .prepareStatement("select COUNT(*) from FOO where NUM < ?");
1446: for (int i = 0; i < NUM_ROWS_IN_FOO + 1; i++) {
1447: pstmt.setInt(1, i);
1448: _rset = pstmt.executeQuery();
1449: assertNotNull("Should have been able to create ResultSet",
1450: _rset);
1451: assertTrue(_rset.next());
1452: int count = _rset.getInt(1);
1453: assertEquals(i, count);
1454: assertTrue("ResultSet shouldn't have any more rows", !_rset
1455: .next());
1456: _rset.close();
1457: }
1458: pstmt.close();
1459: }
1460:
1461: public void test_select_sum_num_from_foo() throws Exception {
1462: createTableFoo();
1463: populateTableFoo();
1464:
1465: PreparedStatement pstmt = _conn
1466: .prepareStatement("select sum(num) from foo where num <= ?");
1467: int sum = 0;
1468: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
1469: sum += i;
1470: pstmt.setInt(1, i);
1471: _rset = pstmt.executeQuery();
1472: assertNotNull("Should have been able to create ResultSet",
1473: _rset);
1474: assertTrue(_rset.next());
1475: int max = _rset.getInt(1);
1476: assertEquals(sum, max);
1477: assertTrue("ResultSet shouldn't have any more rows", !_rset
1478: .next());
1479: _rset.close();
1480: }
1481: pstmt.close();
1482: }
1483:
1484: public void test_select_sum_num_from_empty_table() throws Exception {
1485: createTableFoo();
1486: PreparedStatement pstmt = _conn
1487: .prepareStatement("select sum(num) from foo");
1488: _rset = pstmt.executeQuery();
1489: assertNotNull("Should have been able to create ResultSet",
1490: _rset);
1491: assertTrue(_rset.next());
1492: _rset.getInt(1);
1493: assertTrue(_rset.wasNull());
1494: _rset.close();
1495: pstmt.close();
1496: }
1497:
1498: public void test_select_max_num_from_foo() throws Exception {
1499: createTableFoo();
1500: populateTableFoo();
1501:
1502: PreparedStatement pstmt = _conn
1503: .prepareStatement("select MAX(NUM) from FOO where NUM <= ?");
1504: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
1505: pstmt.setInt(1, i);
1506: _rset = pstmt.executeQuery();
1507: assertNotNull("Should have been able to create ResultSet",
1508: _rset);
1509: assertTrue(_rset.next());
1510: int max = _rset.getInt(1);
1511: assertEquals(i, max);
1512: assertTrue("ResultSet shouldn't have any more rows", !_rset
1513: .next());
1514: _rset.close();
1515: }
1516: pstmt.close();
1517: }
1518:
1519: public void test_select_max_num_from_empty_table() throws Exception {
1520: createTableFoo();
1521: PreparedStatement pstmt = _conn
1522: .prepareStatement("select MAX(NUM) from FOO");
1523: _rset = pstmt.executeQuery();
1524: assertNotNull("Should have been able to create ResultSet",
1525: _rset);
1526: assertTrue(_rset.next());
1527: _rset.getInt(1);
1528: assertTrue(_rset.wasNull());
1529: _rset.close();
1530: pstmt.close();
1531: }
1532:
1533: public void test_select_min_num_from_foo() throws Exception {
1534: createTableFoo();
1535: populateTableFoo();
1536:
1537: PreparedStatement pstmt = _conn
1538: .prepareStatement("select MIN(NUM) from FOO where NUM >= ?");
1539: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
1540: pstmt.setInt(1, i);
1541: _rset = pstmt.executeQuery();
1542: assertNotNull("Should have been able to create ResultSet",
1543: _rset);
1544: assertTrue(_rset.next());
1545: int min = _rset.getInt(1);
1546: assertEquals(i, min);
1547: assertTrue("ResultSet shouldn't have any more rows", !_rset
1548: .next());
1549: _rset.close();
1550: }
1551: pstmt.close();
1552: }
1553:
1554: public void test_select_min_num_from_foo_with_null()
1555: throws Exception {
1556: createTableFoo();
1557: {
1558: Statement stmt = _conn.createStatement();
1559: stmt.execute("insert into foo values ( null, null, null )");
1560: stmt.close();
1561: }
1562: populateTableFoo();
1563: {
1564: Statement stmt = _conn.createStatement();
1565: stmt.execute("insert into foo values ( null, null, null )");
1566: stmt.close();
1567: }
1568: PreparedStatement pstmt = _conn
1569: .prepareStatement("select MIN(NUM) from FOO");
1570: _rset = pstmt.executeQuery();
1571: assertNotNull("Should have been able to create ResultSet",
1572: _rset);
1573: assertTrue(_rset.next());
1574: assertEquals(0, _rset.getInt(1));
1575: assertTrue(!_rset.wasNull());
1576: assertTrue("ResultSet shouldn't have any more rows", !_rset
1577: .next());
1578: _rset.close();
1579: pstmt.close();
1580: }
1581:
1582: public void test_select_min_num_from_empty_table() throws Exception {
1583: createTableFoo();
1584: PreparedStatement pstmt = _conn
1585: .prepareStatement("select MIN(NUM) from FOO");
1586: _rset = pstmt.executeQuery();
1587: assertNotNull("Should have been able to create ResultSet",
1588: _rset);
1589: assertTrue(_rset.next());
1590: _rset.getInt(1);
1591: assertTrue(_rset.wasNull());
1592: _rset.close();
1593: pstmt.close();
1594: }
1595:
1596: public void test_select_count_num_from_empty_table()
1597: throws Exception {
1598: createTableFoo();
1599: PreparedStatement pstmt = _conn
1600: .prepareStatement("select COUNT(NUM) from FOO");
1601: _rset = pstmt.executeQuery();
1602: assertNotNull("Should have been able to create ResultSet",
1603: _rset);
1604: assertTrue(_rset.next());
1605: assertEquals(0, _rset.getInt(1));
1606: assertTrue(!_rset.wasNull());
1607: _rset.close();
1608: pstmt.close();
1609: }
1610:
1611: public void test_select_str_from_foo_order_by_num()
1612: throws Exception {
1613: createTableFoo();
1614: populateTableFoo();
1615:
1616: String sql = "select STR from FOO order by NUM";
1617: _rset = _stmt.executeQuery(sql);
1618: assertNotNull("Should have been able to create ResultSet",
1619: _rset);
1620:
1621: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
1622: assertTrue("ResultSet should contain more rows", _rset
1623: .next());
1624: assertEquals(String.valueOf(i), _rset.getString(1));
1625: assertTrue("ResultSet shouldn't think value was null",
1626: !_rset.wasNull());
1627: }
1628: assertTrue("ResultSet shouldn't have any more rows", !_rset
1629: .next());
1630: _rset.close();
1631: }
1632:
1633: public void test_select_str_from_foo_order_by_num_asc()
1634: throws Exception {
1635: createTableFoo();
1636: populateTableFoo();
1637:
1638: String sql = "select STR from FOO order by NUM asc";
1639: _rset = _stmt.executeQuery(sql);
1640: assertNotNull("Should have been able to create ResultSet",
1641: _rset);
1642:
1643: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
1644: assertTrue("ResultSet should contain more rows", _rset
1645: .next());
1646: assertEquals(String.valueOf(i), _rset.getString(1));
1647: assertTrue("ResultSet shouldn't think value was null",
1648: !_rset.wasNull());
1649: }
1650: assertTrue("ResultSet shouldn't have any more rows", !_rset
1651: .next());
1652: _rset.close();
1653: }
1654:
1655: public void test_select_str_from_foo_order_by_num_desc()
1656: throws Exception {
1657: createTableFoo();
1658: populateTableFoo();
1659:
1660: String sql = "select STR from FOO order by NUM desc";
1661: _rset = _stmt.executeQuery(sql);
1662: assertNotNull("Should have been able to create ResultSet",
1663: _rset);
1664:
1665: for (int i = NUM_ROWS_IN_FOO - 1; i >= 0; i--) {
1666: assertTrue("ResultSet should contain more rows", _rset
1667: .next());
1668: assertEquals(String.valueOf(i), _rset.getString(1));
1669: assertTrue("ResultSet shouldn't think value was null",
1670: !_rset.wasNull());
1671: }
1672: assertTrue("ResultSet shouldn't have any more rows", !_rset
1673: .next());
1674: _rset.close();
1675: }
1676:
1677: public void test_select_str_from_foo_order_by_upper_str_desc()
1678: throws Exception {
1679: createTableFoo();
1680: populateTableFoo();
1681:
1682: String sql = "select STR from FOO order by UPPER(STR) desc";
1683: _rset = _stmt.executeQuery(sql);
1684: assertNotNull("Should have been able to create ResultSet",
1685: _rset);
1686:
1687: for (int i = NUM_ROWS_IN_FOO - 1; i >= 0; i--) {
1688: assertTrue("ResultSet should contain more rows", _rset
1689: .next());
1690: assertEquals(String.valueOf(i), _rset.getString(1));
1691: assertTrue("ResultSet shouldn't think value was null",
1692: !_rset.wasNull());
1693: }
1694: assertTrue("ResultSet shouldn't have any more rows", !_rset
1695: .next());
1696: _rset.close();
1697: }
1698:
1699: public void test_select_rownum() throws Exception {
1700: createTableFoo();
1701: populateTableFoo();
1702: String sql = "select ROWNUM() from FOO";
1703: _rset = _stmt.executeQuery(sql);
1704: assertNotNull("Should have been able to create ResultSet",
1705: _rset);
1706: for (int i = 1; i < NUM_ROWS_IN_FOO + 1; i++) {
1707: assertTrue("ResultSet should contain more rows", _rset
1708: .next());
1709: assertEquals(i, _rset.getInt(1));
1710: }
1711: assertTrue("ResultSet shouldn't have any more rows", !_rset
1712: .next());
1713: _rset.close();
1714: }
1715:
1716: public void test_select_rownum_with_orderby() throws Exception {
1717: createTableFoo();
1718: populateTableFoo();
1719: String sql = "select ROWNUM() from FOO order by NUM desc";
1720: _rset = _stmt.executeQuery(sql);
1721: _rset = _stmt.executeQuery(sql);
1722: assertNotNull("Should have been able to create ResultSet",
1723: _rset);
1724: for (int i = 1; i < NUM_ROWS_IN_FOO + 1; i++) {
1725: assertTrue("ResultSet should contain more rows", _rset
1726: .next());
1727: assertEquals(i, _rset.getInt(1));
1728: }
1729: assertTrue("ResultSet shouldn't have any more rows", !_rset
1730: .next());
1731: _rset.close();
1732: }
1733:
1734: public void test_select_str_from_foo_where_rownum_lt_5()
1735: throws Exception {
1736: createTableFoo();
1737: populateTableFoo();
1738: String sql = "select ROWNUM(), STR from FOO where ROWNUM() < 5";
1739: Bag found = new HashBag();
1740: _rset = _stmt.executeQuery(sql);
1741: assertNotNull("Should have been able to create ResultSet",
1742: _rset);
1743: for (int i = 1; i < 5; i++) {
1744: assertTrue(_rset.next());
1745: assertEquals(i, _rset.getInt(1));
1746: String str = _rset.getString(2);
1747: assertNotNull(str);
1748: assertTrue(!found.contains(str));
1749: found.add(str);
1750: }
1751: assertTrue("ResultSet shouldn't have any more rows", !_rset
1752: .next());
1753: _rset.close();
1754: }
1755:
1756: public void test_select_where_string_eq_null() throws Exception {
1757: createTableFoo();
1758: PreparedStatement pstmt = _conn
1759: .prepareStatement("insert into FOO ( NUM, STR, NUMTWO ) values ( ?, NULL, NULL )");
1760: for (int i = 0; i < 3; i++) {
1761: pstmt.setInt(1, i);
1762: pstmt.executeUpdate();
1763: }
1764: pstmt.close();
1765: _stmt
1766: .execute("insert into FOO ( NUM, STR, NUMTWO ) values ( 999, 'XXX', 9 )");
1767: String sql = "select NUM, STR, NUMTWO from FOO where STR = NULL";
1768: _rset = _stmt.executeQuery(sql);
1769: assertNotNull("Should have been able to create ResultSet",
1770: _rset);
1771: // X = null is always false, so expect no rows
1772: assertTrue(!_rset.next());
1773: }
1774:
1775: public void test_select_where_int_eq_null() throws Exception {
1776: createTableFoo();
1777: PreparedStatement pstmt = _conn
1778: .prepareStatement("insert into FOO ( NUM, STR, NUMTWO ) values ( ?, NULL, NULL )");
1779: for (int i = 0; i < 3; i++) {
1780: pstmt.setInt(1, i);
1781: pstmt.executeUpdate();
1782: }
1783: pstmt.close();
1784: _stmt
1785: .execute("insert into FOO ( NUM, STR, NUMTWO ) values ( 999, 'XXX', 9 )");
1786: String sql = "select NUM, STR, NUMTWO from FOO where NUMTWO = NULL";
1787: _rset = _stmt.executeQuery(sql);
1788: assertNotNull("Should have been able to create ResultSet",
1789: _rset);
1790: // X = null is always false, so expect no rows
1791: assertTrue(!_rset.next());
1792: }
1793:
1794: public void test_select_where_int_is_null() throws Exception {
1795: createTableFoo();
1796:
1797: PreparedStatement pstmt = _conn
1798: .prepareStatement("insert into FOO ( NUM, STR, NUMTWO ) values ( ?, NULL, NULL )");
1799: for (int i = 0; i < 3; i++) {
1800: pstmt.setInt(1, i);
1801: pstmt.executeUpdate();
1802: }
1803: pstmt.close();
1804:
1805: _stmt
1806: .execute("insert into FOO ( NUM, STR, NUMTWO ) values ( 999, 'XXX', 9 )");
1807:
1808: String sql = "select NUM, STR, NUMTWO from FOO where NUMTWO is null";
1809: _rset = _stmt.executeQuery(sql);
1810: assertNotNull("Should have been able to create ResultSet",
1811: _rset);
1812:
1813: // can't assume the order in which rows will be returned
1814: // so populate a set and compare 'em
1815: Bag expected = new HashBag();
1816: Bag found = new HashBag();
1817:
1818: for (int i = 0; i < 3; i++) {
1819: assertTrue("ResultSet should contain more rows", _rset
1820: .next());
1821: expected.add(new Integer(i));
1822: int num = _rset.getInt(1);
1823: assertTrue(!_rset.wasNull());
1824: found.add(new Integer(num));
1825: assertNull(_rset.getString(2));
1826: assertTrue(_rset.wasNull());
1827: assertEquals(0, _rset.getInt(3));
1828: assertTrue(_rset.wasNull());
1829: }
1830: assertTrue("ResultSet shouldn't have any more rows", !_rset
1831: .next());
1832: _rset.close();
1833: assertEquals(expected, found);
1834: }
1835:
1836: public void test_select_where_int_is_not_null() throws Exception {
1837: createTableFoo();
1838:
1839: PreparedStatement pstmt = _conn
1840: .prepareStatement("insert into FOO ( NUM, STR, NUMTWO ) values ( ?, NULL, NULL )");
1841: for (int i = 0; i < 3; i++) {
1842: pstmt.setInt(1, i);
1843: pstmt.executeUpdate();
1844: }
1845: pstmt.close();
1846:
1847: _stmt
1848: .execute("insert into FOO ( NUM, STR, NUMTWO ) values ( 999, 'XXX', 9 )");
1849:
1850: String sql = "select NUM, STR, NUMTWO from FOO where NUMTWO is not null";
1851: _rset = _stmt.executeQuery(sql);
1852: assertNotNull("Should have been able to create ResultSet",
1853: _rset);
1854: assertTrue("ResultSet should contain more rows", _rset.next());
1855: assertEquals(999, _rset.getInt(1));
1856: assertTrue(!_rset.wasNull());
1857: assertEquals("XXX", _rset.getString(2));
1858: assertTrue(!_rset.wasNull());
1859: assertEquals(9, _rset.getInt(3));
1860: assertTrue(!_rset.wasNull());
1861: assertTrue("ResultSet shouldn't have any more rows", !_rset
1862: .next());
1863: _rset.close();
1864: }
1865:
1866: public void test_select_where_not_int_is_null() throws Exception {
1867: createTableFoo();
1868:
1869: PreparedStatement pstmt = _conn
1870: .prepareStatement("insert into FOO ( NUM, STR, NUMTWO ) values ( ?, NULL, NULL )");
1871: for (int i = 0; i < 3; i++) {
1872: pstmt.setInt(1, i);
1873: pstmt.executeUpdate();
1874: }
1875: pstmt.close();
1876:
1877: _stmt
1878: .execute("insert into FOO ( NUM, STR, NUMTWO ) values ( 999, 'XXX', 9 )");
1879:
1880: String sql = "select NUM, STR, NUMTWO from FOO where not NUMTWO is null";
1881: _rset = _stmt.executeQuery(sql);
1882: assertNotNull("Should have been able to create ResultSet",
1883: _rset);
1884: assertTrue("ResultSet should contain more rows", _rset.next());
1885: assertEquals(999, _rset.getInt(1));
1886: assertTrue(!_rset.wasNull());
1887: assertEquals("XXX", _rset.getString(2));
1888: assertTrue(!_rset.wasNull());
1889: assertEquals(9, _rset.getInt(3));
1890: assertTrue(!_rset.wasNull());
1891: assertTrue("ResultSet shouldn't have any more rows", !_rset
1892: .next());
1893: _rset.close();
1894: }
1895:
1896: public void test_select_where_string_is_null() throws Exception {
1897: createTableFoo();
1898:
1899: PreparedStatement pstmt = _conn
1900: .prepareStatement("insert into FOO ( NUM, STR, NUMTWO ) values ( ?, NULL, NULL )");
1901: for (int i = 0; i < 3; i++) {
1902: pstmt.setInt(1, i);
1903: pstmt.executeUpdate();
1904: }
1905: pstmt.close();
1906:
1907: _stmt
1908: .execute("insert into FOO ( NUM, STR, NUMTWO ) values ( 999, 'XXX', 9 )");
1909:
1910: String sql = "select NUM, STR, NUMTWO from FOO where STR is null";
1911: _rset = _stmt.executeQuery(sql);
1912: assertNotNull("Should have been able to create ResultSet",
1913: _rset);
1914:
1915: // can't assume the order in which rows will be returned
1916: // so populate a set and compare 'em
1917: Bag expected = new HashBag();
1918: Bag found = new HashBag();
1919:
1920: for (int i = 0; i < 3; i++) {
1921: assertTrue("ResultSet should contain more rows", _rset
1922: .next());
1923: expected.add(new Integer(i));
1924: int num = _rset.getInt(1);
1925: assertTrue(!_rset.wasNull());
1926: found.add(new Integer(num));
1927: assertNull(_rset.getString(2));
1928: assertTrue(_rset.wasNull());
1929: assertEquals(0, _rset.getInt(3));
1930: assertTrue(_rset.wasNull());
1931: }
1932: assertTrue("ResultSet shouldn't have any more rows", !_rset
1933: .next());
1934: _rset.close();
1935: assertEquals(expected, found);
1936: }
1937:
1938: public void test_select_where_string_is_not_null() throws Exception {
1939: createTableFoo();
1940:
1941: PreparedStatement pstmt = _conn
1942: .prepareStatement("insert into FOO ( NUM, STR, NUMTWO ) values ( ?, NULL, NULL )");
1943: for (int i = 0; i < 3; i++) {
1944: pstmt.setInt(1, i);
1945: pstmt.executeUpdate();
1946: }
1947: pstmt.close();
1948:
1949: _stmt
1950: .execute("insert into FOO ( NUM, STR, NUMTWO ) values ( 999, 'XXX', 9 )");
1951:
1952: String sql = "select NUM, STR, NUMTWO from FOO where STR is not null";
1953: _rset = _stmt.executeQuery(sql);
1954: assertNotNull("Should have been able to create ResultSet",
1955: _rset);
1956: assertTrue("ResultSet should contain more rows", _rset.next());
1957: assertEquals(999, _rset.getInt(1));
1958: assertTrue(!_rset.wasNull());
1959: assertEquals("XXX", _rset.getString(2));
1960: assertTrue(!_rset.wasNull());
1961: assertEquals(9, _rset.getInt(3));
1962: assertTrue(!_rset.wasNull());
1963: assertTrue("ResultSet shouldn't have any more rows", !_rset
1964: .next());
1965: _rset.close();
1966: }
1967:
1968: public void test_select_sequence_nextval() throws Exception {
1969: createTableFoo(false);
1970: populateTableFoo();
1971: createSequenceFooSeq();
1972: String sql = "SELECT foo_seq.nextval FROM foo";
1973: _rset = _stmt.executeQuery(sql);
1974: assertNotNull("Should have been able to create ResultSet",
1975: _rset);
1976: assertTrue("ResultSet should contain a row", _rset.next());
1977: assertEquals(0, _rset.getInt(1));
1978: _rset.close();
1979: dropSequenceFooSeq();
1980: }
1981:
1982: public void test_select_sequence_nextval_from_foo()
1983: throws Exception {
1984: createTableFoo(false);
1985: populateTableFoo();
1986: createSequenceFooSeq();
1987: String sql = "SELECT foo_seq.nextval FROM foo";
1988: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
1989: _rset = _stmt.executeQuery(sql);
1990: assertNotNull("Should have been able to create ResultSet",
1991: _rset);
1992: assertTrue("ResultSet should contain a row", _rset.next());
1993: assertEquals(i, _rset.getInt(1));
1994: }
1995: _rset.close();
1996: dropSequenceFooSeq();
1997: }
1998:
1999: public void test_select_star_from_foo_f_bar_b_where_id_eq_num()
2000: throws Exception {
2001: createTableFoo();
2002: populateTableFoo();
2003: createTableBar();
2004: populateTableBar();
2005:
2006: String sql = "select * from FOO f, BAR b where b.ID = f.NUM ";
2007: PreparedStatement stmt = _conn.prepareStatement(sql);
2008: _rset = stmt.executeQuery();
2009: assertNotNull("Should have been able to create ResultSet",
2010: _rset);
2011: assertTrue("Should have rows", _rset.next());
2012:
2013: assertNotNull("Should have a value", _rset.getObject(1));
2014:
2015: stmt.close();
2016: }
2017:
2018: public void test_select_star_from_foo_where_id_in_list()
2019: throws Exception {
2020: createTableFoo();
2021: populateTableFoo();
2022:
2023: String sql = "select * from FOO where NUM in (1, 2, 3)";
2024: PreparedStatement stmt = _conn.prepareStatement(sql);
2025: _rset = stmt.executeQuery();
2026: assertNotNull("Should have been able to create ResultSet",
2027: _rset);
2028: int matches = 0;
2029: while (_rset.next()) {
2030: matches++;
2031: }
2032: assertEquals("Should have 3 rows selected", 3, matches);
2033:
2034: stmt.close();
2035: }
2036:
2037: public void test_select_f_star_from_foo_f_bar_b_where_id_eq_num()
2038: throws Exception {
2039: createTableFoo();
2040: populateTableFoo();
2041: createTableBar();
2042: populateTableBar();
2043:
2044: String sql = "select f.* from FOO f, BAR b where b.ID = f.NUM ";
2045: PreparedStatement stmt = _conn.prepareStatement(sql);
2046: _rset = stmt.executeQuery();
2047: assertNotNull("Should have been able to create ResultSet",
2048: _rset);
2049:
2050: stmt.close();
2051: }
2052:
2053: // Test to make sure that when we rollback a connection that the PreparedStatements
2054: // remain valid.
2055: public void testPreparedStatementAfterRollback() throws Exception {
2056: createTableFoo();
2057: populateTableFoo();
2058:
2059: AxionConnection conn = (AxionConnection) DriverManager
2060: .getConnection(getConnectString());
2061:
2062: conn.setAutoCommit(false);
2063:
2064: PreparedStatement stmt = conn
2065: .prepareStatement("select NUM, STR from FOO");
2066:
2067: assertNotNull(stmt.executeQuery());
2068:
2069: conn.rollback();
2070:
2071: ResultSet rset2 = stmt.executeQuery();
2072:
2073: //All we are really checking is that we are able to iterate
2074: //through the result set.
2075: while (rset2.next()) {
2076: }
2077: }
2078:
2079: }
|