001: // jTDS JDBC Driver for Microsoft SQL Server and Sybase
002: // Copyright (C) 2004 The jTDS Project
003: //
004: // This library is free software; you can redistribute it and/or
005: // modify it under the terms of the GNU Lesser General Public
006: // License as published by the Free Software Foundation; either
007: // version 2.1 of the License, or (at your option) any later version.
008: //
009: // This library is distributed in the hope that it will be useful,
010: // but WITHOUT ANY WARRANTY; without even the implied warranty of
011: // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
012: // Lesser General Public License for more details.
013: //
014: // You should have received a copy of the GNU Lesser General Public
015: // License along with this library; if not, write to the Free Software
016: // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
017: //
018: package net.sourceforge.jtds.test;
019:
020: import java.sql.Connection;
021: import java.sql.ResultSet;
022: import java.sql.SQLException;
023: import java.sql.Statement;
024:
025: /**
026: * Test case to illustrate use of Cached cursor result set.
027: *
028: * @version 1.0
029: * @author Mike Hutchinson
030: */
031: public class ClientSideCursorTest extends DatabaseTestCase {
032:
033: public ClientSideCursorTest(String name) {
034: super (name);
035: }
036:
037: /**
038: * General test of scrollable cursor functionality.
039: * <p/>
040: * When running on SQL Server this test will exercise MSCursorResultSet.
041: * When running on Sybase this test will exercise CachedResultSet.
042: */
043: public void testCachedCursor() throws Exception {
044: try {
045: dropTable("jTDS_CachedCursorTest");
046: Statement stmt = con.createStatement();
047: stmt
048: .execute("CREATE TABLE jTDS_CachedCursorTest "
049: + "(key1 int NOT NULL, key2 char(4) NOT NULL,"
050: + "data varchar(255))\r\n"
051: + "ALTER TABLE jTDS_CachedCursorTest "
052: + "ADD CONSTRAINT PK_jTDS_CachedCursorTest PRIMARY KEY CLUSTERED"
053: + "( key1, key2)");
054: for (int i = 1; i <= 16; i++) {
055: assertEquals(
056: 1,
057: stmt
058: .executeUpdate("INSERT INTO jTDS_CachedCursorTest VALUES("
059: + i
060: + ", 'XXXX','LINE "
061: + i
062: + "')"));
063: }
064: stmt.close();
065: stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
066: ResultSet.CONCUR_UPDATABLE);
067: ResultSet rs = stmt
068: .executeQuery("SELECT * FROM jTDS_CachedCursorTest ORDER BY key1");
069: assertNotNull(rs);
070: assertEquals(null, stmt.getWarnings());
071: assertTrue(rs.isBeforeFirst());
072: assertTrue(rs.first());
073: assertEquals(1, rs.getInt(1));
074: assertTrue(rs.isFirst());
075: assertTrue(rs.last());
076: assertEquals(16, rs.getInt(1));
077: assertTrue(rs.isLast());
078: assertFalse(rs.next());
079: assertTrue(rs.isAfterLast());
080: rs.beforeFirst();
081: assertTrue(rs.next());
082: assertEquals(1, rs.getInt(1));
083: rs.afterLast();
084: assertTrue(rs.previous());
085: assertEquals(16, rs.getInt(1));
086: assertTrue(rs.absolute(8));
087: assertEquals(8, rs.getInt(1));
088: assertTrue(rs.relative(-1));
089: assertEquals(7, rs.getInt(1));
090: rs.updateString(3, "New line 7");
091: rs.updateRow();
092: // assertTrue(rs.rowUpdated()); // MS API cursors appear not to support this
093: rs.moveToInsertRow();
094: rs.updateInt(1, 17);
095: rs.updateString(2, "XXXX");
096: rs.updateString(3, "LINE 17");
097: rs.insertRow();
098: rs.moveToCurrentRow();
099: rs.last();
100: // assertTrue(rs.rowInserted()); // MS API cursors appear not to support this
101: Statement stmt2 = con.createStatement(
102: ResultSet.TYPE_SCROLL_SENSITIVE,
103: ResultSet.CONCUR_READ_ONLY);
104: ResultSet rs2 = stmt2
105: .executeQuery("SELECT * FROM jTDS_CachedCursorTest ORDER BY key1");
106: rs.updateString(3, "NEW LINE 17");
107: rs.updateRow();
108: assertTrue(rs2.last());
109: assertEquals(17, rs2.getInt(1));
110: assertEquals("NEW LINE 17", rs2.getString(3));
111: rs.deleteRow();
112: rs2.refreshRow();
113: assertTrue(rs2.rowDeleted());
114: rs2.close();
115: stmt2.close();
116: rs.close();
117: stmt.close();
118: } finally {
119: dropTable("jTDS_CachedCursorTest");
120: }
121: }
122:
123: /**
124: * Test support for JDBC 1 style positioned updates with named cursors.
125: * <p/>
126: * When running on SQL Server this test will exercise MSCursorResultSet.
127: * When running on Sybase this test will exercise CachedResultSet.
128: */
129: public void testPositionedUpdate() throws Exception {
130: assertTrue(con.getMetaData().supportsPositionedDelete());
131: assertTrue(con.getMetaData().supportsPositionedUpdate());
132: Statement stmt = con.createStatement();
133: stmt
134: .execute("CREATE TABLE #TESTPOS (id INT primary key, data VARCHAR(255))");
135: for (int i = 1; i < 5; i++) {
136: stmt.execute("INSERT INTO #TESTPOS VALUES(" + i
137: + ", 'This is line " + i + "')");
138: }
139: stmt.setCursorName("curname");
140: ResultSet rs = stmt
141: .executeQuery("SELECT * FROM #TESTPOS FOR UPDATE");
142: Statement stmt2 = con.createStatement();
143: while (rs.next()) {
144: if (rs.getInt(1) == 1) {
145: stmt2
146: .execute("UPDATE #TESTPOS SET data = 'Updated' WHERE CURRENT OF curname");
147: } else if (rs.getInt(1) == 3) {
148: stmt2
149: .execute("DELETE FROM #TESTPOS WHERE CURRENT OF curname");
150: }
151: }
152: rs.close();
153: stmt.setFetchSize(100);
154: rs = stmt.executeQuery("SELECT * FROM #TESTPOS");
155: while (rs.next()) {
156: int id = rs.getInt(1);
157: assertTrue(id != 3); // Should have been deleted
158: if (id == 1) {
159: assertEquals("Updated", rs.getString(2));
160: }
161: }
162: stmt2.close();
163: stmt.close();
164: }
165:
166: /**
167: * Test optimistic updates throw exception if row is changed on disk.
168: * <p/>
169: * When running on SQL Server this test will exercise MSCursorResultSet.
170: * When running on Sybase this test will exercise CachedResultSet.
171: */
172: public void testOptimisticUpdates() throws Exception {
173: Connection con2 = getConnection();
174: try {
175: dropTable("jTDS_CachedCursorTest");
176: Statement stmt = con.createStatement(
177: ResultSet.TYPE_SCROLL_SENSITIVE,
178: ResultSet.CONCUR_UPDATABLE);
179: ResultSet rs;
180: stmt
181: .execute("CREATE TABLE jTDS_CachedCursorTest (id int primary key, data varchar(255))");
182: for (int i = 0; i < 4; i++) {
183: stmt
184: .executeUpdate("INSERT INTO jTDS_CachedCursorTest VALUES("
185: + i + ", 'Table A line " + i + "')");
186: }
187: // Open cursor
188: rs = stmt
189: .executeQuery("SELECT id, data FROM jTDS_CachedCursorTest");
190: Statement stmt2 = con2.createStatement();
191: while (rs.next()) {
192: if (rs.getInt(1) == 1) {
193: assertEquals(
194: 1,
195: stmt2
196: .executeUpdate("UPDATE jTDS_CachedCursorTest SET data = 'NEW VALUE' WHERE id = 1"));
197: rs.updateString(2, "TEST UPDATE");
198: try {
199: rs.updateRow();
200: assertNotNull(rs.getWarnings());
201: assertEquals(
202: "Expected optimistic update exception",
203: "24000", rs.getWarnings().getSQLState());
204: } catch (SQLException e) {
205: // Expected exception as row has been modified on disk
206: assertEquals("24000", e.getSQLState());
207: }
208: }
209: }
210: rs.close();
211: stmt.close();
212: } finally {
213: if (con2 != null) {
214: con2.close();
215: }
216: dropTable("jTDS_CachedCursorTest");
217: }
218: }
219:
220: /**
221: * Test updateable result set where table is not keyed.
222: * Uses a server side cursor and positioned updates on Sybase.
223: */
224: public void testUpdateNoKeys() throws Exception {
225: Statement stmt = con
226: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
227: ResultSet.CONCUR_UPDATABLE);
228: stmt
229: .execute("CREATE TABLE ##TESTNOKEY (id int, data varchar(255))");
230: for (int i = 0; i < 4; i++) {
231: stmt.executeUpdate("INSERT INTO ##TESTNOKEY VALUES(" + i
232: + ", 'Test line " + i + "')");
233: }
234: ResultSet rs = stmt.executeQuery("SELECT * FROM ##TESTNOKEY");
235: assertTrue(rs.next());
236: assertTrue(rs.next());
237: rs.updateString(2, "UPDATED");
238: rs.updateRow();
239: rs.close();
240: rs = stmt.executeQuery("SELECT * FROM ##TESTNOKEY");
241: while (rs.next()) {
242: if (rs.getInt(1) == 1) {
243: assertEquals("UPDATED", rs.getString(2));
244: }
245: }
246: stmt.close();
247: }
248:
249: public static void main(String[] args) {
250: junit.textui.TestRunner.run(ClientSideCursorTest.class);
251: }
252: }
|