0001: /*
0002: * SAfeTest.java
0003: *
0004: * Created on 08/23/2002
0005: */
0006:
0007: package net.sourceforge.jtds.test;
0008:
0009: import java.sql.*;
0010: import java.math.BigDecimal;
0011:
0012: import junit.framework.TestSuite;
0013: import net.sourceforge.jtds.util.Logger;
0014: import net.sourceforge.jtds.jdbc.Driver;
0015: import net.sourceforge.jtds.jdbc.Messages;
0016:
0017: import java.text.SimpleDateFormat;
0018: import java.util.Vector;
0019:
0020: /**
0021: * @author Alin Sinpalean
0022: * @version $Id: SAfeTest.java,v 1.62 2007/07/08 21:43:02 bheineman Exp $
0023: * @since 0.4
0024: */
0025: public class SAfeTest extends DatabaseTestCase {
0026: public SAfeTest(String name) {
0027: super (name);
0028: }
0029:
0030: public static void main(String args[]) {
0031: Logger.setActive(true);
0032:
0033: if (args.length > 0) {
0034: junit.framework.TestSuite s = new TestSuite();
0035:
0036: for (int i = 0; i < args.length; i++) {
0037: s.addTest(new SAfeTest(args[i]));
0038: }
0039:
0040: junit.textui.TestRunner.run(s);
0041: } else {
0042: junit.textui.TestRunner.run(SAfeTest.class);
0043: }
0044: }
0045:
0046: /**
0047: * Test whether NULL values, 0-length strings and single space strings
0048: * are treated right.
0049: */
0050: public void testNullLengthStrings0001() throws Exception {
0051: String types[] = { "VARCHAR(50)", "TEXT", "VARCHAR(350)",
0052: "NVARCHAR(50)", "NTEXT", };
0053:
0054: String values[] = { null, "", " ", "x" };
0055:
0056: Statement stmt = con.createStatement();
0057: boolean tds70orLater = props.getProperty(Messages
0058: .get(Driver.TDS)) == null
0059: || props.getProperty(Messages.get(Driver.TDS))
0060: .charAt(0) >= '7';
0061: int typeCnt = tds70orLater ? types.length : 2;
0062:
0063: for (int i = 0; i < typeCnt; i++) {
0064: assertEquals(0, stmt
0065: .executeUpdate("CREATE TABLE #SAfe0001 (val "
0066: + types[i] + " NULL)"));
0067:
0068: for (int j = 0; j < values.length; j++) {
0069: String insQuery = values[j] == null ? "INSERT INTO #SAfe0001 VALUES (NULL)"
0070: : "INSERT INTO #SAfe0001 VALUES ('" + values[j]
0071: + "')";
0072: assertEquals(1, stmt.executeUpdate(insQuery));
0073: ResultSet rs = stmt
0074: .executeQuery("SELECT val FROM #SAfe0001");
0075:
0076: assertTrue(rs.next());
0077:
0078: if (tds70orLater || !" ".equals(values[j])) {
0079: assertEquals(values[j], rs.getString(1));
0080: } else {
0081: if (values[j] == null) {
0082: assertEquals(null, rs.getObject(1));
0083: } else {
0084: assertEquals("", rs.getString(1));
0085: }
0086: }
0087:
0088: assertTrue(!rs.next());
0089: assertEquals(0, stmt
0090: .executeUpdate("TRUNCATE TABLE #SAfe0001"));
0091: }
0092:
0093: assertEquals(0, stmt.executeUpdate("DROP TABLE #SAfe0001"));
0094: }
0095: stmt.close();
0096: }
0097:
0098: /**
0099: * Test cancelling. Create 2 connections, lock some records on one of them
0100: * and try to read them using the other one. Cancel the statement from the
0101: * second connection, then try executing a simple query on it to make sure
0102: * it's in a correct state.
0103: */
0104: public void testCancel0001() throws Exception {
0105: // Create another connection to make sure the statements will deadlock
0106: Connection con2 = getConnection();
0107:
0108: Statement stmt = con.createStatement();
0109: assertFalse(stmt
0110: .execute("create table ##SAfe0001 (id int primary key, val varchar(20) null)"));
0111: assertFalse(stmt
0112: .execute("insert into ##SAfe0001 values (1, 'Line 1') "
0113: + "insert into ##SAfe0001 values (2, 'Line 2')"));
0114: assertEquals(1, stmt.getUpdateCount());
0115: assertTrue(!stmt.getMoreResults());
0116: assertEquals(1, stmt.getUpdateCount());
0117: assertTrue(!stmt.getMoreResults());
0118: assertEquals(-1, stmt.getUpdateCount());
0119:
0120: con.setAutoCommit(false);
0121: // This is where we lock the first line in the table
0122: stmt
0123: .executeUpdate("update ##SAfe0001 set val='Updated Line' where id=1");
0124:
0125: final Statement stmt2 = con2.createStatement();
0126:
0127: new Thread() {
0128: public void run() {
0129: try {
0130: sleep(1000);
0131: stmt2.cancel();
0132: } catch (Exception ex) {
0133: ex.printStackTrace();
0134: }
0135: }
0136: }.start();
0137:
0138: try {
0139: stmt2.executeQuery("if 1 = 1 select * from ##SAfe0001");
0140: // Make sure we get to the
0141: stmt2.getMoreResults();
0142: fail("Expecting cancel exception");
0143: } catch (SQLException ex) {
0144: assertEquals("Expecting cancel exception. Got "
0145: + ex.getMessage(), "HY008", ex.getSQLState());
0146: }
0147:
0148: con.setAutoCommit(true);
0149:
0150: stmt.execute("drop table ##SAfe0001");
0151: stmt.close();
0152:
0153: // Just run a tiny query to make sure the stream is still in working
0154: // condition.
0155: ResultSet rs = stmt2.executeQuery("select 1");
0156: assertTrue(rs.next());
0157: assertEquals(1, rs.getInt(1));
0158: assertTrue(!rs.next());
0159: stmt2.close();
0160: con2.close();
0161: }
0162:
0163: /**
0164: * Test cancelling. Create 2 connections, lock some records on one of them
0165: * and try to read them using the other one with a timeout set. When the
0166: * second connection times out try executing a simple query on it to make
0167: * sure it's in a correct state.
0168: */
0169: public void testCancel0002() throws Exception {
0170: // Create another connection to make sure the statements will deadlock
0171: Connection con2 = getConnection();
0172:
0173: Statement stmt = con.createStatement();
0174: assertFalse(stmt
0175: .execute("create table ##SAfe0002 (id int primary key, val varchar(20) null)"));
0176: assertFalse(stmt
0177: .execute("insert into ##SAfe0002 values (1, 'Line 1') "
0178: + "insert into ##SAfe0002 values (2, 'Line 2')"));
0179: assertEquals(1, stmt.getUpdateCount());
0180: assertTrue(!stmt.getMoreResults());
0181: assertEquals(1, stmt.getUpdateCount());
0182: assertTrue(!stmt.getMoreResults());
0183: assertEquals(-1, stmt.getUpdateCount());
0184:
0185: con.setAutoCommit(false);
0186: // This is where we lock the first line in the table
0187: stmt
0188: .executeUpdate("update ##SAfe0002 set val='Updated Line' where id=1");
0189:
0190: Statement stmt2 = con2.createStatement();
0191: stmt2.setQueryTimeout(1);
0192:
0193: try {
0194: stmt2.executeQuery("if 1 = 1 select * from ##SAfe0002");
0195: fail("Expecting timeout exception");
0196: } catch (SQLException ex) {
0197: assertEquals("Expecting timeout exception. Got "
0198: + ex.getMessage(), "HYT00", ex.getSQLState());
0199: }
0200:
0201: // SAfe What should we do with the results if the execution timed out?!
0202:
0203: con.setAutoCommit(true);
0204:
0205: stmt.execute("drop table ##SAfe0002");
0206: stmt.close();
0207:
0208: // Just run a tiny query to make sure the stream is still in working
0209: // condition.
0210: ResultSet rs = stmt2.executeQuery("select 1");
0211: assertTrue(rs.next());
0212: assertEquals(1, rs.getInt(1));
0213: assertTrue(!rs.next());
0214: stmt2.close();
0215: con2.close();
0216: }
0217:
0218: /**
0219: * Test for bug [1120442] Statement hangs in socket read after
0220: * Statement.cancel().
0221: * <p/>
0222: * In 1.0.1 and earlier versions network packets consisting of a single
0223: * TDS_DONE packet with the CANCEL flag set were ignored and a new read()
0224: * was attempted, essentially causing a deadlock.
0225: * <p/>
0226: * Because it relies on a particular succession of events this test will
0227: * not always work as expected, i.e. the cancel might be executed too early
0228: * or too late, but it won't fail in this situation.
0229: */
0230: public void testCancel0003() throws Exception {
0231: final Statement stmt = con.createStatement();
0232:
0233: for (int i = 0; i < 100; i++) {
0234: Thread t = new Thread(new Runnable() {
0235: public void run() {
0236: try {
0237: // Cancel the statement and hope this happens
0238: // immediately after the executeQuery() below and
0239: // before any results arrive
0240: stmt.cancel();
0241: } catch (SQLException ex) {
0242: ex.printStackTrace();
0243: }
0244: }
0245: });
0246: t.start();
0247:
0248: // Create a thread that executes a query
0249: try {
0250: stmt.executeQuery("select max(id) from sysobjects");
0251: // Can't fail here, the cancel() request might be out of order
0252: } catch (SQLException ex) {
0253: // Request was canceled
0254: if (!"HY008".equals(ex.getSQLState())) {
0255: ex.printStackTrace();
0256: }
0257: assertEquals("HY008", ex.getSQLState());
0258: }
0259:
0260: // Wait for the cancel to finish executing
0261: try {
0262: t.join();
0263: } catch (InterruptedException ex) {
0264: // Ignore
0265: }
0266: }
0267:
0268: // Make sure the connection is still alive
0269: stmt.executeQuery("select 1");
0270: stmt.close();
0271: }
0272:
0273: /**
0274: * Test for bug [1222199] Delayed exception thrown in statement close.
0275: */
0276: public void testQueryTimeout() throws Exception {
0277: try {
0278: dropTable("jtdsStmtTest");
0279: Statement stmt = con.createStatement();
0280: stmt
0281: .execute("CREATE TABLE jtdsStmtTest (id int primary key, data text)");
0282: assertEquals(
0283: 1,
0284: stmt
0285: .executeUpdate("INSERT INTO jtdsStmtTest VALUES(1, "
0286: + "'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')"));
0287: assertEquals(
0288: 1,
0289: stmt
0290: .executeUpdate("INSERT INTO jtdsStmtTest VALUES(2, "
0291: + "'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')"));
0292: //
0293: // Query timeout
0294: //
0295: try {
0296: stmt.setQueryTimeout(-1);
0297: fail("Expected error timeout < 0");
0298: } catch (SQLException e) {
0299: assertEquals("HY092", e.getSQLState());
0300: }
0301: con.setAutoCommit(false);
0302: assertEquals(
0303: 1,
0304: stmt
0305: .executeUpdate("UPDATE jtdsStmtTest SET data = '' WHERE id = 1"));
0306: Connection con2 = getConnection();
0307: Statement stmt2 = con2.createStatement();
0308: stmt2.setQueryTimeout(1);
0309: assertEquals(1, stmt2.getQueryTimeout());
0310: try {
0311: stmt2
0312: .executeQuery("SELECT * FROM jtdsStmtTest WHERE id = 1");
0313: fail("Expected time out exception");
0314: } catch (SQLException e) {
0315: // This exception is caused by the query timer expiring:
0316: // java.sql.SQLException: The query has timed out.
0317: // But note a cancel ACK is still pending.
0318: assertEquals("HYT00", e.getSQLState());
0319: }
0320: try {
0321: stmt2.close();
0322: } catch (SQLException e) {
0323: // The cancel ACK should not throw an exception. It should be
0324: // masked by the driver.
0325: fail("Not expecting a cancel ACK exception.");
0326: }
0327: //
0328: // The close triggers another exception
0329: // java.sql.SQLException: Request cancelled
0330: // which is caused when the cancel packet itself is reached
0331: // in the input stream.
0332: // The exception means that the close does not complete and the
0333: // actual network socket is not closed when it should be but only
0334: // when (if) the connection object itself is garbage collected.
0335: //
0336: con2.close();
0337: con.rollback();
0338: stmt.close();
0339: } finally {
0340: con.setAutoCommit(true);
0341: dropTable("jtdsStmtTest");
0342: }
0343: }
0344:
0345: // MT-unsafe!!!
0346: volatile int started, done;
0347: volatile boolean failed;
0348:
0349: /**
0350: * Test <code>CursorResultSet</code> concurrency. Create a number of threads that execute concurrent queries using
0351: * scrollable result sets. All requests should be run on the same connection (<code>Tds</code> instance).
0352: */
0353: public void testCursorResultSetConcurrency0003() throws Exception {
0354: Statement stmt0 = con.createStatement();
0355: stmt0
0356: .execute("create table #SAfe0003(id int primary key, val varchar(20) null)");
0357: stmt0.execute("insert into #SAfe0003 values (1, 'Line 1') "
0358: + "insert into #SAfe0003 values (2, 'Line 2')");
0359: while (stmt0.getMoreResults() || stmt0.getUpdateCount() != -1)
0360: ;
0361:
0362: final Object o1 = new Object(), o2 = new Object();
0363:
0364: int threadCount = 25;
0365: Thread threads[] = new Thread[threadCount];
0366:
0367: started = done = 0;
0368: failed = false;
0369:
0370: for (int i = 0; i < threadCount; i++) {
0371:
0372: threads[i] = new Thread() {
0373: public void run() {
0374: ResultSet rs;
0375: Statement stmt = null;
0376:
0377: try {
0378: stmt = con.createStatement(
0379: ResultSet.TYPE_SCROLL_INSENSITIVE,
0380: ResultSet.CONCUR_READ_ONLY);
0381: rs = stmt
0382: .executeQuery("SELECT * FROM #SAfe0003");
0383:
0384: assertEquals(null, rs.getWarnings());
0385: assertEquals(null, stmt.getWarnings());
0386:
0387: // Synchronize all threads
0388: synchronized (o2) {
0389: synchronized (o1) {
0390: started++;
0391: o1.notify();
0392: }
0393:
0394: try {
0395: o2.wait();
0396: } catch (InterruptedException e) {
0397: }
0398: }
0399:
0400: assertNotNull(
0401: "executeQuery should not return null",
0402: rs);
0403: assertTrue(rs.next());
0404: assertTrue(rs.next());
0405: assertTrue(!rs.next());
0406: assertTrue(rs.previous());
0407: assertTrue(rs.previous());
0408: assertTrue(!rs.previous());
0409: } catch (SQLException e) {
0410: e.printStackTrace();
0411:
0412: synchronized (o1) {
0413: failed = true;
0414: }
0415:
0416: fail("An SQL Exception occured: " + e);
0417: } finally {
0418: if (stmt != null) {
0419: try {
0420: stmt.close();
0421: } catch (SQLException e) {
0422: }
0423: }
0424:
0425: // Notify that we're done
0426: synchronized (o1) {
0427: done++;
0428: o1.notify();
0429: }
0430: }
0431: }
0432: };
0433:
0434: threads[i].start();
0435: }
0436:
0437: while (true) {
0438: synchronized (o1) {
0439: if (started == threadCount) {
0440: break;
0441: }
0442:
0443: o1.wait();
0444: }
0445: }
0446:
0447: synchronized (o2) {
0448: o2.notifyAll();
0449: }
0450:
0451: boolean passed = true;
0452:
0453: for (int i = 0; i < threadCount; i++) {
0454: stmt0 = con.createStatement();
0455: ResultSet rs = stmt0.executeQuery("SELECT 1234");
0456: passed &= rs.next();
0457: passed &= !rs.next();
0458: stmt0.close();
0459: }
0460:
0461: while (true) {
0462: synchronized (o1) {
0463: if (done == threadCount) {
0464: break;
0465: }
0466:
0467: o1.wait();
0468: }
0469: }
0470:
0471: for (int i = 0; i < threadCount; i++) {
0472: threads[i].join();
0473: }
0474:
0475: stmt0.close();
0476:
0477: assertTrue(passed);
0478: assertTrue(!failed);
0479: }
0480:
0481: /**
0482: * Check that meta data information is fetched even for empty cursor-based result sets (bug #613199).
0483: *
0484: * @throws Exception
0485: */
0486: public void testCursorResultSetEmpty0004() throws Exception {
0487: Statement stmt = con.createStatement(
0488: ResultSet.TYPE_SCROLL_INSENSITIVE,
0489: ResultSet.CONCUR_READ_ONLY);
0490: ResultSet rs = stmt.executeQuery("SELECT 5 Value WHERE 1=0");
0491: assertEquals(null, stmt.getWarnings());
0492: assertEquals(null, rs.getWarnings());
0493: assertEquals("Value", rs.getMetaData().getColumnName(1));
0494: assertTrue(!rs.isBeforeFirst());
0495: assertTrue(!rs.isAfterLast());
0496: assertTrue(!rs.isFirst());
0497: assertTrue(!rs.isLast());
0498: rs.next();
0499: assertTrue(!rs.isBeforeFirst());
0500: assertTrue(!rs.isAfterLast());
0501: assertTrue(!rs.isFirst());
0502: assertTrue(!rs.isLast());
0503: rs.close();
0504: stmt.close();
0505: }
0506:
0507: /**
0508: * Check that the <code>isBeforeFirst</code>, <code>isAfterLast</code>,
0509: * <code>isFirst</code> and <code>isLast</code> methods work for
0510: * forward-only, read-only result sets (bug [1039876] MS SQL
0511: * JtdsResultSet.isAfterLast() always returns false).
0512: *
0513: * @throws Exception if an error condition occurs
0514: */
0515: public void testPlainResultSetPosition0004() throws Exception {
0516: Statement stmt = con.createStatement();
0517:
0518: // Try with an empty ResultSet
0519: ResultSet rs = stmt.executeQuery("SELECT 5 Value WHERE 1=0");
0520: assertEquals(null, stmt.getWarnings());
0521: assertEquals(null, rs.getWarnings());
0522: assertEquals("Value", rs.getMetaData().getColumnName(1));
0523: assertTrue(!rs.isBeforeFirst());
0524: assertTrue(!rs.isAfterLast());
0525: assertTrue(!rs.isFirst());
0526: assertTrue(!rs.isLast());
0527: rs.next();
0528: assertTrue(!rs.isBeforeFirst());
0529: assertTrue(!rs.isAfterLast());
0530: assertTrue(!rs.isFirst());
0531: assertTrue(!rs.isLast());
0532: rs.close();
0533:
0534: // Try with a non-empty ResultSet
0535: rs = stmt.executeQuery("SELECT 5 Value");
0536: assertEquals(null, stmt.getWarnings());
0537: assertEquals(null, rs.getWarnings());
0538: assertEquals("Value", rs.getMetaData().getColumnName(1));
0539: assertTrue(rs.isBeforeFirst());
0540: assertTrue(!rs.isAfterLast());
0541: assertTrue(!rs.isFirst());
0542: assertTrue(!rs.isLast());
0543: rs.next();
0544: assertTrue(!rs.isBeforeFirst());
0545: assertTrue(!rs.isAfterLast());
0546: assertTrue(rs.isFirst());
0547: assertTrue(rs.isLast());
0548: rs.next();
0549: assertTrue(!rs.isBeforeFirst());
0550: assertTrue(rs.isAfterLast());
0551: assertTrue(!rs.isFirst());
0552: assertTrue(!rs.isLast());
0553: rs.close();
0554:
0555: stmt.close();
0556: }
0557:
0558: /**
0559: * Check that values returned from bit fields are correct (not just 0) (bug #841670).
0560: *
0561: * @throws Exception
0562: */
0563: public void testBitFields0005() throws Exception {
0564: Statement stmt = con.createStatement();
0565: stmt
0566: .execute("create table #SAfe0005(id int primary key, bit1 bit not null, bit2 bit not null)");
0567: stmt.execute("insert into #SAfe0005 values (0, 0, 0) "
0568: + "insert into #SAfe0005 values (1, 1, 1) "
0569: + "insert into #SAfe0005 values (2, 0, 0)");
0570: while (stmt.getMoreResults() || stmt.getUpdateCount() != -1)
0571: ;
0572:
0573: ResultSet rs = stmt.executeQuery("SELECT * FROM #SAfe0005");
0574: while (rs.next()) {
0575: int id = rs.getInt(1);
0576: int bit1 = rs.getInt(2);
0577: int bit2 = rs.getInt(3);
0578:
0579: assertTrue("id: " + id + "; bit1: " + bit1 + "; bit2: "
0580: + bit2, bit1 == id % 2
0581: && (bit2 == id || id == 2 && bit2 == 0));
0582: }
0583:
0584: rs.close();
0585: stmt.close();
0586: }
0587:
0588: /**
0589: * Test that <code>CallableStatement</code>s with return values work correctly.
0590: *
0591: * @throws Exception
0592: */
0593: public void testCallableStatement0006() throws Exception {
0594: final int myVal = 13;
0595:
0596: Statement stmt = con.createStatement();
0597: stmt
0598: .execute("CREATE PROCEDURE #SAfe0006 @p1 INT, @p2 VARCHAR(20) OUT AS "
0599: + "SELECT @p2=CONVERT(VARCHAR(20), @p1-1) "
0600: + "SELECT @p1 AS value " + "RETURN @p1+1");
0601: stmt.close();
0602:
0603: // Try all formats: escaped, w/ exec and w/o exec
0604: String[] sql = { "{?=call #SAfe0006(?,?)}",
0605: "exec ?=#SAfe0006 ?,?", "?=#SAfe0006 ?,?" };
0606:
0607: for (int i = 0; i < sql.length; i++) {
0608: // Execute it using executeQuery
0609: CallableStatement cs = con.prepareCall(sql[i]);
0610: cs.registerOutParameter(1, Types.INTEGER);
0611: cs.setInt(2, myVal);
0612: cs.registerOutParameter(3, Types.VARCHAR);
0613: cs.executeQuery().close();
0614:
0615: assertFalse(cs.getMoreResults());
0616: assertEquals(-1, cs.getUpdateCount());
0617:
0618: assertEquals(myVal + 1, cs.getInt(1));
0619: assertEquals(String.valueOf(myVal - 1), cs.getString(3));
0620:
0621: cs.close();
0622:
0623: // Now use execute
0624: cs = con.prepareCall(sql[i]);
0625: cs.registerOutParameter(1, Types.INTEGER);
0626: cs.setInt(2, myVal);
0627: cs.registerOutParameter(3, Types.VARCHAR);
0628: assertTrue(cs.execute());
0629: cs.getResultSet().close();
0630:
0631: assertFalse(cs.getMoreResults());
0632: assertEquals(-1, cs.getUpdateCount());
0633:
0634: assertEquals(myVal + 1, cs.getInt(1));
0635: assertEquals(String.valueOf(myVal - 1), cs.getString(3));
0636:
0637: cs.close();
0638: }
0639: }
0640:
0641: /**
0642: * Helper method for <code>testBigDecimal0007</code>. Inserts a BigDecimal
0643: * value obtained from a double value.
0644: *
0645: * @param stmt <code>PreparedStatement</code> instance
0646: * @param val the <code>double</code> value to insert
0647: * @param scaleFlag if <code>true</code> scale the value to 4, otherwise
0648: * leave it as it is
0649: */
0650: private static void insertBigDecimal(PreparedStatement stmt,
0651: double val, boolean scaleFlag) throws Exception {
0652: BigDecimal bd = new BigDecimal(val);
0653:
0654: if (scaleFlag) {
0655: bd = bd.setScale(4, BigDecimal.ROUND_HALF_EVEN);
0656: }
0657:
0658: stmt.setBigDecimal(1, bd);
0659: stmt.execute();
0660:
0661: int rowCount = stmt.getUpdateCount();
0662:
0663: assertEquals(1, rowCount);
0664:
0665: assertTrue(stmt.getMoreResults());
0666: ResultSet rs = stmt.getResultSet();
0667: assertTrue(rs.next());
0668:
0669: assertEquals("Values don't match.", val, rs.getDouble(1), 0);
0670: }
0671:
0672: /**
0673: * Test <code>BigDecimal</code>s created from double values (i.e with very
0674: * large scales).
0675: */
0676: public void testBigDecimal0007() throws Exception {
0677: Statement createStmt = con.createStatement();
0678: createStmt.execute("CREATE TABLE #SAfe0007(value MONEY)");
0679: createStmt.close();
0680:
0681: PreparedStatement stmt = con
0682: .prepareStatement("INSERT INTO #SAfe0007(value) VALUES (?) "
0683: + "SELECT * FROM #SAfe0007 DELETE #SAfe0007");
0684: // Now test with certain values.
0685: insertBigDecimal(stmt, 1.1, false);
0686: insertBigDecimal(stmt, 0.1, false);
0687: insertBigDecimal(stmt, 0.1, true);
0688: insertBigDecimal(stmt, 0.01, false);
0689: insertBigDecimal(stmt, 0.01, true);
0690: insertBigDecimal(stmt, 0.02, false);
0691: insertBigDecimal(stmt, 0.02, true);
0692: insertBigDecimal(stmt, 0.25, false);
0693:
0694: stmt.close();
0695: }
0696:
0697: /**
0698: * Test writing <code>long</code> values to VARCHAR fields. There was a
0699: * regression introduced in release 0.6 that caused <code>long</code>
0700: * fields to be sent with non-zero scale and appear with decimals when
0701: * written into VARCHAR fields.
0702: */
0703: public void testLongToVarchar0008() throws Exception {
0704: long myVal = 13;
0705:
0706: Statement createStmt = con.createStatement();
0707: createStmt
0708: .execute("CREATE TABLE #SAfe0008(value VARCHAR(255))");
0709: createStmt.close();
0710:
0711: PreparedStatement stmt = con
0712: .prepareStatement("INSERT INTO #SAfe0008(value) values (CONVERT(VARCHAR(255), ?)) "
0713: + "SELECT * FROM #SAfe0008 DELETE #SAfe0008");
0714:
0715: stmt.setLong(1, myVal);
0716: stmt.execute();
0717: int rowCount = stmt.getUpdateCount();
0718: assertEquals(1, rowCount);
0719:
0720: assertTrue(stmt.getMoreResults());
0721: ResultSet rs = stmt.getResultSet();
0722: assertTrue(rs.next());
0723:
0724: assertEquals("Values don't match.", String.valueOf(myVal), rs
0725: .getString(1));
0726:
0727: stmt.close();
0728: }
0729:
0730: /**
0731: * Test <code>ResultSet.deleteRow()</code> on updateable result sets.
0732: */
0733: public void testDeleteRow0009() throws Exception {
0734: Statement stmt = con.createStatement();
0735: stmt
0736: .execute("CREATE TABLE #SAfe0009(value VARCHAR(255) PRIMARY KEY)");
0737: stmt.close();
0738:
0739: PreparedStatement insStmt = con
0740: .prepareStatement("INSERT INTO #SAfe0009(value) values (?)");
0741: insStmt.setString(1, "Row 1");
0742: assertEquals(1, insStmt.executeUpdate());
0743: insStmt.setString(1, "Row 2");
0744: assertEquals(1, insStmt.executeUpdate());
0745: insStmt.close();
0746:
0747: stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
0748: ResultSet.CONCUR_UPDATABLE);
0749: ResultSet rs = stmt
0750: .executeQuery("SELECT * FROM #SAfe0009 ORDER BY 1");
0751: assertEquals(null, stmt.getWarnings());
0752: assertEquals(null, rs.getWarnings());
0753: assertTrue(rs.last());
0754: assertTrue(!rs.rowDeleted());
0755: rs.deleteRow();
0756: assertTrue(rs.rowDeleted());
0757: rs.close();
0758:
0759: rs = stmt.executeQuery("SELECT * FROM #SAfe0009");
0760: assertTrue(rs.next());
0761: assertEquals("Row 1", rs.getString(1));
0762: assertTrue(!rs.next());
0763: rs.close();
0764: stmt.close();
0765: }
0766:
0767: /**
0768: * Test VARCHAR output parameters returned by CallableStatements.
0769: * <p>
0770: * An issue existed, caused by the fact that the parameter was sent to SQL
0771: * Server as a short VARCHAR (not XORed with 0x80) limiting its length to
0772: * 255 characters. See bug [815348] for more details.
0773: */
0774: public void testCallableStatementVarchar0010() throws Exception {
0775: Statement stmt = con.createStatement();
0776: stmt
0777: .execute("CREATE PROCEDURE #SAfe0010 @p1 VARCHAR(255) OUT AS "
0778: + "SELECT @p1 = @p1 + @p1 "
0779: + "SELECT @p1 = @p1 + @p1 "
0780: + "SELECT @p1 = @p1 + @p1 "
0781: + "SELECT @p1 AS value " + "RETURN 255");
0782: stmt.close();
0783:
0784: // 256 characters long string
0785: String myVal = "01234567890123456789012345678901234567890123456789"
0786: + "01234567890123456789012345678901234567890123456789"
0787: + "01234567890123456789012345678901234567890123456789"
0788: + "01234567890123456789012345678901234567890123456789"
0789: + "01234567890123456789012345678901234567890123456789"
0790: + "01234";
0791:
0792: // Execute it using executeQuery
0793: CallableStatement cs = con.prepareCall("{?=call #SAfe0010(?)}");
0794: cs.registerOutParameter(1, Types.INTEGER);
0795: cs.setString(2, myVal);
0796: cs.registerOutParameter(2, Types.VARCHAR);
0797: ResultSet rs = cs.executeQuery();
0798: assertTrue(rs.next());
0799: String rsVal = rs.getString(1);
0800: rs.close();
0801:
0802: assertFalse(cs.getMoreResults());
0803: assertEquals(-1, cs.getUpdateCount());
0804:
0805: assertEquals(myVal.length(), cs.getInt(1));
0806: assertEquals(rsVal, cs.getString(2));
0807:
0808: cs.close();
0809: }
0810:
0811: /**
0812: * Test <code>ResultSet.updateRow()</code> on updateable result sets.
0813: */
0814: public void testUpdateRow0011() throws Exception {
0815: Statement stmt = con.createStatement();
0816: stmt
0817: .execute("CREATE TABLE #SAfe0011(value VARCHAR(255) PRIMARY KEY)");
0818: stmt.close();
0819:
0820: PreparedStatement insStmt = con
0821: .prepareStatement("INSERT INTO #SAfe0011(value) values (?)");
0822: insStmt.setString(1, "Row 1");
0823: assertEquals(1, insStmt.executeUpdate());
0824: insStmt.setString(1, "Row 2");
0825: assertEquals(1, insStmt.executeUpdate());
0826: insStmt.close();
0827:
0828: stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
0829: ResultSet.CONCUR_UPDATABLE);
0830: ResultSet rs = stmt
0831: .executeQuery("SELECT * FROM #SAfe0011 ORDER BY 1");
0832: assertEquals(null, stmt.getWarnings());
0833: assertEquals(null, rs.getWarnings());
0834: assertTrue(rs.next());
0835: assertTrue(rs.next());
0836: rs.updateString(1, "Row X");
0837: rs.updateRow();
0838: rs.next();
0839: assertEquals("Row X", rs.getString(1));
0840: rs.close();
0841: stmt.close();
0842: }
0843:
0844: /**
0845: * Test <code>ResultSet.insertRow()</code> on updateable result sets.
0846: */
0847: public void testInsertRow0012() throws Exception {
0848: Statement stmt = con.createStatement();
0849: stmt
0850: .execute("CREATE TABLE #SAfe0012(value VARCHAR(255) PRIMARY KEY)");
0851: stmt.close();
0852:
0853: PreparedStatement insStmt = con
0854: .prepareStatement("INSERT INTO #SAfe0012(value) values (?)");
0855: insStmt.setString(1, "Row 1");
0856: assertEquals(1, insStmt.executeUpdate());
0857: insStmt.setString(1, "Row 2");
0858: assertEquals(1, insStmt.executeUpdate());
0859: insStmt.close();
0860:
0861: stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
0862: ResultSet.CONCUR_UPDATABLE);
0863: ResultSet rs = stmt
0864: .executeQuery("SELECT * FROM #SAfe0012 ORDER BY 1");
0865: assertEquals(null, stmt.getWarnings());
0866: assertEquals(null, rs.getWarnings());
0867:
0868: // Insert the new row
0869: rs.moveToInsertRow();
0870: rs.updateString(1, "Row X");
0871: rs.insertRow();
0872:
0873: // Check the ResultSet contents
0874: rs.moveToCurrentRow();
0875: rs.next();
0876: assertEquals("Row 1", rs.getString(1));
0877: rs.next();
0878: assertEquals("Row 2", rs.getString(1));
0879: rs.next();
0880: assertEquals("Row X", rs.getString(1));
0881: rs.close();
0882: stmt.close();
0883: }
0884:
0885: /**
0886: * Test how an "out-of-order" close behaves (e.g close the
0887: * <code>Connection</code> first, then the <code>Statement</code> anf
0888: * finally the <code>ResultSet</code>).
0889: */
0890: public void testOutOfOrderClose0013() throws Exception {
0891: Connection localConn = getConnection();
0892: Statement stmt = localConn.createStatement();
0893: stmt
0894: .execute("CREATE TABLE #SAfe0013(value VARCHAR(255) PRIMARY KEY)");
0895:
0896: PreparedStatement insStmt = localConn
0897: .prepareStatement("INSERT INTO #SAfe0013(value) values (?)");
0898: insStmt.setString(1, "Row 1");
0899: assertEquals(1, insStmt.executeUpdate());
0900: insStmt.setString(1, "Row 2");
0901: assertEquals(1, insStmt.executeUpdate());
0902:
0903: ResultSet rs = stmt.executeQuery("SELECT * FROM #SAfe0013");
0904:
0905: // Close the connection first
0906: localConn.close();
0907:
0908: // Now, close the statements
0909: stmt.close();
0910: insStmt.close();
0911:
0912: // And finally, close the ResultSet
0913: rs.close();
0914: }
0915:
0916: /**
0917: * Test cursor-based <code>ResultSet</code>s obtained from
0918: * <code>PreparedStatement</code>s and <code>CallableStatement</code>s.
0919: */
0920: public void testPreparedAndCallableCursors0014() throws Exception {
0921: // Logger.setActive(true);
0922: Statement stmt = con.createStatement();
0923: stmt
0924: .executeUpdate("CREATE TABLE #SAfe0014(id INT PRIMARY KEY)");
0925: stmt.executeUpdate("INSERT INTO #SAfe0014 VALUES (1)");
0926: stmt
0927: .executeUpdate("CREATE PROCEDURE #sp_SAfe0014(@P1 INT, @P2 INT) AS "
0928: + "SELECT id, @P2 FROM #SAfe0014 WHERE id=@P1");
0929: stmt.close();
0930:
0931: PreparedStatement ps = con.prepareStatement(
0932: "SELECT id FROM #SAfe0014",
0933: ResultSet.TYPE_SCROLL_SENSITIVE,
0934: ResultSet.CONCUR_UPDATABLE);
0935: ResultSet resultSet = ps.executeQuery();
0936: // No warnings
0937: assertEquals(null, resultSet.getWarnings());
0938: assertEquals(null, ps.getWarnings());
0939: // Correct ResultSet
0940: assertTrue(resultSet.next());
0941: assertEquals(1, resultSet.getInt(1));
0942: assertTrue(!resultSet.next());
0943: // Correct meta data
0944: ResultSetMetaData rsmd = resultSet.getMetaData();
0945: assertEquals("id", rsmd.getColumnName(1));
0946: assertEquals("#SAfe0014", rsmd.getTableName(1));
0947: // Insert row
0948: resultSet.moveToInsertRow();
0949: resultSet.updateInt(1, 2);
0950: resultSet.insertRow();
0951: resultSet.moveToCurrentRow();
0952: // Check correct row count
0953: resultSet.last();
0954: assertEquals(2, resultSet.getRow());
0955: resultSet.close();
0956: ps.close();
0957:
0958: ps = con.prepareStatement(
0959: "SELECT id, ? FROM #SAfe0014 WHERE id = ?",
0960: ResultSet.TYPE_SCROLL_SENSITIVE,
0961: ResultSet.CONCUR_UPDATABLE);
0962: ps.setInt(1, 5);
0963: ps.setInt(2, 1);
0964: resultSet = ps.executeQuery();
0965: // No warnings
0966: assertEquals(null, resultSet.getWarnings());
0967: assertEquals(null, ps.getWarnings());
0968: // Correct ResultSet
0969: assertTrue(resultSet.next());
0970: assertEquals(1, resultSet.getInt(1));
0971: assertEquals(5, resultSet.getInt(2));
0972: assertTrue(!resultSet.next());
0973: // Correct meta data
0974: rsmd = resultSet.getMetaData();
0975: assertEquals("id", rsmd.getColumnName(1));
0976: assertEquals("#SAfe0014", rsmd.getTableName(1));
0977: resultSet.close();
0978: ps.close();
0979:
0980: CallableStatement cs = con.prepareCall(
0981: "{call #sp_SAfe0014(?,?)}",
0982: ResultSet.TYPE_SCROLL_SENSITIVE,
0983: ResultSet.CONCUR_UPDATABLE);
0984: cs.setInt(1, 1);
0985: cs.setInt(2, 3);
0986: resultSet = cs.executeQuery();
0987: // No warnings
0988: assertEquals(null, resultSet.getWarnings());
0989: assertEquals(null, cs.getWarnings());
0990: // Correct ResultSet
0991: assertTrue(resultSet.next());
0992: assertEquals(1, resultSet.getInt(1));
0993: assertEquals(3, resultSet.getInt(2));
0994: assertTrue(!resultSet.next());
0995: // Correct meta data
0996: rsmd = resultSet.getMetaData();
0997: assertEquals("id", rsmd.getColumnName(1));
0998: assertEquals("#SAfe0014", rsmd.getTableName(1));
0999: resultSet.close();
1000: cs.close();
1001: }
1002:
1003: /**
1004: * Test batch updates for both plain and prepared statements.
1005: */
1006: public void testBatchUpdates0015() throws Exception {
1007: Statement stmt = con.createStatement();
1008: stmt
1009: .execute("CREATE TABLE #SAfe0015(value VARCHAR(255) PRIMARY KEY)");
1010:
1011: // Execute prepared batch
1012: PreparedStatement insStmt = con
1013: .prepareStatement("INSERT INTO #SAfe0015(value) values (?)");
1014: insStmt.setString(1, "Row 1");
1015: insStmt.addBatch();
1016: insStmt.setString(1, "Row 2");
1017: insStmt.addBatch();
1018: int[] res = insStmt.executeBatch();
1019: assertEquals(2, res.length);
1020: assertEquals(1, res[0]);
1021: assertEquals(1, res[1]);
1022:
1023: // Execute an empty batch
1024: res = insStmt.executeBatch();
1025: insStmt.close();
1026: assertEquals(0, res.length);
1027:
1028: // Execute plain batch
1029: stmt
1030: .addBatch("UPDATE #SAfe0015 SET value='R1' WHERE value='Row 1'");
1031: stmt
1032: .addBatch("UPDATE #SAfe0015 SET value='R2' WHERE value='Row 2'");
1033: res = stmt.executeBatch();
1034: assertEquals(2, res.length);
1035: assertEquals(1, res[0]);
1036: assertEquals(1, res[1]);
1037:
1038: // Execute an empty batch
1039: res = stmt.executeBatch();
1040: assertEquals(0, res.length);
1041:
1042: // Close the statement
1043: stmt.close();
1044: }
1045:
1046: /**
1047: * Test that dates prior to 06/15/1940 0:00:00 are stored and retrieved
1048: * correctly.
1049: */
1050: public void testOldDates0016() throws Exception {
1051: Statement stmt = con.createStatement();
1052: stmt.execute("CREATE TABLE #SAfe0016(id INT, value DATETIME)");
1053:
1054: SimpleDateFormat format = new SimpleDateFormat(
1055: "yyyy-MM-dd HH:mm:ss");
1056: String[] dates = { "1983-10-30 02:00:00",
1057: "1983-10-30 01:59:59", "1940-06-14 23:59:59",
1058: "1911-03-11 00:51:39", "1911-03-11 00:51:38",
1059: "1900-01-01 01:00:00", "1900-01-01 00:59:59",
1060: "1900-01-01 00:09:21", "1900-01-01 00:09:20",
1061: "1753-01-01 00:00:00" };
1062:
1063: // Insert the timestamps
1064: PreparedStatement pstmt = con
1065: .prepareStatement("INSERT INTO #SAfe0016 VALUES(?, ?)");
1066:
1067: for (int i = 0; i < dates.length; i++) {
1068: pstmt.setInt(1, i);
1069: pstmt.setString(2, dates[i]);
1070: pstmt.addBatch();
1071: }
1072:
1073: int[] res = pstmt.executeBatch();
1074: // Check that the insertion went ok
1075:
1076: assertEquals(dates.length, res.length);
1077:
1078: for (int i = 0; i < dates.length; i++) {
1079: assertEquals(1, res[i]);
1080: }
1081:
1082: // Select the timestamps and make sure they are the same
1083: ResultSet rs = stmt
1084: .executeQuery("SELECT value FROM #SAfe0016 ORDER BY id");
1085:
1086: int counter = 0;
1087:
1088: while (rs.next()) {
1089: assertEquals(format.parse(dates[counter]), rs
1090: .getTimestamp(1));
1091: ++counter;
1092: }
1093:
1094: // Close everything
1095: rs.close();
1096: stmt.close();
1097: pstmt.close();
1098: }
1099:
1100: /**
1101: * Test bug #926620 - Too long value for VARCHAR field.
1102: */
1103: /* does not work with SQL 6.5
1104: public void testCursorLargeCharInsert0017() throws Exception {
1105: Statement stmt = con.createStatement(
1106: ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
1107: stmt.execute("CREATE TABLE #SAfe0017(value VARCHAR(10) PRIMARY KEY)");
1108:
1109: // Create the updateable ResultSet
1110: ResultSet rs = stmt.executeQuery(
1111: "SELECT value FROM #SAfe0017");
1112:
1113: // Try inserting a character string less than 10 characters long
1114: rs.moveToInsertRow();
1115: rs.updateString(1, "Test");
1116: rs.insertRow();
1117: rs.moveToCurrentRow();
1118: rs.last();
1119: // Check that we do indeed have one row in the ResultSet now
1120: assertEquals(1, rs.getRow());
1121:
1122: // Try inserting a character string more than 10 characters long
1123: rs.moveToInsertRow();
1124: rs.updateString(1, "Testing: 1, 2, 3...");
1125: try {
1126: rs.insertRow();
1127: fail("Should cause an SQLException with native error number 8152"
1128: + "and SQL state 22001");
1129: } catch (SQLException ex) {
1130: // assertEquals("22001", ex.getSQLState());
1131: assertTrue(ex instanceof DataTruncation);
1132: }
1133:
1134: // Close everything
1135: rs.close();
1136: stmt.close();
1137: }*/
1138:
1139: /**
1140: * Test for bug [939206] TdsException: can't sent this BigDecimal
1141: */
1142: public void testBigDecimal1() throws Exception {
1143: Statement stmt = con.createStatement();
1144: ResultSet rs = stmt.executeQuery("SELECT @@MAX_PRECISION");
1145: assertTrue(rs.next());
1146: int maxPrecision = rs.getInt(1);
1147: rs.close();
1148: BigDecimal maxval = new BigDecimal("1E+" + maxPrecision);
1149: maxval = maxval.subtract(new BigDecimal(1));
1150:
1151: // maxval now = 99999999999999999999999999999999999999
1152: if (maxPrecision > 28) {
1153: stmt
1154: .execute("create table #testBigDecimal1 (id int primary key, data01 decimal(38,0), data02 decimal(38,12) null, data03 money)");
1155: } else {
1156: stmt
1157: .execute("create table #testBigDecimal1 (id int primary key, data01 decimal(28,0), data02 decimal(28,12) null, data03 money)");
1158: }
1159:
1160: PreparedStatement pstmt = con
1161: .prepareStatement("insert into #testBigDecimal1 (id, data01, data02, data03) values (?,?,?,?)");
1162: pstmt.setInt(1, 1);
1163:
1164: try {
1165: pstmt.setBigDecimal(2, maxval.add(new BigDecimal(1)));
1166: assertTrue(false); // Should fail
1167: } catch (SQLException e) {
1168: // System.out.println(e.getMessage());
1169: // OK Genuinely can't send this one!
1170: }
1171:
1172: pstmt.setBigDecimal(2, maxval);
1173: pstmt.setBigDecimal(3, new BigDecimal(1.0 / 3.0)); // Scale > 38
1174: pstmt.setBigDecimal(4, new BigDecimal("12345.56789"));
1175: assertTrue(pstmt.executeUpdate() == 1);
1176: pstmt.close();
1177: rs = stmt.executeQuery("SELECT * FROM #testBigDecimal1");
1178: assertTrue(rs.next());
1179: assertEquals(maxval, rs.getBigDecimal(2));
1180: assertEquals(new BigDecimal("0.333333333333"), rs
1181: .getBigDecimal(3)); // Rounded to scale 10
1182: assertEquals(new BigDecimal("12345.5679"), rs.getBigDecimal(4)); // Money has scale of 4
1183: rs.close();
1184: maxval = maxval.negate();
1185: Statement stmt2 = con
1186: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
1187: ResultSet.CONCUR_UPDATABLE);
1188: rs = stmt2.executeQuery("SELECT * FROM #testBigDecimal1");
1189: SQLWarning warn = stmt.getWarnings();
1190:
1191: while (warn != null) {
1192: System.out.println(warn.getMessage());
1193: warn = warn.getNextWarning();
1194: }
1195:
1196: assertTrue(rs.next());
1197: rs.updateBigDecimal("data01", maxval);
1198: rs.updateNull("data02");
1199: rs.updateObject("data03", new BigDecimal("-12345.56789"), 2); // Round to scale 2
1200: rs.updateRow();
1201: rs.close();
1202: stmt2.close();
1203: rs = stmt.executeQuery("SELECT * FROM #testBigDecimal1");
1204: assertTrue(rs.next());
1205: assertEquals(maxval, rs.getBigDecimal(2));
1206: assertEquals(null, rs.getBigDecimal(3));
1207: assertEquals(new BigDecimal("-12345.5700"), rs.getBigDecimal(4));
1208: rs.close();
1209: stmt.close();
1210: }
1211:
1212: /**
1213: * Test for bug [963799] float values change when written to the database
1214: */
1215: public void testFloat1() throws Exception {
1216: float value = 2.2f;
1217:
1218: Statement stmt = con.createStatement();
1219: stmt.execute("create table #testFloat1 (data decimal(28,10))");
1220: stmt.close();
1221:
1222: PreparedStatement pstmt = con
1223: .prepareStatement("insert into #testFloat1 (data) values (?)");
1224: pstmt.setFloat(1, value);
1225: assertTrue(pstmt.executeUpdate() == 1);
1226: pstmt.close();
1227:
1228: pstmt = con.prepareStatement("select data from #testFloat1");
1229: ResultSet rs = pstmt.executeQuery();
1230:
1231: assertTrue(rs.next());
1232: assertTrue(value == rs.getFloat(1));
1233: assertTrue(!rs.next());
1234:
1235: pstmt.close();
1236: rs.close();
1237: }
1238:
1239: /**
1240: * Test for bug [983561] getDatetimeValue truncates fractional milliseconds
1241: */
1242: public void testDatetimeRounding1() throws Exception {
1243: long dateTime = 1089297738677L;
1244: Timestamp value = new Timestamp(dateTime);
1245:
1246: Statement stmt = con.createStatement();
1247: stmt.execute("create table #dtr1 (data datetime)");
1248: stmt.close();
1249:
1250: PreparedStatement pstmt = con
1251: .prepareStatement("insert into #dtr1 (data) values (?)");
1252: pstmt.setTimestamp(1, value);
1253: assertTrue(pstmt.executeUpdate() == 1);
1254: pstmt.close();
1255:
1256: pstmt = con.prepareStatement("select data from #dtr1");
1257: ResultSet rs = pstmt.executeQuery();
1258:
1259: assertTrue(rs.next());
1260: assertTrue(value.equals(rs.getTimestamp(1)));
1261: assertTrue(!rs.next());
1262:
1263: pstmt.close();
1264: rs.close();
1265: }
1266:
1267: public void testSocketConcurrency1() {
1268: final Connection con = this .con;
1269: final int threadCount = 10, loopCount = 10;
1270: final Vector errors = new Vector();
1271:
1272: // DriverManager.setLogStream(System.out);
1273:
1274: // Create a huge query
1275: StringBuffer queryBuffer = new StringBuffer(4100);
1276: queryBuffer.append("SELECT '");
1277: while (queryBuffer.length() < 2000) {
1278: queryBuffer.append("0123456789");
1279: }
1280: queryBuffer.append("' AS value1, '");
1281: while (queryBuffer.length() < 4000) {
1282: queryBuffer.append("9876543210");
1283: }
1284: queryBuffer.append("' AS value2");
1285: final String query = queryBuffer.toString();
1286:
1287: Thread heavyThreads[] = new Thread[threadCount], lightThreads[] = new Thread[threadCount];
1288:
1289: // Create threadCount heavy threads
1290: for (int i = 0; i < threadCount; i++) {
1291: heavyThreads[i] = new Thread() {
1292: public void run() {
1293: try {
1294: Statement stmt = con.createStatement();
1295: for (int i = 0; i < loopCount; i++) {
1296: stmt.execute(query);
1297: }
1298: stmt.close();
1299: } catch (SQLException ex) {
1300: ex.printStackTrace();
1301: errors.add(ex);
1302: }
1303: }
1304: };
1305: }
1306:
1307: // Create threadCount light threads
1308: for (int i = 0; i < threadCount; i++) {
1309: lightThreads[i] = new Thread() {
1310: public void run() {
1311: try {
1312: sleep(100);
1313: } catch (InterruptedException ex) {
1314: }
1315: try {
1316: Statement stmt = con.createStatement();
1317: for (int i = 0; i < loopCount; i++) {
1318: stmt.execute("SELECT 1");
1319: }
1320: stmt.close();
1321: } catch (Exception ex) {
1322: ex.printStackTrace();
1323: errors.add(ex);
1324: }
1325: }
1326: };
1327: }
1328:
1329: for (int i = 0; i < threadCount; i++) {
1330: heavyThreads[i].start();
1331: lightThreads[i].start();
1332: }
1333:
1334: for (int i = 0; i < threadCount; i++) {
1335: try {
1336: heavyThreads[i].join();
1337: } catch (InterruptedException ex) {
1338: }
1339: try {
1340: lightThreads[i].join();
1341: } catch (InterruptedException ex) {
1342: }
1343: }
1344:
1345: assertEquals(0, errors.size());
1346: }
1347:
1348: public void testSocketConcurrency2() {
1349: final Connection con = this .con;
1350: final int threadCount = 10, loopCount = 10;
1351: final Vector errors = new Vector();
1352:
1353: // DriverManager.setLogStream(System.out);
1354:
1355: // Create a huge query
1356: StringBuffer valueBuffer = new StringBuffer(4000);
1357: while (valueBuffer.length() < 4000) {
1358: valueBuffer.append("0123456789");
1359: }
1360: final String value = valueBuffer.toString();
1361:
1362: Thread heavyThreads[] = new Thread[threadCount], lightThreads[] = new Thread[threadCount];
1363:
1364: // Create threadCount heavy threads
1365: for (int i = 0; i < threadCount; i++) {
1366: heavyThreads[i] = new Thread() {
1367: public void run() {
1368: try {
1369: PreparedStatement pstmt = con
1370: .prepareStatement("SELECT ? AS value1, ? AS value2");
1371: pstmt.setString(1, value);
1372: pstmt.setString(2, value);
1373: for (int i = 0; i < loopCount; i++) {
1374: pstmt.execute();
1375: }
1376: pstmt.close();
1377: } catch (SQLException ex) {
1378: ex.printStackTrace();
1379: errors.add(ex);
1380: }
1381: }
1382: };
1383: }
1384:
1385: // Create threadCount light threads
1386: for (int i = 0; i < threadCount; i++) {
1387: lightThreads[i] = new Thread() {
1388: public void run() {
1389: try {
1390: sleep(100);
1391: } catch (InterruptedException ex) {
1392: }
1393: try {
1394: Statement stmt = con.createStatement();
1395: for (int i = 0; i < loopCount; i++) {
1396: stmt.execute("SELECT 1");
1397: }
1398: stmt.close();
1399: } catch (Exception ex) {
1400: ex.printStackTrace();
1401: errors.add(ex);
1402: }
1403: }
1404: };
1405: }
1406:
1407: for (int i = 0; i < threadCount; i++) {
1408: heavyThreads[i].start();
1409: lightThreads[i].start();
1410: }
1411:
1412: for (int i = 0; i < threadCount; i++) {
1413: try {
1414: heavyThreads[i].join();
1415: } catch (InterruptedException ex) {
1416: }
1417: try {
1418: lightThreads[i].join();
1419: } catch (InterruptedException ex) {
1420: }
1421: }
1422:
1423: assertEquals(0, errors.size());
1424: }
1425:
1426: public void testSocketConcurrency3() {
1427: final Connection con = this .con;
1428: final int threadCount = 10, loopCount = 10;
1429: final Vector errors = new Vector();
1430:
1431: // DriverManager.setLogStream(System.out);
1432:
1433: // Create a huge query
1434: StringBuffer valueBuffer = new StringBuffer(4000);
1435: while (valueBuffer.length() < 4000) {
1436: valueBuffer.append("0123456789");
1437: }
1438: final String value = valueBuffer.toString();
1439:
1440: Thread heavyThreads[] = new Thread[threadCount], lightThreads[] = new Thread[threadCount];
1441:
1442: // Create threadCount heavy threads
1443: for (int i = 0; i < threadCount; i++) {
1444: heavyThreads[i] = new Thread() {
1445: public void run() {
1446: try {
1447: PreparedStatement pstmt = con
1448: .prepareStatement("SELECT ? AS value1, ? AS value2");
1449: pstmt.setString(1, value);
1450: pstmt.setString(2, value);
1451: for (int i = 0; i < loopCount; i++) {
1452: pstmt.execute();
1453: }
1454: pstmt.close();
1455: } catch (SQLException ex) {
1456: ex.printStackTrace();
1457: errors.add(ex);
1458: }
1459: }
1460: };
1461: }
1462:
1463: // Create threadCount light threads
1464: for (int i = 0; i < threadCount; i++) {
1465: lightThreads[i] = new Thread() {
1466: public void run() {
1467: try {
1468: sleep(100);
1469: } catch (InterruptedException ex) {
1470: }
1471: try {
1472: CallableStatement cstmt = con
1473: .prepareCall("sp_who");
1474: for (int i = 0; i < loopCount; i++) {
1475: cstmt.execute();
1476: }
1477: cstmt.close();
1478: } catch (Exception ex) {
1479: ex.printStackTrace();
1480: errors.add(ex);
1481: }
1482: }
1483: };
1484: }
1485:
1486: for (int i = 0; i < threadCount; i++) {
1487: heavyThreads[i].start();
1488: lightThreads[i].start();
1489: }
1490:
1491: for (int i = 0; i < threadCount; i++) {
1492: try {
1493: heavyThreads[i].join();
1494: } catch (InterruptedException ex) {
1495: }
1496: try {
1497: lightThreads[i].join();
1498: } catch (InterruptedException ex) {
1499: }
1500: }
1501:
1502: assertEquals(0, errors.size());
1503: }
1504:
1505: /**
1506: * Test running SELECT queries on one <code>Statement</code> at the same
1507: * time as <code>cancel()</code> is called on a concurrent
1508: * <code>Statement</code>.
1509: */
1510: public void testSocketConcurrency4() throws Exception {
1511: // Just enough rows to break the server response in two network packets
1512: final int rowCount = 256;
1513:
1514: //Set up the test table
1515: final Statement stmt = con.createStatement();
1516: stmt.executeUpdate("create table #testSocketConcurrency4 "
1517: + "(id int primary key, value varchar(30))");
1518: for (int i = 0; i < rowCount; i++) {
1519: stmt.executeUpdate("insert into #testSocketConcurrency4 "
1520: + "values (" + i + ", 'Row number " + i + "')");
1521: }
1522:
1523: final Vector errors = new Vector();
1524:
1525: // Start a thread that does some work
1526: Thread t = new Thread() {
1527: public void run() {
1528: try {
1529: for (int j = 0; j < 10; j++) {
1530: ResultSet rs = stmt
1531: .executeQuery("select * from #testSocketConcurrency4");
1532: int cnt = 0;
1533: while (rs.next()) {
1534: ++cnt;
1535: }
1536: assertEquals(rowCount, cnt);
1537: rs.close();
1538: assertEquals(
1539: 1,
1540: stmt
1541: .executeUpdate("update #testSocketConcurrency4 "
1542: + "set value='Updated' where id="
1543: + j));
1544: }
1545: } catch (Exception ex) {
1546: ex.printStackTrace();
1547: errors.add(ex);
1548: }
1549: }
1550: };
1551: t.start();
1552:
1553: // At the same time run some cancel() tests (on the same connection!)
1554: testCancel0003();
1555:
1556: // Now wait for the worker thread to finish
1557: t.join();
1558:
1559: assertEquals(0, errors.size());
1560: }
1561:
1562: /**
1563: * Test that <code>null</code> output parameters are handled correctly.
1564: * <p/>
1565: * It seems that if a non-nullable type is sent as input value and the
1566: * output value is NULL, SQL Server (not Sybase) gets confused and returns
1567: * the same type but a single 0 byte as value instead of the equivalent
1568: * nullable type (e.g. instead of returning an <code>INTN</code> with
1569: * length 0, which means it's null, it returns an <code>INT4</code>
1570: * followed by a single 0 byte). The output parameter packet length is also
1571: * incorrect, which indicates that SQL Server is confused.
1572: * <p/>
1573: * Currently jTDS always sends RPC parameters as nullable types, but this
1574: * test is necessary to ensure that it will always remain so.
1575: */
1576: public void testNullOutputParameters() throws SQLException {
1577: Statement stmt = con.createStatement();
1578: assertEquals(
1579: 0,
1580: stmt
1581: .executeUpdate("create procedure #testNullOutput @p1 int output as "
1582: + "select @p1=null"));
1583: stmt.close();
1584:
1585: CallableStatement cstmt = con.prepareCall("#testNullOutput ?");
1586: cstmt.setInt(1, 1);
1587: cstmt.registerOutParameter(1, Types.INTEGER);
1588: assertEquals(0, cstmt.executeUpdate());
1589: assertNull(cstmt.getObject(1));
1590: cstmt.close();
1591: }
1592:
1593: /**
1594: * Test that the SQL parser doesn't try to parse the table name unless
1595: * necessary (or that it is able to parse function calls if it does).
1596: */
1597: public void testTableParsing() throws SQLException {
1598: Statement stmt = con.createStatement();
1599: try {
1600: stmt
1601: .executeQuery("SELECT * FROM ::fn_missing('c:\\t file.trc')");
1602: fail("Expecting an SQLException");
1603: } catch (SQLException ex) {
1604: // 42000 == syntax error or access rule violation
1605: assertEquals("42000", ex.getSQLState());
1606: }
1607: }
1608:
1609: /**
1610: * Test for bug related with [1368058] Calling StoredProcedure with
1611: * functions ({fn} escape can't handle special characters, e.g. underscore).
1612: */
1613: public void testFnEscape() throws Exception {
1614: Statement stmt = con.createStatement();
1615: ResultSet rs = stmt.executeQuery("SELECT {fn host_id()}");
1616: assertTrue(rs.next());
1617: assertFalse(rs.next());
1618: rs.close();
1619: stmt.close();
1620: }
1621:
1622: /**
1623: * Test for bug #1116046 {fn } escape can't handle nested functions.
1624: */
1625: public void testFnEscapeNesting() throws Exception {
1626: Statement stmt = con.createStatement();
1627: stmt
1628: .executeUpdate("create table #testFnEscapeNesting (col1 int null, col2 int)");
1629: stmt
1630: .executeUpdate("insert into #testFnEscapeNesting (col1, col2) "
1631: + "values (null, 1)");
1632: stmt
1633: .executeUpdate("insert into #testFnEscapeNesting (col1, col2) "
1634: + "values (1, 2)");
1635:
1636: ResultSet rs = stmt
1637: .executeQuery("select {fn ifnull({fn max(col2)}, 0)} "
1638: + "from #testFnEscapeNesting");
1639: assertNotNull(rs);
1640: assertTrue(rs.next());
1641: assertEquals(2, rs.getInt(1));
1642: assertFalse(rs.next());
1643: rs.close();
1644:
1645: rs = stmt.executeQuery("select {fn ifnull((select col1 "
1646: + "from #testFnEscapeNesting where col2 = 1), 0) }");
1647: assertNotNull(rs);
1648: assertTrue(rs.next());
1649: assertEquals(0, rs.getInt(1));
1650: assertFalse(rs.next());
1651: rs.close();
1652:
1653: rs = stmt
1654: .executeQuery("select {fn ifnull(sum({fn ifnull(col1, 4)}), max(col2))} "
1655: + "from #testFnEscapeNesting "
1656: + "group by col2 order by col2");
1657: assertNotNull(rs);
1658: assertTrue(rs.next());
1659: assertEquals(4, rs.getInt(1));
1660: assertTrue(rs.next());
1661: assertEquals(1, rs.getInt(1));
1662: assertFalse(rs.next());
1663: rs.close();
1664:
1665: stmt.close();
1666: }
1667:
1668: /**
1669: * Test <code>DataTruncation</code> exception.
1670: */
1671: public void testDataTruncException() throws Exception {
1672: con
1673: .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
1674: Statement stmt = con.createStatement();
1675: if (!con.getMetaData().getDatabaseProductName().toLowerCase()
1676: .startsWith("microsoft")) {
1677: // By default Sybase will silently truncate strings,
1678: // set an option to ensure that an exception is thrown.
1679: stmt.execute("SET STRING_RTRUNCATION ON");
1680: }
1681:
1682: stmt
1683: .execute("CREATE TABLE #TESTTRUNC (i tinyint, n numeric(2), c char(2))");
1684:
1685: try {
1686: stmt.execute("INSERT INTO #TESTTRUNC VALUES(1111, 1, 'X')");
1687: fail("Expected data truncation on tinyint");
1688: } catch (DataTruncation e) {
1689: // Expected DataTruncation
1690: }
1691:
1692: try {
1693: stmt.execute("INSERT INTO #TESTTRUNC VALUES(1, 1111, 'X')");
1694: fail("Expected data truncation on numeric");
1695: } catch (DataTruncation e) {
1696: // Expected DataTruncation
1697: }
1698:
1699: try {
1700: stmt
1701: .execute("INSERT INTO #TESTTRUNC VALUES(1, 1, 'XXXXX')");
1702: fail("Expected data truncation on char");
1703: } catch (DataTruncation e) {
1704: // Expected DataTruncation
1705: }
1706: }
1707:
1708: /**
1709: * Test <code>Statement.setMaxFieldSize()</code>.
1710: */
1711: public void testMaxFieldSize() throws Exception {
1712: // TODO Should it also work for fields other than TEXT, per JDBC spec?
1713: Statement stmt = con.createStatement();
1714:
1715: stmt
1716: .executeUpdate("create table #testMaxFieldSize (i int primary key, t text)");
1717: stmt
1718: .executeUpdate("insert into #testMaxFieldSize (i, t) values (1, 'This is a test')");
1719:
1720: PreparedStatement pstmt = con
1721: .prepareStatement("select * from #testMaxFieldSize");
1722:
1723: // Set different max field sizes for two concurrent statements
1724: // Also set max rows, to test setting field size and max rows at the
1725: // same time works ok
1726: stmt.setMaxFieldSize(3);
1727: stmt.setMaxRows(1);
1728: pstmt.setMaxFieldSize(5);
1729:
1730: // Test plain statement
1731: ResultSet rs = stmt
1732: .executeQuery("select * from #testMaxFieldSize");
1733: assertNotNull(rs);
1734: assertTrue(rs.next());
1735: assertEquals(1, rs.getInt(1));
1736: assertEquals(3, rs.getString(2).length());
1737: rs.close();
1738:
1739: // Test prepared statement
1740: rs = pstmt.executeQuery();
1741: assertNotNull(rs);
1742: assertTrue(rs.next());
1743: assertEquals(1, rs.getInt(1));
1744: assertEquals(5, rs.getString(2).length());
1745: rs.close();
1746:
1747: stmt.close();
1748:
1749: // Test scrollable statement
1750: stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
1751: ResultSet.CONCUR_UPDATABLE);
1752: stmt.setMaxFieldSize(3);
1753: rs = stmt.executeQuery("select * from #testMaxFieldSize");
1754: assertNotNull(rs);
1755: assertEquals(null, stmt.getWarnings());
1756: assertEquals(null, rs.getWarnings());
1757: assertTrue(rs.next());
1758: assertEquals(1, rs.getInt(1));
1759: assertEquals(3, rs.getString(2).length());
1760: rs.close();
1761: }
1762:
1763: /**
1764: * Test return of multiple scrollable result sets from one execute.
1765: */
1766: public void testGetMultiScrollRs() throws Exception {
1767: // Manual commit mode to make sure no garbage is left behind
1768: con.setAutoCommit(false);
1769:
1770: Statement stmt = con.createStatement(
1771: ResultSet.TYPE_SCROLL_SENSITIVE,
1772: ResultSet.CONCUR_UPDATABLE);
1773: try {
1774: dropProcedure("jtds_multiSet");
1775: stmt.execute("CREATE PROC jtds_multiSet as\r\n "
1776: + "BEGIN\r\n" + "SELECT 'SINGLE ROW RESULT'\r\n"
1777: + "SELECT 1, 'LINE ONE'\r\n" + "UNION\r\n"
1778: + "SELECT 2, 'LINE TWO'\r\n" + "UNION\r\n"
1779: + "SELECT 3, 'LINE THREE'\r\n"
1780: + "SELECT 'ANOTHER SINGLE ROW RESULT'\r\n"
1781: + "END\r\n");
1782: assertTrue(stmt.execute("exec jtds_multiSet"));
1783: stmt.clearWarnings();
1784: ResultSet rs = stmt.getResultSet();
1785: assertNotNull(stmt.getWarnings()); // Downgrade to read only
1786: assertNotNull(stmt.getWarnings().getNextWarning()); // Downgrade to insensitive
1787: assertTrue(rs.next());
1788: assertEquals("SINGLE ROW RESULT", rs.getString(1));
1789:
1790: assertTrue(stmt.getMoreResults());
1791: rs = stmt.getResultSet();
1792: assertTrue(rs.absolute(2));
1793: assertEquals("LINE TWO", rs.getString(2));
1794: assertTrue(rs.relative(-1));
1795: assertEquals("LINE ONE", rs.getString(2));
1796:
1797: assertTrue(stmt.getMoreResults());
1798: rs = stmt.getResultSet();
1799: assertTrue(rs.next());
1800: assertEquals("ANOTHER SINGLE ROW RESULT", rs.getString(1));
1801: } finally {
1802: dropProcedure("jtds_multiSet");
1803: stmt.close();
1804: // We can safely commit, mess cleaned up (we could rollback, too)
1805: con.commit();
1806: }
1807: }
1808:
1809: /**
1810: * Test for bug [1187927] Driver Hangs on Statement.execute().
1811: * <p/>
1812: * Versions 1.0.3 and prior entered an infinite loop when parsing an
1813: * unterminated multi-line comment.
1814: */
1815: public void testUnterminatedCommentParsing() throws Exception {
1816: Statement stmt = con.createStatement();
1817: try {
1818: stmt.execute("/* This is an unterminated comment");
1819: fail("Expecting parse exception");
1820: } catch (SQLException ex) {
1821: assertEquals("22025", ex.getSQLState());
1822: }
1823: stmt.close();
1824: }
1825:
1826: /**
1827: * Test that getString() on a varbinary column returns a hex string.
1828: */
1829: public void testBytesToString() throws Exception {
1830: Statement stmt = con.createStatement(
1831: ResultSet.TYPE_SCROLL_SENSITIVE,
1832: ResultSet.CONCUR_UPDATABLE);
1833: stmt
1834: .execute("CREATE TABLE #testbytes (id int primary key, b varbinary(8), i image, c varchar(255) null)");
1835: assertEquals(
1836: 1,
1837: stmt
1838: .executeUpdate("INSERT INTO #testbytes VALUES (1, 0x41424344, 0x41424344, null)"));
1839:
1840: ResultSet rs = stmt.executeQuery("SELECT * FROM #testbytes");
1841: assertNotNull(rs);
1842: assertTrue(rs.next());
1843: assertEquals("41424344", rs.getString(2));
1844: assertEquals("41424344", rs.getString(3));
1845: Clob clob = rs.getClob(2);
1846: assertEquals("41424344", clob.getSubString(1, (int) clob
1847: .length()));
1848: clob = rs.getClob(3);
1849: assertEquals("41424344", clob.getSubString(1, (int) clob
1850: .length()));
1851: //
1852: // Check that updating sensitive result sets yields the correct
1853: // results. This test is mainly for Sybase scroll sensitive client
1854: // side cursors.
1855: //
1856: rs.updateBytes(4, new byte[] { 0x41, 0x42, 0x43, 0x44 });
1857: rs.updateRow();
1858: assertEquals("ABCD", rs.getString(4));
1859: stmt.close();
1860: }
1861:
1862: /**
1863: * Tests that <code>executeUpdate("SELECT ...")</code> fails.
1864: */
1865: public void testExecuteUpdateSelect() throws Exception {
1866: Statement stmt = con.createStatement();
1867: try {
1868: stmt.executeUpdate("select 1");
1869: fail("Expecting an exception to be thrown");
1870: } catch (SQLException ex) {
1871: assertEquals("07000", ex.getSQLState());
1872: }
1873: stmt.close();
1874:
1875: PreparedStatement pstmt = con.prepareStatement("select 1");
1876: try {
1877: pstmt.executeUpdate();
1878: fail("Expecting an exception to be thrown");
1879: } catch (SQLException ex) {
1880: assertEquals("07000", ex.getSQLState());
1881: }
1882: stmt.close();
1883: }
1884:
1885: /**
1886: * Test for bug [1596743] executeQuery absorbs thread interrupt status
1887: */
1888: public void testThreadInterrupt() throws Exception {
1889: Thread.currentThread().interrupt();
1890: Statement stmt = con.createStatement();
1891: ResultSet rs = stmt.executeQuery("SELECT 1");
1892: rs.close();
1893: stmt.close();
1894: assertTrue(Thread.currentThread().isInterrupted());
1895: }
1896: }
|