0001: /*
0002: * $Id: TestSpecials.java,v 1.94 2007/11/13 19:04:02 rwald 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.math.BigDecimal;
0044: import java.net.URL;
0045: import java.sql.Blob;
0046: import java.sql.Clob;
0047: import java.sql.Date;
0048: import java.sql.PreparedStatement;
0049: import java.sql.ResultSet;
0050: import java.sql.ResultSetMetaData;
0051: import java.sql.SQLException;
0052: import java.sql.Statement;
0053: import java.sql.Types;
0054: import java.util.Arrays;
0055: import java.util.Map;
0056:
0057: import junit.framework.Test;
0058: import junit.framework.TestSuite;
0059:
0060: /**
0061: * @version $Revision: 1.94 $ $Date: 2007/11/13 19:04:02 $
0062: * @author Rodney Waldhoff
0063: * @author Ahimanikya Satapathy
0064: * @author Ritesh Adval
0065: */
0066: public class TestSpecials extends AbstractFunctionalTest {
0067:
0068: //------------------------------------------------------------ Conventional
0069:
0070: public TestSpecials(String testName) {
0071: super (testName);
0072: }
0073:
0074: public static Test suite() {
0075: return new TestSuite(TestSpecials.class);
0076: }
0077:
0078: //--------------------------------------------------------------- Lifecycle
0079:
0080: public void setUp() throws Exception {
0081: super .setUp();
0082: }
0083:
0084: public void tearDown() throws Exception {
0085: super .tearDown();
0086: }
0087:
0088: //------------------------------------------------------------------- Tests
0089: public void testAddIndexAfterInsertUpdateDelete() throws Exception {
0090: _stmt.execute("create table foo (id int, val varchar(10))");
0091: _stmt
0092: .executeUpdate("insert into foo (id, val) values ( 1, 'one' )");
0093: _stmt
0094: .executeUpdate("insert into foo (id, val) values ( 2, 'two' )");
0095: _stmt
0096: .executeUpdate("insert into foo (id, val) values ( 3, null )");
0097: _stmt.executeUpdate("delete from foo where id = 2");
0098: _stmt
0099: .executeUpdate("update foo set val = 'three' where id = 3");
0100: _stmt.execute("create index foo_ndx on foo (id)");
0101: assertResult("one", "select val from foo where id = 1");
0102: assertResult("three", "select val from foo where id = 3");
0103: }
0104:
0105: public void testBindTypes() throws Exception {
0106: byte[] bytes = new byte[] { (byte) 1, (byte) 2, (byte) 3 };
0107:
0108: final long now = System.currentTimeMillis();
0109: final long dateNow = (now / 86400000L) * 86400000L;
0110: final long timeNow = (now % 86400000L);
0111:
0112: java.sql.Date date = new java.sql.Date(dateNow);
0113: java.sql.Time time = new java.sql.Time(timeNow);
0114: java.sql.Timestamp timestamp = new java.sql.Timestamp(now);
0115: URL url = new URL("http://127.0.0.1/");
0116:
0117: _stmt
0118: .execute("create table foo ( boolv boolean, byteav varbinary(3), bytev byte, shortv short, intv integer, longv bigint, floatv float, doublev float, strv varchar(100), nullv boolean, datev date, timev time, tsv timestamp, urlv varchar(100) )");
0119: PreparedStatement pstmt = _conn
0120: .prepareStatement("insert into foo values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
0121: pstmt.setBoolean(1, true);
0122: pstmt.setBytes(2, bytes);
0123: pstmt.setByte(3, (byte) 3);
0124: pstmt.setShort(4, (short) 4);
0125: pstmt.setInt(5, 5);
0126: pstmt.setLong(6, 6L);
0127: pstmt.setFloat(7, 7.0f);
0128: pstmt.setDouble(8, 8.0d);
0129: pstmt.setString(9, "string");
0130: pstmt.setNull(10, Types.BOOLEAN);
0131: pstmt.setDate(11, date);
0132: pstmt.setTime(12, time);
0133: pstmt.setTimestamp(13, timestamp);
0134: pstmt.setURL(14, url);
0135: assertEquals(1, pstmt.executeUpdate());
0136: pstmt.close();
0137:
0138: _rset = _stmt.executeQuery("select * from foo");
0139: assertTrue(_rset.next());
0140: assertEquals(true, _rset.getBoolean(1));
0141: assertTrue(Arrays.equals(bytes, _rset.getBytes(2)));
0142: assertEquals((byte) 3, _rset.getByte(3));
0143: assertEquals((short) 4, _rset.getShort(4));
0144: assertEquals(5, _rset.getInt(5));
0145: assertEquals(6L, _rset.getLong(6));
0146: assertEquals(7.0f, _rset.getFloat(7), 0f);
0147: assertEquals(8.0d, _rset.getDouble(8), 0d);
0148: assertEquals("string", _rset.getString(9));
0149: assertNull(_rset.getObject(10));
0150: assertEquals(date, _rset.getDate(11));
0151: assertEquals(date, _rset.getDate("datev"));
0152: assertEquals(time, _rset.getTime(12));
0153: assertEquals(time, _rset.getTime("timev"));
0154: assertEquals(timestamp, _rset.getTimestamp(13));
0155: assertEquals(timestamp, _rset.getTimestamp("tsv"));
0156: assertEquals(url, _rset.getURL(14));
0157: assertEquals(url, _rset.getURL("urlv"));
0158:
0159: }
0160:
0161: public void testSQLExceptionOnBadSyntax() throws Exception {
0162: try {
0163: _stmt.execute("xyzzy XYZZY xyzzy");
0164: fail("Expected SQLException");
0165: } catch (SQLException e) {
0166: // expected
0167: }
0168: }
0169:
0170: public void testFunctionNamesAreNotReservedWords() throws Exception {
0171: _stmt.execute("create table max ( min int )");
0172: _stmt.execute("insert into max values ( 1 )");
0173: _stmt.execute("select min from max");
0174: _stmt.execute("drop table max");
0175:
0176: _stmt.execute("create table datediff ( dateadd int )");
0177: _stmt.execute("insert into datediff values ( 1 )");
0178: _stmt.execute("select dateadd from datediff");
0179: _stmt.execute("drop table datediff");
0180: }
0181:
0182: public void testGetURL() throws Exception {
0183: ResultSet rset = _stmt
0184: .executeQuery("select 'http://localhost:8080/'");
0185: assertTrue(rset.next());
0186: assertEquals(new URL("http://localhost:8080/"), rset.getURL(1));
0187: assertTrue(!rset.next());
0188: rset.close();
0189: }
0190:
0191: public void testSimpleExplain() throws Exception {
0192: createTableFoo();
0193: populateTableFoo();
0194: ResultSet rset = _stmt
0195: .executeQuery("explain select * from FOO where NUM < 3");
0196:
0197: assertTrue(rset.next());
0198: assertEquals("Unmod(MemoryTable(FOO))", rset.getString(1));
0199: assertTrue(rset.next());
0200: assertEquals("Filtering(LESSTHAN((FOO).NUM,3))", rset
0201: .getString(1));
0202: assertTrue(!rset.next());
0203: rset.close();
0204: }
0205:
0206: public void testLiteralBooleanInWhere() throws Exception {
0207: _stmt.execute("create table foo ( id int )");
0208: _stmt.executeUpdate("insert into foo values ( 1 )");
0209:
0210: assertOneRow("select id from foo where TRUE");
0211: assertOneRow("select id from foo where true");
0212: assertOneRow("select id from foo where true and true");
0213: assertOneRow("select id from foo where true or true");
0214: assertOneRow("select id from foo where true or false");
0215: assertOneRow("select id from foo where true and not(false)");
0216: assertOneRow("select id from foo where not(false)");
0217: assertOneRow("select id from foo where not(true and false)");
0218:
0219: assertNoRows("select id from foo where true and false");
0220: assertNoRows("select id from foo where false or false");
0221: assertNoRows("select id from foo where false");
0222: assertNoRows("select id from foo where not(true)");
0223: }
0224:
0225: public void testClearBindVariableInFunction() throws Exception {
0226: PreparedStatement stmt = _conn
0227: .prepareStatement("select upper(?)");
0228: stmt.setString(1, "test");
0229: assertResult("TEST", stmt.executeQuery());
0230: stmt.clearParameters();
0231: stmt.setString(1, "test2");
0232: assertResult("TEST2", stmt.executeQuery());
0233: stmt.close();
0234: }
0235:
0236: public void testSequenceWithBadPseduoColumn() throws Exception {
0237: _stmt.execute("create sequence foo_id_seq");
0238: try {
0239: _stmt.executeQuery("select foo_id_seq.xyzzy");
0240: fail("Expected SQLException");
0241: } catch (SQLException e) {
0242: // expected
0243: }
0244: }
0245:
0246: public void testSequenceCurrvalInWhereClause() throws Exception {
0247: _stmt.execute("create sequence foo_id_seq");
0248: _stmt
0249: .execute("create table foo ( id int default foo_id_seq.nextval, val varchar(10) )");
0250: _conn.setAutoCommit(false);
0251: assertEquals(
0252: 1,
0253: _stmt
0254: .executeUpdate("insert into foo ( val ) values ('value')"));
0255: _rset = _stmt
0256: .executeQuery("select id, val from foo where id = foo_id_seq.currval");
0257: assertTrue(_rset.next());
0258: assertNotNull(_rset.getString(1));
0259: assertEquals("value", _rset.getString(2));
0260: assertTrue(!_rset.next());
0261: }
0262:
0263: public void testSequenceNextvalOutsideOfTransaction()
0264: throws Exception {
0265: _stmt.execute("create sequence foo_seq start with 0");
0266: for (int i = 0; i < 3; i++) {
0267: _rset = _stmt.executeQuery("select foo_seq.nextval");
0268: assertTrue(_rset.next());
0269: assertEquals(i, _rset.getInt(1));
0270: assertTrue(!_rset.next());
0271: _rset.close();
0272: }
0273: }
0274:
0275: // TODO: As per ANSI 2003 spec, All (NEXT VALUE FOR expressions) specifyed of
0276: // the same sequence generator within a single statement evaluate to the
0277: // same value for a given row. Make this test pass.
0278: //public void testSequenceMultipleNextvalOutsideOfTransaction() throws Exception {
0279: // _stmt.execute("create sequence foo_seq start with 0");
0280: // for (int i = 0; i < 3; i++) {
0281: // _rset = _stmt.executeQuery("select foo_seq.nextval, foo_seq.nextval");
0282: // assertTrue(_rset.next());
0283: // assertEquals(i, _rset.getInt(1));
0284: // assertEquals(i, _rset.getInt(2));
0285: // assertTrue(!_rset.next());
0286: // _rset.close();
0287: // }
0288: //}
0289:
0290: public void testSequenceCurrvalOutsideOfTransaction()
0291: throws Exception {
0292: _stmt.execute("create sequence foo_seq start with 0");
0293:
0294: try {
0295: _rset = _stmt.executeQuery("select foo_seq.currval");
0296: assertTrue(_rset.next());
0297: _rset.getInt(1);
0298: fail("Expected SQLException");
0299: } catch (SQLException e) {
0300: // expected
0301: }
0302:
0303: for (int i = 0; i < 3; i++) {
0304: _rset = _stmt
0305: .executeQuery("select foo_seq.nextval, foo_seq.currval");
0306: assertTrue(_rset.next());
0307: assertEquals(i, _rset.getInt(1));
0308: assertEquals(i, _rset.getInt(2));
0309: assertTrue(!_rset.next());
0310: _rset.close();
0311:
0312: try {
0313: _rset = _stmt.executeQuery("select foo_seq.currval");
0314: assertTrue(_rset.next());
0315: _rset.getInt(1);
0316: fail("Expected SQLException");
0317: } catch (SQLException e) {
0318: // expected
0319: }
0320: }
0321: }
0322:
0323: public void testSequenceNextvalInsideOfTransaction()
0324: throws Exception {
0325: _stmt.execute("create sequence foo_seq start with 0");
0326: _conn.setAutoCommit(false);
0327: for (int i = 0; i < 3; i++) {
0328: _rset = _stmt.executeQuery("select foo_seq.nextval");
0329: assertTrue(_rset.next());
0330: assertEquals(i, _rset.getInt(1));
0331: assertTrue(!_rset.next());
0332: _rset.close();
0333: }
0334: }
0335:
0336: public void testSequenceCurrvalInsideOfTransaction()
0337: throws Exception {
0338: _stmt.execute("create sequence foo_seq start with 0");
0339: _conn.setAutoCommit(false);
0340: for (int i = 0; i < 3; i++) {
0341: _rset = _stmt
0342: .executeQuery("select foo_seq.nextval, foo_seq.currval");
0343: assertTrue(_rset.next());
0344: assertEquals(i, _rset.getInt(1));
0345: assertEquals(i, _rset.getInt(2));
0346: assertTrue(!_rset.next());
0347: _rset.close();
0348:
0349: _rset = _stmt.executeQuery("select foo_seq.currval");
0350: assertTrue(_rset.next());
0351: assertEquals(i, _rset.getInt(1));
0352: assertTrue(!_rset.next());
0353: _rset.close();
0354: }
0355: }
0356:
0357: public void testCheckFileState() throws Exception {
0358: _rset = _stmt.executeQuery("CHECKFILESTATE");
0359: assertNotNull(_rset);
0360: assertTrue(_rset.next());
0361: assertNotNull(_rset.getString(1));
0362: _rset.close();
0363: }
0364:
0365: public void test_select_star_metadata() throws Exception {
0366: createTableFoo();
0367: populateTableFoo();
0368: _rset = _stmt.executeQuery("select * from FOO");
0369: ResultSetMetaData mdata = _rset.getMetaData();
0370: assertNotNull(mdata);
0371: assertTrue("NUM".equalsIgnoreCase(mdata.getColumnName(1)));
0372: assertTrue("STR".equalsIgnoreCase(mdata.getColumnName(2)));
0373: assertTrue("NUMTWO".equalsIgnoreCase(mdata.getColumnName(3)));
0374: assertTrue("FOO".equalsIgnoreCase(mdata.getTableName(1)));
0375: assertTrue("FOO".equalsIgnoreCase(mdata.getTableName(2)));
0376: assertTrue("FOO".equalsIgnoreCase(mdata.getTableName(3)));
0377: assertNotNull(mdata.getColumnTypeName(1));
0378: assertNotNull(mdata.getColumnTypeName(2));
0379: assertNotNull(mdata.getColumnTypeName(3));
0380: }
0381:
0382: public void testCreateJavaObjectTable() throws Exception {
0383: _stmt
0384: .execute("create table foo( key_object java_object, entry_object java_object )");
0385: }
0386:
0387: public void testFirst() throws Exception {
0388: createTableFoo();
0389: populateTableFoo();
0390:
0391: _rset = _stmt.executeQuery("select str from foo");
0392: assertNotNull("Should have been able to create ResultSet",
0393: _rset);
0394:
0395: assertTrue(_rset.isBeforeFirst());
0396: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0397: assertTrue("ResultSet should contain more rows", _rset
0398: .next());
0399: assertTrue(!_rset.isBeforeFirst());
0400: String val = _rset.getString(1);
0401: assertNotNull("Returned String should not be null", val);
0402: assertTrue("ResultSet shouldn't think value was null",
0403: !_rset.wasNull());
0404: assertEquals(String.valueOf(i), val);
0405: }
0406: assertTrue("ResultSet shouldn't have any more rows", !_rset
0407: .next());
0408:
0409: _rset.first();
0410:
0411: assertTrue(!_rset.isBeforeFirst());
0412: for (int i = 1; i < NUM_ROWS_IN_FOO; i++) {
0413: assertTrue("ResultSet should contain more rows", _rset
0414: .next());
0415: assertTrue(!_rset.isBeforeFirst());
0416: String val = _rset.getString(1);
0417: assertNotNull("Returned String should not be null", val);
0418: assertTrue("ResultSet shouldn't think value was null",
0419: !_rset.wasNull());
0420: assertEquals(String.valueOf(i), val);
0421: }
0422: assertTrue("ResultSet shouldn't have any more rows", !_rset
0423: .next());
0424:
0425: _rset.close();
0426: }
0427:
0428: public void testBeforeFirstAfterLastEtc() throws Exception {
0429: createTableFoo();
0430: populateTableFoo();
0431:
0432: _rset = _stmt.executeQuery("select str from foo");
0433: assertNotNull("Should have been able to create ResultSet",
0434: _rset);
0435:
0436: assertTrue(_rset.isBeforeFirst());
0437: assertTrue(!_rset.isFirst());
0438: assertTrue(!_rset.isAfterLast());
0439: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0440: assertTrue("ResultSet should contain more rows", _rset
0441: .next());
0442: assertTrue(!_rset.isBeforeFirst());
0443: if (0 == i) {
0444: assertTrue(_rset.isFirst());
0445: } else {
0446: assertTrue(!_rset.isFirst());
0447: }
0448:
0449: assertTrue(!_rset.isAfterLast());
0450: if (NUM_ROWS_IN_FOO - 1 == i) {
0451: assertTrue(_rset.isLast());
0452: }
0453:
0454: String val = _rset.getString(1);
0455: assertNotNull("Returned String should not be null", val);
0456: assertTrue("ResultSet shouldn't think value was null",
0457: !_rset.wasNull());
0458: assertEquals(String.valueOf(i), val);
0459: }
0460: assertTrue(!_rset.isFirst());
0461: assertTrue(!_rset.isBeforeFirst());
0462:
0463: assertTrue("ResultSet shouldn't have any more rows", !_rset
0464: .next());
0465: assertTrue(_rset.isAfterLast());
0466:
0467: _rset.beforeFirst();
0468:
0469: assertTrue(_rset.isBeforeFirst());
0470: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0471: assertTrue("ResultSet should contain more rows", _rset
0472: .next());
0473: assertTrue(!_rset.isBeforeFirst());
0474: String val = _rset.getString(1);
0475: assertNotNull("Returned String should not be null", val);
0476: assertTrue("ResultSet shouldn't think value was null",
0477: !_rset.wasNull());
0478: assertEquals(String.valueOf(i), val);
0479: }
0480: assertTrue("ResultSet shouldn't have any more rows", !_rset
0481: .next());
0482:
0483: _rset.close();
0484: }
0485:
0486: public void testResultSetRelative() throws Exception {
0487: createTableFoo();
0488: populateTableFoo();
0489:
0490: _rset = _stmt.executeQuery("select str from foo");
0491:
0492: assertTrue(_rset.next());
0493: for (int i = 0; i < NUM_ROWS_IN_FOO + 1; i++) {
0494: assertTrue(_rset.relative(0));
0495: }
0496: assertTrue(!_rset.relative(NUM_ROWS_IN_FOO + 1));
0497: }
0498:
0499: public void test_select_literal_without_from() throws Exception {
0500: createTableFoo();
0501: populateTableFoo();
0502:
0503: String sql = "select 'Literal'";
0504: _rset = _stmt.executeQuery(sql);
0505: assertNotNull("Should have been able to create ResultSet",
0506: _rset);
0507: assertTrue("ResultSet should contain more rows", _rset.next());
0508: String val = _rset.getString(1);
0509: assertNotNull("Returned String should not be null", val);
0510: assertTrue("ResultSet shouldn't think value was null", !_rset
0511: .wasNull());
0512: assertEquals("Returned string should equal \"Literal\".",
0513: "Literal", val);
0514: assertTrue("ResultSet shouldn't have any more rows", !_rset
0515: .next());
0516: _rset.close();
0517: }
0518:
0519: public void test_select_non_literal_without_from() throws Exception {
0520: createTableFoo();
0521: populateTableFoo();
0522:
0523: String sql = "select foo.num";
0524: try {
0525: _stmt.executeQuery(sql);
0526: fail("Expected SQLException");
0527: } catch (SQLException e) {
0528: // expected
0529: }
0530: }
0531:
0532: public void test_select_bindvar_without_from() throws Exception {
0533: createTableFoo();
0534: populateTableFoo();
0535:
0536: PreparedStatement pstmt = _conn.prepareStatement("select ?");
0537: pstmt.setInt(1, 1);
0538: _rset = pstmt.executeQuery();
0539: assertTrue(_rset.next());
0540: assertEquals(1, _rset.getInt(1));
0541: assertTrue(!_rset.next());
0542: pstmt.clearParameters();
0543: pstmt.setInt(1, 2);
0544: _rset = pstmt.executeQuery();
0545: assertTrue(_rset.next());
0546: assertEquals(2, _rset.getInt(1));
0547: assertTrue(!_rset.next());
0548: pstmt.close();
0549: }
0550:
0551: public void test_select_bindvar_from_foo() throws Exception {
0552: createTableFoo();
0553: populateTableFoo();
0554: PreparedStatement pstmt = _conn
0555: .prepareStatement("select ? from FOO");
0556:
0557: pstmt.setString(1, "bound");
0558: _rset = pstmt.executeQuery();
0559: assertNotNull("Should have been able to create ResultSet",
0560: _rset);
0561: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0562: assertTrue("ResultSet should contain more rows", _rset
0563: .next());
0564: assertEquals("bound", _rset.getString(1));
0565: assertTrue("ResultSet shouldn't think value was null",
0566: !_rset.wasNull());
0567: }
0568: assertTrue("ResultSet shouldn't have any more rows", !_rset
0569: .next());
0570: _rset.close();
0571:
0572: pstmt.clearParameters();
0573: pstmt.setInt(1, 2);
0574: _rset = pstmt.executeQuery();
0575: assertNotNull("Should have been able to create ResultSet",
0576: _rset);
0577: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0578: assertTrue("ResultSet should contain more rows", _rset
0579: .next());
0580: assertEquals(2, _rset.getInt(1));
0581: assertTrue("ResultSet shouldn't think value was null",
0582: !_rset.wasNull());
0583: }
0584: assertTrue("ResultSet shouldn't have any more rows", !_rset
0585: .next());
0586: _rset.close();
0587: }
0588:
0589: public void testBindVariableAsFunctionArgument() throws Exception {
0590: createTableFoo();
0591: populateTableFoo();
0592: PreparedStatement pstmt = _conn
0593: .prepareStatement("select NUM, UPPER(?) from FOO where UPPER(STR) = UPPER(?)");
0594: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0595: pstmt.setString(1, "will become upper");
0596: pstmt.setString(2, String.valueOf(i));
0597: _rset = pstmt.executeQuery();
0598: assertNotNull("Should have been able to create ResultSet",
0599: _rset);
0600: assertTrue("ResultSet should contain more rows", _rset
0601: .next());
0602: assertEquals(i, _rset.getInt(1));
0603: assertEquals("WILL BECOME UPPER", _rset.getString(2));
0604: assertTrue("ResultSet shouldn't think value was null",
0605: !_rset.wasNull());
0606: assertTrue("ResultSet shouldn't have any more rows", !_rset
0607: .next());
0608: _rset.close();
0609: pstmt.clearParameters();
0610: }
0611: pstmt.close();
0612: }
0613:
0614: public void testGetBigDecimal() throws Exception {
0615: createTableFoo();
0616: populateTableFoo();
0617: PreparedStatement pstmt = _conn
0618: .prepareStatement("select NUM from FOO");
0619: _rset = pstmt.executeQuery();
0620: assertNotNull("Should have been able to create ResultSet",
0621: _rset);
0622: for (int i = 0; i < NUM_ROWS_IN_FOO; i++) {
0623: assertTrue("ResultSet should contain more rows", _rset
0624: .next());
0625: assertEquals(new BigDecimal(i), _rset.getBigDecimal(1));
0626: }
0627: assertTrue(!_rset.next());
0628: pstmt.close();
0629: _rset.close();
0630: }
0631:
0632: public void testInsertBigDecimal() throws Exception {
0633: _stmt.execute("create table bigdectable ( \"value\" number )");
0634: PreparedStatement pstmt = _conn
0635: .prepareStatement("insert into bigdectable values ( ? )");
0636: // insert as integer
0637: {
0638: pstmt.setInt(1, Integer.MAX_VALUE);
0639: assertEquals(1, pstmt.executeUpdate());
0640: ResultSet rset = _stmt
0641: .executeQuery("select \"value\" from bigdectable");
0642: assertTrue(rset.next());
0643: assertEquals(Integer.MAX_VALUE, rset.getInt(1));
0644: assertEquals(new BigDecimal(String
0645: .valueOf(Integer.MAX_VALUE)), rset.getBigDecimal(1));
0646: assertEquals(new BigDecimal(String
0647: .valueOf(Integer.MAX_VALUE)), rset
0648: .getBigDecimal("VALUE"));
0649: assertTrue(!rset.next());
0650: rset.close();
0651: assertEquals(1, _stmt
0652: .executeUpdate("delete from bigdectable"));
0653: }
0654: // insert as string
0655: {
0656: pstmt.setString(1, String.valueOf(Integer.MAX_VALUE));
0657: assertEquals(1, pstmt.executeUpdate());
0658: ResultSet rset = _stmt
0659: .executeQuery("select \"value\" from bigdectable");
0660: assertTrue(rset.next());
0661: assertEquals(Integer.MAX_VALUE, rset.getInt(1));
0662: assertEquals(new BigDecimal(String
0663: .valueOf(Integer.MAX_VALUE)), rset.getBigDecimal(1));
0664: assertTrue(!rset.next());
0665: rset.close();
0666: assertEquals(1, _stmt
0667: .executeUpdate("delete from bigdectable"));
0668: }
0669: // insert as bigdecimal
0670: {
0671: pstmt.setBigDecimal(1, new BigDecimal(String
0672: .valueOf(Integer.MAX_VALUE)));
0673: assertEquals(1, pstmt.executeUpdate());
0674: ResultSet rset = _stmt
0675: .executeQuery("select \"value\" from bigdectable");
0676: assertTrue(rset.next());
0677: assertEquals(Integer.MAX_VALUE, rset.getInt(1));
0678: assertEquals(new BigDecimal(String
0679: .valueOf(Integer.MAX_VALUE)), rset.getBigDecimal(1));
0680: assertTrue(!rset.next());
0681: rset.close();
0682: assertEquals(1, _stmt
0683: .executeUpdate("delete from bigdectable"));
0684: }
0685: // insert big decimal value as string
0686: {
0687: String value = "1234567890123456789012";
0688: pstmt.setString(1, value);
0689: assertEquals(1, pstmt.executeUpdate());
0690: ResultSet rset = _stmt
0691: .executeQuery("select \"value\" from bigdectable");
0692: assertTrue(rset.next());
0693: assertEquals(value, rset.getString(1));
0694: assertEquals(new BigDecimal(value), rset.getBigDecimal(1));
0695: assertTrue(!rset.next());
0696: rset.close();
0697: assertEquals(1, _stmt
0698: .executeUpdate("delete from bigdectable"));
0699: }
0700: // insert big decimal value as BigDecimal
0701: {
0702: String value = "1234567890123456789012";
0703: pstmt.setBigDecimal(1, new BigDecimal(value));
0704: assertEquals(1, pstmt.executeUpdate());
0705: ResultSet rset = _stmt
0706: .executeQuery("select \"value\" from bigdectable");
0707: assertTrue(rset.next());
0708: assertEquals(value, rset.getString(1));
0709: assertEquals(new BigDecimal(value), rset.getBigDecimal(1));
0710: assertTrue(!rset.next());
0711: rset.close();
0712: assertEquals(1, _stmt
0713: .executeUpdate("delete from bigdectable"));
0714: }
0715: pstmt.close();
0716: }
0717:
0718: public void testNumberType() throws Exception {
0719: _stmt.execute("create table XYZZY ( MYCOL NUMBER(10,2) )");
0720: assertEquals(1, _stmt
0721: .executeUpdate("insert into XYZZY values ( 1 )"));
0722: assertEquals(1, _stmt
0723: .executeUpdate("insert into XYZZY values ( 1.01 )"));
0724: }
0725:
0726: public void testSysdate() throws Exception {
0727: createAndPopulateDual();
0728: ResultSet rset = _stmt.executeQuery("select SYSDATE, NOW()");
0729: assertTrue(rset.next());
0730: Date date1 = rset.getDate(1);
0731: Date date2 = rset.getDate(2);
0732: assertNotNull(date1);
0733: assertNotNull(date2);
0734: assertTrue(!date1.after(date2));
0735: // TODO: it'd be nice to assert
0736: // date1.equals(date2)
0737: }
0738:
0739: public void testMultipleTransactionsOnASingleConnection()
0740: throws Exception {
0741: createTableFoo();
0742: _conn.setAutoCommit(false);
0743: PreparedStatement pstmt = _conn
0744: .prepareStatement("insert into FOO ( NUM, STR, NUMTWO ) values ( ?, 'xyzzy', null)");
0745: for (int i = 0; i < 10; i++) {
0746: assertRowCount(i, _stmt);
0747: pstmt.setInt(1, i);
0748: assertEquals(1, pstmt.executeUpdate());
0749: assertRowCount(i + 1, _stmt);
0750: _conn.commit();
0751: assertRowCount(i + 1, _stmt);
0752: }
0753: pstmt.close();
0754: }
0755:
0756: public void testColumnsByName() throws Exception {
0757: _stmt
0758: .execute("create table foo ( col1 int, col2 float, col3 varchar(10), col4 varchar(10), col5 boolean )");
0759: _stmt
0760: .executeUpdate("insert into foo values ( 17, 3.14159, 'seventeen', null, true )");
0761: _rset = _stmt
0762: .executeQuery("select col1, col2, col3, col4, col5 from foo");
0763: assertEquals(1, _rset.findColumn("COL1"));
0764: assertEquals(2, _rset.findColumn("COL2"));
0765: assertEquals(3, _rset.findColumn("COL3"));
0766: assertEquals(4, _rset.findColumn("COL4"));
0767: assertEquals(5, _rset.findColumn("COL5"));
0768:
0769: try {
0770: // since next() has not yet been called, this should fail
0771: _rset.getInt("COL1");
0772: fail("Expected SQLException");
0773: } catch (SQLException e) {
0774: // expected
0775: }
0776:
0777: assertTrue(_rset.next());
0778: assertEquals(17, _rset.getInt("COL1"));
0779: assertEquals(17, _rset.getShort("COL1"));
0780: assertEquals(17, _rset.getLong("COL1"));
0781: assertEquals(17, _rset.getByte("COL1"));
0782: assertEquals(3.14159, _rset.getFloat("COL2"), 0.000001);
0783: assertEquals(3.14159, _rset.getDouble("COL2"), 0.000001);
0784: assertEquals("seventeen", _rset.getString("COL3"));
0785: assertEquals("seventeen", _rset.getObject("COL3"));
0786: assertEquals(0, _rset.getInt("COL4"));
0787: assertEquals(0, _rset.getShort("COL4"));
0788: assertEquals(0, _rset.getLong("COL4"));
0789: assertEquals(0, _rset.getByte("COL4"));
0790: assertEquals(0, _rset.getFloat("COL4"), 0.000001);
0791: assertEquals(0, _rset.getDouble("COL4"), 0.000001);
0792: assertNull(_rset.getString("COL4"));
0793: assertNull(_rset.getObject("COL4"));
0794: assertEquals(true, _rset.getBoolean("COL5"));
0795: try {
0796: _rset.findColumn("THIS_COLUMN_DOES_NOT_EXIST");
0797: fail("Expected SQLException");
0798: } catch (SQLException e) {
0799: // expected
0800: }
0801: _rset.close();
0802: }
0803:
0804: public void testResultSetGetStatement() throws Exception {
0805: createAndPopulateDual();
0806: ResultSet rset = _stmt.executeQuery("select * from dual");
0807: assertEquals(_stmt, rset.getStatement());
0808: rset.close();
0809: }
0810:
0811: public void testLiteralSelect() throws Exception {
0812: createAndPopulateDual();
0813: ResultSet rset = _stmt.executeQuery("select 1,2,3 from dual");
0814: assertTrue(rset.next());
0815: assertEquals(2, rset.getInt(2));
0816: rset.close();
0817: }
0818:
0819: public void testCastAsInSelect() throws Exception {
0820: Statement stmt = _conn.createStatement();
0821: stmt
0822: .execute("CREATE TABLE text1 (Id int, Text1 varchar(4), Category int )");
0823: stmt
0824: .execute("CREATE TABLE text2 (Id varchar(1), Text2 varchar(4), Category varchar(1))");
0825: stmt.executeUpdate("INSERT INTO text2 Values(1, 'Mike', 0)");
0826: stmt.executeUpdate("INSERT INTO text2 Values(2, 'John', 0)");
0827:
0828: assertEquals(
0829: 2,
0830: stmt
0831: .executeUpdate("insert into text1 select cast(id as int) id, "
0832: + " text2, cast(Category as int) cat from text2"));
0833:
0834: ResultSet rset = _stmt.executeQuery("select * from text1");
0835:
0836: assertTrue(rset.next());
0837: assertEquals(1, rset.getInt(1));
0838: assertEquals("Mike", rset.getString(2));
0839: assertEquals(0, rset.getInt(3));
0840:
0841: assertTrue(rset.next());
0842: assertEquals(2, rset.getInt(1));
0843: assertEquals("John", rset.getString(2));
0844: assertEquals(0, rset.getInt(3));
0845:
0846: assertTrue(!rset.next());
0847:
0848: try {
0849: stmt.executeQuery("select cast(id as bogus) from text2");
0850: fail("Exception expected: bad datatype");
0851: } catch (Exception e) {
0852: // expected
0853: }
0854:
0855: rset.close();
0856: stmt.close();
0857: }
0858:
0859: public void testBasicGroupBy() throws Exception {
0860: Statement stmt = _conn.createStatement();
0861: stmt
0862: .execute("create table orders ( who varchar(10), what varchar(10), cost integer)");
0863: assertEquals(
0864: 1,
0865: stmt
0866: .executeUpdate("insert into orders values ( 'Joe', 'Book', 10 )"));
0867: assertEquals(
0868: 1,
0869: stmt
0870: .executeUpdate("insert into orders values ( 'Joe', 'Book', 20 )"));
0871: assertEquals(
0872: 1,
0873: stmt
0874: .executeUpdate("insert into orders values ( 'Joe', 'CD', 20 )"));
0875: assertEquals(
0876: 1,
0877: stmt
0878: .executeUpdate("insert into orders values ( 'Jane', 'Book', 20 )"));
0879: assertEquals(
0880: 1,
0881: stmt
0882: .executeUpdate("insert into orders values ( 'Jane', 'CD', 10 )"));
0883: {
0884: ResultSet rset = stmt
0885: .executeQuery("select who, sum(cost) from orders group by who order by who");
0886: assertTrue(rset.next());
0887: assertEquals("Jane", rset.getString(1));
0888: assertEquals(30, rset.getInt(2));
0889: assertTrue(rset.next());
0890: assertEquals("Joe", rset.getString(1));
0891: assertEquals(50, rset.getInt(2));
0892: assertTrue(!rset.next());
0893: rset.close();
0894: }
0895: {
0896: ResultSet rset = stmt
0897: .executeQuery("select what, sum(cost) from orders "
0898: + " group by what order by what");
0899: assertTrue(rset.next());
0900: assertEquals("Book", rset.getString(1));
0901: assertEquals(50, rset.getInt(2));
0902: assertTrue(rset.next());
0903: assertEquals("CD", rset.getString(1));
0904: assertEquals(30, rset.getInt(2));
0905: assertTrue(!rset.next());
0906: rset.close();
0907: }
0908: {
0909: ResultSet rset = stmt
0910: .executeQuery("select cost, count(*) from"
0911: + " orders group by cost order by cost desc");
0912: assertTrue(rset.next());
0913: assertEquals(20, rset.getInt(1));
0914: assertEquals(3, rset.getInt(2));
0915: assertTrue(rset.next());
0916: assertEquals(10, rset.getInt(1));
0917: assertEquals(2, rset.getInt(2));
0918: assertTrue(!rset.next());
0919: rset.close();
0920: }
0921: {
0922: ResultSet rset = stmt
0923: .executeQuery("select S.what, S.totalcost from"
0924: + " (select what, sum(cost) totalcost from orders "
0925: + "group by what order by what) AS S");
0926: assertTrue(rset.next());
0927: assertEquals("Book", rset.getString(1));
0928: assertEquals(50, rset.getInt(2));
0929: assertTrue(rset.next());
0930: assertEquals("CD", rset.getString(1));
0931: assertEquals(30, rset.getInt(2));
0932: assertTrue(!rset.next());
0933: rset.close();
0934: }
0935:
0936: {
0937: try {
0938: stmt.executeQuery("select cost, count(*) from orders");
0939: fail("Expected Exception");
0940: } catch (Exception ex) {
0941: // expected
0942: }
0943:
0944: try {
0945: stmt
0946: .executeQuery("select count(*), who || what from orders");
0947: fail("Expected Exception");
0948: } catch (Exception ex) {
0949: // expected
0950: }
0951:
0952: try {
0953: stmt
0954: .executeQuery("select who || what, sum(cost) from orders group by who order by who");
0955: fail("Expected Exception");
0956: } catch (Exception ex) {
0957: // expected
0958: }
0959:
0960: try {
0961: stmt
0962: .executeQuery("select what, sum(cost) from orders group by who order by who");
0963: fail("Expected Exception");
0964: } catch (Exception ex) {
0965: // expected
0966: }
0967:
0968: try {
0969: stmt
0970: .executeQuery("select what, cost from orders group by what");
0971: fail("Expected Exception");
0972: } catch (Exception ex) {
0973: // expected
0974: }
0975: }
0976:
0977: {
0978: ResultSet rset = stmt
0979: .executeQuery("select sum(subtotal), count(*) from (select sum(cost) subtotal, count(*) subcount from orders group by what order by what) AS S");
0980: assertTrue(rset.next());
0981: assertEquals(80, rset.getInt(1));
0982: assertEquals(2, rset.getInt(2));
0983: assertTrue(!rset.next());
0984: rset.close();
0985: }
0986:
0987: {
0988: ResultSet rset = stmt
0989: .executeQuery("select S.totalcost, S.totalcount from (select sum(cost) totalcost, count(*) totalcount from orders) AS S");
0990: assertTrue(rset.next());
0991: assertEquals(80, rset.getInt(1));
0992: assertEquals(5, rset.getInt(2));
0993: assertTrue(!rset.next());
0994: rset.close();
0995: }
0996:
0997: stmt.close();
0998: }
0999:
1000: public void testGroupByWithWhere() throws Exception {
1001: Statement stmt = _conn.createStatement();
1002: stmt
1003: .execute("CREATE TABLE text1 (Id int, Text1 varchar(5), Category int, PRIMARY KEY ( Id ) )");
1004: stmt.execute("CREATE INDEX text1_text ON text1(text1)");
1005: stmt.executeUpdate("INSERT INTO text1 Values(1, 'Mike', 0)");
1006: stmt.executeUpdate("INSERT INTO text1 Values(2, 'John', 0)");
1007: stmt.executeUpdate("INSERT INTO text1 Values(3, 'Bill', 1)");
1008: stmt.executeUpdate("INSERT INTO text1 Values(4, 'dave', 0)");
1009: stmt.executeUpdate("INSERT INTO text1 Values(7, 'John', 1)");
1010:
1011: ResultSet rset = stmt
1012: .executeQuery("SELECT sum(id), text1 FROM text1 where Category=0 group BY text1");
1013:
1014: assertTrue(rset.next());
1015: assertEquals(2, rset.getInt(1));
1016: assertEquals("John", rset.getString(2));
1017:
1018: assertTrue(rset.next());
1019: assertEquals(1, rset.getInt(1));
1020: assertEquals("Mike", rset.getString(2));
1021:
1022: assertTrue(rset.next());
1023: assertEquals(4, rset.getInt(1));
1024: assertEquals("dave", rset.getString(2));
1025:
1026: assertTrue(!rset.next());
1027:
1028: }
1029:
1030: public void testDropSysIndex() throws Exception {
1031: Statement stmt = _conn.createStatement();
1032: stmt
1033: .execute("CREATE TABLE text1 (Id int, Text1 Varchar(5), Category int, PRIMARY KEY ( Id ) )");
1034:
1035: ResultSet rset = stmt
1036: .executeQuery("select index_name from AXION_INDEX_INFO where table_name = 'TEXT1'");
1037:
1038: assertTrue(rset.next());
1039: String indexName = rset.getString(1);
1040: assertTrue(!rset.next());
1041:
1042: try {
1043: stmt.execute("DROP INDEX " + indexName);
1044: fail("Expected Exception SYS generated Index can't be droped");
1045: } catch (Exception e) {
1046: // expected
1047: }
1048:
1049: // make sure the index still exist.
1050: rset = stmt
1051: .executeQuery("select index_name from AXION_INDEX_INFO where table_name = 'TEXT1'");
1052:
1053: assertTrue(rset.next());
1054: assertEquals(indexName, rset.getString(1));
1055: assertTrue(!rset.next());
1056:
1057: }
1058:
1059: public void testGroupByWithoutWhere() throws Exception {
1060: Statement stmt = _conn.createStatement();
1061: stmt
1062: .execute("CREATE TABLE text1 (Id int, Text1 Varchar(5), Category int, PRIMARY KEY ( Id ) )");
1063: stmt.execute("CREATE INDEX text_text ON text1(text1)");
1064: stmt.executeUpdate("INSERT INTO text1 Values(1, 'Mike', 0)");
1065: stmt.executeUpdate("INSERT INTO text1 Values(2, 'John', 0)");
1066: stmt.executeUpdate("INSERT INTO text1 Values(3, 'Bill', 1)");
1067: stmt.executeUpdate("INSERT INTO text1 Values(4, 'dave', 0)");
1068: stmt.executeUpdate("INSERT INTO text1 Values(7, 'John', 1)");
1069:
1070: ResultSet rset = stmt
1071: .executeQuery("SELECT sum(id), text1 FROM text1 group BY text1");
1072:
1073: assertTrue(rset.next());
1074: assertEquals(3, rset.getInt(1));
1075: assertEquals("Bill", rset.getString(2));
1076:
1077: assertTrue(rset.next());
1078: assertEquals(9, rset.getInt(1));
1079: assertEquals("John", rset.getString(2));
1080:
1081: assertTrue(rset.next());
1082: assertEquals(1, rset.getInt(1));
1083: assertEquals("Mike", rset.getString(2));
1084:
1085: assertTrue(rset.next());
1086: assertEquals(4, rset.getInt(1));
1087: assertEquals("dave", rset.getString(2));
1088:
1089: assertTrue(!rset.next());
1090: }
1091:
1092: public void testOrderByWithWhere() throws Exception {
1093: Statement stmt = _conn.createStatement();
1094: stmt
1095: .execute("CREATE TABLE text1 (Id int, Text1 varchar(5), Category int, PRIMARY KEY ( Id ) )");
1096: stmt.execute("CREATE INDEX text_text ON text1(text1)");
1097: stmt.executeUpdate("INSERT INTO text1 Values(1, 'Mike', 0)");
1098: stmt.executeUpdate("INSERT INTO text1 Values(2, 'John', 0)");
1099: stmt.executeUpdate("INSERT INTO text1 Values(3, 'Bill', 1)");
1100: stmt.executeUpdate("INSERT INTO text1 Values(4, 'dave', 0)");
1101: stmt.executeUpdate("INSERT INTO text1 Values(7, 'John', 1)");
1102:
1103: ResultSet rset = stmt
1104: .executeQuery("SELECT * FROM text1 WHERE Category = 0 ORDER BY text1");
1105:
1106: assertTrue(rset.next());
1107: assertEquals(2, rset.getInt(1));
1108: assertEquals("John", rset.getString(2));
1109: assertEquals(0, rset.getInt(3));
1110:
1111: assertTrue(rset.next());
1112: assertEquals(1, rset.getInt(1));
1113: assertEquals("Mike", rset.getString(2));
1114: assertEquals(0, rset.getInt(3));
1115:
1116: assertTrue(rset.next());
1117: assertEquals(4, rset.getInt(1));
1118: assertEquals("dave", rset.getString(2));
1119: assertEquals(0, rset.getInt(3));
1120:
1121: assertTrue(!rset.next());
1122: }
1123:
1124: public void testGroupByOrderByWithWhere() throws Exception {
1125: Statement stmt = _conn.createStatement();
1126: stmt
1127: .execute("CREATE TABLE text1 (Id int, Text1 varchar(5), Category int, PRIMARY KEY ( Id ) )");
1128: stmt.execute("CREATE INDEX text_text ON text1(text1)");
1129: stmt.executeUpdate("INSERT INTO text1 Values(1, 'Mike', 0)");
1130: stmt.executeUpdate("INSERT INTO text1 Values(2, 'John', 0)");
1131: stmt.executeUpdate("INSERT INTO text1 Values(3, 'Bill', 1)");
1132: stmt.executeUpdate("INSERT INTO text1 Values(4, 'dave', 0)");
1133: stmt.executeUpdate("INSERT INTO text1 Values(7, 'John', 1)");
1134:
1135: ResultSet rset = stmt
1136: .executeQuery("SELECT sum(id), text1 FROM text1 where Category=0 group BY text1 order by text1");
1137:
1138: assertTrue(rset.next());
1139: assertEquals(2, rset.getInt(1));
1140: assertEquals("John", rset.getString(2));
1141:
1142: assertTrue(rset.next());
1143: assertEquals(1, rset.getInt(1));
1144: assertEquals("Mike", rset.getString(2));
1145:
1146: assertTrue(rset.next());
1147: assertEquals(4, rset.getInt(1));
1148: assertEquals("dave", rset.getString(2));
1149:
1150: assertTrue(!rset.next());
1151:
1152: //desc order
1153: rset = stmt
1154: .executeQuery("SELECT sum(id), text1 FROM text1 where Category=0 group BY text1 order by text1 desc");
1155: assertTrue(rset.next());
1156: assertEquals(4, rset.getInt(1));
1157: assertEquals("dave", rset.getString(2));
1158:
1159: assertTrue(rset.next());
1160: assertEquals(1, rset.getInt(1));
1161: assertEquals("Mike", rset.getString(2));
1162:
1163: assertTrue(rset.next());
1164: assertEquals(2, rset.getInt(1));
1165: assertEquals("John", rset.getString(2));
1166:
1167: assertTrue(!rset.next());
1168: }
1169:
1170: public void testGroupByOrderByWithoutWhere() throws Exception {
1171: Statement stmt = _conn.createStatement();
1172: stmt
1173: .execute("CREATE TABLE text1 (Id int, Text1 varchar(5), Category int, PRIMARY KEY ( Id ) )");
1174: stmt.execute("CREATE INDEX text_text ON text1(text1)");
1175: stmt.executeUpdate("INSERT INTO text1 Values(1, 'Mike', 0)");
1176: stmt.executeUpdate("INSERT INTO text1 Values(2, 'John', 0)");
1177: stmt.executeUpdate("INSERT INTO text1 Values(3, 'Bill', 1)");
1178: stmt.executeUpdate("INSERT INTO text1 Values(4, 'dave', 0)");
1179: stmt.executeUpdate("INSERT INTO text1 Values(7, 'John', 1)");
1180:
1181: ResultSet rset = stmt
1182: .executeQuery("SELECT sum(id), text1 FROM text1 group BY text1 order by text1");
1183:
1184: assertTrue(rset.next());
1185: assertEquals(3, rset.getInt(1));
1186: assertEquals("Bill", rset.getString(2));
1187:
1188: assertTrue(rset.next());
1189: assertEquals(9, rset.getInt(1));
1190: assertEquals("John", rset.getString(2));
1191:
1192: assertTrue(rset.next());
1193: assertEquals(1, rset.getInt(1));
1194: assertEquals("Mike", rset.getString(2));
1195:
1196: assertTrue(rset.next());
1197: assertEquals(4, rset.getInt(1));
1198: assertEquals("dave", rset.getString(2));
1199:
1200: assertTrue(!rset.next());
1201:
1202: //desc order
1203: rset = stmt
1204: .executeQuery("SELECT sum(id), text1 FROM text1 group BY text1 order by text1 desc");
1205:
1206: assertTrue(rset.next());
1207: assertEquals(4, rset.getInt(1));
1208: assertEquals("dave", rset.getString(2));
1209:
1210: assertTrue(rset.next());
1211: assertEquals(1, rset.getInt(1));
1212: assertEquals("Mike", rset.getString(2));
1213:
1214: assertTrue(rset.next());
1215: assertEquals(9, rset.getInt(1));
1216: assertEquals("John", rset.getString(2));
1217:
1218: assertTrue(rset.next());
1219: assertEquals(3, rset.getInt(1));
1220: assertEquals("Bill", rset.getString(2));
1221:
1222: assertTrue(!rset.next());
1223:
1224: }
1225:
1226: private void create_table_x() throws Exception {
1227: Statement stmt = _conn.createStatement();
1228: stmt.execute("drop table if exists x ");
1229: stmt.execute("create table x(id int, name varchar(3))");
1230: assertEquals(1, stmt
1231: .executeUpdate("insert into x values(1,'aaa')"));
1232: assertEquals(1, stmt
1233: .executeUpdate("insert into x values(2,'aaa')"));
1234: assertEquals(1, stmt
1235: .executeUpdate("insert into x values(3,'bbb')"));
1236: assertEquals(1, stmt
1237: .executeUpdate("insert into x values(4,'bbb')"));
1238: stmt.close();
1239: }
1240:
1241: public void testSelectSameColumnTwice() throws Exception {
1242: create_table_x();
1243: Statement stmt = _conn.createStatement();
1244:
1245: ResultSet rset = stmt
1246: .executeQuery("select id colid, (name || 'ss') as colname2, name colname from x");
1247: assertTrue(rset.next());
1248:
1249: rset.close();
1250: stmt.close();
1251: }
1252:
1253: public void testBasicSubSelect() throws Exception {
1254: create_table_x();
1255: Statement stmt = _conn.createStatement();
1256:
1257: // insert...select...
1258: stmt.execute("create table y(id int, name varchar(3))");
1259: assertEquals(4, stmt
1260: .executeUpdate("insert into y select * from x"));
1261:
1262: // exists with sub-select
1263: ResultSet rset = stmt
1264: .executeQuery("select * from x where exists (select id from x)");
1265: assertTrue(rset.next());
1266:
1267: // not exists with sub-select
1268: rset = stmt
1269: .executeQuery("select * from x where not exists (select y.id from y where x.id = y.id)");
1270: assertTrue(!rset.next());
1271:
1272: // not exists with sub-select
1273: rset = stmt
1274: .executeQuery("select * from x where not exists (select id from x)");
1275: assertTrue(!rset.next());
1276:
1277: // in with sub-select
1278: rset = stmt
1279: .executeQuery("select * from x where id in (select id from x)");
1280: assertTrue(rset.next());
1281:
1282: // in with sub-select
1283: rset = stmt
1284: .executeQuery("select * from x where id in (select y.id from y where x.id = y.id)");
1285: assertTrue(rset.next());
1286:
1287: // UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
1288:
1289: // A correlated subquery is a subquery that contains a
1290: // reference to a table that also appears in the outer query
1291:
1292: rset = stmt
1293: .executeQuery("select * from x where id = (select y.id from y where x.id = y.id)");
1294: assertTrue(rset.next());
1295:
1296: // scalar sub-select column visibility test
1297: rset = stmt
1298: .executeQuery(" select x.id, (select (select s.name from y s where y.id = x.id) from y, x where y.id = x.id and y.id = 2) from x");
1299: assertTrue(rset.next());
1300:
1301: // object not found
1302: try {
1303: stmt
1304: .executeUpdate("insert into y select * from x where id = (select y.id from y where xxx.id = y.id)");
1305: fail("Expected SQLException");
1306: } catch (SQLException e) {
1307: assertEquals("Expected Column not found exception",
1308: "42703", e.getSQLState());
1309: }
1310:
1311: // too many values - test
1312: try {
1313: rset = stmt
1314: .executeQuery(" select x.id, (select (select * from y s where y.id = x.id) "
1315: + " from y, x where y.id = x.id and y.id = 2) from x");
1316: rset.next();
1317: rset.getInt(1);
1318: rset.getString(2);
1319: fail("Expected SQLException");
1320: } catch (SQLException e) {
1321: // expected
1322: }
1323:
1324: // single-row subquery returns more than one row - test
1325: try {
1326: rset = stmt
1327: .executeQuery(" select x.id, (select (select s.name from y s where y.id = x.id) from y, x where y.id = x.id and y.id = x.id) from x");
1328: rset.next();
1329: rset.getInt(1);
1330: rset.getString(2);
1331: fail("Expected SQLException");
1332: } catch (SQLException e) {
1333: // expected
1334: }
1335:
1336: rset = stmt
1337: .executeQuery("select * from (select id, name from y) as x1 right outer join "
1338: + " (select id xid from x) as y1 on x1.id = y1.xid");
1339: assertTrue(rset.next());
1340:
1341: rset = stmt
1342: .executeQuery("select * from x where name = (select 'aaa')");
1343: assertTrue(rset.next());
1344:
1345: rset = stmt
1346: .executeQuery("SELECT UPPER((SELECT distinct name FROM x where id =2)) FROM y;");
1347: assertTrue(rset.next());
1348:
1349: // Pretending to be a Table
1350: rset = stmt
1351: .executeQuery("SELECT foo FROM (SELECT 1 AS foo) AS tbl;");
1352: assertTrue(rset.next());
1353:
1354: // sub-select as FromNode
1355: rset = stmt.executeQuery("select * from (select * from x) s");
1356: assertTrue(rset.next());
1357:
1358: // sub-select as FromNode
1359: rset = stmt
1360: .executeQuery("select s.id from (select * from x) s");
1361: assertTrue(rset.next());
1362:
1363: // sub-select as FromNode
1364: rset = stmt
1365: .executeQuery("select * from (select * from x ) s where s.id = 2");
1366: assertTrue(rset.next());
1367:
1368: // sub-select as FromNode
1369: rset = stmt
1370: .executeQuery("select * from (select * from x where id not in (select * from x)) s where s.id = 2");
1371: assertTrue(!rset.next());
1372:
1373: stmt
1374: .executeUpdate("create view v1 as select * from (select * from x)");
1375: rset = stmt
1376: .executeQuery("select * from v1 where v1.id in (select id from x)");
1377: assertTrue(rset.next());
1378:
1379: // not in with sub-select
1380: rset = stmt
1381: .executeQuery("select * from x where id not in (select id from x)");
1382: assertTrue(!rset.next());
1383:
1384: // not in with sub-select
1385: rset = stmt
1386: .executeQuery("select * from x where 10 not in (select id from x)");
1387: assertTrue(rset.next());
1388:
1389: // aggregate function as salar value with sub-select
1390: rset = stmt
1391: .executeQuery("select * from x where id = (select max(id) from x)");
1392: assertTrue(rset.next());
1393:
1394: // Test scalar sub-select visibility in where cond using alias
1395: rset = stmt
1396: .executeQuery("select x.id, (select distinct y.id from y where y.id =2) myid from x where x.id = myid");
1397: assertTrue(rset.next());
1398:
1399: // duplicate column test
1400: try {
1401: stmt.executeUpdate("create view v2 as select * from x,y");
1402: fail("Expected SQLException");
1403: } catch (SQLException e) {
1404: // expected
1405: }
1406:
1407: // ambiguous column test
1408: try {
1409: stmt
1410: .executeUpdate("create view v2 as select id,name from x,y");
1411: fail("Expected SQLException");
1412: } catch (SQLException e) {
1413: // expected
1414: }
1415:
1416: stmt
1417: .executeUpdate("create view v2 as select t.id tid, t.name tname from (select * from v1) t");
1418: rset = stmt
1419: .executeQuery("select v2.tid, v2.tname from v2 where v2.tid in (select id from x)");
1420: assertTrue(rset.next());
1421:
1422: // ambiguous column test
1423: try {
1424: stmt.executeUpdate("insert into y select * from x,y");
1425: fail("Expected SQLException");
1426: } catch (SQLException e) {
1427: // expected
1428: }
1429:
1430: stmt.execute("create table z(id1 int, name1 varchar(3))");
1431: assertEquals(4, stmt
1432: .executeUpdate("insert into z select * from x"));
1433:
1434: // no ambiguous column but too many values
1435: try {
1436: stmt.executeUpdate("insert into y select * from x,z");
1437: fail("Expected SQLException");
1438: } catch (SQLException e) {
1439: // expected
1440: }
1441:
1442: // no ambiguous column but too many values
1443: try {
1444: stmt
1445: .executeUpdate("select (select (select (select 1, 2)))");
1446: rset.next();
1447: rset.getObject(1);
1448: fail("Expected SQLException");
1449: } catch (SQLException e) {
1450: // expected
1451: }
1452:
1453: assertEquals(16, stmt
1454: .executeUpdate("insert into y select x.* from x,z"));
1455: assertEquals(
1456: 16,
1457: stmt
1458: .executeUpdate("insert into y select x.id, x.name from x,z"));
1459: assertEquals(
1460: 16,
1461: stmt
1462: .executeUpdate("insert into y select x.id,z.name1 from x,z"));
1463:
1464: //insert as scalar sub-subselect
1465: assertEquals(
1466: 1,
1467: stmt
1468: .executeUpdate("insert into y values((select x.id from x where id =2), (select x.name from x where x.id=2))"));
1469:
1470: // aggregate function with subselect in insert
1471: assertEquals(
1472: 1,
1473: stmt
1474: .executeUpdate("insert into y values ((select max(id) from x), (select distinct x.name from x where id = (select max(id) from x)))"));
1475:
1476: // test delete with sub-select
1477: rset = stmt.executeQuery("select count(*) from x");
1478: assertTrue(rset.next());
1479: int count = rset.getInt(1);
1480: assertEquals(
1481: count,
1482: stmt
1483: .executeUpdate("delete from x where id in (select id from x)"));
1484: assertEquals(
1485: 0,
1486: stmt
1487: .executeUpdate("delete from x where exists (select id from x)"));
1488:
1489: rset = stmt.executeQuery("select count(*) from y where id = 2");
1490: assertTrue(rset.next());
1491: count = rset.getInt(1);
1492: assertEquals(
1493: count,
1494: stmt
1495: .executeUpdate("delete from y where id = (select distinct id from y where id = 2)"));
1496: assertEquals(
1497: 0,
1498: stmt
1499: .executeUpdate("delete from y where id = (select id from y where id = 2)"));
1500:
1501: rset = stmt.executeQuery("select (select (select (select 1)))");
1502: assertTrue(rset.next());
1503:
1504: rset.close();
1505: stmt.close();
1506: }
1507:
1508: public void testColumnAliasInSelect() throws Exception {
1509: create_table_x();
1510: Statement stmt = _conn.createStatement();
1511:
1512: // insert...select...
1513: stmt.execute("create table y (id int, name varchar(3))");
1514: assertEquals(4, stmt
1515: .executeUpdate("insert into y select * from x"));
1516:
1517: ResultSet rset = stmt
1518: .executeQuery("select id as myid, name as myname from x where myid = 4");
1519: assertTrue(rset.next());
1520:
1521: rset = stmt
1522: .executeQuery("select id as myid, name as myname from x where myid in (select id from x)");
1523: assertTrue(rset.next());
1524:
1525: rset = stmt
1526: .executeQuery("select sum(id) mysum, name myname from x group by x.myname");
1527: assertTrue(rset.next());
1528:
1529: rset = stmt
1530: .executeQuery("select id as myid, name as myname from x order by x.id");
1531: assertTrue(rset.next());
1532:
1533: rset = stmt
1534: .executeQuery("select x.id xid, x.name myname, y.id yid, y.name yname from x left outer join y on(xid = yid)");
1535: assertTrue(rset.next());
1536:
1537: rset = stmt
1538: .executeQuery("select 'aaa' as myname from x where x.name = myname");
1539: assertTrue(rset.next());
1540:
1541: try {
1542: rset = stmt
1543: .executeQuery("select 'AAA' from x where x.name = AAA");
1544: fail("Expected SQLException");
1545: } catch (SQLException e) {
1546: // expected
1547: }
1548:
1549: rset.close();
1550: stmt.close();
1551: }
1552:
1553: public void testBadFunctionAndTableName() throws Exception {
1554: Statement stmt = _conn.createStatement();
1555:
1556: try {
1557: stmt.executeQuery("select bogus('AAA')");
1558: fail("Expected SQLException: invalid function name");
1559: } catch (SQLException e) {
1560: // expected
1561: }
1562:
1563: try {
1564: stmt.executeQuery("select abs()");
1565: fail("Expected SQLException: invalid function name");
1566: } catch (SQLException e) {
1567: // expected
1568: }
1569:
1570: try {
1571: stmt.executeQuery("select S1.id from S1");
1572: fail("Expected SQLException: table not found");
1573: } catch (SQLException e) {
1574: // expected
1575: }
1576:
1577: try {
1578: stmt.executeQuery("select * from S1");
1579: fail("Expected SQLException: table not found");
1580: } catch (SQLException e) {
1581: // expected
1582: }
1583:
1584: stmt.close();
1585: }
1586:
1587: public void test_upsert_via_pstmt() throws Exception {
1588: create_table_x();
1589: Statement stmt = _conn.createStatement();
1590:
1591: // insert...select...
1592: stmt.execute("drop table if exists y ");
1593: stmt.execute("create table y(id int, name varchar(4))");
1594: assertEquals(
1595: 2,
1596: stmt
1597: .executeUpdate("insert into y select * from x where name = 'aaa'"));
1598:
1599: PreparedStatement pstmt = _conn
1600: .prepareStatement("upsert into y as D "
1601: + " using x as S on(S.id = D.id and S.id = ?) "
1602: + " when matched then update set D.name = '_' || S.name when not matched then "
1603: + " insert (D.id, D.name) values (S.id, S.name)");
1604:
1605: pstmt.setInt(1, 1);
1606:
1607: assertEquals(4, pstmt.executeUpdate());
1608: pstmt.close();
1609: stmt.close();
1610: }
1611:
1612: public void testBasicUpsert() throws Exception {
1613: create_table_x();
1614: Statement stmt = _conn.createStatement();
1615:
1616: // insert...select...
1617: stmt.execute("drop table if exists y ");
1618: stmt.execute("create table y(id int, name varchar(3))");
1619: assertEquals(
1620: 2,
1621: stmt
1622: .executeUpdate("insert into y select * from x where name = 'aaa'"));
1623:
1624: // We get two exact row, so merge/upsert will skip it,
1625: // hence we will expect 2 mod count.
1626: assertEquals(
1627: 2,
1628: stmt
1629: .executeUpdate("upsert into y as D using (select id, name from x) as S on(S.id = D.id)"
1630: + " when matched then update set D.name = S.name when not matched then "
1631: + " insert (D.id, D.name) values (S.id, S.name)"));
1632:
1633: stmt.execute("delete from y");
1634: assertEquals(
1635: 4,
1636: stmt
1637: .executeUpdate("merge into y as D using (select id, name from x) as S on(S.id = D.id)"
1638: + " when matched then update set D.name = S.name when not matched then "
1639: + " insert (D.id, D.name) values (S.id, S.name)"));
1640:
1641: stmt.execute("delete from y");
1642: assertEquals(
1643: 4,
1644: stmt
1645: .executeUpdate("merge into y as D using (select id myid, name from x) as S on(S.myid = D.id)"
1646: + " when matched then update set D.name = S.name when not matched then "
1647: + " insert (D.id, D.name) values (myid, S.name)"));
1648:
1649: stmt.execute("delete from y");
1650: assertEquals(
1651: 4,
1652: stmt
1653: .executeUpdate("merge into y as D using (select id myid, name from x) as S on(S.myid = D.id and S.myid = D.id)"
1654: + " when matched then update set D.name = S.name when not matched then "
1655: + " insert (D.id, D.name) values (myid, S.name)"));
1656:
1657: stmt.execute("delete from y");
1658: assertEquals(
1659: 4,
1660: stmt
1661: .executeUpdate("merge into y as D using (select id myid, name from x) as S on(S.myid = D.id and S.myid = 2)"
1662: + " when matched then update set D.name = S.name when not matched then "
1663: + " insert (D.id, D.name) values (myid, S.name)"));
1664:
1665: stmt.execute("delete from y");
1666: assertEquals(
1667: 4,
1668: stmt
1669: .executeUpdate("merge into y as D using x as S on(x.id = D.id)"
1670: + " when matched then update set D.name = S.name when not matched then "
1671: + " insert (y.id, D.name) values (x.id, S.name)"));
1672:
1673: // unstable row set
1674: try {
1675: stmt
1676: .executeUpdate(" merge into y as D using (select x.id, x.name from x,y) as S on(s.id = D.id)"
1677: + " when matched then update set D.name = S.name"
1678: + " when not matched then insert (D.id, D.name) values (s.id, s.name)");
1679: fail("Expected SQLException");
1680: } catch (SQLException e) {
1681: // expected
1682: }
1683:
1684: // inner join in sub-query : shd return zero since y is empty
1685: stmt.execute("delete from y");
1686: assertEquals(
1687: 0,
1688: stmt
1689: .executeUpdate(" merge into y as D using (select x.id, x.name from x,y)"
1690: + " as S on(s.id = D.id)"
1691: + " when matched then update set D.name = S.name"
1692: + " when not matched then insert (D.id, D.name) values (s.id, s.name)"));
1693:
1694: stmt.executeUpdate("insert into y values(5,'fff')");
1695: assertEquals(
1696: 4,
1697: stmt
1698: .executeUpdate(" merge into y as D using (select x.id, x.name from x,y)"
1699: + " as S on(s.id = D.id)"
1700: + " when matched then update set D.name = S.name"
1701: + " when not matched then insert (D.id, D.name) values (s.id, s.name)"));
1702:
1703: // ambiguous column test
1704: try {
1705: stmt
1706: .executeUpdate(" merge into y as D using (select * from x,y) as S on(s.id = D.id)"
1707: + " when matched then update set D.name = S.name"
1708: + " when not matched then insert (D.id, D.name) values (s.id, s.name)");
1709: fail("Expected SQLException");
1710: } catch (SQLException e) {
1711: // expected
1712: }
1713:
1714: // Updates Not allowed for cols used in Merge/Upsert Condition
1715: try {
1716: stmt
1717: .executeUpdate(" merge into y as D using (select x.id, x.name from x,y) as S on(s.id = D.id)"
1718: + " when matched then update set D.name = S.name, D.id = S.id"
1719: + " when not matched then insert (D.id, D.name) values (s.id, s.name)");
1720: fail("Expected SQLException");
1721: } catch (SQLException e) {
1722: // expected
1723: }
1724:
1725: // Issue #: 21
1726: stmt.execute("delete from y");
1727: assertEquals(
1728: 4,
1729: stmt
1730: .executeUpdate("merge into y as D using x as S on(x.id = D.id)"
1731: + " when matched then update set D.name = S.name when not matched then "
1732: + " insert (y.id, D.name) values (x.id, S.name)"));
1733: // if source table have 0 rows
1734: stmt.execute("delete from x");
1735: assertEquals(
1736: 0,
1737: stmt
1738: .executeUpdate("merge into y as D using x as S on(x.id = D.id)"
1739: + " when matched then update set D.name = S.name when not matched then "
1740: + " insert (y.id, D.name) values (x.id, S.name)"));
1741:
1742: try {
1743: stmt
1744: .executeUpdate("merge into y as D using (select id myid, name from x) as S on(S.myid = D.id)"
1745: + " when matched then update set S.name = D.name when not matched then "
1746: + " insert (D.id, D.name) values (myid, S.name)");
1747: fail("Expected SQLException");
1748: } catch (SQLException e) {
1749: // expected
1750: }
1751:
1752: stmt.close();
1753: }
1754:
1755: public void testUpsertExceptionWhenClause() throws Exception {
1756: create_table_x();
1757: Statement stmt = _conn.createStatement();
1758:
1759: // insert...select...
1760: stmt.execute("drop table if exists y ");
1761: stmt.execute("create table y(id int, name varchar(3))");
1762:
1763: stmt.execute("drop table if exists z ");
1764: stmt.execute("create table z(id int, name varchar(3))");
1765:
1766: assertEquals(
1767: 2,
1768: stmt
1769: .executeUpdate("insert into y select * from x where name = 'aaa'"));
1770:
1771: assertEquals(
1772: 2,
1773: stmt
1774: .executeUpdate("upsert into y as D using (select id, name from x) as S on(S.id = D.id)"
1775: + " when matched then update set D.name = S.name when not matched then "
1776: + " insert (D.id, D.name) values (S.id, S.name) "
1777: + " exception when S.id < 3 then Insert into z"));
1778:
1779: stmt.execute("delete from y");
1780: assertEquals(
1781: 2,
1782: stmt
1783: .executeUpdate("merge into y as D using (select id, name from x) as S on(S.id = D.id)"
1784: + " when matched then update set D.name = S.name when not matched then "
1785: + " insert (D.id, D.name) values (S.id, S.name)"
1786: + " exception when S.id < 3 then Insert into z values(S.id, S.name)"));
1787:
1788: stmt.execute("delete from y");
1789: assertEquals(
1790: 2,
1791: stmt
1792: .executeUpdate("merge into y as D using (select id myid, name from x) as S on(S.myid = D.id)"
1793: + " when matched then update set D.name = S.name when not matched then "
1794: + " insert (D.id, D.name) values (myid, S.name)"
1795: + " exception when S.myid < 3 then Insert into z(z.id, z.name) values(S.myid, S.name)"));
1796:
1797: stmt.execute("delete from y");
1798: assertEquals(
1799: 2,
1800: stmt
1801: .executeUpdate("merge into y as D using x as S on(S.id = D.id)"
1802: + " when matched then update set D.name = S.name when not matched then "
1803: + " insert (D.id, D.name) values (S.id, S.name)"
1804: + " exception when S.id < 3 then Insert into z(z.id, z.name) values(S.id, S.name)"));
1805:
1806: }
1807:
1808: public void testBasicUpdateSelect() throws Exception {
1809: Statement stmt = _conn.createStatement();
1810: stmt.execute("drop table if exists emp ");
1811: stmt.execute("drop table if exists tmp ");
1812: stmt.execute("create table emp(id int, name varchar(3))");
1813: stmt.execute("create table tmp(tid int, tname varchar(8))");
1814: assertEquals(1, stmt
1815: .executeUpdate("insert into emp values(1,'aaa')"));
1816: assertEquals(1, stmt
1817: .executeUpdate("insert into emp values(2,'aaa')"));
1818: assertEquals(1, stmt
1819: .executeUpdate("insert into tmp values(1,'bbb')"));
1820: assertEquals(1, stmt
1821: .executeUpdate("insert into tmp values(2,'bbb')"));
1822:
1823: assertEquals(2, stmt.executeUpdate("UPDATE tmp "
1824: + "SET tmp.tname = (S.name || 'Test') "
1825: + "FROM tmp T, emp S WHERE T.tid = S.id"));
1826:
1827: assertResult("aaaTest", "select tname from tmp where tid=1");
1828: assertResult("aaa", "select name from emp where id=1");
1829:
1830: assertEquals(2, stmt.executeUpdate("UPDATE tmp "
1831: + "SET tmp.tname = ('RRRR' || 'Test') "
1832: + "FROM tmp T, emp S WHERE T.tid = S.id"));
1833:
1834: assertResult("RRRRTest", "select tname from tmp where tid=1");
1835: assertResult("aaa", "select name from emp where id=1");
1836:
1837: assertEquals(2, stmt.executeUpdate("UPDATE tmp "
1838: + "SET tmp.tname = 'Test' "
1839: + "FROM tmp T, emp S WHERE T.tid = S.id"));
1840:
1841: assertResult("Test", "select tname from tmp where tid=1");
1842: assertResult("aaa", "select name from emp where id=1");
1843:
1844: // switch the table order in from
1845: assertEquals(2, stmt.executeUpdate("UPDATE tmp "
1846: + "SET tmp.tname = 'Test' "
1847: + "FROM tmp T, emp S WHERE T.tid = S.id"));
1848:
1849: assertResult("Test", "select tname from tmp where tid=1");
1850: assertResult("aaa", "select name from emp where id=1");
1851:
1852: assertEquals(1, stmt.executeUpdate("UPDATE tmp "
1853: + "SET tmp.tname = 'aaa' " + "from tmp where tid=1"));
1854:
1855: assertResult("aaa", "select tname from tmp where tid=1");
1856:
1857: stmt.close();
1858:
1859: }
1860:
1861: public void testBasicUpdateSelect1() throws Exception {
1862: Statement stmt = _conn.createStatement();
1863: stmt.execute("drop table if exists emp ");
1864: stmt.execute("drop table if exists tmp ");
1865: stmt.execute("create table emp(id int, name varchar(3))");
1866: stmt.execute("create table tmp(tid int, tname varchar(7))");
1867: assertEquals(1, stmt
1868: .executeUpdate("insert into emp values(1,'aaa')"));
1869: assertEquals(1, stmt
1870: .executeUpdate("insert into emp values(2,'aaa')"));
1871: assertEquals(1, stmt
1872: .executeUpdate("insert into tmp values(1,'bbb')"));
1873: assertEquals(1, stmt
1874: .executeUpdate("insert into tmp values(2,'bbb')"));
1875:
1876: assertEquals(
1877: 2,
1878: stmt
1879: .executeUpdate("UPDATE tmp SET tmp.tname = (S.name || 'Test') "
1880: + "FROM tmp T right outer join emp S on T.tid = S.id"));
1881:
1882: assertResult("aaaTest", "select tname from tmp where tid=1");
1883: assertResult("aaa", "select name from emp where id=1");
1884: }
1885:
1886: public void testBasicUpdateSelect2() throws Exception {
1887: Statement stmt = _conn.createStatement();
1888: stmt.execute("drop table if exists emp ");
1889: stmt.execute("drop table if exists tmp ");
1890: stmt.execute("create table emp(id int, name varchar(3))");
1891: stmt.execute("create table tmp(tid int, tname varchar(7))");
1892: assertEquals(1, stmt
1893: .executeUpdate("insert into emp values(1,'aaa')"));
1894: assertEquals(1, stmt
1895: .executeUpdate("insert into emp values(2,'aaa')"));
1896: assertEquals(1, stmt
1897: .executeUpdate("insert into tmp values(1,'bbb')"));
1898: assertEquals(1, stmt
1899: .executeUpdate("insert into tmp values(2,'bbb')"));
1900:
1901: assertEquals(2, stmt.executeUpdate("UPDATE tmp "
1902: + "SET tmp.tname = (S.name || 'Test') "
1903: + "FROM tmp T left outer join emp S on T.tid = S.id"));
1904:
1905: assertResult("aaaTest", "select tname from tmp where tid=1");
1906: assertResult("aaa", "select name from emp where id=1");
1907: }
1908:
1909: public void testBasicUpdateSelect3() throws Exception {
1910: Statement stmt = _conn.createStatement();
1911: stmt.execute("drop table if exists emp ");
1912: stmt.execute("drop table if exists tmp ");
1913: stmt.execute("create table emp(id int, name varchar(3))");
1914: stmt.execute("create table tmp(tid int, tname varchar(7))");
1915: assertEquals(1, stmt
1916: .executeUpdate("insert into emp values(1,'aaa')"));
1917: assertEquals(1, stmt
1918: .executeUpdate("insert into emp values(2,'ccc')"));
1919: assertEquals(1, stmt
1920: .executeUpdate("insert into tmp values(1,'bbb')"));
1921: assertEquals(1, stmt
1922: .executeUpdate("insert into tmp values(2,'bbb')"));
1923:
1924: assertEquals(
1925: 2,
1926: stmt
1927: .executeUpdate("UPDATE tmp "
1928: + "SET tmp.tname = (S.name || 'Test') "
1929: + "FROM tmp T right outer join emp S on T.tid = S.id where tmp.tid = S.id"));
1930:
1931: assertResult("aaaTest", "select tname from tmp where tid=1");
1932: assertResult("cccTest", "select tname from tmp where tid=2");
1933: assertResult("aaa", "select name from emp where id=1");
1934: }
1935:
1936: public void testBasicUpdateSelect4() throws Exception {
1937: Statement stmt = _conn.createStatement();
1938: stmt.execute("drop table if exists emp ");
1939: stmt.execute("drop table if exists tmp ");
1940: stmt.execute("create table emp(id int, name varchar(3))");
1941: stmt.execute("create table tmp(tid int, tname varchar(7))");
1942: assertEquals(1, stmt
1943: .executeUpdate("insert into emp values(1,'aaa')"));
1944: assertEquals(1, stmt
1945: .executeUpdate("insert into emp values(2,'ccc')"));
1946: assertEquals(1, stmt
1947: .executeUpdate("insert into tmp values(1,'bbb')"));
1948: assertEquals(1, stmt
1949: .executeUpdate("insert into tmp values(2,'bbb')"));
1950:
1951: assertEquals(
1952: 2,
1953: stmt
1954: .executeUpdate("UPDATE tmp "
1955: + "SET tmp.tname = (S.name || 'Test') "
1956: + "FROM tmp T left outer join emp S on T.tid = S.id where tmp.tid = S.id"));
1957:
1958: assertResult("aaaTest", "select tname from tmp where tid=1");
1959: assertResult("cccTest", "select tname from tmp where tid=2");
1960: assertResult("aaa", "select name from emp where id=1");
1961: }
1962:
1963: public void testBasicUpdateSelect5() throws Exception {
1964: Statement stmt = _conn.createStatement();
1965: stmt.execute("drop table if exists emp ");
1966: stmt.execute("drop table if exists tmp ");
1967: stmt.execute("create table emp(id int, name varchar(3))");
1968: stmt.execute("create table tmp(tid int, tname varchar(7))");
1969: assertEquals(1, stmt
1970: .executeUpdate("insert into emp values(1,'aaa')"));
1971: assertEquals(1, stmt
1972: .executeUpdate("insert into emp values(2,'ccc')"));
1973: assertEquals(1, stmt
1974: .executeUpdate("insert into tmp values(1,'bbb')"));
1975: assertEquals(1, stmt
1976: .executeUpdate("insert into tmp values(2,'bbb')"));
1977:
1978: //wrong table alias T.tid in join on conditon exception expected
1979: try {
1980: stmt
1981: .executeUpdate("UPDATE tmp T "
1982: + "SET T.tname = (S1.name || 'Test') "
1983: + "FROM emp S1 inner join tmp T1 on T.tid = S1.id where T.tid = S1.id");
1984: } catch (SQLException ex) {
1985: //expected
1986: }
1987:
1988: assertEquals(
1989: 2,
1990: stmt
1991: .executeUpdate("UPDATE tmp T "
1992: + "SET T.tname = (S1.name || 'Test') "
1993: + "FROM emp S1 inner join tmp T1 on T1.tid = S1.id where T.tid = S1.id"));
1994:
1995: assertResult("aaaTest", "select tname from tmp where tid=1");
1996: assertResult("cccTest", "select tname from tmp where tid=2");
1997: assertResult("aaa", "select name from emp where id=1");
1998: }
1999:
2000: public void testBasicUpdateSelect6() throws Exception {
2001: Statement stmt = _conn.createStatement();
2002: stmt.execute("drop table if exists emp ");
2003: stmt.execute("drop table if exists tmp ");
2004: stmt.execute("create table emp(id int, name varchar(3))");
2005: stmt.execute("create table tmp(tid int, tname varchar(7))");
2006: assertEquals(1, stmt
2007: .executeUpdate("insert into emp values(1,'aaa')"));
2008: assertEquals(1, stmt
2009: .executeUpdate("insert into emp values(2,'ccc')"));
2010: assertEquals(1, stmt
2011: .executeUpdate("insert into tmp values(1,'bbb')"));
2012: assertEquals(1, stmt
2013: .executeUpdate("insert into tmp values(2,'bbb')"));
2014:
2015: //wrong table alias T.tid in join on conditon exception expected
2016: try {
2017: stmt
2018: .executeUpdate("UPDATE tmp T "
2019: + "SET T.tname = (S1.name || 'Test') "
2020: + "FROM emp S1 inner join tmp T1 on T.tid = S1.id where T.tid = S1.id");
2021: } catch (SQLException ex) {
2022: //expected
2023: }
2024:
2025: assertEquals(
2026: 2,
2027: stmt
2028: .executeUpdate("UPDATE tmp T "
2029: + "SET T.tname = (S1.name || 'Test') "
2030: + "FROM emp S1 inner join tmp T1 on T1.tid = S1.id where T.tid = S1.id and T.tid >= 1"));
2031:
2032: assertResult("aaaTest", "select tname from tmp where tid=1");
2033: assertResult("cccTest", "select tname from tmp where tid=2");
2034: assertResult("aaa", "select name from emp where id=1");
2035: }
2036:
2037: public void testBasicUpdateSelect7() throws Exception {
2038: Statement stmt = _conn.createStatement();
2039: stmt.execute("drop table if exists emp ");
2040: stmt.execute("drop table if exists tmp ");
2041: stmt.execute("create table emp(id int, name varchar(3))");
2042: stmt.execute("create table tmp(tid int, tname varchar(7))");
2043: assertEquals(1, stmt
2044: .executeUpdate("insert into emp values(1,'aaa')"));
2045: assertEquals(1, stmt
2046: .executeUpdate("insert into emp values(2,'ccc')"));
2047: assertEquals(1, stmt
2048: .executeUpdate("insert into tmp values(1,'bbb')"));
2049: assertEquals(1, stmt
2050: .executeUpdate("insert into tmp values(2,'bbb')"));
2051:
2052: //wrong table alias T.tid in join on conditon exception expected
2053: try {
2054: stmt
2055: .executeUpdate("UPDATE tmp T "
2056: + "SET T.tname = (S1.name || 'Test') "
2057: + "FROM emp S1 inner join tmp T1 on T.tid = S1.id where T.tid = S1.id");
2058: } catch (SQLException ex) {
2059: //expected
2060: }
2061:
2062: //test to make sure that all the where condition are applied at LOJ (target LOJ
2063: // source) we
2064: //create in update command, otherwise we will get a FilteringRowIterator
2065: //and a ClassCastException is thrown in update command
2066: assertEquals(
2067: 2,
2068: stmt
2069: .executeUpdate("UPDATE tmp T "
2070: + "SET T.tname = (S1.name || 'Test') "
2071: + "FROM emp S1 inner join tmp T1 on T1.tid = S1.id where T.tid = S1.id and T.tname like 'bbb' "));
2072:
2073: assertResult("aaaTest", "select tname from tmp where tid=1");
2074: assertResult("cccTest", "select tname from tmp where tid=2");
2075: assertResult("aaa", "select name from emp where id=1");
2076: }
2077:
2078: public void testBasicUpdateSelectUsingIndexInnerJoin()
2079: throws Exception {
2080: Statement stmt = _conn.createStatement();
2081: stmt.execute("drop table if exists emp ");
2082: stmt.execute("drop table if exists tmp ");
2083: stmt.execute("create table emp(id int, name varchar(3))");
2084: stmt.execute("create table tmp(tid int, tname varchar(7))");
2085: stmt.execute("create btree index tmp_idx on tmp(tid)");
2086: assertEquals(1, stmt
2087: .executeUpdate("insert into emp values(1,'aaa')"));
2088: assertEquals(1, stmt
2089: .executeUpdate("insert into emp values(2,'aaa')"));
2090: assertEquals(1, stmt
2091: .executeUpdate("insert into tmp values(1,'bbb')"));
2092: assertEquals(1, stmt
2093: .executeUpdate("insert into tmp values(2,'bbb')"));
2094:
2095: assertEquals(2, stmt.executeUpdate("UPDATE tmp "
2096: + "SET tmp.tname = (S.name || 'Test') "
2097: + "FROM tmp T inner join emp S on T.tid = S.id"));
2098:
2099: assertResult("aaaTest", "select tname from tmp where tid=1");
2100: assertResult("aaa", "select name from emp where id=1");
2101: }
2102:
2103: public void testBasicUpdateSelectUsingIndexLeftOuterJoin()
2104: throws Exception {
2105: Statement stmt = _conn.createStatement();
2106: stmt.execute("drop table if exists emp ");
2107: stmt.execute("drop table if exists tmp ");
2108: stmt.execute("create table emp(id int, name varchar(3))");
2109: stmt.execute("create table tmp(tid int, tname varchar(7))");
2110: stmt.execute("create btree index tmp_idx on tmp(tid)");
2111: assertEquals(1, stmt
2112: .executeUpdate("insert into emp values(1,'aaa')"));
2113: assertEquals(1, stmt
2114: .executeUpdate("insert into emp values(2,'aaa')"));
2115: assertEquals(1, stmt
2116: .executeUpdate("insert into tmp values(1,'bbb')"));
2117: assertEquals(1, stmt
2118: .executeUpdate("insert into tmp values(2,'bbb')"));
2119:
2120: assertEquals(2, stmt.executeUpdate("UPDATE tmp "
2121: + "SET tmp.tname = (S.name || 'Test') "
2122: + "FROM tmp T left outer join emp S on T.tid = S.id"));
2123:
2124: assertResult("aaaTest", "select tname from tmp where tid=1");
2125: assertResult("aaa", "select name from emp where id=1");
2126: }
2127:
2128: public void testBasicUpdateSelectUsingIndexRightOuter()
2129: throws Exception {
2130: Statement stmt = _conn.createStatement();
2131: stmt.execute("drop table if exists emp ");
2132: stmt.execute("drop table if exists tmp ");
2133: stmt.execute("create table emp(id int, name varchar(3))");
2134: stmt.execute("create table tmp(tid int, tname varchar(7))");
2135: stmt.execute("create btree index emp_idx on emp(id)");
2136: assertEquals(1, stmt
2137: .executeUpdate("insert into emp values(1,'aaa')"));
2138: assertEquals(1, stmt
2139: .executeUpdate("insert into emp values(2,'aaa')"));
2140: assertEquals(1, stmt
2141: .executeUpdate("insert into tmp values(1,'bbb')"));
2142: assertEquals(1, stmt
2143: .executeUpdate("insert into tmp values(2,'bbb')"));
2144:
2145: assertEquals(2, stmt.executeUpdate("UPDATE tmp "
2146: + "SET tmp.tname = (S.name || 'Test') "
2147: + "FROM tmp T right outer join emp S on T.tid = S.id"));
2148:
2149: assertResult("aaaTest", "select tname from tmp where tid=1");
2150: assertResult("aaa", "select name from emp where id=1");
2151: }
2152:
2153: public void testThreeTableBasicUpdateSelectUsingIndex()
2154: throws Exception {
2155: Statement stmt = _conn.createStatement();
2156: stmt.execute("drop table if exists emp ");
2157: stmt.execute("drop table if exists tmp ");
2158: stmt.execute("create table emp(id int, name varchar(3))");
2159: stmt.execute("create table tmp(tid int, tname varchar(7))");
2160: stmt.execute("create table bmp(bid int, bname varchar(3))");
2161: stmt.execute("create btree index tmp_idx on tmp(tid)");
2162: assertEquals(1, stmt
2163: .executeUpdate("insert into emp values(1,'aaa')"));
2164: assertEquals(1, stmt
2165: .executeUpdate("insert into emp values(2,'aaa')"));
2166: assertEquals(1, stmt
2167: .executeUpdate("insert into tmp values(1,'bbb')"));
2168: assertEquals(1, stmt
2169: .executeUpdate("insert into tmp values(2,'bbb')"));
2170: assertEquals(1, stmt
2171: .executeUpdate("insert into bmp values(1,'ccc')"));
2172: assertEquals(1, stmt
2173: .executeUpdate("insert into bmp values(2,'ccc')"));
2174:
2175: assertEquals(2, stmt.executeUpdate("UPDATE tmp "
2176: + "SET tmp.tname = (S.name || 'Test') "
2177: + "FROM tmp T left outer join emp S on T.tid = S.id "
2178: + "left outer join bmp B on S.id = B.bid"));
2179:
2180: assertResult("aaaTest", "select tname from tmp where tid=1");
2181: assertResult("aaa", "select name from emp where id=1");
2182: }
2183:
2184: public void testThreeTableBasicUpdateSelect() throws Exception {
2185: Statement stmt = _conn.createStatement();
2186: stmt.execute("drop table if exists emp ");
2187: stmt.execute("drop table if exists tmp ");
2188: stmt.execute("create table emp(id int, name varchar(3))");
2189: stmt.execute("create table tmp(tid int, tname varchar(7))");
2190: stmt.execute("create table bmp(bid int, bname varchar(3))");
2191: assertEquals(1, stmt
2192: .executeUpdate("insert into emp values(1,'aaa')"));
2193: assertEquals(1, stmt
2194: .executeUpdate("insert into emp values(2,'aaa')"));
2195: assertEquals(1, stmt
2196: .executeUpdate("insert into tmp values(1,'bbb')"));
2197: assertEquals(1, stmt
2198: .executeUpdate("insert into tmp values(2,'bbb')"));
2199: assertEquals(1, stmt
2200: .executeUpdate("insert into bmp values(1,'ccc')"));
2201: assertEquals(1, stmt
2202: .executeUpdate("insert into bmp values(2,'ccc')"));
2203:
2204: assertEquals(2, stmt.executeUpdate("UPDATE tmp "
2205: + "SET tmp.tname = (S.name || 'Test') "
2206: + "FROM tmp T left outer join emp S on T.tid = S.id "
2207: + "left outer join bmp B on S.id = B.bid"));
2208:
2209: assertResult("aaaTest", "select tname from tmp where tid=1");
2210: assertResult("aaa", "select name from emp where id=1");
2211: }
2212:
2213: public void testGeneratedColumn() throws Exception {
2214: Statement stmt = _conn.createStatement();
2215:
2216: stmt.execute("drop table if exists emp ");
2217: stmt
2218: .execute("create table emp(id int, name varchar(3), id_name"
2219: + " generated always as (id || name) )");
2220: assertEquals(1, stmt
2221: .executeUpdate("insert into emp values(1,'aaa')"));
2222:
2223: ResultSet rset = stmt
2224: .executeQuery("SELECT id, name, id_name FROM emp");
2225:
2226: assertTrue(rset.next());
2227: assertEquals(1, rset.getInt(1));
2228: assertEquals("aaa", rset.getString(2));
2229: assertEquals("1aaa", rset.getString(3));
2230: assertTrue(!rset.next());
2231:
2232: assertEquals(1, stmt.executeUpdate("UPDATE emp "
2233: + "SET emp.name = 'bbb'"));
2234: rset = stmt.executeQuery("SELECT id, name, id_name FROM emp");
2235:
2236: assertTrue(rset.next());
2237: assertEquals(1, rset.getInt(1));
2238: assertEquals("bbb", rset.getString(2));
2239: assertEquals("1bbb", rset.getString(3));
2240: assertTrue(!rset.next());
2241:
2242: assertEquals(
2243: 1,
2244: stmt
2245: .executeUpdate("alter table emp"
2246: + " add column name_id generated always as (name || id)"));
2247:
2248: rset = stmt
2249: .executeQuery("SELECT id, name, id_name, name_id FROM emp");
2250:
2251: assertTrue(rset.next());
2252: assertEquals(1, rset.getInt(1));
2253: assertEquals("bbb", rset.getString(2));
2254: assertEquals("1bbb", rset.getString(3));
2255: assertEquals("bbb1", rset.getString(4));
2256: assertTrue(!rset.next());
2257:
2258: assertEquals(1, stmt.executeUpdate("UPDATE emp "
2259: + "SET emp.name = 'ccc'"));
2260:
2261: rset = stmt
2262: .executeQuery("SELECT id, name, id_name, name_id FROM emp");
2263:
2264: assertTrue(rset.next());
2265: assertEquals(1, rset.getInt(1));
2266: assertEquals("ccc", rset.getString(2));
2267: assertEquals("1ccc", rset.getString(3));
2268: assertEquals("ccc1", rset.getString(4));
2269: assertTrue(!rset.next());
2270:
2271: assertEquals(1, stmt
2272: .executeUpdate("insert into emp values(2,'ddd')"));
2273:
2274: rset = stmt
2275: .executeQuery("SELECT id, name, id_name, name_id FROM emp");
2276:
2277: assertTrue(rset.next());
2278: assertEquals(1, rset.getInt(1));
2279: assertEquals("ccc", rset.getString(2));
2280: assertEquals("1ccc", rset.getString(3));
2281: assertEquals("ccc1", rset.getString(4));
2282:
2283: assertTrue(rset.next());
2284: assertEquals(2, rset.getInt(1));
2285: assertEquals("ddd", rset.getString(2));
2286: assertEquals("2ddd", rset.getString(3));
2287: assertEquals("ddd2", rset.getString(4));
2288: assertTrue(!rset.next());
2289: }
2290:
2291: public void testVariousUnsupported() throws Exception {
2292: createTableFoo();
2293: populateTableFoo();
2294: ResultSet rset = _stmt.executeQuery("select NUM from FOO");
2295: try {
2296: rset.getArray(1);
2297: fail("Expected SQLException");
2298: } catch (SQLException e) {
2299: // expected
2300: }
2301: try {
2302: rset.getArray("NUM");
2303: fail("Expected SQLException");
2304: } catch (SQLException e) {
2305: // expected
2306: }
2307: try {
2308: rset.getCursorName();
2309: fail("Expected SQLException");
2310: } catch (SQLException e) {
2311: // expected
2312: }
2313: rset.setFetchDirection(ResultSet.FETCH_UNKNOWN);
2314: assertEquals(ResultSet.FETCH_UNKNOWN, rset.getFetchDirection());
2315: rset.setFetchSize(0);
2316: assertEquals(0, rset.getFetchSize());
2317: try {
2318: rset.getObject(1, (Map) null);
2319: fail("Expected SQLException");
2320: } catch (SQLException e) {
2321: // expected
2322: }
2323:
2324: try {
2325: rset.getObject("NUM", (Map) null);
2326: fail("Expected SQLException");
2327: } catch (SQLException e) {
2328: // expected
2329: }
2330: try {
2331: rset.getRef(1);
2332: fail("Expected SQLException");
2333: } catch (SQLException e) {
2334: // expected
2335: }
2336: try {
2337: rset.getRef("NUM");
2338: fail("Expected SQLException");
2339: } catch (SQLException e) {
2340: // expected
2341: }
2342: try {
2343: rset.moveToCurrentRow();
2344: fail("Expected SQLException");
2345: } catch (SQLException e) {
2346: // expected
2347: }
2348: try {
2349: rset.moveToInsertRow();
2350: fail("Expected SQLException");
2351: } catch (SQLException e) {
2352: // expected
2353: }
2354: try {
2355: rset.refreshRow();
2356: fail("Expected SQLException");
2357: } catch (SQLException e) {
2358: // expected
2359: }
2360: try {
2361: rset.deleteRow();
2362: fail("Expected SQLException");
2363: } catch (SQLException e) {
2364: // expected
2365: }
2366: assertTrue(!rset.rowDeleted());
2367: try {
2368: rset.updateRow();
2369: fail("Expected SQLException");
2370: } catch (SQLException e) {
2371: // expected
2372: }
2373: assertTrue(!rset.rowUpdated());
2374: try {
2375: rset.insertRow();
2376: fail("Expected SQLException");
2377: } catch (SQLException e) {
2378: // expected
2379: }
2380: assertTrue(!rset.rowInserted());
2381: try {
2382: rset.updateAsciiStream(1, null, 1);
2383: fail("Expected SQLException");
2384: } catch (SQLException e) {
2385: // expected
2386: }
2387: try {
2388: rset.updateAsciiStream("NUM", null, 1);
2389: fail("Expected SQLException");
2390: } catch (SQLException e) {
2391: // expected
2392: }
2393: try {
2394: rset.updateBigDecimal(1, null);
2395: fail("Expected SQLException");
2396: } catch (SQLException e) {
2397: // expected
2398: }
2399: try {
2400: rset.updateBigDecimal("NUM", null);
2401: fail("Expected SQLException");
2402: } catch (SQLException e) {
2403: // expected
2404: }
2405: try {
2406: rset.updateBinaryStream(1, null, 1);
2407: fail("Expected SQLException");
2408: } catch (SQLException e) {
2409: // expected
2410: }
2411: try {
2412: rset.updateBinaryStream("NUM", null, 1);
2413: fail("Expected SQLException");
2414: } catch (SQLException e) {
2415: // expected
2416: }
2417: try {
2418: rset.updateBoolean(1, true);
2419: fail("Expected SQLException");
2420: } catch (SQLException e) {
2421: // expected
2422: }
2423: try {
2424: rset.updateBoolean("NUM", true);
2425: fail("Expected SQLException");
2426: } catch (SQLException e) {
2427: // expected
2428: }
2429: try {
2430: rset.updateByte(1, (byte) 1);
2431: fail("Expected SQLException");
2432: } catch (SQLException e) {
2433: // expected
2434: }
2435: try {
2436: rset.updateByte("NUM", (byte) 1);
2437: fail("Expected SQLException");
2438: } catch (SQLException e) {
2439: // expected
2440: }
2441: try {
2442: rset.updateShort(1, (short) 1);
2443: fail("Expected SQLException");
2444: } catch (SQLException e) {
2445: // expected
2446: }
2447: try {
2448: rset.updateShort("NUM", (short) 1);
2449: fail("Expected SQLException");
2450: } catch (SQLException e) {
2451: // expected
2452: }
2453: try {
2454: rset.updateInt(1, 1);
2455: fail("Expected SQLException");
2456: } catch (SQLException e) {
2457: // expected
2458: }
2459: try {
2460: rset.updateInt("NUM", 1);
2461: fail("Expected SQLException");
2462: } catch (SQLException e) {
2463: // expected
2464: }
2465: try {
2466: rset.updateLong(1, 1);
2467: fail("Expected SQLException");
2468: } catch (SQLException e) {
2469: // expected
2470: }
2471: try {
2472: rset.updateLong("NUM", 1);
2473: fail("Expected SQLException");
2474: } catch (SQLException e) {
2475: // expected
2476: }
2477: try {
2478: rset.updateFloat(1, 1);
2479: fail("Expected SQLException");
2480: } catch (SQLException e) {
2481: // expected
2482: }
2483: try {
2484: rset.updateFloat("NUM", 1);
2485: fail("Expected SQLException");
2486: } catch (SQLException e) {
2487: // expected
2488: }
2489: try {
2490: rset.updateNull(1);
2491: fail("Expected SQLException");
2492: } catch (SQLException e) {
2493: // expected
2494: }
2495: try {
2496: rset.updateNull("NUM");
2497: fail("Expected SQLException");
2498: } catch (SQLException e) {
2499: // expected
2500: }
2501: try {
2502: rset.updateString(1, null);
2503: fail("Expected SQLException");
2504: } catch (SQLException e) {
2505: // expected
2506: }
2507: try {
2508: rset.updateString("NUM", null);
2509: fail("Expected SQLException");
2510: } catch (SQLException e) {
2511: // expected
2512: }
2513: try {
2514: rset.updateDate(1, null);
2515: fail("Expected SQLException");
2516: } catch (SQLException e) {
2517: // expected
2518: }
2519: try {
2520: rset.updateDate("NUM", null);
2521: fail("Expected SQLException");
2522: } catch (SQLException e) {
2523: // expected
2524: }
2525: try {
2526: rset.updateTimestamp(1, null);
2527: fail("Expected SQLException");
2528: } catch (SQLException e) {
2529: // expected
2530: }
2531: try {
2532: rset.updateTimestamp("NUM", null);
2533: fail("Expected SQLException");
2534: } catch (SQLException e) {
2535: // expected
2536: }
2537: try {
2538: rset.updateTime(1, null);
2539: fail("Expected SQLException");
2540: } catch (SQLException e) {
2541: // expected
2542: }
2543: try {
2544: rset.updateTime("NUM", null);
2545: fail("Expected SQLException");
2546: } catch (SQLException e) {
2547: // expected
2548: }
2549: try {
2550: rset.updateObject(1, null);
2551: fail("Expected SQLException");
2552: } catch (SQLException e) {
2553: // expected
2554: }
2555: try {
2556: rset.updateObject("NUM", null);
2557: fail("Expected SQLException");
2558: } catch (SQLException e) {
2559: // expected
2560: }
2561: try {
2562: rset.updateObject("NUM", null, 2);
2563: fail("Expected SQLException");
2564: } catch (SQLException e) {
2565: // expected
2566: }
2567: try {
2568: rset.updateBlob(1, (Blob) null);
2569: fail("Expected SQLException");
2570: } catch (SQLException e) {
2571: // expected
2572: }
2573: try {
2574: rset.updateBlob("NUM", (Blob) null);
2575: fail("Expected SQLException");
2576: } catch (SQLException e) {
2577: // expected
2578: }
2579: try {
2580: rset.updateClob(1, (Clob) null);
2581: fail("Expected SQLException");
2582: } catch (SQLException e) {
2583: // expected
2584: }
2585: try {
2586: rset.updateClob("NUM", (Clob) null);
2587: fail("Expected SQLException");
2588: } catch (SQLException e) {
2589: // expected
2590: }
2591: try {
2592: rset.updateRef(1, null);
2593: fail("Expected SQLException");
2594: } catch (SQLException e) {
2595: // expected
2596: }
2597: try {
2598: rset.updateRef("NUM", null);
2599: fail("Expected SQLException");
2600: } catch (SQLException e) {
2601: // expected
2602: }
2603: try {
2604: rset.updateArray(1, null);
2605: fail("Expected SQLException");
2606: } catch (SQLException e) {
2607: // expected
2608: }
2609: try {
2610: rset.updateArray("NUM", null);
2611: fail("Expected SQLException");
2612: } catch (SQLException e) {
2613: // expected
2614: }
2615: }
2616:
2617: private void assertRowCount(int i, Statement stmt)
2618: throws SQLException {
2619: ResultSet rset = stmt.executeQuery("select count(*) from foo");
2620: assertTrue(rset.next());
2621: assertEquals(i, rset.getInt(1));
2622: assertTrue(!rset.next());
2623: rset.close();
2624: }
2625:
2626: public void testQuotedIdentifiers() throws Throwable {
2627: // Expect statement using reserved word as table identifier to fail.
2628: try {
2629: _stmt.execute("create table table (a int, b varchar(30))");
2630: fail("Expected SQLException upon using reserved word as table identifier.");
2631: } catch (SQLException ignore) {
2632: // keep going
2633: }
2634:
2635: // Expect statement using reserved word as column identifier to fail.
2636: try {
2637: _stmt
2638: .execute("create table table_1 (asc int, b varchar(30))");
2639: fail("Expected SQLException upon using reserved word as column identifier.");
2640: } catch (SQLException ignore) {
2641: // keep going
2642: }
2643:
2644: // Expect statement using reserved word as table or column aliases to fail.
2645: _stmt.execute("create table table_1 (a int, b varchar(30))");
2646: try {
2647: _stmt.execute("select drop.a from table_1 drop");
2648: fail("Expected SQLException upon using reserved word as table alias.");
2649: } catch (SQLException ignore) {
2650: // keep going
2651: }
2652:
2653: try {
2654: _stmt.execute("select a as drop from table_1");
2655: fail("Expected SQLException upon using reserved word as table alias.");
2656: } catch (SQLException ignore) {
2657: // keep going
2658: }
2659:
2660: _stmt.execute("drop table table_1");
2661:
2662: // Expect statement with incompletely quoted identifer to fail.
2663: try {
2664: _stmt
2665: .execute("create table \"table (a int, b varchar(30))");
2666: fail("Expected SQLException upon using incompletely quoted identifier.");
2667: } catch (SQLException ignore) {
2668: // keep going
2669: }
2670:
2671: // Expect statement with invalid characters in quoted identifer to fail.
2672: try {
2673: _stmt
2674: .execute("create table \"_!@#$%\" (a int, b varchar(30))");
2675: fail("Expected SQLException upon using invalid quoted identifier.");
2676: } catch (SQLException ignore) {
2677: // keep going
2678: }
2679:
2680: // Create new table "table"
2681: _stmt
2682: .execute("create table \"table\" ( \"asc\" int not null, \"desc\" varchar(30))");
2683:
2684: // Insert data into "table"
2685: assertEquals(
2686: "Could not insert using quoted identifier.",
2687: 1,
2688: _stmt
2689: .executeUpdate("insert into \"table\" values (1, 'first')"));
2690: assertEquals(
2691: "Could not insert using quoted identifier.",
2692: 1,
2693: _stmt
2694: .executeUpdate("insert into \"table\" values (2, 'second')"));
2695:
2696: // Expect statement using reserved word as table or column aliases to fail.
2697: _stmt.execute("create table table_1 (a int, b varchar(30))");
2698: try {
2699: _stmt.execute("select drop.a from table_1 drop");
2700: fail("Expected SQLException upon using reserved word as table alias.");
2701: } catch (SQLException ignore) {
2702: // keep going
2703: }
2704:
2705: try {
2706: _stmt.execute("select a as drop from table_1");
2707: fail("Expected SQLException upon using reserved word as table alias.");
2708: } catch (SQLException ignore) {
2709: // keep going
2710: }
2711:
2712: // Create new table "sequence"
2713: _stmt
2714: .execute("create table \"sequence\" ( \"asc\" int, \"desc\" varchar(30))");
2715:
2716: // Insert-Select into "sequence" from "table"
2717: assertEquals(
2718: "Could not execute insert-select using quoted identifiers.",
2719: 2,
2720: _stmt
2721: .executeUpdate("insert into \"sequence\" select \"table\".\"asc\", "
2722: + "\"table\".\"desc\" from \"table\""));
2723:
2724: // Select using inner join between "table" and "sequence"
2725: ResultSet rs = _stmt
2726: .executeQuery("select \"table\".\"asc\", \"sequence\".\"desc\" from \"table\" inner join \"sequence\" "
2727: + "on (\"table\".\"asc\" = \"sequence\".\"asc\") order by \"table\".\"asc\" desc");
2728:
2729: // Note: order by desc...
2730: assertTrue(
2731: "Could not advance ResultSet as generated from select (inner join) using quoted "
2732: + "identifiers", rs.next());
2733: assertEquals(
2734: "Could not get expected data from select (inner join) using quoted identifiers.",
2735: 2, rs.getInt(1));
2736: assertEquals(
2737: "Could not get expected data from select (inner join) using quoted identifiers.",
2738: "second", rs.getString(2));
2739: assertTrue(
2740: "Could not advance ResultSet as generated from select (inner join) using quoted "
2741: + "identifiers", rs.next());
2742: assertEquals(
2743: "Could not get expected data from select (inner join) using quoted identifiers.",
2744: 1, rs.getInt("ASC"));
2745: assertEquals(
2746: "Could not get expected data from select (inner join) using quoted identifiers.",
2747: "first", rs.getString("DESC"));
2748: assertFalse(
2749: "Expected not to advance ResultSet as generated from select (inner join) using "
2750: + "quoted identifiers", rs.next());
2751:
2752: rs.close();
2753:
2754: // Update "sequence"
2755: assertEquals(
2756: 1,
2757: _stmt
2758: .executeUpdate("update \"sequence\" set \"desc\" = 'KillMe!' where \"asc\" = 2"));
2759: rs = _stmt
2760: .executeQuery("select \"desc\" from \"sequence\" where \"desc\" = 'KillMe!'");
2761: assertTrue(
2762: "Could not advance ResultSet as generated from select after update using quoted "
2763: + "identifiers.", rs.next());
2764: assertEquals(
2765: "Could not get expected data from select after update using quoted identifiers.",
2766: "KillMe!", rs.getString(1));
2767:
2768: // Delete and drop "table"
2769: assertEquals("Could not delete using quoted identifier.", 2,
2770: _stmt.executeUpdate("delete from \"table\""));
2771: assertEquals("Could not drop using quoted identifier.", 0,
2772: _stmt.executeUpdate("drop table \"table\""));
2773: }
2774:
2775: public void testConcurrency() throws Exception {
2776: _stmt.execute("create table foo (id int, name varchar(50))");
2777: ResultSet rset = _stmt.executeQuery("select count(*) from foo");
2778: assertEquals(ResultSet.CONCUR_READ_ONLY, rset.getConcurrency());
2779: }
2780: }
|