0001: package net.sourceforge.jtds.test;
0002:
0003: import java.math.BigDecimal;
0004: import java.sql.*;
0005: import java.util.Calendar;
0006: import java.util.TimeZone;
0007: import java.util.GregorianCalendar;
0008:
0009: import net.sourceforge.jtds.jdbc.Driver;
0010: import net.sourceforge.jtds.util.Logger;
0011:
0012: import junit.framework.TestSuite;
0013:
0014: /**
0015: * test getting timestamps from the database.
0016: */
0017: public class TimestampTest extends DatabaseTestCase {
0018: public TimestampTest(String name) {
0019: super (name);
0020: }
0021:
0022: public static void main(String args[]) {
0023: boolean loggerActive = args.length > 0;
0024: Logger.setActive(loggerActive);
0025:
0026: if (args.length > 0) {
0027: junit.framework.TestSuite s = new TestSuite();
0028: for (int i = 0; i < args.length; i++) {
0029: s.addTest(new TimestampTest(args[i]));
0030: }
0031: junit.textui.TestRunner.run(s);
0032: } else {
0033: junit.textui.TestRunner.run(TimestampTest.class);
0034: }
0035:
0036: // new TimestampTest("test").testOutputParams();
0037: }
0038:
0039: public void testBigint0000() throws Exception {
0040: Statement stmt = con.createStatement();
0041: stmt.executeUpdate("create table #t0000 "
0042: + " (i decimal(28,10) not null, "
0043: + " s char(10) not null) ");
0044:
0045: final int rowsToAdd = 20;
0046: int count = 0;
0047:
0048: for (int i = 1; i <= rowsToAdd; i++) {
0049: String sql = "insert into #t0000 values (" + i + ", 'row"
0050: + i + "')";
0051: count += stmt.executeUpdate(sql);
0052: }
0053:
0054: stmt.close();
0055: assertEquals(count, rowsToAdd);
0056:
0057: PreparedStatement pstmt = con
0058: .prepareStatement("select i from #t0000 where i = ?");
0059:
0060: pstmt.setLong(1, 7);
0061: ResultSet rs = pstmt.executeQuery();
0062: assertNotNull(rs);
0063:
0064: assertTrue("Expected a result set", rs.next());
0065: assertEquals(rs.getLong(1), 7);
0066: assertTrue("Expected no result set", !rs.next());
0067:
0068: pstmt.setLong(1, 8);
0069: rs = pstmt.executeQuery();
0070: assertNotNull(rs);
0071:
0072: assertTrue("Expected a result set", rs.next());
0073: assertEquals(rs.getLong(1), 8);
0074: assertTrue("Expected no result set", !rs.next());
0075:
0076: pstmt.close();
0077: }
0078:
0079: public void testTimestamps0001() throws Exception {
0080: Statement stmt = con.createStatement();
0081: stmt.executeUpdate("create table #t0001 "
0082: + " (t1 datetime not null, "
0083: + " t2 datetime null, "
0084: + " t3 smalldatetime not null, "
0085: + " t4 smalldatetime null)");
0086: stmt.close();
0087:
0088: PreparedStatement pstmt = con
0089: .prepareStatement("insert into #t0001 values (?, '1998-03-09 15:35:06.4', "
0090: + " ?, '1998-03-09 15:35:00')");
0091: Timestamp t0 = Timestamp.valueOf("1998-03-09 15:35:06.4");
0092: Timestamp t1 = Timestamp.valueOf("1998-03-09 15:35:00");
0093:
0094: pstmt.setTimestamp(1, t0);
0095: pstmt.setTimestamp(2, t1);
0096: int count = pstmt.executeUpdate();
0097: assertTrue(count == 1);
0098: pstmt.close();
0099:
0100: pstmt = con
0101: .prepareStatement("select t1, t2, t3, t4 from #t0001");
0102:
0103: ResultSet rs = pstmt.executeQuery();
0104: assertNotNull(rs);
0105:
0106: assertTrue("Expected a result set", rs.next());
0107:
0108: assertEquals(t0, rs.getTimestamp(1));
0109: assertEquals(t0, rs.getTimestamp(2));
0110: assertEquals(t1, rs.getTimestamp(3));
0111: assertEquals(t1, rs.getTimestamp(4));
0112:
0113: pstmt.close();
0114: }
0115:
0116: public void testTimestamps0004() throws Exception {
0117: Statement stmt = con.createStatement();
0118: stmt.executeUpdate("create table #t0004 "
0119: + " (mytime datetime not null, "
0120: + " mytime2 datetime null, "
0121: + " mytime3 datetime null )");
0122: stmt.close();
0123:
0124: PreparedStatement pstmt = con
0125: .prepareStatement("insert into #t0004 values ('1964-02-14 10:00:00.0', ?, ?)");
0126:
0127: Timestamp t0 = Timestamp.valueOf("1964-02-14 10:00:00.0");
0128: pstmt.setTimestamp(1, t0);
0129: pstmt.setTimestamp(2, t0);
0130: assertEquals(1, pstmt.executeUpdate());
0131:
0132: pstmt.setNull(2, java.sql.Types.TIMESTAMP);
0133: assertEquals(1, pstmt.executeUpdate());
0134: pstmt.close();
0135:
0136: pstmt = con
0137: .prepareStatement("select mytime, mytime2, mytime3 from #t0004");
0138:
0139: ResultSet rs = pstmt.executeQuery();
0140: assertNotNull(rs);
0141: Timestamp t1, t2, t3;
0142:
0143: assertTrue("Expected a result set", rs.next());
0144: t1 = rs.getTimestamp(1);
0145: t2 = rs.getTimestamp(2);
0146: t3 = rs.getTimestamp(3);
0147: assertEquals(t0, t1);
0148: assertEquals(t0, t2);
0149: assertEquals(t0, t3);
0150:
0151: assertTrue("Expected a result set", rs.next());
0152: t1 = rs.getTimestamp(1);
0153: t2 = rs.getTimestamp(2);
0154: t3 = rs.getTimestamp(3);
0155: assertEquals(t0, t1);
0156: assertEquals(t0, t2);
0157: assertEquals(null, t3);
0158:
0159: pstmt.close();
0160: }
0161:
0162: public void testEscape(String sql, String expected)
0163: throws Exception {
0164: String tmp = con.nativeSQL(sql);
0165:
0166: assertEquals(tmp, expected);
0167: }
0168:
0169: public void testEscapes0006() throws Exception {
0170: testEscape("select * from tmp where d={d 1999-09-19}",
0171: "select * from tmp where d='19990919'");
0172: testEscape("select * from tmp where d={d '1999-09-19'}",
0173: "select * from tmp where d='19990919'");
0174: testEscape("select * from tmp where t={t 12:34:00}",
0175: "select * from tmp where t='12:34:00'");
0176: testEscape(
0177: "select * from tmp where ts={ts 1998-12-15 12:34:00.1234}",
0178: "select * from tmp where ts='19981215 12:34:00.123'");
0179: testEscape(
0180: "select * from tmp where ts={ts 1998-12-15 12:34:00}",
0181: "select * from tmp where ts='19981215 12:34:00.000'");
0182: testEscape(
0183: "select * from tmp where ts={ts 1998-12-15 12:34:00.1}",
0184: "select * from tmp where ts='19981215 12:34:00.100'");
0185: testEscape(
0186: "select * from tmp where ts={ts 1998-12-15 12:34:00}",
0187: "select * from tmp where ts='19981215 12:34:00.000'");
0188: testEscape("select * from tmp where d={d 1999-09-19}",
0189: "select * from tmp where d='19990919'");
0190: testEscape("select * from tmp where a like '\\%%'",
0191: "select * from tmp where a like '\\%%'");
0192: testEscape("select * from tmp where a like 'b%%' {escape 'b'}",
0193: "select * from tmp where a like 'b%%' escape 'b'");
0194: testEscape("select * from tmp where a like 'bbb' {escape 'b'}",
0195: "select * from tmp where a like 'bbb' escape 'b'");
0196: testEscape("select * from tmp where a='{fn user}'",
0197: "select * from tmp where a='{fn user}'");
0198: testEscape("select * from tmp where a={fn user()}",
0199: "select * from tmp where a=user_name()");
0200: }
0201:
0202: public void testPreparedStatement0007() throws Exception {
0203: Statement stmt = con.createStatement();
0204: stmt.executeUpdate("create table #t0007 "
0205: + " (i integer not null, "
0206: + " s char(10) not null) ");
0207:
0208: final int rowsToAdd = 20;
0209: int count = 0;
0210:
0211: for (int i = 1; i <= rowsToAdd; i++) {
0212: String sql = "insert into #t0007 values (" + i + ", 'row"
0213: + i + "')";
0214:
0215: count += stmt.executeUpdate(sql);
0216: }
0217:
0218: stmt.close();
0219: assertEquals(count, rowsToAdd);
0220:
0221: PreparedStatement pstmt = con
0222: .prepareStatement("select s from #t0007 where i = ?");
0223:
0224: pstmt.setInt(1, 7);
0225: ResultSet rs = pstmt.executeQuery();
0226: assertNotNull(rs);
0227:
0228: assertTrue("Expected a result set", rs.next());
0229: assertEquals(rs.getString(1).trim(), "row7");
0230: // assertTrue("Expected no result set", !rs.next());
0231:
0232: pstmt.setInt(1, 8);
0233: rs = pstmt.executeQuery();
0234: assertNotNull(rs);
0235:
0236: assertTrue("Expected a result set", rs.next());
0237: assertEquals(rs.getString(1).trim(), "row8");
0238: assertTrue("Expected no result set", !rs.next());
0239:
0240: pstmt.close();
0241: }
0242:
0243: public void testPreparedStatement0008() throws Exception {
0244: Statement stmt = con.createStatement();
0245: stmt.executeUpdate("create table #t0008 "
0246: + " (i integer not null, "
0247: + " s char(10) not null) ");
0248:
0249: PreparedStatement pstmt = con
0250: .prepareStatement("insert into #t0008 values (?, ?)");
0251:
0252: final int rowsToAdd = 8;
0253: final String theString = "abcdefghijklmnopqrstuvwxyz";
0254: int count = 0;
0255:
0256: for (int i = 1; i <= rowsToAdd; i++) {
0257: pstmt.setInt(1, i);
0258: pstmt.setString(2, theString.substring(0, i));
0259:
0260: count += pstmt.executeUpdate();
0261: }
0262:
0263: assertEquals(count, rowsToAdd);
0264: pstmt.close();
0265:
0266: ResultSet rs = stmt.executeQuery("select s, i from #t0008");
0267: assertNotNull(rs);
0268:
0269: count = 0;
0270:
0271: while (rs.next()) {
0272: count++;
0273: assertEquals(rs.getString(1).trim().length(), rs.getInt(2));
0274: }
0275: assertTrue(count == rowsToAdd);
0276: stmt.close();
0277: pstmt.close();
0278: }
0279:
0280: public void testPreparedStatement0009() throws Exception {
0281: Statement stmt = con.createStatement();
0282: stmt.executeUpdate("create table #t0009 "
0283: + " (i integer not null, "
0284: + " s char(10) not null) ");
0285:
0286: con.setAutoCommit(false);
0287:
0288: PreparedStatement pstmt = con
0289: .prepareStatement("insert into #t0009 values (?, ?)");
0290:
0291: int rowsToAdd = 8;
0292: final String theString = "abcdefghijklmnopqrstuvwxyz";
0293: int count = 0;
0294:
0295: for (int i = 1; i <= rowsToAdd; i++) {
0296: pstmt.setInt(1, i);
0297: pstmt.setString(2, theString.substring(0, i));
0298:
0299: count += pstmt.executeUpdate();
0300: }
0301:
0302: pstmt.close();
0303: assertEquals(count, rowsToAdd);
0304: con.rollback();
0305:
0306: ResultSet rs = stmt.executeQuery("select s, i from #t0009");
0307: assertNotNull(rs);
0308:
0309: count = 0;
0310:
0311: while (rs.next()) {
0312: count++;
0313: assertEquals(rs.getString(1).trim().length(), rs.getInt(2));
0314: }
0315:
0316: assertEquals(count, 0);
0317: con.commit();
0318:
0319: pstmt = con
0320: .prepareStatement("insert into #t0009 values (?, ?)");
0321: rowsToAdd = 6;
0322: count = 0;
0323:
0324: for (int i = 1; i <= rowsToAdd; i++) {
0325: pstmt.setInt(1, i);
0326: pstmt.setString(2, theString.substring(0, i));
0327:
0328: count += pstmt.executeUpdate();
0329: }
0330:
0331: assertEquals(count, rowsToAdd);
0332: con.commit();
0333: pstmt.close();
0334:
0335: rs = stmt.executeQuery("select s, i from #t0009");
0336:
0337: count = 0;
0338:
0339: while (rs.next()) {
0340: count++;
0341: assertEquals(rs.getString(1).trim().length(), rs.getInt(2));
0342: }
0343:
0344: assertEquals(count, rowsToAdd);
0345: con.commit();
0346: stmt.close();
0347: con.setAutoCommit(true);
0348: }
0349:
0350: public void testTransactions0010() throws Exception {
0351: Statement stmt = con.createStatement();
0352: stmt.executeUpdate("create table #t0010 "
0353: + " (i integer not null, "
0354: + " s char(10) not null) ");
0355:
0356: con.setAutoCommit(false);
0357:
0358: PreparedStatement pstmt = con
0359: .prepareStatement("insert into #t0010 values (?, ?)");
0360:
0361: int rowsToAdd = 8;
0362: final String theString = "abcdefghijklmnopqrstuvwxyz";
0363: int count = 0;
0364:
0365: for (int i = 1; i <= rowsToAdd; i++) {
0366: pstmt.setInt(1, i);
0367: pstmt.setString(2, theString.substring(0, i));
0368:
0369: count += pstmt.executeUpdate();
0370: }
0371:
0372: assertEquals(count, rowsToAdd);
0373: con.rollback();
0374:
0375: ResultSet rs = stmt.executeQuery("select s, i from #t0010");
0376: assertNotNull(rs);
0377:
0378: count = 0;
0379:
0380: while (rs.next()) {
0381: count++;
0382: assertEquals(rs.getString(1).trim().length(), rs.getInt(2));
0383: }
0384:
0385: assertEquals(count, 0);
0386:
0387: rowsToAdd = 6;
0388:
0389: for (int j = 1; j <= 2; j++) {
0390: count = 0;
0391:
0392: for (int i = 1; i <= rowsToAdd; i++) {
0393: pstmt.setInt(1, i + ((j - 1) * rowsToAdd));
0394: pstmt.setString(2, theString.substring(0, i));
0395:
0396: count += pstmt.executeUpdate();
0397: }
0398:
0399: assertEquals(count, rowsToAdd);
0400: con.commit();
0401: }
0402:
0403: rs = stmt.executeQuery("select s, i from #t0010");
0404:
0405: count = 0;
0406:
0407: while (rs.next()) {
0408: count++;
0409:
0410: int i = rs.getInt(2);
0411:
0412: if (i > rowsToAdd) {
0413: i -= rowsToAdd;
0414: }
0415:
0416: assertEquals(rs.getString(1).trim().length(), i);
0417: }
0418:
0419: assertEquals(count, (2 * rowsToAdd));
0420:
0421: stmt.close();
0422: pstmt.close();
0423: con.setAutoCommit(true);
0424: }
0425:
0426: public void testEmptyResults0011() throws Exception {
0427: Statement stmt = con.createStatement();
0428: stmt.executeUpdate("create table #t0011 "
0429: + " (mytime datetime not null, "
0430: + " mytime2 datetime null )");
0431:
0432: ResultSet rs = stmt
0433: .executeQuery("select mytime, mytime2 from #t0011");
0434: assertNotNull(rs);
0435: assertTrue("Expected no result set", !rs.next());
0436:
0437: rs = stmt.executeQuery("select mytime, mytime2 from #t0011");
0438: assertTrue("Expected no result set", !rs.next());
0439: stmt.close();
0440: }
0441:
0442: public void testEmptyResults0012() throws Exception {
0443: Statement stmt = con.createStatement();
0444: stmt.executeUpdate("create table #t0012 "
0445: + " (mytime datetime not null, "
0446: + " mytime2 datetime null )");
0447: stmt.close();
0448:
0449: PreparedStatement pstmt = con
0450: .prepareStatement("select mytime, mytime2 from #t0012");
0451:
0452: ResultSet rs = pstmt.executeQuery();
0453: assertNotNull(rs);
0454: assertTrue("Expected no result", !rs.next());
0455: rs.close();
0456:
0457: rs = pstmt.executeQuery();
0458: assertTrue("Expected no result", !rs.next());
0459: pstmt.close();
0460: }
0461:
0462: public void testEmptyResults0013() throws Exception {
0463: Statement stmt = con.createStatement();
0464: stmt.executeUpdate("create table #t0013 "
0465: + " (mytime datetime not null, "
0466: + " mytime2 datetime null )");
0467:
0468: ResultSet rs1 = stmt
0469: .executeQuery("select mytime, mytime2 from #t0013");
0470: assertNotNull(rs1);
0471: assertTrue("Expected no result set", !rs1.next());
0472: stmt.close();
0473:
0474: PreparedStatement pstmt = con
0475: .prepareStatement("select mytime, mytime2 from #t0013");
0476: ResultSet rs2 = pstmt.executeQuery();
0477: assertNotNull(rs2);
0478: assertTrue("Expected no result", !rs2.next());
0479: pstmt.close();
0480: }
0481:
0482: public void testForBrowse0014() throws Exception {
0483: Statement stmt = con.createStatement();
0484: stmt.executeUpdate("create table #t0014 (i integer not null)");
0485:
0486: PreparedStatement pstmt = con
0487: .prepareStatement("insert into #t0014 values (?)");
0488:
0489: final int rowsToAdd = 100;
0490: int count = 0;
0491:
0492: for (int i = 1; i <= rowsToAdd; i++) {
0493: pstmt.setInt(1, i);
0494: count += pstmt.executeUpdate();
0495: }
0496:
0497: assertEquals(count, rowsToAdd);
0498: pstmt.close();
0499:
0500: pstmt = con.prepareStatement("select i from #t0014 for browse");
0501: ResultSet rs = pstmt.executeQuery();
0502: assertNotNull(rs);
0503: count = 0;
0504:
0505: while (rs.next()) {
0506: rs.getInt("i");
0507: count++;
0508: }
0509:
0510: assertEquals(count, rowsToAdd);
0511: pstmt.close();
0512:
0513: rs = stmt.executeQuery("select * from #t0014");
0514: assertNotNull(rs);
0515: count = 0;
0516:
0517: while (rs.next()) {
0518: rs.getInt("i");
0519: count++;
0520: }
0521:
0522: assertEquals(count, rowsToAdd);
0523:
0524: rs = stmt.executeQuery("select * from #t0014");
0525: assertNotNull(rs);
0526: count = 0;
0527:
0528: while (rs.next() && count < 5) {
0529: rs.getInt("i");
0530: count++;
0531: }
0532: assertTrue(count == 5);
0533:
0534: rs = stmt.executeQuery("select * from #t0014");
0535: assertNotNull(rs);
0536: count = 0;
0537:
0538: while (rs.next()) {
0539: rs.getInt("i");
0540: count++;
0541: }
0542:
0543: assertEquals(count, rowsToAdd);
0544: stmt.close();
0545: }
0546:
0547: public void testMultipleResults0015() throws Exception {
0548: Statement stmt = con.createStatement();
0549: stmt.executeUpdate("create table #t0015 "
0550: + " (i integer not null, "
0551: + " s char(10) not null) ");
0552:
0553: PreparedStatement pstmt = con
0554: .prepareStatement("insert into #t0015 values (?, ?)");
0555:
0556: int rowsToAdd = 8;
0557: final String theString = "abcdefghijklmnopqrstuvwxyz";
0558: int count = 0;
0559:
0560: for (int i = 1; i <= rowsToAdd; i++) {
0561: pstmt.setInt(1, i);
0562: pstmt.setString(2, theString.substring(0, i));
0563:
0564: count += pstmt.executeUpdate();
0565: }
0566:
0567: assertEquals(count, rowsToAdd);
0568: pstmt.close();
0569:
0570: stmt.execute("select s from #t0015 select i from #t0015");
0571: ResultSet rs = stmt.getResultSet();
0572: assertNotNull(rs);
0573: count = 0;
0574:
0575: while (rs.next()) {
0576: count++;
0577: }
0578:
0579: assertEquals(count, rowsToAdd);
0580:
0581: assertTrue(stmt.getMoreResults());
0582:
0583: rs = stmt.getResultSet();
0584: assertNotNull(rs);
0585: count = 0;
0586:
0587: while (rs.next()) {
0588: count++;
0589: }
0590:
0591: assertEquals(count, rowsToAdd);
0592:
0593: rs = stmt.executeQuery("select i, s from #t0015");
0594: count = 0;
0595:
0596: while (rs.next()) {
0597: count++;
0598: }
0599:
0600: assertEquals(count, rowsToAdd);
0601: stmt.close();
0602: }
0603:
0604: public void testMissingParameter0016() throws Exception {
0605: Statement stmt = con.createStatement();
0606: stmt.executeUpdate("create table #t0016 "
0607: + " (i integer not null, "
0608: + " s char(10) not null) ");
0609:
0610: final int rowsToAdd = 20;
0611: int count = 0;
0612:
0613: for (int i = 1; i <= rowsToAdd; i++) {
0614: String sql = "insert into #t0016 values (" + i + ", 'row"
0615: + i + "')";
0616: count += stmt.executeUpdate(sql);
0617: }
0618:
0619: stmt.close();
0620: assertEquals(count, rowsToAdd);
0621:
0622: PreparedStatement pstmt = con
0623: .prepareStatement("select s from #t0016 where i=? and s=?");
0624:
0625: // see what happens if neither is set
0626: try {
0627: pstmt.executeQuery();
0628: assertTrue("Failed to throw exception", false);
0629: } catch (SQLException e) {
0630: assertTrue("07000".equals(e.getSQLState())
0631: && (e.getMessage().indexOf('1') >= 0 || e
0632: .getMessage().indexOf('2') >= 0));
0633: }
0634:
0635: pstmt.clearParameters();
0636:
0637: try {
0638: pstmt.setInt(1, 7);
0639: pstmt.setString(2, "row7");
0640: pstmt.clearParameters();
0641:
0642: pstmt.executeQuery();
0643: assertTrue("Failed to throw exception", false);
0644: } catch (SQLException e) {
0645: assertTrue("07000".equals(e.getSQLState())
0646: && (e.getMessage().indexOf('1') >= 0 || e
0647: .getMessage().indexOf('2') >= 0));
0648: }
0649:
0650: pstmt.clearParameters();
0651:
0652: try {
0653: pstmt.setInt(1, 7);
0654: pstmt.executeQuery();
0655: assertTrue("Failed to throw exception", false);
0656: } catch (SQLException e) {
0657: assertTrue("07000".equals(e.getSQLState())
0658: && e.getMessage().indexOf('2') >= 0);
0659: }
0660:
0661: pstmt.clearParameters();
0662:
0663: try {
0664: pstmt.setString(2, "row7");
0665: pstmt.executeQuery();
0666: assertTrue("Failed to throw exception", false);
0667: } catch (SQLException e) {
0668: assertTrue("07000".equals(e.getSQLState())
0669: && e.getMessage().indexOf('1') >= 0);
0670: }
0671:
0672: pstmt.close();
0673: }
0674:
0675: Object[][] getDatatypes() {
0676: return new Object[][] {
0677: /* { "binary(272)",
0678:
0679: "0x101112131415161718191a1b1c1d1e1f" +
0680: "101112131415161718191a1b1c1d1e1f" +
0681: "101112131415161718191a1b1c1d1e1f" +
0682: "101112131415161718191a1b1c1d1e1f" +
0683: "101112131415161718191a1b1c1d1e1f" +
0684: "101112131415161718191a1b1c1d1e1f" +
0685: "101112131415161718191a1b1c1d1e1f" +
0686: "101112131415161718191a1b1c1d1e1f" +
0687: "101112131415161718191a1b1c1d1e1f" +
0688: "101112131415161718191a1b1c1d1e1f" +
0689: "101112131415161718191a1b1c1d1e1f" +
0690: "101112131415161718191a1b1c1d1e1f" +
0691: "101112131415161718191a1b1c1d1e1f" +
0692: "101112131415161718191a1b1c1d1e1f" +
0693: "101112131415161718191a1b1c1d1e1f" +
0694: "101112131415161718191a1b1c1d1e1f" +
0695: "101112131415161718191a1b1c1d1e1f" +
0696: "101112131415161718191a1b1c1d1e1f" +
0697: "101112131415161718191a1b1c1d1e1f" +
0698: "101112131415161718191a1b1c1d1e1f" +
0699: "101112131415161718191a1b1c1d1e1f" +
0700: "101112131415161718191a1b1c1d1e1f" +
0701: "101112131415161718191a1b1c1d1e1f" +
0702: "101112131415161718191a1b1c1d1e1f" +
0703: "101112131415161718191a1b1c1d1e1f" +
0704: "101112131415161718191a1b1c1d1e1f" +
0705: "101112131415161718191a1b1c1d1e1f",
0706:
0707: new byte[] {
0708: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0709: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0710: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0711: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0712: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0713: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0714: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0715: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0716: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0717: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0718: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0719: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0720: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0721: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0722: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0723: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f,
0724: 0x10, 0x11, 0x12, 0x13, 0x14, 0x15, 0x16, 0x17, 0x18, 0x19, 0x1a, 0x1b, 0x1c, 0x1d, 0x1e, 0x1f
0725: } },
0726: */
0727: { "float(6)", "65.4321", new BigDecimal("65.4321") },
0728: { "binary(5)", "0x1213141516",
0729: new byte[] { 0x12, 0x13, 0x14, 0x15, 0x16 } },
0730: { "varbinary(4)", "0x1718191A",
0731: new byte[] { 0x17, 0x18, 0x19, 0x1A } },
0732: { "varchar(8)", "'12345678'", "12345678" },
0733: { "datetime", "'19990815 21:29:59.01'",
0734: Timestamp.valueOf("1999-08-15 21:29:59.01") },
0735: { "smalldatetime", "'19990215 20:45'",
0736: Timestamp.valueOf("1999-02-15 20:45:00") },
0737: { "float(6)", "65.4321", new Float(65.4321) /* new BigDecimal("65.4321") */},
0738: { "float(14)", "1.123456789", new Double(1.123456789) /*new BigDecimal("1.123456789") */},
0739: { "real", "7654321.0", new Double(7654321.0) },
0740: { "int", "4097", new Integer(4097) },
0741: { "float(6)", "65.4321", new BigDecimal("65.4321") },
0742: { "float(14)", "1.123456789",
0743: new BigDecimal("1.123456789") },
0744: { "decimal(10,3)", "1234567.089",
0745: new BigDecimal("1234567.089") },
0746: { "numeric(5,4)", "1.2345", new BigDecimal("1.2345") },
0747: { "smallint", "4094", new Short((short) 4094) },
0748: // {"tinyint", "127", new Byte((byte) 127)},
0749: // {"tinyint", "-128", new Byte((byte) -128)},
0750: { "tinyint", "127", new Byte((byte) 127) },
0751: { "tinyint", "128", new Short((short) 128) },
0752: { "money", "19.95", new BigDecimal("19.95") },
0753: { "smallmoney", "9.97", new BigDecimal("9.97") },
0754: { "bit", "1", Boolean.TRUE },
0755: // { "text", "'abcedefg'", "abcdefg" },
0756: /* { "char(1000)",
0757: "'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'",
0758: "123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890" },
0759: */
0760: // { "char(1000)", "'1234567890'", "1234567890" },
0761: // { "image", "0x0a0a0b", new byte[] { 0x0a, 0x0a, 0x0b } },
0762: };
0763: }
0764:
0765: public void testOutputParams() throws Exception {
0766: Statement stmt = con.createStatement();
0767: dropProcedure("#jtds_outputTest");
0768:
0769: Object[][] datatypes = getDatatypes();
0770:
0771: for (int i = 0; i < datatypes.length; i++) {
0772: String valueToAssign;
0773: boolean bImage = datatypes[i][0].equals("image");
0774:
0775: if (bImage) {
0776: valueToAssign = "";
0777: } else {
0778: valueToAssign = " = " + datatypes[i][1];
0779: }
0780:
0781: String sql = "create procedure #jtds_outputTest " + "@a1 "
0782: + datatypes[i][0] + " = null out "
0783: + "as select @a1" + valueToAssign;
0784: stmt.executeUpdate(sql);
0785:
0786: for (int pass = 0; (pass < 2 && !bImage) || pass < 1; pass++) {
0787: CallableStatement cstmt = con
0788: .prepareCall("{call #jtds_outputTest(?)}");
0789:
0790: int jtype = getType(datatypes[i][2]);
0791:
0792: if (pass == 1)
0793: cstmt.setObject(1, null, jtype, 10);
0794: if (jtype == java.sql.Types.NUMERIC
0795: || jtype == java.sql.Types.DECIMAL) {
0796: cstmt.registerOutParameter(1, jtype, 10);
0797:
0798: if (pass == 0) {
0799: cstmt.setObject(1, datatypes[i][2], jtype, 10);
0800: }
0801: } else if (jtype == java.sql.Types.VARCHAR) {
0802: cstmt.registerOutParameter(1, jtype);
0803:
0804: if (pass == 0) {
0805: cstmt.setObject(1, datatypes[i][2]);
0806: }
0807: } else {
0808: cstmt.registerOutParameter(1, jtype);
0809:
0810: if (pass == 0) {
0811: cstmt.setObject(1, datatypes[i][2]);
0812: }
0813: }
0814:
0815: assertEquals(bImage, cstmt.execute());
0816:
0817: while (cstmt.getMoreResults()
0818: || cstmt.getUpdateCount() != -1)
0819: ;
0820:
0821: if (jtype == java.sql.Types.VARBINARY) {
0822: assertTrue(compareBytes(cstmt.getBytes(1),
0823: (byte[]) datatypes[i][2]) == 0);
0824: } else if (datatypes[i][2] instanceof Number) {
0825: Number n = (Number) cstmt.getObject(1);
0826:
0827: if (n != null) {
0828: assertEquals("Failed on " + datatypes[i][0], n
0829: .doubleValue(),
0830: ((Number) datatypes[i][2])
0831: .doubleValue(), 0.001);
0832: } else {
0833: assertEquals("Failed on " + datatypes[i][0], n,
0834: datatypes[i][2]);
0835: }
0836: } else {
0837: assertEquals("Failed on " + datatypes[i][0], cstmt
0838: .getObject(1), datatypes[i][2]);
0839: }
0840:
0841: cstmt.close();
0842: } // for (pass
0843:
0844: stmt.executeUpdate(" drop procedure #jtds_outputTest");
0845: } // for (int
0846:
0847: stmt.close();
0848: }
0849:
0850: public void testStatements0020() throws Exception {
0851: Statement stmt = con.createStatement();
0852: stmt.executeUpdate("create table #t0020a ( "
0853: + " i1 int not null, "
0854: + " s1 char(10) not null "
0855: + ") " + "");
0856: stmt.executeUpdate("create table #t0020b ( "
0857: + " i2a int not null, "
0858: + " i2b int not null, "
0859: + " s2 char(10) not null "
0860: + ") " + "");
0861: stmt.executeUpdate("create table #t0020c ( "
0862: + " i3 int not null, "
0863: + " s3 char(10) not null "
0864: + ") " + "");
0865:
0866: int nextB = 1;
0867: int nextC = 1;
0868:
0869: for (int i = 1; i < 50; i++) {
0870: stmt.executeUpdate("insert into #t0020a " + " values(" + i
0871: + ", " + " 'row" + i + "') " + "");
0872:
0873: for (int j = nextB; (nextB % 5) != 0; j++, nextB++) {
0874: stmt.executeUpdate("insert into #t0020b " + " values("
0875: + i + ", " + " " + j + ", "
0876: + " 'row" + i + "." + j + "' "
0877: + " )" + "");
0878:
0879: for (int k = nextC; (nextC % 3) != 0; k++, nextC++) {
0880: stmt.executeUpdate("insert into #t0020c "
0881: + " values(" + j + ", " + " 'row"
0882: + i + "." + j + "." + k + "' "
0883: + " )" + "");
0884: }
0885: }
0886: }
0887:
0888: Statement stmtA = con.createStatement();
0889: PreparedStatement stmtB = con
0890: .prepareStatement("select i2b, s2 from #t0020b where i2a=?");
0891: PreparedStatement stmtC = con
0892: .prepareStatement("select s3 from #t0020c where i3=?");
0893:
0894: ResultSet rs1 = stmtA.executeQuery("select i1 from #t0020a");
0895: assertNotNull(rs1);
0896:
0897: while (rs1.next()) {
0898: stmtB.setInt(1, rs1.getInt("i1"));
0899: ResultSet rs2 = stmtB.executeQuery();
0900: assertNotNull(rs2);
0901:
0902: while (rs2.next()) {
0903: stmtC.setInt(1, rs2.getInt(1));
0904: ResultSet rs3 = stmtC.executeQuery();
0905: assertNotNull(rs3);
0906: rs3.next();
0907: }
0908: }
0909:
0910: stmt.close();
0911: stmtA.close();
0912: stmtB.close();
0913: stmtC.close();
0914: }
0915:
0916: public void testBlob0021() throws Exception {
0917: byte smallarray[] = { 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07,
0918: 0x08, 0x09, 0x0A, 0x0B, 0x0C, 0x0D, 0x0E, 0x0F, 0x10 };
0919:
0920: byte array1[] = { 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07,
0921: 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08,
0922: 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x01,
0923: 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x01, 0x02,
0924: 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x01, 0x02, 0x03,
0925: 0x04, 0x05, 0x06, 0x07, 0x08, 0x01, 0x02, 0x03, 0x04,
0926: 0x05, 0x06, 0x07, 0x08, 0x01, 0x02, 0x03, 0x04, 0x05,
0927: 0x06, 0x07, 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06,
0928: 0x07, 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07,
0929: 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08,
0930: 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x01,
0931: 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x01, 0x02,
0932: 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x01, 0x02, 0x03,
0933: 0x04, 0x05, 0x06, 0x07, 0x08, 0x01, 0x02, 0x03, 0x04,
0934: 0x05, 0x06, 0x07, 0x08, 0x01, 0x02, 0x03, 0x04, 0x05,
0935: 0x06, 0x07, 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06,
0936: 0x07, 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07,
0937: 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08,
0938: 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x01,
0939: 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x01, 0x02,
0940: 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x01, 0x02, 0x03,
0941: 0x04, 0x05, 0x06, 0x07, 0x08, 0x01, 0x02, 0x03, 0x04,
0942: 0x05, 0x06, 0x07, 0x08, 0x01, 0x02, 0x03, 0x04, 0x05,
0943: 0x06, 0x07, 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06,
0944: 0x07, 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07,
0945: 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08,
0946: 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x01,
0947: 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x01, 0x02,
0948: 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x01, 0x02, 0x03,
0949: 0x04, 0x05, 0x06, 0x07, 0x08, 0x01, 0x02, 0x03, 0x04,
0950: 0x05, 0x06, 0x07, 0x08, 0x01, 0x02, 0x03, 0x04, 0x05,
0951: 0x06, 0x07, 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06,
0952: 0x07, 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07,
0953: 0x08, 0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08 };
0954:
0955: String bigtext1 = "abcdefghijklmnop" + "abcdefghijklmnop"
0956: + "abcdefghijklmnop" + "abcdefghijklmnop"
0957: + "abcdefghijklmnop" + "abcdefghijklmnop"
0958: + "abcdefghijklmnop" + "abcdefghijklmnop"
0959: + "abcdefghijklmnop" + "abcdefghijklmnop"
0960: + "abcdefghijklmnop" + "abcdefghijklmnop"
0961: + "abcdefghijklmnop" + "abcdefghijklmnop"
0962: + "abcdefghijklmnop" + "abcdefghijklmnop"
0963: + "abcdefghijklmnop" + "abcdefghijklmnop"
0964: + "abcdefghijklmnop" + "abcdefghijklmnop"
0965: + "abcdefghijklmnop" + "abcdefghijklmnop"
0966: + "abcdefghijklmnop" + "abcdefghijklmnop"
0967: + "abcdefghijklmnop" + "abcdefghijklmnop"
0968: + "abcdefghijklmnop" + "abcdefghijklmnop"
0969: + "abcdefghijklmnop" + "abcdefghijklmnop" + "";
0970:
0971: Statement stmt = con.createStatement();
0972:
0973: dropTable("#t0021");
0974:
0975: stmt.executeUpdate("create table #t0021 ( "
0976: + " mybinary binary(16) not null, "
0977: + " myimage image not null, "
0978: + " mynullimage image null, "
0979: + " mytext text not null, "
0980: + " mynulltext text null) ");
0981:
0982: // Insert a row without nulls via a Statement
0983: PreparedStatement insert = con
0984: .prepareStatement("insert into #t0021( "
0985: + " mybinary, "
0986: + " myimage, "
0987: + " mynullimage, "
0988: + " mytext, "
0989: + " mynulltext "
0990: + ") "
0991: + "values(?, ?, ?, ?, ?) ");
0992:
0993: insert.setBytes(1, smallarray);
0994: insert.setBytes(2, array1);
0995: insert.setBytes(3, array1);
0996: insert.setString(4, bigtext1);
0997: insert.setString(5, bigtext1);
0998:
0999: int count = insert.executeUpdate();
1000: assertEquals(count, 1);
1001: insert.close();
1002:
1003: ResultSet rs = stmt.executeQuery("select * from #t0021");
1004: assertNotNull(rs);
1005:
1006: assertTrue("Expected a result set", rs.next());
1007:
1008: byte[] a1 = rs.getBytes("myimage");
1009: byte[] a2 = rs.getBytes("mynullimage");
1010: String s1 = rs.getString("mytext");
1011: String s2 = rs.getString("mynulltext");
1012:
1013: assertEquals(0, compareBytes(a1, array1));
1014: assertEquals(0, compareBytes(a2, array1));
1015: assertEquals(bigtext1, s1);
1016: assertEquals(bigtext1, s2);
1017:
1018: stmt.close();
1019: }
1020:
1021: public void testNestedStatements0022() throws Exception {
1022: Statement stmt = con.createStatement();
1023: stmt.executeUpdate("create table #t0022a "
1024: + " (i integer not null, "
1025: + " str char(254) not null) ");
1026:
1027: stmt.executeUpdate("create table #t0022b "
1028: + " (i integer not null, "
1029: + " t datetime not null) ");
1030:
1031: PreparedStatement pStmtA = con
1032: .prepareStatement("insert into #t0022a values (?, ?)");
1033: PreparedStatement pStmtB = con
1034: .prepareStatement("insert into #t0022b values (?, getdate())");
1035:
1036: final int rowsToAdd = 100;
1037: int count = 0;
1038:
1039: for (int i = 1; i <= rowsToAdd; i++) {
1040: pStmtA.setInt(1, i);
1041: StringBuffer tmp = new StringBuffer(255);
1042:
1043: while (tmp.length() < 240) {
1044: tmp.append("row ").append(i).append(". ");
1045: }
1046:
1047: pStmtA.setString(2, tmp.toString());
1048: count += pStmtA.executeUpdate();
1049:
1050: pStmtB.setInt(1, i);
1051: pStmtB.executeUpdate();
1052: }
1053:
1054: pStmtA.close();
1055: pStmtB.close();
1056: assertEquals(count, rowsToAdd);
1057:
1058: Statement stmtA = con.createStatement();
1059: Statement stmtB = con.createStatement();
1060:
1061: count = 0;
1062: ResultSet rsA = stmtA.executeQuery("select * from #t0022a");
1063: assertNotNull(rsA);
1064:
1065: while (rsA.next()) {
1066: count++;
1067:
1068: ResultSet rsB = stmtB
1069: .executeQuery("select * from #t0022b where i="
1070: + rsA.getInt("i"));
1071:
1072: assertNotNull(rsB);
1073: assertTrue("Expected a result set", rsB.next());
1074: assertTrue("Expected no result set", !rsB.next());
1075: }
1076:
1077: assertEquals(count, rowsToAdd);
1078:
1079: stmt.close();
1080: stmtA.close();
1081: stmtB.close();
1082: }
1083:
1084: public void testPrimaryKeyFloat0023() throws Exception {
1085: Double d[] = { new Double(-1.0), new Double(1234.543),
1086: new Double(0.0), new Double(1), new Double(-2.0),
1087: new Double(0.14), new Double(0.79),
1088: new Double(1000000.12345), new Double(-1000000.12345),
1089: new Double(1000000), new Double(-1000000),
1090: new Double(1.7E+308), new Double(1.7E-307) // jikes 1.04 has a bug and can't handle 1.7E-308
1091: };
1092:
1093: Statement stmt = con.createStatement();
1094: stmt.executeUpdate("" + "create table #t0023 "
1095: + " (pk float not null, "
1096: + " type char(30) not null, " + " b bit, "
1097: + " str char(30) not null, "
1098: + " t int identity(1,1), "
1099: + " primary key (pk, type)) ");
1100:
1101: PreparedStatement pstmt = con
1102: .prepareStatement("insert into #t0023 (pk, type, b, str) values(?, 'prepared', 0, ?)");
1103:
1104: for (int i = 0; i < d.length; i++) {
1105: pstmt.setDouble(1, d[i].doubleValue());
1106: pstmt.setString(2, (d[i]).toString());
1107: int preparedCount = pstmt.executeUpdate();
1108:
1109: assertEquals(preparedCount, 1);
1110:
1111: int adhocCount = stmt.executeUpdate(""
1112: + "insert into #t0023 "
1113: + " (pk, type, b, str) " + " values(" + " "
1114: + d[i] + ", " + " 'adhoc', "
1115: + " 1, " + " '" + d[i]
1116: + "') ");
1117:
1118: assertEquals(adhocCount, 1);
1119: }
1120:
1121: int count = 0;
1122: ResultSet rs = stmt
1123: .executeQuery("select * from #t0023 where type='prepared' order by t");
1124: assertNotNull(rs);
1125:
1126: while (rs.next()) {
1127: assertEquals(d[count].toString(), "" + rs.getDouble("pk"));
1128: count++;
1129: }
1130:
1131: assertEquals(count, d.length);
1132:
1133: count = 0;
1134: rs = stmt
1135: .executeQuery("select * from #t0023 where type='adhoc' order by t");
1136:
1137: while (rs.next()) {
1138: assertEquals(d[count].toString(), "" + rs.getDouble("pk"));
1139: count++;
1140: }
1141:
1142: assertEquals(count, d.length);
1143:
1144: stmt.close();
1145: pstmt.close();
1146: }
1147:
1148: public void testPrimaryKeyReal0024() throws Exception {
1149: Float d[] = { new Float(-1.0), new Float(1234.543),
1150: new Float(0.0), new Float(1), new Float(-2.0),
1151: new Float(0.14), new Float(0.79),
1152: new Float(1000000.12345), new Float(-1000000.12345),
1153: new Float(1000000), new Float(-1000000),
1154: new Float(3.4E+38), new Float(3.4E-38) };
1155:
1156: Statement stmt = con.createStatement();
1157: stmt.executeUpdate("" + "create table #t0024 "
1158: + " (pk real not null, "
1159: + " type char(30) not null, "
1160: + " b bit, "
1161: + " str char(30) not null, "
1162: + " t int identity(1,1), "
1163: + " primary key (pk, type)) ");
1164:
1165: PreparedStatement pstmt = con
1166: .prepareStatement("insert into #t0024 (pk, type, b, str) values(?, 'prepared', 0, ?)");
1167:
1168: for (int i = 0; i < d.length; i++) {
1169: pstmt.setFloat(1, d[i].floatValue());
1170: pstmt.setString(2, (d[i]).toString());
1171: int preparedCount = pstmt.executeUpdate();
1172: assertTrue(preparedCount == 1);
1173:
1174: int adhocCount = stmt.executeUpdate(""
1175: + "insert into #t0024 "
1176: + " (pk, type, b, str) " + " values(" + " "
1177: + d[i] + ", " + " 'adhoc', "
1178: + " 1, " + " '" + d[i]
1179: + "') ");
1180: assertEquals(adhocCount, 1);
1181: }
1182:
1183: int count = 0;
1184: ResultSet rs = stmt
1185: .executeQuery("select * from #t0024 where type='prepared' order by t");
1186: assertNotNull(rs);
1187:
1188: while (rs.next()) {
1189: String s1 = d[count].toString().trim();
1190: String s2 = ("" + rs.getFloat("pk")).trim();
1191:
1192: assertTrue(s1.equalsIgnoreCase(s2));
1193: count++;
1194: }
1195:
1196: assertEquals(count, d.length);
1197:
1198: count = 0;
1199: rs = stmt
1200: .executeQuery("select * from #t0024 where type='adhoc' order by t");
1201:
1202: while (rs.next()) {
1203: String s1 = d[count].toString().trim();
1204: String s2 = ("" + rs.getFloat("pk")).trim();
1205:
1206: assertTrue(s1.equalsIgnoreCase(s2));
1207: count++;
1208: }
1209:
1210: assertEquals(count, d.length);
1211:
1212: stmt.close();
1213: pstmt.close();
1214: }
1215:
1216: public void testGetBoolean0025() throws Exception {
1217: Statement stmt = con.createStatement();
1218: stmt.executeUpdate("create table #t0025 "
1219: + " (i integer, " + " b bit, "
1220: + " s char(5), " + " f float) ");
1221:
1222: // @todo Check which CHAR/VARCHAR values should be true and which should be false.
1223: assertTrue(stmt
1224: .executeUpdate("insert into #t0025 values(0, 0, 'false', 0.0)") == 1);
1225: assertTrue(stmt
1226: .executeUpdate("insert into #t0025 values(0, 0, '0', 0.0)") == 1);
1227: assertTrue(stmt
1228: .executeUpdate("insert into #t0025 values(1, 1, 'true', 7.0)") == 1);
1229: assertTrue(stmt
1230: .executeUpdate("insert into #t0025 values(2, 1, '1', -5.0)") == 1);
1231:
1232: ResultSet rs = stmt
1233: .executeQuery("select * from #t0025 order by i");
1234: assertNotNull(rs);
1235:
1236: assertTrue("Expected a result set", rs.next());
1237:
1238: assertTrue(!rs.getBoolean("i"));
1239: assertTrue(!rs.getBoolean("b"));
1240: assertTrue(!rs.getBoolean("s"));
1241: assertTrue(!rs.getBoolean("f"));
1242:
1243: assertTrue("Expected a result set", rs.next());
1244:
1245: assertTrue(!rs.getBoolean("i"));
1246: assertTrue(!rs.getBoolean("b"));
1247: assertTrue(!rs.getBoolean("s"));
1248: assertTrue(!rs.getBoolean("f"));
1249:
1250: assertTrue("Expected a result set", rs.next());
1251:
1252: assertTrue(rs.getBoolean("i"));
1253: assertTrue(rs.getBoolean("b"));
1254: assertTrue(rs.getBoolean("s"));
1255: assertTrue(rs.getBoolean("f"));
1256:
1257: assertTrue("Expected a result set", rs.next());
1258:
1259: assertTrue(rs.getBoolean("i"));
1260: assertTrue(rs.getBoolean("b"));
1261: assertTrue(rs.getBoolean("s"));
1262: assertTrue(rs.getBoolean("f"));
1263:
1264: assertTrue("Expected no result set", !rs.next());
1265:
1266: stmt.close();
1267: }
1268:
1269: /**
1270: * <b>SAfe</b> Tests whether cursor-based statements still work ok when
1271: * nested. Similar to <code>testNestedStatements0022</code>, which tests
1272: * the same with plain (non-cursor-based) statements (and unfortunately
1273: * fails).
1274: *
1275: * @throws Exception if an Exception occurs (very relevant, huh?)
1276: */
1277: public void testNestedStatements0026() throws Exception {
1278: Statement stmt = con.createStatement();
1279: stmt.executeUpdate("create table #t0026a "
1280: + " (i integer not null, "
1281: + " str char(254) not null) ");
1282:
1283: stmt.executeUpdate("create table #t0026b "
1284: + " (i integer not null, "
1285: + " t datetime not null) ");
1286: stmt.close();
1287:
1288: PreparedStatement pstmtA = con
1289: .prepareStatement("insert into #t0026a values (?, ?)");
1290: PreparedStatement pstmtB = con
1291: .prepareStatement("insert into #t0026b values (?, getdate())");
1292:
1293: final int rowsToAdd = 100;
1294: int count = 0;
1295:
1296: for (int i = 1; i <= rowsToAdd; i++) {
1297: pstmtA.setInt(1, i);
1298: StringBuffer tmp = new StringBuffer(255);
1299:
1300: while (tmp.length() < 240) {
1301: tmp.append("row ").append(i).append(". ");
1302: }
1303:
1304: pstmtA.setString(2, tmp.toString());
1305: count += pstmtA.executeUpdate();
1306:
1307: pstmtB.setInt(1, i);
1308: pstmtB.executeUpdate();
1309: }
1310:
1311: assertEquals(count, rowsToAdd);
1312: pstmtA.close();
1313: pstmtB.close();
1314:
1315: Statement stmtA = con.createStatement(
1316: ResultSet.TYPE_SCROLL_INSENSITIVE,
1317: ResultSet.CONCUR_READ_ONLY);
1318: Statement stmtB = con.createStatement(
1319: ResultSet.TYPE_SCROLL_INSENSITIVE,
1320: ResultSet.CONCUR_READ_ONLY);
1321:
1322: count = 0;
1323: ResultSet rsA = stmtA.executeQuery("select * from #t0026a");
1324: assertNotNull(rsA);
1325:
1326: while (rsA.next()) {
1327: count++;
1328:
1329: ResultSet rsB = stmtB
1330: .executeQuery("select * from #t0026b where i="
1331: + rsA.getInt("i"));
1332:
1333: assertNotNull(rsB);
1334: assertTrue("Expected a result set", rsB.next());
1335: assertTrue("Expected no result set", !rsB.next());
1336: rsB.close();
1337: }
1338:
1339: assertEquals(count, rowsToAdd);
1340:
1341: stmtA.close();
1342: stmtB.close();
1343: }
1344:
1345: public void testErrors0036() throws Exception {
1346: Statement stmt = con.createStatement();
1347:
1348: final int numberToTest = 5;
1349:
1350: for (int i = 0; i < numberToTest; i++) {
1351: String table = "#t0036_no_create_" + i;
1352:
1353: try {
1354: stmt.executeUpdate("drop table " + table);
1355: fail("Did not expect to reach here");
1356: } catch (SQLException e) {
1357: assertEquals("42S02", e.getSQLState());
1358: }
1359: }
1360:
1361: stmt.close();
1362: }
1363:
1364: public void testTimestamps0037() throws Exception {
1365: Statement stmt = con.createStatement();
1366: ResultSet rs = stmt
1367: .executeQuery("select "
1368: + " convert(smalldatetime, '1999-01-02') a, "
1369: + " convert(smalldatetime, null) b, "
1370: + " convert(datetime, '1999-01-02') c, "
1371: + " convert(datetime, null) d ");
1372: assertNotNull(rs);
1373:
1374: assertTrue("Expected a result", rs.next());
1375:
1376: assertNotNull(rs.getDate("a"));
1377: assertNull(rs.getDate("b"));
1378: assertNotNull(rs.getDate("c"));
1379: assertNull(rs.getDate("d"));
1380:
1381: assertNotNull(rs.getTime("a"));
1382: assertNull(rs.getTime("b"));
1383: assertNotNull(rs.getTime("c"));
1384: assertNull(rs.getTime("d"));
1385:
1386: assertNotNull(rs.getTimestamp("a"));
1387: assertNull(rs.getTimestamp("b"));
1388: assertNotNull(rs.getTimestamp("c"));
1389: assertNull(rs.getTimestamp("d"));
1390:
1391: assertTrue("Expected no more results", !rs.next());
1392:
1393: stmt.close();
1394: }
1395:
1396: public void testConnection0038() throws Exception {
1397: Statement stmt = con.createStatement();
1398: stmt.executeUpdate("create table #t0038 ("
1399: + " keyField char(255) not null, "
1400: + " descField varchar(255) not null) ");
1401:
1402: int count = stmt
1403: .executeUpdate("insert into #t0038 values ('value', 'test')");
1404: assertEquals(count, 1);
1405:
1406: con
1407: .setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
1408: con.setAutoCommit(false);
1409: PreparedStatement ps = con
1410: .prepareStatement("update #t0038 set descField=descField where keyField=?");
1411: ps.setString(1, "value");
1412: ps.executeUpdate();
1413: ps.close();
1414: con.commit();
1415: // conn.rollback();
1416:
1417: ResultSet resultSet = stmt
1418: .executeQuery("select descField from #t0038 where keyField='value'");
1419: assertTrue(resultSet.next());
1420: stmt.close();
1421: }
1422:
1423: public void testConnection0039() throws Exception {
1424: for (int i = 0; i < 10; i++) {
1425: Connection conn = getConnection();
1426: Statement statement = conn.createStatement();
1427: ResultSet resultSet = statement.executeQuery("select 5");
1428: assertNotNull(resultSet);
1429:
1430: resultSet.close();
1431: statement.close();
1432: conn.close();
1433: }
1434: }
1435:
1436: public void testPreparedStatement0040() throws Exception {
1437: Statement stmt = con.createStatement();
1438: stmt.executeUpdate("create table #t0040 ("
1439: + " c255 char(255) not null, "
1440: + " v255 varchar(255) not null) ");
1441:
1442: PreparedStatement pstmt = con
1443: .prepareStatement("insert into #t0040 values (?, ?)");
1444:
1445: String along = getLongString('a');
1446: String blong = getLongString('b');
1447:
1448: pstmt.setString(1, along);
1449: pstmt.setString(2, along);
1450:
1451: int count = pstmt.executeUpdate();
1452:
1453: assertEquals(count, 1);
1454: pstmt.close();
1455:
1456: count = stmt.executeUpdate("" + "insert into #t0040 values ( "
1457: + "'" + blong + "', " + "'" + blong + "')");
1458: assertEquals(count, 1);
1459:
1460: pstmt = con
1461: .prepareStatement("select c255, v255 from #t0040 order by c255");
1462: ResultSet rs = pstmt.executeQuery();
1463: assertNotNull(rs);
1464:
1465: assertTrue("Expected a result set", rs.next());
1466: assertEquals(rs.getString("c255"), along);
1467: assertEquals(rs.getString("v255"), along);
1468:
1469: assertTrue("Expected a result set", rs.next());
1470: assertEquals(rs.getString("c255"), blong);
1471: assertEquals(rs.getString("v255"), blong);
1472:
1473: assertTrue("Expected no result set", !rs.next());
1474: pstmt.close();
1475:
1476: rs = stmt
1477: .executeQuery("select c255, v255 from #t0040 order by c255");
1478: assertNotNull(rs);
1479:
1480: assertTrue("Expected a result set", rs.next());
1481: assertEquals(rs.getString("c255"), along);
1482: assertEquals(rs.getString("v255"), along);
1483:
1484: assertTrue("Expected a result set", rs.next());
1485: assertEquals(rs.getString("c255"), blong);
1486: assertEquals(rs.getString("v255"), blong);
1487:
1488: assertTrue("Expected no result set", !rs.next());
1489: stmt.close();
1490: }
1491:
1492: public void testPreparedStatement0041() throws Exception {
1493: Statement stmt = con.createStatement();
1494: stmt.executeUpdate("create table #t0041 "
1495: + " (i integer not null, "
1496: + " s text not null) ");
1497:
1498: PreparedStatement pstmt = con
1499: .prepareStatement("insert into #t0041 values (?, ?)");
1500:
1501: // TODO: Check values
1502: final int rowsToAdd = 400;
1503: final String theString = getLongString(400);
1504: int count = 0;
1505:
1506: for (int i = 1; i <= rowsToAdd; i++) {
1507: pstmt.setInt(1, i);
1508: pstmt.setString(2, theString.substring(0, i));
1509:
1510: count += pstmt.executeUpdate();
1511: }
1512:
1513: assertEquals(rowsToAdd, count);
1514: pstmt.close();
1515:
1516: ResultSet rs = stmt.executeQuery("select s, i from #t0041");
1517: assertNotNull(rs);
1518:
1519: count = 0;
1520:
1521: while (rs.next()) {
1522: rs.getString("s");
1523: count++;
1524: }
1525:
1526: assertEquals(rowsToAdd, count);
1527: stmt.close();
1528: }
1529:
1530: public void testPreparedStatement0042() throws Exception {
1531: Statement stmt = con.createStatement();
1532: stmt
1533: .executeUpdate("create table #t0042 (s char(5) null, i integer null, j integer not null)");
1534: stmt.close();
1535:
1536: PreparedStatement pstmt = con
1537: .prepareStatement("insert into #t0042 (s, i, j) values (?, ?, ?)");
1538:
1539: pstmt.setString(1, "hello");
1540: pstmt.setNull(2, java.sql.Types.INTEGER);
1541: pstmt.setInt(3, 1);
1542:
1543: int count = pstmt.executeUpdate();
1544: assertEquals(count, 1);
1545:
1546: pstmt.setInt(2, 42);
1547: pstmt.setInt(3, 2);
1548: count = pstmt.executeUpdate();
1549: assertEquals(count, 1);
1550: pstmt.close();
1551:
1552: pstmt = con.prepareStatement("select i from #t0042 order by j");
1553: ResultSet rs = pstmt.executeQuery();
1554: assertNotNull(rs);
1555:
1556: assertTrue("Expected a result set", rs.next());
1557: rs.getInt(1);
1558: assertTrue(rs.wasNull());
1559:
1560: assertTrue("Expected a result set", rs.next());
1561: assertEquals(rs.getInt(1), 42);
1562: assertTrue(!rs.wasNull());
1563:
1564: assertTrue("Expected no result set", !rs.next());
1565: pstmt.close();
1566: }
1567:
1568: public void testResultSet0043() throws Exception {
1569: Statement stmt = con.createStatement();
1570:
1571: try {
1572: ResultSet rs = stmt.executeQuery("select 1");
1573: assertNotNull(rs);
1574: rs.getInt(1);
1575:
1576: fail("Did not expect to reach here");
1577: } catch (SQLException e) {
1578: assertEquals("24000", e.getSQLState());
1579: }
1580:
1581: stmt.close();
1582: }
1583:
1584: public void testResultSet0044() throws Exception {
1585: Statement stmt = con.createStatement();
1586:
1587: ResultSet rs = stmt.executeQuery("select 1");
1588: assertNotNull(rs);
1589: rs.close();
1590:
1591: try {
1592: rs.next();
1593: fail("Was expecting ResultSet.next() to throw an exception if the ResultSet was closed");
1594: } catch (SQLException e) {
1595: assertEquals("HY010", e.getSQLState());
1596: }
1597:
1598: stmt.close();
1599: }
1600:
1601: public void testResultSet0045() throws Exception {
1602: Statement stmt = con.createStatement();
1603:
1604: ResultSet rs = stmt.executeQuery("select 1");
1605: assertNotNull(rs);
1606:
1607: assertTrue("Expected a result set", rs.next());
1608: rs.getInt(1);
1609:
1610: assertTrue("Expected no result set", !rs.next());
1611:
1612: try {
1613: rs.getInt(1);
1614: fail("Did not expect to reach here");
1615: } catch (java.sql.SQLException e) {
1616: assertEquals("24000", e.getSQLState());
1617: }
1618:
1619: stmt.close();
1620: }
1621:
1622: public void testMetaData0046() throws Exception {
1623: Statement stmt = con.createStatement();
1624: stmt.executeUpdate("create table #t0046 ("
1625: + " i integer identity, " + " a integer not null, "
1626: + " b integer null ) ");
1627:
1628: int count = stmt
1629: .executeUpdate("insert into #t0046 (a, b) values (-2, -3)");
1630: assertEquals(count, 1);
1631:
1632: ResultSet rs = stmt
1633: .executeQuery("select i, a, b, 17 c from #t0046");
1634: assertNotNull(rs);
1635:
1636: ResultSetMetaData md = rs.getMetaData();
1637: assertNotNull(md);
1638:
1639: assertTrue(md.isAutoIncrement(1));
1640: assertTrue(!md.isAutoIncrement(2));
1641: assertTrue(!md.isAutoIncrement(3));
1642: assertTrue(!md.isAutoIncrement(4));
1643:
1644: assertTrue(md.isReadOnly(1));
1645: assertTrue(!md.isReadOnly(2));
1646: assertTrue(!md.isReadOnly(3));
1647: // assertTrue(md.isReadOnly(4)); SQL 6.5 does not report this one correctly!
1648:
1649: assertEquals(md.isNullable(1),
1650: java.sql.ResultSetMetaData.columnNoNulls);
1651: assertEquals(md.isNullable(2),
1652: java.sql.ResultSetMetaData.columnNoNulls);
1653: assertEquals(md.isNullable(3),
1654: java.sql.ResultSetMetaData.columnNullable);
1655: // assert(md.isNullable(4) == java.sql.ResultSetMetaData.columnNoNulls);
1656:
1657: rs.close();
1658: stmt.close();
1659: }
1660:
1661: public void testTimestamps0047() throws Exception {
1662: Statement stmt = con.createStatement();
1663: stmt.executeUpdate("create table #t0047 "
1664: + "( "
1665: + " t1 datetime not null, "
1666: + " t2 datetime null, "
1667: + " t3 smalldatetime not null, "
1668: + " t4 smalldatetime null " + ")");
1669:
1670: String query = "insert into #t0047 (t1, t2, t3, t4) "
1671: + " values('2000-01-02 19:35:01.333', "
1672: + " '2000-01-02 19:35:01.333', "
1673: + " '2000-01-02 19:35:01.333', "
1674: + " '2000-01-02 19:35:01.333' " + ")";
1675: int count = stmt.executeUpdate(query);
1676:
1677: assertEquals(count, 1);
1678:
1679: ResultSet rs = stmt
1680: .executeQuery("select t1, t2, t3, t4 from #t0047");
1681: assertNotNull(rs);
1682:
1683: assertTrue("Expected a result set", rs.next());
1684:
1685: java.sql.Timestamp t1 = rs.getTimestamp("t1");
1686: java.sql.Timestamp t2 = rs.getTimestamp("t2");
1687: java.sql.Timestamp t3 = rs.getTimestamp("t3");
1688: java.sql.Timestamp t4 = rs.getTimestamp("t4");
1689:
1690: java.sql.Timestamp r1 = Timestamp
1691: .valueOf("2000-01-02 19:35:01.333");
1692: java.sql.Timestamp r2 = Timestamp
1693: .valueOf("2000-01-02 19:35:00");
1694:
1695: assertEquals(r1, t1);
1696: assertEquals(r1, t2);
1697: assertEquals(r2, t3);
1698: assertEquals(r2, t4);
1699:
1700: stmt.close();
1701: }
1702:
1703: public void testTimestamps0048() throws Exception {
1704: Statement stmt = con.createStatement();
1705: stmt.executeUpdate("create table #t0048 "
1706: + "( "
1707: + " t1 datetime not null, "
1708: + " t2 datetime null, "
1709: + " t3 smalldatetime not null, "
1710: + " t4 smalldatetime null " + ")");
1711:
1712: java.sql.Timestamp r1;
1713: java.sql.Timestamp r2;
1714: r1 = Timestamp.valueOf("2000-01-02 19:35:01");
1715: r2 = Timestamp.valueOf("2000-01-02 19:35:00");
1716:
1717: java.sql.PreparedStatement pstmt = con
1718: .prepareStatement("insert into #t0048 (t1, t2, t3, t4) values(?, ?, ?, ?)");
1719:
1720: pstmt.setTimestamp(1, r1);
1721: pstmt.setTimestamp(2, r1);
1722: pstmt.setTimestamp(3, r1);
1723: pstmt.setTimestamp(4, r1);
1724:
1725: int count = pstmt.executeUpdate();
1726: assertEquals(count, 1);
1727: pstmt.close();
1728:
1729: ResultSet rs = stmt
1730: .executeQuery("select t1, t2, t3, t4 from #t0048");
1731: assertNotNull(rs);
1732:
1733: assertTrue("Expected a result set", rs.next());
1734: java.sql.Timestamp t1 = rs.getTimestamp("t1");
1735: java.sql.Timestamp t2 = rs.getTimestamp("t2");
1736: java.sql.Timestamp t3 = rs.getTimestamp("t3");
1737: java.sql.Timestamp t4 = rs.getTimestamp("t4");
1738:
1739: assertEquals(r1, t1);
1740: assertEquals(r1, t2);
1741: assertEquals(r2, t3);
1742: assertEquals(r2, t4);
1743:
1744: stmt.close();
1745: }
1746:
1747: public void testDecimalConversion0058() throws Exception {
1748: Statement stmt = con.createStatement();
1749:
1750: ResultSet rs = stmt
1751: .executeQuery("select convert(DECIMAL(4,0), 0)");
1752: assertNotNull(rs);
1753: assertTrue("Expected a result set", rs.next());
1754: assertEquals(rs.getInt(1), 0);
1755: assertTrue("Expected no result set", !rs.next());
1756:
1757: rs = stmt.executeQuery("select convert(DECIMAL(4,0), 1)");
1758: assertNotNull(rs);
1759: assertTrue("Expected a result set", rs.next());
1760: assertEquals(rs.getInt(1), 1);
1761: assertTrue("Expected no result set", !rs.next());
1762:
1763: rs = stmt.executeQuery("select convert(DECIMAL(4,0), -1)");
1764: assertNotNull(rs);
1765: assertTrue("Expected a result set", rs.next());
1766: assertEquals(rs.getInt(1), -1);
1767: assertTrue("Expected no result set", !rs.next());
1768:
1769: stmt.close();
1770: }
1771:
1772: /**
1773: * Test for bug [994916] datetime decoding in TdsData.java
1774: */
1775: public void testDatetimeRounding1() throws Exception {
1776: // Per the SQL Server documentation
1777: // Send: 01/01/98 23:59:59.990
1778: // Receive: 01/01/98 23:59:59.990
1779: Calendar sendValue = Calendar.getInstance();
1780: Calendar receiveValue = Calendar.getInstance();
1781:
1782: sendValue.set(Calendar.MONTH, Calendar.JANUARY);
1783: sendValue.set(Calendar.DAY_OF_MONTH, 1);
1784: sendValue.set(Calendar.YEAR, 1998);
1785: sendValue.set(Calendar.HOUR_OF_DAY, 23);
1786: sendValue.set(Calendar.MINUTE, 59);
1787: sendValue.set(Calendar.SECOND, 59);
1788: sendValue.set(Calendar.MILLISECOND, 990);
1789:
1790: receiveValue.set(Calendar.MONTH, Calendar.JANUARY);
1791: receiveValue.set(Calendar.DAY_OF_MONTH, 1);
1792: receiveValue.set(Calendar.YEAR, 1998);
1793: receiveValue.set(Calendar.HOUR_OF_DAY, 23);
1794: receiveValue.set(Calendar.MINUTE, 59);
1795: receiveValue.set(Calendar.SECOND, 59);
1796: receiveValue.set(Calendar.MILLISECOND, 990);
1797:
1798: Statement stmt = con.createStatement();
1799: stmt.execute("create table #dtr1 (data datetime)");
1800: stmt.close();
1801:
1802: PreparedStatement pstmt = con
1803: .prepareStatement("insert into #dtr1 (data) values (?)");
1804: pstmt.setTimestamp(1, new Timestamp(sendValue.getTime()
1805: .getTime()));
1806: assertEquals(pstmt.executeUpdate(), 1);
1807: pstmt.close();
1808:
1809: pstmt = con.prepareStatement("select data from #dtr1");
1810: ResultSet rs = pstmt.executeQuery();
1811:
1812: assertTrue(rs.next());
1813: assertEquals(receiveValue.getTime().getTime(), getTimeInMs(rs));
1814: assertTrue(!rs.next());
1815:
1816: pstmt.close();
1817: rs.close();
1818: }
1819:
1820: /**
1821: * Test for bug [994916] datetime decoding in TdsData.java
1822: */
1823: public void testDatetimeRounding2() throws Exception {
1824: // Per the SQL Server documentation
1825: // Send: 01/01/98 23:59:59.991
1826: // Receive: 01/01/98 23:59:59.990
1827: Calendar sendValue = Calendar.getInstance();
1828: Calendar receiveValue = Calendar.getInstance();
1829:
1830: sendValue.set(Calendar.MONTH, Calendar.JANUARY);
1831: sendValue.set(Calendar.DAY_OF_MONTH, 1);
1832: sendValue.set(Calendar.YEAR, 1998);
1833: sendValue.set(Calendar.HOUR_OF_DAY, 23);
1834: sendValue.set(Calendar.MINUTE, 59);
1835: sendValue.set(Calendar.SECOND, 59);
1836: sendValue.set(Calendar.MILLISECOND, 991);
1837:
1838: receiveValue.set(Calendar.MONTH, Calendar.JANUARY);
1839: receiveValue.set(Calendar.DAY_OF_MONTH, 1);
1840: receiveValue.set(Calendar.YEAR, 1998);
1841: receiveValue.set(Calendar.HOUR_OF_DAY, 23);
1842: receiveValue.set(Calendar.MINUTE, 59);
1843: receiveValue.set(Calendar.SECOND, 59);
1844: receiveValue.set(Calendar.MILLISECOND, 990);
1845:
1846: Statement stmt = con.createStatement();
1847: stmt.execute("create table #dtr2 (data datetime)");
1848: stmt.close();
1849:
1850: PreparedStatement pstmt = con
1851: .prepareStatement("insert into #dtr2 (data) values (?)");
1852: pstmt.setTimestamp(1, new Timestamp(sendValue.getTime()
1853: .getTime()));
1854: assertEquals(pstmt.executeUpdate(), 1);
1855: pstmt.close();
1856:
1857: pstmt = con.prepareStatement("select data from #dtr2");
1858: ResultSet rs = pstmt.executeQuery();
1859:
1860: assertTrue(rs.next());
1861: assertEquals(receiveValue.getTime().getTime(), getTimeInMs(rs));
1862: assertTrue(!rs.next());
1863:
1864: pstmt.close();
1865: rs.close();
1866: }
1867:
1868: /**
1869: * Test for bug [994916] datetime decoding in TdsData.java
1870: */
1871: public void testDatetimeRounding3() throws Exception {
1872: // Per the SQL Server documentation
1873: // Send: 01/01/98 23:59:59.992
1874: // Receive: 01/01/98 23:59:59.993
1875: Calendar sendValue = Calendar.getInstance();
1876: Calendar receiveValue = Calendar.getInstance();
1877:
1878: sendValue.set(Calendar.MONTH, Calendar.JANUARY);
1879: sendValue.set(Calendar.DAY_OF_MONTH, 1);
1880: sendValue.set(Calendar.YEAR, 1998);
1881: sendValue.set(Calendar.HOUR_OF_DAY, 23);
1882: sendValue.set(Calendar.MINUTE, 59);
1883: sendValue.set(Calendar.SECOND, 59);
1884: sendValue.set(Calendar.MILLISECOND, 992);
1885:
1886: receiveValue.set(Calendar.MONTH, Calendar.JANUARY);
1887: receiveValue.set(Calendar.DAY_OF_MONTH, 1);
1888: receiveValue.set(Calendar.YEAR, 1998);
1889: receiveValue.set(Calendar.HOUR_OF_DAY, 23);
1890: receiveValue.set(Calendar.MINUTE, 59);
1891: receiveValue.set(Calendar.SECOND, 59);
1892: receiveValue.set(Calendar.MILLISECOND, 993);
1893:
1894: Statement stmt = con.createStatement();
1895: stmt.execute("create table #dtr3 (data datetime)");
1896: stmt.close();
1897:
1898: PreparedStatement pstmt = con
1899: .prepareStatement("insert into #dtr3 (data) values (?)");
1900: pstmt.setTimestamp(1, new Timestamp(sendValue.getTime()
1901: .getTime()));
1902: assertEquals(pstmt.executeUpdate(), 1);
1903: pstmt.close();
1904:
1905: pstmt = con.prepareStatement("select data from #dtr3");
1906: ResultSet rs = pstmt.executeQuery();
1907:
1908: assertTrue(rs.next());
1909: assertEquals(receiveValue.getTime().getTime(), getTimeInMs(rs));
1910: assertTrue(!rs.next());
1911:
1912: pstmt.close();
1913: rs.close();
1914: }
1915:
1916: /**
1917: * Test for bug [994916] datetime decoding in TdsData.java
1918: */
1919: public void testDatetimeRounding4() throws Exception {
1920: // Per the SQL Server documentation
1921: // Send: 01/01/98 23:59:59.993
1922: // Receive: 01/01/98 23:59:59.993
1923: Calendar sendValue = Calendar.getInstance();
1924: Calendar receiveValue = Calendar.getInstance();
1925:
1926: sendValue.set(Calendar.MONTH, Calendar.JANUARY);
1927: sendValue.set(Calendar.DAY_OF_MONTH, 1);
1928: sendValue.set(Calendar.YEAR, 1998);
1929: sendValue.set(Calendar.HOUR_OF_DAY, 23);
1930: sendValue.set(Calendar.MINUTE, 59);
1931: sendValue.set(Calendar.SECOND, 59);
1932: sendValue.set(Calendar.MILLISECOND, 993);
1933:
1934: receiveValue.set(Calendar.MONTH, Calendar.JANUARY);
1935: receiveValue.set(Calendar.DAY_OF_MONTH, 1);
1936: receiveValue.set(Calendar.YEAR, 1998);
1937: receiveValue.set(Calendar.HOUR_OF_DAY, 23);
1938: receiveValue.set(Calendar.MINUTE, 59);
1939: receiveValue.set(Calendar.SECOND, 59);
1940: receiveValue.set(Calendar.MILLISECOND, 993);
1941:
1942: Statement stmt = con.createStatement();
1943: stmt.execute("create table #dtr4 (data datetime)");
1944: stmt.close();
1945:
1946: PreparedStatement pstmt = con
1947: .prepareStatement("insert into #dtr4 (data) values (?)");
1948: pstmt.setTimestamp(1, new Timestamp(sendValue.getTime()
1949: .getTime()));
1950: assertEquals(pstmt.executeUpdate(), 1);
1951: pstmt.close();
1952:
1953: pstmt = con.prepareStatement("select data from #dtr4");
1954: ResultSet rs = pstmt.executeQuery();
1955:
1956: assertTrue(rs.next());
1957: assertEquals(receiveValue.getTime().getTime(), getTimeInMs(rs));
1958: assertTrue(!rs.next());
1959:
1960: pstmt.close();
1961: rs.close();
1962: }
1963:
1964: /**
1965: * Test for bug [994916] datetime decoding in TdsData.java
1966: */
1967: public void testDatetimeRounding5() throws Exception {
1968: // Per the SQL Server documentation
1969: // Send: 01/01/98 23:59:59.994
1970: // Receive: 01/01/98 23:59:59.993
1971: Calendar sendValue = Calendar.getInstance();
1972: Calendar receiveValue = Calendar.getInstance();
1973:
1974: sendValue.set(Calendar.MONTH, Calendar.JANUARY);
1975: sendValue.set(Calendar.DAY_OF_MONTH, 1);
1976: sendValue.set(Calendar.YEAR, 1998);
1977: sendValue.set(Calendar.HOUR_OF_DAY, 23);
1978: sendValue.set(Calendar.MINUTE, 59);
1979: sendValue.set(Calendar.SECOND, 59);
1980: sendValue.set(Calendar.MILLISECOND, 994);
1981:
1982: receiveValue.set(Calendar.MONTH, Calendar.JANUARY);
1983: receiveValue.set(Calendar.DAY_OF_MONTH, 1);
1984: receiveValue.set(Calendar.YEAR, 1998);
1985: receiveValue.set(Calendar.HOUR_OF_DAY, 23);
1986: receiveValue.set(Calendar.MINUTE, 59);
1987: receiveValue.set(Calendar.SECOND, 59);
1988: receiveValue.set(Calendar.MILLISECOND, 993);
1989:
1990: Statement stmt = con.createStatement();
1991: stmt.execute("create table #dtr5 (data datetime)");
1992: stmt.close();
1993:
1994: PreparedStatement pstmt = con
1995: .prepareStatement("insert into #dtr5 (data) values (?)");
1996: pstmt.setTimestamp(1, new Timestamp(sendValue.getTime()
1997: .getTime()));
1998: assertEquals(pstmt.executeUpdate(), 1);
1999: pstmt.close();
2000:
2001: pstmt = con.prepareStatement("select data from #dtr5");
2002: ResultSet rs = pstmt.executeQuery();
2003:
2004: assertTrue(rs.next());
2005: assertEquals(receiveValue.getTime().getTime(), getTimeInMs(rs));
2006: assertTrue(!rs.next());
2007:
2008: pstmt.close();
2009: rs.close();
2010: }
2011:
2012: /**
2013: * Test for bug [994916] datetime decoding in TdsData.java
2014: */
2015: public void testDatetimeRounding6() throws Exception {
2016: // Per the SQL Server documentation
2017: // Send: 01/01/98 23:59:59.995
2018: // Receive: 01/01/98 23:59:59.997
2019: Calendar sendValue = Calendar.getInstance();
2020: Calendar receiveValue = Calendar.getInstance();
2021:
2022: sendValue.set(Calendar.MONTH, Calendar.JANUARY);
2023: sendValue.set(Calendar.DAY_OF_MONTH, 1);
2024: sendValue.set(Calendar.YEAR, 1998);
2025: sendValue.set(Calendar.HOUR_OF_DAY, 23);
2026: sendValue.set(Calendar.MINUTE, 59);
2027: sendValue.set(Calendar.SECOND, 59);
2028: sendValue.set(Calendar.MILLISECOND, 995);
2029:
2030: receiveValue.set(Calendar.MONTH, Calendar.JANUARY);
2031: receiveValue.set(Calendar.DAY_OF_MONTH, 1);
2032: receiveValue.set(Calendar.YEAR, 1998);
2033: receiveValue.set(Calendar.HOUR_OF_DAY, 23);
2034: receiveValue.set(Calendar.MINUTE, 59);
2035: receiveValue.set(Calendar.SECOND, 59);
2036: receiveValue.set(Calendar.MILLISECOND, 997);
2037:
2038: Statement stmt = con.createStatement();
2039: stmt.execute("create table #dtr6 (data datetime)");
2040: stmt.close();
2041:
2042: PreparedStatement pstmt = con
2043: .prepareStatement("insert into #dtr6 (data) values (?)");
2044: pstmt.setTimestamp(1, new Timestamp(sendValue.getTime()
2045: .getTime()));
2046: assertEquals(pstmt.executeUpdate(), 1);
2047: pstmt.close();
2048:
2049: pstmt = con.prepareStatement("select data from #dtr6");
2050: ResultSet rs = pstmt.executeQuery();
2051:
2052: assertTrue(rs.next());
2053: assertEquals(receiveValue.getTime().getTime(), getTimeInMs(rs));
2054: assertTrue(!rs.next());
2055:
2056: pstmt.close();
2057: rs.close();
2058: }
2059:
2060: /**
2061: * Test for bug [994916] datetime decoding in TdsData.java
2062: */
2063: public void testDatetimeRounding7() throws Exception {
2064: // Per the SQL Server documentation
2065: // Send: 01/01/98 23:59:59.996
2066: // Receive: 01/01/98 23:59:59.997
2067: Calendar sendValue = Calendar.getInstance();
2068: Calendar receiveValue = Calendar.getInstance();
2069:
2070: sendValue.set(Calendar.MONTH, Calendar.JANUARY);
2071: sendValue.set(Calendar.DAY_OF_MONTH, 1);
2072: sendValue.set(Calendar.YEAR, 1998);
2073: sendValue.set(Calendar.HOUR_OF_DAY, 23);
2074: sendValue.set(Calendar.MINUTE, 59);
2075: sendValue.set(Calendar.SECOND, 59);
2076: sendValue.set(Calendar.MILLISECOND, 996);
2077:
2078: receiveValue.set(Calendar.MONTH, Calendar.JANUARY);
2079: receiveValue.set(Calendar.DAY_OF_MONTH, 1);
2080: receiveValue.set(Calendar.YEAR, 1998);
2081: receiveValue.set(Calendar.HOUR_OF_DAY, 23);
2082: receiveValue.set(Calendar.MINUTE, 59);
2083: receiveValue.set(Calendar.SECOND, 59);
2084: receiveValue.set(Calendar.MILLISECOND, 997);
2085:
2086: Statement stmt = con.createStatement();
2087: stmt.execute("create table #dtr7 (data datetime)");
2088: stmt.close();
2089:
2090: PreparedStatement pstmt = con
2091: .prepareStatement("insert into #dtr7 (data) values (?)");
2092: pstmt.setTimestamp(1, new Timestamp(sendValue.getTime()
2093: .getTime()));
2094: assertEquals(pstmt.executeUpdate(), 1);
2095: pstmt.close();
2096:
2097: pstmt = con.prepareStatement("select data from #dtr7");
2098: ResultSet rs = pstmt.executeQuery();
2099:
2100: assertTrue(rs.next());
2101: assertEquals(receiveValue.getTime().getTime(), getTimeInMs(rs));
2102: assertTrue(!rs.next());
2103:
2104: pstmt.close();
2105: rs.close();
2106: }
2107:
2108: /**
2109: * Test for bug [994916] datetime decoding in TdsData.java
2110: */
2111: public void testDatetimeRounding8() throws Exception {
2112: // Per the SQL Server documentation
2113: // Send: 01/01/98 23:59:59.997
2114: // Receive: 01/01/98 23:59:59.997
2115: Calendar sendValue = Calendar.getInstance();
2116: Calendar receiveValue = Calendar.getInstance();
2117:
2118: sendValue.set(Calendar.MONTH, Calendar.JANUARY);
2119: sendValue.set(Calendar.DAY_OF_MONTH, 1);
2120: sendValue.set(Calendar.YEAR, 1998);
2121: sendValue.set(Calendar.HOUR_OF_DAY, 23);
2122: sendValue.set(Calendar.MINUTE, 59);
2123: sendValue.set(Calendar.SECOND, 59);
2124: sendValue.set(Calendar.MILLISECOND, 997);
2125:
2126: receiveValue.set(Calendar.MONTH, Calendar.JANUARY);
2127: receiveValue.set(Calendar.DAY_OF_MONTH, 1);
2128: receiveValue.set(Calendar.YEAR, 1998);
2129: receiveValue.set(Calendar.HOUR_OF_DAY, 23);
2130: receiveValue.set(Calendar.MINUTE, 59);
2131: receiveValue.set(Calendar.SECOND, 59);
2132: receiveValue.set(Calendar.MILLISECOND, 997);
2133:
2134: Statement stmt = con.createStatement();
2135: stmt.execute("create table #dtr8 (data datetime)");
2136: stmt.close();
2137:
2138: PreparedStatement pstmt = con
2139: .prepareStatement("insert into #dtr8 (data) values (?)");
2140: pstmt.setTimestamp(1, new Timestamp(sendValue.getTime()
2141: .getTime()));
2142: assertEquals(pstmt.executeUpdate(), 1);
2143: pstmt.close();
2144:
2145: pstmt = con.prepareStatement("select data from #dtr8");
2146: ResultSet rs = pstmt.executeQuery();
2147:
2148: assertTrue(rs.next());
2149: assertEquals(receiveValue.getTime().getTime(), getTimeInMs(rs));
2150: assertTrue(!rs.next());
2151:
2152: pstmt.close();
2153: rs.close();
2154: }
2155:
2156: /**
2157: * Test for bug [994916] datetime decoding in TdsData.java
2158: */
2159: public void testDatetimeRounding9() throws Exception {
2160: // Per the SQL Server documentation
2161: // Send: 01/01/98 23:59:59.998
2162: // Receive: 01/01/98 23:59:59.997
2163: Calendar sendValue = Calendar.getInstance();
2164: Calendar receiveValue = Calendar.getInstance();
2165:
2166: sendValue.set(Calendar.MONTH, Calendar.JANUARY);
2167: sendValue.set(Calendar.DAY_OF_MONTH, 1);
2168: sendValue.set(Calendar.YEAR, 1998);
2169: sendValue.set(Calendar.HOUR_OF_DAY, 23);
2170: sendValue.set(Calendar.MINUTE, 59);
2171: sendValue.set(Calendar.SECOND, 59);
2172: sendValue.set(Calendar.MILLISECOND, 998);
2173:
2174: receiveValue.set(Calendar.MONTH, Calendar.JANUARY);
2175: receiveValue.set(Calendar.DAY_OF_MONTH, 1);
2176: receiveValue.set(Calendar.YEAR, 1998);
2177: receiveValue.set(Calendar.HOUR_OF_DAY, 23);
2178: receiveValue.set(Calendar.MINUTE, 59);
2179: receiveValue.set(Calendar.SECOND, 59);
2180: receiveValue.set(Calendar.MILLISECOND, 997);
2181:
2182: Statement stmt = con.createStatement();
2183: stmt.execute("create table #dtr9 (data datetime)");
2184: stmt.close();
2185:
2186: PreparedStatement pstmt = con
2187: .prepareStatement("insert into #dtr9 (data) values (?)");
2188: pstmt.setTimestamp(1, new Timestamp(sendValue.getTime()
2189: .getTime()));
2190: assertEquals(pstmt.executeUpdate(), 1);
2191: pstmt.close();
2192:
2193: pstmt = con.prepareStatement("select data from #dtr9");
2194: ResultSet rs = pstmt.executeQuery();
2195:
2196: assertTrue(rs.next());
2197: assertEquals(receiveValue.getTime().getTime(), getTimeInMs(rs));
2198: assertTrue(!rs.next());
2199:
2200: pstmt.close();
2201: rs.close();
2202: }
2203:
2204: /**
2205: * Test for bug [994916] datetime decoding in TdsData.java
2206: */
2207: public void testDatetimeRounding10() throws Exception {
2208: // Per the SQL Server documentation
2209: // Send: 01/01/98 23:59:59.999
2210: // Receive: 01/02/98 00:00:00.000
2211: Calendar sendValue = Calendar.getInstance();
2212: Calendar receiveValue = Calendar.getInstance();
2213:
2214: sendValue.set(Calendar.MONTH, Calendar.JANUARY);
2215: sendValue.set(Calendar.DAY_OF_MONTH, 1);
2216: sendValue.set(Calendar.YEAR, 1998);
2217: sendValue.set(Calendar.HOUR_OF_DAY, 23);
2218: sendValue.set(Calendar.MINUTE, 59);
2219: sendValue.set(Calendar.SECOND, 59);
2220: sendValue.set(Calendar.MILLISECOND, 999);
2221:
2222: receiveValue.set(Calendar.MONTH, Calendar.JANUARY);
2223: receiveValue.set(Calendar.DAY_OF_MONTH, 2);
2224: receiveValue.set(Calendar.YEAR, 1998);
2225: receiveValue.set(Calendar.HOUR_OF_DAY, 0);
2226: receiveValue.set(Calendar.MINUTE, 0);
2227: receiveValue.set(Calendar.SECOND, 0);
2228: receiveValue.set(Calendar.MILLISECOND, 0);
2229:
2230: Statement stmt = con.createStatement();
2231: stmt.execute("create table #dtr10 (data datetime)");
2232: stmt.close();
2233:
2234: PreparedStatement pstmt = con
2235: .prepareStatement("insert into #dtr10 (data) values (?)");
2236: pstmt.setTimestamp(1, new Timestamp(sendValue.getTime()
2237: .getTime()));
2238: assertEquals(pstmt.executeUpdate(), 1);
2239: pstmt.close();
2240:
2241: pstmt = con.prepareStatement("select data from #dtr10");
2242: ResultSet rs = pstmt.executeQuery();
2243:
2244: assertTrue(rs.next());
2245: assertEquals(receiveValue.getTime().getTime(), getTimeInMs(rs));
2246: assertTrue(!rs.next());
2247:
2248: pstmt.close();
2249: rs.close();
2250: }
2251:
2252: /**
2253: * Test for bug [1036059] getTimestamp with Calendar applies tzone offset
2254: * wrong way.
2255: */
2256: public void testTimestampTimeZone() throws SQLException {
2257: Statement stmt = con.createStatement();
2258: stmt.executeUpdate("CREATE TABLE #testTimestampTimeZone ("
2259: + "ref INT NOT NULL, " + "tstamp DATETIME NOT NULL)");
2260: stmt.close();
2261:
2262: Calendar calNY = Calendar.getInstance(TimeZone
2263: .getTimeZone("America/New_York"));
2264:
2265: Timestamp tsStart = new Timestamp(System.currentTimeMillis());
2266:
2267: PreparedStatement pstmt = con
2268: .prepareStatement("INSERT INTO #testTimestampTimeZone (ref, tstamp) VALUES (?, ?)");
2269: pstmt.setInt(1, 0);
2270: pstmt.setTimestamp(2, tsStart, calNY);
2271: assertEquals(1, pstmt.executeUpdate());
2272: pstmt.close();
2273:
2274: pstmt = con
2275: .prepareStatement("SELECT * FROM #testTimestampTimeZone WHERE ref = ?");
2276: pstmt.setInt(1, 0);
2277: ResultSet rs = pstmt.executeQuery();
2278: assertTrue(rs.next());
2279: Timestamp ts = rs.getTimestamp("tstamp", calNY);
2280:
2281: // The difference should be less than 3 milliseconds (i.e. 1 or 2)
2282: assertTrue(Math.abs(tsStart.getTime() - ts.getTime()) < 3);
2283: rs.close();
2284: pstmt.close();
2285: }
2286:
2287: /**
2288: * Test for bug [1040475] Possible bug when converting to and from
2289: * datetime.
2290: * <p>
2291: * jTDS seems to accept dates outside the range accepted by SQL
2292: * Server (i.e. 1753-9999).
2293: */
2294: public void testTimestampRange() throws SQLException {
2295: Statement stmt = con.createStatement();
2296: stmt
2297: .executeUpdate("CREATE TABLE #testTimestampRange (id INT, d DATETIME)");
2298:
2299: PreparedStatement pstmt = con
2300: .prepareStatement("INSERT INTO #testTimestampRange VALUES (?, ?)");
2301: pstmt.setInt(1, 1);
2302: try {
2303: pstmt.setDate(2, Date.valueOf("0012-03-03")); // This should fail
2304: pstmt.executeUpdate();
2305: fail("Expecting an exception to be thrown. Date out of range.");
2306: } catch (SQLException ex) {
2307: assertEquals("22003", ex.getSQLState());
2308: }
2309: pstmt.close();
2310:
2311: ResultSet rs = stmt
2312: .executeQuery("SELECT * FROM #testTimestampRange");
2313: assertFalse(
2314: "Row was inserted even though date was out of range.",
2315: rs.next());
2316: rs.close();
2317: stmt.close();
2318: }
2319:
2320: /**
2321: * Test that <code>java.sql.Date</code> objects are inserted and retrieved
2322: * correctly (ie no time component).
2323: */
2324: public void testWriteDate() throws SQLException {
2325: Statement stmt = con.createStatement();
2326: stmt.executeUpdate("CREATE TABLE #testWriteDate (d DATETIME)");
2327: stmt.close();
2328:
2329: long time = System.currentTimeMillis();
2330:
2331: PreparedStatement pstmt = con
2332: .prepareStatement("INSERT INTO #testWriteDate VALUES (?)");
2333: pstmt.setDate(1, new Date(time));
2334: pstmt.executeUpdate();
2335: pstmt.close();
2336:
2337: pstmt = con
2338: .prepareStatement("SELECT * FROM #testWriteDate WHERE d=?");
2339: pstmt.setDate(1, new Date(time + 10));
2340: ResultSet rs = pstmt.executeQuery();
2341: assertTrue(rs.next());
2342: assertTrue(time - rs.getDate(1).getTime() < 24 * 60 * 60 * 1000);
2343: Calendar c1 = new GregorianCalendar(), c2 = new GregorianCalendar();
2344: c1.setTime(rs.getTimestamp(1));
2345: c2.setTime(new Timestamp(time));
2346: assertEquals(c2.get(Calendar.YEAR), c1.get(Calendar.YEAR));
2347: assertEquals(c2.get(Calendar.MONTH), c1.get(Calendar.MONTH));
2348: assertEquals(c2.get(Calendar.DAY_OF_MONTH), c1
2349: .get(Calendar.DAY_OF_MONTH));
2350: assertEquals(0, c1.get(Calendar.HOUR));
2351: assertEquals(0, c1.get(Calendar.MINUTE));
2352: assertEquals(0, c1.get(Calendar.SECOND));
2353: assertEquals(0, c1.get(Calendar.MILLISECOND));
2354: rs.close();
2355: pstmt.close();
2356:
2357: stmt = con.createStatement();
2358: rs = stmt
2359: .executeQuery("select datepart(hour, d), datepart(minute, d),"
2360: + " datepart(second, d), datepart(millisecond, d)"
2361: + " from #testWriteDate");
2362: assertTrue(rs.next());
2363: assertEquals(0, rs.getInt(1));
2364: assertEquals(0, rs.getInt(2));
2365: assertEquals(0, rs.getInt(3));
2366: assertEquals(0, rs.getInt(4));
2367: assertFalse(rs.next());
2368: rs.close();
2369: stmt.close();
2370: }
2371:
2372: /**
2373: * Test for bug [1226210] {fn dayofweek()} depends on the language.
2374: */
2375: public void testDayOfWeek() throws Exception {
2376: PreparedStatement pstmt = con
2377: .prepareStatement("SELECT {fn dayofweek({fn curdate()})}");
2378:
2379: // Execute and retrieve the day of week with the default @@DATEFIRST
2380: ResultSet rs = pstmt.executeQuery();
2381: assertNotNull(rs);
2382: assertTrue(rs.next());
2383: int day = rs.getInt(1);
2384:
2385: // Set a new (very unlikely) value for @@DATEFIRST (Thursday)
2386: Statement stmt = con.createStatement();
2387: assertEquals(0, stmt.executeUpdate("SET DATEFIRST 4"));
2388: stmt.close();
2389:
2390: // Now re-execute and compare the two values
2391: rs = pstmt.executeQuery();
2392: assertNotNull(rs);
2393: assertTrue(rs.next());
2394: assertEquals(day, rs.getInt(1));
2395:
2396: pstmt.close();
2397: }
2398:
2399: /**
2400: * Test for bug [1235845] getTimestamp() returns illegal value after
2401: * getString().
2402: */
2403: public void testGetString() throws SQLException {
2404: Statement stmt = con.createStatement();
2405:
2406: ResultSet rs = stmt.executeQuery("select getdate()");
2407: assertTrue(rs.next());
2408: String stringValue = rs.getString(1);
2409: String timestampValue = rs.getTimestamp(1).toString();
2410: assertEquals(stringValue, timestampValue);
2411: rs.close();
2412:
2413: stmt.close();
2414: }
2415:
2416: /**
2417: * Test for bug [1234531] Dates before 01/01/1900 broken due to DateTime
2418: * value markers.
2419: */
2420: public void test1899Date() throws Exception {
2421: // Per the SQL Server documentation
2422: // Send: 12/31/1899 23:59:59.990
2423: // Receive: 12/31/1899 23:59:59.990
2424: Calendar originalValue = Calendar.getInstance();
2425:
2426: originalValue.set(Calendar.MONTH, Calendar.DECEMBER);
2427: originalValue.set(Calendar.DAY_OF_MONTH, 31);
2428: originalValue.set(Calendar.YEAR, 1899);
2429: originalValue.set(Calendar.HOUR_OF_DAY, 23);
2430: originalValue.set(Calendar.MINUTE, 59);
2431: originalValue.set(Calendar.SECOND, 59);
2432: originalValue.set(Calendar.MILLISECOND, 990);
2433:
2434: PreparedStatement pstmt = con.prepareStatement("select ?");
2435: pstmt.setTimestamp(1, new Timestamp(originalValue.getTime()
2436: .getTime()));
2437: ResultSet rs = pstmt.executeQuery();
2438:
2439: assertTrue(rs.next());
2440: final long expectedTime = originalValue.getTime().getTime();
2441: final long actualTime = getTimeInMs(rs);
2442: assertEquals(expectedTime, actualTime);
2443: assertFalse(rs.next());
2444:
2445: rs.close();
2446: pstmt.close();
2447: }
2448:
2449: /**
2450: * Java 1.3 Timestamp.getDate() does not add the nano seconds to
2451: * the millisecond value returned. This causes the timestamp tests
2452: * to fail. If running under java 1.3 we add the nanos ourselves.
2453: *
2454: * @param rs the result set returning the Timstamp value in column 1
2455: * @return the millisecond date value as a <code>long</code>.
2456: */
2457: public long getTimeInMs(ResultSet rs) throws SQLException {
2458: Timestamp value = rs.getTimestamp(1);
2459: long ms = value.getTime();
2460: if (!Driver.JDBC3) {
2461: // Not Running under 1.4 so need to add milliseconds
2462: ms += ((java.sql.Timestamp) value).getNanos() / 1000000;
2463: }
2464: return ms;
2465: }
2466: }
|