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: import java.util.*;
022:
023: import net.sourceforge.jtds.jdbc.*;
024:
025: /**
026: * Simple test suite to exercise batch execution.
027: *
028: * @version $Id: BatchTest.java,v 1.11 2007/07/12 21:21:27 bheineman Exp $
029: */
030: public class BatchTest extends DatabaseTestCase {
031: // Constants to use instead of the JDBC 3.0-only Statement constants
032: private static int SUCCESS_NO_INFO = -2;
033: private static int EXECUTE_FAILED = -3;
034:
035: public BatchTest(String name) {
036: super (name);
037: }
038:
039: /**
040: * This test should generate an error as the second statement in the batch
041: * returns a result set.
042: */
043: public void testResultSetError() throws Exception {
044: Statement stmt = con.createStatement();
045: stmt
046: .addBatch("create table #testbatch (id int, data varchar(255))");
047: stmt.addBatch("insert into #testbatch VALUES(1, 'Test line')");
048: stmt.addBatch("SELECT 'This is an error'");
049: int x[];
050: try {
051: x = stmt.executeBatch();
052: fail("Expecting BatchUpdateException");
053: } catch (BatchUpdateException e) {
054: x = e.getUpdateCounts();
055: }
056: assertEquals(3, x.length);
057: assertEquals(SUCCESS_NO_INFO, x[0]);
058: assertEquals(1, x[1]);
059: assertEquals(EXECUTE_FAILED, x[2]);
060: }
061:
062: /**
063: * The first statement in this batch does not return an update count.
064: * SUCCESS_NO_INFO is expected instead.
065: */
066: public void testNoCount() throws Exception {
067: Statement stmt = con.createStatement();
068: stmt
069: .addBatch("create table #testbatch (id int, data varchar(255))");
070: stmt.addBatch("insert into #testbatch VALUES(1, 'Test line')");
071: int x[] = stmt.executeBatch();
072: assertEquals(2, x.length);
073: assertEquals(SUCCESS_NO_INFO, x[0]);
074: assertEquals(1, x[1]);
075: }
076:
077: /**
078: * Test batched statements.
079: */
080: public void testBatch() throws Exception {
081: Statement stmt = con.createStatement();
082: stmt
083: .execute("create table #testbatch (id int, data varchar(255))");
084: for (int i = 0; i < 5; i++) {
085: if (i == 2) {
086: // This statement will generate an error
087: stmt
088: .addBatch("INSERT INTO #testbatch VALUES ('xx', 'This is line "
089: + i + "')");
090: } else {
091: stmt.addBatch("INSERT INTO #testbatch VALUES (" + i
092: + ", 'This is line " + i + "')");
093: }
094: }
095: int x[];
096: try {
097: x = stmt.executeBatch();
098: } catch (BatchUpdateException e) {
099: x = e.getUpdateCounts();
100: }
101: if (con.getMetaData().getDatabaseProductName().toLowerCase()
102: .startsWith("microsoft")
103: && ((JtdsDatabaseMetaData) con.getMetaData())
104: .getDatabaseMajorVersion() > 6) {
105: assertEquals(5, x.length);
106: assertEquals(1, x[0]);
107: assertEquals(1, x[1]);
108: assertEquals(EXECUTE_FAILED, x[2]);
109: assertEquals(EXECUTE_FAILED, x[3]);
110: assertEquals(EXECUTE_FAILED, x[4]);
111: } else {
112: // Sybase or SQL Server 6.5 - Entire batch fails due to data conversion error
113: // detected in statement 3
114: assertEquals(5, x.length);
115: assertEquals(EXECUTE_FAILED, x[0]);
116: assertEquals(EXECUTE_FAILED, x[1]);
117: assertEquals(EXECUTE_FAILED, x[2]);
118: assertEquals(EXECUTE_FAILED, x[3]);
119: assertEquals(EXECUTE_FAILED, x[4]);
120: }
121: // Now without errors
122: stmt.execute("TRUNCATE TABLE #testbatch");
123: for (int i = 0; i < 5; i++) {
124: stmt.addBatch("INSERT INTO #testbatch VALUES (" + i
125: + ", 'This is line " + i + "')");
126: }
127: x = stmt.executeBatch();
128: assertEquals(5, x.length);
129: assertEquals(1, x[0]);
130: assertEquals(1, x[1]);
131: assertEquals(1, x[2]);
132: assertEquals(1, x[3]);
133: assertEquals(1, x[4]);
134: }
135:
136: /**
137: * Test batched prepared statements.
138: */
139: public void testPrepStmtBatch() throws Exception {
140: Statement stmt = con.createStatement();
141: stmt
142: .execute("create table #testbatch (id int, data varchar(255))");
143: PreparedStatement pstmt = con
144: .prepareStatement("INSERT INTO #testbatch VALUES (?, ?)");
145: for (int i = 0; i < 5; i++) {
146: if (i == 2) {
147: pstmt.setString(1, "xxx");
148: } else {
149: pstmt.setInt(1, i);
150: }
151: pstmt.setString(2, "This is line " + i);
152: pstmt.addBatch();
153: }
154: int x[];
155: try {
156: x = pstmt.executeBatch();
157: } catch (BatchUpdateException e) {
158: x = e.getUpdateCounts();
159: }
160: if (con.getMetaData().getDatabaseProductName().toLowerCase()
161: .startsWith("microsoft")) {
162: assertEquals(5, x.length);
163: assertEquals(1, x[0]);
164: assertEquals(1, x[1]);
165: assertEquals(EXECUTE_FAILED, x[2]);
166: assertEquals(EXECUTE_FAILED, x[3]);
167: assertEquals(EXECUTE_FAILED, x[4]);
168: } else {
169: // Sybase - Entire batch fails due to data conversion error
170: // detected in statement 3
171: assertEquals(5, x.length);
172: assertEquals(EXECUTE_FAILED, x[0]);
173: assertEquals(EXECUTE_FAILED, x[1]);
174: assertEquals(EXECUTE_FAILED, x[2]);
175: assertEquals(EXECUTE_FAILED, x[3]);
176: assertEquals(EXECUTE_FAILED, x[4]);
177: }
178: // Now without errors
179: stmt.execute("TRUNCATE TABLE #testbatch");
180: for (int i = 0; i < 5; i++) {
181: pstmt.setInt(1, i);
182: pstmt.setString(2, "This is line " + i);
183: pstmt.addBatch();
184: }
185: x = pstmt.executeBatch();
186: assertEquals(5, x.length);
187: assertEquals(1, x[0]);
188: assertEquals(1, x[1]);
189: assertEquals(1, x[2]);
190: assertEquals(1, x[3]);
191: assertEquals(1, x[4]);
192: }
193:
194: /**
195: * Test batched callable statements.
196: */
197: public void testCallStmtBatch() throws Exception {
198: dropProcedure("jTDS_PROC");
199: try {
200: Statement stmt = con.createStatement();
201: stmt
202: .execute("create table #testbatch (id int, data varchar(255))");
203: stmt
204: .execute("create proc jTDS_PROC @p1 varchar(10), @p2 varchar(255) as "
205: + "INSERT INTO #testbatch VALUES (convert(int, @p1), @p2)");
206: CallableStatement cstmt = con
207: .prepareCall("{call jTDS_PROC (?, ?)}");
208: for (int i = 0; i < 5; i++) {
209: cstmt.setString(1, Integer.toString(i));
210: cstmt.setString(2, "This is line " + i);
211: cstmt.addBatch();
212: }
213: int x[];
214: try {
215: x = cstmt.executeBatch();
216: } catch (BatchUpdateException e) {
217: x = e.getUpdateCounts();
218: }
219: assertEquals(5, x.length);
220: assertEquals(1, x[0]);
221: assertEquals(1, x[1]);
222: assertEquals(1, x[2]);
223: assertEquals(1, x[3]);
224: assertEquals(1, x[4]);
225: // Now with errors
226: stmt.execute("TRUNCATE TABLE #testbatch");
227: for (int i = 0; i < 5; i++) {
228: if (i == 2) {
229: cstmt.setString(1, "XXX");
230: } else {
231: cstmt.setString(1, Integer.toString(i));
232: }
233: cstmt.setString(2, "This is line " + i);
234: cstmt.addBatch();
235: }
236: try {
237: x = cstmt.executeBatch();
238: } catch (BatchUpdateException e) {
239: x = e.getUpdateCounts();
240: }
241: if (con.getMetaData().getDatabaseProductName()
242: .toLowerCase().startsWith("microsoft")) {
243: assertEquals(5, x.length);
244: assertEquals(1, x[0]);
245: assertEquals(1, x[1]);
246: assertEquals(EXECUTE_FAILED, x[2]);
247: assertEquals(EXECUTE_FAILED, x[3]);
248: assertEquals(EXECUTE_FAILED, x[4]);
249: } else {
250: assertEquals(5, x.length);
251: assertEquals(1, x[0]);
252: assertEquals(1, x[1]);
253: assertEquals(EXECUTE_FAILED, x[2]);
254: assertEquals(1, x[3]);
255: assertEquals(1, x[4]);
256: }
257: } finally {
258: dropProcedure("jTDS_PROC");
259: }
260: }
261:
262: /**
263: * Test batched callable statements where the call includes literal parameters which prevent the use of RPC calls.
264: */
265: public void testCallStmtBatch2() throws Exception {
266: dropProcedure("jTDS_PROC");
267: try {
268: Statement stmt = con.createStatement();
269: stmt
270: .execute("create table #testbatch (id int, data varchar(255))");
271: stmt
272: .execute("create proc jTDS_PROC @p1 varchar(10), @p2 varchar(255) as "
273: + "INSERT INTO #testbatch VALUES (convert(int, @p1), @p2)");
274: CallableStatement cstmt = con
275: .prepareCall("{call jTDS_PROC (?, 'literal parameter')}");
276: for (int i = 0; i < 5; i++) {
277: if (i == 2) {
278: cstmt.setString(1, "XXX");
279: } else {
280: cstmt.setString(1, Integer.toString(i));
281: }
282: cstmt.addBatch();
283: }
284: int x[];
285: try {
286: x = cstmt.executeBatch();
287: } catch (BatchUpdateException e) {
288: x = e.getUpdateCounts();
289: }
290: if (con.getMetaData().getDatabaseProductName()
291: .toLowerCase().startsWith("microsoft")) {
292: assertEquals(5, x.length);
293: assertEquals(1, x[0]);
294: assertEquals(1, x[1]);
295: assertEquals(EXECUTE_FAILED, x[2]);
296: assertEquals(EXECUTE_FAILED, x[3]);
297: assertEquals(EXECUTE_FAILED, x[4]);
298: } else {
299: assertEquals(5, x.length);
300: assertEquals(1, x[0]);
301: assertEquals(1, x[1]);
302: assertEquals(EXECUTE_FAILED, x[2]);
303: assertEquals(1, x[3]);
304: assertEquals(1, x[4]);
305: }
306: // Now without errors
307: stmt.execute("TRUNCATE TABLE #testbatch");
308: for (int i = 0; i < 5; i++) {
309: cstmt.setString(1, Integer.toString(i));
310: cstmt.addBatch();
311: }
312: try {
313: x = cstmt.executeBatch();
314: } catch (BatchUpdateException e) {
315: x = e.getUpdateCounts();
316: }
317: assertEquals(5, x.length);
318: assertEquals(1, x[0]);
319: assertEquals(1, x[1]);
320: assertEquals(1, x[2]);
321: assertEquals(1, x[3]);
322: assertEquals(1, x[4]);
323: } finally {
324: dropProcedure("jTDS_PROC");
325: }
326: }
327:
328: /**
329: * Test large batch behavior.
330: */
331: public void testLargeBatch() throws Exception {
332: final int n = 5000;
333: getConnection().close();
334:
335: Statement stmt = con.createStatement();
336: stmt.executeUpdate("create table #testLargeBatch (val int)");
337: stmt
338: .executeUpdate("insert into #testLargeBatch (val) values (0)");
339:
340: PreparedStatement pstmt = con
341: .prepareStatement("update #testLargeBatch set val=? where val=?");
342: for (int i = 0; i < n; i++) {
343: pstmt.setInt(1, i + 1);
344: pstmt.setInt(2, i);
345: pstmt.addBatch();
346: }
347: int counts[] = pstmt.executeBatch();
348: // System.out.println(pstmt.getWarnings());
349: assertEquals(n, counts.length);
350: for (int i = 0; i < n; i++) {
351: assertEquals(1, counts[i]);
352: }
353: pstmt.close();
354:
355: ResultSet rs = stmt
356: .executeQuery("select count(*) from #testLargeBatch");
357: assertTrue(rs.next());
358: assertEquals(1, rs.getInt(1));
359: assertFalse(rs.next());
360: rs.close();
361: stmt.close();
362: }
363:
364: /**
365: * Test for bug [1180169] JDBC escapes not allowed with Sybase addBatch.
366: */
367: public void testBatchEsc() throws Exception {
368: Statement stmt = con.createStatement();
369: stmt.execute("CREATE TABLE #TESTBATCH (ts datetime)");
370: stmt
371: .addBatch("INSERT INTO #TESTBATCH VALUES ({ts '1999-01-01 23:50:00'})");
372: int counts[] = stmt.executeBatch();
373: assertEquals(1, counts[0]);
374: stmt.close();
375: }
376:
377: /**
378: * Test for bug [1371295] SQL Server continues after duplicate key error.
379: */
380: public void testPrepStmtBatchDupKey() throws Exception {
381: Statement stmt = con.createStatement();
382: stmt
383: .execute("create table #testbatch (id int, data varchar(255), PRIMARY KEY (id))");
384: PreparedStatement pstmt = con
385: .prepareStatement("INSERT INTO #testbatch VALUES (?, ?)");
386: for (int i = 0; i < 5; i++) {
387: if (i == 2) {
388: pstmt.setInt(1, 1); // Will cause duplicate key batch will continue
389: } else {
390: pstmt.setInt(1, i);
391: }
392: pstmt.setString(2, "This is line " + i);
393: pstmt.addBatch();
394: }
395: int x[];
396: try {
397: x = pstmt.executeBatch();
398: } catch (BatchUpdateException e) {
399: x = e.getUpdateCounts();
400: }
401: assertEquals(5, x.length);
402: assertEquals(1, x[0]);
403: assertEquals(1, x[1]);
404: assertEquals(EXECUTE_FAILED, x[2]);
405: assertEquals(1, x[3]);
406: assertEquals(1, x[4]);
407: // Now without errors
408: stmt.execute("TRUNCATE TABLE #testbatch");
409: for (int i = 0; i < 5; i++) {
410: pstmt.setInt(1, i);
411: pstmt.setString(2, "This is line " + i);
412: pstmt.addBatch();
413: }
414: x = pstmt.executeBatch();
415: assertEquals(5, x.length);
416: assertEquals(1, x[0]);
417: assertEquals(1, x[1]);
418: assertEquals(1, x[2]);
419: assertEquals(1, x[3]);
420: assertEquals(1, x[4]);
421: }
422:
423: /**
424: * Test for bug [1371295] SQL Server continues after duplicate key error.
425: */
426: public void testBatchDupKey() throws Exception {
427: Statement stmt = con.createStatement();
428: stmt
429: .execute("create table #testbatch (id int, data varchar(255), PRIMARY KEY (id))");
430: for (int i = 0; i < 5; i++) {
431: if (i == 2) {
432: // This statement will generate an duplicate key error
433: stmt
434: .addBatch("INSERT INTO #testbatch VALUES (1, 'This is line "
435: + i + "')");
436: } else {
437: stmt.addBatch("INSERT INTO #testbatch VALUES (" + i
438: + ", 'This is line " + i + "')");
439: }
440: }
441: int x[];
442: try {
443: x = stmt.executeBatch();
444: } catch (BatchUpdateException e) {
445: x = e.getUpdateCounts();
446: }
447: assertEquals(5, x.length);
448: assertEquals(1, x[0]);
449: assertEquals(1, x[1]);
450: assertEquals(EXECUTE_FAILED, x[2]);
451: assertEquals(1, x[3]);
452: assertEquals(1, x[4]);
453: // Now without errors
454: stmt.execute("TRUNCATE TABLE #testbatch");
455: for (int i = 0; i < 5; i++) {
456: stmt.addBatch("INSERT INTO #testbatch VALUES (" + i
457: + ", 'This is line " + i + "')");
458: }
459: x = stmt.executeBatch();
460: assertEquals(5, x.length);
461: assertEquals(1, x[0]);
462: assertEquals(1, x[1]);
463: assertEquals(1, x[2]);
464: assertEquals(1, x[3]);
465: assertEquals(1, x[4]);
466: }
467:
468: /**
469: * Test for PreparedStatement batch with no parameters.
470: */
471: public void testPrepStmtNoParams() throws Exception {
472: Statement stmt = con.createStatement();
473: stmt
474: .execute("create table #testbatch (id numeric(10) identity, data varchar(255), PRIMARY KEY (id))");
475: PreparedStatement pstmt = con
476: .prepareStatement("INSERT INTO #testbatch (data) VALUES ('Same each time')");
477: for (int i = 0; i < 5; i++) {
478: pstmt.addBatch();
479: }
480: int x[];
481: try {
482: x = pstmt.executeBatch();
483: } catch (BatchUpdateException e) {
484: x = e.getUpdateCounts();
485: }
486: assertEquals(5, x.length);
487: assertEquals(1, x[0]);
488: assertEquals(1, x[1]);
489: assertEquals(1, x[2]);
490: assertEquals(1, x[3]);
491: assertEquals(1, x[4]);
492: }
493:
494: /**
495: * Test for PreparedStatement batch with variable parameter types.
496: */
497: public void testPrepStmtVariableParams() throws Exception {
498: Statement stmt = con.createStatement();
499: stmt
500: .execute("create table #testbatch (id int, data int, PRIMARY KEY (id))");
501: PreparedStatement pstmt = con
502: .prepareStatement("INSERT INTO #testbatch VALUES (?, convert(int, ?))");
503: for (int i = 0; i < 5; i++) {
504: pstmt.setInt(1, i);
505: if (i == 2) {
506: // This statement will require a string param instead of an int
507: pstmt.setString(2, "123");
508: } else {
509: pstmt.setInt(2, 123);
510: }
511: pstmt.addBatch();
512: }
513: int x[];
514: try {
515: x = pstmt.executeBatch();
516: } catch (BatchUpdateException e) {
517: x = e.getUpdateCounts();
518: }
519: assertEquals(5, x.length);
520: assertEquals(1, x[0]);
521: assertEquals(1, x[1]);
522: assertEquals(1, x[2]);
523: assertEquals(1, x[3]);
524: assertEquals(1, x[4]);
525: ResultSet rs = stmt.executeQuery("SELECT * FROM #testbatch");
526: assertNotNull(rs);
527: int i = 0;
528: while (rs.next()) {
529: assertEquals(123, rs.getInt(2));
530: i++;
531: }
532: assertEquals(5, i);
533: }
534:
535: /**
536: * Test batched callable statements where the call has no parameters.
537: */
538: public void testCallStmtNoParams() throws Exception {
539: dropProcedure("jTDS_PROC");
540: try {
541: Statement stmt = con.createStatement();
542: stmt
543: .execute("create table #testbatch (id numeric(10) identity, data varchar(255))");
544: stmt
545: .execute("create proc jTDS_PROC as "
546: + "INSERT INTO #testbatch (data) VALUES ('same each time')");
547: CallableStatement cstmt = con
548: .prepareCall("{call jTDS_PROC}");
549: for (int i = 0; i < 5; i++) {
550: cstmt.addBatch();
551: }
552: int x[];
553: try {
554: x = cstmt.executeBatch();
555: } catch (BatchUpdateException e) {
556: x = e.getUpdateCounts();
557: }
558: assertEquals(5, x.length);
559: assertEquals(1, x[0]);
560: assertEquals(1, x[1]);
561: assertEquals(1, x[2]);
562: assertEquals(1, x[3]);
563: assertEquals(1, x[4]);
564: } finally {
565: dropProcedure("jTDS_PROC");
566: }
567: }
568:
569: /**
570: * Helper thread used by <code>testConcurrentBatching()</code> to execute a batch within a transaction that is
571: * then rolled back. Starting a couple of these threads concurrently should show whether there are any race
572: * conditions WRT preparation and execution in the batching implementation.
573: */
574: private class ConcurrentBatchingHelper extends Thread {
575: /** Connection on which to do the work. */
576: private Connection con;
577: /** Container to store any exceptions into. */
578: private Vector exceptions;
579:
580: ConcurrentBatchingHelper(Connection con, Vector exceptions) {
581: this .con = con;
582: this .exceptions = exceptions;
583: }
584:
585: public void run() {
586: try {
587: PreparedStatement pstmt = con
588: .prepareStatement("insert into #testConcurrentBatch (v1, v2, v3, v4, v5, v6) values (?, ?, ?, ?, ?, ?)");
589: for (int i = 0; i < 64; ++i) {
590: // Make sure we end up with 64 different prepares, use the binary representation of i to set each
591: // of the 6 parameters to either an int or a string.
592: int mask = i;
593: for (int j = 1; j <= 6; ++j, mask >>= 1) {
594: if ((mask & 1) != 0) {
595: pstmt.setInt(j, i);
596: } else {
597: pstmt.setString(j, String.valueOf(i));
598: }
599: }
600: pstmt.addBatch();
601: }
602: int x[];
603: try {
604: x = pstmt.executeBatch();
605: } catch (BatchUpdateException e) {
606: e.printStackTrace();
607: x = e.getUpdateCounts();
608: }
609: if (x.length != 64) {
610: throw new SQLException(
611: "Expected 64 update counts, got "
612: + x.length);
613: }
614: for (int i = 0; i < x.length; ++i) {
615: if (x[i] != 1) {
616: throw new SQLException("Error at position " + i
617: + ", got " + x[i] + " instead of 1");
618: }
619: }
620: // Rollback the transaction, exposing any race conditions.
621: con.rollback();
622: pstmt.close();
623: } catch (SQLException ex) {
624: ex.printStackTrace();
625: exceptions.add(ex);
626: }
627: }
628: }
629:
630: /**
631: * Test batched prepared statement concurrency. Batch prepares must not disappear between the moment when they
632: * were created and when they are executed.
633: */
634: public void testConcurrentBatching() throws Exception {
635: // Create a connection with a batch size of 1. This should cause prepares and actual batch execution to become
636: // interspersed (if correct synchronization is not in place) and greatly increase the chance of prepares
637: // being rolled back before getting executed.
638: Properties props = new Properties();
639: props.setProperty(Messages
640: .get(net.sourceforge.jtds.jdbc.Driver.BATCHSIZE), "1");
641: props.setProperty(Messages
642: .get(net.sourceforge.jtds.jdbc.Driver.PREPARESQL),
643: String.valueOf(TdsCore.TEMPORARY_STORED_PROCEDURES));
644: Connection con = getConnection(props);
645:
646: try {
647: Statement stmt = con.createStatement();
648: stmt
649: .execute("create table #testConcurrentBatch (v1 int, v2 int, v3 int, v4 int, v5 int, v6 int)");
650: stmt.close();
651:
652: Vector exceptions = new Vector();
653: con.setAutoCommit(false);
654:
655: Thread t1 = new ConcurrentBatchingHelper(con, exceptions);
656: Thread t2 = new ConcurrentBatchingHelper(con, exceptions);
657: t1.start();
658: t2.start();
659: t1.join();
660: t2.join();
661:
662: assertEquals(0, exceptions.size());
663: } finally {
664: con.close();
665: }
666: }
667:
668: public static void main(String[] args) {
669: junit.textui.TestRunner.run(BatchTest.class);
670: }
671: }
|