001: /*
002: Copyright (C) 2002-2004 MySQL AB
003:
004: This program is free software; you can redistribute it and/or modify
005: it under the terms of version 2 of the GNU General Public License as
006: published by the Free Software Foundation.
007:
008: There are special exceptions to the terms and conditions of the GPL
009: as it is applied to this software. View the full text of the
010: exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
011: software distribution.
012:
013: This program is distributed in the hope that it will be useful,
014: but WITHOUT ANY WARRANTY; without even the implied warranty of
015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
016: GNU General Public License for more details.
017:
018: You should have received a copy of the GNU General Public License
019: along with this program; if not, write to the Free Software
020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
021:
022:
023:
024: */
025: package testsuite.simple;
026:
027: import testsuite.BaseTestCase;
028:
029: import java.sql.Connection;
030: import java.sql.Date;
031: import java.sql.PreparedStatement;
032: import java.sql.SQLException;
033: import java.sql.Statement;
034: import java.sql.Time;
035: import java.sql.Timestamp;
036:
037: import java.text.DateFormat;
038: import java.text.SimpleDateFormat;
039:
040: import java.util.Calendar;
041: import java.util.Locale;
042: import java.util.Properties;
043: import java.util.TimeZone;
044:
045: import com.mysql.jdbc.SQLError;
046:
047: /**
048: *
049: * @author Mark Matthews
050: * @version $Id: DateTest.java 5345 2006-06-01 20:18:04Z mmatthews $
051: */
052: public class DateTest extends BaseTestCase {
053: // ~ Constructors
054: // -----------------------------------------------------------
055:
056: /**
057: * Creates a new DateTest object.
058: *
059: * @param name
060: * DOCUMENT ME!
061: */
062: public DateTest(String name) {
063: super (name);
064: }
065:
066: // ~ Methods
067: // ----------------------------------------------------------------
068:
069: /**
070: * Runs all test cases in this test suite
071: *
072: * @param args
073: */
074: public static void main(String[] args) {
075: junit.textui.TestRunner.run(DateTest.class);
076: }
077:
078: /**
079: * DOCUMENT ME!
080: *
081: * @throws Exception
082: * DOCUMENT ME!
083: */
084: public void setUp() throws Exception {
085: super .setUp();
086: createTestTable();
087: }
088:
089: /**
090: * DOCUMENT ME!
091: *
092: * @throws SQLException
093: * DOCUMENT ME!
094: */
095: public void testTimestamp() throws SQLException {
096: this .pstmt = this .conn
097: .prepareStatement("INSERT INTO DATETEST(tstamp, dt, dtime, tm) VALUES (?, ?, ?, ?)");
098:
099: // TimeZone.setDefault(TimeZone.getTimeZone("GMT"));
100: Calendar cal = Calendar.getInstance();
101: cal.set(Calendar.MONTH, 6);
102: cal.set(Calendar.DAY_OF_MONTH, 3);
103: cal.set(Calendar.YEAR, 2002);
104: cal.set(Calendar.HOUR, 7);
105: cal.set(Calendar.MINUTE, 0);
106: cal.set(Calendar.SECOND, 0);
107: cal.set(Calendar.MILLISECOND, 0);
108: cal.set(Calendar.AM_PM, Calendar.AM);
109: cal.getTime();
110: System.out.println(cal);
111:
112: // DateFormat df = SimpleDateFormat.getInstance();
113: DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss z");
114:
115: Calendar calGMT = Calendar.getInstance(TimeZone
116: .getTimeZone("GMT"));
117: // df.setTimeZone(TimeZone.getTimeZone("GMT"));
118: Timestamp nowTstamp = new Timestamp(cal.getTime().getTime());
119: java.sql.Date nowDate = new java.sql.Date(cal.getTime()
120: .getTime());
121: Timestamp nowDatetime = new Timestamp(cal.getTime().getTime());
122: java.sql.Time nowTime = new java.sql.Time(cal.getTime()
123: .getTime());
124: System.out
125: .println("** Times with given calendar (before storing) **\n");
126: System.out.println("TIMESTAMP:\t" + nowTstamp.getTime()
127: + " -> " + df.format(nowTstamp));
128: System.out.println("DATE:\t\t" + nowDate.getTime() + " -> "
129: + df.format(nowDate));
130: System.out.println("DATETIME:\t" + nowDatetime.getTime()
131: + " -> " + df.format(nowDatetime));
132: System.out.println("DATE:\t\t" + nowDate.getTime() + " -> "
133: + df.format(nowDate));
134: System.out.println("TIME:\t\t" + nowTime.getTime() + " -> "
135: + df.format(nowTime));
136: System.out.println("\n");
137: this .pstmt.setTimestamp(1, nowTstamp, calGMT);
138: // have to use the same TimeZone as used to create or there will be
139: // shift
140: this .pstmt.setDate(2, nowDate, cal);
141: this .pstmt.setTimestamp(3, nowDatetime, calGMT);
142: // have to use the same TimeZone as used to create or there will be
143: // shift
144: this .pstmt.setTime(4, nowTime, cal);
145: this .pstmt.execute();
146:
147: this .pstmt.getUpdateCount();
148: this .pstmt.clearParameters();
149: this .rs = this .stmt.executeQuery("SELECT * from DATETEST");
150:
151: java.sql.Date thenDate = null;
152:
153: while (this .rs.next()) {
154: Timestamp thenTstamp = this .rs.getTimestamp(1, calGMT);
155: thenDate = this .rs.getDate(2, cal);
156:
157: java.sql.Timestamp thenDatetime = this .rs.getTimestamp(3,
158: calGMT);
159:
160: java.sql.Time thenTime = this .rs.getTime(4, cal);
161: System.out
162: .println("** Times with given calendar (retrieved from database) **\n");
163: System.out.println("TIMESTAMP:\t" + thenTstamp.getTime()
164: + " -> " + df.format(thenTstamp));
165: System.out.println("DATE:\t\t" + thenDate.getTime()
166: + " -> " + df.format(thenDate));
167: System.out.println("DATETIME:\t" + thenDatetime.getTime()
168: + " -> " + df.format(thenDatetime));
169: System.out.println("TIME:\t\t" + thenTime.getTime()
170: + " -> " + df.format(thenTime));
171: System.out.println("\n");
172: }
173:
174: this .rs.close();
175: this .rs = null;
176: }
177:
178: public void testNanosParsing() throws SQLException {
179: try {
180: this .stmt
181: .executeUpdate("DROP TABLE IF EXISTS testNanosParsing");
182: this .stmt
183: .executeUpdate("CREATE TABLE testNanosParsing (dateIndex int, field1 VARCHAR(32))");
184: this .stmt
185: .executeUpdate("INSERT INTO testNanosParsing VALUES (1, '1969-12-31 18:00:00.0'), "
186: + "(2, '1969-12-31 18:00:00.90'), "
187: + "(3, '1969-12-31 18:00:00.900'), "
188: + "(4, '1969-12-31 18:00:00.9000'), "
189: + "(5, '1969-12-31 18:00:00.90000'), "
190: + "(6, '1969-12-31 18:00:00.900000'), "
191: + "(7, '1969-12-31 18:00:00.')");
192:
193: this .rs = this .stmt
194: .executeQuery("SELECT field1 FROM testNanosParsing ORDER BY dateIndex ASC");
195: assertTrue(this .rs.next());
196: assertTrue(this .rs.getTimestamp(1).getNanos() == 0);
197: assertTrue(this .rs.next());
198: assertTrue(this .rs.getTimestamp(1).getNanos() + " != 90",
199: this .rs.getTimestamp(1).getNanos() == 90);
200: assertTrue(this .rs.next());
201: assertTrue(this .rs.getTimestamp(1).getNanos() + " != 900",
202: this .rs.getTimestamp(1).getNanos() == 900);
203: assertTrue(this .rs.next());
204: assertTrue(this .rs.getTimestamp(1).getNanos() + " != 9000",
205: this .rs.getTimestamp(1).getNanos() == 9000);
206: assertTrue(this .rs.next());
207: assertTrue(
208: this .rs.getTimestamp(1).getNanos() + " != 90000",
209: this .rs.getTimestamp(1).getNanos() == 90000);
210: assertTrue(this .rs.next());
211: assertTrue(this .rs.getTimestamp(1).getNanos()
212: + " != 900000",
213: this .rs.getTimestamp(1).getNanos() == 900000);
214: assertTrue(this .rs.next());
215:
216: try {
217: this .rs.getTimestamp(1);
218: } catch (SQLException sqlEx) {
219: assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT
220: .equals(sqlEx.getSQLState()));
221: }
222: } finally {
223: this .stmt
224: .executeUpdate("DROP TABLE IF EXISTS testNanosParsing");
225: }
226: }
227:
228: private void createTestTable() throws SQLException {
229: //
230: // Catch the error, the table might exist
231: //
232: try {
233: this .stmt.executeUpdate("DROP TABLE DATETEST");
234: } catch (SQLException SQLE) {
235: ;
236: }
237:
238: this .stmt
239: .executeUpdate("CREATE TABLE DATETEST (tstamp TIMESTAMP, dt DATE, dtime DATETIME, tm TIME)");
240: }
241:
242: /**
243: * Tests the configurability of all-zero date/datetime/timestamp handling in
244: * the driver.
245: *
246: * @throws Exception
247: * if the test fails.
248: */
249: public void testZeroDateBehavior() throws Exception {
250: try {
251: this .stmt
252: .executeUpdate("DROP TABLE IF EXISTS testZeroDateBehavior");
253: this .stmt
254: .executeUpdate("CREATE TABLE testZeroDateBehavior(fieldAsString VARCHAR(32), fieldAsDateTime DATETIME)");
255: this .stmt
256: .executeUpdate("INSERT INTO testZeroDateBehavior VALUES ('0000-00-00 00:00:00', '0000-00-00 00:00:00')");
257: Properties props = new Properties();
258: props.setProperty("zeroDateTimeBehavior", "round");
259: Connection roundConn = getConnectionWithProps(props);
260: Statement roundStmt = roundConn.createStatement();
261: this .rs = roundStmt
262: .executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
263: this .rs.next();
264:
265: assertEquals("0001-01-01", this .rs.getDate(1).toString());
266: assertEquals("0001-01-01 00:00:00.0", new SimpleDateFormat(
267: "yyyy-MM-dd HH:mm:ss.0", Locale.US).format(this .rs
268: .getTimestamp(1)));
269: assertEquals("0001-01-01", this .rs.getDate(2).toString());
270: assertEquals("0001-01-01 00:00:00.0", new SimpleDateFormat(
271: "yyyy-MM-dd HH:mm:ss.0", Locale.US).format(this .rs
272: .getTimestamp(2)));
273:
274: PreparedStatement roundPrepStmt = roundConn
275: .prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
276: this .rs = roundPrepStmt.executeQuery();
277: this .rs.next();
278:
279: assertEquals("0001-01-01", this .rs.getDate(1).toString());
280: assertEquals("0001-01-01 00:00:00.0", new SimpleDateFormat(
281: "yyyy-MM-dd HH:mm:ss.0", Locale.US).format(this .rs
282: .getTimestamp(1)));
283: assertEquals("0001-01-01", this .rs.getDate(2).toString());
284: assertEquals("0001-01-01 00:00:00.0", new SimpleDateFormat(
285: "yyyy-MM-dd HH:mm:ss.0", Locale.US).format(this .rs
286: .getTimestamp(2)));
287:
288: props = new Properties();
289: props.setProperty("zeroDateTimeBehavior", "convertToNull");
290: Connection nullConn = getConnectionWithProps(props);
291: Statement nullStmt = nullConn.createStatement();
292: this .rs = nullStmt
293: .executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
294:
295: this .rs.next();
296:
297: assertTrue(null == this .rs.getDate(1));
298: assertTrue(null == this .rs.getTimestamp(1));
299: assertTrue(null == this .rs.getDate(2));
300: assertTrue(null == this .rs.getTimestamp(2));
301:
302: PreparedStatement nullPrepStmt = nullConn
303: .prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
304: this .rs = nullPrepStmt.executeQuery();
305:
306: this .rs.next();
307:
308: assertTrue(null == this .rs.getDate(1));
309: assertTrue(null == this .rs.getTimestamp(1));
310: assertTrue(null == this .rs.getDate(2));
311: assertTrue(null == this .rs.getTimestamp(2));
312: assertTrue(null == this .rs.getString(2));
313:
314: props = new Properties();
315: props.setProperty("zeroDateTimeBehavior", "exception");
316: Connection exceptionConn = getConnectionWithProps(props);
317: Statement exceptionStmt = exceptionConn.createStatement();
318: this .rs = exceptionStmt
319: .executeQuery("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
320:
321: this .rs.next();
322:
323: try {
324: this .rs.getDate(1);
325: fail("Exception should have been thrown when trying to retrieve invalid date");
326: } catch (SQLException sqlEx) {
327: assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT
328: .equals(sqlEx.getSQLState()));
329: }
330:
331: try {
332: this .rs.getTimestamp(1);
333: fail("Exception should have been thrown when trying to retrieve invalid date");
334: } catch (SQLException sqlEx) {
335: assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT
336: .equals(sqlEx.getSQLState()));
337: }
338:
339: try {
340: this .rs.getDate(2);
341: fail("Exception should have been thrown when trying to retrieve invalid date");
342: } catch (SQLException sqlEx) {
343: assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT
344: .equals(sqlEx.getSQLState()));
345: }
346:
347: try {
348: this .rs.getTimestamp(2);
349: fail("Exception should have been thrown when trying to retrieve invalid date");
350: } catch (SQLException sqlEx) {
351: assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT
352: .equals(sqlEx.getSQLState()));
353: }
354:
355: PreparedStatement exceptionPrepStmt = exceptionConn
356: .prepareStatement("SELECT fieldAsString, fieldAsDateTime FROM testZeroDateBehavior");
357:
358: try {
359: this .rs = exceptionPrepStmt.executeQuery();
360: this .rs.next();
361: this .rs.getDate(2);
362: fail("Exception should have been thrown when trying to retrieve invalid date");
363: } catch (SQLException sqlEx) {
364: assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT
365: .equals(sqlEx.getSQLState()));
366: }
367:
368: } finally {
369: this .stmt
370: .executeUpdate("DROP TABLE IF EXISTS testZeroDateBehavior");
371: }
372: }
373:
374: public void testReggieBug() throws Exception {
375: try {
376: this .stmt
377: .executeUpdate("DROP TABLE IF EXISTS testReggieBug");
378: this .stmt
379: .executeUpdate("CREATE TABLE testReggieBug (field1 DATE)");
380:
381: PreparedStatement pStmt = this .conn
382: .prepareStatement("INSERT INTO testReggieBug VALUES (?)");
383: pStmt.setDate(1, new Date(2004 - 1900, 07, 28));
384: pStmt.executeUpdate();
385: this .rs = this .stmt
386: .executeQuery("SELECT * FROM testReggieBug");
387: this .rs.next();
388: System.out.println(this .rs.getDate(1));
389: this .rs = this .conn.prepareStatement(
390: "SELECT * FROM testReggieBug").executeQuery();
391: this .rs.next();
392: System.out.println(this .rs.getDate(1));
393:
394: } finally {
395: this .stmt
396: .executeUpdate("DROP TABLE IF EXISTS testReggieBug");
397: }
398: }
399:
400: public void testNativeConversions() throws Exception {
401: Timestamp ts = new Timestamp(System.currentTimeMillis());
402: Date dt = new Date(ts.getTime());
403: Time tm = new Time(ts.getTime());
404:
405: createTable(
406: "testNativeConversions",
407: "(time_field TIME, date_field DATE, datetime_field DATETIME, timestamp_field TIMESTAMP)");
408: this .pstmt = this .conn
409: .prepareStatement("INSERT INTO testNativeConversions VALUES (?,?,?,?)");
410: this .pstmt.setTime(1, tm);
411: this .pstmt.setDate(2, dt);
412: this .pstmt.setTimestamp(3, ts);
413: this .pstmt.setTimestamp(4, ts);
414: this .pstmt.execute();
415: this .pstmt.close();
416:
417: this .pstmt = this .conn
418: .prepareStatement("SELECT time_field, date_field, datetime_field, timestamp_field FROM testNativeConversions");
419: this .rs = this .pstmt.executeQuery();
420: assertTrue(this .rs.next());
421: System.out.println(this .rs.getTime(1));
422: System.out.println(this .rs.getTime(2));
423: System.out.println(this .rs.getTime(3));
424: System.out.println(this .rs.getTime(4));
425: System.out.println();
426: System.out.println(this .rs.getDate(1));
427: System.out.println(this .rs.getDate(2));
428: System.out.println(this .rs.getDate(3));
429: System.out.println(this .rs.getDate(4));
430: System.out.println();
431: System.out.println(this .rs.getTimestamp(1));
432: System.out.println(this .rs.getTimestamp(2));
433: System.out.println(this .rs.getTimestamp(3));
434: System.out.println(this .rs.getTimestamp(4));
435: }
436:
437: }
|