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 ;
28 import java.sql.DatabaseMetaData ;
29 import java.sql.PreparedStatement ;
30 import java.sql.ResultSet ;
31 import java.sql.ResultSetMetaData ;
32 import java.sql.SQLException ;
33 import java.sql.Statement ;
34 import java.sql.Types ;
35 import java.util.HashMap ;
36 import java.util.Properties ;
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 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 [] 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 {
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 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 {
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 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 {
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 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 {
158         String 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 dbmdRs = this.conn.getMetaData().getColumns(
192                     this.conn.getCatalog(), null, "testBug3570", "%");
193
194             this.rs = this.stmt.executeQuery("SELECT * FROM testBug3570");
195
196             ResultSetMetaData rsmd = this.rs.getMetaData();
197
198             while (dbmdRs.next()) {
199                 String 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 {
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 query = "SELECT TableName, FieldName, NextCounter FROM charVarCharTest";
235             this.rs = this.stmt.executeQuery(query);
236
237             ResultSetMetaData 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 {
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 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 {
306         try {
307             this.stmt
308                     .execute("CREATE TABLE IF NOT EXISTS longblob_regress(field_1 longblob)");
309
310             DatabaseMetaData dbmd = this.conn.getMetaData();
311             ResultSet 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 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 {
340         try {
341             this.stmt
342                     .executeUpdate("DROP TABLE IF EXISTS testGetColumnsBug1099");
343
344             DatabaseMetaData dbmd = this.conn.getMetaData();
345
346             this.rs = dbmd.getTypeInfo();
347
348             StringBuffer types = new StringBuffer ();
349
350             HashMap alreadyDoneTypes = new HashMap ();
351
352             while (this.rs.next()) {
353                 String typeName = this.rs.getString("TYPE_NAME");
354                 String 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 safeTypeName = new StringBuffer (
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 {
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 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 {
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 {
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 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 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 {
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 {
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 rsmd = this.rs.getMetaData();
551
552             int numCols = rsmd.getColumnCount();
553
554             for (int i = 0; i < numCols; i++) {
555                 String columnName = rsmd.getColumnName(i + 1);
556                 String 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 {
571         HashMap clashMap = new HashMap ();
572
573         this.rs = this.conn.getMetaData().getTypeInfo();
574
575         while (this.rs.next()) {
576             String 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 {
591         try {
592             String [] typesToTest = new String [] { "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 createBuf = new StringBuffer ();
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 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 queryBuf = new StringBuffer ("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 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 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 {
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 {
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 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 {
755         if (versionMeetsMinimum(4, 1)) {
756             try {
757