0001: // jTDS JDBC Driver for Microsoft SQL Server and Sybase
0002: // Copyright (C) 2004 The jTDS Project
0003: //
0004: // This library is free software; you can redistribute it and/or
0005: // modify it under the terms of the GNU Lesser General Public
0006: // License as published by the Free Software Foundation; either
0007: // version 2.1 of the License, or (at your option) any later version.
0008: //
0009: // This library is distributed in the hope that it will be useful,
0010: // but WITHOUT ANY WARRANTY; without even the implied warranty of
0011: // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
0012: // Lesser General Public License for more details.
0013: //
0014: // You should have received a copy of the GNU Lesser General Public
0015: // License along with this library; if not, write to the Free Software
0016: // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0017: //
0018: package net.sourceforge.jtds.test;
0019:
0020: import java.math.BigDecimal;
0021: import java.sql.*;
0022: import java.util.*;
0023:
0024: /**
0025: * @version $Id: PreparedStatementTest.java,v 1.46 2007/07/08 18:26:26 bheineman Exp $
0026: */
0027: public class PreparedStatementTest extends TestBase {
0028:
0029: public PreparedStatementTest(String name) {
0030: super (name);
0031: }
0032:
0033: public void testPreparedStatement() throws Exception {
0034: PreparedStatement pstmt = con
0035: .prepareStatement("SELECT * FROM #test");
0036:
0037: Statement stmt = con.createStatement();
0038: makeTestTables(stmt);
0039: makeObjects(stmt, 10);
0040: stmt.close();
0041:
0042: ResultSet rs = pstmt.executeQuery();
0043: dump(rs);
0044:
0045: rs.close();
0046: pstmt.close();
0047: }
0048:
0049: public void testScrollablePreparedStatement() throws Exception {
0050: Statement stmt = con.createStatement();
0051: makeTestTables(stmt);
0052: makeObjects(stmt, 10);
0053: stmt.close();
0054:
0055: PreparedStatement pstmt = con.prepareStatement(
0056: "SELECT * FROM #test", ResultSet.TYPE_SCROLL_SENSITIVE,
0057: ResultSet.CONCUR_READ_ONLY);
0058:
0059: ResultSet rs = pstmt.executeQuery();
0060:
0061: assertTrue(rs.isBeforeFirst());
0062:
0063: while (rs.next()) {
0064: }
0065:
0066: assertTrue(rs.isAfterLast());
0067:
0068: //This currently fails because the PreparedStatement
0069: //Doesn't know it needs to create a cursored ResultSet.
0070: //Needs some refactoring!!
0071: // SAfe Not any longer. ;o)
0072: while (rs.previous()) {
0073: }
0074:
0075: assertTrue(rs.isBeforeFirst());
0076:
0077: rs.close();
0078: pstmt.close();
0079: }
0080:
0081: public void testPreparedStatementAddBatch1() throws Exception {
0082: int count = 50;
0083:
0084: Statement stmt = con.createStatement();
0085: stmt.execute("CREATE TABLE #psbatch1 (f_int INT)");
0086:
0087: int sum = 0;
0088:
0089: con.setAutoCommit(false);
0090: PreparedStatement pstmt = con
0091: .prepareStatement("INSERT INTO #psbatch1 (f_int) VALUES (?)");
0092:
0093: for (int i = 0; i < count; i++) {
0094: pstmt.setInt(1, i);
0095: pstmt.addBatch();
0096: sum += i;
0097: }
0098:
0099: int[] results = pstmt.executeBatch();
0100:
0101: assertEquals(results.length, count);
0102:
0103: for (int i = 0; i < count; i++) {
0104: assertEquals(results[i], 1);
0105: }
0106:
0107: pstmt.close();
0108:
0109: con.commit();
0110: con.setAutoCommit(true);
0111:
0112: ResultSet rs = stmt
0113: .executeQuery("SELECT SUM(f_int) FROM #psbatch1");
0114:
0115: assertTrue(rs.next());
0116: System.out.println(rs.getInt(1));
0117: assertEquals(rs.getInt(1), sum);
0118: rs.close();
0119: stmt.close();
0120: }
0121:
0122: /**
0123: * Test for [924030] EscapeProcesser problem with "{}" brackets
0124: */
0125: public void testPreparedStatementParsing1() throws Exception {
0126: String data = "New {order} plus {1} more";
0127: Statement stmt = con.createStatement();
0128:
0129: stmt.execute("CREATE TABLE #psp1 (data VARCHAR(32))");
0130: stmt.close();
0131:
0132: stmt = con.createStatement();
0133: stmt
0134: .execute("create procedure #sp_psp1 @data VARCHAR(32) as INSERT INTO #psp1 (data) VALUES(@data)");
0135: stmt.close();
0136:
0137: PreparedStatement pstmt = con
0138: .prepareStatement("{call #sp_psp1('" + data + "')}");
0139:
0140: pstmt.execute();
0141: pstmt.close();
0142:
0143: stmt = con.createStatement();
0144: ResultSet rs = stmt.executeQuery("SELECT data FROM #psp1");
0145:
0146: assertTrue(rs.next());
0147:
0148: assertTrue(data.equals(rs.getString(1)));
0149:
0150: assertFalse(rs.next());
0151: rs.close();
0152: stmt.close();
0153: }
0154:
0155: /**
0156: * Test for bug [1008882] Some queries with parameters cannot be executed with 0.9-rc1
0157: */
0158: public void testPreparedStatementParsing2() throws Exception {
0159: PreparedStatement pstmt = con.prepareStatement(" SELECT ?");
0160:
0161: pstmt.setString(1, "TEST");
0162:
0163: ResultSet rs = pstmt.executeQuery();
0164:
0165: assertTrue(rs.next());
0166: assertEquals("TEST", rs.getString(1));
0167: assertFalse(rs.next());
0168:
0169: pstmt.close();
0170: rs.close();
0171: }
0172:
0173: /**
0174: * Test for "invalid parameter index" error.
0175: */
0176: public void testPreparedStatementParsing3() throws Exception {
0177: PreparedStatement pstmt = con
0178: .prepareStatement("UPDATE dbo.DEPARTMENTS SET DEPARTMENT_NAME=? WHERE DEPARTMENT_ID=?");
0179:
0180: pstmt.setString(1, "TEST");
0181: pstmt.setString(2, "TEST");
0182:
0183: pstmt.close();
0184: }
0185:
0186: /**
0187: * Test for [931090] ArrayIndexOutOfBoundsException in rollback()
0188: */
0189: public void testPreparedStatementRollback1() throws Exception {
0190: Connection localCon = getConnection();
0191: Statement stmt = localCon.createStatement();
0192:
0193: stmt.execute("CREATE TABLE #psr1 (data BIT)");
0194:
0195: localCon.setAutoCommit(false);
0196: PreparedStatement pstmt = localCon
0197: .prepareStatement("INSERT INTO #psr1 (data) VALUES (?)");
0198:
0199: pstmt.setBoolean(1, true);
0200: assertEquals(1, pstmt.executeUpdate());
0201: pstmt.close();
0202:
0203: localCon.rollback();
0204:
0205: ResultSet rs = stmt.executeQuery("SELECT data FROM #psr1");
0206: assertFalse(rs.next());
0207: rs.close();
0208: stmt.close();
0209:
0210: localCon.close();
0211:
0212: try {
0213: localCon.commit();
0214: fail("Expecting commit to fail, connection was closed");
0215: } catch (SQLException ex) {
0216: assertEquals("HY010", ex.getSQLState());
0217: }
0218:
0219: try {
0220: localCon.rollback();
0221: fail("Expecting rollback to fail, connection was closed");
0222: } catch (SQLException ex) {
0223: assertEquals("HY010", ex.getSQLState());
0224: }
0225: }
0226:
0227: /**
0228: * Test for bug [938494] setObject(i, o, NUMERIC/DECIMAL) cuts off decimal places
0229: */
0230: public void testPreparedStatementSetObject1() throws Exception {
0231: BigDecimal data = new BigDecimal(3.7D);
0232:
0233: Statement stmt = con.createStatement();
0234: stmt.execute("CREATE TABLE #psso1 (data MONEY)");
0235:
0236: PreparedStatement pstmt = con
0237: .prepareStatement("INSERT INTO #psso1 (data) VALUES (?)");
0238:
0239: pstmt.setObject(1, data);
0240: assertEquals(1, pstmt.executeUpdate());
0241: pstmt.close();
0242:
0243: ResultSet rs = stmt.executeQuery("SELECT data FROM #psso1");
0244:
0245: assertTrue(rs.next());
0246: assertEquals(data.doubleValue(), rs.getDouble(1), 0);
0247: assertFalse(rs.next());
0248: rs.close();
0249: stmt.close();
0250: }
0251:
0252: /**
0253: * Test for bug [938494] setObject(i, o, NUMERIC/DECIMAL) cuts off decimal places
0254: */
0255: public void testPreparedStatementSetObject2() throws Exception {
0256: BigDecimal data = new BigDecimal(3.7D);
0257:
0258: Statement stmt = con.createStatement();
0259: stmt.execute("CREATE TABLE #psso2 (data MONEY)");
0260:
0261: PreparedStatement pstmt = con
0262: .prepareStatement("INSERT INTO #psso2 (data) VALUES (?)");
0263:
0264: pstmt.setObject(1, data, Types.NUMERIC);
0265: assertEquals(1, pstmt.executeUpdate());
0266: pstmt.close();
0267:
0268: ResultSet rs = stmt.executeQuery("SELECT data FROM #psso2");
0269:
0270: assertTrue(rs.next());
0271: assertEquals(data.doubleValue(), rs.getDouble(1), 0);
0272: assertFalse(rs.next());
0273: rs.close();
0274: stmt.close();
0275: }
0276:
0277: /**
0278: * Test for bug [938494] setObject(i, o, NUMERIC/DECIMAL) cuts off decimal places
0279: */
0280: public void testPreparedStatementSetObject3() throws Exception {
0281: BigDecimal data = new BigDecimal(3.7D);
0282:
0283: Statement stmt = con.createStatement();
0284: stmt.execute("CREATE TABLE #psso3 (data MONEY)");
0285:
0286: PreparedStatement pstmt = con
0287: .prepareStatement("INSERT INTO #psso3 (data) VALUES (?)");
0288:
0289: pstmt.setObject(1, data, Types.DECIMAL);
0290: assertEquals(1, pstmt.executeUpdate());
0291: pstmt.close();
0292:
0293: ResultSet rs = stmt.executeQuery("SELECT data FROM #psso3");
0294:
0295: assertTrue(rs.next());
0296: assertEquals(data.doubleValue(), rs.getDouble(1), 0);
0297: assertFalse(rs.next());
0298: rs.close();
0299: stmt.close();
0300: }
0301:
0302: /**
0303: * Test for bug [938494] setObject(i, o, NUMERIC/DECIMAL) cuts off decimal places
0304: */
0305: public void testPreparedStatementSetObject4() throws Exception {
0306: BigDecimal data = new BigDecimal(3.7D);
0307:
0308: Statement stmt = con.createStatement();
0309: stmt.execute("CREATE TABLE #psso4 (data MONEY)");
0310:
0311: PreparedStatement pstmt = con
0312: .prepareStatement("INSERT INTO #psso4 (data) VALUES (?)");
0313:
0314: pstmt.setObject(1, data, Types.NUMERIC, 4);
0315: assertEquals(1, pstmt.executeUpdate());
0316: pstmt.close();
0317:
0318: ResultSet rs = stmt.executeQuery("SELECT data FROM #psso4");
0319:
0320: assertTrue(rs.next());
0321: assertEquals(data.doubleValue(), rs.getDouble(1), 0);
0322: assertFalse(rs.next());
0323: rs.close();
0324: stmt.close();
0325: }
0326:
0327: /**
0328: * Test for bug [938494] setObject(i, o, NUMERIC/DECIMAL) cuts off decimal places
0329: */
0330: public void testPreparedStatementSetObject5() throws Exception {
0331: BigDecimal data = new BigDecimal(3.7D);
0332:
0333: Statement stmt = con.createStatement();
0334: stmt.execute("CREATE TABLE #psso5 (data MONEY)");
0335:
0336: PreparedStatement pstmt = con
0337: .prepareStatement("INSERT INTO #psso5 (data) VALUES (?)");
0338:
0339: pstmt.setObject(1, data, Types.DECIMAL, 4);
0340: assertEquals(1, pstmt.executeUpdate());
0341: pstmt.close();
0342:
0343: ResultSet rs = stmt.executeQuery("SELECT data FROM #psso5");
0344:
0345: assertTrue(rs.next());
0346: assertEquals(data.doubleValue(), rs.getDouble(1), 0);
0347: assertFalse(rs.next());
0348: rs.close();
0349: stmt.close();
0350: }
0351:
0352: /**
0353: * Test for bug [1204658] Conversion from Number to BigDecimal causes data
0354: * corruption.
0355: */
0356: public void testPreparedStatementSetObject6() throws Exception {
0357: final Long TEST_VALUE = new Long(2265157674817400199L);
0358:
0359: Statement s = con.createStatement();
0360: s.execute("CREATE TABLE #psso6 (test_value NUMERIC(22,0))");
0361:
0362: PreparedStatement ps = con
0363: .prepareStatement("insert into #psso6(test_value) values (?)");
0364: ps.setObject(1, TEST_VALUE, Types.DECIMAL);
0365: assertEquals(1, ps.executeUpdate());
0366: ps.close();
0367:
0368: ResultSet rs = s.executeQuery("select test_value from #psso6");
0369: assertTrue(rs.next());
0370: assertEquals("Persisted value not equal to original value",
0371: TEST_VALUE.longValue(), rs.getLong(1));
0372: assertFalse(rs.next());
0373: rs.close();
0374:
0375: s.close();
0376: }
0377:
0378: /**
0379: * Test for bug [985754] row count is always 0
0380: */
0381: public void testUpdateCount1() throws Exception {
0382: int count = 50;
0383:
0384: Statement stmt = con.createStatement();
0385: stmt.execute("CREATE TABLE #updateCount1 (data INT)");
0386:
0387: PreparedStatement pstmt = con
0388: .prepareStatement("INSERT INTO #updateCount1 (data) VALUES (?)");
0389:
0390: for (int i = 1; i <= count; i++) {
0391: pstmt.setInt(1, i);
0392: assertEquals(1, pstmt.executeUpdate());
0393: }
0394:
0395: pstmt.close();
0396:
0397: ResultSet rs = stmt
0398: .executeQuery("SELECT COUNT(*) FROM #updateCount1");
0399:
0400: assertTrue(rs.next());
0401: assertEquals(count, rs.getInt(1));
0402: assertFalse(rs.next());
0403:
0404: stmt.close();
0405: rs.close();
0406:
0407: pstmt = con.prepareStatement("DELETE FROM #updateCount1");
0408: assertEquals(count, pstmt.executeUpdate());
0409: pstmt.close();
0410:
0411: }
0412:
0413: /**
0414: * Test for parameter markers in function escapes.
0415: */
0416: public void testEscapedParams() throws Exception {
0417: PreparedStatement pstmt = con
0418: .prepareStatement("SELECT {fn left(?, 2)}");
0419:
0420: pstmt.setString(1, "TEST");
0421:
0422: ResultSet rs = pstmt.executeQuery();
0423:
0424: assertTrue(rs.next());
0425: assertEquals("TE", rs.getString(1));
0426: assertFalse(rs.next());
0427:
0428: rs.close();
0429: pstmt.close();
0430: }
0431:
0432: /**
0433: * Test for bug [ 1059916 ] whitespace needed in preparedStatement.
0434: */
0435: public void testMissingWhitespace() throws Exception {
0436: PreparedStatement pstmt = con
0437: .prepareStatement("SELECT name from master..syscharsets where description like?and?between csid and 10");
0438: pstmt.setString(1, "ISO%");
0439: pstmt.setInt(2, 0);
0440: ResultSet rs = pstmt.executeQuery();
0441: assertNotNull(rs);
0442: assertTrue(rs.next());
0443: }
0444:
0445: /**
0446: * Test for bug [1022968] Long SQL expression error.
0447: * NB. Test must be run with TDS=7.0 to fail.
0448: */
0449: public void testLongStatement() throws Exception {
0450: Statement stmt = con
0451: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
0452: ResultSet.CONCUR_UPDATABLE);
0453:
0454: stmt
0455: .execute("CREATE TABLE #longStatement (id int primary key, data varchar(8000))");
0456:
0457: StringBuffer buf = new StringBuffer(4096);
0458: buf.append("SELECT * FROM #longStatement WHERE data = '");
0459:
0460: for (int i = 0; i < 4000; i++) {
0461: buf.append('X');
0462: }
0463:
0464: buf.append("'");
0465:
0466: ResultSet rs = stmt.executeQuery(buf.toString());
0467:
0468: assertNotNull(rs);
0469: assertFalse(rs.next());
0470:
0471: rs.close();
0472: stmt.close();
0473: }
0474:
0475: /**
0476: * Test for bug [1047330] prep statement with more than 2100 params fails.
0477: */
0478: public void testManyParametersStatement() throws Exception {
0479: final int PARAMS = 2110;
0480:
0481: Statement stmt = con.createStatement();
0482: makeTestTables(stmt);
0483: makeObjects(stmt, 10);
0484: stmt.close();
0485:
0486: StringBuffer sb = new StringBuffer(PARAMS * 3 + 100);
0487: sb.append("SELECT * FROM #test WHERE f_int in (?");
0488: for (int i = 1; i < PARAMS; i++) {
0489: sb.append(", ?");
0490: }
0491: sb.append(")");
0492:
0493: try {
0494: // This can work if prepareSql=0
0495: PreparedStatement pstmt = con.prepareStatement(sb
0496: .toString());
0497:
0498: // Set the parameters
0499: for (int i = 1; i <= PARAMS; i++) {
0500: pstmt.setInt(i, i);
0501: }
0502:
0503: // Execute query and count rows
0504: ResultSet rs = pstmt.executeQuery();
0505: int cnt = 0;
0506: while (rs.next()) {
0507: ++cnt;
0508: }
0509:
0510: // Make sure this worked
0511: assertEquals(9, cnt);
0512: } catch (SQLException ex) {
0513: assertEquals("22025", ex.getSQLState());
0514: }
0515: }
0516:
0517: /**
0518: * Test for bug [1010660] 0.9-rc1 setMaxRows causes unlimited temp stored
0519: * procedures. This test has to be run with logging enabled or while
0520: * monitoring it with SQL Profiler to see whether the temporary stored
0521: * procedure is executed or the SQL is executed directly.
0522: */
0523: public void testMaxRows() throws SQLException {
0524: Statement stmt = con.createStatement();
0525: stmt.execute("CREATE TABLE #maxRows (val int)"
0526: + " INSERT INTO #maxRows VALUES (1)"
0527: + " INSERT INTO #maxRows VALUES (2)");
0528:
0529: PreparedStatement pstmt = con
0530: .prepareStatement("SELECT * FROM #maxRows WHERE val<? ORDER BY val");
0531: pstmt.setInt(1, 100);
0532: pstmt.setMaxRows(1);
0533:
0534: ResultSet rs = pstmt.executeQuery();
0535:
0536: assertNotNull(rs);
0537: assertTrue(rs.next());
0538: assertEquals(1, rs.getInt(1));
0539: assertFalse(rs.next());
0540:
0541: rs.close();
0542: pstmt.close();
0543:
0544: stmt.executeUpdate("DROP TABLE #maxRows");
0545: stmt.close();
0546: }
0547:
0548: /**
0549: * Test for bug [1050660] PreparedStatement.getMetaData() clears resultset.
0550: */
0551: public void testMetaDataClearsResultSet() throws Exception {
0552: Statement stmt = con
0553: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
0554: ResultSet.CONCUR_UPDATABLE);
0555:
0556: stmt
0557: .executeUpdate("CREATE TABLE #metaDataClearsResultSet (id int primary key, data varchar(8000))");
0558: stmt
0559: .executeUpdate("INSERT INTO #metaDataClearsResultSet (id, data)"
0560: + " VALUES (1, '1')");
0561: stmt
0562: .executeUpdate("INSERT INTO #metaDataClearsResultSet (id, data)"
0563: + " VALUES (2, '2')");
0564: stmt.close();
0565:
0566: PreparedStatement pstmt = con
0567: .prepareStatement("SELECT * FROM #metaDataClearsResultSet ORDER BY id");
0568: ResultSet rs = pstmt.executeQuery();
0569:
0570: assertNotNull(rs);
0571:
0572: ResultSetMetaData rsmd = pstmt.getMetaData();
0573: assertEquals(2, rsmd.getColumnCount());
0574: assertEquals("id", rsmd.getColumnName(1));
0575: assertEquals("data", rsmd.getColumnName(2));
0576: assertEquals(8000, rsmd.getColumnDisplaySize(2));
0577:
0578: assertTrue(rs.next());
0579: assertEquals(1, rs.getInt(1));
0580: assertEquals("1", rs.getString(2));
0581:
0582: assertTrue(rs.next());
0583: assertEquals(2, rs.getInt(1));
0584: assertEquals("2", rs.getString(2));
0585:
0586: assertFalse(rs.next());
0587:
0588: rs.close();
0589: pstmt.close();
0590: }
0591:
0592: /**
0593: * Test for bad truncation in prepared statements on metadata retrieval
0594: * (patch [1076383] ResultSetMetaData for more complex statements for SQL
0595: * Server).
0596: */
0597: public void testMetaData() throws Exception {
0598: Statement stmt = con
0599: .createStatement(ResultSet.TYPE_FORWARD_ONLY,
0600: ResultSet.CONCUR_UPDATABLE);
0601:
0602: stmt
0603: .executeUpdate("CREATE TABLE #metaData (id int, data varchar(8000))");
0604: stmt.executeUpdate("INSERT INTO #metaData (id, data)"
0605: + " VALUES (1, 'Data1')");
0606: stmt.executeUpdate("INSERT INTO #metaData (id, data)"
0607: + " VALUES (1, 'Data2')");
0608: stmt.executeUpdate("INSERT INTO #metaData (id, data)"
0609: + " VALUES (2, 'Data3')");
0610: stmt.executeUpdate("INSERT INTO #metaData (id, data)"
0611: + " VALUES (2, 'Data4')");
0612: stmt.close();
0613:
0614: // test simple statement
0615: PreparedStatement pstmt = con.prepareStatement("SELECT id "
0616: + "FROM #metaData " + "WHERE data=? GROUP BY id");
0617:
0618: ResultSetMetaData rsmd = pstmt.getMetaData();
0619:
0620: assertNotNull("No meta data returned for simple statement",
0621: rsmd);
0622:
0623: assertEquals(1, rsmd.getColumnCount());
0624: assertEquals("id", rsmd.getColumnName(1));
0625:
0626: pstmt.close();
0627:
0628: // test more complex statement
0629: pstmt = con.prepareStatement("SELECT id, count(*) as count "
0630: + "FROM #metaData " + "WHERE data=? GROUP BY id");
0631:
0632: rsmd = pstmt.getMetaData();
0633:
0634: assertNotNull("No metadata returned for complex statement",
0635: rsmd);
0636:
0637: assertEquals(2, rsmd.getColumnCount());
0638: assertEquals("id", rsmd.getColumnName(1));
0639: assertEquals("count", rsmd.getColumnName(2));
0640:
0641: pstmt.close();
0642: }
0643:
0644: /**
0645: * Test for bug [1071397] Error in prepared statement (parameters in outer
0646: * join escapes are not recognized).
0647: */
0648: public void testOuterJoinParameters() throws SQLException {
0649: Statement stmt = con.createStatement();
0650: stmt
0651: .executeUpdate("CREATE TABLE #outerJoinParameters (id int primary key)");
0652: stmt
0653: .executeUpdate("INSERT #outerJoinParameters (id) values (1)");
0654: stmt.close();
0655:
0656: // Real dumb join, the idea is to see the parser works fine
0657: PreparedStatement pstmt = con
0658: .prepareStatement("select * from "
0659: + "{oj #outerJoinParameters a left outer join #outerJoinParameters b on a.id = ?}"
0660: + "where b.id = ?");
0661: pstmt.setInt(1, 1);
0662: pstmt.setInt(2, 1);
0663: ResultSet rs = pstmt.executeQuery();
0664: assertTrue(rs.next());
0665: assertEquals(1, rs.getInt(1));
0666: assertEquals(1, rs.getInt(2));
0667: assertFalse(rs.next());
0668: rs.close();
0669: pstmt.close();
0670:
0671: pstmt = con.prepareStatement("select {fn round(?, 0)}");
0672: pstmt.setDouble(1, 1.2);
0673: rs = pstmt.executeQuery();
0674: assertTrue(rs.next());
0675: assertEquals(1, rs.getDouble(1), 0);
0676: assertFalse(rs.next());
0677: rs.close();
0678: pstmt.close();
0679: }
0680:
0681: /**
0682: * Inner class used by {@link PreparedStatementTest#testMultiThread} to
0683: * test concurrency.
0684: */
0685: static class TestMultiThread extends Thread {
0686: static Connection con;
0687: static final int THREAD_MAX = 10;
0688: static final int LOOP_MAX = 10;
0689: static final int ROWS_MAX = 10;
0690: static int live;
0691: static Exception error;
0692:
0693: int threadId;
0694:
0695: TestMultiThread(int n) {
0696: threadId = n;
0697: }
0698:
0699: public void run() {
0700: try {
0701: con.rollback();
0702: PreparedStatement pstmt = con.prepareStatement(
0703: "SELECT id, data FROM #TEST WHERE id = ?",
0704: ResultSet.TYPE_SCROLL_INSENSITIVE,
0705: ResultSet.CONCUR_READ_ONLY);
0706:
0707: for (int i = 1; i <= LOOP_MAX; i++) {
0708: pstmt.clearParameters();
0709: pstmt.setInt(1, i);
0710: ResultSet rs = pstmt.executeQuery();
0711:
0712: while (rs.next()) {
0713: rs.getInt(1);
0714: rs.getString(2);
0715: }
0716:
0717: }
0718:
0719: pstmt.close();
0720: } catch (Exception e) {
0721: System.err.print("ID=" + threadId + ' ');
0722: e.printStackTrace();
0723: error = e;
0724: }
0725:
0726: synchronized (this .getClass()) {
0727: live--;
0728: }
0729: }
0730:
0731: static void startThreads(Connection con) throws Exception {
0732: TestMultiThread.con = con;
0733: con.setAutoCommit(false);
0734:
0735: Statement stmt = con.createStatement();
0736: stmt
0737: .execute("CREATE TABLE #TEST (id int identity primary key, data varchar(255))");
0738:
0739: for (int i = 0; i < ROWS_MAX; i++) {
0740: stmt
0741: .executeUpdate("INSERT INTO #TEST (data) VALUES('This is line "
0742: + i + "')");
0743: }
0744:
0745: stmt.close();
0746: con.commit();
0747:
0748: live = THREAD_MAX;
0749: for (int i = 0; i < THREAD_MAX; i++) {
0750: new TestMultiThread(i).start();
0751: }
0752: while (live > 0) {
0753: sleep(1);
0754: }
0755:
0756: if (error != null) {
0757: throw error;
0758: }
0759: }
0760: }
0761:
0762: /**
0763: * Test <code>Connection</code> concurrency by running
0764: * <code>PreparedStatement</code>s and rollbacks at the same time to see
0765: * whether handles are not lost in the process.
0766: */
0767: public void testMultiThread() throws Exception {
0768: TestMultiThread.startThreads(con);
0769: }
0770:
0771: /**
0772: * Test for bug [1094621] Decimal conversion error: A prepared statement
0773: * with a decimal parameter that is -1E38 will fail as a result of the
0774: * driver generating a parameter specification of decimal(38,10) rather
0775: * than decimal(38,0).
0776: */
0777: public void testBigDecBadParamSpec() throws Exception {
0778: Statement stmt = con.createStatement();
0779: stmt
0780: .execute("create table #test (id int primary key, val decimal(38,0))");
0781: BigDecimal bd = new BigDecimal(
0782: "99999999999999999999999999999999999999");
0783: PreparedStatement pstmt = con
0784: .prepareStatement("insert into #test values(?,?)");
0785: pstmt.setInt(1, 1);
0786: pstmt.setBigDecimal(2, bd);
0787: assertEquals(1, pstmt.executeUpdate()); // Worked OK
0788: pstmt.setInt(1, 2);
0789: pstmt.setBigDecimal(2, bd.negate());
0790: assertEquals(1, pstmt.executeUpdate()); // Failed
0791: }
0792:
0793: /**
0794: * Test for bug [1111516 ] Illegal Parameters in PreparedStatement.
0795: */
0796: public void testIllegalParameters() throws Exception {
0797: Statement stmt = con.createStatement();
0798: stmt.execute("create table #test (id int)");
0799: PreparedStatement pstmt = con
0800: .prepareStatement("select top ? * from #test");
0801: pstmt.setInt(1, 10);
0802: try {
0803: pstmt.executeQuery();
0804: // This won't fail in unprepared mode (prepareSQL == 0)
0805: // fail("Expecting an exception to be thrown.");
0806: } catch (SQLException ex) {
0807: assertTrue("37000".equals(ex.getSQLState())
0808: || "42000".equals(ex.getSQLState()));
0809: }
0810: pstmt.close();
0811: }
0812:
0813: /**
0814: * Test for bug [1180777] collation-related execption on update.
0815: * <p/>
0816: * If a statement prepare fails the statement should still be executed
0817: * (unprepared) and a warning should be added to the connection (the
0818: * prepare failed, this is a connection event even if it happened on
0819: * statement execute).
0820: */
0821: public void testPrepareFailWarning() throws SQLException {
0822: try {
0823: PreparedStatement pstmt = con
0824: .prepareStatement("CREATE VIEW prepFailWarning AS SELECT 1 AS value");
0825: pstmt.execute();
0826: // Check that a warning was generated on the connection.
0827: // Although not totally correct (the warning should be generated on
0828: // the statement) the warning is generated while preparing the
0829: // statement, so it belongs to the connection.
0830: assertNotNull(con.getWarnings());
0831: pstmt.close();
0832:
0833: Statement stmt = con.createStatement();
0834: ResultSet rs = stmt
0835: .executeQuery("SELECT * FROM prepFailWarning");
0836: assertTrue(rs.next());
0837: assertEquals(1, rs.getInt(1));
0838: assertFalse(rs.next());
0839: rs.close();
0840: stmt.close();
0841: } finally {
0842: Statement stmt = con.createStatement();
0843: stmt.execute("DROP VIEW prepFailWarning");
0844: stmt.close();
0845: }
0846: }
0847:
0848: /**
0849: * Test that preparedstatement logic copes with commit modes and
0850: * database changes.
0851: */
0852: public void testPrepareModes() throws Exception {
0853: //
0854: // To see in detail what is happening enable logging and study the prepare
0855: // statements that are being executed.
0856: // For example if maxStatements=0 then the log should show that each
0857: // statement is prepared and then unprepared at statement close.
0858: // If maxStatements < 4 then you will see statements being unprepared
0859: // when the cache is full.
0860: //
0861: // DriverManager.setLogStream(System.out);
0862: Statement stmt = con.createStatement();
0863: stmt
0864: .execute("CREATE TABLE #TEST (id int primary key, data varchar(255))");
0865: //
0866: // Statement prepared with auto commit = true
0867: //
0868: PreparedStatement pstmt1 = con
0869: .prepareStatement("INSERT INTO #TEST (id, data) VALUES (?,?)");
0870: pstmt1.setInt(1, 1);
0871: pstmt1.setString(2, "Line one");
0872: assertEquals(1, pstmt1.executeUpdate());
0873: //
0874: // Move to manual commit mode
0875: //
0876: con.setAutoCommit(false);
0877: //
0878: // Ensure a new transaction is started
0879: //
0880: ResultSet rs = stmt.executeQuery("SELECT * FROM #TEST");
0881: assertNotNull(rs);
0882: rs.close();
0883: //
0884: // With Sybase this execution should cause a new proc to be created
0885: // as we are now in chained mode
0886: //
0887: pstmt1.setInt(1, 2);
0888: pstmt1.setString(2, "Line two");
0889: assertEquals(1, pstmt1.executeUpdate());
0890: //
0891: // Statement prepared with auto commit = false
0892: //
0893: PreparedStatement pstmt2 = con
0894: .prepareStatement("SELECT * FROM #TEST WHERE id = ?");
0895: pstmt2.setInt(1, 2);
0896: rs = pstmt2.executeQuery();
0897: assertNotNull(rs);
0898: assertTrue(rs.next());
0899: assertEquals("Line two", rs.getString("data"));
0900: //
0901: // Change catalog
0902: //
0903: String oldCat = con.getCatalog();
0904: con.setCatalog("master");
0905: //
0906: // Executiion from another database should cause SQL Server to create
0907: // a new handle or store proc
0908: //
0909: pstmt2.setInt(1, 1);
0910: rs = pstmt2.executeQuery();
0911: assertNotNull(rs);
0912: assertTrue(rs.next());
0913: assertEquals("Line one", rs.getString("data"));
0914: //
0915: // Now change back to original database
0916: //
0917: con.setCatalog(oldCat);
0918: //
0919: // Roll back transaction which should cause SQL Server procs (but not
0920: // handles to be lost) causing statement to be prepared again.
0921: //
0922: pstmt2.setInt(1, 1);
0923: rs = pstmt2.executeQuery();
0924: assertNotNull(rs);
0925: assertTrue(rs.next());
0926: assertEquals("Line one", rs.getString("data"));
0927: //
0928: // Now return to auto commit mode
0929: //
0930: con.setAutoCommit(true);
0931: //
0932: // With Sybase statement will be prepared again as now in chained off mode
0933: //
0934: pstmt2.setInt(1, 1);
0935: rs = pstmt2.executeQuery();
0936: assertNotNull(rs);
0937: assertTrue(rs.next());
0938: assertEquals("Line one", rs.getString("data"));
0939: pstmt2.close();
0940: pstmt1.close();
0941: stmt.close();
0942: //
0943: // Now we create a final prepared statement to demonstate that
0944: // the cache is flushed correctly when the number of statements
0945: // exceeds the cachesize. For example setting maxStatements=1
0946: // will cause three statements to be unprepared when this statement
0947: // is closed
0948: //
0949: pstmt1 = con.prepareStatement("SELECT id, data FROM #TEST");
0950: pstmt1.executeQuery();
0951: pstmt1.close();
0952: }
0953:
0954: /**
0955: * Test that statements which cannot be prepared are remembered.
0956: */
0957: public void testNoPrepare() throws Exception {
0958: // DriverManager.setLogStream(System.out);
0959: Statement stmt = con.createStatement();
0960: stmt
0961: .execute("CREATE TABLE #TEST (id int primary key, data text)");
0962: //
0963: // Statement cannot be prepared on Sybase due to text field
0964: //
0965: PreparedStatement pstmt1 = con
0966: .prepareStatement("INSERT INTO #TEST (id, data) VALUES (?,?)");
0967: pstmt1.setInt(1, 1);
0968: pstmt1.setString(2, "Line one");
0969: assertEquals(1, pstmt1.executeUpdate());
0970: //
0971: // This time should not try and prepare
0972: //
0973: pstmt1.setInt(1, 2);
0974: pstmt1.setString(2, "Line two");
0975: assertEquals(1, pstmt1.executeUpdate());
0976: pstmt1.close();
0977: }
0978:
0979: /**
0980: * Tests that float (single precision - 32 bit) values are not converted to
0981: * double (thus loosing precision).
0982: */
0983: public void testFloatValues() throws Exception {
0984: Statement stmt = con.createStatement();
0985: stmt.executeUpdate("create table #floatTest (v real)");
0986: stmt.executeUpdate("insert into #floatTest (v) values (2.3)");
0987: stmt.close();
0988:
0989: PreparedStatement pstmt = con
0990: .prepareStatement("select * from #floatTest where v = ?");
0991: pstmt.setFloat(1, 2.3f);
0992: ResultSet rs = pstmt.executeQuery();
0993: assertTrue(rs.next());
0994: assertEquals(2.3f, rs.getFloat(1), 0);
0995: assertTrue(rs.getObject(1) instanceof Float);
0996: assertEquals(2.3f, ((Float) rs.getObject(1)).floatValue(), 0);
0997:
0998: // Just make sure that conversion to double will break this
0999: assertFalse(2.3 - rs.getDouble(1) == 0);
1000: assertFalse(rs.next());
1001: rs.close();
1002: pstmt.close();
1003: }
1004:
1005: public void testNegativeScale() throws Exception {
1006: Statement stmt = con.createStatement();
1007: stmt
1008: .execute("CREATE TABLE #testNegativeScale (val decimal(28,10))");
1009: PreparedStatement pstmt = con
1010: .prepareStatement("INSERT INTO #testNegativeScale VALUES(?)");
1011: pstmt.setBigDecimal(1, new BigDecimal("2.9E7"));
1012: assertEquals(1, pstmt.executeUpdate());
1013: pstmt.close();
1014:
1015: ResultSet rs = stmt
1016: .executeQuery("SELECT * FROM #testNegativeScale");
1017: assertNotNull(rs);
1018: assertTrue(rs.next());
1019: assertEquals(29000000, rs.getBigDecimal(1).intValue());
1020: stmt.close();
1021: }
1022:
1023: /**
1024: * Test for bug [1623668] Lost apostrophes in statement parameter values(prepareSQL=0)
1025: */
1026: public void testPrepareSQL0() throws Exception {
1027: Properties props = new Properties();
1028: props.setProperty("prepareSQL", "0");
1029: Connection con = getConnection(props);
1030:
1031: try {
1032: Statement stmt = con.createStatement();
1033: stmt
1034: .execute("CREATE TABLE #prepareSQL0 (position int, data varchar(32))");
1035: stmt.close();
1036:
1037: PreparedStatement ps = con
1038: .prepareStatement("INSERT INTO #prepareSQL0 (position, data) VALUES (?, ?)");
1039:
1040: String data1 = "foo'foo";
1041: String data2 = "foo''foo";
1042: String data3 = "foo'''foo";
1043:
1044: ps.setInt(1, 1);
1045: ps.setString(2, data1);
1046: ps.executeUpdate();
1047:
1048: ps.setInt(1, 2);
1049: ps.setString(2, data2);
1050: ps.executeUpdate();
1051:
1052: ps.setInt(1, 3);
1053: ps.setString(2, data3);
1054: ps.executeUpdate();
1055:
1056: ps.close();
1057: ps = con
1058: .prepareStatement("SELECT data FROM #prepareSQL0 ORDER BY position");
1059: ResultSet rs = ps.executeQuery();
1060:
1061: rs.next();
1062: assertEquals(data1, rs.getString(1));
1063:
1064: rs.next();
1065: assertEquals(data2, rs.getString(1));
1066:
1067: rs.next();
1068: assertEquals(data3, rs.getString(1));
1069:
1070: rs.close();
1071: } finally {
1072: con.close();
1073: }
1074: }
1075:
1076: public static void main(String[] args) {
1077: junit.textui.TestRunner.run(PreparedStatementTest.class);
1078: }
1079: }
|