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: import net.sourceforge.jtds.jdbc.DefaultProperties;
023: import net.sourceforge.jtds.jdbc.Messages;
024: import net.sourceforge.jtds.jdbc.Driver;
025:
026: import java.math.BigDecimal;
027: import java.sql.CallableStatement;
028: import java.sql.PreparedStatement;
029: import java.sql.ResultSet;
030: import java.sql.ResultSetMetaData;
031: import java.sql.Statement;
032: import java.sql.Types;
033:
034: /**
035: * Test case to illustrate use of TDS 8 support
036: *
037: * @version 1.0
038: */
039: public class Tds8Test extends DatabaseTestCase {
040:
041: public static Test suite() {
042:
043: if (!DefaultProperties.TDS_VERSION_80.equals(props
044: .getProperty(Messages.get(Driver.TDS)))) {
045:
046: return new TestSuite();
047: }
048:
049: return new TestSuite(Tds8Test.class);
050: }
051:
052: public Tds8Test(String name) {
053: super (name);
054: }
055:
056: public void testBigInt1() throws Exception {
057: Statement stmt = con.createStatement();
058: stmt
059: .execute("CREATE TABLE #bigint1 (num bigint, txt varchar(100))");
060: PreparedStatement pstmt = con
061: .prepareStatement("INSERT INTO #bigint1 (num, txt) VALUES (?, ?)");
062: pstmt.setLong(1, 1234567890123L);
063: pstmt.setString(2, "1234567890123");
064: assertEquals("Insert bigint failed", 1, pstmt.executeUpdate());
065: ResultSet rs = stmt.executeQuery("SELECT * FROM #bigint1");
066: assertNotNull(rs);
067: assertTrue(rs.next());
068: assertEquals(String.valueOf(rs.getLong(1)), rs.getString(2));
069: stmt.close();
070: pstmt.close();
071: }
072:
073: /**
074: * Test BIGINT data type.
075: * Test for [989963] BigInt becomes Numeric
076: */
077: public void testBigInt2() throws Exception {
078: long data = 1;
079:
080: Statement stmt = con.createStatement();
081: stmt
082: .execute("CREATE TABLE #bigint2 (data BIGINT, minval BIGINT, maxval BIGINT)");
083: stmt.close();
084:
085: PreparedStatement pstmt = con
086: .prepareStatement("INSERT INTO #bigint2 (data, minval, maxval) VALUES (?, ?, ?)");
087:
088: pstmt.setLong(1, data);
089: pstmt.setLong(2, Long.MIN_VALUE);
090: pstmt.setLong(3, Long.MAX_VALUE);
091: assertEquals(pstmt.executeUpdate(), 1);
092:
093: pstmt.close();
094:
095: Statement stmt2 = con.createStatement();
096: ResultSet rs = stmt2
097: .executeQuery("SELECT data, minval, maxval FROM #bigint2");
098:
099: assertTrue(rs.next());
100:
101: assertTrue(rs.getBoolean(1));
102: assertTrue(rs.getByte(1) == 1);
103: assertTrue(rs.getShort(1) == 1);
104: assertTrue(rs.getInt(1) == 1);
105: assertTrue(rs.getLong(1) == 1);
106: assertTrue(rs.getFloat(1) == 1);
107: assertTrue(rs.getDouble(1) == 1);
108: assertTrue(rs.getBigDecimal(1).longValue() == 1);
109: assertEquals(rs.getString(1), "1");
110:
111: Object tmpData = rs.getObject(1);
112:
113: assertTrue(tmpData instanceof Long);
114: assertTrue(data == ((Long) tmpData).longValue());
115:
116: ResultSetMetaData resultSetMetaData = rs.getMetaData();
117:
118: assertNotNull(resultSetMetaData);
119: assertEquals(resultSetMetaData.getColumnType(1), Types.BIGINT);
120:
121: assertEquals(rs.getLong(2), Long.MIN_VALUE);
122: assertEquals(rs.getLong(3), Long.MAX_VALUE);
123:
124: assertTrue(!rs.next());
125: stmt2.close();
126: rs.close();
127: }
128:
129: public void testSqlVariant() throws Exception {
130: Statement stmt = con.createStatement();
131: stmt
132: .execute("CREATE TABLE #VARTEST (id int, data sql_variant)");
133: PreparedStatement pstmt = con
134: .prepareStatement("INSERT INTO #VARTEST (id, data) VALUES (?, ?)");
135:
136: pstmt.setInt(1, 1);
137: pstmt.setString(2, "TEST STRING");
138: assertEquals("Insert 1 failed", pstmt.executeUpdate(), 1);
139: pstmt.setInt(1, 2);
140: pstmt.setInt(2, 255);
141: assertEquals("Insert 2 failed", pstmt.executeUpdate(), 1);
142: pstmt.setInt(1, 3);
143: pstmt.setBigDecimal(2, new BigDecimal("10.23"));
144: assertEquals("Insert 3 failed", pstmt.executeUpdate(), 1);
145: pstmt.setInt(1, 4);
146: byte bytes[] = { 'X', 'X', 'X' };
147: pstmt.setBytes(2, bytes);
148: assertEquals("Insert 4 failed", pstmt.executeUpdate(), 1);
149: ResultSet rs = stmt
150: .executeQuery("SELECT id, data FROM #VARTEST ORDER BY id");
151: assertNotNull(rs);
152: assertTrue(rs.next());
153: assertEquals("TEST STRING", rs.getString(2));
154: assertTrue(rs.next());
155: assertEquals(255, rs.getInt(2));
156: assertTrue(rs.next());
157: assertEquals("java.math.BigDecimal", rs.getObject(2).getClass()
158: .getName());
159: assertEquals("10.23", rs.getString(2));
160: assertTrue(rs.next());
161: assertEquals("585858", rs.getString(2));
162: stmt.close();
163: pstmt.close();
164: }
165:
166: public void testUserFn() throws Exception {
167: dropFunction("f_varret");
168: Statement stmt = con.createStatement();
169: stmt
170: .execute("CREATE FUNCTION f_varret(@data varchar(100)) RETURNS sql_variant AS\r\n"
171: + "BEGIN\r\n"
172: + "RETURN 'Test ' + @data\r\n"
173: + "END");
174: stmt.close();
175: CallableStatement cstmt = con
176: .prepareCall("{?=call f_varret(?)}");
177: cstmt.registerOutParameter(1, java.sql.Types.OTHER);
178: cstmt.setString(2, "String");
179: cstmt.execute();
180: assertEquals("Test String", cstmt.getString(1));
181: cstmt.close();
182: dropFunction("f_varret");
183: }
184:
185: public void testMetaData() throws Exception {
186: Statement stmt = con.createStatement();
187: stmt
188: .execute("create table #testrsmd (id int, data varchar(10), num decimal(10,2))");
189: stmt.close();
190:
191: PreparedStatement pstmt = con
192: .prepareStatement("select * from #testrsmd where id = ?");
193: ResultSetMetaData rsmd = pstmt.getMetaData();
194: assertNotNull(rsmd);
195: assertEquals(3, rsmd.getColumnCount());
196: assertEquals("data", rsmd.getColumnName(2));
197: assertEquals(2, rsmd.getScale(3));
198: pstmt.close();
199: }
200:
201: /**
202: * Test for bug [1042272] jTDS doesn't allow null value into Boolean.
203: */
204: public void testNullBoolean() throws Exception {
205: Statement stmt = con.createStatement();
206: stmt
207: .execute("create table #testNullBoolean (id int, value bit)");
208:
209: PreparedStatement pstmt = con
210: .prepareStatement("insert into #testNullBoolean (id, value) values (?, ?)");
211: pstmt.setInt(1, 1);
212: pstmt.setNull(2, 16 /* Types.BOOLEAN */);
213: assertEquals(1, pstmt.executeUpdate());
214: pstmt.close();
215:
216: ResultSet rs = stmt
217: .executeQuery("select * from #testNullBoolean");
218: assertTrue(rs.next());
219: assertEquals(1, rs.getInt(1));
220: assertEquals(null, rs.getObject(2));
221: assertFalse(rs.next());
222: rs.close();
223: stmt.close();
224: }
225:
226: /**
227: * Test column collations.
228: */
229: public void testColumnCollations() throws Exception {
230: Statement stmt = con.createStatement();
231: stmt
232: .execute("create table #testColumnCollations (id int primary key, "
233: + "cp437val varchar(255) collate SQL_Latin1_General_Cp437_CI_AS, "
234: + "cp850val varchar(255) collate SQL_Latin1_General_Cp850_CI_AS, "
235: + "ms874val varchar(255) collate Thai_CI_AS, "
236: + "ms932val varchar(255) collate Japanese_CI_AS, "
237: + "ms936val varchar(255) collate Chinese_PRC_CI_AS, "
238: + "ms949val varchar(255) collate Korean_Wansung_CI_AS, "
239: + "ms950val varchar(255) collate Chinese_Taiwan_Stroke_CI_AS, "
240: + "cp1250val varchar(255) collate SQL_Romanian_Cp1250_CI_AS, "
241: + "cp1252val varchar(255) collate SQL_Latin1_General_Cp1_CI_AS)");
242:
243: ResultSet rs = stmt
244: .executeQuery("select * from #testColumnCollations");
245: assertFalse(rs.next());
246: rs.close();
247:
248: PreparedStatement pstmt = con
249: .prepareStatement("insert into #testColumnCollations "
250: + "(id, cp437val, cp850val, ms874val, ms932val, "
251: + "ms936val, ms949val, ms950val, cp1250val, cp1252val) "
252: + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
253:
254: // Test inserting and retrieving pure-ASCII values
255: pstmt.setInt(1, 1);
256: for (int i = 2; i <= 10; i++) {
257: pstmt.setString(i, "test");
258: }
259: assertEquals(1, pstmt.executeUpdate());
260:
261: rs = stmt.executeQuery("select * from #testColumnCollations");
262: assertTrue(rs.next());
263: for (int i = 2; i <= 10; i++) {
264: assertEquals("test", rs.getString(i));
265: }
266: assertFalse(rs.next());
267: rs.close();
268: assertEquals(1, stmt
269: .executeUpdate("delete from #testColumnCollations"));
270:
271: // Test inserting and retrieving charset-specific values via PreparedStatement
272: String[] values = {
273: "123abc\u2591\u2592\u2593\u221a\u221e\u03b1",
274: "123abc\u00d5\u00f5\u2017\u00a5\u2591\u2592",
275: "123abc\u20ac\u2018\u2019\u0e10\u0e1e\u0e3a",
276: "123abc\uff67\uff68\uff9e\u60c6\u7210\ufa27",
277: "123abc\u6325\u8140\u79a9\u9f1e\u9f32\ufa29",
278: "123abc\uac4e\ub009\ubcde\u00de\u24d0\u30e5",
279: "123abc\ufe4f\u00d7\uff5e\u515e\u65b0\u7881",
280: "123abc\u20ac\u201a\u0103\u015e\u0162\u00f7",
281: "123abc\u20ac\u201e\u017d\u00fe\u02dc\u00b8" };
282: for (int i = 2; i <= 10; i++) {
283: pstmt.setString(i, values[i - 2]);
284: }
285: assertEquals(1, pstmt.executeUpdate());
286: pstmt.close();
287:
288: rs = stmt.executeQuery("select * from #testColumnCollations");
289: assertTrue(rs.next());
290: for (int i = 2; i <= 10; i++) {
291: assertEquals("Column " + i + " doesn't match",
292: values[i - 2], rs.getString(i));
293: }
294: assertFalse(rs.next());
295: rs.close();
296: pstmt.close();
297: stmt.close();
298:
299: // Test inserting and retrieving charset-specific values via updateable ResultSet
300: stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
301: ResultSet.CONCUR_UPDATABLE);
302: rs = stmt.executeQuery("select * from #testColumnCollations");
303: assertTrue(rs.next());
304: for (int i = 2; i <= 10; i++) {
305: rs.updateString(i, rs.getString(i) + "updated");
306: values[i - 2] = values[i - 2] + "updated";
307: }
308: rs.updateRow();
309: for (int i = 2; i <= 10; i++) {
310: assertEquals("Column " + i + " doesn't match",
311: values[i - 2], rs.getString(i));
312: }
313: assertFalse(rs.next());
314: rs.close();
315: stmt.close();
316: }
317:
318: /**
319: * Test for bug [981958] PreparedStatement doesn't work correctly
320: */
321: public void testEncoding1251Test1() throws Exception {
322: String value = "\u0441\u043b\u043e\u0432\u043e"; // String in Cp1251 encoding
323: Statement stmt = con.createStatement();
324:
325: stmt
326: .execute("CREATE TABLE #e1251t1 (data varchar(255) COLLATE Cyrillic_General_BIN)");
327: assertEquals(stmt
328: .executeUpdate("INSERT INTO #e1251t1 (data) VALUES (N'"
329: + value + "')"), 1);
330: stmt.close();
331:
332: PreparedStatement pstmt = con
333: .prepareStatement("SELECT data FROM #e1251t1 WHERE data = ?");
334: pstmt.setString(1, value);
335: ResultSet rs = pstmt.executeQuery();
336:
337: assertTrue(rs.next());
338: //assertEquals(value, rs.getString(1));
339: assertTrue(!rs.next());
340: pstmt.close();
341: rs.close();
342: }
343:
344: /**
345: * Test for enhanced database metadata for SQL 2005.
346: * E.g. distinguish between varchar(max) and text.
347: * @throws Exception
348: */
349: public void testSQL2005MetaData() throws Exception {
350: Statement stmt = con.createStatement();
351: int dbVer = Integer.parseInt(con.getMetaData()
352: .getDatabaseProductVersion().substring(0, 2));
353: if (dbVer <= 8) {
354: // Not SQL 2005
355: return;
356: }
357: stmt
358: .execute("CREATE TABLE #test ("
359: + "id int primary key, "
360: + "txt text, ntxt ntext, img image, "
361: + "vc varchar(max), nvc nvarchar(max), vb varbinary(max))");
362: ResultSet rs = con.getMetaData().getColumns("tempdb", null,
363: "#test", "%");
364: assertNotNull(rs);
365: assertTrue(rs.next());
366: // Skip int col
367: assertTrue(rs.next());
368: // Should be text
369: assertEquals("text", rs.getString("TYPE_NAME"));
370: assertTrue(rs.next());
371: // Should be ntext
372: assertEquals("ntext", rs.getString("TYPE_NAME"));
373: assertTrue(rs.next());
374: // Should be image
375: assertEquals("image", rs.getString("TYPE_NAME"));
376: assertTrue(rs.next());
377: // Should be varchar(max)
378: assertEquals("varchar", rs.getString("TYPE_NAME"));
379: assertTrue(rs.next());
380: // Should be nvarchar(max)
381: assertEquals("nvarchar", rs.getString("TYPE_NAME"));
382: assertTrue(rs.next());
383: // Should be varbinary(max)
384: assertEquals("varbinary", rs.getString("TYPE_NAME"));
385: stmt.close();
386: }
387:
388: public static void main(String[] args) {
389: junit.textui.TestRunner.run(Tds8Test.class);
390: }
391: }
|