CallableStatementRegressionTest


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.CallableStatement  ;
28  import java.sql.Connection  ;
29  import java.sql.SQLException  ;
30  import java.sql.Types  ;
31  
32  import com.mysql.jdbc.DatabaseMetaData;
33  import com.mysql.jdbc.SQLError;
34  
35  import testsuite.BaseTestCase;
36  
37  /**
38   * Tests fixes for bugs in CallableStatement code.
39   * 
40   * @version $Id: CallableStatementRegressionTest.java,v 1.1.2.6 2004/12/09
41   *          15:57:26 mmatthew Exp $
42   */
43  public class CallableStatementRegressionTest extends BaseTestCase {
44      /**
45       * DOCUMENT ME!
46       * 
47       * @param name
48       */
49      public CallableStatementRegressionTest(String   name) {
50          super(name);
51  
52          // TODO Auto-generated constructor stub
53      }
54  
55      /**
56       * Runs all test cases in this test suite
57       * 
58       * @param args
59       *            ignored
60       */
61      public static void main(String  [] args) {
62          junit.textui.TestRunner.run(CallableStatementRegressionTest.class);
63      }
64  
65      /**
66       * Tests fix for BUG#3539 getProcedures() does not return any procedures in
67       * result set
68       * 
69       * @throws Exception
70       *             if an error occurs.
71       */
72      public void testBug3539() throws Exception   {
73          if (versionMeetsMinimum(5, 0)) {
74              try {
75                  this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3539");
76                  this.stmt.executeUpdate("CREATE PROCEDURE testBug3539()\n"
77                          + "BEGIN\n" + "SELECT 1;" + "end\n");
78  
79                  this.rs = this.conn.getMetaData().getProcedures(null, null,
80                          "testBug3539");
81  
82                  assertTrue(this.rs.next());
83                  assertTrue("testBug3539".equals(this.rs.getString(3)));
84              } finally {
85                  this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3539");
86              }
87          }
88      }
89  
90      /**
91       * Tests fix for BUG#3540 getProcedureColumns doesn't work with wildcards
92       * for procedure name
93       * 
94       * @throws Exception
95       *             if an error occurs.
96       */
97      public void testBug3540() throws Exception   {
98          if (versionMeetsMinimum(5, 0)) {
99              try {
100                 this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3540");
101                 this.stmt
102                         .executeUpdate("CREATE PROCEDURE testBug3540(x int, out y int)\n"
103                                 + "BEGIN\n" + "SELECT 1;" + "end\n");
104 
105                 this.rs = this.conn.getMetaData().getProcedureColumns(null,
106                         null, "testBug3540%", "%");
107 
108                 assertTrue(this.rs.next());
109                 assertTrue("testBug3540".equals(this.rs.getString(3)));
110                 assertTrue("x".equals(this.rs.getString(4)));
111 
112                 assertTrue(this.rs.next());
113                 assertTrue("testBug3540".equals(this.rs.getString(3)));
114                 assertTrue("y".equals(this.rs.getString(4)));
115 
116                 assertTrue(!this.rs.next());
117             } finally {
118                 this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug3540");
119             }
120         }
121     }
122 
123     /**
124      * Tests fix for BUG#7026 - DBMD.getProcedures() doesn't respect catalog
125      * parameter
126      * 
127      * @throws Exception
128      *             if the test fails.
129      */
130     public void testBug7026() throws Exception   {
131         if (versionMeetsMinimum(5, 0)) {
132             try {
133                 this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug7026");
134                 this.stmt
135                         .executeUpdate("CREATE PROCEDURE testBug7026(x int, out y int)\n"
136                                 + "BEGIN\n" + "SELECT 1;" + "end\n");
137 
138                 //
139                 // Should be found this time.
140                 //
141                 this.rs = this.conn.getMetaData().getProcedures(
142                         this.conn.getCatalog(), null, "testBug7026");
143 
144                 assertTrue(this.rs.next());
145                 assertTrue("testBug7026".equals(this.rs.getString(3)));
146 
147                 assertTrue(!this.rs.next());
148 
149                 //
150                 // This time, shouldn't be found, because not associated with
151                 // this (bogus) catalog
152                 //
153                 this.rs = this.conn.getMetaData().getProcedures("abfgerfg",
154                         null, "testBug7026");
155                 assertTrue(!this.rs.next());
156 
157                 //
158                 // Should be found this time as well, as we haven't
159                 // specified a catalog.
160                 //
161                 this.rs = this.conn.getMetaData().getProcedures(null, null,
162                         "testBug7026");
163 
164                 assertTrue(this.rs.next());
165                 assertTrue("testBug7026".equals(this.rs.getString(3)));
166 
167                 assertTrue(!this.rs.next());
168             } finally {
169                 this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug7026");
170             }
171         }
172     }
173 
174     /**
175      * Tests fix for BUG#9319 -- Stored procedures with same name in different
176      * databases confuse the driver when it tries to determine parameter
177      * counts/types.
178      * 
179      * @throws Exception
180      *             if the test fails
181      */
182     public void testBug9319() throws Exception   {
183         boolean doASelect = true; // SELECT currently causes the server to
184                                     // hang on the
185         // last execution of this testcase, filed as BUG#9405
186 
187         if (versionMeetsMinimum(5, 0, 2)) {
188             if (isAdminConnectionConfigured()) {
189                 Connection   db2Connection = null;
190                 Connection   db1Connection = null;
191 
192                 try {
193                     db2Connection = getAdminConnection();
194                     db1Connection = getAdminConnection();
195 
196                     db2Connection.createStatement().executeUpdate(
197                             "CREATE DATABASE IF NOT EXISTS db_9319_2");
198                     db2Connection.setCatalog("db_9319_2");
199 
200                     db2Connection.createStatement().executeUpdate(
201                             "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI");
202 
203                     db2Connection
204                             .createStatement()
205                             .executeUpdate(
206                                     "CREATE PROCEDURE COMPROVAR_USUARI(IN p_CodiUsuari VARCHAR(10),"
207                                             + "\nIN p_contrasenya VARCHAR(10),"
208                                             + "\nOUT p_userId INTEGER,"
209                                             + "\nOUT p_userName VARCHAR(30),"
210                                             + "\nOUT p_administrador VARCHAR(1),"
211                                             + "\nOUT p_idioma VARCHAR(2))"
212                                             + "\nBEGIN"
213 
214                                             + (doASelect ? "\nselect 2;"
215                                                     : "\nSELECT 2 INTO p_administrador;")
216                                             + "\nEND");
217 
218                     db1Connection.createStatement().executeUpdate(
219                             "CREATE DATABASE IF NOT EXISTS db_9319_1");
220                     db1Connection.setCatalog("db_9319_1");
221 
222                     db1Connection.createStatement().executeUpdate(
223                             "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI");
224                     db1Connection
225                             .createStatement()
226                             .executeUpdate(
227                                     "CREATE PROCEDURE COMPROVAR_USUARI(IN p_CodiUsuari VARCHAR(10),"
228                                             + "\nIN p_contrasenya VARCHAR(10),"
229                                             + "\nOUT p_userId INTEGER,"
230                                             + "\nOUT p_userName VARCHAR(30),"
231                                             + "\nOUT p_administrador VARCHAR(1))"
232                                             + "\nBEGIN"
233                                             + (doASelect ? "\nselect 1;"
234                                                     : "\nSELECT 1 INTO p_administrador;")
235                                             + "\nEND");
236 
237                     CallableStatement   cstmt = db2Connection
238                             .prepareCall("{ call COMPROVAR_USUARI(?, ?, ?, ?, ?, ?) }");
239                     cstmt.setString(1, "abc");
240                     cstmt.setString(2, "def");
241                     cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
242                     cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
243                     cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
244 
245                     cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
246 
247                     cstmt.execute();
248 
249                     if (doASelect) {
250                         this.rs = cstmt.getResultSet();
251                         assertTrue(this.rs.next());
252                         assertEquals(2, this.rs.getInt(1));
253                     } else {
254                         assertEquals(2, cstmt.getInt(5));
255                     }
256 
257                     cstmt = db1Connection
258                             .prepareCall("{ call COMPROVAR_USUARI(?, ?, ?, ?, ?, ?) }");
259                     cstmt.setString(1, "abc");
260                     cstmt.setString(2, "def");
261                     cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
262                     cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
263                     cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
264 
265                     try {
266                         cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
267                         fail("Should've thrown an exception");
268                     } catch (SQLException   sqlEx) {
269                         assertEquals(SQLError.SQL_STATE_ILLEGAL_ARGUMENT, sqlEx
270                                 .getSQLState());
271                     }
272 
273                     cstmt = db1Connection
274                             .prepareCall("{ call COMPROVAR_USUARI(?, ?, ?, ?, ?) }");
275                     cstmt.setString(1, "abc");
276                     cstmt.setString(2, "def");
277                     cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
278                     cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
279                     cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
280 
281                     cstmt.execute();
282 
283                     if (doASelect) {
284                         this.rs = cstmt.getResultSet();
285                         assertTrue(this.rs.next());
286                         assertEquals(1, this.rs.getInt(1));
287                     } else {
288                         assertEquals(1, cstmt.getInt(5));
289                     }
290 
291                     String   quoteChar = db2Connection.getMetaData()
292                             .getIdentifierQuoteString();
293 
294                     cstmt = db2Connection.prepareCall("{ call " + quoteChar
295                             + db1Connection.getCatalog() + quoteChar + "."
296                             + quoteChar + "COMPROVAR_USUARI" + quoteChar
297                             + "(?, ?, ?, ?, ?) }");
298                     cstmt.setString(1, "abc");
299                     cstmt.setString(2, "def");
300                     cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
301                     cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
302                     cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
303 
304                     cstmt.execute();
305 
306                     if (doASelect) {
307                         this.rs = cstmt.getResultSet();
308                         assertTrue(this.rs.next());
309                         assertEquals(1, this.rs.getInt(1));
310                     } else {
311                         assertEquals(1, cstmt.getInt(5));
312                     }
313                 } finally {
314                     if (db2Connection != null) {
315                         db2Connection.createStatement().executeUpdate(
316                                 "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI");
317                         db2Connection.createStatement().executeUpdate(
318                                 "DROP DATABASE IF EXISTS db_9319_2");
319                     }
320 
321                     if (db1Connection != null) {
322                         db1Connection.createStatement().executeUpdate(
323                                 "DROP PROCEDURE IF EXISTS COMPROVAR_USUARI");
324                         db1Connection.createStatement().executeUpdate(
325                                 "DROP DATABASE IF EXISTS db_9319_1");
326                     }
327                 }
328             }
329         }
330     }
331 
332     /*
333      * public void testBug9319() throws Exception { boolean doASelect = false; //
334      * SELECT currently causes the server to hang on the // last execution of
335      * this testcase, filed as BUG#9405
336      * 
337      * if (versionMeetsMinimum(5, 0, 2)) { if (isAdminConnectionConfigured()) {
338      * Connection db2Connection = null; Connection db1Connection = null;
339      * 
340      * try { db2Connection = getAdminConnection();
341      * 
342      * db2Connection.createStatement().executeUpdate( "CREATE DATABASE IF NOT
343      * EXISTS db_9319"); db2Connection.setCatalog("db_9319");
344      * 
345      * db2Connection.createStatement().executeUpdate( "DROP PROCEDURE IF EXISTS
346      * COMPROVAR_USUARI");
347      * 
348      * db2Connection.createStatement().executeUpdate( "CREATE PROCEDURE
349      * COMPROVAR_USUARI(IN p_CodiUsuari VARCHAR(10)," + "\nIN p_contrasenya
350      * VARCHAR(10)," + "\nOUT p_userId INTEGER," + "\nOUT p_userName
351      * VARCHAR(30)," + "\nOUT p_administrador VARCHAR(1)," + "\nOUT p_idioma
352      * VARCHAR(2))" + "\nBEGIN"
353      *  + (doASelect ? "\nselect 2;" : "\nSELECT 2 INTO p_administrador;" ) +
354      * "\nEND");
355      * 
356      * this.stmt .executeUpdate("DROP PROCEDURE IF EXISTS COMPROVAR_USUARI");
357      * this.stmt .executeUpdate("CREATE PROCEDURE COMPROVAR_USUARI(IN
358      * p_CodiUsuari VARCHAR(10)," + "\nIN p_contrasenya VARCHAR(10)," + "\nOUT
359      * p_userId INTEGER," + "\nOUT p_userName VARCHAR(30)," + "\nOUT
360      * p_administrador VARCHAR(1))" + "\nBEGIN" + (doASelect ? "\nselect 1;" :
361      * "\nSELECT 1 INTO p_administrador;" ) + "\nEND");
362      * 
363      * CallableStatement cstmt = db2Connection .prepareCall("{ call
364      * COMPROVAR_USUARI(?, ?, ?, ?, ?, ?) }"); cstmt.setString(1, "abc");
365      * cstmt.setString(2, "def"); cstmt.registerOutParameter(3,
366      * java.sql.Types.INTEGER); cstmt.registerOutParameter(4,
367      * java.sql.Types.VARCHAR); cstmt.registerOutParameter(5,
368      * java.sql.Types.VARCHAR);
369      * 
370      * cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
371      * 
372      * cstmt.execute();
373      * 
374      * if (doASelect) { this.rs = cstmt.getResultSet();
375      * assertTrue(this.rs.next()); assertEquals(2, this.rs.getInt(1)); } else {
376      * assertEquals(2, cstmt.getInt(5)); }
377      * 
378      * cstmt = this.conn .prepareCall("{ call COMPROVAR_USUARI(?, ?, ?, ?, ?, ?)
379      * }"); cstmt.setString(1, "abc"); cstmt.setString(2, "def");
380      * cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
381      * cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
382      * cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
383      * 
384      * try { cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
385      * fail("Should've thrown an exception"); } catch (SQLException sqlEx) {
386      * assertEquals(SQLError.SQL_STATE_ILLEGAL_ARGUMENT, sqlEx .getSQLState()); }
387      * 
388      * cstmt = this.conn .prepareCall("{ call COMPROVAR_USUARI(?, ?, ?, ?, ?)
389      * }"); cstmt.setString(1, "abc"); cstmt.setString(2, "def");
390      * cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
391      * cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
392      * cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
393      * 
394      * cstmt.execute();
395      * 
396      * if (doASelect) { this.rs = cstmt.getResultSet();
397      * assertTrue(this.rs.next()); assertEquals(1, this.rs.getInt(1)); } else {
398      * assertEquals(1, cstmt.getInt(5)); }
399      * 
400      * String quoteChar =
401      * db2Connection.getMetaData().getIdentifierQuoteString();
402      * 
403      * cstmt = db2Connection .prepareCall("{ call " + quoteChar +
404      * this.conn.getCatalog() + quoteChar + "." + quoteChar + "COMPROVAR_USUARI" +
405      * quoteChar + "(?, ?, ?, ?, ?) }"); cstmt.setString(1, "abc");
406      * cstmt.setString(2, "def"); cstmt.registerOutParameter(3,
407      * java.sql.Types.INTEGER); cstmt.registerOutParameter(4,
408      * java.sql.Types.VARCHAR); cstmt.registerOutParameter(5,
409      * java.sql.Types.VARCHAR);
410      * 
411      * cstmt.execute();
412      * 
413      * if (doASelect) { this.rs = cstmt.getResultSet();
414      * assertTrue(this.rs.next()); assertEquals(1, this.rs.getInt(1)); } else {
415      * assertEquals(1, cstmt.getInt(5)); } } finally { if (db2Connection !=
416      * null) { db2Connection.createStatement().executeUpdate( "DROP PROCEDURE IF
417      * EXISTS COMPROVAR_USUARI"); //
418      * db2Connection.createStatement().executeUpdate( // "DROP DATABASE IF
419      * EXISTS db_9319"); }
420      * 
421      * this.stmt .executeUpdate("DROP PROCEDURE IF EXISTS COMPROVAR_USUARI"); } } } }
422      */
423 
424     /**
425      * Tests fix for BUG#9682 - Stored procedures with DECIMAL parameters with
426      * storage specifications that contained "," in them would fail.
427      * 
428      * @throws Exception
429      *             if the test fails.
430      */
431     public void testBug9682() throws Exception   {
432         if (versionMeetsMinimum(5, 0)) {
433             CallableStatement   cStmt = null;
434 
435             try {
436                 this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug9682");
437                 this.stmt
438                         .executeUpdate("CREATE PROCEDURE testBug9682(decimalParam DECIMAL(18,0))"
439                                 + "\nBEGIN" + "\n   SELECT 1;" + "\nEND");
440                 cStmt = this.conn.prepareCall("Call testBug9682(?)");
441                 cStmt.setDouble(1, 18.0);
442                 cStmt.execute();
443             } finally {
444                 if (cStmt != null) {
445                     cStmt.close();
446                 }
447 
448                 this.stmt.executeUpdate("DROP PROCEDURE IF EXISTS testBug9682");
449             }
450         }
451     }
452 
453     /**
454      * Tests fix forBUG#10310 - Driver doesn't support {?=CALL(...)} for calling
455      * stored functions. This involved adding support for function retrieval to
456      * DatabaseMetaData.getProcedures() and getProcedureColumns() as well.
457      * 
458      * @throws Exception
459      *             if the test fails.
460      */
461     public void testBug10310() throws Exception   {
462         if (versionMeetsMinimum(5, 0)) {
463             CallableStatement   cStmt = null;
464 
465             try {
466                 this.stmt.executeUpdate("DROP FUNCTION IF EXISTS testBug10310");
467                 this.stmt
468                         .executeUpdate("CREATE FUNCTION testBug10310(a float) RETURNS INT"
469                                 + "\nBEGIN" + "\nRETURN a;" + "\nEND");
470                 cStmt = this.conn.prepareCall("{? = CALL testBug10310(?)}");
471                 cStmt.registerOutParameter(1, Types.INTEGER);
472                 cStmt.setFloat(1, 2);
473                 assertFalse(cStmt.execute());
474                 assertEquals(2f, cStmt.getInt(1), .001);
475                 assertEquals("java.lang.Integer", cStmt.getObject(1).getClass()
476                         .getName());
477 
478                 assertEquals(-1, cStmt.executeUpdate());
479                 assertEquals(2f, cStmt.getInt(1), .001);
480                 assertEquals("java.lang.Integer", cStmt.getObject(1).getClass()
481                         .getName());
482 
483                 // Check metadata while we're at it
484 
485                 java.sql.DatabaseMetaData   dbmd = this.conn.getMetaData();
486 
487                 this.rs = dbmd.getProcedures(this.conn.getCatalog(), null,
488                         "testBug10310");
489                 this.rs.next();
490                 assertEquals("testBug10310", this.rs
491                         .getString("PROCEDURE_NAME"));
492                 assertEquals(DatabaseMetaData.procedureReturnsResult, this.rs
493                         .getShort("PROCEDURE_TYPE"));
494                 cStmt.setNull(1, Types.FLOAT);
495 
496                 assertFalse(cStmt.execute());
497                 assertEquals(0f, cStmt.getInt(1), .001);
498                 assertEquals(true, cStmt.wasNull());
499                 assertEquals(null, cStmt.getObject(1));
500                 assertEquals(true, cStmt.wasNull());
501 
502                 assertEquals(-1, cStmt.executeUpdate());
503                 assertEquals(0f, cStmt.getInt(1), .001);
504                 assertEquals(true, cStmt.wasNull());
505                 assertEquals(null, cStmt.getObject(1));
506                 assertEquals(true, cStmt.wasNull());
507 
508             } finally {
509                 if (this.rs != null) {
510                     this.rs.close();
511                     this.rs = null;
512                 }
513 
514                 if (cStmt != null) {
515                     cStmt.close();
516                 }
517 
518                 this.stmt.executeUpdate("DROP FUNCTION IF EXISTS testBug10310");
519             }
520         }
521     }
522 }
523
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags