// jTDS JDBC Driver for Microsoft SQL Server and Sybase // Copyright (C) 2004 The jTDS Project // // This library is free software; you can redistribute it and/or // modify it under the terms of the GNU Lesser General Public // License as published by the Free Software Foundation; either // version 2.1 of the License, or (at your option) any later version. // // This library is distributed in the hope that it will be useful, // but WITHOUT ANY WARRANTY; without even the implied warranty of // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU // Lesser General Public License for more details. // // You should have received a copy of the GNU Lesser General Public // License along with this library; if not, write to the Free Software // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA // package net.sourceforge.jtds.jdbc; import junit.framework.Test; import junit.framework.TestSuite; import java.sql.*; import java.math.BigDecimal; /** * Test case to illustrate errors reported by SUN JBDC compatibility test suite. * * @version 1.0 */ public class SunTest extends DatabaseTestCase { public static Test suite() { return new TestSuite(SunTest.class); } public SunTest(String name) { super(name); } /** * Test for SUN bug [ PrepStmt1.getMetaData() ] * Driver loops if select contains commas. * * @throws Exception */ public void testGetMetaData() throws Exception { PreparedStatement pstmt = con.prepareStatement("SELECT name, id, type FROM sysobjects WHERE type = 'U'"); ResultSetMetaData rsmd = pstmt.getMetaData(); assertEquals("name", rsmd.getColumnName(1)); pstmt.close(); } /** * Generic Tests for SUN bugs such as *
    *
  1. Can't convert VARCHAR to Timestamp *
  2. Can't convert VARCHAR to Time *
  3. Can't convert VARCHAR to Date *
  4. Internal time representation causes equals to fail *
* @throws Exception */ public void testDateTime() throws Exception { final String dateStr = "1983-01-31"; final String timeStr = "12:59:59"; final String tsStr = "1983-01-31 23:59:59.333"; Statement stmt = con.createStatement(); stmt.execute("CREATE PROC #CTOT_PROC @tdate DATETIME OUTPUT, @ttime DATETIME OUTPUT, @tts DATETIME OUTPUT AS " + "BEGIN SELECT @tdate=tdate, @ttime=ttime, @tts=tts FROM #CTOT END"); stmt.execute("CREATE TABLE #CTOT (tdate DATETIME, ttime DATETIME, tts DATETIME, tnull DATETIME NULL)"); stmt.close(); PreparedStatement pstmt = con.prepareStatement("INSERT INTO #CTOT (tdate, ttime, tts) VALUES(?,?,?)"); pstmt.setObject(1, dateStr, java.sql.Types.DATE); pstmt.setObject(2, timeStr, java.sql.Types.TIME); pstmt.setObject(3, tsStr, java.sql.Types.TIMESTAMP); pstmt.execute(); assertEquals(1, pstmt.getUpdateCount()); pstmt.close(); CallableStatement cstmt = con.prepareCall("{call #CTOT_PROC(?,?,?)}"); cstmt.registerOutParameter(1, java.sql.Types.DATE); cstmt.registerOutParameter(2, java.sql.Types.TIME); cstmt.registerOutParameter(3, java.sql.Types.TIMESTAMP); cstmt.execute(); assertEquals(dateStr, cstmt.getString(1)); assertEquals(timeStr, cstmt.getString(2)); assertEquals(java.sql.Time.valueOf(timeStr), cstmt.getTime(2)); assertEquals(tsStr, cstmt.getString(3)); cstmt.close(); stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM #CTOT"); assertTrue(rs.next()); java.sql.Time retval = rs.getTime(2); java.sql.Time tstval = java.sql.Time.valueOf(timeStr); assertEquals(tstval, retval); stmt.close(); pstmt = con.prepareStatement("UPDATE #CTOT SET tnull = ?"); pstmt.setTime(1, tstval); pstmt.execute(); assertEquals(1, pstmt.getUpdateCount()); pstmt.close(); stmt = con.createStatement(); rs = stmt.executeQuery("SELECT * FROM #CTOT"); assertTrue(rs.next()); retval = rs.getTime(4); assertEquals(tstval, retval); stmt.close(); } /** * Generic test for errors caused by promotion out parameters of Float to Double by driver. * eg [ callStmt4.testGetObject34 ] Class cast exception Float. * * @throws Exception */ public void testCharToReal() throws Exception { final String minStr = "3.4E38"; final String maxStr = "1.18E-38"; Statement stmt = con.createStatement(); stmt.execute("CREATE PROC #CTOR_PROC @minval REAL OUTPUT, @maxval REAL OUTPUT AS " + "BEGIN SELECT @minval=min_val, @maxval=max_val FROM #CTOR END"); stmt.execute("CREATE TABLE #CTOR (min_val REAL, max_val REAL)"); stmt.execute("INSERT INTO #CTOR VALUES(" + minStr +"," + maxStr + ")"); assertEquals(1, stmt.getUpdateCount()); ResultSet rs = stmt.executeQuery("SELECT * FROM #CTOR"); assertNotNull(rs); assertTrue(rs.next()); assertEquals(minStr, rs.getString(1)); assertEquals(maxStr, rs.getString(2)); assertTrue(rs.getObject(1) instanceof Float); stmt.close(); CallableStatement cstmt = con.prepareCall("{call #CTOR_PROC(?,?)}"); cstmt.registerOutParameter(1, java.sql.Types.REAL); cstmt.registerOutParameter(2, java.sql.Types.REAL); cstmt.execute(); assertEquals(minStr, cstmt.getString(1)); assertEquals(maxStr, cstmt.getString(2)); cstmt.close(); } /** * Generic test for SUN bugs: bigint null parameter values sent as integer size. * * @throws Exception */ public void testCharToLong() throws Exception { final String minStr = "9223372036854775807"; final String maxStr = "-9223372036854775808"; Statement stmt = con.createStatement(); stmt.execute("CREATE PROC #CTOL_PROC @minval BIGINT OUTPUT, @maxval BIGINT OUTPUT AS " + "BEGIN SELECT @minval=min_val, @maxval=max_val FROM #CTOL END"); stmt.execute("CREATE TABLE #CTOL (min_val BIGINT, max_val BIGINT)"); stmt.execute("INSERT INTO #CTOL VALUES(" + minStr +"," + maxStr + ")"); assertEquals(1, stmt.getUpdateCount()); ResultSet rs = stmt.executeQuery("SELECT * FROM #CTOL"); assertNotNull(rs); assertTrue(rs.next()); assertEquals(minStr, rs.getString(1)); assertEquals(maxStr, rs.getString(2)); stmt.close(); CallableStatement cstmt = con.prepareCall("{call #CTOL_PROC(?,?)}"); cstmt.registerOutParameter(1, java.sql.Types.BIGINT); cstmt.registerOutParameter(2, java.sql.Types.BIGINT); cstmt.execute(); assertEquals(minStr, cstmt.getString(1)); assertEquals(maxStr, cstmt.getString(2)); cstmt.close(); } /** * Test for SUN bug [ dbMeta8.testGetProcedures ] * The wrong column names are returned by getProcedures(). * * @throws Exception */ public void testGetProcedures() throws Exception { String names[] = {"PROCEDURE_CAT","PROCEDURE_SCHEM","PROCEDURE_NAME","","","","REMARKS","PROCEDURE_TYPE"}; DatabaseMetaData dbmd = con.getMetaData(); ResultSet rs = dbmd.getProcedures(null, null, "%"); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 0; i < names.length; i++) { if (names[i].length() > 0) { assertEquals(names[i], rsmd.getColumnName(i+1)); } } rs.close(); } /** * Generic test for SUN bug where Float was promoted to Double * by driver leading to ClassCastExceptions in the tests. * Example [ prepStmt4.testSetObject16 ] * * @throws Exception */ public void testGetFloatObject() throws Exception { Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #GETF (val REAL)"); stmt.execute("INSERT INTO #GETF (val) VALUES (1.7E10)"); assertEquals(1,stmt.getUpdateCount()); ResultSet rs = stmt.executeQuery("SELECT * FROM #GETF"); assertTrue(rs.next()); assertTrue(rs.getObject(1) instanceof Float); rs.close(); stmt.close(); } /** * Test for SUN bug [ resultSet1.testSetFetchSize02 ] * attempt to set non zero fetch size rejected. * * @throws Exception */ public void testSetFetchSize() throws Exception { CallableStatement cstmt = con.prepareCall("{call sp_who}"); ResultSet rs = cstmt.executeQuery(); rs.setFetchSize(5); assertEquals(5, rs.getFetchSize()); rs.close(); cstmt.close(); } /** * Test for SUN bug [ stmt2.testSetFetchDirection04 ] * fetch direction constant not validated. * * @throws Exception */ public void testSetFetchDirectiion() throws Exception { Statement stmt = con.createStatement(); try { stmt.setFetchDirection(-1); fail("setFecthDirection does not validate parameter"); } catch (SQLException sqe) { } stmt.close(); } /** * Test for bug [ 1012307 ] PreparedStatement.setObject(java.util.Date) not working. * The driver should throw an exception if the object is not of a valid * type according to table * * @throws Exception */ public void testSetDateObject() throws Exception { Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #SETD (val DATETIME)"); PreparedStatement pstmt = con.prepareStatement("INSERT INTO #SETD (val) VALUES (?)"); long tval = 60907507200000L; //1999-12-31 try { pstmt.setObject(1, new java.util.Date(tval)); fail("No exception for setObject(java.util.Date)"); } catch (SQLException e) { // OK unsupported object type trapped } pstmt.close(); stmt.close(); } /** * Test for bug [ 1012301 ] 0.9-rc1: Prepared statement execution error. * * @throws Exception */ public void testPrepStmtError() throws Exception { Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #PERR (val VARCHAR(255))\r\n" + "INSERT INTO #PERR (val) VALUES('Test String')"); PreparedStatement pstmt = con.prepareStatement(" SELECT * FROM #PERR WHERE val = ?"); pstmt.setString(1,"Test String"); assertTrue(pstmt.execute()); ResultSet rs = pstmt.getResultSet(); assertTrue(rs.next()); rs.close(); pstmt.close(); stmt.close(); } /** * Test for bug [ 1011650 ] 0.9-rc1: comments get parsed * * @throws Exception */ public void testSqlComments() throws Exception { String testSql = "/* This is a test of the comment {fn test()} parser */\r\n" + "SELECT * FROM XXXX -- In line comment {d 1999-01-01}\r\n"+ "INSERT INTO B VALUES({d 1999-01-01}) -- Unterminated in line comment"; String outSql = "/* This is a test of the comment {fn test()} parser */\r\n" + "SELECT * FROM XXXX -- In line comment {d 1999-01-01}\r\n"+ "INSERT INTO B VALUES(convert(datetime,'19990101')) -- Unterminated in line comment"; assertEquals(outSql, con.nativeSQL(testSql)); } /** * Test for bug [ 1008126 ] Metadata getTimeDateFunctions() wrong * * @throws Exception */ public void testDateTimeFn() throws Exception { Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #DTFN (ttime SMALLDATETIME, tdate SMALLDATETIME, ftime SMALLDATETIME, fdate SMALLDATETIME, tnow DATETIME)"); stmt.execute("INSERT INTO #DTFN (ttime, tdate, ftime, fdate, tnow) VALUES (getdate(), getdate(), {fn curtime()}, {fn curdate()}, {fn now()})"); assertEquals(1, stmt.getUpdateCount()); ResultSet rs = stmt.executeQuery("SELECT * FROM #DTFN"); assertTrue(rs.next()); assertEquals("curdate()",rs.getDate(2),rs.getDate(4)); assertEquals("curtime()",rs.getTime(1),rs.getTime(3)); assertEquals("now()",rs.getDate(1),rs.getDate(5)); rs = stmt.executeQuery("SELECT {fn dayname('2004-08-21')}, " + "{fn dayofmonth('2004-08-21')}, " + "{fn dayofweek('2004-08-21')}," + "{fn dayofyear('2004-08-21')}," + "{fn hour('23:47:32')}," + "{fn minute('23:47:32')}," + "{fn second('23:47:32')}," + "{fn year('2004-08-21')}," + "{fn quarter('2004-08-21')}," + "{fn month('2004-08-21')}," + "{fn week('2004-08-21')}," + "{fn monthname('2004-08-21')}," + "{fn timestampdiff(SQL_TSI_DAY, '2004-08-19','2004-08-21')}," + "{fn timestampadd(SQL_TSI_MONTH, 1, '2004-08-21')}" + ""); assertTrue(rs.next()); assertEquals("dayname", "Saturday", rs.getString(1)); assertEquals("dayofmonth", 21, rs.getInt(2)); assertEquals("dayofweek", 7, rs.getInt(3)); assertEquals("dayofyear", 234, rs.getInt(4)); assertEquals("hour", 23, rs.getInt(5)); assertEquals("minute", 47, rs.getInt(6)); assertEquals("second", 32, rs.getInt(7)); assertEquals("year", 2004, rs.getInt(8)); assertEquals("quarter", 3, rs.getInt(9)); assertEquals("month", 8, rs.getInt(10)); assertEquals("week", 34, rs.getInt(11)); assertEquals("monthname", "August", rs.getString(12)); assertEquals("timestampdiff", 2, rs.getInt(13)); assertEquals("timestampadd", java.sql.Date.valueOf("2004-09-21"), rs.getDate(14)); stmt.close(); } /** * Test for scalar string functions. * * @throws Exception */ public void testStringFn() throws Exception { Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT {fn ascii('X')}, "+ "{fn char(88)}," + "{fn concat('X','B')}," + "{fn difference('X','B')}," + "{fn insert('XXX',2,1, 'Y')}," + "{fn lcase('XXX')}," + "{fn length('XXX')}," + "{fn ltrim(' XXX')}," + "{fn repeat('X', 3)}," + "{fn replace('XXXYYYXXX', 'YYY', 'FRED')}," + "{fn right('XXX', 1)}," + "{fn rtrim('XXX ')}, " + "{fn soundex('FRED')}," + "'X' + {fn space(1)} + 'X'," + "{fn substring('FRED', 2, 1)}," + "{fn ucase('xxx')}," + "{fn locate('fred', 'xxxfredyyy')}," + "{fn left('FRED', 1)}" + ""); assertTrue(rs.next()); assertEquals("ascii", 88, rs.getInt(1)); assertEquals("char", "X", rs.getString(2)); assertEquals("concat", "XB", rs.getString(3)); assertEquals("difference", 3, rs.getInt(4)); assertEquals("insert", "XYX", rs.getString(5)); assertEquals("lcase", "xxx", rs.getString(6)); assertEquals("insert", 3, rs.getInt(7)); assertEquals("ltrim", "XXX", rs.getString(8)); assertEquals("repeat", "XXX", rs.getString(9)); assertEquals("replace", "XXXFREDXXX", rs.getString(10)); assertEquals("right", "X", rs.getString(11)); assertEquals("rtrim", "XXX", rs.getString(12)); assertEquals("soundex", "F630", rs.getString(13)); assertEquals("space", "X X", rs.getString(14)); assertEquals("substring", "R", rs.getString(15)); assertEquals("ucase", "XXX", rs.getString(16)); assertEquals("locate", 4, rs.getInt(17)); assertEquals("left", "F", rs.getString(18)); stmt.close(); } /** * Test nested escapes * * @throws Exception */ public void testNestedEscapes() throws Exception { String sql = "SELECT {fn convert({fn month({fn now()})},varchar)} WHERE X"; assertEquals("SELECT convert(varchar,datepart(month,getdate())) WHERE X", con.nativeSQL(sql)); sql = "{?=call testproc(?, {fn now()})}"; assertEquals("EXECUTE testproc ?,getdate()", con.nativeSQL(sql)); sql = "SELECT * FROM {oj t1 LEFT OUTER JOIN {oj t2 LEFT OUTER JOIN t2 ON condition1} ON condition2}"; assertEquals("SELECT * FROM t1 LEFT OUTER JOIN t2 LEFT OUTER JOIN t2 ON condition1 ON condition2", con.nativeSQL(sql)); } /** * Test conversion of various types to LONGVARCHAR. This functionality was * broken in 0.9 because changes were made to handle LONGVARCHAR internally * as Clob rather than String (but these did not take into consideration * all possible cases. * * @throws SQLException */ public void testConversionToLongvarchar() throws SQLException { Statement stmt = con.createStatement(); stmt.execute("CREATE TABLE #testConversionToLongvarchar (" + " id INT," + " val NTEXT)"); int id = 0; String decimalValue = "1234.5678"; String booleanValue = "true"; String integerValue = "1234567"; String longValue = "1234567890123"; Date dateValue = new Date(System.currentTimeMillis()); Time timeValue = new Time(System.currentTimeMillis()); Timestamp timestampValue = new Timestamp(System.currentTimeMillis()); PreparedStatement pstmt = con.prepareStatement( "INSERT INTO #testConversionToLongvarchar (id, val) VALUES (?, ?)"); // Test BigDecimal to LONGVARCHAR conversion pstmt.setInt(1, ++id); pstmt.setObject(2, new BigDecimal(decimalValue), java.sql.Types.LONGVARCHAR); pstmt.executeUpdate(); // Test Boolean to LONGVARCHAR conversion pstmt.setInt(1, ++id); pstmt.setObject(2, new Boolean(booleanValue), java.sql.Types.LONGVARCHAR); pstmt.executeUpdate(); // Test Integer to LONGVARCHAR conversion pstmt.setInt(1, ++id); pstmt.setObject(2, new Integer(integerValue), java.sql.Types.LONGVARCHAR); pstmt.executeUpdate(); // Test Long to LONGVARCHAR conversion pstmt.setInt(1, ++id); pstmt.setObject(2, new Long(longValue), java.sql.Types.LONGVARCHAR); pstmt.executeUpdate(); // Test Float to LONGVARCHAR conversion pstmt.setInt(1, ++id); pstmt.setObject(2, new Float(integerValue), java.sql.Types.LONGVARCHAR); pstmt.executeUpdate(); // Test Double to LONGVARCHAR conversion pstmt.setInt(1, ++id); pstmt.setObject(2, new Double(longValue), java.sql.Types.LONGVARCHAR); pstmt.executeUpdate(); // Test Date to LONGVARCHAR conversion pstmt.setInt(1, ++id); pstmt.setObject(2, dateValue, java.sql.Types.LONGVARCHAR); pstmt.executeUpdate(); // Test Time to LONGVARCHAR conversion pstmt.setInt(1, ++id); pstmt.setObject(2, timeValue, java.sql.Types.LONGVARCHAR); pstmt.executeUpdate(); // Test Timestamp to LONGVARCHAR conversion pstmt.setInt(1, id); pstmt.setObject(2, timestampValue, java.sql.Types.LONGVARCHAR); pstmt.executeUpdate(); pstmt.close(); ResultSet rs = stmt.executeQuery( "SELECT * FROM #testConversionToLongvarchar ORDER BY id"); assertTrue(rs.next()); assertEquals(decimalValue, rs.getString("val")); assertTrue(rs.next()); assertEquals("1", rs.getString("val")); assertTrue(rs.next()); assertEquals(integerValue, rs.getString("val")); assertTrue(rs.next()); assertEquals(longValue, rs.getString("val")); assertTrue(rs.next()); assertEquals(Float.parseFloat(integerValue), Float.parseFloat(rs.getString("val")), 0); assertTrue(rs.next()); assertEquals(Double.parseDouble(longValue), Double.parseDouble(rs.getString("val")), 0); assertTrue(rs.next()); assertEquals(dateValue.toString(), rs.getString("val")); assertTrue(rs.next()); assertEquals(timeValue.toString(), rs.getString("val")); assertTrue(rs.next()); assertEquals(timestampValue.toString(), rs.getString("val")); rs.close(); stmt.close(); } public static void main(String[] args) { junit.textui.TestRunner.run(SunTest.class); } }