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.*;
021:
022: /**
023: * Test case to illustrate JDBC 3 GetGeneratedKeys() function.
024: *
025: * @version 1.0
026: */
027: public class GenKeyTest extends TestBase {
028:
029: public GenKeyTest(String name) {
030: super (name);
031: }
032:
033: public void testParams() throws Exception {
034: //
035: // Test data
036: //
037: Statement stmt = con.createStatement();
038:
039: stmt
040: .execute("CREATE TABLE #gktemp (id INT IDENTITY (1,1) PRIMARY KEY, dummyx VARCHAR(50))");
041:
042: stmt.close();
043: //
044: // Test PrepareStatement(sql, int) option
045: //
046: PreparedStatement pstmt = con.prepareStatement(
047: "INSERT INTO #gktemp (dummyx) VALUES (?)",
048: Statement.RETURN_GENERATED_KEYS);
049: pstmt.setString(1, "TEST01");
050: assertEquals("First Insert failed", 1, pstmt.executeUpdate());
051: ResultSet rs = pstmt.getGeneratedKeys();
052: assertTrue("ResultSet empty", rs.next());
053: assertEquals("Bad inserted row ID ", 1, rs.getInt(1));
054: rs.close();
055: pstmt.close();
056: //
057: // Test PrepareStatement(sql, int[]) option
058: //
059: int cols[] = new int[1];
060: cols[0] = 1;
061: pstmt = con.prepareStatement(
062: "INSERT INTO #gktemp (dummyx) VALUES (?)", cols);
063: pstmt.setString(1, "TEST02");
064: assertEquals("Second Insert failed", 1, pstmt.executeUpdate());
065: rs = pstmt.getGeneratedKeys();
066: assertTrue("ResultSet 2 empty", rs.next());
067: assertEquals("Bad inserted row ID ", 2, rs.getInt(1));
068: rs.close();
069: pstmt.close();
070: //
071: // Test PrepareStatement(sql, String[]) option
072: //
073: String colNames[] = new String[1];
074: colNames[0] = "ID";
075: pstmt = con.prepareStatement(
076: "INSERT INTO #gktemp (dummyx) VALUES (?)", colNames);
077: pstmt.setString(1, "TEST03");
078: pstmt.execute();
079: assertEquals("Third Insert failed", 1, pstmt.getUpdateCount());
080: rs = pstmt.getGeneratedKeys();
081: assertTrue("ResultSet 3 empty", rs.next());
082: assertEquals("Bad inserted row ID ", 3, rs.getInt(1));
083: rs.close();
084: pstmt.close();
085: //
086: // Test CreateStatement()
087: //
088: stmt = con.createStatement();
089: assertEquals("Fourth Insert failed", 1, stmt.executeUpdate(
090: "INSERT INTO #gktemp (dummyx) VALUES ('TEST04')",
091: Statement.RETURN_GENERATED_KEYS));
092: rs = stmt.getGeneratedKeys();
093: assertTrue("ResultSet 4 empty", rs.next());
094: assertEquals("Bad inserted row ID ", 4, rs.getInt(1));
095: rs.close();
096: stmt.close();
097:
098: stmt = con.createStatement();
099:
100: stmt.execute("DROP TABLE #gktemp");
101:
102: stmt.close();
103: }
104:
105: /**
106: * Test for bug [930305] getGeneratedKeys() does not work with triggers
107: */
108: public void testTrigger1() throws Exception {
109: Statement stmt = con.createStatement();
110: stmt
111: .execute("CREATE TABLE jtdsTestTrigger1 (id INT IDENTITY (1,1) PRIMARY KEY, data INT)");
112: stmt
113: .execute("CREATE TABLE jtdsTestTrigger2 (id INT IDENTITY (1,1) PRIMARY KEY, data INT)");
114: stmt.close();
115:
116: try {
117: stmt = con.createStatement();
118: stmt
119: .execute("CREATE TRIGGER testTrigger1 ON jtdsTestTrigger1 FOR INSERT AS "
120: + "INSERT INTO jtdsTestTrigger2 (data) VALUES (1)");
121: stmt.close();
122:
123: PreparedStatement pstmt = con.prepareStatement(
124: "INSERT INTO jtdsTestTrigger1 (data) VALUES (?)",
125: Statement.RETURN_GENERATED_KEYS);
126:
127: for (int i = 0; i < 10; i++) {
128: pstmt.setInt(1, i);
129: assertEquals("Insert failed: " + i, 1, pstmt
130: .executeUpdate());
131:
132: ResultSet rs = pstmt.getGeneratedKeys();
133:
134: assertTrue("ResultSet empty: " + i, rs.next());
135: assertEquals("Bad inserted row ID: " + i, i + 1, rs
136: .getInt(1));
137: assertTrue("ResultSet not empty: " + i, !rs.next());
138: rs.close();
139: }
140:
141: pstmt.close();
142: } finally {
143: stmt = con.createStatement();
144: stmt.execute("DROP TABLE jtdsTestTrigger1");
145: stmt.execute("DROP TABLE jtdsTestTrigger2");
146: stmt.close();
147: }
148: }
149:
150: /**
151: * Test empty result set returned when no keys available.
152: */
153: public void testNoKeys() throws Exception {
154: Statement stmt = con.createStatement();
155: ResultSet rs = stmt.getGeneratedKeys();
156: assertEquals("ID", rs.getMetaData().getColumnName(1));
157: assertFalse(rs.next());
158: }
159:
160: /**
161: * Test that SELECT statements work correctly with
162: * <code>PreparedStatement</code>s created with
163: * <code>RETURN_GENERATED_KEYS</code>.
164: */
165: public void testSelect() throws SQLException {
166: Statement stmt = con.createStatement();
167: stmt
168: .executeUpdate("create table #colors (id int, color varchar(255))");
169: stmt.executeUpdate("insert into #colors values (1, 'red')");
170: stmt.executeUpdate("insert into #colors values (1, 'green')");
171: stmt.executeUpdate("insert into #colors values (1, 'blue')");
172: stmt.close();
173:
174: PreparedStatement pstmt = con.prepareStatement(
175: "select * from #colors",
176: Statement.RETURN_GENERATED_KEYS);
177:
178: assertTrue(pstmt.execute());
179: ResultSet rs = pstmt.getResultSet();
180: assertEquals(2, rs.getMetaData().getColumnCount());
181: assertTrue(rs.next());
182: assertTrue(rs.next());
183: assertTrue(rs.next());
184: assertFalse(rs.next());
185: rs.close();
186: assertFalse(pstmt.getMoreResults());
187: assertEquals(-1, pstmt.getUpdateCount());
188:
189: rs = pstmt.executeQuery();
190: assertEquals(2, rs.getMetaData().getColumnCount());
191: assertTrue(rs.next());
192: assertTrue(rs.next());
193: assertTrue(rs.next());
194: assertFalse(rs.next());
195: rs.close();
196: pstmt.close();
197: }
198:
199: public static void main(String[] args) {
200: junit.textui.TestRunner.run(GenKeyTest.class);
201: }
202: }
|