001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.timestampArith
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.lang;
023:
024: import org.apache.derby.tools.ij;
025:
026: import java.sql.Connection;
027: import java.sql.PreparedStatement;
028: import java.sql.Statement;
029: import java.sql.ResultSet;
030: import java.sql.SQLException;
031: import java.sql.Timestamp;
032: import java.sql.Types;
033:
034: import java.util.Calendar;
035:
036: /**
037: * Test the JDBC TIMESTAMPADD and TIMESTAMPDIFF escape functions.
038: *
039: * Things to test:
040: * + Test each interval type with timestamp, date, and time inputs.
041: * + Test diff with all 9 combinations of datetime input types (timestamp - timestamp, timestamp - date, etc).
042: * + Test PreparedStatements with parameters, '?', in each argument, and Statements. (Statements are prepared
043: * internally so we do not also have to test PrepardStatements without parameters).
044: * + Test with null inputs.
045: * + Test with input string that is convertible to timestamp.
046: * + Test with invalid interval type.
047: * + Test with invalid arguments in the date time arguments.
048: * + Test TIMESTAMPADD with an invalid type in the count argument.
049: * + Test overflow cases.
050: */
051: public class timestampArith {
052: private static final int FRAC_SECOND_INTERVAL = 0;
053: private static final int SECOND_INTERVAL = 1;
054: private static final int MINUTE_INTERVAL = 2;
055: private static final int HOUR_INTERVAL = 3;
056: private static final int DAY_INTERVAL = 4;
057: private static final int WEEK_INTERVAL = 5;
058: private static final int MONTH_INTERVAL = 6;
059: private static final int QUARTER_INTERVAL = 7;
060: private static final int YEAR_INTERVAL = 8;
061: private static final String[] intervalJdbcNames = {
062: "SQL_TSI_FRAC_SECOND", "SQL_TSI_SECOND", "SQL_TSI_MINUTE",
063: "SQL_TSI_HOUR", "SQL_TSI_DAY", "SQL_TSI_WEEK",
064: "SQL_TSI_MONTH", "SQL_TSI_QUARTER", "SQL_TSI_YEAR" };
065:
066: private static final int ONE_BILLION = 1000000000;
067:
068: int errorCount = 0;
069: private Connection conn;
070: private PreparedStatement[] tsAddPS = new PreparedStatement[intervalJdbcNames.length];
071: private PreparedStatement[] tsDiffPS = new PreparedStatement[intervalJdbcNames.length];
072: private Statement stmt;
073: private static final String TODAY;
074: private static final String TOMORROW;
075: private static final String YEAR_FROM_TOMORROW;
076: private static final String YEAR_FROM_TODAY;
077: private static final String YESTERDAY;
078: private static final String WEEK_FROM_TODAY;
079: static {
080: Calendar cal = Calendar.getInstance();
081: // Make sure that we are not so close to midnight that TODAY might be yesterday before
082: // we are finished using it.
083: while (cal.get(Calendar.HOUR) == 23
084: && cal.get(Calendar.MINUTE) >= 58) {
085: try {
086: Thread.sleep((60 - cal.get(Calendar.SECOND)) * 1000);
087: } catch (InterruptedException ie) {
088: }
089: ;
090: cal = Calendar.getInstance();
091: }
092: TODAY = isoFormatDate(cal);
093: cal.add(Calendar.DATE, -1);
094: YESTERDAY = isoFormatDate(cal);
095: cal.add(Calendar.DATE, 2);
096: TOMORROW = isoFormatDate(cal);
097: cal.add(Calendar.YEAR, 1);
098: YEAR_FROM_TOMORROW = isoFormatDate(cal);
099: cal.add(Calendar.DATE, -1);
100: YEAR_FROM_TODAY = isoFormatDate(cal);
101: cal.add(Calendar.YEAR, -1); // today
102: cal.add(Calendar.DATE, 7);
103: WEEK_FROM_TODAY = isoFormatDate(cal);
104: }
105:
106: private static String isoFormatDate(Calendar cal) {
107: StringBuffer sb = new StringBuffer();
108: String s = String.valueOf(cal.get(Calendar.YEAR));
109: for (int i = s.length(); i < 4; i++)
110: sb.append('0');
111: sb.append(s);
112: sb.append('-');
113:
114: s = String.valueOf(cal.get(Calendar.MONTH) + 1);
115: for (int i = s.length(); i < 2; i++)
116: sb.append('0');
117: sb.append(s);
118: sb.append('-');
119:
120: s = String.valueOf(cal.get(Calendar.DAY_OF_MONTH));
121: for (int i = s.length(); i < 2; i++)
122: sb.append('0');
123: sb.append(s);
124:
125: return sb.toString();
126: }
127:
128: private final OneTest[] tests = {
129: // timestamp - timestamp
130: new OneDiffTest(FRAC_SECOND_INTERVAL,
131: ts("2005-05-10 08:25:00"),
132: ts("2005-05-10 08:25:00.000001"), 1000, null, null),
133: new OneDiffTest(SECOND_INTERVAL, ts("2005-05-10 08:25:01"),
134: ts("2005-05-10 08:25:00"), -1, null, null),
135: new OneDiffTest(SECOND_INTERVAL,
136: ts("2005-05-10 08:25:00.1"),
137: ts("2005-05-10 08:25:00"), 0, null, null),
138: new OneDiffTest(SECOND_INTERVAL, ts("2005-05-10 08:25:00"),
139: ts("2005-05-10 08:26:00"), 60, null, null),
140: new OneDiffTest(MINUTE_INTERVAL, ts("2005-05-11 08:25:00"),
141: ts("2005-05-10 08:25:00"), -24 * 60, null, null),
142: new OneDiffTest(HOUR_INTERVAL, ts("2005-05-10 08:25:00"),
143: ts("2005-05-11 08:25:00"), 24, null, null),
144: new OneDiffTest(DAY_INTERVAL, ts("2005-05-10 08:25:00"),
145: ts("2005-05-11 08:25:00"), 1, null, null),
146: new OneDiffTest(DAY_INTERVAL, ts("2005-05-10 08:25:01"),
147: ts("2005-05-11 08:25:00"), 0, null, null),
148: new OneDiffTest(WEEK_INTERVAL, ts("2005-02-23 08:25:00"),
149: ts("2005-03-01 08:25:00"), 0, null, null),
150: new OneDiffTest(MONTH_INTERVAL, ts("2005-02-23 08:25:00"),
151: ts("2005-03-23 08:25:00"), 1, null, null),
152: new OneDiffTest(MONTH_INTERVAL, ts("2005-02-23 08:25:01"),
153: ts("2005-03-23 08:25:00"), 0, null, null),
154: new OneDiffTest(QUARTER_INTERVAL,
155: ts("2005-02-23 08:25:00"),
156: ts("2005-05-23 08:25:00"), 1, null, null),
157: new OneDiffTest(QUARTER_INTERVAL,
158: ts("2005-02-23 08:25:01"),
159: ts("2005-05-23 08:25:00"), 0, null, null),
160: new OneDiffTest(YEAR_INTERVAL, ts("2005-02-23 08:25:00"),
161: ts("2005-05-23 08:25:00"), 0, null, null),
162: new OneDiffTest(YEAR_INTERVAL, ts("2005-02-23 08:25:00"),
163: ts("2006-02-23 08:25:00"), 1, null, null),
164:
165: // timestamp - time, time - timestamp
166: new OneDiffTest(FRAC_SECOND_INTERVAL, ts(TODAY
167: + " 10:00:00.123456"), tm("10:00:00"), -123456000,
168: null, null),
169: new OneDiffTest(FRAC_SECOND_INTERVAL, tm("10:00:00"),
170: ts(TODAY + " 10:00:00.123456"), 123456000, null,
171: null),
172: new OneDiffTest(SECOND_INTERVAL, ts(TODAY + " 10:00:00.1"),
173: tm("10:00:01"), 0, null, null),
174: new OneDiffTest(SECOND_INTERVAL, tm("10:00:01"), ts(TODAY
175: + " 10:00:00"), -1, null, null),
176: new OneDiffTest(MINUTE_INTERVAL, ts(TODAY + " 10:02:00"),
177: tm("10:00:00"), -2, null, null),
178: new OneDiffTest(MINUTE_INTERVAL, tm("11:00:00"), ts(TODAY
179: + " 10:02:00"), -58, null, null),
180: new OneDiffTest(HOUR_INTERVAL, ts(TODAY + " 10:02:00"),
181: tm("10:00:00"), 0, null, null),
182: new OneDiffTest(HOUR_INTERVAL, tm("10:00:00"), ts(TODAY
183: + " 23:02:00"), 13, null, null),
184: new OneDiffTest(DAY_INTERVAL, ts(TODAY + " 00:00:00"),
185: tm("23:59:59"), 0, null, null),
186: new OneDiffTest(DAY_INTERVAL, tm("23:59:59"), ts(TODAY
187: + " 00:00:00"), 0, null, null),
188: new OneDiffTest(WEEK_INTERVAL, ts(TODAY + " 00:00:00"),
189: tm("23:59:59"), 0, null, null),
190: new OneDiffTest(WEEK_INTERVAL, tm("23:59:59"), ts(TODAY
191: + " 00:00:00"), 0, null, null),
192: new OneDiffTest(MONTH_INTERVAL, ts(TODAY + " 00:00:00"),
193: tm("23:59:59"), 0, null, null),
194: new OneDiffTest(MONTH_INTERVAL, tm("23:59:59"), ts(TODAY
195: + " 00:00:00"), 0, null, null),
196: new OneDiffTest(QUARTER_INTERVAL, ts(TODAY + " 00:00:00"),
197: tm("23:59:59"), 0, null, null),
198: new OneDiffTest(QUARTER_INTERVAL, tm("23:59:59"), ts(TODAY
199: + " 00:00:00"), 0, null, null),
200: new OneDiffTest(YEAR_INTERVAL, ts(TODAY + " 00:00:00"),
201: tm("23:59:59"), 0, null, null),
202: new OneDiffTest(YEAR_INTERVAL, tm("23:59:59"), ts(TODAY
203: + " 00:00:00"), 0, null, null),
204:
205: // timestamp - date, date - timestamp
206: new OneDiffTest(FRAC_SECOND_INTERVAL,
207: ts("2004-05-10 00:00:00.123456"), dt("2004-05-10"),
208: -123456000, null, null),
209: new OneDiffTest(FRAC_SECOND_INTERVAL, dt("2004-05-10"),
210: ts("2004-05-10 00:00:00.123456"), 123456000, null,
211: null),
212: new OneDiffTest(SECOND_INTERVAL, ts("2004-05-10 08:25:01"),
213: dt("2004-05-10"), -(1 + 60 * (25 + 60 * 8)), null,
214: null),
215: new OneDiffTest(SECOND_INTERVAL, dt("2004-05-10"),
216: ts("2004-05-09 23:59:00"), -60, null, null),
217: new OneDiffTest(MINUTE_INTERVAL, ts("2004-05-11 08:25:00"),
218: dt("2004-05-10"), -(24 * 60 + 8 * 60 + 25), null,
219: null),
220: new OneDiffTest(MINUTE_INTERVAL, dt("2004-05-10"),
221: ts("2004-05-11 08:25:00"), 24 * 60 + 8 * 60 + 25,
222: null, null),
223: new OneDiffTest(HOUR_INTERVAL, ts("2004-02-28 08:25:00"),
224: dt("2004-03-01"), 39, null, null),
225: new OneDiffTest(HOUR_INTERVAL, dt("2005-03-01"),
226: ts("2005-02-28 08:25:00"), -15, null, null),
227: new OneDiffTest(DAY_INTERVAL, ts("2004-05-10 08:25:00"),
228: dt("2004-05-11"), 0, null, null),
229: new OneDiffTest(DAY_INTERVAL, dt("2004-05-10"),
230: ts("2004-05-11 08:25:00"), 1, null, null),
231: new OneDiffTest(WEEK_INTERVAL, ts("2004-02-23 00:00:00"),
232: dt("2004-03-01"), 1, null, null),
233: new OneDiffTest(WEEK_INTERVAL, dt("2004-03-01"),
234: ts("2004-02-23 00:00:00"), -1, null, null),
235: new OneDiffTest(MONTH_INTERVAL, ts("2004-02-23 08:25:00"),
236: dt("2004-03-24"), 1, null, null),
237: new OneDiffTest(MONTH_INTERVAL, dt("2005-03-24"),
238: ts("2004-02-23 08:25:00"), -13, null, null),
239: new OneDiffTest(QUARTER_INTERVAL,
240: ts("2004-02-23 08:25:00"), dt("2004-05-24"), 1,
241: null, null),
242: new OneDiffTest(QUARTER_INTERVAL, dt("2004-05-23"),
243: ts("2004-02-23 08:25:01"), 0, null, null),
244: new OneDiffTest(YEAR_INTERVAL, ts("2004-02-23 08:25:00"),
245: dt("2004-05-23"), 0, null, null),
246: new OneDiffTest(YEAR_INTERVAL, dt("2004-05-23"),
247: ts("2003-02-23 08:25:00"), -1, null, null),
248:
249: // date - time, time - date
250: new OneDiffTest(FRAC_SECOND_INTERVAL, dt(TODAY),
251: tm("00:00:01"), ONE_BILLION, null, null),
252: new OneDiffTest(FRAC_SECOND_INTERVAL, tm("00:00:02"),
253: dt(TODAY), -2 * ONE_BILLION, null, null),
254: new OneDiffTest(SECOND_INTERVAL, dt(TODAY), tm("00:00:01"),
255: 1, null, null),
256: new OneDiffTest(SECOND_INTERVAL, tm("00:00:02"), dt(TODAY),
257: -2, null, null),
258: new OneDiffTest(MINUTE_INTERVAL, dt(TODAY), tm("12:34:56"),
259: 12 * 60 + 34, null, null),
260: new OneDiffTest(MINUTE_INTERVAL, tm("12:34:56"), dt(TODAY),
261: -(12 * 60 + 34), null, null),
262: new OneDiffTest(HOUR_INTERVAL, dt(TODAY), tm("12:34:56"),
263: 12, null, null),
264: new OneDiffTest(HOUR_INTERVAL, tm("12:34:56"), dt(TODAY),
265: -12, null, null),
266: new OneDiffTest(DAY_INTERVAL, dt(TOMORROW), tm("00:00:00"),
267: -1, null, null),
268: new OneDiffTest(DAY_INTERVAL, tm("00:00:00"), dt(TOMORROW),
269: 1, null, null),
270: new OneDiffTest(WEEK_INTERVAL, dt(TOMORROW),
271: tm("00:00:00"), 0, null, null),
272: new OneDiffTest(WEEK_INTERVAL, tm("00:00:00"),
273: dt(TOMORROW), 0, null, null),
274: new OneDiffTest(MONTH_INTERVAL, dt(YEAR_FROM_TOMORROW),
275: tm("12:00:00"), -12, null, null),
276: new OneDiffTest(MONTH_INTERVAL, tm("12:00:00"),
277: dt(YEAR_FROM_TOMORROW), 12, null, null),
278: new OneDiffTest(QUARTER_INTERVAL, dt(YEAR_FROM_TOMORROW),
279: tm("12:00:00"), -4, null, null),
280: new OneDiffTest(QUARTER_INTERVAL, tm("12:00:00"),
281: dt(YEAR_FROM_TOMORROW), 4, null, null),
282: new OneDiffTest(YEAR_INTERVAL, dt(YEAR_FROM_TOMORROW),
283: tm("12:00:00"), -1, null, null),
284: new OneDiffTest(YEAR_INTERVAL, tm("12:00:00"),
285: dt(YEAR_FROM_TOMORROW), 1, null, null),
286:
287: // Test add with all combinatons of interval types and datetime types
288: new OneAddTest(FRAC_SECOND_INTERVAL, 1000,
289: ts("2005-05-11 15:55:00"),
290: ts("2005-05-11 15:55:00.000001"), null, null),
291: new OneAddTest(FRAC_SECOND_INTERVAL, -1000,
292: dt("2005-05-11"), ts("2005-05-10 23:59:59.999999"),
293: null, null),
294: new OneAddTest(FRAC_SECOND_INTERVAL, ONE_BILLION,
295: tm("23:59:59"), ts(TOMORROW + " 00:00:00"), null,
296: null),
297: new OneAddTest(SECOND_INTERVAL, 60,
298: ts("2005-05-11 15:55:00"),
299: ts("2005-05-11 15:56:00"), null, null),
300: new OneAddTest(SECOND_INTERVAL, 60, dt("2005-05-11"),
301: ts("2005-05-11 00:01:00"), null, null),
302: new OneAddTest(SECOND_INTERVAL, 60, tm("23:59:30"),
303: ts(TOMORROW + " 00:00:30"), null, null),
304: new OneAddTest(MINUTE_INTERVAL, -1,
305: ts("2005-05-11 15:55:00"),
306: ts("2005-05-11 15:54:00"), null, null),
307: new OneAddTest(MINUTE_INTERVAL, 1, dt("2005-05-11"),
308: ts("2005-05-11 00:01:00"), null, null),
309: new OneAddTest(MINUTE_INTERVAL, 1, tm("12:00:00"), ts(TODAY
310: + " 12:01:00"), null, null),
311: new OneAddTest(HOUR_INTERVAL, 2, ts("2005-05-11 15:55:00"),
312: ts("2005-05-11 17:55:00"), null, null),
313: new OneAddTest(HOUR_INTERVAL, -2, dt("2005-05-11"),
314: ts("2005-05-10 22:00:00"), null, null),
315: new OneAddTest(HOUR_INTERVAL, 1, tm("12:00:00"), ts(TODAY
316: + " 13:00:00"), null, null),
317: new OneAddTest(DAY_INTERVAL, 1, ts("2005-05-11 15:55:00"),
318: ts("2005-05-12 15:55:00"), null, null),
319: new OneAddTest(DAY_INTERVAL, 1, dt("2005-05-11"),
320: ts("2005-05-12 00:00:00"), null, null),
321: new OneAddTest(DAY_INTERVAL, -1, tm("12:00:00"),
322: ts(YESTERDAY + " 12:00:00"), null, null),
323: new OneAddTest(WEEK_INTERVAL, 1, ts("2005-05-11 15:55:00"),
324: ts("2005-05-18 15:55:00"), null, null),
325: new OneAddTest(WEEK_INTERVAL, 1, dt("2005-05-11"),
326: ts("2005-05-18 00:00:00"), null, null),
327: new OneAddTest(WEEK_INTERVAL, 1, tm("12:00:00"),
328: ts(WEEK_FROM_TODAY + " 12:00:00"), null, null),
329: new OneAddTest(MONTH_INTERVAL, 1,
330: ts("2005-05-11 15:55:00"),
331: ts("2005-06-11 15:55:00"), null, null),
332: new OneAddTest(MONTH_INTERVAL, -1, dt("2005-03-29"),
333: ts("2005-02-28 00:00:00"), null, null),
334: new OneAddTest(MONTH_INTERVAL, 12, tm("12:00:00"),
335: ts(YEAR_FROM_TODAY + " 12:00:00"), null, null),
336: new OneAddTest(QUARTER_INTERVAL, 1,
337: ts("2005-10-11 15:55:00"),
338: ts("2006-01-11 15:55:00"), null, null),
339: new OneAddTest(QUARTER_INTERVAL, -2, dt("2005-05-05"),
340: ts("2004-11-05 00:00:00"), null, null),
341: new OneAddTest(QUARTER_INTERVAL, 4, tm("12:00:00"),
342: ts(YEAR_FROM_TODAY + " 12:00:00"), null, null),
343: new OneAddTest(YEAR_INTERVAL, -10,
344: ts("2005-10-11 15:55:00"),
345: ts("1995-10-11 15:55:00"), null, null),
346: new OneAddTest(YEAR_INTERVAL, 2, dt("2005-05-05"),
347: ts("2007-05-05 00:00:00"), null, null),
348: new OneAddTest(YEAR_INTERVAL, 1, tm("12:00:00"),
349: ts(YEAR_FROM_TODAY + " 12:00:00"), null, null),
350:
351: // String inputs
352: new OneStringDiffTest(SECOND_INTERVAL,
353: "2005-05-10 08:25:00", "2005-05-10 08:26:00", 60,
354: null, null),
355: new OneStringAddTest(DAY_INTERVAL, 1,
356: "2005-05-11 15:55:00", ts("2005-05-12 15:55:00"),
357: null, null),
358:
359: // Overflow
360: new OneDiffTest(FRAC_SECOND_INTERVAL,
361: ts("2004-05-10 00:00:00.123456"),
362: ts("2004-05-10 00:00:10.123456"), 0, "22003",
363: "The resulting value is outside the range for the data type INTEGER."),
364: new OneDiffTest(FRAC_SECOND_INTERVAL,
365: ts("2004-05-10 00:00:00.123456"),
366: ts("2005-05-10 00:00:00.123456"), 0, "22003",
367: "The resulting value is outside the range for the data type INTEGER."),
368: new OneDiffTest(SECOND_INTERVAL, ts("1904-05-10 00:00:00"),
369: ts("2205-05-10 00:00:00"), 0, "22003",
370: "The resulting value is outside the range for the data type INTEGER."),
371: new OneAddTest(YEAR_INTERVAL, 99999,
372: ts("2004-05-10 00:00:00.123456"), null, "22003",
373: "The resulting value is outside the range for the data type TIMESTAMP.") };
374:
375: private final String[][] invalid = {
376: {
377: "values( {fn TIMESTAMPDIFF( SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})",
378: "42X01",
379: "Syntax error: Encountered \"SECOND\" at line 1, column 28." },
380: {
381: "values( {fn TIMESTAMPDIFF( , CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})",
382: "42X01",
383: "Syntax error: Encountered \",\" at line 1, column 28." },
384: {
385: "values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 5)})",
386: "42X01",
387: "Syntax error: Encountered \",\" at line 1, column 80." },
388: {
389: "values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, 'x')})",
390: "42X45",
391: "CHAR is an invalid type for argument number 3 of TIMESTAMPDIFF." },
392: {
393: "values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, 'x', CURRENT_TIMESTAMP)})",
394: "42X45",
395: "CHAR is an invalid type for argument number 2 of TIMESTAMPDIFF." },
396: {
397: "values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP)})",
398: "42X01",
399: "Syntax error: Encountered \")\" at line 1, column 61." },
400: { "values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND)})", "42X01",
401: "Syntax error: Encountered \")\" at line 1, column 42." },
402: { "values( {fn TIMESTAMPADD( x, 1, CURRENT_TIMESTAMP)})",
403: "42X01",
404: "Syntax error: Encountered \"x\" at line 1, column 27." },
405: {
406: "values( {fn TIMESTAMPADD( SQL_TSI_SECOND, CURRENT_DATE, CURRENT_TIMESTAMP)})",
407: "42X45",
408: "DATE is an invalid type for argument number 2 of TIMESTAMPADD." },
409: {
410: "values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 'XX', CURRENT_TIMESTAMP)})",
411: "42X45",
412: "CHAR is an invalid type for argument number 2 of TIMESTAMPADD." },
413: {
414: "values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1.1, CURRENT_TIMESTAMP)})",
415: "42X45",
416: "DECIMAL is an invalid type for argument number 2 of TIMESTAMPADD." },
417: { "values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1, 2.1)})",
418: "42X45",
419: "DECIMAL is an invalid type for argument number 3 of TIMESTAMPADD." },
420: { "values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1, 'XX')})",
421: "42X45",
422: "CHAR is an invalid type for argument number 3 of TIMESTAMPADD." },
423: { "values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1)})",
424: "42X01",
425: "Syntax error: Encountered \")\" at line 1, column 44." },
426: { "values( {fn TIMESTAMPADD( SQL_TSI_SECOND)})", "42X01",
427: "Syntax error: Encountered \")\" at line 1, column 41." } };
428:
429: private static java.sql.Timestamp ts(String s) {
430: // Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff
431: if (s.length() < 29) {
432: // Pad out the fraction with zeros
433: StringBuffer sb = new StringBuffer(s);
434: if (s.length() == 19)
435: sb.append('.');
436: while (sb.length() < 29)
437: sb.append('0');
438: s = sb.toString();
439: }
440: try {
441: return java.sql.Timestamp.valueOf(s);
442: } catch (Exception e) {
443: System.out
444: .println(s + " is not a proper timestamp string.");
445: System.out.println(e.getClass().getName() + ": "
446: + e.getMessage());
447: e.printStackTrace();
448: System.exit(1);
449: return null;
450: }
451: }
452:
453: private static java.sql.Date dt(String s) {
454: return java.sql.Date.valueOf(s);
455: }
456:
457: private static java.sql.Time tm(String s) {
458: return java.sql.Time.valueOf(s);
459: }
460:
461: private static String dateTimeToLiteral(Object ts) {
462: if (ts instanceof java.sql.Timestamp)
463: return "{ts '" + ((java.sql.Timestamp) ts).toString()
464: + "'}";
465: else if (ts instanceof java.sql.Time)
466: return "{t '" + ((java.sql.Time) ts).toString() + "'}";
467: else if (ts instanceof java.sql.Date)
468: return "{d '" + ((java.sql.Date) ts).toString() + "'}";
469: else if (ts instanceof String)
470: return "TIMESTAMP( '" + ((String) ts) + "')";
471: else
472: return ts.toString();
473: }
474:
475: public static void main(String[] args) {
476: System.out.println("Test timestamp arithmetic starting.");
477: try {
478: timestampArith tester = new timestampArith(args);
479: tester.doIt();
480: if (tester.errorCount == 0)
481: System.out.println("PASSED.");
482: else if (tester.errorCount == 1)
483: System.out.println("FAILED. 1 error.");
484: else
485: System.out.println("FAILED. " + tester.errorCount
486: + " errors.");
487: } catch (SQLException sqle) {
488: reportSQLException(sqle);
489: System.exit(1);
490: } catch (Exception e) {
491: System.out.println("Unexpected exception: "
492: + e.getMessage());
493: e.printStackTrace();
494: System.exit(1);
495: }
496: System.exit(0);
497: } // end of main
498:
499: String composeSqlStr(String fn, int interval, String parm1,
500: String parm2) {
501: return "values( {fn TIMESTAMP" + fn + "( "
502: + intervalJdbcNames[interval] + ", " + parm1 + ","
503: + parm2 + ")})";
504: }
505:
506: private timestampArith(String[] args) throws Exception {
507: // make the initial connection.
508: ij.getPropertyArg(args);
509: conn = ij.startJBMS();
510:
511: conn.setAutoCommit(false);
512: for (int i = 0; i < intervalJdbcNames.length; i++) {
513: tsAddPS[i] = conn.prepareStatement(composeSqlStr("ADD", i,
514: "?", "?"));
515: tsDiffPS[i] = conn.prepareStatement(composeSqlStr("DIFF",
516: i, "?", "?"));
517: }
518: stmt = conn.createStatement();
519: }
520:
521: private void doIt() throws SQLException {
522: for (int i = 0; i < tests.length; i++)
523: tests[i].runTest();
524:
525: testNullInputs();
526:
527: for (int i = 0; i < invalid.length; i++) {
528: try {
529: ResultSet rs = stmt.executeQuery(invalid[i][0]);
530: rs.next();
531: reportFailure("\"" + invalid[i][0]
532: + "\" did not throw an exception.");
533: } catch (SQLException sqle) {
534: checkExpectedException(sqle, invalid[i][1],
535: invalid[i][2], "\"" + invalid[i][0] + "\"");
536: }
537: }
538:
539: testInvalidArgTypes();
540: } // end of doIt
541:
542: private void testInvalidArgTypes() throws SQLException {
543: expectException(
544: tsDiffPS[HOUR_INTERVAL],
545: ts("2005-05-11 15:26:00"),
546: new Double(2.0),
547: "XCL12",
548: "An attempt was made to put a data value of type 'double' into a data value of type 'TIMESTAMP'.",
549: "TIMESTAMPDIFF with double ts2");
550: expectException(
551: tsDiffPS[HOUR_INTERVAL],
552: new Double(2.0),
553: ts("2005-05-11 15:26:00"),
554: "XCL12",
555: "An attempt was made to put a data value of type 'double' into a data value of type 'TIMESTAMP'.",
556: "TIMESTAMPDIFF with double ts1");
557:
558: expectException(
559: tsAddPS[MINUTE_INTERVAL],
560: new Integer(1),
561: new Integer(-1),
562: "XCL12",
563: "An attempt was made to put a data value of type 'int' into a data value of type 'TIMESTAMP'.",
564: "TIMESTAMPADD with int ts");
565: expectException(
566: tsAddPS[MINUTE_INTERVAL],
567: ts("2005-05-11 15:26:00"),
568: ts("2005-05-11 15:26:00"),
569: "XCL12",
570: "An attempt was made to put a data value of type 'java.sql.Timestamp' into a data value of type 'INTEGER'.",
571: "TIMESTAMPADD with timestamp count");
572: } // end of testInvalidArgTypes
573:
574: private void expectException(PreparedStatement ps, Object arg1,
575: Object arg2, String expectedSQLState, String expectedMsg,
576: String label) {
577: try {
578: ps.setObject(1, arg1);
579: ps.setObject(2, arg2);
580: ResultSet rs = ps.executeQuery();
581: rs.next();
582: reportFailure(label + " did not throw an exception.");
583: } catch (SQLException sqle) {
584: checkExpectedException(sqle, expectedSQLState, expectedMsg,
585: label);
586: }
587: ;
588: } // end of expectException
589:
590: private void checkExpectedException(SQLException sqle,
591: String expectedSQLState, String expectedMsg, String label) {
592: if (!expectedSQLState.equals(sqle.getSQLState()))
593: reportFailure("Unexpected SQLState from \"" + label
594: + "\". expected " + expectedSQLState + " got "
595: + sqle.getSQLState());
596: else if (expectedMsg != null
597: && !expectedMsg.equals(sqle.getMessage()))
598: reportFailure("Unexpected message from \"" + label
599: + "\".\n expected \"" + expectedMsg
600: + "\"\n got \"" + sqle.getMessage() + "\"");
601: } // end of checkExpectedException
602:
603: private void testNullInputs() throws SQLException {
604: // Null inputs, each position, each type
605: tsDiffPS[HOUR_INTERVAL].setTimestamp(1,
606: ts("2005-05-11 15:26:00"));
607: tsDiffPS[HOUR_INTERVAL].setNull(2, Types.TIMESTAMP);
608: expectNullResult(tsDiffPS[HOUR_INTERVAL],
609: "TIMESTAMPDIFF with null timestamp in third argument");
610: tsDiffPS[HOUR_INTERVAL].setNull(2, Types.DATE);
611: expectNullResult(tsDiffPS[HOUR_INTERVAL],
612: "TIMESTAMPDIFF with null date in third argument");
613:
614: tsDiffPS[HOUR_INTERVAL].setTimestamp(2,
615: ts("2005-05-11 15:26:00"));
616: tsDiffPS[HOUR_INTERVAL].setNull(1, Types.TIMESTAMP);
617: expectNullResult(tsDiffPS[HOUR_INTERVAL],
618: "TIMESTAMPDIFF with null timestamp in second argument");
619: tsDiffPS[HOUR_INTERVAL].setNull(1, Types.DATE);
620: expectNullResult(tsDiffPS[HOUR_INTERVAL],
621: "TIMESTAMPDIFF with null date in second argument");
622:
623: tsAddPS[MINUTE_INTERVAL].setTimestamp(2,
624: ts("2005-05-11 15:26:00"));
625: tsAddPS[MINUTE_INTERVAL].setNull(1, Types.INTEGER);
626: expectNullResult(tsAddPS[MINUTE_INTERVAL],
627: "TIMESTAMPADD with null integer in second argument");
628:
629: tsAddPS[MINUTE_INTERVAL].setInt(1, 1);
630: tsAddPS[MINUTE_INTERVAL].setNull(2, Types.TIMESTAMP);
631: expectNullResult(tsAddPS[MINUTE_INTERVAL],
632: "TIMESTAMPADD with null timestamp in third argument");
633: tsAddPS[MINUTE_INTERVAL].setNull(2, Types.DATE);
634: expectNullResult(tsAddPS[MINUTE_INTERVAL],
635: "TIMESTAMPADD with null date in third argument");
636: } // end of testNullInputs
637:
638: private void expectNullResult(PreparedStatement ps, String label) {
639: try {
640: ResultSet rs = ps.executeQuery();
641: if (!rs.next())
642: reportFailure(label + " returned no rows.");
643: else if (rs.getObject(1) != null)
644: reportFailure(label + " did not return null.");
645: } catch (SQLException sqle) {
646: reportFailure("Unexpected exception from " + label);
647: reportSQLException(sqle);
648: }
649: } // end of expectNullResult
650:
651: private static void reportSQLException(SQLException sqle) {
652: System.out.println("Unexpected exception:");
653: for (;;) {
654: System.out.println(" " + sqle.getMessage());
655: if (sqle.getNextException() != null)
656: sqle = sqle.getNextException();
657: else
658: break;
659: }
660: sqle.printStackTrace();
661: } // end of reportSQLException
662:
663: private void reportFailure(String msg) {
664: errorCount++;
665: System.out.println(msg);
666: }
667:
668: private static void setDateTime(PreparedStatement ps,
669: int parameterIdx, java.util.Date dateTime)
670: throws SQLException {
671: if (dateTime instanceof java.sql.Timestamp)
672: ps
673: .setTimestamp(parameterIdx,
674: (java.sql.Timestamp) dateTime);
675: else if (dateTime instanceof java.sql.Date)
676: ps.setDate(parameterIdx, (java.sql.Date) dateTime);
677: else if (dateTime instanceof java.sql.Time)
678: ps.setTime(parameterIdx, (java.sql.Time) dateTime);
679: else
680: ps
681: .setTimestamp(parameterIdx,
682: (java.sql.Timestamp) dateTime);
683: }
684:
685: abstract class OneTest {
686: final int interval; // FRAC_SECOND_INTERVAL, SECOND_INTERVAL, ... or YEAR_INTERVAL
687: final String expectedSQLState; // Null if no SQLException is expected
688: final String expectedMsg; // Null if no SQLException is expected
689: String sql;
690:
691: OneTest(int interval, String expectedSQLState,
692: String expectedMsg) {
693: this .interval = interval;
694: this .expectedSQLState = expectedSQLState;
695: this .expectedMsg = expectedMsg;
696: }
697:
698: void runTest() {
699: sql = composeSQL();
700: ResultSet rs = null;
701: try {
702: rs = stmt.executeQuery(sql);
703: checkResultSet(rs, sql);
704: if (expectedSQLState != null)
705: reportFailure("Statement '" + sql
706: + "' did not generate an exception");
707: } catch (SQLException sqle) {
708: checkSQLException("Statement", sqle);
709: }
710: if (rs != null) {
711: try {
712: rs.close();
713: } catch (SQLException sqle) {
714: }
715: ;
716: rs = null;
717: }
718:
719: try {
720: rs = executePS();
721: checkResultSet(rs, sql);
722: if (expectedSQLState != null)
723: reportFailure("PreparedStatement '" + sql
724: + "' did not generate an exception");
725: } catch (SQLException sqle) {
726: checkSQLException("PreparedStatement", sqle);
727: }
728: if (rs != null) {
729: try {
730: rs.close();
731: } catch (SQLException sqle) {
732: }
733: ;
734: rs = null;
735: }
736: } // end of RunTest
737:
738: private void checkResultSet(ResultSet rs, String sql)
739: throws SQLException {
740: if (rs.next()) {
741: checkResultRow(rs, sql);
742: if (rs.next())
743: reportFailure("'" + sql
744: + "' returned more than one row.");
745: } else
746: reportFailure("'" + sql + "' did not return any rows.");
747: } // end of checkResultSet
748:
749: private void checkSQLException(String type, SQLException sqle) {
750: if (expectedSQLState != null) {
751: if (!expectedSQLState.equals(sqle.getSQLState()))
752: reportFailure("Incorrect SQLState from " + type
753: + " '" + sql + "' expected "
754: + expectedSQLState + " got "
755: + sqle.getSQLState());
756: else if (expectedMsg != null
757: && !expectedMsg.equals(sqle.getMessage()))
758: reportFailure("Incorrect exception message from "
759: + type + " '" + sql + "' expected '"
760: + expectedMsg + "' got '"
761: + sqle.getMessage() + "'");
762: } else {
763: reportFailure("Unexpected exception from " + type
764: + " '" + sql + "'");
765: reportSQLException(sqle);
766: }
767: } // end of checkSQLException
768:
769: abstract String composeSQL();
770:
771: abstract void checkResultRow(ResultSet rs, String sql)
772: throws SQLException;
773:
774: abstract ResultSet executePS() throws SQLException;
775: }
776:
777: class OneDiffTest extends OneTest {
778: private final java.util.Date ts1;
779: private final java.util.Date ts2;
780: final int expectedDiff;
781: protected boolean expectNull;
782:
783: OneDiffTest(int interval, java.util.Date ts1,
784: java.util.Date ts2, int expectedDiff,
785: String expectedSQLState, String expectedMsg) {
786: super (interval, expectedSQLState, expectedMsg);
787: this .ts1 = ts1;
788: this .ts2 = ts2;
789: this .expectedDiff = expectedDiff;
790: expectNull = (ts1 == null) || (ts2 == null);
791: }
792:
793: String composeSQL() {
794: return composeSqlStr("DIFF", interval,
795: dateTimeToLiteral(ts1), dateTimeToLiteral(ts2));
796: }
797:
798: void checkResultRow(ResultSet rs, String sql)
799: throws SQLException {
800: int actualDiff = rs.getInt(1);
801: if (rs.wasNull()) {
802: if (!expectNull)
803: reportFailure("Unexpected null result from '" + sql
804: + "'.");
805: } else {
806: if (expectNull)
807: reportFailure("Expected null result from '" + sql
808: + "'.");
809: else if (actualDiff != expectedDiff)
810: reportFailure("Unexpected result from '" + sql
811: + "'. Expected " + expectedDiff + " got "
812: + actualDiff + ".");
813: }
814: }
815:
816: ResultSet executePS() throws SQLException {
817: setDateTime(tsDiffPS[interval], 1, ts1);
818: setDateTime(tsDiffPS[interval], 2, ts2);
819: return tsDiffPS[interval].executeQuery();
820: }
821: } // end of class OneDiffTest
822:
823: class OneStringDiffTest extends OneDiffTest {
824: private final String ts1;
825: private final String ts2;
826:
827: OneStringDiffTest(int interval, String ts1, String ts2,
828: int expectedDiff, String expectedSQLState,
829: String expectedMsg) {
830: super (interval, (java.util.Date) null,
831: (java.util.Date) null, expectedDiff,
832: expectedSQLState, expectedMsg);
833: this .ts1 = ts1;
834: this .ts2 = ts2;
835: expectNull = (ts1 == null) || (ts2 == null);
836: }
837:
838: String composeSQL() {
839: return composeSqlStr("DIFF", interval,
840: dateTimeToLiteral(ts1), dateTimeToLiteral(ts2));
841: }
842:
843: ResultSet executePS() throws SQLException {
844: tsDiffPS[interval].setString(1, ts1);
845: tsDiffPS[interval].setString(2, ts2);
846: return tsDiffPS[interval].executeQuery();
847: }
848: } // end of class OneStringDiffTest
849:
850: class OneAddTest extends OneTest {
851: private final java.util.Date ts;
852: final int count;
853: final java.sql.Timestamp expected;
854:
855: OneAddTest(int interval, int count, java.util.Date ts,
856: java.sql.Timestamp expected, String expectedSQLState,
857: String expectedMsg) {
858: super (interval, expectedSQLState, expectedMsg);
859: this .count = count;
860: this .ts = ts;
861: this .expected = expected;
862: }
863:
864: String composeSQL() {
865: return composeSqlStr("ADD", interval,
866: String.valueOf(count), dateTimeToLiteral(ts));
867: }
868:
869: void checkResultRow(ResultSet rs, String sql)
870: throws SQLException {
871: java.sql.Timestamp actual = rs.getTimestamp(1);
872: if (rs.wasNull() || actual == null) {
873: if (expected != null)
874: reportFailure("Unexpected null result from '" + sql
875: + "'.");
876: } else {
877: if (expected == null)
878: reportFailure("Expected null result from '" + sql
879: + "'.");
880: else if (!actual.equals(expected))
881: reportFailure("Unexpected result from '" + sql
882: + "'. Expected " + expected.toString()
883: + " got " + actual.toString() + ".");
884: }
885: }
886:
887: ResultSet executePS() throws SQLException {
888: tsAddPS[interval].setInt(1, count);
889: setDateTime(tsAddPS[interval], 2, ts);
890: return tsAddPS[interval].executeQuery();
891: }
892: } // end of class OneAddTest
893:
894: class OneStringAddTest extends OneAddTest {
895: private final String ts;
896:
897: OneStringAddTest(int interval, int count, String ts,
898: java.sql.Timestamp expected, String expectedSQLState,
899: String expectedMsg) {
900: super (interval, count, (java.util.Date) null, expected,
901: expectedSQLState, expectedMsg);
902: this .ts = ts;
903: }
904:
905: String composeSQL() {
906: return composeSqlStr("ADD", interval,
907: String.valueOf(count), dateTimeToLiteral(ts));
908: }
909:
910: ResultSet executePS() throws SQLException {
911: tsAddPS[interval].setInt(1, count);
912: tsAddPS[interval].setString(2, ts);
913: return tsAddPS[interval].executeQuery();
914: }
915: } // end of class OneStringAddTest
916: }
|