|
1
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import org.apache.derby.tools.ij;
25
26 import java.sql.Connection  ;
27 import java.sql.PreparedStatement ;
28 import java.sql.Statement ;
29 import java.sql.ResultSet ;
30 import java.sql.SQLException ;
31 import java.sql.Timestamp ;
32 import java.sql.Types ;
33
34 import java.util.Calendar ;
35
36
51 public class timestampArith
52 {
53 private static final int FRAC_SECOND_INTERVAL = 0;
54 private static final int SECOND_INTERVAL = 1;
55 private static final int MINUTE_INTERVAL = 2;
56 private static final int HOUR_INTERVAL = 3;
57 private static final int DAY_INTERVAL = 4;
58 private static final int WEEK_INTERVAL = 5;
59 private static final int MONTH_INTERVAL = 6;
60 private static final int QUARTER_INTERVAL = 7;
61 private static final int YEAR_INTERVAL = 8;
62 private static final String [] intervalJdbcNames =
63 {"SQL_TSI_FRAC_SECOND", "SQL_TSI_SECOND", "SQL_TSI_MINUTE", "SQL_TSI_HOUR",
64 "SQL_TSI_DAY", "SQL_TSI_WEEK", "SQL_TSI_MONTH", "SQL_TSI_QUARTER", "SQL_TSI_YEAR"};
65
66 private static final int ONE_BILLION = 1000000000;
67
68 int errorCount = 0;
69 private Connection conn;
70 private PreparedStatement [] tsAddPS = new PreparedStatement [intervalJdbcNames.length];
71 private PreparedStatement [] tsDiffPS = new PreparedStatement [intervalJdbcNames.length];
72 private Statement stmt;
73 private static final String TODAY;
74 private static final String TOMORROW;
75 private static final String YEAR_FROM_TOMORROW;
76 private static final String YEAR_FROM_TODAY;
77 private static final String YESTERDAY;
78 private static final String WEEK_FROM_TODAY;
79 static {
80 Calendar cal = Calendar.getInstance();
81 while( cal.get( Calendar.HOUR) == 23 && cal.get( Calendar.MINUTE) >= 58)
84 {
85 try
86 {
87 Thread.sleep( (60 - cal.get( Calendar.SECOND))*1000);
88 }
89 catch( InterruptedException ie) {};
90 cal = Calendar.getInstance();
91 }
92 TODAY = isoFormatDate( cal);
93 cal.add( Calendar.DATE, -1);
94 YESTERDAY = isoFormatDate( cal);
95 cal.add( Calendar.DATE, 2);
96 TOMORROW = isoFormatDate( cal);
97 cal.add( Calendar.YEAR, 1);
98 YEAR_FROM_TOMORROW = isoFormatDate( cal);
99 cal.add( Calendar.DATE, -1);
100 YEAR_FROM_TODAY = isoFormatDate( cal);
101 cal.add( Calendar.YEAR, -1); cal.add( Calendar.DATE, 7);
103 WEEK_FROM_TODAY = isoFormatDate( cal);
104 }
105
106 private static String isoFormatDate( Calendar cal)
107 {
108 StringBuffer sb = new StringBuffer ();
109 String s = String.valueOf( cal.get( Calendar.YEAR));
110 for( int i = s.length(); i < 4; i++)
111 sb.append( '0');
112 sb.append( s);
113 sb.append( '-');
114
115 s = String.valueOf( cal.get( Calendar.MONTH) + 1);
116 for( int i = s.length(); i < 2; i++)
117 sb.append( '0');
118 sb.append( s);
119 sb.append( '-');
120
121 s = String.valueOf( cal.get( Calendar.DAY_OF_MONTH));
122 for( int i = s.length(); i < 2; i++)
123 sb.append( '0');
124 sb.append( s);
125
126 return sb.toString();
127 }
128
129 private final OneTest[] tests =
130 {
131 new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2005-05-10 08:25:00"), ts("2005-05-10 08:25:00.000001"), 1000,
133 null, null),
134 new OneDiffTest( SECOND_INTERVAL, ts( "2005-05-10 08:25:01"), ts("2005-05-10 08:25:00"), -1, null, null),
135 new OneDiffTest( SECOND_INTERVAL, ts( "2005-05-10 08:25:00.1"), ts("2005-05-10 08:25:00"), 0, null, null),
136 new OneDiffTest( SECOND_INTERVAL, ts( "2005-05-10 08:25:00"), ts("2005-05-10 08:26:00"), 60, null, null),
137 new OneDiffTest( MINUTE_INTERVAL, ts( "2005-05-11 08:25:00"), ts("2005-05-10 08:25:00"), -24*60, null, null),
138 new OneDiffTest( HOUR_INTERVAL, ts("2005-05-10 08:25:00"), ts( "2005-05-11 08:25:00"), 24, null, null),
139 new OneDiffTest( DAY_INTERVAL, ts("2005-05-10 08:25:00"), ts( "2005-05-11 08:25:00"), 1, null, null),
140 new OneDiffTest( DAY_INTERVAL, ts("2005-05-10 08:25:01"), ts( "2005-05-11 08:25:00"), 0, null, null),
141 new OneDiffTest( WEEK_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-03-01 08:25:00"), 0, null, null),
142 new OneDiffTest( MONTH_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-03-23 08:25:00"), 1, null, null),
143 new OneDiffTest( MONTH_INTERVAL, ts("2005-02-23 08:25:01"), ts( "2005-03-23 08:25:00"), 0, null, null),
144 new OneDiffTest( QUARTER_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-05-23 08:25:00"), 1, null, null),
145 new OneDiffTest( QUARTER_INTERVAL, ts("2005-02-23 08:25:01"), ts( "2005-05-23 08:25:00"), 0, null, null),
146 new OneDiffTest( YEAR_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-05-23 08:25:00"), 0, null, null),
147 new OneDiffTest( YEAR_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2006-02-23 08:25:00"), 1, null, null),
148
149 new OneDiffTest( FRAC_SECOND_INTERVAL, ts( TODAY + " 10:00:00.123456"), tm( "10:00:00"), -123456000, null, null),
151 new OneDiffTest( FRAC_SECOND_INTERVAL, tm( "10:00:00"), ts( TODAY + " 10:00:00.123456"), 123456000, null, null),
152 new OneDiffTest( SECOND_INTERVAL, ts( TODAY + " 10:00:00.1"), tm( "10:00:01"), 0, null, null),
153 new OneDiffTest( SECOND_INTERVAL, tm( "10:00:01"), ts( TODAY + " 10:00:00"), -1, null, null),
154 new OneDiffTest( MINUTE_INTERVAL, ts( TODAY + " 10:02:00"), tm( "10:00:00"), -2, null, null),
155 new OneDiffTest( MINUTE_INTERVAL, tm( "11:00:00"), ts( TODAY + " 10:02:00"), -58, null, null),
156 new OneDiffTest( HOUR_INTERVAL, ts( TODAY + " 10:02:00"), tm( "10:00:00"), 0, null, null),
157 new OneDiffTest( HOUR_INTERVAL, tm( "10:00:00"), ts( TODAY + " 23:02:00"), 13, null, null),
158 new OneDiffTest( DAY_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
159 new OneDiffTest( DAY_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
160 new OneDiffTest( WEEK_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
161 new OneDiffTest( WEEK_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
162 new OneDiffTest( MONTH_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
163 new OneDiffTest( MONTH_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
164 new OneDiffTest( QUARTER_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
165 new OneDiffTest( QUARTER_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
166 new OneDiffTest( YEAR_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
167 new OneDiffTest( YEAR_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
168
169 new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2004-05-10 00:00:00.123456"), dt("2004-05-10"), -123456000,
171 null, null),
172 new OneDiffTest( FRAC_SECOND_INTERVAL, dt("2004-05-10"), ts( "2004-05-10 00:00:00.123456"), 123456000,
173 null, null),
174 new OneDiffTest( SECOND_INTERVAL, ts( "2004-05-10 08:25:01"), dt("2004-05-10"), -(1+60*(25+60*8)), null, null),
175 new OneDiffTest( SECOND_INTERVAL, dt( "2004-05-10"), ts("2004-05-09 23:59:00"), -60, null, null),
176 new OneDiffTest( MINUTE_INTERVAL, ts( "2004-05-11 08:25:00"), dt("2004-05-10"), -(24*60+8*60+25), null, null),
177 new OneDiffTest( MINUTE_INTERVAL, dt("2004-05-10"), ts( "2004-05-11 08:25:00"), 24*60+8*60+25, null, null),
178 new OneDiffTest( HOUR_INTERVAL, ts("2004-02-28 08:25:00"), dt( "2004-03-01"), 39, null, null),
179 new OneDiffTest( HOUR_INTERVAL, dt( "2005-03-01"), ts("2005-02-28 08:25:00"), -15, null, null),
180 new OneDiffTest( DAY_INTERVAL, ts("2004-05-10 08:25:00"), dt( "2004-05-11"), 0, null, null),
181 new OneDiffTest( DAY_INTERVAL, dt("2004-05-10"), ts( "2004-05-11 08:25:00"), 1, null, null),
182 new OneDiffTest( WEEK_INTERVAL, ts("2004-02-23 00:00:00"), dt( "2004-03-01"), 1, null, null),
183 new OneDiffTest( WEEK_INTERVAL, dt( "2004-03-01"), ts("2004-02-23 00:00:00"), -1, null, null),
184 new OneDiffTest( MONTH_INTERVAL, ts("2004-02-23 08:25:00"), dt( "2004-03-24"), 1, null, null),
185 new OneDiffTest( MONTH_INTERVAL, dt( "2005-03-24"), ts("2004-02-23 08:25:00"), -13, null, null),
186 new OneDiffTest( QUARTER_INTERVAL, ts("2004-02-23 08:25:00"), dt( "2004-05-24"), 1, null, null),
187 new OneDiffTest( QUARTER_INTERVAL, dt( "2004-05-23"), ts("2004-02-23 08:25:01"), 0, null, null),
188 new OneDiffTest( YEAR_INTERVAL, ts("2004-02-23 08:25:00"), dt( "2004-05-23"), 0, null, null),
189 new OneDiffTest( YEAR_INTERVAL, dt( "2004-05-23"), ts("2003-02-23 08:25:00"), -1, null, null),
190
191 new OneDiffTest( FRAC_SECOND_INTERVAL, dt( TODAY), tm("00:00:01"), ONE_BILLION, null, null),
193 new OneDiffTest( FRAC_SECOND_INTERVAL, tm("00:00:02"), dt( TODAY), -2*ONE_BILLION, null, null),
194 new OneDiffTest( SECOND_INTERVAL, dt( TODAY), tm("00:00:01"), 1, null, null),
195 new OneDiffTest( SECOND_INTERVAL, tm("00:00:02"), dt( TODAY), -2, null, null),
196 new OneDiffTest( MINUTE_INTERVAL, dt( TODAY), tm("12:34:56"), 12*60 + 34, null, null),
197 new OneDiffTest( MINUTE_INTERVAL, tm("12:34:56"), dt( TODAY), -(12*60 + 34), null, null),
198 new OneDiffTest( HOUR_INTERVAL, dt( TODAY), tm("12:34:56"), 12, null, null),
199 new OneDiffTest( HOUR_INTERVAL, tm("12:34:56"), dt( TODAY), -12, null, null),
200 new OneDiffTest( DAY_INTERVAL, dt( TOMORROW), tm( "00:00:00"), -1, null, null),
201 new OneDiffTest( DAY_INTERVAL, tm( "00:00:00"), dt( TOMORROW), 1, null, null),
202 new OneDiffTest( WEEK_INTERVAL, dt( TOMORROW), tm( "00:00:00"), 0, null, null),
203 new OneDiffTest( WEEK_INTERVAL, tm( "00:00:00"), dt( TOMORROW), 0, null, null),
204 new OneDiffTest( MONTH_INTERVAL, dt( YEAR_FROM_TOMORROW), tm( "12:00:00"), -12, null, null),
205 new OneDiffTest( MONTH_INTERVAL, tm( "12:00:00"), dt( YEAR_FROM_TOMORROW), 12, null, null),
206 new OneDiffTest( QUARTER_INTERVAL, dt( YEAR_FROM_TOMORROW), tm( "12:00:00"), -4, null, null),
207 new OneDiffTest( QUARTER_INTERVAL, tm( "12:00:00"), dt( YEAR_FROM_TOMORROW), 4, null, null),
208 new OneDiffTest( YEAR_INTERVAL, dt( YEAR_FROM_TOMORROW), tm( "12:00:00"), -1, null, null),
209 new OneDiffTest( YEAR_INTERVAL, tm( "12:00:00"), dt( YEAR_FROM_TOMORROW), 1, null, null),
210
211 new OneAddTest( FRAC_SECOND_INTERVAL, 1000, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:55:00.000001"),
213 null, null),
214 new OneAddTest( FRAC_SECOND_INTERVAL, -1000, dt("2005-05-11"), ts("2005-05-10 23:59:59.999999"),
215 null, null),
216 new OneAddTest( FRAC_SECOND_INTERVAL, ONE_BILLION, tm("23:59:59"), ts( TOMORROW + " 00:00:00"), null, null),
217 new OneAddTest( SECOND_INTERVAL, 60, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:56:00"), null, null),
218 new OneAddTest( SECOND_INTERVAL, 60, dt("2005-05-11"), ts("2005-05-11 00:01:00"), null, null),
219 new OneAddTest( SECOND_INTERVAL, 60, tm("23:59:30"), ts( TOMORROW + " 00:00:30"), null, null),
220 new OneAddTest( MINUTE_INTERVAL, -1, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:54:00"), null, null),
221 new OneAddTest( MINUTE_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-11 00:01:00"), null, null),
222 new OneAddTest( MINUTE_INTERVAL, 1, tm("12:00:00"), ts( TODAY + " 12:01:00"), null, null),
223 new OneAddTest( HOUR_INTERVAL, 2, ts("2005-05-11 15:55:00"), ts("2005-05-11 17:55:00"), null, null),
224 new OneAddTest( HOUR_INTERVAL, -2, dt("2005-05-11"), ts("2005-05-10 22:00:00"), null, null),
225 new OneAddTest( HOUR_INTERVAL, 1, tm("12:00:00"), ts( TODAY + " 13:00:00"), null, null),
226 new OneAddTest( DAY_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-05-12 15:55:00"), null, null),
227 new OneAddTest( DAY_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-12 00:00:00"), null, null),
228 new OneAddTest( DAY_INTERVAL, -1, tm( "12:00:00"), ts( YESTERDAY + " 12:00:00"), null, null),
229 new OneAddTest( WEEK_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-05-18 15:55:00"), null, null),
230 new OneAddTest( WEEK_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-18 00:00:00"), null, null),
231 new OneAddTest( WEEK_INTERVAL, 1, tm("12:00:00"), ts( WEEK_FROM_TODAY + " 12:00:00"), null, null),
232 new OneAddTest( MONTH_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-06-11 15:55:00"), null, null),
233 new OneAddTest( MONTH_INTERVAL, -1, dt("2005-03-29"), ts("2005-02-28 00:00:00"), null, null),
234 new OneAddTest( MONTH_INTERVAL, 12, tm( "12:00:00"), ts( YEAR_FROM_TODAY + " 12:00:00"), null, null),
235 new OneAddTest( QUARTER_INTERVAL, 1, ts("2005-10-11 15:55:00"), ts("2006-01-11 15:55:00"), null, null),
236 new OneAddTest( QUARTER_INTERVAL, -2, dt( "2005-05-05"), ts( "2004-11-05 00:00:00"), null, null),
237 new OneAddTest( QUARTER_INTERVAL, 4, tm( "12:00:00"), ts( YEAR_FROM_TODAY + " 12:00:00"), null, null),
238 new OneAddTest( YEAR_INTERVAL, -10, ts("2005-10-11 15:55:00"), ts("1995-10-11 15:55:00"), null, null),
239 new OneAddTest( YEAR_INTERVAL, 2, dt( "2005-05-05"), ts( "2007-05-05 00:00:00"), null, null),
240 new OneAddTest( YEAR_INTERVAL, 1, tm( "12:00:00"), ts( YEAR_FROM_TODAY + " 12:00:00"), null, null),
241
242 new OneStringDiffTest( SECOND_INTERVAL, "2005-05-10 08:25:00", "2005-05-10 08:26:00", 60, null, null),
244 new OneStringAddTest( DAY_INTERVAL, 1, "2005-05-11 15:55:00", ts("2005-05-12 15:55:00"), null, null),
245
246 new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2004-05-10 00:00:00.123456"), ts( "2004-05-10 00:00:10.123456"), 0,
248 "22003", "The resulting value is outside the range for the data type INTEGER."),
249 new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2004-05-10 00:00:00.123456"), ts( "2005-05-10 00:00:00.123456"), 0,
250 "22003", "The resulting value is outside the range for the data type INTEGER."),
251 new OneDiffTest( SECOND_INTERVAL, ts( "1904-05-10 00:00:00"), ts( "2205-05-10 00:00:00"), 0,
252 "22003", "The resulting value is outside the range for the data type INTEGER."),
253 new OneAddTest( YEAR_INTERVAL, 99999, ts( "2004-05-10 00:00:00.123456"), null,
254 "22003", "The resulting value is outside the range for the data type TIMESTAMP.")
255 };
256
257 private final String [][] invalid =
258 {
259 {"values( {fn TIMESTAMPDIFF( SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})", "42X01",
260 "Syntax error: Encountered \"SECOND\" at line 1, column 28."},
261 {"values( {fn TIMESTAMPDIFF( , CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})", "42X01",
262 "Syntax error: Encountered \",\" at line 1, column 28."},
263 {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 5)})", "42X01",
264 "Syntax error: Encountered \",\" at line 1, column 80."},
265 {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, 'x')})", "42X45",
266 "CHAR is an invalid type for argument number 3 of TIMESTAMPDIFF."},
267 {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, 'x', CURRENT_TIMESTAMP)})", "42X45",
268 "CHAR is an invalid type for argument number 2 of TIMESTAMPDIFF."},
269 {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP)})", "42X01",
270 "Syntax error: Encountered \")\" at line 1, column 61."},
271 {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND)})", "42X01",
272 "Syntax error: Encountered \")\" at line 1, column 42."},
273 {"values( {fn TIMESTAMPADD( x, 1, CURRENT_TIMESTAMP)})", "42X01",
274 "Syntax error: Encountered \"x\" at line 1, column 27."},
275 {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, CURRENT_DATE, CURRENT_TIMESTAMP)})", "42X45",
276 "DATE is an invalid type for argument number 2 of TIMESTAMPADD."},
277 {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 'XX', CURRENT_TIMESTAMP)})", "42X45",
278 "CHAR is an invalid type for argument number 2 of TIMESTAMPADD."},
279 {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1.1, CURRENT_TIMESTAMP)})", "42X45",
280 "DECIMAL is an invalid type for argument number 2 of TIMESTAMPADD."},
281 {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1, 2.1)})", "42X45",
282 "DECIMAL is an invalid type for argument number 3 of TIMESTAMPADD."},
283 {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1, 'XX')})", "42X45",
284 "CHAR is an invalid type for argument number 3 of TIMESTAMPADD."},
285 {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1)})", "42X01",
286 "Syntax error: Encountered \")\" at line 1, column 44."},
287 {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND)})", "42X01",
288 "Syntax error: Encountered \")\" at line 1, column 41."}
289 };
290
291 private static java.sql.Timestamp ts( String s)
292 {
293 if( s.length() < 29)
295 {
296 StringBuffer sb = new StringBuffer ( s);
298 if( s.length() == 19)
299 sb.append( '.');
300 while( sb.length() < 29)
301 sb.append( '0');
302 s = sb.toString();
303 }
304 try
305 {
306 return java.sql.Timestamp.valueOf( s);
307 }
308 catch( Exception e)
309 {
310 System.out.println( s + " is not a proper timestamp string.");
311 System.out.println( e.getClass().getName() + ": " + e.getMessage());
312 e.printStackTrace();
313 System.exit(1);
314 return null;
315 }
316 }
317
318 private static java.sql.Date dt( String s)
319 {
320 return java.sql.Date.valueOf( s);
321 }
322
323 private static java.sql.Time tm( String s)
324 {
325 return java.sql.Time.valueOf( s);
326 }
327
328 private static String dateTimeToLiteral( Object ts)
329 {
330 if( ts instanceof java.sql.Timestamp )
331 return "{ts '" + ((java.sql.Timestamp )ts).toString() + "'}";
332 else if( ts instanceof java.sql.Time )
333 return "{t '" + ((java.sql.Time )ts).toString() + "'}";
334 else if( ts instanceof java.sql.Date )
335 return "{d '" + ((java.sql.Date )ts).toString() + "'}";
336 else if( ts instanceof String )
337 return "TIMESTAMP( '" + ((String ) ts) + "')";
338 else
339 return ts.toString();
340 }
341
342 public static void main( String [] args)
343 {
344 System.out.println("Test timestamp arithmetic starting.");
345 try
346 {
347 timestampArith tester = new timestampArith( args);
348 tester.doIt();
349 if( tester.errorCount == 0)
350 System.out.println( "PASSED.");
351 else if( tester.errorCount == 1)
352 System.out.println( "FAILED. 1 error.");
353 else
354 System.out.println( "FAILED. " + tester.errorCount + " errors.");
355 }
356 catch( SQLException sqle)
357 {
358 reportSQLException( sqle);
359 System.exit(1);
360 }
361 catch( Exception e)
362 {
363 System.out.println("Unexpected exception: " + e.getMessage());
364 e.printStackTrace();
365 System.exit(1);
366 }
367 System.exit(0);
368 }
370 String composeSqlStr( String fn, int interval, String parm1, String parm2)
371 {
372 return "values( {fn TIMESTAMP" + fn + "( " + intervalJdbcNames[interval] +
373 ", " + parm1 + "," + parm2 + ")})";
374 }
375
376 private timestampArith( String [] args) throws Exception
377 {
378 ij.getPropertyArg(args);
380 conn = ij.startJBMS();
381
382 conn.setAutoCommit(false);
383 for( int i = 0; i < intervalJdbcNames.length; i++)
384 {
385 tsAddPS[i] = conn.prepareStatement( composeSqlStr( "ADD", i, "?", "?"));
386 tsDiffPS[i] = conn.prepareStatement( composeSqlStr( "DIFF", i, "?", "?"));
387 }
388 stmt = conn.createStatement();
389 }
390
391 private void doIt() throws SQLException
392 {
393 for( int i = 0; i < tests.length; i++)
394 tests[i].runTest();
395
396 testNullInputs();
397
398 for( int i = 0; i < invalid.length; i++)
399 {
400 try
401 {
402 ResultSet rs = stmt.executeQuery( invalid[i][0]);
403 rs.next();
404 reportFailure( "\"" + invalid[i][0] + "\" did not throw an exception.");
405 }
406 catch( SQLException sqle)
407 {
408 checkExpectedException( sqle, invalid[i][1], invalid[i][2], "\"" + invalid[i][0] + "\"");
409 }
410 }
411
412 testInvalidArgTypes();
413 }
415 private void testInvalidArgTypes() throws SQLException
416 {
417 expectException( tsDiffPS[ HOUR_INTERVAL], ts( "2005-05-11 15:26:00"), new Double ( 2.0), "XCL12",
418 "An attempt was made to put a data value of type 'double' into a data value of type 'TIMESTAMP'.",
419 "TIMESTAMPDIFF with double ts2");
420 expectException( tsDiffPS[ HOUR_INTERVAL], new Double ( 2.0), ts( "2005-05-11 15:26:00"), "XCL12",
421 "An attempt was made to put a data value of type 'double' into a data value of type 'TIMESTAMP'.",
422 "TIMESTAMPDIFF with double ts1");
423
424 expectException( tsAddPS[ MINUTE_INTERVAL], new Integer (1), new Integer (-1), "XCL12",
425 "An attempt was made to put a data value of type 'int' into a data value of type 'TIMESTAMP'.",
426 "TIMESTAMPADD with int ts");
427 expectException( tsAddPS[ MINUTE_INTERVAL], ts( "2005-05-11 15:26:00"), ts( "2005-05-11 15:26:00"), "XCL12",
428 "An attempt was made to put a data value of type 'java.sql.Timestamp' into a data value of type 'INTEGER'.",
429 "TIMESTAMPADD with timestamp count");
430 }
432 private void expectException( PreparedStatement ps, Object arg1, Object arg2,
433 String expectedSQLState, String expectedMsg, String label)
434 {
435 try
436 {
437 ps.setObject( 1, arg1);
438 ps.setObject( 2, arg2);
439 ResultSet rs = ps.executeQuery();
440 rs.next();
441 reportFailure( label + " did not throw an exception.");
442 }
443 catch( SQLException sqle) { checkExpectedException( sqle, expectedSQLState, expectedMsg, label);};
444 }
446 private void checkExpectedException( SQLException sqle, String expectedSQLState, String expectedMsg, String label)
447 {
448 if( ! expectedSQLState.equals( sqle.getSQLState()))
449 reportFailure( "Unexpected SQLState from \"" + label + "\". expected " +
450 expectedSQLState + " got " + sqle.getSQLState());
451 else if( expectedMsg != null && ! expectedMsg.equals( sqle.getMessage()))
452 reportFailure( "Unexpected message from \"" + label + "\".\n expected \"" +
453 expectedMsg + "\"\n got \"" + sqle.getMessage() + "\"");
454 }
456 private void testNullInputs() throws SQLException
457 {
458 tsDiffPS[ HOUR_INTERVAL].setTimestamp( 1, ts( "2005-05-11 15:26:00"));
460 tsDiffPS[ HOUR_INTERVAL].setNull( 2, Types.TIMESTAMP);
461 expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null timestamp in third argument");
462 tsDiffPS[ HOUR_INTERVAL].setNull( 2, Types.DATE);
463 expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null date in third argument");
464
465 tsDiffPS[ HOUR_INTERVAL].setTimestamp( 2, ts( "2005-05-11 15:26:00"));
466 tsDiffPS[ HOUR_INTERVAL].setNull( 1, Types.TIMESTAMP);
467 expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null timestamp in second argument");
468 tsDiffPS[ HOUR_INTERVAL].setNull( 1, Types.DATE);
469 expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null date in second argument");
470
471 tsAddPS[ MINUTE_INTERVAL].setTimestamp( 2, ts( "2005-05-11 15:26:00"));
472 tsAddPS[ MINUTE_INTERVAL].setNull( 1, Types.INTEGER);
473 expectNullResult( tsAddPS[ MINUTE_INTERVAL], "TIMESTAMPADD with null integer in second argument");
474
475 tsAddPS[ MINUTE_INTERVAL].setInt( 1, 1);
476 tsAddPS[ MINUTE_INTERVAL].setNull( 2, Types.TIMESTAMP);
477 expectNullResult( tsAddPS[ MINUTE_INTERVAL], "TIMESTAMPADD with null timestamp in third argument");
478 tsAddPS[ MINUTE_INTERVAL].setNull( 2, Types.DATE);
479 expectNullResult( tsAddPS[ MINUTE_INTERVAL], "TIMESTAMPADD with null date in third argument");
480 }
482 private void expectNullResult( PreparedStatement ps, String label)
483 {
484 try
485 {
486 ResultSet rs = ps.executeQuery();
487 if( ! rs.next())
488 reportFailure( label + " returned no rows.");
489 else if( rs.getObject( 1) != null)
490 reportFailure( label + " did not return null.");
491 }
492 catch (SQLException sqle)
493 {
494 reportFailure( "Unexpected exception from " + label);
495 reportSQLException( sqle);
496 }
497 }
499 private static void reportSQLException( SQLException sqle)
500 {
501 System.out.println("Unexpected exception:");
502 for(;;)
503 {
504 System.out.println( " " + sqle.getMessage());
505 if( sqle.getNextException() != null)
506 sqle = sqle.getNextException();
507 else
508 break;
509 }
510 sqle.printStackTrace();
511 }
513 private void reportFailure( String msg)
514 {
515 errorCount++;
516 System.out.println( msg);
517 }
518
519 private static void setDateTime( PreparedStatement ps, int parameterIdx, java.util.Date dateTime)
520 throws SQLException
521 {
522 if( dateTime instanceof java.sql.Timestamp )
523 ps.setTimestamp( parameterIdx, (java.sql.Timestamp ) dateTime);
524 else if( dateTime instanceof java.sql.Date )
525 ps.setDate( parameterIdx, (java.sql.Date ) dateTime);
526 else if( dateTime instanceof java.sql.Time )
527 ps.setTime( parameterIdx, (java.sql.Time ) dateTime);
528 else
529 ps.setTimestamp( parameterIdx, (java.sql.Timestamp ) dateTime);
530 }
531
532 abstract class OneTest
533 {
534 final int interval; final String expectedSQLState; final String expectedMsg; String sql;
538
539 OneTest( int interval, String expectedSQLState, String expectedMsg)
540 {
541 this.interval = interval;
542 this.expectedSQLState = expectedSQLState;
543 this.expectedMsg = expectedMsg;
544 }
545
546 void runTest()
547 {
548 sql = composeSQL();
549 ResultSet rs = null;
550 try
551 {
552 rs = stmt.executeQuery( sql);
553 checkResultSet( rs, sql);
554 if( expectedSQLState != null)
555 reportFailure( "Statement '" + sql + "' did not generate an exception");
556 }
557 catch( SQLException sqle)
558 {
559 checkSQLException( "Statement", sqle);
560 }
561 if( rs != null)
562 {
563 try
564 {
565 rs.close();
566 }
567 catch( SQLException sqle){};
568 rs = null;
569 }
570
571 try
572 {
573 rs = executePS();
574 checkResultSet( rs, sql);
575 if( expectedSQLState != null)
576 reportFailure( "PreparedStatement '" + sql + "' did not generate an exception");
577 }
578 catch( SQLException sqle)
579 {
580 checkSQLException( "PreparedStatement", sqle);
581 }
582 if( rs != null)
583 {
584 try
585 {
586 rs.close();
587 }
588 catch( SQLException sqle){};
589 rs = null;
590 }
591 }
593 private void checkResultSet( ResultSet rs, String sql) throws SQLException
594 {
595 if( rs.next())
596 {
597 checkResultRow( rs, sql);
598 if( rs.next())
599 reportFailure( "'" + sql + "' returned more than one row.");
600 }
601 else
602 reportFailure( "'" + sql + "' did not return any rows.");
603 }
605 private void checkSQLException( String type, SQLException sqle)
606 {
607 if( expectedSQLState != null)
608 {
609 if( ! expectedSQLState.equals( sqle.getSQLState()))
610 reportFailure( "Incorrect SQLState from " + type + " '" + sql + "' expected " + expectedSQLState +
611 " got " + sqle.getSQLState());
612 else if( expectedMsg != null && ! expectedMsg.equals( sqle.getMessage()))
613 reportFailure( "Incorrect exception message from " + type + " '" + sql + "' expected '" + expectedMsg +
614 "' got '" + sqle.getMessage() + "'");
615 }
616 else
617 {
618 reportFailure( "Unexpected exception from " + type + " '" + sql + "'");
619 reportSQLException( sqle);
620 }
621 }
623 abstract String composeSQL();
624
625 abstract void checkResultRow( ResultSet rs, String sql) throws SQLException ;
626
627 abstract ResultSet executePS() throws SQLException ;
628 }
629
630 class OneDiffTest extends OneTest
631 {
632 private final java.util.Date ts1;
633 private final java.util.Date ts2;
634 final int expectedDiff;
635 protected boolean expectNull;
636
637 OneDiffTest( int interval,
638 java.util.Date ts1,
639 java.util.Date ts2,
640 int expectedDiff,
641 String expectedSQLState,
642 String expectedMsg)
643 {
644 super( interval, expectedSQLState, expectedMsg);
645 this.ts1 = ts1;
646 this.ts2 = ts2;
647 this.expectedDiff = expectedDiff;
648 expectNull = (ts1 == null) || (ts2 == null);
649 }
650
651 String composeSQL()
652 {
653 return composeSqlStr( "DIFF", interval, dateTimeToLiteral( ts1), dateTimeToLiteral( ts2));
654 }
655
656 void checkResultRow( ResultSet rs, String sql) throws SQLException
657 {
658 int actualDiff = rs.getInt(1);
659 if( rs.wasNull())
660 {
661 if( !expectNull)
662 reportFailure( "Unexpected null result from '" + sql + "'.");
663 }
664 else
665 {
666 if( expectNull)
667 reportFailure( "Expected null result from '" + sql + "'.");
668 else if( actualDiff != expectedDiff)
669 reportFailure( "Unexpected result from '" + sql + "'. Expected " +
670 expectedDiff + " got " + actualDiff + ".");
671 }
672 }
673
674 ResultSet executePS() throws SQLException
675 {
676 setDateTime( tsDiffPS[interval], 1, ts1);
677 setDateTime( tsDiffPS[interval], 2, ts2);
678 return tsDiffPS[interval].executeQuery();
679 }
680 }
682 class OneStringDiffTest extends OneDiffTest
683 {
684 private final String ts1;
685 private final String ts2;
686
687 OneStringDiffTest( int interval,
688 String ts1,
689 String ts2,
690 int expectedDiff,
691 String expectedSQLState,
692 String expectedMsg)
693 {
694 super( interval, (java.util.Date ) null, (java.util.Date ) null, expectedDiff, expectedSQLState, expectedMsg);
695 this.ts1 = ts1;
696 this.ts2 = ts2;
697 expectNull = (ts1 == null) || (ts2 == null);
698 }
699
700 String composeSQL()
701 {
702 return composeSqlStr( "DIFF", interval, dateTimeToLiteral( ts1), dateTimeToLiteral( ts2));
703 }
704
705 ResultSet executePS() throws SQLException
706 {
707 tsDiffPS[interval].setString( 1, ts1);
708 tsDiffPS[interval].setString( 2, ts2);
709 return tsDiffPS[interval].executeQuery();
710 }
711 }
713 class OneAddTest extends OneTest
714 {
715 private final java.util.Date ts;
716 final int count;
717 final java.sql.Timestamp expected;
718
719 OneAddTest( int interval,
720 int count,
721 java.util.Date ts,
722 java.sql.Timestamp expected,
723 String expectedSQLState,
724 String expectedMsg)
725 {
726 super( interval, expectedSQLState, expectedMsg);
727 this.count = count;
728 this.ts = ts;
729 this.expected = expected;
730 }
731
732 String composeSQL()
733 {
734 return composeSqlStr( "ADD", interval, String.valueOf( count), dateTimeToLiteral( ts));
735 }
736
737 void checkResultRow( ResultSet rs, String sql) throws SQLException
738 {
739 java.sql.Timestamp actual = rs.getTimestamp( 1);
740 if( rs.wasNull() || actual == null)
741 {
742 if( expected != null)
743 reportFailure( "Unexpected null result from '" + sql + "'.");
744 }
745 else
746 {
747 if( expected == null)
748 reportFailure( "Expected null result from '" + sql + "'.");
749 else if( ! actual.equals( expected))
750 reportFailure( "Unexpected result from '" + sql + "'. Expected " +
751 expected.toString() + " got " + actual.toString() + ".");
752 }
753 }
754
755 ResultSet executePS() throws SQLException
756 {
757 tsAddPS[interval].setInt( 1, count);
758 setDateTime( tsAddPS[interval], 2, ts);
759 return tsAddPS[interval].executeQuery();
760 }
761 }
763 class OneStringAddTest extends OneAddTest
764 {
765 private final String ts;
766
767 OneStringAddTest( int interval,
768 int count,
769 String ts,
770 java.sql.Timestamp expected,
771 String expectedSQLState,
772 String expectedMsg)
773 {
774 super( interval, count, (java.util.Date ) null, expected, expectedSQLState, expectedMsg);
775 this.ts = ts;
776 }
777
778 String composeSQL()
779 {
780 return composeSqlStr( "ADD", interval, String.valueOf( count), dateTimeToLiteral( ts));
781 }
782
783 ResultSet executePS() throws SQLException
784 {
785 tsAddPS[interval].setInt( 1, count);
786 tsAddPS[interval].setString( 2, ts);
787 return tsAddPS[interval].executeQuery();
788 }
789 } }
791 |
Popular Tags |
Java API By Example, From Geeks To Geeks.