001: // jTDS JDBC Driver for Microsoft SQL Server and Sybase
002: // Copyright (C) 2004 The jTDS Project
003: //
004: // This library is free software; you can redistribute it and/or
005: // modify it under the terms of the GNU Lesser General Public
006: // License as published by the Free Software Foundation; either
007: // version 2.1 of the License, or (at your option) any later version.
008: //
009: // This library is distributed in the hope that it will be useful,
010: // but WITHOUT ANY WARRANTY; without even the implied warranty of
011: // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
012: // Lesser General Public License for more details.
013: //
014: // You should have received a copy of the GNU Lesser General Public
015: // License along with this library; if not, write to the Free Software
016: // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
017: //
018: package net.sourceforge.jtds.test;
019:
020: import junit.framework.Test;
021: import junit.framework.TestSuite;
022:
023: import java.sql.*;
024: import java.math.BigDecimal;
025:
026: /**
027: * Test case to illustrate errors reported by SUN JBDC compatibility test suite.
028: *
029: * @version 1.0
030: */
031: public class SunTest extends DatabaseTestCase {
032: public static Test suite() {
033: return new TestSuite(SunTest.class);
034: }
035:
036: public SunTest(String name) {
037: super (name);
038: }
039:
040: /**
041: * Test for SUN bug [ PrepStmt1.getMetaData() ]
042: * Driver loops if select contains commas.
043: *
044: * @throws Exception
045: */
046: public void testGetMetaData() throws Exception {
047: PreparedStatement pstmt = con
048: .prepareStatement("SELECT name, id, type FROM sysobjects WHERE type = 'U'");
049: ResultSetMetaData rsmd = pstmt.getMetaData();
050: assertEquals("name", rsmd.getColumnName(1));
051: pstmt.close();
052: }
053:
054: /**
055: * Generic Tests for SUN bugs such as
056: * <ol>
057: * <li>Can't convert VARCHAR to Timestamp
058: * <li>Can't convert VARCHAR to Time
059: * <li>Can't convert VARCHAR to Date
060: * <li>Internal time representation causes equals to fail
061: * </ol>
062: * @throws Exception
063: */
064: public void testDateTime() throws Exception {
065: final String dateStr = "1983-01-31";
066: final String timeStr = "12:59:59";
067: final String tsStr = "1983-01-31 23:59:59.333";
068:
069: Statement stmt = con.createStatement();
070: stmt
071: .execute("CREATE PROC #CTOT_PROC @tdate DATETIME OUTPUT, @ttime DATETIME OUTPUT, @tts DATETIME OUTPUT AS "
072: + "BEGIN SELECT @tdate=tdate, @ttime=ttime, @tts=tts FROM #CTOT END");
073: stmt
074: .execute("CREATE TABLE #CTOT (tdate DATETIME, ttime DATETIME, tts DATETIME, tnull DATETIME NULL)");
075: stmt.close();
076: PreparedStatement pstmt = con
077: .prepareStatement("INSERT INTO #CTOT (tdate, ttime, tts) VALUES(?,?,?)");
078: pstmt.setObject(1, dateStr, java.sql.Types.DATE);
079: pstmt.setObject(2, timeStr, java.sql.Types.TIME);
080: pstmt.setObject(3, tsStr, java.sql.Types.TIMESTAMP);
081: pstmt.execute();
082: assertEquals(1, pstmt.getUpdateCount());
083: pstmt.close();
084: CallableStatement cstmt = con
085: .prepareCall("{call #CTOT_PROC(?,?,?)}");
086: cstmt.registerOutParameter(1, java.sql.Types.DATE);
087: cstmt.registerOutParameter(2, java.sql.Types.TIME);
088: cstmt.registerOutParameter(3, java.sql.Types.TIMESTAMP);
089: cstmt.execute();
090: assertEquals(dateStr, cstmt.getString(1));
091: assertEquals(timeStr, cstmt.getString(2));
092: assertEquals(java.sql.Time.valueOf(timeStr), cstmt.getTime(2));
093: assertEquals(tsStr, cstmt.getString(3));
094: cstmt.close();
095: stmt = con.createStatement();
096: ResultSet rs = stmt.executeQuery("SELECT * FROM #CTOT");
097: assertTrue(rs.next());
098: java.sql.Time retval = rs.getTime(2);
099: java.sql.Time tstval = java.sql.Time.valueOf(timeStr);
100: assertEquals(tstval, retval);
101: stmt.close();
102: pstmt = con.prepareStatement("UPDATE #CTOT SET tnull = ?");
103: pstmt.setTime(1, tstval);
104: pstmt.execute();
105: assertEquals(1, pstmt.getUpdateCount());
106: pstmt.close();
107: stmt = con.createStatement();
108: rs = stmt.executeQuery("SELECT * FROM #CTOT");
109: assertTrue(rs.next());
110: retval = rs.getTime(4);
111: assertEquals(tstval, retval);
112: stmt.close();
113: }
114:
115: /**
116: * Generic test for errors caused by promotion out parameters of Float to Double by driver.
117: * eg [ callStmt4.testGetObject34 ] Class cast exception Float.
118: *
119: * @throws Exception
120: */
121: public void testCharToReal() throws Exception {
122: final String minStr = "3.4E38";
123: final String maxStr = "1.18E-38";
124:
125: Statement stmt = con.createStatement();
126: stmt
127: .execute("CREATE PROC #CTOR_PROC @minval REAL OUTPUT, @maxval REAL OUTPUT AS "
128: + "BEGIN SELECT @minval=min_val, @maxval=max_val FROM #CTOR END");
129: stmt.execute("CREATE TABLE #CTOR (min_val REAL, max_val REAL)");
130: stmt.execute("INSERT INTO #CTOR VALUES(" + minStr + ","
131: + maxStr + ")");
132: assertEquals(1, stmt.getUpdateCount());
133: ResultSet rs = stmt.executeQuery("SELECT * FROM #CTOR");
134: assertNotNull(rs);
135: assertTrue(rs.next());
136: assertEquals(minStr, rs.getString(1));
137: assertEquals(maxStr, rs.getString(2));
138: assertTrue(rs.getObject(1) instanceof Float);
139: stmt.close();
140: CallableStatement cstmt = con
141: .prepareCall("{call #CTOR_PROC(?,?)}");
142: cstmt.registerOutParameter(1, java.sql.Types.REAL);
143: cstmt.registerOutParameter(2, java.sql.Types.REAL);
144: cstmt.execute();
145: assertEquals(minStr, cstmt.getString(1));
146: assertEquals(maxStr, cstmt.getString(2));
147: cstmt.close();
148: }
149:
150: /**
151: * Generic test for SUN bugs: bigint null parameter values sent as integer size.
152: *
153: * @throws Exception
154: */
155: public void testCharToLong() throws Exception {
156: final String minStr = "9223372036854775807";
157: final String maxStr = "-9223372036854775808";
158:
159: Statement stmt = con.createStatement();
160: stmt
161: .execute("CREATE PROC #CTOL_PROC @minval BIGINT OUTPUT, @maxval BIGINT OUTPUT AS "
162: + "BEGIN SELECT @minval=min_val, @maxval=max_val FROM #CTOL END");
163: stmt
164: .execute("CREATE TABLE #CTOL (min_val BIGINT, max_val BIGINT)");
165: stmt.execute("INSERT INTO #CTOL VALUES(" + minStr + ","
166: + maxStr + ")");
167: assertEquals(1, stmt.getUpdateCount());
168: ResultSet rs = stmt.executeQuery("SELECT * FROM #CTOL");
169: assertNotNull(rs);
170: assertTrue(rs.next());
171: assertEquals(minStr, rs.getString(1));
172: assertEquals(maxStr, rs.getString(2));
173: stmt.close();
174: CallableStatement cstmt = con
175: .prepareCall("{call #CTOL_PROC(?,?)}");
176: cstmt.registerOutParameter(1, java.sql.Types.BIGINT);
177: cstmt.registerOutParameter(2, java.sql.Types.BIGINT);
178: cstmt.execute();
179: assertEquals(minStr, cstmt.getString(1));
180: assertEquals(maxStr, cstmt.getString(2));
181: cstmt.close();
182: }
183:
184: /**
185: * Test for SUN bug [ dbMeta8.testGetProcedures ]
186: * The wrong column names are returned by getProcedures().
187: *
188: * @throws Exception
189: */
190: public void testGetProcedures() throws Exception {
191: String names[] = { "PROCEDURE_CAT", "PROCEDURE_SCHEM",
192: "PROCEDURE_NAME", "", "", "", "REMARKS",
193: "PROCEDURE_TYPE" };
194: DatabaseMetaData dbmd = con.getMetaData();
195: ResultSet rs = dbmd.getProcedures(null, null, "%");
196: ResultSetMetaData rsmd = rs.getMetaData();
197:
198: for (int i = 0; i < names.length; i++) {
199: if (names[i].length() > 0) {
200: assertEquals(names[i], rsmd.getColumnName(i + 1));
201: }
202: }
203:
204: rs.close();
205: }
206:
207: /**
208: * Generic test for SUN bug where Float was promoted to Double
209: * by driver leading to ClassCastExceptions in the tests.
210: * Example [ prepStmt4.testSetObject16 ]
211: *
212: * @throws Exception
213: */
214: public void testGetFloatObject() throws Exception {
215: Statement stmt = con.createStatement();
216: stmt.execute("CREATE TABLE #GETF (val REAL)");
217: stmt.execute("INSERT INTO #GETF (val) VALUES (1.7E10)");
218: assertEquals(1, stmt.getUpdateCount());
219: ResultSet rs = stmt.executeQuery("SELECT * FROM #GETF");
220: assertTrue(rs.next());
221: assertTrue(rs.getObject(1) instanceof Float);
222: rs.close();
223: stmt.close();
224: }
225:
226: /**
227: * Test for SUN bug [ resultSet1.testSetFetchSize02 ]
228: * attempt to set non zero fetch size rejected.
229: *
230: * @throws Exception
231: */
232: public void testSetFetchSize() throws Exception {
233: CallableStatement cstmt = con.prepareCall("{call sp_who}");
234: ResultSet rs = cstmt.executeQuery();
235: rs.setFetchSize(5);
236: assertEquals(5, rs.getFetchSize());
237: rs.close();
238: cstmt.close();
239: }
240:
241: /**
242: * Test for SUN bug [ stmt2.testSetFetchDirection04 ]
243: * fetch direction constant not validated.
244: *
245: * @throws Exception
246: */
247: public void testSetFetchDirectiion() throws Exception {
248: Statement stmt = con.createStatement();
249:
250: try {
251: stmt.setFetchDirection(-1);
252: fail("setFecthDirection does not validate parameter");
253: } catch (SQLException sqe) {
254: }
255:
256: stmt.close();
257: }
258:
259: /**
260: * Test for bug [ 1012307 ] PreparedStatement.setObject(java.util.Date) not working.
261: * The driver should throw an exception if the object is not of a valid
262: * type according to table
263: *
264: * @throws Exception
265: */
266: public void testSetDateObject() throws Exception {
267: Statement stmt = con.createStatement();
268: stmt.execute("CREATE TABLE #SETD (val DATETIME)");
269: PreparedStatement pstmt = con
270: .prepareStatement("INSERT INTO #SETD (val) VALUES (?)");
271: long tval = 60907507200000L; //1999-12-31
272:
273: try {
274: pstmt.setObject(1, new java.util.Date(tval));
275: fail("No exception for setObject(java.util.Date)");
276: } catch (SQLException e) {
277: // OK unsupported object type trapped
278: }
279:
280: pstmt.close();
281: stmt.close();
282: }
283:
284: /**
285: * Test for bug [ 1012301 ] 0.9-rc1: Prepared statement execution error.
286: *
287: * @throws Exception
288: */
289: public void testPrepStmtError() throws Exception {
290: Statement stmt = con.createStatement();
291: stmt.execute("CREATE TABLE #PERR (val VARCHAR(255))\r\n"
292: + "INSERT INTO #PERR (val) VALUES('Test String')");
293: PreparedStatement pstmt = con
294: .prepareStatement(" SELECT * FROM #PERR WHERE val = ?");
295: pstmt.setString(1, "Test String");
296: assertTrue(pstmt.execute());
297: ResultSet rs = pstmt.getResultSet();
298: assertTrue(rs.next());
299: rs.close();
300: pstmt.close();
301: stmt.close();
302: }
303:
304: /**
305: * Test for bug [ 1011650 ] 0.9-rc1: comments get parsed
306: *
307: * @throws Exception
308: */
309: public void testSqlComments() throws Exception {
310: String testSql = "/* This is a test of the comment {fn test()} parser */\r\n"
311: + "SELECT * FROM XXXX -- In line comment {d 1999-01-01}\r\n"
312: + "INSERT INTO B VALUES({d 1999-01-01}) -- Unterminated in line comment";
313: String outSql = "/* This is a test of the comment {fn test()} parser */\r\n"
314: + "SELECT * FROM XXXX -- In line comment {d 1999-01-01}\r\n"
315: + "INSERT INTO B VALUES('19990101') -- Unterminated in line comment";
316: assertEquals(outSql, con.nativeSQL(testSql));
317: }
318:
319: /**
320: * Test for bug [ 1008126 ] Metadata getTimeDateFunctions() wrong
321: *
322: * @throws Exception
323: */
324: public void testDateTimeFn() throws Exception {
325: Statement stmt = con.createStatement();
326: stmt
327: .execute("CREATE TABLE #DTFN (ttime SMALLDATETIME, tdate SMALLDATETIME, ftime SMALLDATETIME, fdate SMALLDATETIME, tnow DATETIME)");
328: stmt
329: .execute("INSERT INTO #DTFN (ttime, tdate, ftime, fdate, tnow) VALUES (getdate(), getdate(), {fn curtime()}, {fn curdate()}, {fn now()})");
330: assertEquals(1, stmt.getUpdateCount());
331: ResultSet rs = stmt.executeQuery("SELECT * FROM #DTFN");
332: assertTrue(rs.next());
333: assertEquals("curdate()", rs.getDate(2), rs.getDate(4));
334: assertEquals("curtime()", rs.getTime(1), rs.getTime(3));
335: assertEquals("now()", rs.getDate(1), rs.getDate(5));
336: rs = stmt
337: .executeQuery("SELECT {fn dayname('2004-08-21')}, "
338: + "{fn dayofmonth('2004-08-21')}, "
339: + "{fn dayofweek('2004-08-21')},"
340: + "{fn dayofyear('2004-08-21')},"
341: + "{fn hour('23:47:32')},"
342: + "{fn minute('23:47:32')},"
343: + "{fn second('23:47:32')},"
344: + "{fn year('2004-08-21')},"
345: + "{fn quarter('2004-08-21')},"
346: + "{fn month('2004-08-21')},"
347: + "{fn week('2004-08-21')},"
348: + "{fn monthname('2004-08-21')},"
349: + "{fn timestampdiff(SQL_TSI_DAY, '2004-08-19','2004-08-21')},"
350: + "{fn timestampadd(SQL_TSI_MONTH, 1, '2004-08-21')}"
351: + "");
352: assertTrue(rs.next());
353: assertEquals("dayname", "Saturday", rs.getString(1));
354: assertEquals("dayofmonth", 21, rs.getInt(2));
355: assertEquals("dayofweek", 7, rs.getInt(3));
356: assertEquals("dayofyear", 234, rs.getInt(4));
357: assertEquals("hour", 23, rs.getInt(5));
358: assertEquals("minute", 47, rs.getInt(6));
359: assertEquals("second", 32, rs.getInt(7));
360: assertEquals("year", 2004, rs.getInt(8));
361: assertEquals("quarter", 3, rs.getInt(9));
362: assertEquals("month", 8, rs.getInt(10));
363: assertEquals("week", 34, rs.getInt(11));
364: assertEquals("monthname", "August", rs.getString(12));
365: assertEquals("timestampdiff", 2, rs.getInt(13));
366: assertEquals("timestampadd", java.sql.Date
367: .valueOf("2004-09-21"), rs.getDate(14));
368: stmt.close();
369: }
370:
371: /**
372: * Test for scalar string functions.
373: *
374: * @throws Exception
375: */
376: public void testStringFn() throws Exception {
377: Statement stmt = con.createStatement();
378: ResultSet rs = stmt.executeQuery("SELECT {fn ascii('X')}, "
379: + "{fn char(88)}," + "{fn concat('X','B')},"
380: + "{fn difference('X','B')},"
381: + "{fn insert('XXX',2,1, 'Y')}," + "{fn lcase('XXX')},"
382: + "{fn length('XXX')}," + "{fn ltrim(' XXX')},"
383: + "{fn repeat('X', 3)},"
384: + "{fn replace('XXXYYYXXX', 'YYY', 'FRED')},"
385: + "{fn right('XXX', 1)}," + "{fn rtrim('XXX ')}, "
386: + "{fn soundex('FRED')},"
387: + "'X' + {fn space(1)} + 'X',"
388: + "{fn substring('FRED', 2, 1)},"
389: + "{fn ucase('xxx')},"
390: + "{fn locate('fred', 'xxxfredyyy')},"
391: + "{fn left('FRED', 1)}" + "");
392: assertTrue(rs.next());
393: assertEquals("ascii", 88, rs.getInt(1));
394: assertEquals("char", "X", rs.getString(2));
395: assertEquals("concat", "XB", rs.getString(3));
396: assertEquals("difference", 3, rs.getInt(4));
397: assertEquals("insert", "XYX", rs.getString(5));
398: assertEquals("lcase", "xxx", rs.getString(6));
399: assertEquals("insert", 3, rs.getInt(7));
400: assertEquals("ltrim", "XXX", rs.getString(8));
401: assertEquals("repeat", "XXX", rs.getString(9));
402: assertEquals("replace", "XXXFREDXXX", rs.getString(10));
403: assertEquals("right", "X", rs.getString(11));
404: assertEquals("rtrim", "XXX", rs.getString(12));
405: assertEquals("soundex", "F630", rs.getString(13));
406: assertEquals("space", "X X", rs.getString(14));
407: assertEquals("substring", "R", rs.getString(15));
408: assertEquals("ucase", "XXX", rs.getString(16));
409: assertEquals("locate", 4, rs.getInt(17));
410: assertEquals("left", "F", rs.getString(18));
411:
412: stmt.close();
413: }
414:
415: /**
416: * Test nested escapes
417: *
418: * @throws Exception
419: */
420: public void testNestedEscapes() throws Exception {
421: String sql = "SELECT {fn convert({fn month({fn now()})},varchar)} WHERE X";
422: assertEquals(
423: "SELECT convert(varchar,datepart(month,getdate())) WHERE X",
424: con.nativeSQL(sql));
425: sql = "{?=call testproc(?, {fn now()})}";
426: assertEquals("EXECUTE testproc ?,getdate()", con.nativeSQL(sql));
427: sql = "SELECT * FROM {oj t1 LEFT OUTER JOIN {oj t2 LEFT OUTER JOIN t2 ON condition1} ON condition2}";
428: assertEquals(
429: "SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t2 ON condition1 ON condition2",
430: con.nativeSQL(sql));
431: }
432:
433: /**
434: * Test conversion of various types to LONGVARCHAR. This functionality was
435: * broken in 0.9 because changes were made to handle LONGVARCHAR internally
436: * as Clob rather than String (but these did not take into consideration
437: * all possible cases.
438: *
439: * @throws SQLException
440: */
441: public void testConversionToLongvarchar() throws SQLException {
442: Statement stmt = con.createStatement();
443: stmt.execute("CREATE TABLE #testConversionToLongvarchar ("
444: + " id INT," + " val NTEXT)");
445:
446: int id = 0;
447: String decimalValue = "1234.5678";
448: String booleanValue = "true";
449: String integerValue = "1234567";
450: String longValue = "1234567890123";
451: Date dateValue = new Date(System.currentTimeMillis());
452: Time timeValue = new Time(System.currentTimeMillis());
453: Timestamp timestampValue = new Timestamp(System
454: .currentTimeMillis());
455:
456: PreparedStatement pstmt = con
457: .prepareStatement("INSERT INTO #testConversionToLongvarchar (id, val) VALUES (?, ?)");
458:
459: // Test BigDecimal to LONGVARCHAR conversion
460: pstmt.setInt(1, ++id);
461: pstmt.setObject(2, new BigDecimal(decimalValue),
462: java.sql.Types.LONGVARCHAR);
463: pstmt.executeUpdate();
464:
465: // Test Boolean to LONGVARCHAR conversion
466: pstmt.setInt(1, ++id);
467: pstmt.setObject(2, new Boolean(booleanValue),
468: java.sql.Types.LONGVARCHAR);
469: pstmt.executeUpdate();
470:
471: // Test Integer to LONGVARCHAR conversion
472: pstmt.setInt(1, ++id);
473: pstmt.setObject(2, new Integer(integerValue),
474: java.sql.Types.LONGVARCHAR);
475: pstmt.executeUpdate();
476:
477: // Test Long to LONGVARCHAR conversion
478: pstmt.setInt(1, ++id);
479: pstmt.setObject(2, new Long(longValue),
480: java.sql.Types.LONGVARCHAR);
481: pstmt.executeUpdate();
482:
483: // Test Float to LONGVARCHAR conversion
484: pstmt.setInt(1, ++id);
485: pstmt.setObject(2, new Float(integerValue),
486: java.sql.Types.LONGVARCHAR);
487: pstmt.executeUpdate();
488:
489: // Test Double to LONGVARCHAR conversion
490: pstmt.setInt(1, ++id);
491: pstmt.setObject(2, new Double(longValue),
492: java.sql.Types.LONGVARCHAR);
493: pstmt.executeUpdate();
494:
495: // Test Date to LONGVARCHAR conversion
496: pstmt.setInt(1, ++id);
497: pstmt.setObject(2, dateValue, java.sql.Types.LONGVARCHAR);
498: pstmt.executeUpdate();
499:
500: // Test Time to LONGVARCHAR conversion
501: pstmt.setInt(1, ++id);
502: pstmt.setObject(2, timeValue, java.sql.Types.LONGVARCHAR);
503: pstmt.executeUpdate();
504:
505: // Test Timestamp to LONGVARCHAR conversion
506: pstmt.setInt(1, id);
507: pstmt.setObject(2, timestampValue, java.sql.Types.LONGVARCHAR);
508: pstmt.executeUpdate();
509: pstmt.close();
510:
511: ResultSet rs = stmt
512: .executeQuery("SELECT * FROM #testConversionToLongvarchar ORDER BY id");
513: assertTrue(rs.next());
514: assertEquals(decimalValue, rs.getString("val"));
515: assertTrue(rs.next());
516: assertEquals("1", rs.getString("val"));
517: assertTrue(rs.next());
518: assertEquals(integerValue, rs.getString("val"));
519: assertTrue(rs.next());
520: assertEquals(longValue, rs.getString("val"));
521: assertTrue(rs.next());
522: assertEquals(Float.parseFloat(integerValue), Float
523: .parseFloat(rs.getString("val")), 0);
524: assertTrue(rs.next());
525: assertEquals(Double.parseDouble(longValue), Double
526: .parseDouble(rs.getString("val")), 0);
527: assertTrue(rs.next());
528: assertEquals(dateValue.toString(), rs.getString("val"));
529: assertTrue(rs.next());
530: assertEquals(timeValue.toString(), rs.getString("val"));
531: assertTrue(rs.next());
532: assertEquals(timestampValue.toString(), rs.getString("val"));
533:
534: rs.close();
535: stmt.close();
536: }
537:
538: public static void main(String[] args) {
539: junit.textui.TestRunner.run(SunTest.class);
540: }
541: }
|