KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > testsuite > regression > MetaDataRegressionTest


1 /*
2  Copyright (C) 2002-2004 MySQL AB
3
4  This program is free software; you can redistribute it and/or modify
5  it under the terms of version 2 of the GNU General Public License as
6  published by the Free Software Foundation.
7
8  There are special exceptions to the terms and conditions of the GPL
9  as it is applied to this software. View the full text of the
10  exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
11  software distribution.
12
13  This program is distributed in the hope that it will be useful,
14  but WITHOUT ANY WARRANTY; without even the implied warranty of
15  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16  GNU General Public License for more details.
17
18  You should have received a copy of the GNU General Public License
19  along with this program; if not, write to the Free Software
20  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
21
22
23
24  */

25 package testsuite.regression;
26
27 import java.sql.Connection JavaDoc;
28 import java.sql.DatabaseMetaData JavaDoc;
29 import java.sql.PreparedStatement JavaDoc;
30 import java.sql.ResultSet JavaDoc;
31 import java.sql.ResultSetMetaData JavaDoc;
32 import java.sql.SQLException JavaDoc;
33 import java.sql.Statement JavaDoc;
34 import java.sql.Types JavaDoc;
35 import java.util.HashMap JavaDoc;
36 import java.util.Properties JavaDoc;
37
38 import testsuite.BaseTestCase;
39
40 import com.mysql.jdbc.Driver;
41 import com.mysql.jdbc.SQLError;
42
43 /**
44  * Regression tests for DatabaseMetaData
45  *
46  * @author Mark Matthews
47  * @version $Id: MetaDataRegressionTest.java,v 1.1.2.1 2005/05/13 18:58:38
48  * mmatthews Exp $
49  */

50 public class MetaDataRegressionTest extends BaseTestCase {
51     /**
52      * Creates a new MetaDataRegressionTest.
53      *
54      * @param name
55      * the name of the test
56      */

57     public MetaDataRegressionTest(String JavaDoc name) {
58         super(name);
59     }
60
61     /**
62      * Runs all test cases in this test suite
63      *
64      * @param args
65      */

66     public static void main(String JavaDoc[] args) {
67         junit.textui.TestRunner.run(MetaDataRegressionTest.class);
68     }
69
70     /**
71      * DOCUMENT ME!
72      *
73      * @throws Exception
74      * ...
75      */

76     public void testBug2607() throws Exception JavaDoc {
77         try {
78             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2607");
79             this.stmt
80                     .executeUpdate("CREATE TABLE testBug2607 (field1 INT PRIMARY KEY)");
81
82             this.rs = this.stmt.executeQuery("SELECT field1 FROM testBug2607");
83
84             ResultSetMetaData JavaDoc rsmd = this.rs.getMetaData();
85
86             assertTrue(!rsmd.isAutoIncrement(1));
87         } finally {
88             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2607");
89         }
90     }
91
92     /**
93      * Tests fix for BUG#2852, where RSMD is not returning correct (or matching)
94      * types for TINYINT and SMALLINT.
95      *
96      * @throws Exception
97      * if the test fails.
98      */

99     public void testBug2852() throws Exception JavaDoc {
100         try {
101             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2852");
102             this.stmt
103                     .executeUpdate("CREATE TABLE testBug2852 (field1 TINYINT, field2 SMALLINT)");
104             this.stmt.executeUpdate("INSERT INTO testBug2852 VALUES (1,1)");
105
106             this.rs = this.stmt.executeQuery("SELECT * from testBug2852");
107
108             assertTrue(this.rs.next());
109
110             ResultSetMetaData JavaDoc rsmd = this.rs.getMetaData();
111
112             assertTrue(rsmd.getColumnClassName(1).equals(
113                     this.rs.getObject(1).getClass().getName()));
114             assertTrue("java.lang.Integer".equals(rsmd.getColumnClassName(1)));
115
116             assertTrue(rsmd.getColumnClassName(2).equals(
117                     this.rs.getObject(2).getClass().getName()));
118             assertTrue("java.lang.Integer".equals(rsmd.getColumnClassName(2)));
119         } finally {
120             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2852");
121         }
122     }
123
124     /**
125      * Tests fix for BUG#2855, where RSMD is not returning correct (or matching)
126      * types for FLOAT.
127      *
128      * @throws Exception
129      * if the test fails.
130      */

131     public void testBug2855() throws Exception JavaDoc {
132         try {
133             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2855");
134             this.stmt.executeUpdate("CREATE TABLE testBug2855 (field1 FLOAT)");
135             this.stmt.executeUpdate("INSERT INTO testBug2855 VALUES (1)");
136
137             this.rs = this.stmt.executeQuery("SELECT * from testBug2855");
138
139             assertTrue(this.rs.next());
140
141             ResultSetMetaData JavaDoc rsmd = this.rs.getMetaData();
142
143             assertTrue(rsmd.getColumnClassName(1).equals(
144                     this.rs.getObject(1).getClass().getName()));
145             assertTrue("java.lang.Float".equals(rsmd.getColumnClassName(1)));
146         } finally {
147             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2855");
148         }
149     }
150
151     /**
152      * Tests fix for BUG#3570 -- inconsistent reporting of column type
153      *
154      * @throws Exception
155      * if an error occurs
156      */

157     public void testBug3570() throws Exception JavaDoc {
158         String JavaDoc createTableQuery = " CREATE TABLE testBug3570(field_tinyint TINYINT"
159                 + ",field_smallint SMALLINT"
160                 + ",field_mediumint MEDIUMINT"
161                 + ",field_int INT"
162                 + ",field_integer INTEGER"
163                 + ",field_bigint BIGINT"
164                 + ",field_real REAL"
165                 + ",field_float FLOAT"
166                 + ",field_decimal DECIMAL"
167                 + ",field_numeric NUMERIC"
168                 + ",field_double DOUBLE"
169                 + ",field_char CHAR(3)"
170                 + ",field_varchar VARCHAR(255)"
171                 + ",field_date DATE"
172                 + ",field_time TIME"
173                 + ",field_year YEAR"
174                 + ",field_timestamp TIMESTAMP"
175                 + ",field_datetime DATETIME"
176                 + ",field_tinyblob TINYBLOB"
177                 + ",field_blob BLOB"
178                 + ",field_mediumblob MEDIUMBLOB"
179                 + ",field_longblob LONGBLOB"
180                 + ",field_tinytext TINYTEXT"
181                 + ",field_text TEXT"
182                 + ",field_mediumtext MEDIUMTEXT"
183                 + ",field_longtext LONGTEXT"
184                 + ",field_enum ENUM('1','2','3')"
185                 + ",field_set SET('1','2','3'))";
186
187         try {
188             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3570");
189             this.stmt.executeUpdate(createTableQuery);
190
191             ResultSet JavaDoc dbmdRs = this.conn.getMetaData().getColumns(
192                     this.conn.getCatalog(), null, "testBug3570", "%");
193
194             this.rs = this.stmt.executeQuery("SELECT * FROM testBug3570");
195
196             ResultSetMetaData JavaDoc rsmd = this.rs.getMetaData();
197
198             while (dbmdRs.next()) {
199                 String JavaDoc columnName = dbmdRs.getString(4);
200                 int typeFromGetColumns = dbmdRs.getInt(5);
201                 int typeFromRSMD = rsmd.getColumnType(this.rs
202                         .findColumn(columnName));
203
204                 //
205
// TODO: Server needs to send these types correctly....
206
//
207
if (!"field_tinyblob".equals(columnName)
208                         && !"field_tinytext".equals(columnName)) {
209                     assertTrue(columnName + " -> type from DBMD.getColumns("
210                             + typeFromGetColumns
211                             + ") != type from RSMD.getColumnType("
212                             + typeFromRSMD + ")",
213                             typeFromGetColumns == typeFromRSMD);
214                 }
215             }
216         } finally {
217             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug3570");
218         }
219     }
220
221     /**
222      * Tests char/varchar bug
223      *
224      * @throws Exception
225      * if any errors occur
226      */

227     public void testCharVarchar() throws Exception JavaDoc {
228         try {
229             this.stmt.execute("DROP TABLE IF EXISTS charVarCharTest");
230             this.stmt.execute("CREATE TABLE charVarCharTest ("
231                     + " TableName VARCHAR(64)," + " FieldName VARCHAR(64),"
232                     + " NextCounter INTEGER);");
233
234             String JavaDoc query = "SELECT TableName, FieldName, NextCounter FROM charVarCharTest";
235             this.rs = this.stmt.executeQuery(query);
236
237             ResultSetMetaData JavaDoc rsmeta = this.rs.getMetaData();
238
239             assertTrue(rsmeta.getColumnTypeName(1).equalsIgnoreCase("VARCHAR"));
240
241             // is "CHAR", expected "VARCHAR"
242
assertTrue(rsmeta.getColumnType(1) == 12);
243
244             // is 1 (java.sql.Types.CHAR), expected 12 (java.sql.Types.VARCHAR)
245
} finally {
246             this.stmt.execute("DROP TABLE IF EXISTS charVarCharTest");
247         }
248     }
249
250     /**
251      * Tests fix for BUG#1673, where DatabaseMetaData.getColumns() is not
252      * returning correct column ordinal info for non '%' column name patterns.
253      *
254      * @throws Exception
255      * if the test fails for any reason
256      */

257     public void testFixForBug1673() throws Exception JavaDoc {
258         try {
259             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1673");
260             this.stmt
261                     .executeUpdate("CREATE TABLE testBug1673 (field_1 INT, field_2 INT)");
262
263             DatabaseMetaData JavaDoc dbmd = this.conn.getMetaData();
264
265             int ordinalPosOfCol2Full = 0;
266
267             this.rs = dbmd.getColumns(this.conn.getCatalog(), null,
268                     "testBug1673", null);
269
270             while (this.rs.next()) {
271                 if (this.rs.getString(4).equals("field_2")) {
272                     ordinalPosOfCol2Full = this.rs.getInt(17);
273                 }
274             }
275
276             int ordinalPosOfCol2Scoped = 0;
277
278             this.rs = dbmd.getColumns(this.conn.getCatalog(), null,
279                     "testBug1673", "field_2");
280
281             while (this.rs.next()) {
282                 if (this.rs.getString(4).equals("field_2")) {
283                     ordinalPosOfCol2Scoped = this.rs.getInt(17);
284                 }
285             }
286
287             assertTrue("Ordinal position in full column list of '"
288                     + ordinalPosOfCol2Full
289                     + "' != ordinal position in pattern search, '"
290                     + ordinalPosOfCol2Scoped + "'.",
291                     (ordinalPosOfCol2Full != 0)
292                             && (ordinalPosOfCol2Scoped != 0)
293                             && (ordinalPosOfCol2Scoped == ordinalPosOfCol2Full));
294         } finally {
295             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1673");
296         }
297     }
298
299     /**
300      * Tests bug reported by OpenOffice team with getColumns and LONGBLOB
301      *
302      * @throws Exception
303      * if any errors occur
304      */

305     public void testGetColumns() throws Exception JavaDoc {
306         try {
307             this.stmt
308                     .execute("CREATE TABLE IF NOT EXISTS longblob_regress(field_1 longblob)");
309
310             DatabaseMetaData JavaDoc dbmd = this.conn.getMetaData();
311             ResultSet JavaDoc dbmdRs = null;
312
313             try {
314                 dbmdRs = dbmd.getColumns("", "", "longblob_regress", "%");
315
316                 while (dbmdRs.next()) {
317                     dbmdRs.getInt(7);
318                 }
319             } finally {
320                 if (dbmdRs != null) {
321                     try {
322                         dbmdRs.close();
323                     } catch (SQLException JavaDoc ex) {
324                         ;
325                     }
326                 }
327             }
328         } finally {
329             this.stmt.execute("DROP TABLE IF EXISTS longblob_regress");
330         }
331     }
332
333     /**
334      * Tests fix for Bug#
335      *
336      * @throws Exception
337      * if an error occurs
338      */

339     public void testGetColumnsBug1099() throws Exception JavaDoc {
340         try {
341             this.stmt
342                     .executeUpdate("DROP TABLE IF EXISTS testGetColumnsBug1099");
343
344             DatabaseMetaData JavaDoc dbmd = this.conn.getMetaData();
345
346             this.rs = dbmd.getTypeInfo();
347
348             StringBuffer JavaDoc types = new StringBuffer JavaDoc();
349
350             HashMap JavaDoc alreadyDoneTypes = new HashMap JavaDoc();
351
352             while (this.rs.next()) {
353                 String JavaDoc typeName = this.rs.getString("TYPE_NAME");
354                 String JavaDoc createParams = this.rs.getString("CREATE_PARAMS");
355
356                 if ((typeName.indexOf("BINARY") == -1)
357                         && !typeName.equals("LONG VARCHAR")) {
358                     if (!alreadyDoneTypes.containsKey(typeName)) {
359                         alreadyDoneTypes.put(typeName, null);
360
361                         if (types.length() != 0) {
362                             types.append(", \n");
363                         }
364
365                         int typeNameLength = typeName.length();
366                         StringBuffer JavaDoc safeTypeName = new StringBuffer JavaDoc(
367                                 typeNameLength);
368
369                         for (int i = 0; i < typeNameLength; i++) {
370                             char c = typeName.charAt(i);
371
372                             if (Character.isWhitespace(c)) {
373                                 safeTypeName.append("_");
374                             } else {
375                                 safeTypeName.append(c);
376                             }
377                         }
378
379                         types.append(safeTypeName);
380                         types.append("Column ");
381                         types.append(typeName);
382
383                         if (typeName.indexOf("CHAR") != -1) {
384                             types.append(" (1)");
385                         } else if (typeName.equalsIgnoreCase("enum")
386                                 || typeName.equalsIgnoreCase("set")) {
387                             types.append("('a', 'b', 'c')");
388                         }
389                     }
390                 }
391             }
392
393             this.stmt.executeUpdate("CREATE TABLE testGetColumnsBug1099("
394                     + types.toString() + ")");
395
396             dbmd.getColumns(null, this.conn.getCatalog(),
397                     "testGetColumnsBug1099", "%");
398         } finally {
399             this.stmt
400                     .executeUpdate("DROP TABLE IF EXISTS testGetColumnsBug1099");
401         }
402     }
403
404     /**
405      * Tests whether or not unsigned columns are reported correctly in
406      * DBMD.getColumns
407      *
408      * @throws Exception
409      */

410     public void testGetColumnsUnsigned() throws Exception JavaDoc {
411         try {
412             this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetUnsignedCols");
413             this.stmt
414                     .executeUpdate("CREATE TABLE testGetUnsignedCols (field1 BIGINT, field2 BIGINT UNSIGNED)");
415
416             DatabaseMetaData JavaDoc dbmd = this.conn.getMetaData();
417
418             this.rs = dbmd.getColumns(this.conn.getCatalog(), null,
419                     "testGetUnsignedCols", "%");
420
421             assertTrue(this.rs.next());
422             // This row doesn't have 'unsigned' attribute
423
assertTrue(this.rs.next());
424             assertTrue(this.rs.getString(6).toLowerCase().indexOf("unsigned") != -1);
425         } finally {
426             this.stmt.executeUpdate("DROP TABLE IF EXISTS testGetUnsignedCols");
427         }
428     }
429
430     /**
431      * Tests whether bogus parameters break Driver.getPropertyInfo().
432      *
433      * @throws Exception
434      * if an error occurs.
435      */

436     public void testGetPropertyInfo() throws Exception JavaDoc {
437         new Driver().getPropertyInfo("", null);
438     }
439
440     /**
441      * Tests whether ResultSetMetaData returns correct info for CHAR/VARCHAR
442      * columns.
443      *
444      * @throws Exception
445      * if the test fails
446      */

447     public void testIsCaseSensitive() throws Exception JavaDoc {
448         try {
449             this.stmt.executeUpdate("DROP TABLE IF EXISTS testIsCaseSensitive");
450             this.stmt
451                     .executeUpdate("CREATE TABLE testIsCaseSensitive (bin_char CHAR(1) BINARY, bin_varchar VARCHAR(64) BINARY, ci_char CHAR(1), ci_varchar VARCHAR(64))");
452             this.rs = this.stmt
453                     .executeQuery("SELECT bin_char, bin_varchar, ci_char, ci_varchar FROM testIsCaseSensitive");
454
455             ResultSetMetaData JavaDoc rsmd = this.rs.getMetaData();
456             assertTrue(rsmd.isCaseSensitive(1));
457             assertTrue(rsmd.isCaseSensitive(2));
458             assertTrue(!rsmd.isCaseSensitive(3));
459             assertTrue(!rsmd.isCaseSensitive(4));
460         } finally {
461             this.stmt.executeUpdate("DROP TABLE IF EXISTS testIsCaseSensitive");
462         }
463
464         if (versionMeetsMinimum(4, 1)) {
465             try {
466                 this.stmt
467                         .executeUpdate("DROP TABLE IF EXISTS testIsCaseSensitiveCs");
468                 this.stmt
469                         .executeUpdate("CREATE TABLE testIsCaseSensitiveCs ("
470                                 + "bin_char CHAR(1) CHARACTER SET latin1 COLLATE latin1_general_cs,"
471                                 + "bin_varchar VARCHAR(64) CHARACTER SET latin1 COLLATE latin1_general_cs,"
472                                 + "ci_char CHAR(1) CHARACTER SET latin1 COLLATE latin1_general_ci,"
473                                 + "ci_varchar VARCHAR(64) CHARACTER SET latin1 COLLATE latin1_general_ci, "
474                                 + "bin_tinytext TINYTEXT CHARACTER SET latin1 COLLATE latin1_general_cs,"
475                                 + "bin_text TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,"
476                                 + "bin_med_text MEDIUMTEXT CHARACTER SET latin1 COLLATE latin1_general_cs,"
477                                 + "bin_long_text LONGTEXT CHARACTER SET latin1 COLLATE latin1_general_cs,"
478                                 + "ci_tinytext TINYTEXT CHARACTER SET latin1 COLLATE latin1_general_ci,"
479                                 + "ci_text TEXT CHARACTER SET latin1 COLLATE latin1_general_ci,"
480                                 + "ci_med_text MEDIUMTEXT CHARACTER SET latin1 COLLATE latin1_general_ci,"
481                                 + "ci_long_text LONGTEXT CHARACTER SET latin1 COLLATE latin1_general_ci)");
482
483                 this.rs = this.stmt
484                         .executeQuery("SELECT bin_char, bin_varchar, ci_char, ci_varchar, bin_tinytext, bin_text, bin_med_text, bin_long_text, ci_tinytext, ci_text, ci_med_text, ci_long_text FROM testIsCaseSensitiveCs");
485
486                 ResultSetMetaData JavaDoc rsmd = this.rs.getMetaData();
487                 assertTrue(rsmd.isCaseSensitive(1));
488                 assertTrue(rsmd.isCaseSensitive(2));
489                 assertTrue(!rsmd.isCaseSensitive(3));
490                 assertTrue(!rsmd.isCaseSensitive(4));
491
492                 assertTrue(rsmd.isCaseSensitive(5));
493                 assertTrue(rsmd.isCaseSensitive(6));
494                 assertTrue(rsmd.isCaseSensitive(7));
495                 assertTrue(rsmd.isCaseSensitive(8));
496
497                 assertTrue(!rsmd.isCaseSensitive(9));
498                 assertTrue(!rsmd.isCaseSensitive(10));
499                 assertTrue(!rsmd.isCaseSensitive(11));
500                 assertTrue(!rsmd.isCaseSensitive(12));
501             } finally {
502                 this.stmt
503                         .executeUpdate("DROP TABLE IF EXISTS testIsCaseSensitiveCs");
504             }
505         }
506     }
507
508     /**
509      * Tests whether or not DatabaseMetaData.getColumns() returns the correct
510      * java.sql.Types info.
511      *
512      * @throws Exception
513      * if the test fails.
514      */

515     public void testLongText() throws Exception JavaDoc {
516         try {
517             this.stmt.executeUpdate("DROP TABLE IF EXISTS testLongText");
518             this.stmt
519                     .executeUpdate("CREATE TABLE testLongText (field1 LONGTEXT)");
520
521             this.rs = this.conn.getMetaData().getColumns(
522                     this.conn.getCatalog(), null, "testLongText", "%");
523
524             assertTrue(this.rs.next());
525
526             assertTrue(this.rs.getInt("DATA_TYPE") == java.sql.Types.LONGVARCHAR);
527         } finally {
528             this.stmt.executeUpdate("DROP TABLE IF EXISTS testLongText");
529         }
530     }
531
532     /**
533      * Tests for types being returned correctly
534      *
535      * @throws Exception
536      * if an error occurs.
537      */

538     public void testTypes() throws Exception JavaDoc {
539         try {
540             this.stmt.execute("DROP TABLE IF EXISTS typesRegressTest");
541             this.stmt.execute("CREATE TABLE typesRegressTest ("
542                     + "varcharField VARCHAR(32)," + "charField CHAR(2),"
543                     + "enumField ENUM('1','2'),"
544                     + "setField SET('1','2','3')," + "tinyblobField TINYBLOB,"
545                     + "mediumBlobField MEDIUMBLOB," + "longblobField LONGBLOB,"
546                     + "blobField BLOB)");
547
548             this.rs = this.stmt.executeQuery("SELECT * from typesRegressTest");
549
550             ResultSetMetaData JavaDoc rsmd = this.rs.getMetaData();
551
552             int numCols = rsmd.getColumnCount();
553
554             for (int i = 0; i < numCols; i++) {
555                 String JavaDoc columnName = rsmd.getColumnName(i + 1);
556                 String JavaDoc columnTypeName = rsmd.getColumnTypeName(i + 1);
557                 System.out.println(columnName + " -> " + columnTypeName);
558             }
559         } finally {
560             this.stmt.execute("DROP TABLE IF EXISTS typesRegressTest");
561         }
562     }
563
564     /**
565      * Tests fix for BUG#4742, 'DOUBLE' mapped twice in getTypeInfo().
566      *
567      * @throws Exception
568      * if the test fails.
569      */

570     public void testBug4742() throws Exception JavaDoc {
571         HashMap JavaDoc clashMap = new HashMap JavaDoc();
572
573         this.rs = this.conn.getMetaData().getTypeInfo();
574
575         while (this.rs.next()) {
576             String JavaDoc name = this.rs.getString(1);
577             assertTrue("Type represented twice in type info, '" + name + "'.",
578                     !clashMap.containsKey(name));
579             clashMap.put(name, name);
580         }
581     }
582
583     /**
584      * Tests fix for BUG#4138, getColumns() returns incorrect JDBC type for
585      * unsigned columns.
586      *
587      * @throws Exception
588      * if the test fails.
589      */

590     public void testBug4138() throws Exception JavaDoc {
591         try {
592             String JavaDoc[] typesToTest = new String JavaDoc[] { "TINYINT", "SMALLINT",
593                     "MEDIUMINT", "INTEGER", "BIGINT", "FLOAT", "DOUBLE",
594                     "DECIMAL" };
595
596             short[] jdbcMapping = new short[] { Types.TINYINT, Types.SMALLINT,
597                     Types.INTEGER, Types.INTEGER, Types.BIGINT, Types.REAL,
598                     Types.DOUBLE, Types.DECIMAL };
599
600             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4138");
601
602             StringBuffer JavaDoc createBuf = new StringBuffer JavaDoc();
603
604             createBuf.append("CREATE TABLE testBug4138 (");
605
606             boolean firstColumn = true;
607
608             for (int i = 0; i < typesToTest.length; i++) {
609                 if (!firstColumn) {
610                     createBuf.append(", ");
611                 } else {
612                     firstColumn = false;
613                 }
614
615                 createBuf.append("field");
616                 createBuf.append((i + 1));
617                 createBuf.append(" ");
618                 createBuf.append(typesToTest[i]);
619                 createBuf.append(" UNSIGNED");
620             }
621             createBuf.append(")");
622             this.stmt.executeUpdate(createBuf.toString());
623
624             DatabaseMetaData JavaDoc dbmd = this.conn.getMetaData();
625             this.rs = dbmd.getColumns(this.conn.getCatalog(), null,
626                     "testBug4138", "field%");
627
628             assertTrue(this.rs.next());
629
630             for (int i = 0; i < typesToTest.length; i++) {
631                 assertTrue(
632                         "JDBC Data Type of "
633                                 + this.rs.getShort("DATA_TYPE")
634                                 + " for MySQL type '"
635                                 + this.rs.getString("TYPE_NAME")
636                                 + "' from 'DATA_TYPE' column does not match expected value of "
637                                 + jdbcMapping[i] + ".",
638                         jdbcMapping[i] == this.rs.getShort("DATA_TYPE"));
639                 this.rs.next();
640             }
641
642             this.rs.close();
643
644             StringBuffer JavaDoc queryBuf = new StringBuffer JavaDoc("SELECT ");
645             firstColumn = true;
646
647             for (int i = 0; i < typesToTest.length; i++) {
648                 if (!firstColumn) {
649                     queryBuf.append(", ");
650                 } else {
651                     firstColumn = false;
652                 }
653
654                 queryBuf.append("field");
655                 queryBuf.append((i + 1));
656             }
657
658             queryBuf.append(" FROM testBug4138");
659
660             this.rs = this.stmt.executeQuery(queryBuf.toString());
661
662             ResultSetMetaData JavaDoc rsmd = this.rs.getMetaData();
663
664             for (int i = 0; i < typesToTest.length; i++) {
665
666                 assertTrue(jdbcMapping[i] == rsmd.getColumnType(i + 1));
667                 String JavaDoc desiredTypeName = typesToTest[i] + " unsigned";
668
669                 assertTrue(rsmd.getColumnTypeName((i + 1)) + " != "
670                         + desiredTypeName, desiredTypeName
671                         .equalsIgnoreCase(rsmd.getColumnTypeName(i + 1)));
672             }
673         } finally {
674             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4138");
675         }
676     }
677
678     /**
679      * Here for housekeeping only, the test is actually in testBug4138().
680      *
681      * @throws Exception
682      * if the test fails.
683      */

684     public void testBug4860() throws Exception JavaDoc {
685         testBug4138();
686     }
687
688     /**
689      * Tests fix for BUG#4880 - RSMD.getPrecision() returns '0' for non-numeric
690      * types.
691      *
692      * Why-oh-why is this not in the spec, nor the api-docs, but in some
693      * 'optional' book, _and_ it is a variance from both ODBC and the ANSI SQL
694      * standard :p
695      *
696      * (from the CTS testsuite)....
697      *
698      * The getPrecision(int colindex) method returns an integer value
699      * representing the number of decimal digits for number types,maximum length
700      * in characters for character types,maximum length in bytes for JDBC binary
701      * datatypes.
702      *
703      * (See Section 27.3 of JDBC 2.0 API Reference & Tutorial 2nd edition)
704      *
705      * @throws Exception
706      * if the test fails.
707      */

708
709     public void testBug4880() throws Exception JavaDoc {
710         try {
711             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4880");
712             this.stmt
713                     .executeUpdate("CREATE TABLE testBug4880 (field1 VARCHAR(80), field2 TINYBLOB, field3 BLOB, field4 MEDIUMBLOB, field5 LONGBLOB)");
714             this.rs = this.stmt
715                     .executeQuery("SELECT field1, field2, field3, field4, field5 FROM testBug4880");
716             ResultSetMetaData JavaDoc rsmd = this.rs.getMetaData();
717
718             assertEquals(80, rsmd.getPrecision(1));
719             assertEquals(Types.VARCHAR, rsmd.getColumnType(1));
720             assertEquals(80, rsmd.getColumnDisplaySize(1));
721
722             assertEquals(255, rsmd.getPrecision(2));
723             assertEquals(Types.VARBINARY, rsmd.getColumnType(2));
724             assertTrue("TINYBLOB".equalsIgnoreCase(rsmd.getColumnTypeName(2)));
725             assertEquals(255, rsmd.getColumnDisplaySize(2));
726
727             assertEquals(65535, rsmd.getPrecision(3));
728             assertEquals(Types.LONGVARBINARY, rsmd.getColumnType(3));
729             assertTrue("BLOB".equalsIgnoreCase(rsmd.getColumnTypeName(3)));
730             assertEquals(65535, rsmd.getColumnDisplaySize(3));
731
732             assertEquals(16777215, rsmd.getPrecision(4));
733             assertEquals(Types.LONGVARBINARY, rsmd.getColumnType(4));
734             assertTrue("MEDIUMBLOB".equalsIgnoreCase(rsmd.getColumnTypeName(4)));
735             assertEquals(16777215, rsmd.getColumnDisplaySize(4));
736
737             // Server doesn't send us enough information to detect LONGBLOB type
738
assertEquals(16777215, rsmd.getPrecision(5));
739             assertEquals(Types.LONGVARBINARY, rsmd.getColumnType(5));
740             assertTrue("MEDIUMBLOB".equalsIgnoreCase(rsmd.getColumnTypeName(5)));
741             assertEquals(16777215, rsmd.getColumnDisplaySize(5));
742         } finally {
743             this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug4880");
744         }
745     }
746
747     /**
748      * Tests fix for BUG#6399, ResultSetMetaData.getDisplaySize() is wrong for
749      * multi-byte charsets.
750      *
751      * @throws Exception
752      * if the test fails
753      */

754     public void testBug6399() throws Exception JavaDoc {
755         if (versionMeetsMinimum(4, 1)) {
756             try {
757                 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6399");
758                 this.stmt
759                         .executeUpdate("CREATE TABLE testBug6399 (field1 CHAR(3) CHARACTER SET UTF8, field2 CHAR(3) CHARACTER SET LATIN1, field3 CHAR(3) CHARACTER SET SJIS)");
760                 this.stmt
761                         .executeUpdate("INSERT INTO testBug6399 VALUES ('a', 'a', 'a')");
762
763                 this.rs = this.stmt
764                         .executeQuery("SELECT field1, field2, field3 FROM testBug6399");
765                 ResultSetMetaData JavaDoc rsmd = this.rs.getMetaData();
766
767                 assertTrue(3 == rsmd.getColumnDisplaySize(1));
768                 assertTrue(3 == rsmd.getColumnDisplaySize(2));
769                 assertTrue(3 == rsmd.getColumnDisplaySize(3));
770             } finally {
771                 this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6399");
772             }
773         }
774     }
775
776     /**
777      * Tests fix for BUG#7081, DatabaseMetaData.getIndexInfo() ignoring 'unique'
778      * parameters.
779      *
780      * @throws Exception
781      * if the test fails.
782      */

783     public void testBug7081() throws Exception JavaDoc {
784         String JavaDoc tableName = "testBug7081";
785
786         try {
787             createTable(tableName, "(field1 INT, INDEX(field1))");
788
789             DatabaseMetaData JavaDoc dbmd = this.conn.getMetaData();
790             this.rs = dbmd.getIndexInfo(this.conn.getCatalog(), null,
791                     tableName, true, false);
792             assertTrue(!this.rs.next()); // there should be no rows that meet
793
// this requirement
794

795             this.rs = dbmd.getIndexInfo(this.conn.getCatalog(), null,
796                     tableName, false, false);
797             assertTrue(this.rs.next()); // there should be one row that meets
798
// this requirement
799
assertTrue(!this.rs.next());
800
801         } finally {
802             dropTable(tableName);
803         }
804     }
805
806     /**
807      * Tests fix for BUG#7033 - PreparedStatements don't encode Big5 (and other
808      * multibyte) character sets correctly in static SQL strings.
809      *
810      * @throws Exception
811      * if the test fails.
812      */

813     public void testBug7033() throws Exception JavaDoc {
814         Connection JavaDoc big5Conn = null;
815         Statement JavaDoc big5Stmt = null;
816         PreparedStatement JavaDoc big5PrepStmt = null;
817
818         String JavaDoc testString = "\u5957 \u9910";
819
820         try {
821             Properties JavaDoc props = new Properties JavaDoc();
822             props.setProperty("useUnicode", "true");
823             props.setProperty("characterEncoding", "Big5");
824
825             big5Conn = getConnectionWithProps(props);
826             big5Stmt = big5Conn.createStatement();
827
828             byte[] foobar = testString.getBytes("Big5");
829             System.out.println(foobar);
830
831             this.rs = big5Stmt.executeQuery("select 1 as '\u5957 \u9910'");
832             String JavaDoc retrString = this.rs.getMetaData().getColumnName(1);
833             assertTrue(testString.equals(retrString));
834
835             big5PrepStmt = big5Conn
836                     .prepareStatement("select 1 as '\u5957 \u9910'");
837             this.rs = big5PrepStmt.executeQuery();
838             retrString = this.rs.getMetaData().getColumnName(1);
839             assertTrue(testString.equals(retrString));
840         } finally {
841             if (this.rs != null) {
842                 this.rs.close();
843                 this.rs = null;
844             }
845
846             if (big5Stmt != null) {
847                 big5Stmt.close();
848
849             }
850
851             if (big5PrepStmt != null) {
852                 big5PrepStmt.close();
853             }
854
855             if (big5Conn != null) {
856                 big5Conn.close();
857             }
858         }
859     }
860
861     /**
862      * Tests fix for Bug#8812, DBMD.getIndexInfo() returning inverted values for
863      * 'NON_UNIQUE' column.
864      *
865      * @throws Exception
866      * if the test fails.
867      */

868     public void testBug8812() throws Exception JavaDoc {
869         String JavaDoc tableName = "testBug8812";
870
871         try {
872             createTable(tableName,
873                     "(field1 INT, field2 INT, INDEX(field1), UNIQUE INDEX(field2))");
874
875             DatabaseMetaData JavaDoc dbmd = this.conn.getMetaData();
876             this.rs = dbmd.getIndexInfo(this.conn.getCatalog(), null,
877                     tableName, true, false);
878             assertTrue(this.rs.next()); // there should be one row that meets
879
// this requirement
880
assertEquals(this.rs.getBoolean("NON_UNIQUE"), false);
881
882             this.rs = dbmd.getIndexInfo(this.conn.getCatalog(), null,
883                     tableName, false, false);
884             assertTrue(this.rs.next()); // there should be two rows that meets
885
// this requirement
886
assertEquals(this.rs.getBoolean("NON_UNIQUE"), false);
887             assertTrue(this.rs.next());
888             assertEquals(this.rs.getBoolean("NON_UNIQUE"), true);
889
890         } finally {
891             dropTable(tableName);
892         }
893     }
894
895     /**
896      * Tests fix for BUG#8800 - supportsMixedCase*Identifiers() returns wrong
897      * value on servers running on case-sensitive filesystems.
898      */

899
900     public void testBug8800() throws Exception JavaDoc {
901         assertEquals(((com.mysql.jdbc.Connection) this.conn)
902                 .lowerCaseTableNames(), !this.conn.getMetaData()
903                 .supportsMixedCaseIdentifiers());
904         assertEquals(((com.mysql.jdbc.Connection) this.conn)
905                 .lowerCaseTableNames(), !this.conn.getMetaData()
906                 .supportsMixedCaseQuotedIdentifiers());
907
908     }
909
910     /**
911      * Tests fix for BUG#8792 - DBMD.supportsResultSetConcurrency() not
912      * returning true for forward-only/read-only result sets (we obviously
913      * support this).
914      *
915      * @throws Exception
916      * if the test fails.
917      */

918     public void testBug8792() throws Exception JavaDoc {
919         DatabaseMetaData JavaDoc dbmd = this.conn.getMetaData();
920
921         assertTrue(dbmd.supportsResultSetConcurrency(
922                 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY));
923
924         assertTrue(dbmd.supportsResultSetConcurrency(
925                 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE));
926
927         assertTrue(dbmd.supportsResultSetConcurrency(
928                 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
929
930         assertTrue(dbmd.supportsResultSetConcurrency(
931                 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE));
932
933         assertTrue(!dbmd.supportsResultSetConcurrency(
934                 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY));
935
936         assertTrue(!dbmd.supportsResultSetConcurrency(
937                 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE));
938
939         // Check error conditions
940
try {
941             dbmd.supportsResultSetConcurrency(ResultSet.TYPE_FORWARD_ONLY,
942                     Integer.MIN_VALUE);
943             fail("Exception should've been raised for bogus concurrency value");
944         } catch (SQLException JavaDoc sqlEx) {
945             assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
946                     .getSQLState()));
947         }
948
949         try {
950             assertTrue(dbmd.supportsResultSetConcurrency(
951                     ResultSet.TYPE_SCROLL_INSENSITIVE, Integer.MIN_VALUE));
952             fail("Exception should've been raised for bogus concurrency value");
953         } catch (SQLException JavaDoc sqlEx) {
954             assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
955                     .getSQLState()));
956         }
957
958         try {
959             assertTrue(dbmd.supportsResultSetConcurrency(Integer.MIN_VALUE,
960                     Integer.MIN_VALUE));
961             fail("Exception should've been raised for bogus concurrency value");
962         } catch (SQLException JavaDoc sqlEx) {
963             assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx
964                     .getSQLState()));
965         }
966     }
967
968     /**
969      * Tests fix for BUG#8803, 'DATA_TYPE' column from
970      * DBMD.getBestRowIdentifier() causes ArrayIndexOutOfBoundsException when
971      * accessed (and in fact, didn't return any value).
972      *
973      * @throws Exception
974      * if the test fails.
975      */

976     public void testBug8803() throws Exception JavaDoc {
977         String JavaDoc tableName = "testBug8803";
978         createTable(tableName, "(field1 INT NOT NULL PRIMARY KEY)");
979         DatabaseMetaData JavaDoc metadata = this.conn.getMetaData();
980         try {
981             this.rs = metadata.getBestRowIdentifier(this.conn.getCatalog(),
982                     null, tableName, DatabaseMetaData.bestRowNotPseudo, true);
983
984             assertTrue(this.rs.next());
985
986             this.rs.getInt("DATA_TYPE"); // **** Fails here *****
987
} finally {
988             if (this.rs != null) {
989                 this.rs.close();
990
991                 this.rs = null;
992             }
993         }
994
995     }
996
997     /**
998      * Tests fix for BUG#9320 - PreparedStatement.getMetaData() inserts blank
999      * row in database under certain conditions when not using server-side
1000     * prepared statements.
1001     *
1002     * @throws Exception
1003     * if the test fails.
1004     */

1005    public void testBug9320() throws Exception JavaDoc {
1006        createTable("testBug9320", "(field1 int)");
1007
1008        testAbsenceOfMetadataForQuery("INSERT INTO testBug9320 VALUES (?)");
1009        testAbsenceOfMetadataForQuery("UPDATE testBug9320 SET field1=?");
1010        testAbsenceOfMetadataForQuery("DELETE FROM testBug9320 WHERE field1=?");
1011    }
1012
1013    /**
1014     * Tests fix for BUG#9778, DBMD.getTables() shouldn't return tables if views
1015     * are asked for, even if the database version doesn't support views.
1016     *
1017     * @throws Exception
1018     * if the test fails.
1019     */

1020    public void testBug9778() throws Exception JavaDoc {
1021        String JavaDoc tableName = "testBug9778";
1022
1023        try {
1024            createTable(tableName, "(field1 int)");
1025            this.rs = this.conn.getMetaData().getTables(null, null, tableName,
1026                    new String JavaDoc[] { "VIEW" });
1027            assertEquals(false, this.rs.next());
1028
1029            this.rs = this.conn.getMetaData().getTables(null, null, tableName,
1030                    new String JavaDoc[] { "TABLE" });
1031            assertEquals(true, this.rs.next());
1032        } finally {
1033            if (this.rs != null) {
1034                this.rs.close();
1035                this.rs = null;
1036            }
1037        }
1038    }
1039
1040    /**
1041     * Tests fix for BUG#9769 - Should accept null for procedureNamePattern,
1042     * even though it isn't JDBC compliant, for legacy's sake.
1043     *
1044     * @throws Exception
1045     * if the test fails.
1046     */

1047    public void testBug9769() throws Exception JavaDoc {
1048        boolean defaultPatternConfig = ((com.mysql.jdbc.Connection) this.conn)
1049                .getNullNamePatternMatchesAll();
1050
1051        // We're going to change this in 3.2.x, so make that test here, so we
1052
// catch it.
1053

1054        if (this.conn.getMetaData().getDriverMajorVersion() == 3
1055                && this.conn.getMetaData().getDriverMinorVersion() >= 2) {
1056            assertEquals(false, defaultPatternConfig);
1057        } else {
1058            assertEquals(true, defaultPatternConfig);
1059        }
1060
1061        try {
1062            this.conn.getMetaData().getProcedures(this.conn.getCatalog(), "%",
1063                    null);
1064
1065            if (!defaultPatternConfig) {
1066                // we shouldn't have gotten here
1067
fail("Exception should've been thrown");
1068            }
1069        } catch (SQLException JavaDoc sqlEx) {
1070            if (!defaultPatternConfig) {
1071                assertEquals(SQLError.SQL_STATE_ILLEGAL_ARGUMENT, sqlEx
1072                        .getSQLState());
1073            } else {
1074                throw sqlEx; // we shouldn't have gotten an exception here
1075
}
1076        }
1077
1078        // FIXME: TO test for 3.1.9
1079
// getColumns();
1080
// getTablePrivileges();
1081
// getTables();
1082

1083    }
1084
1085    /**
1086     * Tests fix for BUG#9917 - Should accept null for catalog in DBMD methods,
1087     * even though it's not JDBC-compliant for legacy's sake.
1088     *
1089     * @throws Exception
1090     * if the test fails.
1091     */

1092    public void testBug9917() throws Exception JavaDoc {
1093        String JavaDoc tableName = "testBug9917";
1094        boolean defaultCatalogConfig = ((com.mysql.jdbc.Connection) this.conn)
1095                .getNullCatalogMeansCurrent();
1096
1097        // We're going to change this in 3.2.x, so make that test here, so we
1098
// catch it.
1099

1100        if (this.conn.getMetaData().getDriverMajorVersion() == 3
1101                && this.conn.getMetaData().getDriverMinorVersion() >= 2) {
1102            assertEquals(false, defaultCatalogConfig);
1103        } else {
1104            assertEquals(true, defaultCatalogConfig);
1105        }
1106
1107        try {
1108            createTable(tableName, "(field1 int)");
1109            String JavaDoc currentCatalog = this.conn.getCatalog();
1110
1111            try {
1112                this.rs = this.conn.getMetaData().getTables(null, null,
1113                        tableName, new String JavaDoc[] { "TABLE" });
1114
1115                if (!defaultCatalogConfig) {
1116                    // we shouldn't have gotten here
1117
fail("Exception should've been thrown");
1118                }
1119
1120                assertEquals(true, this.rs.next());
1121                assertEquals(currentCatalog, this.rs.getString("TABLE_CAT"));
1122
1123                // FIXME: Methods to test for 3.1.9
1124
//
1125
// getBestRowIdentifier()
1126
// getColumns()
1127
// getCrossReference()
1128
// getExportedKeys()
1129
// getImportedKeys()
1130
// getIndexInfo()
1131
// getPrimaryKeys()
1132
// getProcedures()
1133

1134            } catch (SQLException JavaDoc sqlEx) {
1135                if (!defaultCatalogConfig) {
1136                    assertEquals(SQLError.SQL_STATE_ILLEGAL_ARGUMENT, sqlEx
1137                            .getSQLState());
1138                } else {
1139                    throw sqlEx; // we shouldn't have gotten an exception
1140
// here
1141
}
1142            }
1143
1144        } finally {
1145            if (this.rs != null) {
1146                this.rs.close();
1147                this.rs = null;
1148            }
1149        }
1150    }
1151
1152    private void testAbsenceOfMetadataForQuery(String JavaDoc query) throws Exception JavaDoc {
1153        try {
1154            this.pstmt = this.conn.prepareStatement(query);
1155            ResultSetMetaData JavaDoc rsmd = this.pstmt.getMetaData();
1156
1157            assertNull(rsmd);
1158
1159            this.pstmt = ((com.mysql.jdbc.Connection) this.conn)
1160                    .clientPrepareStatement(query);
1161            rsmd = this.pstmt.getMetaData();
1162
1163            assertNull(rsmd);
1164        } finally {
1165            if (this.pstmt != null) {
1166                this.pstmt.close();
1167            }
1168        }
1169    }
1170}
1171
Popular Tags