|
1
21
22 package org.apache.derbyTesting.functionTests.tests.lang;
23
24 import java.sql.CallableStatement  ;
25 import java.sql.Connection ;
26 import java.sql.DatabaseMetaData ;
27 import java.sql.DriverManager ;
28 import java.sql.PreparedStatement ;
29 import java.sql.ResultSet ;
30 import java.sql.ResultSetMetaData ;
31 import java.sql.Statement ;
32 import java.sql.SQLException ;
33 import java.sql.SQLWarning ;
34
35 import org.apache.derby.tools.ij;
36 import org.apache.derby.tools.JDBCDisplayUtil;
37 import org.apache.derby.iapi.services.info.JVMInfo;
38 import org.apache.derbyTesting.functionTests.util.BigDecimalHandler;
39 import org.apache.derbyTesting.functionTests.util.TestUtil;
40
41 import java.math.BigDecimal ;
42 import java.sql.Array ;
43 import java.sql.Blob ;
44 import java.sql.Clob ;
45 import java.sql.Date ;
46 import java.sql.Time ;
47 import java.sql.Timestamp ;
48
49
52 public class updatableResultSet {
53
54 private static boolean HAVE_BIG_DECIMAL;
55
56 static{
57 if(BigDecimalHandler.representation != BigDecimalHandler.BIGDECIMAL_REPRESENTATION)
58 HAVE_BIG_DECIMAL = false;
59 else
60 HAVE_BIG_DECIMAL = true;
61 }
62
63 private static Connection conn;
64 private static DatabaseMetaData dbmt;
65 private static Statement stmt, stmt1;
66 private static ResultSet rs, rs1;
67 private static PreparedStatement pStmt = null;
68 private static CallableStatement callStmt = null;
69 static SQLWarning warnings;
70
71 private static String [] allSQLTypes =
73 {
74 "SMALLINT",
75 "INTEGER",
76 "BIGINT",
77 "DECIMAL(10,5)",
78 "REAL",
79 "DOUBLE",
80 "CHAR(60)",
81 "VARCHAR(60)",
82 "LONG VARCHAR",
83 "CHAR(2) FOR BIT DATA",
84 "VARCHAR(2) FOR BIT DATA",
85 "LONG VARCHAR FOR BIT DATA",
86 "CLOB(1k)",
87 "DATE",
88 "TIME",
89 "TIMESTAMP",
90 "BLOB(1k)",
91
92 };
93
94 private static String [] ColumnNames =
96 {
97 "SMALLINTCOL",
98 "INTEGERCOL",
99 "BIGINTCOL",
100 "DECIMALCOL",
101 "REALCOL",
102 "DOUBLECOL",
103 "CHARCOL",
104 "VARCHARCOL",
105 "LONGVARCHARCOL",
106 "CHARFORBITCOL",
107 "VARCHARFORBITCOL",
108 "LVARCHARFORBITCOL",
109 "CLOBCOL",
110 "DATECOL",
111 "TIMECOL",
112 "TIMESTAMPCOL",
113 "BLOBCOL",
114
115 };
116
117 private static String [][]SQLData =
119 {
120 {"11","22"}, {"111","1111"}, {"22","222"}, {"3.3","3.33"}, {"4.4","4.44"}, {"5.5","5.55"}, {"'1992-01-06'","'1992'"}, {"'1992-01-07'","'1992'"}, {"'1992-01-08'","'1992'"}, {"X'10'","X'10aa'"}, {"X'10'","X'10bb'"}, {"X'10'","X'10cc'"}, {"'13'","'14'"}, {"'2000-01-01'","'2000-01-01'"}, {"'15:30:20'","'15:30:20'"}, {"'2000-01-01 15:30:20'","'2000-01-01 15:30:20'"}, {"X'1020'","X'10203040'"} };
138
139 private static String [] allUpdateXXXNames =
141 {
142 "updateShort",
143 "updateInt",
144 "updateLong",
145 "updateBigDecimal",
146 "updateFloat",
147 "updateDouble",
148 "updateString",
149 "updateAsciiStream",
150 "updateCharacterStream",
151 "updateByte",
152 "updateBytes",
153 "updateBinaryStream",
154 "updateClob",
155 "updateDate",
156 "updateTime",
157 "updateTimestamp",
158 "updateBlob",
159 "updateBoolean",
160 "updateNull",
161 "updateArray",
162 "updateRef"
163
164 };
165
166
167 public static final String [][] updateXXXRulesTableForEmbedded = {
169
170 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
191 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
192 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
193 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
194 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
195 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
196 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
197 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
198 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
199 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
200 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
201 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
202 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
203 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
204 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
205 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
206 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "PASS", "ERROR", "ERROR" },
207
208 };
209
210 public static final String [][] updateXXXRulesTableForNetworkServer = {
212
213 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
234 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
235 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
236 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
237 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
238 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
239 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
240 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
241 { "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "PASS", "PASS", "ERROR", "ERROR" },
242 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
243 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
244 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
245 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
246 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
247 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
248 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "PASS", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
249 { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "PASS", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "PASS", "ERROR", "ERROR" },
250
251 };
252
253 public static void main(String [] args) {
254 System.out.println("Start testing delete and update using JDBC2.0 updateable resultset apis");
255
256 try {
257 ij.getPropertyArg(args);
260 conn = ij.startJBMS();
261
262 setup(true);
263
264 System.out.println("Negative Testl - request for scroll insensitive updatable resultset will give a read only scroll insensitive resultset");
265 System.out.println("This test has been removed because scrollable " +
266 "insensitive updatable result sets have been " +
267 "implemented.");
268
269 System.out.println("Negative Test2 - request for scroll sensitive " +
270 "updatable resultset will give an updatable " +
271 "scroll insensitive resultset");
272 stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
273 JDBCDisplayUtil.ShowWarnings(System.out, conn);
274 System.out.println("requested TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE but that is not supported");
275 System.out.println("Jira issue Derby-154 : When client connects to Network Server using JCC, it incorrectly shows support for scroll sensitive updatable resultsets");
276 System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " + (stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE));
277 System.out.println("Make sure that we got CONCUR_UPDATABLE? " +
278 (stmt.getResultSetConcurrency() ==
279 ResultSet.CONCUR_UPDATABLE));
280 System.out.println("Rest of the test removed because scrollable " +
281 "insensitive updatable result sets have been " +
282 "implemented.");
283
284 System.out.println("Negative Test3 - request a read only resultset and attempt deleteRow and updateRow on it");
285 stmt = conn.createStatement(); rs = stmt.executeQuery("select * from t1");
287 System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
288 rs.next();
289 System.out.println("Now attempting to send a deleteRow on a read only resultset.");
290 try {
291 rs.deleteRow();
292 System.out.println("FAIL!!! deleteRow should have failed because this is a read only resultset");
293 }
294 catch (SQLException e) {
295 System.out.println("SQL State : " + e.getSQLState());
296 System.out.println("Got expected exception " + e.getMessage());
297 }
298 System.out.println("Now attempting to send an updateRow on a read only resultset.");
299 try {
300 rs.updateRow();
301 System.out.println("FAIL!!! updateRow should have failed because this is a read only resultset");
302 }
303 catch (SQLException e) {
304 System.out.println("SQL State : " + e.getSQLState());
305 System.out.println("Got expected exception " + e.getMessage());
306 }
307 rs.close();
309
310 System.out.println("Negative Test4 - request a read only resultset and send a sql with FOR UPDATE clause and attempt deleteRow/updateRow on it");
311 stmt = conn.createStatement(); rs = stmt.executeQuery("select * from t1 FOR UPDATE");
313 System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
314 rs.next();
315 System.out.println("Now attempting to send a deleteRow on a read only resultset with FOR UPDATE clause in the SELECT sql.");
316 try {
317 rs.deleteRow();
318 System.out.println("FAIL!!! deleteRow should have failed because this is a read only resultset");
319 }
320 catch (SQLException e) {
321 System.out.println("SQL State : " + e.getSQLState());
322 System.out.println("Got expected exception " + e.getMessage());
323 }
324 System.out.println("Now attempting to send a updateRow on a read only resultset with FOR UPDATE clause in the SELECT sql.");
325 try {
326 rs.updateRow();
327 System.out.println("FAIL!!! updateRow should have failed because this is a read only resultset");
328 }
329 catch (SQLException e) {
330 System.out.println("SQL State : " + e.getSQLState());
331 System.out.println("Got expected exception " + e.getMessage());
332 }
333 rs.close();
335
336 System.out.println("Negative Test5 - request resultset with no FOR UPDATE clause and CONCUR_READ_ONLY");
337 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
338 rs = stmt.executeQuery("select * from t1"); System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
340
341 System.out.println("Now attempting to send a delete on a sql with no FOR UPDATE clause and CONCUR_READ_ONLY.");
342 try {
343 rs.deleteRow();
344 System.out.println("FAIL!!! deleteRow should have failed on sql with no FOR UPDATE clause and CONCUR_READ_ONLY.");
345 }
346 catch (SQLException e) {
347 System.out.println("SQL State : " + e.getSQLState());
348 System.out.println("Got expected exception " + e.getMessage());
349 }
350 System.out.println("Now attempting to send a updateRow on a sql with no FOR UPDATE clause and CONCUR_READ_ONLY.");
351 try {
352 rs.updateRow();
353 System.out.println("FAIL!!! updateRow should have failed on sql with no FOR UPDATE clause and CONCUR_READ_ONLY.");
354 }
355 catch (SQLException e) {
356 System.out.println("SQL State : " + e.getSQLState());
357 System.out.println("Got expected exception " + e.getMessage());
358 }
359 rs.close();
361
362 System.out.println("Negative Test6 - request updatable resultset for sql with FOR READ ONLY clause");
363 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
364 rs = stmt.executeQuery("select * from t1 FOR READ ONLY");
365 System.out.println("Make sure that we got CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
366 System.out.println("Jira issue Derby-159 : Warnings raised by Derby are not getting passed to the Client in Network Server Mode");
367 JDBCDisplayUtil.ShowWarnings(System.out, rs);
368 rs.next();
369 System.out.println("Now attempting to send a delete on a sql with FOR READ ONLY clause.");
370 try {
371 rs.deleteRow();
372 System.out.println("FAIL!!! deleteRow should have failed on sql with FOR READ ONLY clause");
373 }
374 catch (SQLException e) {
375 System.out.println("SQL State : " + e.getSQLState());
376 System.out.println("Got expected exception " + e.getMessage());
377 }
378 System.out.println("Now attempting to send a updateRow on a sql with FOR READ ONLY clause.");
379 try {
380 rs.updateRow();
381 System.out.println("FAIL!!! updateRow should have failed on sql with FOR READ ONLY clause");
382 }
383 catch (SQLException e) {
384 System.out.println("SQL State : " + e.getSQLState());
385 System.out.println("Got expected exception " + e.getMessage());
386 }
387 rs.close();
389
390 System.out.println("Negative Test7 - attempt to deleteRow & updateRow on updatable resultset when the resultset is not positioned on a row");
391 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
392 rs = stmt.executeQuery("SELECT * FROM t1");
393 System.out.println("Make sure that we got CONCUR_UPDATABLE? " + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
394 System.out.println("Now attempt a deleteRow without first doing next on the resultset.");
395 try {
396 rs.deleteRow();
397 System.out.println("FAIL!!! deleteRow should have failed because resultset is not on a row");
398 }
399 catch (SQLException e) {
400 System.out.println("SQL State : " + e.getSQLState());
401 System.out.println("Got expected exception " + e.getMessage());
402 }
403 System.out.println("Now attempt a updateRow without first doing next on the resultset.");
404 System.out.println("updateRow will check if it is on a row or not even " +
405 "though no changes have been made to the row using updateXXX");
406 try {
407 rs.updateRow();
408 System.out.println("FAIL!!! updateRow should have failed because " +
409 "resultset is not on a row");
410 }
411 catch (SQLException e) {
412 System.out.println("SQL State : " + e.getSQLState());
413 System.out.println("Got expected exception " + e.getMessage());
414 }
415 while (rs.next()); System.out.println("ResultSet is positioned after the last row. attempt to deleteRow at this point should fail!");
417 try {
418 rs.deleteRow();
419 System.out.println("FAIL!!! deleteRow should have failed because resultset is after the last row");
420 }
421 catch (SQLException e) {
422 System.out.println("SQL State : " + e.getSQLState());
423 System.out.println("Got expected exception " + e.getMessage());
424 }
425 System.out.println("ResultSet is positioned after the last row. attempt to updateRow at this point should fail!");
426 try {
427 rs.updateRow();
428 System.out.println("FAIL!!! updateRow should have failed because resultset is after the last row");
429 }
430 catch (SQLException e) {
431 System.out.println("SQL State : " + e.getSQLState());
432 System.out.println("Got expected exception " + e.getMessage());
433 }
434 rs.close();
435
436 System.out.println("Negative Test8 - attempt deleteRow & updateRow on updatable resultset after closing the resultset");
437 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
438 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
439 System.out.println("Make sure that we got CONCUR_UPDATABLE? " + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
440 rs.next();
441 rs.close();
442 try {
443 rs.deleteRow();
444 System.out.println("FAIL!!! deleteRow should have failed because resultset is closed");
445 }
446 catch (SQLException e) {
447 System.out.println("SQL State : " + e.getSQLState());
448 System.out.println("Got expected exception " + e.getMessage());
449 }
450 try {
451 rs.updateRow();
452 System.out.println("FAIL!!! updateRow should have failed because resultset is closed");
453 }
454 catch (SQLException e) {
455 System.out.println("SQL State : " + e.getSQLState());
456 System.out.println("Got expected exception " + e.getMessage());
457 }
458
459 System.out.println("Negative Test9 - try updatable resultset on system table");
460 try {
461 rs = stmt.executeQuery("SELECT * FROM sys.systables FOR UPDATE");
462 System.out.println("FAIL!!! trying to open an updatable resultset on a system table should have failed because system tables can't be updated by a user");
463 }
464 catch (SQLException e) {
465 System.out.println("SQL State : " + e.getSQLState());
466 System.out.println("Got expected exception " + e.getMessage());
467 }
468
469 System.out.println("Negative Test10 - try updatable resultset on a view");
470 try {
471 rs = stmt.executeQuery("SELECT * FROM v1 FOR UPDATE");
472 System.out.println("FAIL!!! trying to open an updatable resultset on a view should have failed because Derby doesnot support updates to views yet");
473 }
474 catch (SQLException e) {
475 System.out.println("SQL State : " + e.getSQLState());
476 System.out.println("Got expected exception " + e.getMessage());
477 }
478 stmt.executeUpdate("drop view v1");
479
480 System.out.println("Negative Test11 - attempt to open updatable resultset when there is join in the select query should fail");
481 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
482 try {
483 rs = stmt.executeQuery("SELECT c1 FROM t1,t2 where t1.c1 = t2.c21 FOR UPDATE");
484 System.out.println("FAIL!!! trying to open an updatable resultset should have failed because updatable resultset donot support join in the select query");
485 }
486 catch (SQLException e) {
487 System.out.println("SQL State : " + e.getSQLState());
488 System.out.println("Got expected exception " + e.getMessage());
489 }
490
491 System.out.println("Negative Test12 - With autocommit on, attempt to drop a table when there is an open updatable resultset on it");
492 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
493 rs = stmt.executeQuery("SELECT c1 FROM t1 FOR UPDATE");
494 rs.next();
495 rs.updateInt(1,123);
496 System.out.println("Opened an updatable resultset. Now trying to drop that table through another Statement");
497 stmt1 = conn.createStatement();
498 try {
499 stmt1.executeUpdate("drop table t1");
500 System.out.println("FAIL!!! drop table should have failed because the updatable resultset is still open");
501 }
502 catch (SQLException e) {
503 System.out.println("SQL State : " + e.getSQLState());
504 System.out.println("Got expected exception " + e.getMessage());
505 }
506 System.out.println("Since autocommit is on, the drop table exception resulted in a runtime rollback causing updatable resultset object to close");
507 try {
508 rs.updateRow();
509 System.out.println("FAIL!!! resultset should have been closed at this point and updateRow should have failed");
510 }
511 catch (SQLException e) {
512 System.out.println("SQL State : " + e.getSQLState());
513 System.out.println("Got expected exception " + e.getMessage());
514 }
515 try {
516 rs.deleteRow();
517 System.out.println("FAIL!!! resultset should have been closed at this point and deleteRow should have failed");
518 }
519 catch (SQLException e) {
520 System.out.println("SQL State : " + e.getSQLState());
521 System.out.println("Got expected exception " + e.getMessage());
522 }
523
524 System.out.println("Negative Test13 - foreign key constraint failure will cause deleteRow to fail");
525 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
526 rs = stmt.executeQuery("SELECT * FROM tableWithPrimaryKey FOR UPDATE");
527 rs.next();
528 try {
529 rs.deleteRow();
530 System.out.println("FAIL!!! deleteRow should have failed because it will cause foreign key constraint failure");
531 }
532 catch (SQLException e) {
533 System.out.println("SQL State : " + e.getSQLState());
534 System.out.println("Got expected exception " + e.getMessage());
535 }
536 System.out.println("Since autocommit is on, the constraint exception resulted in a runtime rollback causing updatable resultset object to close");
537 try {
538 rs.next();
539 if (TestUtil.isNetFramework())
540 System.out.println("Jira entry Derby-160 : for Network Server because next should have failed");
541 System.out.println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback");
542 }
543 catch (SQLException e) {
544 System.out.println("SQL State : " + e.getSQLState());
545 System.out.println("Got expected exception " + e.getMessage());
546 }
547
548 System.out.println("Negative Test14 - foreign key constraint failure will cause updateRow to fail");
549 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
550 rs = stmt.executeQuery("SELECT c1, c2 FROM tableWithPrimaryKey FOR UPDATE");
551 rs.next();
552 rs.updateInt(1,11);
553 rs.updateInt(2,22);
554 try {
555 rs.updateRow();
556 System.out.println("FAIL!!! updateRow should have failed because it will cause foreign key constraint failure");
557 }
558 catch (SQLException e) {
559 System.out.println("SQL State : " + e.getSQLState());
560 System.out.println("Got expected exception " + e.getMessage());
561 }
562 System.out.println("Since autocommit is on, the constraint exception resulted in a runtime rollback causing updatable resultset object to close");
563 try {
564 rs.next();
565 if (TestUtil.isNetFramework())
566 System.out.println("Jira entry Derby-160 : for Network Server because next should have failed");
567 System.out.println("FAIL!!! next should have failed because foreign key constraint failure resulted in a runtime rollback");
568 }
569 catch (SQLException e) {
570 System.out.println("SQL State : " + e.getSQLState());
571 System.out.println("Got expected exception " + e.getMessage());
572 }
573
574 System.out.println("Negative Test15 - Can't call updateXXX methods on columns that do not correspond to a column in the table");
575 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
576 rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
577 rs.next();
578 try {
579 rs.updateInt(1,22);
580 System.out.println("FAIL!!! updateInt should have failed because it is trying to update a column that does not correspond to column in base table");
581 }
582 catch (SQLException e) {
583 System.out.println("SQL State : " + e.getSQLState());
584 System.out.println("Got expected exception " + e.getMessage());
585 }
586
587 System.out.println("Negative Test16 - Call updateXXX method on out of the range column");
588 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
589 rs = stmt.executeQuery("SELECT c1, c2 FROM t1 FOR UPDATE");
590 rs.next();
591 System.out.println("There are only 2 columns in the select list and we are trying to send updateXXX on column position 3");
592 try {
593 rs.updateInt(3,22);
594 System.out.println("FAIL!!! updateInt should have failed because there are only 2 columns in the select list");
595 }
596 catch (SQLException e) {
597 System.out.println("SQL State : " + e.getSQLState());
598 System.out.println("Got expected exception " + e.getMessage());
599 }
600
601 reloadData();
602
603 System.out.println("Positive Test1a - request updatable resultset for forward only type resultset");
604 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
605 JDBCDisplayUtil.ShowWarnings(System.out, conn);
606 System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
607 System.out.println("got TYPE_FORWARD_ONLY? " + (stmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
608 System.out.println("got CONCUR_UPDATABLE? " + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
609 rs = stmt.executeQuery("SELECT * FROM t1");
610 System.out.println("JDBC 2.0 updatable resultset apis on this ResultSet object will pass because this is an updatable resultset");
611 rs.next();
612 System.out.println("column 1 on this row before deleteRow is " + rs.getInt(1));
613 System.out.println("column 2 on this row before deleteRow is " + rs.getString(2));
614 rs.deleteRow();
615 System.out.println("Since after deleteRow(), in embedded mode and Network "+
616 "Server mode using Derby Net Client, ResultSet is positioned before " +
617 "the next row, getXXX will fail");
618 try {
619 System.out.println("column 1 on this deleted row is " + rs.getInt(1));
620 }
621 catch (SQLException e) {
622 System.out.println("SQL State : " + e.getSQLState());
623 System.out.println("Got expected exception " + e.getMessage());
624 }
625 System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
626 try {
627 rs.deleteRow();
628 System.out.println("FAIL!!! deleteRow should have failed because ResultSet is not positioned on a row");
629 }
630 catch (SQLException e) {
631 System.out.println("SQL State : " + e.getSQLState());
632 System.out.println("Got expected exception " + e.getMessage());
633 }
634 System.out.println("Position the ResultSet with next()");
635 rs.next();
636 System.out.println("Should be able to deletRow() on the current row now");
637 rs.deleteRow();
638 rs.close();
640
641 System.out.println("Positive Test1b - request updatable resultset for forward only type resultset");
642 reloadData();
643 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
644 JDBCDisplayUtil.ShowWarnings(System.out, conn);
645 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
646 rs.next();
647 System.out.println("column 1 on this row before updateInt is " + rs.getInt(1));
648 rs.updateInt(1,234);
649 System.out.println("column 1 on this row after updateInt is " + rs.getInt(1));
650 System.out.println("column 2 on this row before updateString is " + rs.getString(2));
651 System.out.println("now updateRow on the row");
652 rs.updateRow();
653 System.out.println("Since after updateRow(), in embedded mode and Network "+
654 "Server mode using Derby Net Client, ResultSet is positioned before " +
655 "the next row, getXXX will fail");
656 try {
657 System.out.println("column 1 on this updateRow row is " + rs.getInt(1));
658 }
659 catch (SQLException e) {
660 System.out.println("SQL State : " + e.getSQLState());
661 System.out.println("Got expected exception " + e.getMessage());
662 }
663 System.out.println("calling updateRow again w/o first positioning the ResultSet on the next row will fail");
664 try {
665 rs.updateRow();
666 System.out.println("FAIL!!! updateRow should have failed because ResultSet is not positioned on a row");
667 }
668 catch (SQLException e) {
669 System.out.println("SQL State : " + e.getSQLState());
670 System.out.println("Got expected exception " + e.getMessage());
671 }
672 System.out.println("Position the ResultSet with next()");
673 rs.next();
674 System.out.println("Should be able to updateRow() on the current row now");
675 rs.updateString(2,"234");
676 rs.updateRow();
677 rs.close();
679
680 System.out.println("Positive Test1c - use updatable resultset to do postitioned delete");
681 conn.setAutoCommit(false);
682 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
683 JDBCDisplayUtil.ShowWarnings(System.out, conn);
684
685 System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
686 System.out.println("got TYPE_FORWARD_ONLY? " + (stmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
687 System.out.println("got CONCUR_UPDATABLE? " + (stmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
688
689 rs = stmt.executeQuery("SELECT * FROM t1");
690 rs.next();
691
692 System.out.println("column 1 on this row before positioned delete " + rs.getInt(1));
693 System.out.println("column 2 on this row before positioned delete " + rs.getString(2));
694
695 pStmt = conn.prepareStatement("DELETE FROM T1 WHERE CURRENT OF " + rs.getCursorName());
696 pStmt.executeUpdate();
697 try {
698 System.out.println("column 1 on this deleted row is " + rs.getInt(1));
699 System.out.println("column 2 on this deleted row is " + rs.getString(2));
700 } catch (SQLException e) {
701 System.out.println("SQL State : " + e.getSQLState());
702 System.out.println("Got expected exception " + e.getMessage());
703 }
704
705 System.out.println("doing positioned delete again w/o first positioning the ResultSet on the next row will fail");
706
707 try {
708 pStmt.executeUpdate();
709 System.out.println("FAIL!!! positioned delete should have failed because ResultSet is not positioned on a row");
710 } catch (SQLException e) {
711 System.out.println("SQL State : " + e.getSQLState());
712 System.out.println("Got expected exception " + e.getMessage());
713 }
714
715 System.out.println("Position the ResultSet with next()");
716
717 rs.next();
718
719 System.out.println("Should be able to do positioned delete on the current row now");
720
721 pStmt.executeUpdate();
722 rs.close();
724 conn.rollback();
725
726 System.out.println("Positive Test1d - updatable resultset to do positioned update");
727 reloadData();
728
729 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
730 JDBCDisplayUtil.ShowWarnings(System.out, conn);
731
732 rs = stmt.executeQuery("SELECT * FROM t1");
733 rs.next();
734
735 System.out.println("column 1 on this row before positioned update is " + rs.getInt(1));
736
737 pStmt = conn.prepareStatement("UPDATE T1 SET C1=?,C2=? WHERE CURRENT OF " + rs.getCursorName());
738 final int c1 = 2345;
739 final String c2 = "UUU";
740
741 pStmt.setInt(1, c1);
742 pStmt.setString(2, c2); System.out.println("now dow positioned update on the row");
744
745 pStmt.executeUpdate();
746
747 System.out.println("column 1 on the updated row is " + rs.getInt(1));
748 System.out.println("column 1 on the updated row is " + rs.getString(2));
749
750 try {
751 System.out.println("Refreshing the row");
752 rs.refreshRow();
754 if (c1!=rs.getInt(1)) {
756 System.out.println("FAIL!!! Expected column 1 to be update to " + c1);
757 }
758 if (!c2.equals(rs.getString(2))) {
759 System.out.println("FAIL!!! Expected column 1 to be update to " + c2);
760 }
761 } catch (SQLException e) {
762 System.out.println("SQL State : " + e.getSQLState());
763 System.out.println("Got expected exception " + e.getMessage());
764 }
765
766 System.out.println("doing positioned update again w/o positioning the RS will succeed");
767 System.out.println("because the cursor is still positioned");
768
769 pStmt.setInt(1, c1);
770 pStmt.setString(2, c2); pStmt.executeUpdate();
772
773 System.out.println("Position the ResultSet with next()");
774
775 rs.next();
776
777 System.out.println("Should still be able to do positioned update");
778
779 pStmt.setInt(1, rs.getInt(1)); pStmt.setString(2, "abc");
781 pStmt.executeUpdate();
782
783 rs.close();
785 conn.rollback();
786
787 conn.setAutoCommit(true);
788 System.out.println("Positive Test2 - even if no columns from table " +
789 "specified in the column list, we should be able to get updatable " +
790 "resultset");
791 reloadData();
792 System.out.println("Will work in embedded mode because target table is "+
793 "not derived from the columns in the select list");
794 System.out.println("Will not work in network server mode because it " +
795 "derives the target table from the columns in the select list");
796 System.out.println("total number of rows in T1 ");
797 dumpRS(stmt.executeQuery("select count(*) from t1"));
798 rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
799 rs.next();
800 System.out.println("column 1 on this row is " + rs.getInt(1));
801 try {
802 rs.deleteRow();
803 if (TestUtil.isNetFramework())
804 System.out.println("FAIL!!! should have failed in network server");
805 else
806 System.out.println("PASS!!! passed in embedded mode");
807 }
808 catch (SQLException e) {
809 if (TestUtil.isNetFramework()) {
810 System.out.println("SQL State : " + e.getSQLState());
811 System.out.println("Got expected exception " + e.getMessage());
812 } else
813 System.out.println("Got unexpected exception " + e.getMessage());
814 }
815 rs.close();
817 System.out.println("total number of rows in T1 after one deleteRow is ");
818 dumpRS(stmt.executeQuery("select count(*) from t1"));
819
820 System.out.println("Positive Test3a - use prepared statement with concur updatable status to test deleteRow");
821 reloadData();
822 pStmt = conn.prepareStatement("select * from t1 where c1>?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
823 System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
824 System.out.println("got TYPE_FORWARD_ONLY? " + (pStmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
825 System.out.println("got CONCUR_UPDATABLE? " + (pStmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
826 pStmt.setInt(1,0);
827 rs = pStmt.executeQuery();
828 rs.next();
829 System.out.println("column 1 on this row is " + rs.getInt(1));
830 rs.deleteRow();
831 System.out.println("Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail");
832 try {
833 System.out.println("column 1 on this deleted row is " + rs.getInt(1));
834 }
835 catch (SQLException e) {
836 System.out.println("SQL State : " + e.getSQLState());
837 System.out.println("Got expected exception " + e.getMessage());
838 }
839 System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
840 try {
841 rs.deleteRow();
842 System.out.println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row");
843 }
844 catch (SQLException e) {
845 System.out.println("SQL State : " + e.getSQLState());
846 System.out.println("Got expected exception " + e.getMessage());
847 }
848 System.out.println("Position the ResultSet with next()");
849 rs.next();
850 if (rs.getInt(1) == 0) {
852 System.out.println("First column should not be 0");
853 }
854 System.out.println("Should be able to deletRow() on the current row now");
856 rs.deleteRow();
857 rs.close();
859
860 System.out.println("Positive Test3b - use prepared statement with concur updatable status to test updateXXX");
861 reloadData();
862 pStmt = conn.prepareStatement("select * from t1 where c1>? for update", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
863 System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
864 System.out.println("got TYPE_FORWARD_ONLY? " + (pStmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
865 System.out.println("got CONCUR_UPDATABLE? " + (pStmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
866 pStmt.setInt(1,0);
867 rs = pStmt.executeQuery();
868 rs.next();
869 System.out.println("column 1 on this row is " + rs.getInt(1));
870 rs.updateInt(1,5);
871 System.out.println("column 1 on this row after updateInt is " + rs.getInt(1));
872 rs.updateRow();
873 System.out.println("Since after updateRow(), ResultSet is positioned before the next row, getXXX will fail");
874 try {
875 System.out.println("column 1 on this updated row is " + rs.getInt(1));
876 }
877 catch (SQLException e) {
878 System.out.println("SQL State : " + e.getSQLState());
879 System.out.println("Got expected exception " + e.getMessage());
880 }
881 System.out.println("calling updateRow/updateXXX again w/o first positioning the ResultSet on the next row will fail");
882 try {
883 rs.updateInt(1,0);
884 System.out.println("FAIL!!! updateXXX should have failed because resultset is not positioned on a row");
885 }
886 catch (SQLException e) {
887 System.out.println("SQL State : " + e.getSQLState());
888 System.out.println("Got expected exception " + e.getMessage());
889 }
890 try {
891 rs.updateRow();
892 System.out.println("FAIL!!! updateRow should have failed because resultset is not positioned on a row");
893 }
894 catch (SQLException e) {
895 System.out.println("SQL State : " + e.getSQLState());
896 System.out.println("Got expected exception " + e.getMessage());
897 }
898 try {
899 rs.cancelRowUpdates();
900 System.out.println("FAIL!!! cancelRowUpdates should have failed because resultset is not positioned on a row");
901 }
902 catch (SQLException e) {
903 System.out.println("SQL State : " + e.getSQLState());
904 System.out.println("Got expected exception " + e.getMessage());
905 }
906 System.out.println("Position the ResultSet with next()");
907 rs.next();
908 System.out.println("Should be able to cancelRowUpdates() on the current row now");
909 rs.cancelRowUpdates();
910 rs.close();
912
913 System.out.println("Positive Test4 - use callable statement with concur updatable status");
914 reloadData();
915 callStmt = conn.prepareCall("select * from t1", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
916 rs = callStmt.executeQuery();
917 System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
918 System.out.println("got TYPE_FORWARD_ONLY? " + (callStmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
919 System.out.println("got CONCUR_UPDATABLE? " + (callStmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
920 rs.next();
921 System.out.println("column 1 on this row is " + rs.getInt(1));
922 rs.deleteRow();
923 System.out.println("Since after deleteRow(), ResultSet is positioned before the next row, getXXX will fail");
924 try {
925 System.out.println("column 1 on this deleted row is " + rs.getInt(1));
926 }
927 catch (SQLException e) {
928 System.out.println("SQL State : " + e.getSQLState());
929 System.out.println("Got expected exception " + e.getMessage());
930 }
931 System.out.println("calling deleteRow again w/o first positioning the ResultSet on the next row will fail");
932 try {
933 rs.deleteRow();
934 System.out.println("FAIL!!! deleteRow should have failed because it can't be called more than once on the same row");
935 }
936 catch (SQLException e) {
937 System.out.println("SQL State : " + e.getSQLState());
938 System.out.println("Got expected exception " + e.getMessage());
939 }
940 System.out.println("Position the ResultSet with next()");
941 rs.next();
942 System.out.println("Should be able to deletRow() on the current row now");
943 rs.deleteRow();
944 rs.close();
946
947 System.out.println("Positive Test5 - donot have to select primary key to get an updatable resultset");
948 reloadData();
949 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
950 rs = stmt.executeQuery("SELECT c32 FROM t3");
951 rs.next();
952 System.out.println("column 1 on this row is " + rs.getInt(1));
953 System.out.println("now try to delete row when primary key is not selected for that row");
954 rs.deleteRow();
955 rs.next();
956 rs.updateLong(1,123);
957 rs.updateRow();
958 rs.close();
960
961 System.out.println("Positive Test6a - For Forward Only resultsets, DatabaseMetaData will return false for ownDeletesAreVisible and deletesAreDetected");
962 System.out.println("This is because, after deleteRow, we position the ResultSet before the next row. We don't make a hole for the deleted row and then stay on that deleted hole");
963 dbmt = conn.getMetaData();
964 System.out.println("ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
965 System.out.println("othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
966 System.out.println("deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
967 reloadData();
968 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
969 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
970 rs.next();
971 System.out.println("The JDBC program should look at rowDeleted only if deletesAreDetected returns true");
972 System.out.println("Since Derby returns false for detlesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowDeleted() for FORWARD_ONLY updatable resultsets");
973 System.out.println("Have this call to rs.rowDeleted() just to make sure the method does always return false? " + rs.rowDeleted());
974 rs.deleteRow();
975 rs.close();
976
977 System.out.println("Positive Test6b - For Forward Only resultsets, DatabaseMetaData will return false for ownUpdatesAreVisible and updatesAreDetected");
978 System.out.println("This is because, after updateRow, we position the ResultSet before the next row");
979 dbmt = conn.getMetaData();
980 System.out.println("ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.ownUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
981 System.out.println("othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
982 System.out.println("updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? " + dbmt.updatesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
983 reloadData();
984 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
985 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
986 rs.next();
987 System.out.println("The JDBC program should look at rowUpdated only if updatesAreDetected returns true");
988 System.out.println("Since Derby returns false for updatesAreDetected for FORWARD_ONLY updatable resultset,the program should not rely on rs.rowUpdated() for FORWARD_ONLY updatable resultsets");
989 System.out.println("Have this call to rs.rowUpdated() just to make sure the method does always return false? " + rs.rowUpdated());
990 rs.updateLong(1,123);
991 rs.updateRow();
992 rs.close();
993
994 System.out.println("Positive Test7a - delete using updatable resultset api from a temporary table");
995 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
996 stmt.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
997 stmt.executeUpdate("insert into SESSION.t2 values(21, 1)");
998 stmt.executeUpdate("insert into SESSION.t2 values(22, 1)");
999 System.out.println("following rows in temp table before deleteRow");
1000 dumpRS(stmt.executeQuery("select * from SESSION.t2"));
1001 rs = stmt.executeQuery("select c21 from session.t2 for update");
1002 rs.next();
1003 rs.deleteRow();
1004 rs.next();
1005 if (rs.getInt(1) == 0) {
1007 System.out.println("Column c21 should not be 0");
1008 }
1009 rs.deleteRow();
1011 System.out.println("As expected, no rows in temp table after deleteRow");
1012 dumpRS(stmt.executeQuery("select * from SESSION.t2"));
1013 rs.close();
1014 stmt.executeUpdate("DROP TABLE SESSION.t2");
1015
1016 System.out.println("Positive Test7b - update using updatable resultset api from a temporary table");
1017 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1018 stmt.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE SESSION.t3(c31 int, c32 int) on commit preserve rows not logged");
1019 stmt.executeUpdate("insert into SESSION.t3 values(21, 1)");
1020 stmt.executeUpdate("insert into SESSION.t3 values(22, 1)");
1021 System.out.println("following rows in temp table before deleteRow");
1022 dumpRS(stmt.executeQuery("select * from SESSION.t3"));
1023 rs = stmt.executeQuery("select c31 from session.t3");
1024 rs.next();
1025 rs.updateLong(1,123);
1026 rs.updateRow();
1027 rs.next();
1028 rs.updateLong(1,123);
1029 rs.updateRow();
1030 System.out.println("As expected, updated rows in temp table after updateRow");
1031 dumpRS(stmt.executeQuery("select * from SESSION.t3"));
1032 rs.close();
1033 stmt.executeUpdate("DROP TABLE SESSION.t3");
1034
1035 System.out.println("Positive Test8a - change the name of the statement " +
1036 "when the resultset is open and see if deleteRow still works");
1037 System.out.println("This test works in embedded mode since Derby can " +
1038 "handle the change in the name of the statement with an open resultset");
1039 System.out.println("But it fails under Network Server mode because JCC " +
1040 "and Derby Net Client do not allow statement name change when there " +
1041 "an open resultset against it");
1042 reloadData();
1043 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1044 System.out.println("change the cursor name(case sensitive name) with setCursorName and then try to deleteRow");
1045 stmt.setCursorName("CURSORNOUPDATe"); rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
1047 rs.next();
1048 rs.deleteRow();
1049 System.out.println("change the cursor name one more time with setCursorName and then try to deleteRow");
1050 try {
1051 stmt.setCursorName("CURSORNOUPDATE1");
1052 rs.next();
1053 rs.deleteRow();
1054 if (TestUtil.isNetFramework())
1055 System.out.println("FAIL!!! should have failed in network server");
1056 else
1057 System.out.println("PASS!!! passed in embedded mode");
1058 }
1059 catch (SQLException e) {
1060 if (TestUtil.isNetFramework()) {
1061 System.out.println("SQL State : " + e.getSQLState());
1062 System.out.println("Got expected exception " + e.getMessage());
1063 } else
1064 System.out.println("Got unexpected exception " + e.getMessage());
1065 }
1066 rs.close();
1067
1068 System.out.println("Positive Test8b - change the name of the statement " +
1069 "when the resultset is open and see if updateRow still works");
1070 System.out.println("This test works in embedded mode since Derby can " +
1071 "handle the change in the name of the statement with an open resultset");
1072 System.out.println("But it fails under Network Server mode because JCC " +
1073 "and Derby Net Client do not allow statement name change when there " +
1074 "an open resultset against it");
1075 reloadData();
1076 System.out.println("change the cursor name one more time with setCursorName and then try to updateRow");
1077 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1078 System.out.println("change the cursor name(case sensitive name) with setCursorName and then try to updateRow");
1079 stmt.setCursorName("CURSORNOUPDATe"); rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
1081 rs.next();
1082 rs.updateLong(1,123);
1083 try {
1084 stmt.setCursorName("CURSORNOUPDATE1");
1085 rs.updateRow();
1086 if (TestUtil.isNetFramework())
1087 System.out.println("FAIL!!! should have failed in network server");
1088 else
1089 System.out.println("PASS!!! passed in embedded mode");
1090 }
1091 catch (SQLException e) {
1092 if (TestUtil.isNetFramework()) {
1093 System.out.println("SQL State : " + e.getSQLState());
1094 System.out.println("Got expected exception " + e.getMessage());
1095 } else
1096 System.out.println("Got unexpected exception " + e.getMessage());
1097 }
1098 rs.close();
1099
1100 System.out.println("Positive Test9a - using correlation name for the " +
1101 "table in the select sql works in embedded mode and Network Server " +
1102 "using Derby Net Client driver");
1103 System.out.println("Correlation name for table does not work in Network "+
1104 "Server mode (using JCC) because the drivers construct the delete sql "+
1105 "with the correlation name rather than the base table name");
1106 reloadData();
1107 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1108 rs = stmt.executeQuery("SELECT * FROM t1 abcde FOR UPDATE of c1");
1109 rs.next();
1110 System.out.println("column 1 on this row is " + rs.getInt(1));
1111 System.out.println("now try to deleteRow");
1112 try {
1113 rs.deleteRow();
1114 if (TestUtil.isJCCFramework())
1115 System.out.println("FAIL!!! should have failed in network server");
1116 else
1117 System.out.println("PASS!!! passed in embedded mode");
1118 }
1119 catch (SQLException e) {
1120 if (TestUtil.isJCCFramework()) {
1121 System.out.println("SQL State : " + e.getSQLState());
1122 System.out.println("Got expected exception " + e.getMessage());
1123 } else
1124 System.out.println("Got unexpected exception " + e.getMessage());
1125 }
1126 rs.close();
1127
1128 System.out.println("Positive Test9b - using correlation name for " +
1129 "updatable columns is not allowed.");
1130 reloadData();
1131 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1132 System.out.println("Table t1 has following rows");
1133 dumpRS(stmt.executeQuery("select * from t1"));
1134 try {
1135 System.out.println("attempt to get an updatable resultset using correlation name for an updatable column");
1136 System.out.println("The sql is SELECT c1 as col1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1137 rs = stmt.executeQuery("SELECT c1 as col1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1138 System.out.println("FAIL!!! executeQuery should have failed");
1139 }
1140 catch (SQLException e) {
1141 System.out.println("SQL State : " + e.getSQLState());
1142 System.out.println("Got expected exception " + e.getMessage());
1143 }
1144 System.out.println("attempt to get an updatable resultset using correlation name for an readonly column. It should work");
1145 System.out.println("The sql is SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1146 rs = stmt.executeQuery("SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1147 rs.next();
1148 rs.updateInt(1,11);
1149 rs.updateRow();
1150 rs.close();
1151 System.out.println("Table t1 after updateRow has following rows");
1152 dumpRS(stmt.executeQuery("select * from t1"));
1153
1154 System.out.println("Positive Test9c - try to updateXXX on a readonly column. Should get error");
1155 reloadData();
1156 rs = stmt.executeQuery("SELECT c1, c2 FROM t1 abcde FOR UPDATE of c1");
1157 rs.next();
1158 try {
1159 rs.updateString(2,"bbbb");
1160 System.out.println("FAIL!!! updateString on readonly column should have failed");
1161 }
1162 catch (SQLException e) {
1163 System.out.println("SQL State : " + e.getSQLState());
1164 System.out.println("Got expected exception " + e.getMessage());
1165 }
1166 System.out.println("attempt to get an updatable resultset using correlation name for an readonly column. It should work");
1167 System.out.println("The sql is SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1168 rs = stmt.executeQuery("SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1169 rs.next();
1170 rs.updateInt(1,11);
1171 rs.updateRow();
1172 rs.close();
1173 System.out.println("Table t1 after updateRow has following rows");
1174 dumpRS(stmt.executeQuery("select * from t1"));
1175
1176 System.out.println("Positive Test9d - try to updateXXX on a readonly column with correlation name. Should get error");
1177 reloadData();
1178 rs = stmt.executeQuery("SELECT c1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
1179 rs.next();
1180 try {
1181 rs.updateString(2,"bbbb");
1182 System.out.println("FAIL!!! updateString on readonly column should have failed");
1183 }
1184 catch (SQLException e) {
1185 System.out.println("SQL State : " + e.getSQLState());
1186 System.out.println("Got expected exception " + e.getMessage());
1187 }
1188 rs.close();
1189 System.out.println("Table t1 has following rows");
1190 dumpRS(stmt.executeQuery("select * from t1"));
1191
1192 System.out.println("Positive Test10 - 2 updatable resultsets going against the same table, will they conflict?");
1193 conn.setAutoCommit(false);
1194 reloadData();
1195 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1196 stmt1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1197 rs = stmt.executeQuery("SELECT * FROM t1");
1198 rs.next();
1199 rs1 = stmt1.executeQuery("SELECT * FROM t1 FOR UPDATE");
1200 rs1.next();
1201 System.out.println("delete using first resultset");
1202 rs.deleteRow();
1203 try {
1204 System.out.println("attempt to send deleteRow on the same row through a different resultset should throw an exception");
1205 rs1.deleteRow();
1206 System.out.println("FAIL!!! delete using second resultset succedded? ");
1207 }
1208 catch (SQLException e) {
1209 System.out.println("SQL State : " + e.getSQLState());
1210 System.out.println("Got expected exception " + e.getMessage());
1211 }
1212 System.out.println("Move to next row in the 2nd resultset and then delete using the second resultset");
1213 rs1.next();
1214 rs1.deleteRow();
1215 rs.close();
1216 rs1.close();
1217 conn.setAutoCommit(true);
1218
1219 System.out.println("Positive Test11 - setting the fetch size to > 1 will be ignored by updatable resultset. Same as updatable cursors");
1220 reloadData();
1221 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1222 stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
1223 stmt.setFetchSize(200);
1224 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE of c1");
1225 System.out.println("Notice the Fetch Size in run time statistics output.");
1226 showScanStatistics(rs, conn);
1227 System.out.println("statement's fetch size is " + stmt.getFetchSize());
1228 rs.close();
1229 stmt.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
1230
1231 System.out.println("Positive Test12a - make sure delete trigger gets fired when deleteRow is issued");
1232 reloadData();
1233 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1234 System.out.println("Verify that before delete trigger got fired, row count is 0 in deleteTriggerInsertIntoThisTable");
1235 dumpRS(stmt.executeQuery("select count(*) from deleteTriggerInsertIntoThisTable"));
1236 rs = stmt.executeQuery("SELECT * FROM table0WithTriggers FOR UPDATE");
1237 rs.next();
1238 System.out.println("column 1 on this row is " + rs.getInt(1));
1239 System.out.println("now try to delete row and make sure that trigger got fired");
1240 rs.deleteRow();
1241 rs.close();
1242 System.out.println("Verify that delete trigger got fired by verifying the row count to be 1 in deleteTriggerInsertIntoThisTable");
1243 dumpRS(stmt.executeQuery("select count(*) from deleteTriggerInsertIntoThisTable"));
1244 rs.close();
1246
1247 System.out.println("Positive Test12b - make sure update trigger gets fired when updateRow is issued");
1248 reloadData();
1249 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1250 System.out.println("Verify that before update trigger got fired, row count is 0 in updateTriggerInsertIntoThisTable");
1251 dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
1252 rs = stmt.executeQuery("SELECT * FROM table0WithTriggers");
1253 rs.next();
1254 System.out.println("column 1 on this row is " + rs.getInt(1));
1255 System.out.println("now try to update row and make sure that trigger got fired");
1256 rs.updateLong(1,123);
1257 rs.updateRow();
1258 rs.close();
1259 System.out.println("Verify that update trigger got fired by verifying the row count to be 1 in updateTriggerInsertIntoThisTable");
1260 dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
1261 rs.close();
1263
1264 System.out.println("Positive Test13a - Another test case for delete trigger");
1265 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1266 rs = stmt.executeQuery("SELECT * FROM table1WithTriggers FOR UPDATE");
1267 rs.next();
1268 System.out.println("column 1 on this row is " + rs.getInt(1));
1269 System.out.println("this delete row will fire the delete trigger which will delete all the rows from the table and from the resultset");
1270 rs.deleteRow();
1271 rs.next();
1272 try {
1273 rs.deleteRow();
1274 System.out.println("FAIL!!! there should have be no more rows in the resultset at this point because delete trigger deleted all the rows");
1275 }
1276 catch (SQLException e) {
1277 System.out.println("SQL State : " + e.getSQLState());
1278 System.out.println("Got expected exception " + e.getMessage());
1279 }
1280 rs.close();
1281 System.out.println("Verify that delete trigger got fired by verifying the row count to be 0 in table1WithTriggers");
1282 dumpRS(stmt.executeQuery("select count(*) from table1WithTriggers"));
1283 rs.close();
1285
1286 System.out.println("Positive Test13b - Another test case for update trigger");
1287 System.out.println("Look at the current contents of table2WithTriggers");
1288 dumpRS(stmt.executeQuery("select * from table2WithTriggers"));
1289 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1290 rs = stmt.executeQuery("SELECT * FROM table2WithTriggers where c1>1 FOR UPDATE");
1291 rs.next();
1292 System.out.println("column 1 on this row is " + rs.getInt(1));
1293 System.out.println("this update row will fire the update trigger which will update all the rows in the table to have c1=1 and hence no more rows will qualify for the resultset");
1294 rs.updateLong(2,2);
1295 rs.updateRow();
1296 rs.next();
1297 try {
1298 rs.updateRow();
1299 System.out.println("FAIL!!! there should have be no more rows in the resultset at this point because update trigger made all the rows not qualify for the resultset");
1300 }
1301 catch (SQLException e) {
1302 System.out.println("SQL State : " + e.getSQLState());
1303 System.out.println("Got expected exception " + e.getMessage());
1304 }
1305 rs.close();
1306 System.out.println("Verify that update trigger got fired by verifying that all column c1s have value 1 in table2WithTriggers");
1307 dumpRS(stmt.executeQuery("select * from table2WithTriggers"));
1308 rs.close();
1310 conn.rollback();
1311
1312 System.out.println("Positive Test14a - make sure self referential delete cascade works when deleteRow is issued");
1313 dumpRS(stmt.executeQuery("select * from selfReferencingT1"));
1314 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1315 rs = stmt.executeQuery("SELECT * FROM selfReferencingT1");
1316 rs.next();
1317 System.out.println("column 1 on this row is " + rs.getString(1));
1318 System.out.println("this delete row will cause the delete cascade constraint to delete all the rows from the table and from the resultset");
1319 rs.deleteRow();
1320 rs.next();
1321 try {
1322 rs.deleteRow();
1323 System.out.println("FAIL!!! there should have be no more rows in the resultset at this point because delete cascade deleted all the rows");
1324 }
1325 catch (SQLException e) {
1326 System.out.println("SQL State : " + e.getSQLState());
1327 System.out.println("Got expected exception " + e.getMessage());
1328 }
1329 rs.close();
1330 System.out.println("Verify that delete trigger got fired by verifying the row count to be 0 in selfReferencingT1");
1331 dumpRS(stmt.executeQuery("select count(*) from selfReferencingT1"));
1332 rs.close();
1334
1335 System.out.println("Positive Test14b - make sure self referential update restrict works when updateRow is issued");
1336 dumpRS(stmt.executeQuery("select * from selfReferencingT2"));
1337 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1338 rs = stmt.executeQuery("SELECT * FROM selfReferencingT2 FOR UPDATE");
1339 rs.next();
1340 System.out.println("column 1 on this row is " + rs.getString(1));
1341 System.out.println("update row should fail because cascade constraint is update restrict");
1342 rs.updateString(1,"e2");
1343 try {
1344 rs.updateRow();
1345 System.out.println("FAIL!!! this update should have caused violation of foreign key constraint");
1346 }
1347 catch (SQLException e) {
1348 System.out.println("SQL State : " + e.getSQLState());
1349 System.out.println("Got expected exception " + e.getMessage());
1350 }
1351 rs.close();
1353
1354 System.out.println("Positive Test15 - With autocommit off, attempt to drop a table when there is an open updatable resultset on it");
1355 reloadData();
1356 conn.setAutoCommit(false);
1357 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1358 rs = stmt.executeQuery("SELECT * FROM t1");
1359 rs.next();
1360 System.out.println("Opened an updatable resultset. Now trying to drop that table through another Statement");
1361 stmt1 = conn.createStatement();
1362 try {
1363 stmt1.executeUpdate("drop table t1");
1364 System.out.println("FAIL!!! drop table should have failed because the updatable resultset is still open");
1365 }
1366 catch (SQLException e) {
1367 System.out.println("SQL State : " + e.getSQLState());
1368 System.out.println("Got expected exception " + e.getMessage());
1369 }
1370 System.out.println("Since autocommit is off, the drop table exception will NOT result in a runtime rollback and hence updatable resultset object is still open");
1371 rs.deleteRow();
1372 rs.close();
1373 conn.setAutoCommit(true);
1374
1375 System.out.println("Positive Test16a - Do deleteRow within a transaction and then rollback the transaction");
1376 reloadData();
1377 conn.setAutoCommit(false);
1378 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1379 System.out.println("Verify that before delete trigger got fired, row count is 0 in deleteTriggerInsertIntoThisTable");
1380 dumpRS(stmt.executeQuery("select count(*) from deleteTriggerInsertIntoThisTable"));
1381 System.out.println("Verify that before deleteRow, row count is 4 in table0WithTriggers");
1382 dumpRS(stmt.executeQuery("select count(*) from table0WithTriggers"));
1383 rs = stmt.executeQuery("SELECT * FROM table0WithTriggers FOR UPDATE");
1384 rs.next();
1385 System.out.println("column 1 on this row is " + rs.getInt(1));
1386 System.out.println("now try to delete row and make sure that trigger got fired");
1387 rs.deleteRow();
1388 rs.close();
1389 System.out.println("Verify that delete trigger got fired by verifying the row count to be 1 in deleteTriggerInsertIntoThisTable");
1390 dumpRS(stmt.executeQuery("select count(*) from deleteTriggerInsertIntoThisTable"));
1391 System.out.println("Verify that deleteRow in transaction, row count is 3 in table0WithTriggers");
1392 dumpRS(stmt.executeQuery("select count(*) from table0WithTriggers"));
1393 rs.close();
1395 conn.rollback();
1396 System.out.println("Verify that after rollback, row count is back to 0 in deleteTriggerInsertIntoThisTable");
1397 dumpRS(stmt.executeQuery("select count(*) from deleteTriggerInsertIntoThisTable"));
1398 System.out.println("Verify that after rollback, row count is back to 4 in table0WithTriggers");
1399 dumpRS(stmt.executeQuery("select count(*) from table0WithTriggers"));
1400 conn.setAutoCommit(true);
1401
1402 System.out.println("Positive Test16b - Do updateRow within a transaction and then rollback the transaction");
1403 reloadData();
1404 conn.setAutoCommit(false);
1405 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1406 System.out.println("Verify that before update trigger got fired, row count is 0 in updateTriggerInsertIntoThisTable");
1407 dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
1408 System.out.println("Look at the data in table0WithTriggers before trigger gets fired");
1409 dumpRS(stmt.executeQuery("select * from table0WithTriggers"));
1410 rs = stmt.executeQuery("SELECT * FROM table0WithTriggers");
1411 rs.next();
1412 System.out.println("column 1 on this row is " + rs.getInt(1));
1413 System.out.println("now try to update row and make sure that trigger got fired");
1414 rs.updateLong(1,123);
1415 rs.updateRow();
1416 rs.close();
1417 System.out.println("Verify that update trigger got fired by verifying the row count to be 1 in updateTriggerInsertIntoThisTable");
1418 dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
1419 System.out.println("Verify that new data in table0WithTriggers");
1420 dumpRS(stmt.executeQuery("select * from table0WithTriggers"));
1421 rs.close();
1423 conn.rollback();
1424 System.out.println("Verify that after rollback, row count is back to 0 in updateTriggerInsertIntoThisTable");
1425 dumpRS(stmt.executeQuery("select count(*) from updateTriggerInsertIntoThisTable"));
1426 System.out.println("Verify that after rollback, table0WithTriggers is back to its original contents");
1427 dumpRS(stmt.executeQuery("select * from table0WithTriggers"));
1428 conn.setAutoCommit(true);
1429
1430 System.out.println("Positive Test17 - After deleteRow, resultset is positioned before the next row");
1431 reloadData();
1432 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1433 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
1434 rs.next();
1435 rs.deleteRow();
1436 System.out.println("getXXX right after deleteRow will fail because resultset is not positioned on a row, instead it is right before the next row");
1437 try {
1438 System.out.println("column 1 (which is not nullable) after deleteRow is " + rs.getString(1));
1439 }
1440 catch (SQLException e) {
1441 System.out.println("SQL State : " + e.getSQLState());
1442 System.out.println("Got expected exception " + e.getMessage());
1443 }
1444 rs.close();
1445
1446 System.out.println("Positive Test18 - Test cancelRowUpdates method as the first updatable ResultSet api on a read-only resultset");
1447 stmt = conn.createStatement();
1448 rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable");
1449 try {
1450 rs.cancelRowUpdates();
1451 System.out.println("Test failed - should not have reached here because cancelRowUpdates is being called on a read-only resultset");
1452 } catch (SQLException e) {
1453 System.out.println("SQL State : " + e.getSQLState());
1454 System.out.println("Got expected exception " + e.getMessage());
1455 }
1456 rs.close();
1457
1458 System.out.println("Positive Test19 - Test updateRow method as the first updatable ResultSet api on a read-only resultset");
1459 stmt = conn.createStatement();
1460 rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable");
1461 rs.next();
1462 try {
1463 rs.updateRow();
1464 System.out.println("Test failed - should not have reached here because updateRow is being called on a read-only resultset");
1465 return;
1466 } catch (Throwable e) {
1467 System.out.println(" Got expected exception : " + e.getMessage());
1468 }
1469 rs.close();
1470
1471 System.out.println("Positive Test20 - Test updateXXX methods as the first updatable ResultSet api on a read-only resultset");
1472 conn.setAutoCommit(false);
1473 stmt = conn.createStatement();
1474 for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
1475 System.out.println(" Test " + allUpdateXXXNames[updateXXXName-1] + " on a readonly resultset");
1476 for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
1477 rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable");
1478 rs.next();
1479 rs1 = stmt1.executeQuery("SELECT * FROM AllDataTypesNewValuesData");
1480 rs1.next();
1481 if (indexOrName == 1) System.out.println(" Using column position as first parameter to " + allUpdateXXXNames[updateXXXName-1]);
1483 else
1484 System.out.println(" Using column name as first parameter to " + allUpdateXXXNames[updateXXXName-1]);
1485 try {
1486 if (updateXXXName == 1) { if (indexOrName == 1) rs.updateShort(1, rs1.getShort(updateXXXName));
1489 else rs.updateShort(ColumnNames[0], rs1.getShort(updateXXXName));
1491 } else if (updateXXXName == 2){ if (indexOrName == 1) rs.updateInt(1, rs1.getInt(updateXXXName));
1494 else rs.updateInt(ColumnNames[0], rs1.getInt(updateXXXName));
1496 } else if (updateXXXName == 3){ if (indexOrName == 1) rs.updateLong(1, rs1.getLong(updateXXXName));
1499 else rs.updateLong(ColumnNames[0], rs1.getLong(updateXXXName));
1501 } else if (updateXXXName == 4){ if (indexOrName == 1) BigDecimalHandler.updateBigDecimalString(rs, 1,
1504 BigDecimalHandler.getBigDecimalString(rs1, updateXXXName));
1505 else BigDecimalHandler.updateBigDecimalString(rs, ColumnNames[0],
1507 BigDecimalHandler.getBigDecimalString(rs1, updateXXXName));
1508 } else if (updateXXXName == 5){ if (indexOrName == 1) rs.updateFloat(1, rs1.getFloat(updateXXXName));
1511 else rs.updateFloat(ColumnNames[0], rs1.getFloat(updateXXXName));
1513 } else if (updateXXXName == 6){ if (indexOrName == 1) rs.updateDouble(1, rs1.getDouble(updateXXXName));
1516 else rs.updateDouble(ColumnNames[0], rs1.getDouble(updateXXXName));
1518 } else if (updateXXXName == 7){ if (indexOrName == 1) rs.updateString(1, rs1.getString(updateXXXName));
1521 else rs.updateString(ColumnNames[0], rs1.getString(updateXXXName));
1523 } else if (updateXXXName == 8){ if (indexOrName == 1) rs.updateAsciiStream(1,rs1.getAsciiStream(updateXXXName), 4);
1526 else rs.updateAsciiStream(ColumnNames[0],rs1.getAsciiStream(updateXXXName), 4);
1528 } else if (updateXXXName == 9){ if (indexOrName == 1) rs.updateCharacterStream(1,rs1.getCharacterStream(updateXXXName), 4);
1531 else rs.updateCharacterStream(ColumnNames[0],rs1.getCharacterStream(updateXXXName), 4);
1533 } else if (updateXXXName == 10){ if (indexOrName == 1) rs.updateByte(1,rs1.getByte(1));
1536 else rs.updateByte(ColumnNames[0],rs1.getByte(1));
1538 } else if (updateXXXName == 11){ if (indexOrName == 1) rs.updateBytes(1,rs1.getBytes(updateXXXName));
1541 else rs.updateBytes(ColumnNames[0],rs1.getBytes(updateXXXName));
1543 } else if (updateXXXName == 12){ if (indexOrName == 1) rs.updateBinaryStream(1,rs1.getBinaryStream(updateXXXName), 2);
1546 else rs.updateBinaryStream(ColumnNames[0],rs1.getBinaryStream(updateXXXName), 2);
1548 } else if (updateXXXName == 13){ if (JVMInfo.JDK_ID == 2) continue;
1551 if (indexOrName == 1) rs.updateClob(1,rs1.getClob(updateXXXName));
1553 else rs.updateClob(ColumnNames[0],rs1.getClob(updateXXXName));
1555 } else if (updateXXXName == 14){ if (indexOrName == 1) rs.updateDate(1,rs1.getDate(updateXXXName));
1558 else rs.updateDate(ColumnNames[0],rs1.getDate(updateXXXName));
1560 } else if (updateXXXName == 15){ if (indexOrName == 1) rs.updateTime(1,rs1.getTime(updateXXXName));
1563 else rs.updateTime(ColumnNames[0],rs1.getTime(updateXXXName));
1565 } else if (updateXXXName == 16){ if (indexOrName == 1) rs.updateTimestamp(1,rs1.getTimestamp(updateXXXName));
1568 else rs.updateTimestamp(ColumnNames[0],rs1.getTimestamp(updateXXXName));
1570 } else if (updateXXXName == 17){ if (JVMInfo.JDK_ID == 2) continue;
1573 if (indexOrName == 1) rs.updateBlob(1,rs1.getBlob(updateXXXName));
1575 else rs.updateBlob(ColumnNames[0],rs1.getBlob(updateXXXName));
1577 } else if (updateXXXName == 18){ if (indexOrName == 1) rs.updateBoolean(1, rs1.getBoolean(1));
1582 else rs.updateBoolean(ColumnNames[0], rs1.getBoolean(1));
1584 } else if (updateXXXName == 19){ if (indexOrName == 1) rs.updateNull(1);
1587 else rs.updateNull(ColumnNames[0]);
1589 } else if (updateXXXName == 20){ if (JVMInfo.JDK_ID == 2) continue;
1592 if (indexOrName == 1) rs.updateArray(1, null);
1594 else rs.updateArray(ColumnNames[0], null);
1596 } else if (updateXXXName == 21){ if (JVMInfo.JDK_ID == 2) continue;
1599 if (indexOrName == 1) rs.updateRef(1, null);
1601 else rs.updateRef(ColumnNames[0], null);
1603 }
1604 System.out.println("Test failed - should not have reached here because updateXXX is being called on a read-only resultset");
1605 return;
1606 } catch (Throwable e) {
1607 System.out.println(" Got expected exception : " + e.getMessage());
1608 }
1609 }
1610 }
1611 conn.rollback();
1612 conn.setAutoCommit(true);
1613
1614 System.out.println("Positive Test21 - Test all updateXXX(excluding updateObject) methods on all the supported sql datatypes");
1615 conn.setAutoCommit(false);
1616 PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM AllDataTypesForTestingTable FOR UPDATE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1617 PreparedStatement pstmt1 = conn.prepareStatement("SELECT * FROM AllDataTypesNewValuesData");
1618 for (int sqlType = 1, checkAgainstColumn = 1; sqlType <= allSQLTypes.length; sqlType++ ) {
1619 conn.rollback();
1620 System.out.println("Next datatype to test is " + allSQLTypes[sqlType-1]);
1621 for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
1622 checkAgainstColumn = updateXXXName;
1623 if(!HAVE_BIG_DECIMAL && (updateXXXName == 4))
1624 continue;
1625 System.out.println(" Testing " + allUpdateXXXNames[updateXXXName-1] + " on SQL type " + allSQLTypes[sqlType-1]);
1626 for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
1627 if (indexOrName == 1) System.out.println(" Using column position as first parameter to " + allUpdateXXXNames[updateXXXName-1]);
1629 else
1630 System.out.println(" Using column name as first parameter to " + allUpdateXXXNames[updateXXXName-1]);
1631 rs = pstmt.executeQuery();
1632 rs.next();
1633 rs1 = pstmt1.executeQuery();
1634 rs1.next();
1635 try {
1636 if (updateXXXName == 1) { if (indexOrName == 1) rs.updateShort(sqlType, rs1.getShort(updateXXXName));
1639 else rs.updateShort(ColumnNames[sqlType-1], rs1.getShort(updateXXXName));
1641 } else if (updateXXXName == 2){ if (indexOrName == 1) rs.updateInt(sqlType, rs1.getInt(updateXXXName));
1644 else rs.updateInt(ColumnNames[sqlType-1], rs1.getInt(updateXXXName));
1646 } else if (updateXXXName == 3){ if (indexOrName == 1) rs.updateLong(sqlType, rs1.getLong(updateXXXName));
1649 else rs.updateLong(ColumnNames[sqlType-1], rs1.getLong(updateXXXName));
1651 } else if (updateXXXName == 4){ if(HAVE_BIG_DECIMAL) {
1653 if (indexOrName == 1) rs.updateBigDecimal(sqlType, rs1.getBigDecimal(updateXXXName));
1655 else rs.updateBigDecimal(ColumnNames[sqlType-1], rs1.getBigDecimal(updateXXXName));
1657 }
1658 } else if (updateXXXName == 5){ if (indexOrName == 1) rs.updateFloat(sqlType, rs1.getFloat(updateXXXName));
1661 else rs.updateFloat(ColumnNames[sqlType-1], rs1.getFloat(updateXXXName));
1663 } else if (updateXXXName == 6){ if (indexOrName == 1) rs.updateDouble(sqlType, rs1.getDouble(updateXXXName));
1666 else rs.updateDouble(ColumnNames[sqlType-1], rs1.getDouble(updateXXXName));
1668 } else if (updateXXXName == 7){ if (indexOrName == 1) rs.updateString(sqlType, rs1.getString(updateXXXName));
1671 else rs.updateString(ColumnNames[sqlType-1], rs1.getString(updateXXXName));
1673 } else if (updateXXXName == 8){ if (indexOrName == 1) rs.updateAsciiStream(sqlType,rs1.getAsciiStream(updateXXXName), 4);
1676 else rs.updateAsciiStream(ColumnNames[sqlType-1],rs1.getAsciiStream(updateXXXName), 4);
1678 } else if (updateXXXName == 9){ if (indexOrName == 1) rs.updateCharacterStream(sqlType,rs1.getCharacterStream(updateXXXName), 4);
1681 else rs.updateCharacterStream(ColumnNames[sqlType-1],rs1.getCharacterStream(updateXXXName), 4);
1683 } else if (updateXXXName == 10){ checkAgainstColumn = 1;
1685 if (indexOrName == 1) rs.updateByte(sqlType,rs1.getByte(checkAgainstColumn));
1687 else rs.updateByte(ColumnNames[sqlType-1],rs1.getByte(checkAgainstColumn));
1689 } else if (updateXXXName == 11){ if (indexOrName == 1) rs.updateBytes(sqlType,rs1.getBytes(updateXXXName));
1692 else rs.updateBytes(ColumnNames[sqlType-1],rs1.getBytes(updateXXXName));
1694 } else if (updateXXXName == 12){ if (indexOrName == 1) rs.updateBinaryStream(sqlType,rs1.getBinaryStream(updateXXXName), 2);
1697 else rs.updateBinaryStream(ColumnNames[sqlType-1],rs1.getBinaryStream(updateXXXName), 2);
1699 } else if (updateXXXName == 13){ if (JVMInfo.JDK_ID == 2) continue;
1702 if (indexOrName == 1) rs.updateClob(sqlType,rs1.getClob(updateXXXName));
1704 else rs.updateClob(ColumnNames[sqlType-1],rs1.getClob(updateXXXName));
1706 } else if (updateXXXName == 14){ if (indexOrName == 1) rs.updateDate(sqlType,rs1.getDate(updateXXXName));
1709 else rs.updateDate(ColumnNames[sqlType-1],rs1.getDate(updateXXXName));
1711 } else if (updateXXXName == 15){ if (indexOrName == 1) rs.updateTime(sqlType,rs1.getTime(updateXXXName));
1714 else rs.updateTime(ColumnNames[sqlType-1],rs1.getTime(updateXXXName));
1716 } else if (updateXXXName == 16){ if (indexOrName == 1) rs.updateTimestamp(sqlType,rs1.getTimestamp(updateXXXName));
1719 else rs.updateTimestamp(ColumnNames[sqlType-1],rs1.getTimestamp(updateXXXName));
1721 } else if (updateXXXName == 17){ if (JVMInfo.JDK_ID == 2) continue;
1724 if (indexOrName == 1) rs.updateBlob(sqlType,rs1.getBlob(updateXXXName));
1726 else rs.updateBlob(ColumnNames[sqlType-1],rs1.getBlob(updateXXXName));
1728 } else if (updateXXXName == 18){ if (indexOrName == 1) rs.updateBoolean(sqlType, rs1.getBoolean(1));
1733 else rs.updateBoolean(ColumnNames[sqlType-1], rs1.getBoolean(1));
1735 } else if (updateXXXName == 19){ if (indexOrName == 1) rs.updateNull(sqlType);
1738 else rs.updateNull(ColumnNames[sqlType-1]);
1740 } else if (updateXXXName == 20){ if (JVMInfo.JDK_ID == 2) continue;
1743 if (indexOrName == 1) rs.updateArray(sqlType, null);
1745 else rs.updateArray(ColumnNames[sqlType-1], null);
1747 } else if (updateXXXName == 21){ if (JVMInfo.JDK_ID == 2) continue;
1750 if (indexOrName == 1) rs.updateRef(sqlType, null);
1752 else rs.updateRef(ColumnNames[sqlType-1], null);
1754 }
1755 rs.updateRow();
1756 if ((TestUtil.isNetFramework() && updateXXXRulesTableForNetworkServer[sqlType-1][updateXXXName-1].equals("ERROR")) ||
1757 (TestUtil.isEmbeddedFramework() && updateXXXRulesTableForEmbedded[sqlType-1][updateXXXName-1].equals("ERROR"))) {
1758 System.out.println("FAILURE : We shouldn't reach here. The test should have failed earlier on updateXXX or updateRow call");
1759 return;
1760 }
1761 if (verifyData(sqlType,checkAgainstColumn, "AllDataTypesNewValuesData") == false)
1762 {
1763 System.out.println("Test failed");
1764 return;
1765 }
1766 resetData();
1767 } catch (Throwable e) {
1768 if ((TestUtil.isNetFramework() && updateXXXRulesTableForNetworkServer[sqlType-1][updateXXXName-1].equals("ERROR")) ||
1769 (TestUtil.isEmbeddedFramework() && updateXXXRulesTableForEmbedded[sqlType-1][updateXXXName-1].equals("ERROR")))
1770 System.out.println(" Got expected exception : " + e.getMessage());
1771 else {
1772 if ((sqlType == 14 || sqlType == 15 || sqlType == 16) && checkAgainstColumn == 7) System.out.println(" Got expected exception : " + e.getMessage());
1775 else {
1776 System.out.println(" Got UNexpected exception : " + e.getMessage());
1777 return;
1778 }
1779 }
1780 }
1781 }
1782 rs.close();
1783 rs1.close();
1784 }
1785 }
1786 conn.rollback();
1787 conn.setAutoCommit(true);
1788
1789 System.out.println("Positive Test22 - Test updateObject method");
1790 conn.setAutoCommit(false);
1791 String displayString;
1792 for (int sqlType = 1; sqlType <= allSQLTypes.length; sqlType++ ) {
1793 conn.rollback();
1794 System.out.println("Next datatype to test is " + allSQLTypes[sqlType-1]);
1795 for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
1796 if(!HAVE_BIG_DECIMAL && (updateXXXName == 4))
1797 continue;
1798 for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
1799 if (indexOrName == 1) displayString = " updateObject with column position &";
1801 else
1802 displayString = " updateObject with column name &";
1803 rs = pstmt.executeQuery();
1804 rs.next();
1805 rs1 = pstmt1.executeQuery();
1806 rs1.next();
1807 try {
1808 if (updateXXXName == 1){ System.out.println(displayString + " Short object as parameters");
1810 if (indexOrName == 1) rs.updateObject(sqlType, new Short (rs1.getShort(updateXXXName)));
1812 else rs.updateObject(ColumnNames[sqlType-1], new Short (rs1.getShort(updateXXXName)));
1814 } else if (updateXXXName == 2){ System.out.println(displayString + " Integer object as parameters");
1816 if (indexOrName == 1) rs.updateObject(sqlType, new Integer (rs1.getInt(updateXXXName)));
1818 else rs.updateObject(ColumnNames[sqlType-1], new Integer (rs1.getInt(updateXXXName)));
1820 } else if (updateXXXName == 3){ System.out.println(displayString + " Long object as parameters");
1822 if (indexOrName == 1) rs.updateObject(sqlType, new Long (rs1.getLong(updateXXXName)));
1824 else rs.updateObject(ColumnNames[sqlType-1], new Long (rs1.getLong(updateXXXName)));
1826 } else if (updateXXXName == 4){ if(HAVE_BIG_DECIMAL) {
1828 System.out.println(displayString + " BigDecimal object as parameters");
1829 if (indexOrName == 1) rs.updateObject(sqlType, rs1.getBigDecimal(updateXXXName));
1831 else rs.updateObject(ColumnNames[sqlType-1],rs1.getBigDecimal(updateXXXName));
1833 }
1834 } else if (updateXXXName == 5){ System.out.println(displayString + " Float object as parameters");
1836 if (indexOrName == 1) rs.updateObject(sqlType, new Float (rs1.getFloat(updateXXXName)));
1838 else rs.updateObject(ColumnNames[sqlType-1], new Float (rs1.getFloat(updateXXXName)));
1840 } else if (updateXXXName == 6){ System.out.println(displayString + " Double object as parameters");
1842 if (indexOrName == 1) rs.updateObject(sqlType, new Double (rs1.getDouble(updateXXXName)));
1844 else rs.updateObject(ColumnNames[sqlType-1], new Double (rs1.getDouble(updateXXXName)));
1846 } else if (updateXXXName == 7){ System.out.println(displayString + " String object as parameters");
1848 if (indexOrName == 1) rs.updateObject(sqlType,rs1.getString(updateXXXName));
1850 else rs.updateObject(ColumnNames[sqlType-1],rs1.getString(updateXXXName));
1852 } else if (updateXXXName == 8 || updateXXXName == 12) continue;
1854 else if (updateXXXName == 9) continue;
1856 else if (updateXXXName == 10) continue;
1859 else if (updateXXXName == 11){ System.out.println(displayString + " bytes[] array as parameters");
1861 if (indexOrName == 1) rs.updateObject(sqlType,rs1.getBytes(updateXXXName));
1863 else rs.updateObject(ColumnNames[sqlType-1], rs1.getBytes(updateXXXName));
1865 } else if (updateXXXName == 13){ if (JVMInfo.JDK_ID == 2) continue;
1868 System.out.println(displayString + " Clob object as parameters");
1869 if (indexOrName == 1) rs.updateObject(sqlType, rs1.getClob(updateXXXName));
1871 else rs.updateObject(ColumnNames[sqlType-1], rs1.getClob(updateXXXName));
1873 } else if (updateXXXName == 14){ System.out.println(displayString + " Date object as parameters");
1875 if (indexOrName == 1) rs.updateObject(sqlType, rs1.getDate(updateXXXName));
1877 else rs.updateObject(ColumnNames[sqlType-1], rs1.getDate(updateXXXName));
1879 } else if (updateXXXName == 15){ System.out.println(displayString + " Time object as parameters");
1881 if (indexOrName == 1) rs.updateObject(sqlType, rs1.getTime(updateXXXName));
1883 else rs.updateObject(ColumnNames[sqlType-1], rs1.getTime(updateXXXName));
1885 } else if (updateXXXName == 16){ System.out.println(displayString + " TimeStamp object as parameters");
1887 if (indexOrName == 1) rs.updateObject(sqlType, rs1.getTimestamp(updateXXXName));
1889 else rs.updateObject(ColumnNames[sqlType-1], rs1.getTimestamp(updateXXXName));
1891 } else if (updateXXXName == 17){ if (JVMInfo.JDK_ID == 2) continue;
1894 System.out.println(displayString + " Blob object as parameters");
1895 if (indexOrName == 1) rs.updateObject(sqlType, rs1.getBlob(updateXXXName));
1897 else rs.updateObject(ColumnNames[sqlType-1], rs1.getBlob(updateXXXName));
1899 } else if (updateXXXName == 18) { System.out.println(displayString + " Boolean object as parameters");
1901 if (indexOrName == 1) rs.updateObject(sqlType, new Boolean (rs1.getBoolean(1)));
1904 else rs.updateObject(ColumnNames[sqlType-1], new Boolean (rs1.getBoolean(1)));
1906 } else if (updateXXXName == 19){ System.out.println(displayString + " null as parameters");
1908 try {
1909 if (indexOrName == 1) rs.updateObject(sqlType, null);
1911 else rs.updateObject(ColumnNames[sqlType-1], null);
1913 } catch (Throwable e) {
1914 System.out.println(" Got UNexpected exception:" + e.getMessage());
1915 return;
1916 }
1917 } else if (updateXXXName == 20 || updateXXXName == 21) continue;
1919
1920 rs.updateRow();
1921 if (TestUtil.isNetFramework() && updateXXXName == 13 &&
1922 (sqlType==7 || sqlType==8 || sqlType==9 || sqlType==13))
1923 System.out.print("");
1925 else if (TestUtil.isNetFramework() && updateXXXName == 17 &&
1926 (sqlType==12 || sqlType==17))
1927 System.out.print("");
1929 else if ((TestUtil.isNetFramework() && updateXXXRulesTableForNetworkServer[sqlType-1][updateXXXName-1].equals("ERROR")) ||
1930 (TestUtil.isEmbeddedFramework() && updateXXXRulesTableForEmbedded[sqlType-1][updateXXXName-1].equals("ERROR"))) {
1931 System.out.println("FAILURE : We shouldn't reach here. The test should have failed earlier on updateXXX or updateRow call");
1932 return;
1933 }
1934 if(!HAVE_BIG_DECIMAL && (updateXXXName == 4))
1935 continue;
1936 if (verifyData(sqlType,updateXXXName, "AllDataTypesNewValuesData") == false)
1937 {
1938 System.out.println("Test failed");
1939 return;
1940 }
1941 resetData();
1942 } catch (Throwable e) {
1943 if ((TestUtil.isNetFramework() && updateXXXRulesTableForNetworkServer[sqlType-1][updateXXXName-1].equals("ERROR")) ||
1944 (TestUtil.isEmbeddedFramework() && updateXXXRulesTableForEmbedded[sqlType-1][updateXXXName-1].equals("ERROR")))
1945 System.out.println(" Got expected exception : " + e.getMessage());
1946 else {
1947 if ((sqlType == 14 || sqlType == 15 || sqlType == 16) && updateXXXName == 7) System.out.println(" Got expected exception : " + e.getMessage());
1950 else {
1951 System.out.println(" Got UNexpected exception : " + e.getMessage());
1952 return;}
1953 }
1954 }
1955 rs.close();
1956 rs1.close();
1957 }
1958 }
1959 }
1960 conn.rollback();
1961 conn.setAutoCommit(true);
1962
1963 System.out.println("Positive Test23 - Test cancelRowUpdates after updateXXX methods on all the supported sql datatypes");
1964 conn.setAutoCommit(false);
1965 reloadAllDataTypesForTestingTableData();
1966 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
1967 stmt1 = conn.createStatement();
1968 rs = stmt.executeQuery("SELECT * FROM AllDataTypesForTestingTable FOR UPDATE");
1969 rs.next();
1970 rs1 = stmt1.executeQuery("SELECT * FROM AllDataTypesNewValuesData");
1971 rs1.next();
1972
1973 System.out.println(" updateShort and then cancelRowUpdates");
1974 short s = rs.getShort(1);
1975 rs.updateShort(1, rs1.getShort(1));
1976 if(rs.getShort(1) != rs1.getShort(1))
1977 return;
1978 rs.cancelRowUpdates();
1979 if(rs.getShort(1) != s)
1980 return;
1981
1982 System.out.println(" updateInt and then cancelRowUpdates");
1983 int i = rs.getInt(2);
1984 rs.updateInt(2, rs1.getInt(2));
1985 if(rs.getInt(2) != rs1.getInt(2))
1986 return;
1987 rs.cancelRowUpdates();
1988 if(rs.getInt(2) != i)
1989 return;
1990
1991 System.out.println(" updateLong and then cancelRowUpdates");
1992 long l = rs.getLong(3);
1993 rs.updateLong(3, rs1.getLong(3));
1994 if(rs.getLong(3) != rs1.getLong(3))
1995 return;
1996 rs.cancelRowUpdates();
1997 if(rs.getLong(3) != l)
1998 return;
1999
2000 System.out.println(" updateBigDecimal and then cancelRowUpdates");
2001 String bdString = BigDecimalHandler.getBigDecimalString(rs, 4);
2002 BigDecimalHandler.updateBigDecimalString(rs, 4,
2003 BigDecimalHandler.getBigDecimalString(rs1, 4));
2004 if(!BigDecimalHandler.getBigDecimalString(rs,4)
2005 .equals(BigDecimalHandler.getBigDecimalString(rs1, 4)))
2006 return;
2007 rs.cancelRowUpdates();
2008 if(!BigDecimalHandler.getBigDecimalString(rs, 4).equals(bdString))
2009 return;
2010
2011 System.out.println(" updateFloat and then cancelRowUpdates");
2012 float f = rs.getFloat(5);
2013 rs.updateFloat(5, rs1.getFloat(5));
2014 if(rs.getFloat(5) != rs1.getFloat(5))
2015 return;
2016 rs.cancelRowUpdates();
2017 if(rs.getFloat(5) != f)
2018 return;
2019
2020 System.out.println(" updateDouble and then cancelRowUpdates");
2021 double db = rs.getDouble(6);
2022 rs.updateDouble(6, rs1.getDouble(6));
2023 if(rs.getDouble(6) != rs1.getDouble(6))
2024 return;
2025 rs.cancelRowUpdates();
2026 if(rs.getDouble(6) != db)
2027 return;
2028
2029 System.out.println(" updateString and then cancelRowUpdates");
2030 String str = rs.getString(7);
2031 rs.updateString(7, rs1.getString(7));
2032 if(!rs.getString(7).equals(rs1.getString(7)))
2033 return;
2034 rs.cancelRowUpdates();
2035 if(!rs.getString(7).equals(str))
2036 return;
2037
2038 System.out.println(" updateAsciiStream and then cancelRowUpdates");
2039 str = rs.getString(8);
2040 rs.updateAsciiStream(8,rs1.getAsciiStream(8), 4);
2041 if(!rs.getString(8).equals(rs1.getString(8)))
2042 return;
2043 rs.cancelRowUpdates();
2044 if(!rs.getString(8).equals(str))
2045 return;
2046
2047 System.out.println(" updateCharacterStream and then cancelRowUpdates");
2048 str = rs.getString(9);
2049 rs.updateCharacterStream(9,rs1.getCharacterStream(9), 4);
2050 if(!rs.getString(9).equals(rs1.getString(9)))
2051 return;
2052 rs.cancelRowUpdates();
2053 if(!rs.getString(9).equals(str))
2054 return;
2055
2056 System.out.println(" updateByte and then cancelRowUpdates");
2057 s = rs.getShort(1);
2058 rs.updateByte(1,rs1.getByte(1));
2059 if(rs.getShort(1) != rs1.getShort(1))
2060 return;
2061 rs.cancelRowUpdates();
2062 if(rs.getShort(1) != s)
2063 return;
2064
2065 System.out.println(" updateBytes and then cancelRowUpdates");
2066 byte[] bts = rs.getBytes(11);
2067 rs.updateBytes(11,rs1.getBytes(11));
2068 if (!(java.util.Arrays.equals(rs.getBytes(11),rs1.getBytes(11))))
2069 return;
2070 rs.cancelRowUpdates();
2071 if (!(java.util.Arrays.equals(rs.getBytes(11),bts)))
2072 return;
2073
2074 System.out.println(" updateBinaryStream and then cancelRowUpdates");
2075 bts = rs.getBytes(12);
2076 rs.updateBinaryStream(12,rs1.getBinaryStream(12), 2);
2077 if (!(java.util.Arrays.equals(rs.getBytes(12),rs1.getBytes(12))))
2078 return;
2079 rs.cancelRowUpdates();
2080 if (!(java.util.Arrays.equals(rs.getBytes(12),bts)))
2081 return;
2082
2083 System.out.println(" updateDate and then cancelRowUpdates");
2084 Date date = rs.getDate(14);
2085 rs.updateDate(14,rs1.getDate(14));
2086 if(rs.getDate(14).compareTo(rs1.getDate(14)) != 0)
2087 return;
2088 rs.cancelRowUpdates();
2089 if(rs.getDate(14).compareTo(date) != 0)
2090 return;
2091
2092 System.out.println(" updateTime and then cancelRowUpdates");
2093 Time time = rs.getTime(15);
2094 rs.updateTime(15,rs1.getTime(15));
2095 if(rs.getTime(15).compareTo(rs1.getTime(15)) != 0)
2096 return;
2097 rs.cancelRowUpdates();
2098 if(rs.getTime(15).compareTo(time) != 0)
2099 return;
2100
2101 System.out.println(" updateTimestamp and then cancelRowUpdates");
2102 Timestamp timeStamp = rs.getTimestamp(16);
2103 rs.updateTimestamp(16,rs1.getTimestamp(16));
2104 if(!rs.getTimestamp(16).toString().equals(rs1.getTimestamp(16).toString()))
2105 return;
2106 rs.cancelRowUpdates();
2107 if(!rs.getTimestamp(16).toString().equals(timeStamp.toString()))
2108 return;
2109
2110 if (JVMInfo.JDK_ID != 2 && TestUtil.isEmbeddedFramework()){
2113 System.out.println(" updateClob and then cancelRowUpdates");
2114 String clb1 = rs.getString(13);
2115 rs.updateClob(13,rs1.getClob(13));
2116 if(!rs.getString(13).equals(rs1.getString(13)))
2117 return;
2118 rs.cancelRowUpdates();
2119 if(!rs.getString(13).equals(clb1))
2120 return;
2121 System.out.println(" updateBlob and then cancelRowUpdates");
2122 bts = rs.getBytes(17);
2123 rs.updateBlob(17,rs1.getBlob(17));
2124 if (!(java.util.Arrays.equals(rs.getBytes(17),rs1.getBytes(17))))
2125 return;
2126 rs.cancelRowUpdates();
2127 if (!(java.util.Arrays.equals(rs.getBytes(17),bts)))
2128 return;
2129 }
2130
2131 rs.close();
2132 rs1.close();
2133 conn.setAutoCommit(true);
2134
2135 System.out.println("Positive Test24a - after updateXXX, try cancelRowUpdates and then deleteRow");
2136 reloadData();
2137 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2138 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
2139 rs.next();
2140 System.out.println("column 1 on this row before updateInt is " + rs.getInt(1));
2141 rs.updateInt(1,234);
2142 System.out.println("column 1 on this row after updateInt is " + rs.getInt(1));
2143 System.out.println("now cancelRowUpdates on the row");
2144 rs.cancelRowUpdates();
2145 System.out.println("Since after cancelRowUpdates(), ResultSet is positioned on the same row, getXXX will pass");
2146 System.out.println("column 1 on this row after cancelRowUpdates is " + rs.getInt(1));
2147 System.out.println("Since after cancelRowUpdates(), ResultSet is positioned on the same row, a deleteRow at this point will pass");
2148 try {
2149 rs.deleteRow();
2150 System.out.println("PASS : deleteRow passed as expected");
2151 }
2152 catch (SQLException e) {
2153 dumpSQLExceptions(e);
2154 }
2155 System.out.println("calling updateRow after deleteRow w/o first positioning the ResultSet on the next row will fail");
2156 try {
2157 rs.updateRow();
2158 System.out.println("FAIL!!! updateRow should have failed because ResultSet is not positioned on a row");
2159 }
2160 catch (SQLException e) {
2161 System.out.println("SQL State : " + e.getSQLState());
2162 System.out.println("Got expected exception " + e.getMessage());
2163 }
2164 System.out.println("Position the ResultSet with next()");
2165 rs.next();
2166 System.out.println("Should be able to updateRow() on the current row now");
2167 rs.updateString(2,"234");
2168 rs.updateRow();
2169 rs.close();
2171
2172 System.out.println("Positive Test25 - issue cancelRowUpdates without any updateXXX");
2173 reloadData();
2174 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2175 rs = stmt.executeQuery("SELECT * FROM t1");
2176 rs.next();
2177 rs.cancelRowUpdates();
2178 rs.close();
2180
2181 System.out.println("Positive Test26 - issue updateRow without any updateXXX will not move the resultset position");
2182 reloadData();
2183 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2184 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
2185 rs.next();
2186 rs.updateRow(); rs.updateRow();
2188 rs.close();
2190
2191 System.out.println("Positive Test27 - issue updateXXX and then deleteRow");
2192 reloadData();
2193 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2194 rs = stmt.executeQuery("SELECT * FROM t1");
2195 rs.next();
2196 rs.updateInt(1,1234);
2197 rs.updateString(2,"aaaaa");
2198 rs.deleteRow();
2199 try {
2200 rs.updateRow();
2201 System.out.println("FAIL!!! deleteRow should have moved the ResultSet to right before the next row");
2202 }
2203 catch (SQLException e) {
2204 System.out.println("SQL State : " + e.getSQLState());
2205 System.out.println("Got expected exception " + e.getMessage());
2206 }
2207 try {
2208 rs.updateInt(1,2345);
2209 System.out.println("FAIL!!! deleteRow should have moved the ResultSet to right before the next row");
2210 }
2211 catch (SQLException e) {
2212 System.out.println("SQL State : " + e.getSQLState());
2213 System.out.println("Got expected exception " + e.getMessage());
2214 }
2215 try {
2216 rs.getInt(1);
2217 System.out.println("FAIL!!! deleteRow should have moved the ResultSet to right before the next row");
2218 }
2219 catch (SQLException e) {
2220 System.out.println("SQL State : " + e.getSQLState());
2221 System.out.println("Got expected exception " + e.getMessage());
2222 }
2223 rs.close();
2225
2226 System.out.println("Positive Test28 - issue updateXXXs and then move off the row, the changes should be ignored");
2227 reloadData();
2228 dumpRS(stmt.executeQuery("select * from t1"));
2229 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2230 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
2231 rs.next();
2232 System.out.println(" column 1 on this row before updateInt is " + rs.getInt(1));
2233 System.out.println(" Issue updateInt to change the column's value to 2345");
2234 rs.updateInt(1,2345);
2235 System.out.println(" Move to next row w/o issuing updateRow");
2236 rs.next(); rs.close();
2239 System.out.println(" Make sure that changes didn't make it to the database");
2240 dumpRS(stmt.executeQuery("select * from t1"));
2241
2242 System.out.println("Positive Test29 - issue multiple updateXXXs and then a updateRow");
2243 reloadData();
2244 dumpRS(stmt.executeQuery("select * from t1"));
2245 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2246 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
2247 rs.next();
2248 System.out.println(" column 1 on this row before updateInt is " + rs.getInt(1));
2249 System.out.println(" Issue updateInt to change the column's value to 2345");
2250 rs.updateInt(1,2345);
2251 System.out.println(" Issue another updateInt on the same row and column to change the column's value to 9999");
2252 rs.updateInt(1,9999);
2253 System.out.println(" Issue updateString to change the column's value to 'xxxxxxx'");
2254 rs.updateString(2,"xxxxxxx");
2255 System.out.println(" Now issue updateRow");
2256 rs.updateRow();
2257 rs.close();
2259 System.out.println(" Make sure that changes made it to the database correctly");
2260 dumpRS(stmt.executeQuery("select * from t1"));
2261
2262 System.out.println("Positive Test30 - call updateXXX methods on only columns that correspond to a column in the table");
2263 dumpRS(stmt.executeQuery("select * from t1"));
2264 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2265 rs = stmt.executeQuery("SELECT 1, 2, c1, c2 FROM t1");
2266 rs.next();
2267 rs.updateInt(3,22);
2268 rs.updateRow();
2269 rs.close();
2270 System.out.println(" Make sure that changes made it to the database correctly");
2271 dumpRS(stmt.executeQuery("select * from t1"));
2272
2273 System.out.println("Positive Test31a - case sensitive table and column names");
2274 stmt.executeUpdate("create table \"t1\" (\"c11\" int, c12 int)");
2275 stmt.executeUpdate("insert into \"t1\" values(1, 2), (2,3)");
2276 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2277 rs = stmt.executeQuery("SELECT \"c11\", \"C12\" FROM \"t1\" FOR UPDATE");
2278 rs.next();
2279 rs.updateInt(1,11);
2280 rs.updateInt(2,22);
2281 rs.updateRow();
2282 rs.next();
2283 rs.deleteRow();
2284 rs.close();
2285 System.out.println(" Make sure that changes made it to the database correctly");
2286 dumpRS(stmt.executeQuery("select * from \"t1\""));
2287
2288 System.out.println("Positive Test31b - table and column names with spaces in middle and end");
2289 stmt.executeUpdate("create table \" t 11 \" (\" c 111 \" int, c112 int)");
2290 stmt.executeUpdate("insert into \" t 11 \" values(1, 2), (2,3)");
2291 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2292 rs = stmt.executeQuery("SELECT \" c 111 \", \"C112\" FROM \" t 11 \" ");
2293 rs.next();
2294 rs.updateInt(1,11);
2295 rs.updateInt(2,22);
2296 rs.updateRow();
2297 rs.next();
2298 rs.deleteRow();
2299 rs.close();
2300 System.out.println(" Make sure for table \" t 11 \" that changes made it to the database correctly");
2301 dumpRS(stmt.executeQuery("select * from \" t 11 \""));
2302
2303 System.out.println("Positive Test32 - call updateXXX methods on column that is not in for update columns list");
2304 dumpRS(stmt.executeQuery("select * from t1"));
2305 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2306 rs = stmt.executeQuery("SELECT c1, c2 FROM t1 FOR UPDATE of c1");
2307 rs.next();
2308 try {
2309 rs.updateInt(2,22);
2310 if (TestUtil.isEmbeddedFramework())
2311 System.out.println("PASS!!! Embedded throws exception for updateRow");
2312 else
2313 System.out.println("FAIL!!! Network Server should throw exception for updateXXX");
2314 }
2315 catch (SQLException e) {
2316 System.out.println("SQL State : " + e.getSQLState());
2317 System.out.println("Got expected exception " + e.getMessage());
2318 }
2319 try {
2320 rs.updateRow();
2321 System.out.println("updateRow passed");
2322 }
2323 catch (SQLException e) {
2324 if (TestUtil.isNetFramework())
2325 System.out.println("FAIL!!! updateRow w/o updateXXX is no-op in Network Server");
2326 else
2327 System.out.println("FAIL!!! exception is " + e.getMessage());
2328 }
2329 rs.close();
2330 System.out.println(" Make sure the contents of table are unchanged");
2331 dumpRS(stmt.executeQuery("select * from t1"));
2332
2333 System.out.println("Positive Test33 - try to update a table from another schema");
2334 System.out.println(" contents of table t1 from current schema");
2335 dumpRS(stmt.executeQuery("select * from t1"));
2336 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2337 stmt.executeUpdate("create schema s2");
2338 stmt.executeUpdate("create table s2.t1 (c1s2t1 int, c2s2t1 smallint, c3s2t2 double)");
2339 stmt.executeUpdate("insert into s2.t1 values(1,2,2.2),(1,3,3.3)");
2340 System.out.println(" contents of table t1 from schema s2");
2341 dumpRS(stmt.executeQuery("select * from s2.t1"));
2342 System.out.println(" Try to change contents of 2nd column of s2.t1 using updateRow");
2343 rs = stmt.executeQuery("SELECT * FROM s2.t1 FOR UPDATE");
2344 rs.next();
2345 rs.updateInt(2,1);
2346 rs.updateRow();
2347 rs.next();
2348 rs.updateInt(2,1);
2349 rs.updateRow();
2350 rs.close();
2351 System.out.println(" Make sure that changes made to the right table t1");
2352 System.out.println(" contents of table t1 from current schema should have remained unchanged");
2353 dumpRS(stmt.executeQuery("select * from t1"));
2354 System.out.println(" contents of table t1 from schema s2 should have changed");
2355 dumpRS(stmt.executeQuery("select * from s2.t1"));
2356
2357 System.out.println("Positive Test34 - in autocommit mode, check that updateRow and deleteRow does not commit");
2358 conn.setAutoCommit(true);
2359
2360 reloadData();
2362 System.out.println(" Contents before changes to first row in RS:");
2363 dumpRS(stmt.executeQuery("select * from t1"));
2364 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2365 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
2366 rs.next();
2367 rs.deleteRow();
2368 conn.rollback();
2369 rs.close();
2370 System.out.println(" Make sure the contents of table are unchanged:");
2371 dumpRS(stmt.executeQuery("select * from t1"));
2372 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2373 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
2374 rs.next();
2375 rs.updateInt(1,-rs.getInt(1));
2376 rs.updateRow();
2377 conn.rollback();
2378 rs.close();
2379 System.out.println(" Make sure the contents of table are unchanged:");
2380 dumpRS(stmt.executeQuery("select * from t1"));
2381
2382 reloadData();
2384 stmt = conn.createStatement();
2385 rs = stmt.executeQuery("SELECT COUNT(*) FROM t1");
2386 rs.next();
2387 int count = rs.getInt(1);
2388 rs.close();
2389
2390 System.out.println(" Contents before changes to last row in RS:");
2391 dumpRS(stmt.executeQuery("select * from t1"));
2392 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2393 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
2394 for (int j = 0; j < count; j++) {
2395 rs.next();
2396 }
2397 rs.deleteRow();
2398 conn.rollback();
2399 rs.close();
2400 System.out.println(" Make sure the contents of table are unchanged:");
2401 dumpRS(stmt.executeQuery("select * from t1"));
2402
2403 stmt = conn.createStatement();
2404 rs = stmt.executeQuery("SELECT COUNT(*) FROM t1");
2405 rs.next();
2406 count = rs.getInt(1);
2407 rs.close();
2408
2409 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2410 rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
2411 for (int j = 0; j < count; j++) {
2412 rs.next();
2413 }
2414 rs.updateInt(1,-rs.getInt(1));
2415 rs.updateRow();
2416 conn.rollback();
2417 rs.close();
2418 System.out.println(" Make sure the contents of table are unchanged:");
2419 dumpRS(stmt.executeQuery("select * from t1"));
2420
2421 stmt.close();
2422 reloadData();
2423
2424 int c41, c42, c41old, c42old;
2426 conn.setAutoCommit(false);
2427 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2428
2429 System.out.println("Positive Test 35 - moveToInsertRow, insertRow," +
2430 "getXXX and moveToCurrentRow");
2431 rs = stmt.executeQuery("SELECT * FROM t4");
2432 rs.next();
2433 c41old = rs.getInt(1);
2435 c42old = rs.getInt(2);
2436 System.out.println("Positive Test 35.a - moveToInsertRow");
2437 rs.moveToInsertRow();
2438 rs.updateInt(1, 4);
2439 rs.updateInt(2, 4);
2440 System.out.println("Positive Test 35.b - insertRow");
2441 try {
2442 rs.insertRow();
2443 } catch (Throwable t) {
2444 System.out.println("Error " + t.getMessage());
2445 }
2446 System.out.println("Positive Test 35.c - check that getXXX gets the " +
2447 "correct values after insertRow");
2448 c41 = rs.getInt(1);
2449 c42 = rs.getInt(2);
2450 if ((c41 != 4) || (c42 != 4)) {
2451 System.out.println("getXXX failed after insertRow");
2452 }
2453 System.out.println("Positive Test 35.d - moveToCurrentRow");
2454 rs.moveToCurrentRow();
2455 System.out.println("Positive Test 35.e - check that getXXX gets the " +
2456 "correct values after moveToCurrentRow");
2457 if (c41old != rs.getInt(1) || c42old != rs.getInt(2)) {
2458 System.out.println("rs positioned on wrong row after moveToCurrentRow");
2459 }
2460
2461 System.out.println("Positive test 36 - call moveToCurrentRow from current row");
2462 rs.moveToCurrentRow();
2463 if (c41old != rs.getInt(1) || c42old != rs.getInt(2)) {
2464 System.out.println("rs positioned on wrong row after second moveToCurrentRow");
2465 }
2466
2467 System.out.println("Positive test 37 - several moveToInsertRow");
2468 System.out.println("Positive test 37.a - check that getXXX gets the " +
2469 "correct values after moveToInsertRow");
2470 rs.moveToInsertRow();
2471 rs.updateInt(1, 5);
2472 rs.updateInt(2, 4);
2473 c41 = rs.getInt(1);
2474 c42 = rs.getInt(2);
2475 if (c41 != 5 || c42 != 4) {
2476 System.out.println("Got wrong value for columns");
2477 }
2478 System.out.println("Positive test 37.b - moveToinsertRow from " +
2479 "insertRow");
2480 rs.moveToInsertRow();
2481 System.out.println("Positive test 37.c - check that getXXX gets " +
2482 "undefined values when updateXXX has not been called yet " +
2483 "on insertRow");
2484 c41 = rs.getInt(1);
2485 if (!rs.wasNull() || c41 != 0) {
2486 System.out.println("c41 should have been set to NULL after second " +
2487 "moveToInsertRow");
2488 }
2489 c42 = rs.getInt(2);
2490 if (!rs.wasNull() || c42 != 0) {
2491 System.out.println("c42 should have been set to NULL after second " +
2492 "moveToInsertRow");
2493 }
2494
2495 System.out.println("Negative Test 38 - insertRow: do not set a value " +
2496 "to all not nullable columns");
2497 rs.moveToInsertRow();
2498 rs.updateInt(2, 5);
2500 try {
2501 rs.insertRow();
2502 System.out.println("Should not have gotten here");
2503 } catch (SQLException se) {
2504 dumpExpectedSQLException(se);
2505 }
2506
2507 System.out.println("Negative Test 39 - run updateRow and deleterow " +
2508 "when positioned at insertRow");
2509 rs.moveToInsertRow();
2510 rs.updateInt(1, 6);
2511 rs.updateInt(2, 6);
2512 try {
2513 System.out.println("Negative Test 39.a - run updateRow on " +
2514 "insertRow");
2515 rs.updateRow();
2516 System.out.println("Never get here, updateRow not allowed from insertRow");
2517 } catch (SQLException se) {
2518 dumpExpectedSQLException(se);
2519 }
2520 try {
2521 System.out.println("Negative Test 39.a - run deleteRow on " +
2522 "insertRow");
2523 rs.deleteRow();
2524 System.out.println("Never get here, deleteRow not allowed from insertRow");
2525 } catch (SQLException se) {
2526 dumpExpectedSQLException(se);
2527 }
2528
2529 System.out.println("Negative test 40 - Try to insert row from currentRow");
2530 rs.moveToCurrentRow();
2531 try {
2532 rs.insertRow();
2533 System.out.println("Should not get here, insertRow should fail " +
2534 "when cursor is not positioned on InsertRow.");
2535 } catch (SQLException se) {
2536 dumpExpectedSQLException(se);
2537 }
2538
2539 System.out.println("Positive test 41 - try to insertRow from all " +
2540 "posible positions");
2541 rs = stmt.executeQuery("SELECT * FROM t4 WHERE c41 <= 5");
2542 rs.moveToInsertRow();
2543 rs.updateInt(1, 1000);
2544 rs.updateInt(2, 1000);
2545 rs.insertRow();
2546 while (rs.next()) {
2547 c41 = rs.getInt(1);
2548 c42 = rs.getInt(2);
2549 rs.moveToInsertRow();
2550 rs.updateInt(1, c41 + 100);
2551 rs.updateInt(2, c42 + 100);
2552 rs.insertRow();
2553 }
2554 rs.moveToInsertRow();
2555 rs.updateInt(1, 2000);
2556 rs.updateInt(2, 2000);
2557 rs.insertRow();
2558
2559 System.out.println("Positive test 42 - InsertRow leaving a nullable " +
2560 "columns = NULL");
2561 rs.moveToInsertRow();
2562 rs.updateInt(1, 7);
2563 rs.insertRow();
2564
2565 rs.close();
2566 stmt.close();
2567
2568 System.out.println("Positive and negative tests 43 - Commit while on insertRow");
2569 try {
2570 conn.setHoldability(ResultSet.HOLD_CURSORS_OVER_COMMIT);
2571 } catch (Throwable e) {
2572 if (JVMInfo.JDK_ID <= 2) {
2573 System.out.println("This exception is expected with jdk 1.3: " +
2574 "holdability not supported with jdk131 /ibm131 based jvms");
2575
2576 } else {
2577 System.out.println("Got unexpected exception: " + e.getMessage());
2578 }
2579 }
2580 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2581 System.out.println("Positive test 43 - Commit while on insertRow " +
2582 "with holdable cursor");
2583 rs = stmt.executeQuery("SELECT * FROM t4");
2584 rs.next();
2585 rs.moveToInsertRow();
2586 rs.updateInt(1, 8);
2587 rs.updateInt(2, 8);
2588 conn.commit();
2589 try {
2590 rs.insertRow();
2591 } catch (SQLException se){
2592 dumpSQLExceptions(se);
2593 }
2594 rs.close();
2595 stmt.close();
2596 try {
2597 conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
2598 } catch (Throwable e) {
2599 if (JVMInfo.JDK_ID <= 2) {
2600 System.out.println("This exception is expected with jdk 1.3: " +
2601 "holdability not supported with jdk131 /ibm131 based jvms");
2602 } else {
2603 System.out.println("Got unexpected exception: " + e.getMessage());
2604 }
2605 }
2606 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2607 System.out.println("Negative test 43 - Commit while on insertRow " +
2608 "with not holdable cursor");
2609 rs = stmt.executeQuery("SELECT * FROM t4");
2610 rs.next();
2611 rs.moveToInsertRow();
2612 rs.updateInt(1, 82);
2613 rs.updateInt(2, 82);
2614 conn.commit();
2615 try {
2616 rs.insertRow();
2617 } catch (SQLException se){
2618 dumpExpectedSQLException(se);
2619 }
2620 rs.close();
2621
2622 System.out.println("Negative test 44 - Closed RS");
2623 rs = stmt.executeQuery("SELECT * FROM t4");
2624 rs.next();
2625 rs.moveToInsertRow();
2626 rs.updateInt(1, 9);
2627 rs.updateInt(2, 9);
2628 rs.close();
2629 System.out.println("Negative test 44.a - try insertRow on closed RS");
2630 try {
2631 rs.insertRow();
2632 System.out.println("FAIL: insertRow can not be called on " +
2633 "closed RS");
2634 } catch (SQLException se){
2635 dumpExpectedSQLException(se);
2636 }
2637 System.out.println("Negative test 44.b - try moveToCurrentRow on " +
2638 "closed RS");
2639 try {
2640 rs.moveToCurrentRow();
2641 System.out.println("FAIL: moveToCurrentRow can not be called on " +
2642 "closed RS");
2643 } catch (SQLException se){
2644 dumpExpectedSQLException(se);
2645 }
2646 System.out.println("Negative test 44.c - try moveToInsertRow on " +
2647 "closed RS");
2648 try {
2649 rs.moveToInsertRow();
2650 System.out.println("FAIL: moveToInsertRow can not be called on " +
2651 "closed RS");
2652 } catch (SQLException se){
2653 dumpExpectedSQLException(se);
2654 }
2655
2656 System.out.println("Positive test 45 - try to insert without " +
2657 "updating all columns. All columns allow nulls or have a " +
2658 "default value");
2659 rs = stmt.executeQuery("SELECT * FROM t5");
2660 rs.next();
2661 rs.moveToInsertRow();
2662 try {
2663 rs.insertRow();
2665 } catch (SQLException se){
2666 dumpSQLExceptions(se);
2667 }
2668
2669 rs.close();
2670 conn.commit();
2671
2672 System.out.println("Positive test 46 - Rollback with AutoCommit on");
2673 conn.setAutoCommit(true);
2674 rs = stmt.executeQuery("SELECT * FROM t4");
2675 rs.next();
2676 rs.moveToInsertRow();
2677 rs.updateInt(1, 4000);
2678 rs.updateInt(2, 4000);
2679 rs.insertRow();
2680 conn.rollback();
2681 stmt.close();
2682
2683 System.out.println("Negative test 47 - insertRow and read-only RS");
2684 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
2685 ResultSet.CONCUR_READ_ONLY);
2686 rs = stmt.executeQuery("SELECT * FROM t4");
2687 System.out.println("Negative test 47.a - try moveToInsertRow on " +
2688 "read-only RS");
2689 try {
2690 rs.moveToInsertRow();
2691 System.out.println("FAIL: moveToInsertRow can not be called on " +
2692 "read-only RS");
2693 } catch (SQLException se){
2694 dumpExpectedSQLException(se);
2695 }
2696 System.out.println("Negative test 47.b - try updateXXX on " +
2697 "read-only RS");
2698 try {
2699 rs.updateInt(1, 5000);
2700 System.out.println("FAIL: updateXXX not allowed on read-only RS");
2701 rs.updateInt(2, 5000);
2702 System.out.println("FAIL: updateXXX not allowed on read-only RS");
2703 } catch (SQLException se){
2704 dumpExpectedSQLException(se);
2705 }
2706 System.out.println("Negative test 47.c - try insertRow on " +
2707 "read-only RS");
2708 try {
2709 rs.insertRow();
2710 System.out.println("FAIL: insertRow not allowed on read-only RS");
2711 } catch (SQLException se){
2712 dumpExpectedSQLException(se);
2713 }
2714 System.out.println("Negative test 47.d - try moveToCurrentRow on " +
2715 "read-only RS");
2716 try {
2717 rs.moveToCurrentRow();
2718 System.out.println("FAIL: moveToCurrentRow can not be called on " +
2719 "read-only RS");
2720 } catch (SQLException se){
2721 dumpExpectedSQLException(se);
2722 }
2723 rs.close();
2724 conn.commit();
2725 stmt.close();
2726
2727 System.out.println("Positive test 48 - Test all updateXXX methods on " +
2728 "all the supported sql datatypes");
2729 conn.setAutoCommit(false);
2730 stmt = conn.createStatement();
2731 stmt.executeUpdate("DELETE FROM AllDataTypesForTestingTable");
2732 conn.commit();
2733 PreparedStatement pstmti = conn.prepareStatement("SELECT * FROM AllDataTypesForTestingTable FOR UPDATE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
2734 PreparedStatement pstmt1i = conn.prepareStatement("SELECT * FROM AllDataTypesNewValuesData");
2735 for (int sqlType = 1, checkAgainstColumn = 1; sqlType <= allSQLTypes.length; sqlType++ ) {
2736 System.out.println("Next datatype to test is " + allSQLTypes[sqlType-1]);
2737 for (int updateXXXName = 1; updateXXXName <= allUpdateXXXNames.length; updateXXXName++) {
2738 checkAgainstColumn = updateXXXName;
2739 System.out.println(" Testing " + allUpdateXXXNames[updateXXXName-1] + " on SQL type " + allSQLTypes[sqlType-1]);
2740 for (int indexOrName = 1; indexOrName <= 2; indexOrName++) {
2741 if (indexOrName == 1) System.out.println(" Using column position as first parameter to " + allUpdateXXXNames[updateXXXName-1]);
2743 else
2744 System.out.println(" Using column name as first parameter to " + allUpdateXXXNames[updateXXXName-1]);
2745 rs = pstmti.executeQuery();
2746 rs.moveToInsertRow();
2747 rs1 = pstmt1i.executeQuery();
2748 rs1.next();
2749 try {
2750 if (updateXXXName == 1) { if (indexOrName == 1) rs.updateShort(sqlType, rs1.getShort(updateXXXName));
2753 else rs.updateShort(ColumnNames[sqlType-1], rs1.getShort(updateXXXName));
2755 } else if (updateXXXName == 2){ if (indexOrName == 1) rs.updateInt(sqlType, rs1.getInt(updateXXXName));
2758 else rs.updateInt(ColumnNames[sqlType-1], rs1.getInt(updateXXXName));
2760 } else if (updateXXXName == 3){ if (indexOrName == 1) rs.updateLong(sqlType, rs1.getLong(updateXXXName));
2763 else rs.updateLong(ColumnNames[sqlType-1], rs1.getLong(updateXXXName));
2765 } else if (updateXXXName == 4){ if (indexOrName == 1) rs.updateBigDecimal(sqlType, rs1.getBigDecimal(updateXXXName));
2768 else rs.updateBigDecimal(ColumnNames[sqlType-1], rs1.getBigDecimal(updateXXXName));
2770 } else if (updateXXXName == 5){ if (indexOrName == 1) rs.updateFloat(sqlType, rs1.getFloat(updateXXXName));
2773 else rs.updateFloat(ColumnNames[sqlType-1], rs1.getFloat(updateXXXName));
2775 } else if (updateXXXName == 6){ if (indexOrName == 1) rs.updateDouble(sqlType, rs1.getDouble(updateXXXName));
2778 else rs.updateDouble(ColumnNames[sqlType-1], rs1.getDouble(updateXXXName));
2780 } else if (updateXXXName == 7){ if (indexOrName == 1) rs.updateString(sqlType, rs1.getString(updateXXXName));
2783 else rs.updateString(ColumnNames[sqlType-1], rs1.getString(updateXXXName));
2785 } else if (updateXXXName == 8){ if (indexOrName == 1) rs.updateAsciiStream(sqlType,rs1.getAsciiStream(updateXXXName), 4);
2788 else rs.updateAsciiStream(ColumnNames[sqlType-1],rs1.getAsciiStream(updateXXXName), 4);
2790 } else if (updateXXXName == 9){ if (indexOrName == 1) rs.updateCharacterStream(sqlType,rs1.getCharacterStream(updateXXXName), 4);
2793 else rs.updateCharacterStream(ColumnNames[sqlType-1],rs1.getCharacterStream(updateXXXName), 4);
2795 } else if (updateXXXName == 10){ checkAgainstColumn = 1;
2797 if (indexOrName == 1) rs.updateByte(sqlType,rs1.getByte(checkAgainstColumn));
2799 else rs.updateByte(ColumnNames[sqlType-1],rs1.getByte(checkAgainstColumn));
2801 } else if (updateXXXName == 11){ if (indexOrName == 1) rs.updateBytes(sqlType,rs1.getBytes(updateXXXName));
2804 else rs.updateBytes(ColumnNames[sqlType-1],rs1.getBytes(updateXXXName));
2806 } else if (updateXXXName == 12){ if (indexOrName == 1) rs.updateBinaryStream(sqlType,rs1.getBinaryStream(updateXXXName), 2);
2809 else rs.updateBinaryStream(ColumnNames[sqlType-1],rs1.getBinaryStream(updateXXXName), 2);
2811 } else if (updateXXXName == 13){ if (JVMInfo.JDK_ID == 2) continue;
2814 if (indexOrName == 1) rs.updateClob(sqlType,rs1.getClob(updateXXXName));
2816 else rs.updateClob(ColumnNames[sqlType-1],rs1.getClob(updateXXXName));
2818 } else if (updateXXXName == 14){ if (indexOrName == 1) rs.updateDate(sqlType,rs1.getDate(updateXXXName));
2821 else rs.updateDate(ColumnNames[sqlType-1],rs1.getDate(updateXXXName));
2823 } else if (updateXXXName == 15){ if (indexOrName == 1) rs.updateTime(sqlType,rs1.getTime(updateXXXName));
2826 else rs.updateTime(ColumnNames[sqlType-1],rs1.getTime(updateXXXName));
2828 } else if (updateXXXName == 16){ if (indexOrName == 1) rs.updateTimestamp(sqlType,rs1.getTimestamp(updateXXXName));
2831 else rs.updateTimestamp(ColumnNames[sqlType-1],rs1.getTimestamp(updateXXXName));
2833 } else if (updateXXXName == 17){ if (JVMInfo.JDK_ID == 2) continue;
2836 if (indexOrName == 1) rs.updateBlob(sqlType,rs1.getBlob(updateXXXName));
2838 else rs.updateBlob(ColumnNames[sqlType-1],rs1.getBlob(updateXXXName));
2840 } else if (updateXXXName == 18){ if (indexOrName == 1) rs.updateBoolean(sqlType, rs1.getBoolean(1));
2845 else rs.updateBoolean(ColumnNames[sqlType-1], rs1.getBoolean(1));
2847 } else if (updateXXXName == 19){ if (indexOrName == 1) rs.updateNull(sqlType);
2850 else rs.updateNull(ColumnNames[sqlType-1]);
2852 } else if (updateXXXName == 20){ if (JVMInfo.JDK_ID == 2) continue;
2855 if (indexOrName == 1) rs.updateArray(sqlType, null);
2857 else rs.updateArray(ColumnNames[sqlType-1], null);
2859 } else if (updateXXXName == 21){ if (JVMInfo.JDK_ID == 2) continue;
2862 if (indexOrName == 1) rs.updateRef(sqlType, null);
2864 else rs.updateRef(ColumnNames[sqlType-1], null);
2866 }
2867 rs.insertRow();
2868 if ((TestUtil.isNetFramework() && updateXXXRulesTableForNetworkServer[sqlType-1][updateXXXName-1].equals("ERROR")) ||
2869 (TestUtil.isEmbeddedFramework() && updateXXXRulesTableForEmbedded[sqlType-1][updateXXXName-1].equals("ERROR"))) {
2870 System.out.println("FAILURE : We shouldn't reach here. The test should have failed earlier on updateXXX or updateRow call");
2871 return;
2872 }
2873 if (!verifyData(sqlType,checkAgainstColumn, "AllDataTypesNewValuesData"))
2874 {
2875 System.out.println("Verify data failed\nTest failed");
2876 return;
2877 }
2878 stmt.executeUpdate("DELETE FROM AllDataTypesForTestingTable");
2879 } catch (Throwable e) {
2880 if ((TestUtil.isNetFramework() && updateXXXRulesTableForNetworkServer[sqlType-1][updateXXXName-1].equals("ERROR")) ||
2881 (TestUtil.isEmbeddedFramework() && updateXXXRulesTableForEmbedded[sqlType-1][updateXXXName-1].equals("ERROR")))
2882 System.out.println(" Got expected exception : " + e.getMessage());
2883 else {
2884 if ((sqlType == 14 || sqlType == 15 || sqlType == 16) && checkAgainstColumn == 7) System.out.println(" Got expected exception : " + e.getMessage());
2887 else {
2888 System.out.println(" Got UNexpected exception : " + e.getMessage());
2889 return;
2890 }
2891 }
2892 }
2893 }
2894 rs.close();
2895 rs1.close();
2896 }
2897 }
2898 conn.rollback();
2899 conn.setAutoCommit(true);
2900
2901 dumpRS(stmt.executeQuery("select * from t4"));
2903 dumpRS(stmt.executeQuery("select * from t5"));
2904
2905 stmt.close();
2906
2907 teardown();
2908
2909 conn.close();
2910
2911
2912 } catch (Throwable e) {
2913 System.out.println("FAIL: exception thrown:");
2914 JDBCDisplayUtil.ShowException(System.out,e);
2915 }
2916
2917 System.out.println("Finished testing updateable resultsets");
2918 }
2919
2920 static boolean verifyData(int sqlType, int updateXXXName, String checkAgainstTheTable) throws SQLException {
2921 PreparedStatement pstmt1 = conn.prepareStatement("select * from " + checkAgainstTheTable);
2922 ResultSet rs1 = pstmt1.executeQuery();
2923 rs1.next();
2924 PreparedStatement pstmt = conn.prepareStatement("select * from AllDataTypesForTestingTable");
2925 ResultSet rs = pstmt.executeQuery();
2926 rs.next();
2927
2928 if (updateXXXName == 18){ if(rs.getBoolean(sqlType) != rs1.getBoolean(1))
2930 return(false);
2931 else
2932 return(true);
2933 }
2934
2935 if (updateXXXName == 19){ if(rs.getObject(sqlType) == null && rs.wasNull())
2937 return(true);
2938 else
2939 return(false);
2940 }
2941
2942 if (sqlType == 1) { if(rs.getShort(sqlType) != rs1.getShort(updateXXXName)) {
2944 return(false); }
2945 } else if (sqlType == 2) { if(rs.getInt(sqlType) != rs1.getInt(updateXXXName)) {
2947 return(false); }
2948 } else if (sqlType == 3) if(rs.getLong(sqlType) != rs1.getLong(updateXXXName)) {
2950 return(false); }
2951 else if (sqlType == 4) if(BigDecimalHandler.getBigDecimalString(rs, sqlType) !=
2953 BigDecimalHandler.getBigDecimalString(rs1, updateXXXName)) {
2954 return(false); }
2955 else if (sqlType == 5) if(rs.getFloat(sqlType) != rs1.getFloat(updateXXXName)) {
2957 return(false); }
2958 else if (sqlType == 6) if(rs.getDouble(sqlType) != rs1.getDouble(updateXXXName)) {
2960 return(false); }
2961 else if (sqlType == 7 || sqlType == 8 || sqlType == 9) if(!rs.getString(sqlType).equals(rs1.getString(updateXXXName))) {
2963 return(false); }
2964 else if (sqlType == 10 || sqlType == 11 || sqlType == 12) if(rs.getBytes(sqlType) != rs1.getBytes(updateXXXName)) {
2966 return(false); }
2967 else if (sqlType == 13 && JVMInfo.JDK_ID != 2) if(!rs.getClob(sqlType).getSubString(1,4).equals(rs1.getClob(updateXXXName).getSubString(1,4))) {
2969 return(false); }
2970 else if (sqlType == 14) if(rs.getDate(sqlType) != rs1.getDate(updateXXXName)) {
2972 return(false); }
2973 else if (sqlType == 15) { if(rs.getTime(sqlType) != rs1.getTime(updateXXXName)) {
2975 return(false); }
2976 } else if (sqlType == 16) { if(!rs.getTimestamp(sqlType).equals(rs1.getTimestamp(updateXXXName))) {
2979 return(false); }
2980 } else if (sqlType == 17 && JVMInfo.JDK_ID != 2) if(rs.getBlob(sqlType).getBytes(1,4) != rs1.getBlob(updateXXXName).getBytes(1,4)) {
2982 return(false); }
2983
2984 rs.close();
2985 rs1.close();
2986 pstmt.close();
2987 pstmt1.close();
2988 return(true);
2989 }
2990
2991 static void resetData() throws SQLException {
2992 Statement stmt = conn.createStatement();
2993 stmt.executeUpdate("delete from AllDataTypesForTestingTable");
2994 StringBuffer insertSQL = new StringBuffer ("insert into AllDataTypesForTestingTable values(");
2995 for (int type = 0; type < allSQLTypes.length - 1; type++)
2996 {
2997 insertSQL.append(SQLData[type][0] + ",");
2998 }
2999 insertSQL.append("cast("+SQLData[allSQLTypes.length - 1][0]+" as BLOB(1K)))");
3000 stmt.executeUpdate(insertSQL.toString());
3001 }
3002
3003 public static void dumpRS(ResultSet s) throws SQLException
3005 {
3006 if (s == null)
3007 {
3008 System.out.println("<NULL>");
3009 return;
3010 }
3011
3012 ResultSetMetaData rsmd = s.getMetaData();
3013
3014 int numCols = rsmd.getColumnCount();
3016
3017 if (numCols <= 0)
3018 {
3019 System.out.println("(no columns!)");
3020 return;
3021 }
3022
3023 StringBuffer heading = new StringBuffer ("\t ");
3024 StringBuffer underline = new StringBuffer ("\t ");
3025
3026 int len;
3027 for (int i=1; i<=numCols; i++)
3029 {
3030 if (i > 1)
3031 {
3032 heading.append(",");
3033 underline.append(" ");
3034 }
3035 len = heading.length();
3036 heading.append(rsmd.getColumnLabel(i));
3037 len = heading.length() - len;
3038 for (int j = len; j > 0; j--)
3039 {
3040 underline.append("-");
3041 }
3042 }
3043 System.out.println(heading.toString());
3044 System.out.println(underline.toString());
3045
3046
3047 StringBuffer row = new StringBuffer ();
3048 while (s.next())
3050 {
3051 row.append("\t{");
3052 for (int i=1; i<=numCols; i++)
3055 {
3056 if (i > 1) row.append(",");
3057 row.append(s.getString(i));
3058 }
3059 row.append("}\n");
3060 }
3061 System.out.println(row.toString());
3062 s.close();
3063 }
3064
3065 static void reloadAllDataTypesForTestingTableData() throws SQLException {
3066 Statement stmt = conn.createStatement();
3067 stmt.executeUpdate("delete from t1");
3068 stmt.executeUpdate("delete from AllDataTypesForTestingTable");
3069 StringBuffer insertSQL = new StringBuffer ("insert into AllDataTypesForTestingTable values(");
3070 for (int type = 0; type < allSQLTypes.length - 1; type++)
3071 insertSQL.append(SQLData[type][0] + ",");
3072 insertSQL.append("cast("+SQLData[allSQLTypes.length - 1][0]+" as BLOB(1K)))");
3073 stmt.executeUpdate(insertSQL.toString());
3074 }
3075
3076 static void reloadData() throws SQLException {
3077 Statement stmt = conn.createStatement();
3078 stmt.executeUpdate("delete from t1");
3079 stmt.executeUpdate("insert into t1 values (1,'aa'), (2,'bb'), (3,'cc')");
3080 stmt.executeUpdate("delete from t3");
3081 stmt.executeUpdate("insert into t3 values (1,1), (2,2)");
3082 stmt.executeUpdate("delete from t4");
3083 stmt.executeUpdate("insert into t4 values (1,1), (2,2), (3,3)");
3084 stmt.executeUpdate("delete from t5");
3085 stmt.executeUpdate("insert into t5 values (1,1), (2,2), (3,3)");
3086 stmt.executeUpdate("delete from table0WithTriggers");
3087 stmt.executeUpdate("insert into table0WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
3088 stmt.executeUpdate("delete from table1WithTriggers");
3089 stmt.executeUpdate("insert into table1WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
3090 stmt.executeUpdate("delete from table2WithTriggers");
3091 stmt.executeUpdate("insert into table2WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
3092 stmt.executeUpdate("delete from deleteTriggerInsertIntoThisTable");
3093 stmt.executeUpdate("delete from updateTriggerInsertIntoThisTable");
3094 }
3095
3096 static void setup(boolean first) throws SQLException {
3097 Statement stmt = conn.createStatement();
3098 stmt.executeUpdate("create table t1 (c1 int, c2 char(20))");
3099 stmt.executeUpdate("create view v1 as select * from t1");
3100 stmt.executeUpdate("create table t2 (c21 int, c22 int)");
3101 stmt.executeUpdate("create table t3 (c31 int not null primary key, c32 smallint)");
3102 stmt.executeUpdate("create table t4 (c41 int not null primary key, c42 int)");
3103 stmt.executeUpdate("create table t5 (c51 int not null default 0, c52 int)");
3104 stmt.executeUpdate("create table tableWithPrimaryKey (c1 int not null, c2 int not null, constraint pk primary key(c1,c2))");
3105 stmt.executeUpdate("create table tableWithConstraint (c1 int, c2 int, constraint fk foreign key(c1,c2) references tableWithPrimaryKey)");
3106 stmt.executeUpdate("create table table0WithTriggers (c1 int, c2 bigint)");
3107 stmt.executeUpdate("create table deleteTriggerInsertIntoThisTable (c1 int)");
3108 stmt.executeUpdate("create table updateTriggerInsertIntoThisTable (c1 int)");
3109 stmt.executeUpdate("create trigger tr1 after delete on table0WithTriggers for each statement mode db2sql insert into deleteTriggerInsertIntoThisTable values (1)");
3110 stmt.executeUpdate("create trigger tr2 after update on table0WithTriggers for each statement mode db2sql insert into updateTriggerInsertIntoThisTable values (1)");
3111 stmt.executeUpdate("create table table1WithTriggers (c1 int, c2 bigint)");
3112 stmt.executeUpdate("create trigger tr3 after delete on table1WithTriggers referencing old as old for each row mode db2sql delete from table1WithTriggers where c1=old.c1+1 or c1=old.c1-1");
3113 stmt.executeUpdate("create table table2WithTriggers (c1 int, c2 bigint)");
3114 stmt.executeUpdate("create trigger tr4 after update of c2 on table2WithTriggers for each statement mode db2sql update table2WithTriggers set c1=1");
3115 stmt.executeUpdate("create table selfReferencingT1 (c1 char(2) not null, c2 char(2), constraint selfReferencingT1 primary key(c1), constraint manages1 foreign key(c2) references selfReferencingT1(c1) on delete cascade)");
3116 stmt.executeUpdate("create table selfReferencingT2 (c1 char(2) not null, c2 char(2), constraint selfReferencingT2 primary key(c1), constraint manages2 foreign key(c2) references selfReferencingT2(c1) on update restrict)");
3117
3118 stmt.executeUpdate("insert into t1 values (1,'aa')");
3119 stmt.executeUpdate("insert into t1 values (2,'bb')");
3120 stmt.executeUpdate("insert into t1 values (3,'cc')");
3121 stmt.executeUpdate("insert into t2 values (1,1)");
3122 stmt.executeUpdate("insert into t3 values (1,1)");
3123 stmt.executeUpdate("insert into t3 values (2,2)");
3124 stmt.executeUpdate("insert into t4 values (1,1), (2,2), (3,3)");
3125 stmt.executeUpdate("insert into t5 values (1,1), (2,2), (3,3)");
3126 stmt.executeUpdate("insert into tableWithPrimaryKey values (1, 1), (2, 2), (3, 3), (4, 4)");
3127 stmt.executeUpdate("insert into tableWithConstraint values (1, 1), (2, 2), (3, 3), (4, 4)");
3128 stmt.executeUpdate("insert into table0WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
3129 stmt.executeUpdate("insert into table1WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
3130 stmt.executeUpdate("insert into table2WithTriggers values (1, 1), (2, 2), (3, 3), (4, 4)");
3131 stmt.executeUpdate("insert into selfReferencingT1 values ('e1', null), ('e2', 'e1'), ('e3', 'e2'), ('e4', 'e3')");
3132 stmt.executeUpdate("insert into selfReferencingT2 values ('e1', null), ('e2', 'e1'), ('e3', 'e2'), ('e4', 'e3')");
3133
3134 StringBuffer createSQL = new StringBuffer ("create table AllDataTypesForTestingTable (");
3135 StringBuffer createTestDataSQL = new StringBuffer ("create table AllDataTypesNewValuesData (");
3136 for (int type = 0; type < allSQLTypes.length - 1; type++)
3137 {
3138 createSQL.append(ColumnNames[type] + " " + allSQLTypes[type] + ",");
3139 createTestDataSQL.append(ColumnNames[type] + " " + allSQLTypes[type] + ",");
3140 }
3141 createSQL.append(ColumnNames[allSQLTypes.length - 1] + " " + allSQLTypes[allSQLTypes.length - 1] + ")");
3142 createTestDataSQL.append(ColumnNames[allSQLTypes.length - 1] + " " + allSQLTypes[allSQLTypes.length - 1] + ")");
3143 stmt.executeUpdate(createSQL.toString());
3144 stmt.executeUpdate(createTestDataSQL.toString());
3145
3146 createSQL = new StringBuffer ("insert into AllDataTypesForTestingTable values(");
3147 createTestDataSQL = new StringBuffer ("insert into AllDataTypesNewValuesData values(");
3148 for (int type = 0; type < allSQLTypes.length - 1; type++)
3149 {
3150 createSQL.append(SQLData[type][0] + ",");
3151 createTestDataSQL.append(SQLData[type][1] + ",");
3152 }
3153 createSQL.append("cast("+SQLData[allSQLTypes.length - 1][0]+" as BLOB(1K)))");
3154 createTestDataSQL.append("cast("+SQLData[allSQLTypes.length - 1][1]+" as BLOB(1K)))");
3155 stmt.executeUpdate(createSQL.toString());
3156 stmt.executeUpdate(createTestDataSQL.toString());
3157
3158 stmt.close();
3159 }
3160
3161
3162 static void teardown() throws SQLException {
3163 Statement stmt = conn.createStatement();
3164 String [] testObjects={"table \" t 11 \"", "table \"t1\"",
3165 "trigger tr1", "trigger tr2", "trigger tr3", "trigger tr4",
3166 "view v1", "table s2.t1", "schema s2 restrict", "table t2",
3167 "table t1", "table t3", "table tableWithConstraint",
3168 "table tableWithPrimaryKey", "table deleteTriggerInsertIntoThisTable",
3169 "table updateTriggerInsertIntoThisTable", "table table0WithTriggers",
3170 "table table1WithTriggers", "table table2WithTriggers",
3171 "table selfReferencingT1", "table selfReferencingT2",
3172 "table AllDataTypesForTestingTable", "table AllDataTypesNewValuesData",
3173 "table t4", "table t5"};
3174 TestUtil.cleanUpTest(stmt, testObjects);
3175 conn.commit();
3176 stmt.close();
3177 }
3178
3179 public static void showScanStatistics(ResultSet rs, Connection conn)
3180 {
3181 Statement s = null;
3182 ResultSet infors = null;
3183
3184
3185 try {
3186 rs.close(); s =conn.createStatement();
3188 infors = s.executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
3189 JDBCDisplayUtil.setMaxDisplayWidth(2000);
3190 JDBCDisplayUtil.DisplayResults(System.out,infors,conn);
3191 infors.close();
3192 }
3193 catch (SQLException se)
3194 {
3195 System.out.print("FAIL:");
3196 JDBCDisplayUtil.ShowSQLException(System.out,se);
3197 }
3198 }
3199
3200 static private void dumpSQLExceptions (SQLException se) {
3201 System.out.println("FAIL -- unexpected exception: " + se.toString());
3202 while (se != null) {
3203 System.out.print("SQLSTATE("+se.getSQLState()+"):");
3204 se = se.getNextException();
3205 }
3206 }
3207
3208 static private void dumpExpectedSQLException (SQLException se) {
3209 while (se != null) {
3210 System.out.println("SQL State: " + se.getSQLState());
3211 System.out.println("Got expected exception: " + se.getMessage());
3212 se = se.getNextException();
3213 }
3214 }
3215
3216}
3217
3218 |
Popular Tags |
Java API By Example, From Geeks To Geeks.