KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbcapi > autoGeneratedJdbc30


1 /*
2
3    Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.autoGeneratedJdbc30
4
5    Licensed to the Apache Software Foundation (ASF) under one or more
6    contributor license agreements. See the NOTICE file distributed with
7    this work for additional information regarding copyright ownership.
8    The ASF licenses this file to You under the Apache License, Version 2.0
9    (the "License"); you may not use this file except in compliance with
10    the License. You may obtain a copy of the License at
11
12       http://www.apache.org/licenses/LICENSE-2.0
13
14    Unless required by applicable law or agreed to in writing, software
15    distributed under the License is distributed on an "AS IS" BASIS,
16    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17    See the License for the specific language governing permissions and
18    limitations under the License.
19
20  */

21
22 package org.apache.derbyTesting.functionTests.tests.jdbcapi;
23
24 import java.sql.Connection JavaDoc;
25 import java.sql.DriverManager JavaDoc;
26 import java.sql.PreparedStatement JavaDoc;
27 import java.sql.ResultSet JavaDoc;
28 import java.sql.ResultSetMetaData JavaDoc;
29 import java.sql.Savepoint JavaDoc;
30 import java.sql.Statement JavaDoc;
31 import java.sql.SQLException JavaDoc;
32
33 import org.apache.derby.tools.ij;
34 import org.apache.derby.tools.JDBCDisplayUtil;
35
36 import org.apache.derbyTesting.functionTests.util.TestUtil;
37
38 /**
39  * Test the new Auto Generated Keys feature in jdbc 30 for Statement and PreparedStatement.
40  *
41  * @author mamta
42  */

43
44 public class autoGeneratedJdbc30 {
45
46     private static String JavaDoc[] testObjects= {"table t11","table t21","table t31"};
47
48
49     private static boolean HAVE_DRIVER_MANAGER_CLASS;
50
51     static{
52         try{
53             Class.forName("java.sql.DriverManager");
54             HAVE_DRIVER_MANAGER_CLASS = true;
55         }
56         catch(ClassNotFoundException JavaDoc e){
57             //Used for JSR169
58
HAVE_DRIVER_MANAGER_CLASS = false;
59         }
60     }
61     
62     public static void main(String JavaDoc[] args) {
63
64         Connection JavaDoc con = null;
65         Statement JavaDoc s;
66         PreparedStatement JavaDoc ps;
67
68         System.out.println("Test autoGeneratedJdbc30 starting");
69
70         try
71         {
72             ij.getPropertyArg(args);
73             con = ij.startJBMS();
74
75             s = con.createStatement();
76             /* Create the tables and do any other set-up */
77             setUpTest(s);
78             con.setAutoCommit(false);
79
80             positiveTests(con);
81
82             if(HAVE_DRIVER_MANAGER_CLASS)
83                 doTest1920(s, con);
84
85             negativeTests(con);
86
87             s = con.createStatement();
88             TestUtil.cleanUpTest(s, testObjects);
89             con.commit();
90             con.close();
91         }
92         catch (SQLException JavaDoc e) {
93             JDBCDisplayUtil.ShowSQLException(System.out,e);
94         }
95         catch (Throwable JavaDoc e) {
96             System.out.println("FAIL -- unexpected exception:");
97             e.printStackTrace(System.out);
98         }
99
100         System.out.println("Test autoGeneratedJdbc30 finished");
101     }
102
103     public static String JavaDoc MyMethodWithNoInsert() throws SQLException JavaDoc {
104         System.out.println("Inside server-side method with no insert statement");
105         Connection JavaDoc conn = DriverManager.getConnection("jdbc:default:connection");
106         Statement JavaDoc s = conn.createStatement();
107         s.executeQuery("select * from t11");
108         return "true";
109     }
110
111     public static String JavaDoc MyMethodWithInsert() throws SQLException JavaDoc {
112         System.out.println("Inside server-side method with couple insert statement with various combination of auto generated keys flag");
113         Connection JavaDoc conn = DriverManager.getConnection("jdbc:default:connection");
114         Statement JavaDoc s = conn.createStatement();
115         s.execute("insert into t11(c11) values(999)", Statement.RETURN_GENERATED_KEYS);
116         dumpRS(s.getGeneratedKeys());
117         s.execute("insert into t11(c11) values(999)", Statement.NO_GENERATED_KEYS);
118         try {
119             dumpRS(s.getGeneratedKeys());
120         } catch (SQLException JavaDoc e) {
121             dumpExpectedSQLExceptions(e);
122         }
123         dumpRS(s.executeQuery("select * from t11"));
124         return "true";
125     }
126
127     public static int count(Connection JavaDoc con, Statement JavaDoc s) throws SQLException JavaDoc {
128         int count = 0;
129         ResultSet JavaDoc rs = s.executeQuery("select count(*) from t11");
130         rs.next();
131         count = rs.getInt(1);
132         rs.close();
133         return count;
134     }
135
136     //Set up the test by creating the table used by the rest of the test.
137
public static void setUpTest(Statement JavaDoc s)
138                     throws SQLException JavaDoc {
139         try {
140             // first drop to make sure there's nothing there
141
s.execute("drop table t11");
142             s.execute("drop table t21");
143             s.execute("drop table t31");
144         } catch (SQLException JavaDoc se) {
145             // assume failure because there really wasn't anything there
146
}
147
148         /* Create a table */
149         // set by increment not yet supported for create table...
150
// does not matter for purpose of this test.
151
// s.execute("create table t11 (c11 int, c12 int default set increment by 1)");
152
s.execute("create table t11 (c11 int, c12 int generated always as identity)");
153         s.execute("alter table t11 alter c12 set increment by 1");
154         s.execute("create table t21 (c21 int not null unique, c22 char(5))");
155         s.execute("insert into t21 values(21, 'true')");
156         s.execute("insert into t21 values(22, 'true')");
157         s.execute("create table t31 (c31 int, c32 int generated always as identity, c33 int default 2)");
158         s.execute("alter table t31 alter c32 set increment by 1");
159     }
160
161     public static void dumpExpectedSQLExceptions (SQLException JavaDoc se) {
162         System.out.println("PASS -- expected exception");
163         while (se != null)
164         {
165             System.out.println("SQLSTATE("+se.getSQLState()+"): "+se.getMessage());
166             se = se.getNextException();
167         }
168     }
169
170
171     // lifted from the metadata test
172
public static void dumpRS(ResultSet JavaDoc s) throws SQLException JavaDoc
173     {
174         if (s == null)
175         {
176             System.out.println("<NULL>");
177             return;
178         }
179
180         ResultSetMetaData JavaDoc rsmd = s.getMetaData();
181
182         // Get the number of columns in the result set
183
int numCols = rsmd.getColumnCount();
184
185         if (numCols <= 0)
186         {
187             System.out.println("(no columns!)");
188             return;
189         }
190
191         StringBuffer JavaDoc heading = new StringBuffer JavaDoc("\t ");
192         StringBuffer JavaDoc underline = new StringBuffer JavaDoc("\t ");
193
194         int len;
195         // Display column headings
196
for (int i=1; i<=numCols; i++)
197         {
198             if (i > 1)
199             {
200                 heading.append(",");
201                 underline.append(" ");
202             }
203             len = heading.length();
204             heading.append(rsmd.getColumnLabel(i));
205             len = heading.length() - len;
206             for (int j = len; j > 0; j--)
207             {
208                 underline.append("-");
209             }
210         }
211         System.out.println(heading.toString());
212         System.out.println(underline.toString());
213         
214     
215         StringBuffer JavaDoc row = new StringBuffer JavaDoc();
216         // Display data, fetching until end of the result set
217
while (s.next())
218         {
219             row.append("\t{");
220             // Loop through each column, getting the
221
// column data and displaying
222
for (int i=1; i<=numCols; i++)
223             {
224                 if (i > 1) row.append(",");
225                 row.append(s.getString(i));
226             }
227             row.append("}\n");
228         }
229         System.out.println(row.toString());
230         s.close();
231     }
232
233     public static void disabledTestsBecauseOfBug5580(Statement JavaDoc s, Connection JavaDoc con, PreparedStatement JavaDoc ps) throws SQLException JavaDoc
234     {
235             // re-enable following test whenever bug 5580 fixed
236
//Test11 - insert select with columnIndexes[] array - bug 5580
237
System.out.println("Test11 - insert select with columnIndexes[] array");
238             int colPositions[] = new int[1];
239       colPositions[0] = 1;
240             s.execute("insert into t11(c11) select c21 from t21", colPositions);
241             dumpRS(s.getGeneratedKeys());
242             s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions);
243             dumpRS(s.getGeneratedKeys());
244
245             System.out.println("Test11ps - insert select with columnIndexes[] array");
246             ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions);
247             ps.execute();
248             dumpRS(ps.getGeneratedKeys());
249             ps.executeUpdate();
250             dumpRS(ps.getGeneratedKeys());
251
252             // BUG 4836 Hey, actually fetch a generated column!!!!!!!!!!!!!
253
colPositions[0] = 2;
254             s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions);
255             try {
256                 dumpRS(s.getGeneratedKeys());
257             } catch (SQLException JavaDoc e) {
258                 dumpExpectedSQLExceptions(e);
259             }
260             ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions);
261             ps.executeUpdate();
262             dumpRS(ps.getGeneratedKeys());
263
264             //Verify data in the table
265
if(count(con,s) != 12) {
266                 System.out.println("Test failed");
267                 return;
268             }
269             s.execute("delete from t11");
270
271             //Test12 - insert select with columnIndexes[] array with duplicate column positions
272
System.out.println("Test12 - insert select with columnIndexes[] array with duplicate column positions");
273             colPositions = new int[2];
274       colPositions[0] = 1;
275       colPositions[1] = 1;
276             s.execute("insert into t11(c11) select c21 from t21", colPositions);
277             dumpRS(s.getGeneratedKeys());
278             s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions);
279             dumpRS(s.getGeneratedKeys());
280
281             System.out.println("Test12ps - insert select with columnIndexes[] array with duplicate column positions");
282             ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions);
283             ps.execute();
284             dumpRS(ps.getGeneratedKeys());
285             ps.executeUpdate();
286             dumpRS(ps.getGeneratedKeys());
287
288             //Verify data in the table
289
if(count(con,s) != 8) {
290                 System.out.println("Test failed");
291                 return;
292             }
293             s.execute("delete from t11");
294
295             //Test13 - insert select with columnIndexes[] array with invalid column position
296
System.out.println("Test13 - insert select with columnIndexes[] array with invalid column position");
297       colPositions[0] = 3;
298             try {
299                 s.execute("insert into t11(c11) select c21 from t21", colPositions);
300             } catch (SQLException JavaDoc e) {
301                 dumpExpectedSQLExceptions(e);
302             }
303             try {
304                 dumpRS(s.getGeneratedKeys());
305             } catch (SQLException JavaDoc e) {
306                 dumpExpectedSQLExceptions(e);
307             }
308             try {
309                 s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions);
310             } catch (SQLException JavaDoc e) {
311                 dumpExpectedSQLExceptions(e);
312             }
313             try {
314         dumpRS(s.getGeneratedKeys());
315             } catch (SQLException JavaDoc e) {
316                 dumpExpectedSQLExceptions(e);
317             }
318
319             System.out.println("Test13ps - insert select with columnIndexes[] array with invalid column position");
320             try {
321                 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions);
322                 ps.execute();
323             } catch (SQLException JavaDoc e) {
324                 dumpExpectedSQLExceptions(e);
325             }
326             try {
327                 dumpRS(ps.getGeneratedKeys());
328             } catch (SQLException JavaDoc e) {
329                 dumpExpectedSQLExceptions(e);
330             }
331             try {
332                 ps.executeUpdate();
333             } catch (SQLException JavaDoc e) {
334                 dumpExpectedSQLExceptions(e);
335             }
336             try {
337                 dumpRS(ps.getGeneratedKeys());
338             } catch (SQLException JavaDoc e) {
339                 dumpExpectedSQLExceptions(e);
340             }
341
342             //Verify data in the table
343
if(count(con,s) != 0) {
344                 System.out.println("Test failed");
345                 return;
346             }
347             s.execute("delete from t11");
348
349             //Test14 - insert select with columnNames[] array
350
System.out.println("Test14 - insert select with columnNames[] array");
351             String JavaDoc colNames[] = new String JavaDoc[1];
352       colNames[0] = "C11";
353             s.execute("insert into t11(c11) select c21 from t21", colNames);
354             dumpRS(s.getGeneratedKeys());
355             s.executeUpdate("insert into t11(c11) select c21 from t21", colNames);
356             dumpRS(s.getGeneratedKeys());
357
358             System.out.println("Test14ps - insert select with columnNames[] array");
359             ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames);
360             ps.execute();
361             dumpRS(ps.getGeneratedKeys());
362             ps.executeUpdate();
363             dumpRS(ps.getGeneratedKeys());
364
365             // BUG 4836 Hey, actually fetch a generated column!!!!!!!!!!!!!
366
colNames[0] = "C12";
367             s.executeUpdate("insert into t11(c11) select c21 from t21", colNames);
368             dumpRS(s.getGeneratedKeys());
369             ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames);
370             ps.executeUpdate();
371             dumpRS(ps.getGeneratedKeys());
372
373             //Verify data in the table
374
if(count(con,s) != 12) {
375                 System.out.println("Test failed");
376                 return;
377             }
378             s.execute("delete from t11");
379
380             //Test15 - insert select with columnNames[] array with duplicate column names
381
System.out.println("Test15 - insert select with columnNames[] array with duplicate column names");
382             colNames = new String JavaDoc[2];
383       colNames[0] = "C11";
384       colNames[1] = "C11";
385             s.execute("insert into t11(c11) select c21 from t21", colNames);
386             dumpRS(s.getGeneratedKeys());
387             s.executeUpdate("insert into t11(c11) select c21 from t21", colNames);
388             dumpRS(s.getGeneratedKeys());
389
390             System.out.println("Test15ps - insert select with columnNames[] array with duplicate column names");
391             ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames);
392             ps.execute();
393             dumpRS(ps.getGeneratedKeys());
394             ps.executeUpdate();
395             dumpRS(ps.getGeneratedKeys());
396
397             //Verify data in the table
398
if(count(con,s) != 8) {
399                 System.out.println("Test failed");
400                 return;
401             }
402             s.execute("delete from t11");
403
404             //Test16 - insert select with columnNames[] array with invalid column name
405
colNames = new String JavaDoc[1];
406             System.out.println("Test16 - insert select with columnNames[] array with invalid column name");
407       colNames[0] = "C13";
408             try {
409                 s.execute("insert into t11(c11) select c21 from t21", colNames);
410             } catch (SQLException JavaDoc e) {
411                 dumpExpectedSQLExceptions(e);
412             }
413             try {
414                 dumpRS(s.getGeneratedKeys());
415             } catch (SQLException JavaDoc e) {
416                 dumpExpectedSQLExceptions(e);
417             }
418             try {
419                 s.executeUpdate("insert into t11(c11) select c21 from t21", colNames);
420             } catch (SQLException JavaDoc e) {
421                 dumpExpectedSQLExceptions(e);
422             }
423             try {
424                 dumpRS(s.getGeneratedKeys());
425             } catch (SQLException JavaDoc e) {
426                 dumpExpectedSQLExceptions(e);
427             }
428
429             System.out.println("Test16ps - insert select with columnNames[] array with invalid column name");
430             try {
431                 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames);
432                 ps.execute();
433             } catch (SQLException JavaDoc e) {
434                 dumpExpectedSQLExceptions(e);
435             }
436             try {
437                 dumpRS(ps.getGeneratedKeys());
438             } catch (SQLException JavaDoc e) {
439                 dumpExpectedSQLExceptions(e);
440             }
441             try {
442                 ps.executeUpdate();
443             } catch (SQLException JavaDoc e) {
444                 dumpExpectedSQLExceptions(e);
445             }
446             try {
447                 dumpRS(ps.getGeneratedKeys());
448             } catch (SQLException JavaDoc e) {
449                 dumpExpectedSQLExceptions(e);
450             }
451
452             //Verify data in the table
453
if(count(con,s) != 0) {
454                 System.out.println("Test failed");
455                 return;
456             }
457             s.execute("delete from t11");
458
459             //Test17 - insert select with null columnIndexes[] internally switches to Statement.NO_GENERATED_KEYS
460
System.out.println("Test17 - insert select with null columnIndexes[] internally switches to Statement.NO_GENERATED_KEYS");
461             colPositions = null;
462             s.execute("insert into t11(c11) select c21 from t21", colPositions);
463             try {
464                 dumpRS(s.getGeneratedKeys());
465             } catch (SQLException JavaDoc e) {
466                 dumpExpectedSQLExceptions(e);
467             }
468             s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions);
469             try {
470                 dumpRS(s.getGeneratedKeys());
471             } catch (SQLException JavaDoc e) {
472                 dumpExpectedSQLExceptions(e);
473             }
474
475             System.out.println("Test17ps - insert select with null columnIndexes[] internally switches to Statement.NO_GENERATED_KEYS");
476             ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions);
477             ps.execute();
478             try {
479                 dumpRS(ps.getGeneratedKeys());
480             } catch (SQLException JavaDoc e) {
481                 dumpExpectedSQLExceptions(e);
482             }
483             ps.executeUpdate();
484             try {
485                 dumpRS(ps.getGeneratedKeys());
486             } catch (SQLException JavaDoc e) {
487                 dumpExpectedSQLExceptions(e);
488             }
489
490             //Verify data in the table
491
if(count(con,s) != 8) {
492                 System.out.println("Test failed");
493                 return;
494             }
495             s.execute("delete from t11");
496
497             //Test18 - insert select with null columnNames[] internally switches to Statement.NO_GENERATED_KEYS
498
System.out.println("Test18 - insert select with null columnNames[] internally switches to Statement.NO_GENERATED_KEYS");
499             colNames = null;
500             s.execute("insert into t11(c11) select c21 from t21", colNames);
501             try {
502                 dumpRS(s.getGeneratedKeys());
503             } catch (SQLException JavaDoc e) {
504                 dumpExpectedSQLExceptions(e);
505             }
506             s.executeUpdate("insert into t11(c11) select c21 from t21", colNames);
507             try {
508                 dumpRS(s.getGeneratedKeys());
509             } catch (SQLException JavaDoc e) {
510                 dumpExpectedSQLExceptions(e);
511             }
512
513             System.out.println("Test18ps - insert select with null columnNames[] internally switches to Statement.NO_GENERATED_KEYS");
514             ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames);
515             ps.execute();
516             try {
517                 dumpRS(ps.getGeneratedKeys());
518             } catch (SQLException JavaDoc e) {
519                 dumpExpectedSQLExceptions(e);
520             }
521             ps.executeUpdate();
522             try {
523                 dumpRS(ps.getGeneratedKeys());
524             } catch (SQLException JavaDoc e) {
525                 dumpExpectedSQLExceptions(e);
526             }
527
528             //Verify data in the table
529
if(count(con,s) != 8) {
530                 System.out.println("Test failed");
531                 return;
532             }
533             s.execute("delete from t11");
534
535             //Test19a - insert values with column position order which doesn't match column positions in the actual table
536
//The column positions correspond to columns in the table and not the columns in the insert statement
537
System.out.println("Test19a - insert values with column position order which doesn't match column positions in the actual table");
538             colPositions = new int[1];
539             colPositions[0] = 1;
540             s.execute("insert into t21(c22,c21) values('true', 23)", colPositions);
541             dumpRS(s.getGeneratedKeys());
542             s.executeUpdate("insert into t21(c22,c21) values('true', 23)", colPositions);
543             dumpRS(s.getGeneratedKeys());
544
545             //Test19aps - insert values with column position order which doesn't match column positions in the actual table
546
System.out.println("Test19aps - insert values with column position order which doesn't match column positions in the actual table");
547             ps = con.prepareStatement("insert into t21(c22,c21) values('true', 23)", colPositions);
548             ps.execute();
549             dumpRS(ps.getGeneratedKeys());
550             ps.executeUpdate();
551             dumpRS(ps.getGeneratedKeys());
552
553             //Verify data in the table
554
dumpRS(s.executeQuery("select count(*) from t21"));
555             s.execute("delete from t11");
556     }
557
558     private static void positiveTests(Connection JavaDoc conn) throws SQLException JavaDoc
559     {
560             System.out.println("Test 1 - request for generated keys resultset on a brand new statement with no sql executed on it yet");
561             System.out.println("We will get a resultset with no rows because it is a non-insert sql");
562             Statement JavaDoc s = conn.createStatement();
563             dumpRS(s.getGeneratedKeys());
564
565             System.out.println("Test2 - request for generated keys on a statement which does select from a table ie a non-insert sql");
566             s.execute("select * from t11", Statement.RETURN_GENERATED_KEYS);
567             System.out.println("We will get a resultset with no rows because it is a non-insert sql");
568             dumpRS(s.getGeneratedKeys());
569             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
570             PreparedStatement JavaDoc ps = conn.prepareStatement("select * from t11", Statement.RETURN_GENERATED_KEYS);
571             ps.execute();
572             dumpRS(ps.getGeneratedKeys());
573
574             System.out.println("Test 3 - insert multiple rows into a table with autogenerated key and request generated keys resultset");
575             System.out.println(" We will get a row with NULL value because this insert sql inserted more than one row and ");
576             System.out.println(" there was no prior one-row insert into a table with autogenerated key");
577             s.execute("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS);
578             dumpRS(s.getGeneratedKeys());
579             System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
580             s.executeUpdate("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS);
581             dumpRS(s.getGeneratedKeys());
582             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
583             ps = conn.prepareStatement("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS);
584             ps.execute();
585             dumpRS(ps.getGeneratedKeys());
586             System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
587             ps.executeUpdate();
588             dumpRS(ps.getGeneratedKeys());
589
590             System.out.println("Test 4 - request for generated keys after doing an insert into a table with no auto generated keys");
591             System.out.println(" And there has been no one-row insert into a table with auto-generated keys yet.");
592             s.execute("insert into t21 values(23, 'true')", Statement.RETURN_GENERATED_KEYS);
593             System.out.println("We should get a resultset with one row of NULL value from getGeneratedKeys");
594             dumpRS(s.getGeneratedKeys());
595             System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
596             s.executeUpdate("insert into t21 values(24, 'true')", Statement.RETURN_GENERATED_KEYS);
597             dumpRS(s.getGeneratedKeys());
598             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
599             ps = conn.prepareStatement("insert into t21 values(25, 'true')", Statement.RETURN_GENERATED_KEYS);
600             ps.execute();
601             dumpRS(ps.getGeneratedKeys());
602             System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
603             ps = conn.prepareStatement("insert into t21 values(26, 'true')", Statement.RETURN_GENERATED_KEYS);
604             ps.executeUpdate();
605             dumpRS(ps.getGeneratedKeys());
606
607             System.out.println("Test 5a - request for generated keys after doing a one-row insert(using a subquery) into a table with autogenerated key using a subquery with auto generated keys");
608             s.execute("insert into t11(c11) select c21 from t21", Statement.RETURN_GENERATED_KEYS);
609             System.out.println("Even though this is a one-row insert but since insert has a sub-select, get generated keys will return one row of NULL value");
610             dumpRS(s.getGeneratedKeys());
611             System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
612             s.executeUpdate("insert into t11(c11) select c21 from t21", Statement.RETURN_GENERATED_KEYS);
613             dumpRS(s.getGeneratedKeys());
614             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
615             ps = conn.prepareStatement("insert into t11(c11) select c21 from t21", Statement.RETURN_GENERATED_KEYS);
616             ps.execute();
617             dumpRS(ps.getGeneratedKeys());
618             System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
619             ps.executeUpdate();
620             dumpRS(ps.getGeneratedKeys());
621
622             System.out.println("Test 5b - request for generated keys after doing a one-row insert(using a subquery) into a table with autogenerated key using a subquery with auto generated keys");
623             s.execute("insert into t11(c11) select c21 from t21 where 1=2", Statement.RETURN_GENERATED_KEYS);
624             System.out.println("Even though this is a one-row insert but since insert has a sub-select, get generated keys will return one row of NULL value");
625             dumpRS(s.getGeneratedKeys());
626             System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
627             s.executeUpdate("insert into t11(c11) select c21 from t21 where 1=2", Statement.RETURN_GENERATED_KEYS);
628             dumpRS(s.getGeneratedKeys());
629             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
630             ps = conn.prepareStatement("insert into t11(c11) select c21 from t21 where 1=2", Statement.RETURN_GENERATED_KEYS);
631             ps.execute();
632             dumpRS(ps.getGeneratedKeys());
633             System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
634             ps.executeUpdate();
635             dumpRS(ps.getGeneratedKeys());
636
637             System.out.println("Test 5c - request for generated keys after doing a one-row insert(using a subquery) into a table with autogenerated key using a subquery with auto generated keys");
638             s.execute("insert into t11(c11) select c21 from t21 where c21=23", Statement.RETURN_GENERATED_KEYS);
639             System.out.println("Even though this is a one-row insert but since insert has a sub-select, get generated keys will return one row of NULL value");
640             dumpRS(s.getGeneratedKeys());
641             System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
642             s.executeUpdate("insert into t11(c11) select c21 from t21 where c21=23", Statement.RETURN_GENERATED_KEYS);
643             dumpRS(s.getGeneratedKeys());
644             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
645             ps = conn.prepareStatement("insert into t11(c11) select c21 from t21 where c21=23", Statement.RETURN_GENERATED_KEYS);
646             ps.execute();
647             dumpRS(ps.getGeneratedKeys());
648             System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
649             ps.executeUpdate();
650             dumpRS(ps.getGeneratedKeys());
651
652             System.out.println("Test 6 - request for generated keys after doing a one-row insert into a table with auto generated keys");
653             s.execute("insert into t11(c11) values (99)", Statement.RETURN_GENERATED_KEYS);
654             System.out.println("We should get a resultset with one row of non-NULL value");
655             dumpRS(s.getGeneratedKeys());
656             System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
657             s.executeUpdate("insert into t11(c11) values (99)", Statement.RETURN_GENERATED_KEYS);
658             dumpRS(s.getGeneratedKeys());
659             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
660             ps = conn.prepareStatement("insert into t11(c11) values (99)", Statement.RETURN_GENERATED_KEYS);
661             ps.execute();
662             dumpRS(ps.getGeneratedKeys());
663             System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
664             ps.executeUpdate();
665             dumpRS(ps.getGeneratedKeys());
666
667             System.out.println("Test 7 - Now try again inserting multiple rows into a table with autogenerated key and request generated keys resultset");
668             System.out.println(" This time we will get a row of non-NULL value because there has been a prior one-row insert into table with auto-generated key ");
669             s.execute("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS);
670             dumpRS(s.getGeneratedKeys());
671             System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
672             s.executeUpdate("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS);
673             dumpRS(s.getGeneratedKeys());
674             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
675             ps = conn.prepareStatement("insert into t31(c31) values (99), (98), (97)", Statement.RETURN_GENERATED_KEYS);
676             ps.execute();
677             dumpRS(ps.getGeneratedKeys());
678             System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
679             ps.executeUpdate();
680             dumpRS(ps.getGeneratedKeys());
681
682             System.out.println("Test 8 - create a new statement and request for generated keys on it after doing an insert into ");
683             System.out.println(" a table with no auto generated keys");
684             Statement JavaDoc s1 = conn.createStatement();
685             s1.execute("insert into t21 values(27, 'true')", Statement.RETURN_GENERATED_KEYS);
686             System.out.println("We should get a resultset with one row of non-NULL value");
687             dumpRS(s1.getGeneratedKeys());
688             System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
689             s1.executeUpdate("insert into t21 values(28, 'true')", Statement.RETURN_GENERATED_KEYS);
690             dumpRS(s1.getGeneratedKeys());
691             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
692             ps = conn.prepareStatement("insert into t21 values(29, 'true')", Statement.RETURN_GENERATED_KEYS);
693             ps.execute();
694             dumpRS(ps.getGeneratedKeys());
695             System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
696             ps = conn.prepareStatement("insert into t21 values(30, 'true')", Statement.RETURN_GENERATED_KEYS);
697             ps.executeUpdate();
698             dumpRS(ps.getGeneratedKeys());
699
700             System.out.println("Test 9 - request for generated keys on a statement which does a update ");
701             s.execute("update t11 set c11=1", Statement.RETURN_GENERATED_KEYS);
702             System.out.println("We should get a resultset with no rows from getGeneratedKeys because we executed a non-insert sql");
703             dumpRS(s.getGeneratedKeys());
704             System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
705             s.executeUpdate("update t11 set c11=1", Statement.RETURN_GENERATED_KEYS);
706             dumpRS(s.getGeneratedKeys());
707             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
708             ps = conn.prepareStatement("update t11 set c11=1", Statement.RETURN_GENERATED_KEYS);
709             ps.execute();
710             dumpRS(ps.getGeneratedKeys());
711             System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
712             ps.executeUpdate();
713             dumpRS(ps.getGeneratedKeys());
714
715             System.out.println("Test 10 - request for generated keys on a statement which does a delete ");
716             s.execute("delete from t11", Statement.RETURN_GENERATED_KEYS);
717             System.out.println("We should get a resultset with no rows from getGeneratedKeys because we executed a non-insert sql");
718             dumpRS(s.getGeneratedKeys());
719             System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
720             s.executeUpdate("delete from t11", Statement.RETURN_GENERATED_KEYS);
721             dumpRS(s.getGeneratedKeys());
722             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
723             ps = conn.prepareStatement("delete from t11", Statement.RETURN_GENERATED_KEYS);
724             ps.execute();
725             dumpRS(ps.getGeneratedKeys());
726             System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
727             ps.executeUpdate();
728             dumpRS(ps.getGeneratedKeys());
729
730             System.out.println("Test 11 - do a commit and request for generated keys on a statement which does insert into a table with ");
731             System.out.println(" no auto generated keys (previous transaction had a one-row insert on a table with auto-generated keys)");
732             conn.commit();
733             s.execute("insert into t21 values(31, 'true')", Statement.RETURN_GENERATED_KEYS);
734             System.out.println("expected to see resultset with one row of NULL value but instead get one row of non-NULL value from getGeneratedKeys");
735             dumpRS(s.getGeneratedKeys());
736             System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
737             s.executeUpdate("insert into t21 values(32, 'true')", Statement.RETURN_GENERATED_KEYS);
738             dumpRS(s.getGeneratedKeys());
739             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
740             ps = conn.prepareStatement("insert into t21 values(33, 'true')", Statement.RETURN_GENERATED_KEYS);
741             ps.execute();
742             dumpRS(ps.getGeneratedKeys());
743             System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
744             ps = conn.prepareStatement("insert into t21 values(34, 'true')", Statement.RETURN_GENERATED_KEYS);
745             ps.executeUpdate();
746             dumpRS(ps.getGeneratedKeys());
747
748             System.out.println("Test 12 - do a rollback and request for generated keys on a statement which does insert into a table with ");
749             System.out.println(" no auto generated keys (previous transaction had a one-row insert on a table with auto-generated keys)");
750             conn.rollback();
751             s.execute("insert into t21 values(35, 'true')", Statement.RETURN_GENERATED_KEYS);
752             System.out.println("had expected to see resultset with one row of NULL value but instead get one row of non-NULL value from getGeneratedKeys");
753             dumpRS(s.getGeneratedKeys());
754             System.out.println("Now try the same test again but this time with Statement.executeUpdate. It should behave the same");
755             s.executeUpdate("insert into t21 values(36, 'true')", Statement.RETURN_GENERATED_KEYS);
756             dumpRS(s.getGeneratedKeys());
757             System.out.println("Now try the same test again but this time with PreparedStatement.execute. It should behave the same");
758             ps = conn.prepareStatement("insert into t21 values(37, 'true')", Statement.RETURN_GENERATED_KEYS);
759             ps.execute();
760             dumpRS(ps.getGeneratedKeys());
761             System.out.println("Now try the same test again but this time with PreparedStatement.executeUpdate. It should behave the same");
762             ps = conn.prepareStatement("insert into t21 values(38, 'true')", Statement.RETURN_GENERATED_KEYS);
763             ps.executeUpdate();
764             dumpRS(ps.getGeneratedKeys());
765
766             System.out.println("Test 13 - try savepoint rollback and see what happens to auto generated keys resultset");
767             Savepoint JavaDoc savepoint1;
768             System.out.println("Inside the savepoint unit, issue a one-row insert into a table with auto generated keys");
769             savepoint1 = conn.setSavepoint();
770             s.execute("insert into t11(c11) values (99)", Statement.RETURN_GENERATED_KEYS);
771             System.out.println("We should get a resultset with one row of non-NULL value");
772             dumpRS(s.getGeneratedKeys());
773             System.out.println("Now rollback the savepoint unit, and make sure that autogenerated keys resultset still holds on to ");
774             System.out.println(" value that got set inside the rolled back savepoint unit");
775             conn.rollback(savepoint1);
776             s.execute("insert into t21 values(39, 'true')", Statement.RETURN_GENERATED_KEYS);
777             dumpRS(s.getGeneratedKeys());
778
779             System.out.println("Test 14 - Look at metadata of a getGeneratedKeys resultset");
780             s = conn.createStatement();
781             s.execute("insert into t31(c31) values (99)", Statement.RETURN_GENERATED_KEYS);
782             ResultSet JavaDoc rs = s.getGeneratedKeys();
783             ResultSetMetaData JavaDoc rsmd = rs.getMetaData();
784             System.out.println("The resultset will have one column only");
785             System.out.println("Found " + rsmd.getColumnCount() + " column in the resultset");
786             System.out.println("Type of the column is " + rsmd.getColumnTypeName(1));
787             System.out.println("Precision of the column is " + rsmd.getPrecision(1));
788             System.out.println("Scale of the column is " + rsmd.getScale(1));
789             dumpRS(rs);
790
791             System.out.println("Test 15 - Can not see the auto generated keys if insert is with NO_GENERATED_KEYS");
792             s = conn.createStatement();
793             s.execute("insert into t31(c31) values (99)", Statement.NO_GENERATED_KEYS);
794             dumpRS(s.getGeneratedKeys());
795             s.executeUpdate("insert into t31(c31) values (99)", Statement.NO_GENERATED_KEYS);
796             dumpRS(s.getGeneratedKeys());
797             ps = conn.prepareStatement("insert into t31(c31) values (99)", Statement.NO_GENERATED_KEYS);
798             ps.execute();
799             dumpRS(ps.getGeneratedKeys());
800             ps.executeUpdate();
801             dumpRS(ps.getGeneratedKeys());
802
803             System.out.println("Test 16 - Can not see the auto generated keys if insert is done jdbc 2.0 way ie with no generated key feature");
804             s.execute("insert into t31(c31) values (99)");
805             dumpRS(s.getGeneratedKeys());
806             s.executeUpdate("insert into t31(c31) values (99)");
807             dumpRS(s.getGeneratedKeys());
808             ps = conn.prepareStatement("insert into t31(c31) values (99)");
809             ps.execute();
810             dumpRS(ps.getGeneratedKeys());
811             ps.executeUpdate();
812             dumpRS(ps.getGeneratedKeys());
813
814             System.out.println("Test 17 - non-insert with NO_GENERATED_KEYS");
815             s = conn.createStatement();
816             s.execute("update t31 set c31=98", Statement.NO_GENERATED_KEYS);
817             dumpRS(s.getGeneratedKeys());
818             s.executeUpdate("update t31 set c31=98", Statement.NO_GENERATED_KEYS);
819             dumpRS(s.getGeneratedKeys());
820             ps = conn.prepareStatement("update t31 set c31=98", Statement.NO_GENERATED_KEYS);
821             ps.execute();
822             dumpRS(ps.getGeneratedKeys());
823             ps.executeUpdate();
824             dumpRS(ps.getGeneratedKeys());
825
826             System.out.println("Test 18 - non-insert is done jdbc 2.0 way ie with no generated key feature");
827             s.execute("delete from t31");
828             dumpRS(s.getGeneratedKeys());
829             s.executeUpdate("delete from t31");
830             dumpRS(s.getGeneratedKeys());
831             ps = conn.prepareStatement("delete from t31");
832             ps.execute();
833             dumpRS(ps.getGeneratedKeys());
834             ps.executeUpdate();
835             dumpRS(ps.getGeneratedKeys());
836             
837     }
838
839     public static void negativeTests(Connection JavaDoc con) throws SQLException JavaDoc
840     {
841             Statement JavaDoc s = con.createStatement();
842             PreparedStatement JavaDoc ps;
843             //Test21 - insert select with columnIndexes[] array
844
System.out.println("Test21 - insert select with columnIndexes[] array should fail");
845             int colPositions[] = new int[1];
846       colPositions[0] = 1;
847             try {
848                 System.out.println("Try passing array with Statement.execute");
849                 s.execute("insert into t11(c11) select c21 from t21", colPositions);
850                 System.out.println("ERROR: shouldn't be able to pass array with Statement.execute");
851
852             } catch (SQLException JavaDoc e) {
853                 if ((e.getMessage() != null &&
854                      e.getMessage().indexOf("Driver not capable") >= 0)
855                     || (e.getSQLState() != null &&
856                         (e.getSQLState().startsWith("0A"))))
857                     System.out.println("PASS - expected exception - Feature not implemented");
858                 else System.out.println("Unexpected FAILURE at " +e);
859
860             }
861             try {
862                 System.out.println("Try passing array with Statement.executeUpdate");
863                 s.executeUpdate("insert into t11(c11) select c21 from t21", colPositions);
864                 System.out.println("ERROR: shouldn't be able to pass array with Statement.executeUpdate");
865             } catch (SQLException JavaDoc e) {
866                 if (e.getSQLState() == null || e.getSQLState().startsWith("0A"))
867                     dumpExpectedSQLExceptions(e);
868                 else
869                     JDBCDisplayUtil.ShowSQLException(System.out,e);
870             }
871
872             System.out.println("Test21ps - insert select with columnIndexes[] array should fail");
873             try {
874                 System.out.println("Try passing array with Connection.prepareStatement");
875                 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colPositions);
876                 System.out.println("ERROR: shouldn't be able to pass array with Connection.prepareStatement");
877             } catch (SQLException JavaDoc e) {
878                 if (e.getSQLState() == null || e.getSQLState().startsWith("0A"))
879                     dumpExpectedSQLExceptions(e);
880                 else
881                     JDBCDisplayUtil.ShowSQLException(System.out,e);
882             }
883
884             //Test22 - insert select with columnNames[] array
885
System.out.println("Test22 - insert select with columnNames[] array should fail");
886             String JavaDoc colNames[] = new String JavaDoc[1];
887       colNames[0] = "C11";
888             try {
889                 System.out.println("Try passing array with Statement.execute");
890                 s.execute("insert into t11(c11) select c21 from t21", colNames);
891                 System.out.println("ERROR: shouldn't be able to pass array with Statement.execute");
892             } catch (SQLException JavaDoc e) {
893                 if (e.getSQLState() == null || e.getSQLState().startsWith("0A"))
894                     dumpExpectedSQLExceptions(e);
895                 else
896                     JDBCDisplayUtil.ShowSQLException(System.out,e);
897             }
898             try {
899                 System.out.println("Try passing array with Statement.executeUpdate");
900                 s.executeUpdate("insert into t11(c11) select c21 from t21", colNames);
901                 System.out.println("ERROR: shouldn't be able to pass array with Statement.executeUpdate");
902             } catch (SQLException JavaDoc e) {
903                 if (e.getSQLState() == null || e.getSQLState().startsWith("0A"))
904                     dumpExpectedSQLExceptions(e);
905                 else
906                     JDBCDisplayUtil.ShowSQLException(System.out,e);
907             }
908
909             System.out.println("Test22ps - insert select with columnNames[] array should fail");
910             try {
911                 System.out.println("Try passing array with Connection.prepareStatement");
912                 ps = con.prepareStatement("insert into t11(c11) select c21 from t21", colNames);
913                 System.out.println("ERROR: shouldn't be able to pass array with Connection.prepareStatement");
914             } catch (SQLException JavaDoc e) {
915                 if (e.getSQLState() == null || e.getSQLState().startsWith("0A"))
916                     dumpExpectedSQLExceptions(e);
917                 else
918                     JDBCDisplayUtil.ShowSQLException(System.out,e);
919             }
920         con.rollback();
921     }
922
923     public static void doTest1920(Statement JavaDoc s, Connection JavaDoc con) throws SQLException JavaDoc
924     {
925             //Test19 - bug 4838 no auto generated key resultset generated for INSERT with
926
//generated keys if server-side methods are invoked.
927
//Adding the tests to try server side methods which toggle the auto generated flag
928
//and make sure we don't loose the client side setting in the process
929
System.out.println("Test19 - fix the no auto generated key resultset generated for INSERT with " +
930             "generated keys if server-side methods are invoked");
931             s.execute("CREATE FUNCTION MMWNI() RETURNS VARCHAR(20) LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.jdbcapi.autoGeneratedJdbc30.MyMethodWithNoInsert' PARAMETER STYLE JAVA READS SQL DATA");
932             con.commit();
933             s.execute("insert into t21 values(40,MMWNI())", Statement.RETURN_GENERATED_KEYS);
934             System.out.println("Back to client side looking for auto generated keys");
935             dumpRS(s.getGeneratedKeys());
936
937             //Verify data in the table
938
dumpRS(s.executeQuery("select count(*) from t21"));
939             s.execute("delete from t11");
940
941             s.execute("DROP FUNCTION MMWNI");
942             con.commit();
943             s.close();
944
945             // Test 20 - BUG 4837 garbage collection of the generated key result sets was closing the activation.
946
System.out.println("Test20 - bug 4837garbage collection of the generated key result sets was closing the activation.");
947             PreparedStatement JavaDoc ps = con.prepareStatement("insert into t11(c11) values(?)", Statement.RETURN_GENERATED_KEYS);
948
949             for (int i = 0; i < 100; i++) {
950                 ps.setInt(1, 100+i);
951                 ps.executeUpdate();
952
953                 ResultSet JavaDoc rs = ps.getGeneratedKeys();
954                 while (rs.next()) {
955                     rs.getInt(1);
956                 }
957                 rs.close();
958                 con.commit();
959
960                 System.runFinalization();
961                 System.gc();
962                 System.runFinalization();
963                 System.gc();
964             }
965     }
966 }
967
Popular Tags