001: /*
002: * AsTest.java
003: *
004: * Created on 10. September 2001, 09:44
005: */
006: package net.sourceforge.jtds.test;
007:
008: import java.sql.*;
009: import java.math.BigDecimal;
010:
011: import junit.framework.TestSuite;
012:
013: import net.sourceforge.jtds.util.Logger;
014:
015: /**
016: *
017: * @author builder
018: * @version 1.0
019: */
020: public class AsTest extends DatabaseTestCase {
021:
022: public AsTest(String name) {
023: super (name);
024: }
025:
026: public static void main(String args[]) {
027: Logger.setActive(true);
028: if (args.length > 0) {
029: junit.framework.TestSuite s = new TestSuite();
030: for (int i = 0; i < args.length; i++) {
031: s.addTest(new AsTest(args[i]));
032: }
033: junit.textui.TestRunner.run(s);
034: } else
035: junit.textui.TestRunner.run(AsTest.class);
036: }
037:
038: public void testProc1() throws Exception {
039: Statement stmt = con.createStatement();
040: dropProcedure(stmt, "#spTestExec");
041: dropProcedure(stmt, "#spTestExec2");
042:
043: stmt.executeUpdate(" create procedure #spTestExec2 as "
044: + "select 'Did it work?' as Result");
045: stmt.executeUpdate("create procedure #spTestExec as "
046: + "set nocount off "
047: + "create table #tmp ( Result varchar(50) ) "
048: + "insert #tmp execute #spTestExec2 "
049: + "select * from #tmp");
050: stmt.close();
051:
052: CallableStatement cstmt = con.prepareCall("#spTestExec");
053: assertFalse(cstmt.execute());
054: assertEquals(1, cstmt.getUpdateCount());
055:
056: // The JDBC-ODBC driver does not return update counts from stored
057: // procedures so we won't, either.
058: //
059: // SAfe Yes, we will. It seems like that's how it should work. The idea
060: // however is to only return valid update counts (e.g. not from
061: // SET, EXEC or such).
062: assertTrue(cstmt.getMoreResults());
063:
064: boolean passed = false;
065: ResultSet rs = cstmt.getResultSet();
066: while (rs.next()) {
067: passed = true;
068: }
069: assertTrue("Expecting at least one result row", passed);
070: assertTrue(!cstmt.getMoreResults()
071: && cstmt.getUpdateCount() == -1);
072: cstmt.close();
073: // stmt.executeQuery("execute spTestExec");
074: }
075:
076: public void testProc2() throws Exception {
077: Statement stmt = con.createStatement();
078: String sqlwithcount = "create procedure #multi1withcount as "
079: + " set nocount off " + " select 'a' "
080: + " select 'b' "
081: + " create table #multi1withcountt (A VARCHAR(20)) "
082: + " insert into #multi1withcountt VALUES ('a') "
083: + " insert into #multi1withcountt VALUES ('a') "
084: + " insert into #multi1withcountt VALUES ('a') "
085: + " select 'a' " + " select 'b' ";
086: String sqlnocount = "create procedure #multi1nocount as "
087: + " set nocount on " + " select 'a' "
088: + " select 'b' "
089: + " create table #multi1nocountt (A VARCHAR(20)) "
090: + " insert into #multi1nocountt VALUES ('a') "
091: + " insert into #multi1nocountt VALUES ('a') "
092: + " insert into #multi1nocountt VALUES ('a') "
093: + " select 'a' " + " select 'b' ";
094: dropProcedure(stmt, "#multi1withcount");
095: dropProcedure(stmt, "#multi1nocount");
096: stmt.executeUpdate(sqlwithcount);
097: stmt.executeUpdate(sqlnocount);
098: stmt.close();
099:
100: CallableStatement cstmt = con.prepareCall("#multi1nocount");
101: assertTrue(cstmt.execute());
102: ResultSet rs = cstmt.getResultSet();
103: assertTrue(rs.next());
104: assertTrue(rs.getString(1).equals("a"));
105: assertTrue(!rs.next());
106: assertTrue(cstmt.getMoreResults());
107: rs = cstmt.getResultSet();
108: assertTrue(rs.next());
109: assertTrue(rs.getString(1).equals("b"));
110: assertTrue(!rs.next());
111: assertTrue(cstmt.getMoreResults());
112: rs = cstmt.getResultSet();
113: assertTrue(rs.next());
114: assertTrue(!rs.next());
115: assertTrue(cstmt.getMoreResults());
116: rs = cstmt.getResultSet();
117: assertTrue(rs.next());
118: assertTrue(!rs.next());
119: assertTrue(!cstmt.getMoreResults()
120: && cstmt.getUpdateCount() == -1);
121: cstmt.close();
122:
123: cstmt = con.prepareCall("#multi1withcount");
124:
125: // The JDBC-ODBC driver does not return update counts from stored
126: // procedures so we won't, either.
127: //
128: // SAfe Yes, we will. It seems like that's how it should work. The idea
129: // however is to only return valid update counts (e.g. not from
130: // SET, EXEC or such).
131: assertTrue(cstmt.execute());
132: rs = cstmt.getResultSet();
133: assertTrue(rs.next());
134: assertTrue(rs.getString(1).equals("a"));
135: assertTrue(!rs.next());
136: assertTrue(cstmt.getMoreResults());
137: rs = cstmt.getResultSet();
138: assertTrue(rs.next());
139: assertTrue(rs.getString(1).equals("b"));
140: assertTrue(!rs.next());
141: assertTrue(!cstmt.getMoreResults()
142: && cstmt.getUpdateCount() == 1); // insert
143: assertTrue(!cstmt.getMoreResults()
144: && cstmt.getUpdateCount() == 1); // insert
145: assertTrue(!cstmt.getMoreResults()
146: && cstmt.getUpdateCount() == 1); // insert
147: assertTrue(cstmt.getMoreResults()); // select
148: rs = cstmt.getResultSet();
149: assertTrue(rs.next());
150: assertTrue(!rs.next());
151: assertTrue(cstmt.getMoreResults());
152: rs = cstmt.getResultSet();
153: assertTrue(rs.next());
154: assertTrue(!rs.next());
155: assertTrue(!cstmt.getMoreResults()
156: && cstmt.getUpdateCount() == -1);
157: cstmt.close();
158:
159: }
160:
161: public void testBatch1() throws Exception {
162: Statement stmt = con.createStatement();
163: String sqlwithcount1 = " set nocount off " + " select 'a' "
164: + " select 'b' "
165: + " create table #multi2withcountt (A VARCHAR(20)) "
166: + " insert into #multi2withcountt VALUES ('a') "
167: + " insert into #multi2withcountt VALUES ('a') "
168: + " insert into #multi2withcountt VALUES ('a') "
169: + " select 'a' " + " select 'b' "
170: + " drop table #multi2withcountt";
171: String sqlnocount1 = " set nocount on " + " select 'a' "
172: + " select 'b' "
173: + " create table #multi2nocountt (A VARCHAR(20)) "
174: + " insert into #multi2nocountt VALUES ('a') "
175: + " insert into #multi2nocountt VALUES ('a') "
176: + " insert into #multi2nocountt VALUES ('a') "
177: + " select 'a' " + " select 'b' "
178: + " drop table #multi2nocountt";
179: assertTrue(stmt.execute(sqlwithcount1)); // set
180: ResultSet rs = stmt.getResultSet();
181: assertTrue(rs.next());
182: assertTrue(rs.getString(1).equals("a"));
183: assertTrue(!rs.next());
184: assertTrue(stmt.getMoreResults());
185: rs = stmt.getResultSet();
186: assertTrue(rs.next());
187: assertTrue(rs.getString(1).equals("b"));
188: assertTrue(!rs.next());
189: assertTrue(!stmt.getMoreResults() && stmt.getUpdateCount() == 1);
190: assertTrue(!stmt.getMoreResults() && stmt.getUpdateCount() == 1);
191: assertTrue(!stmt.getMoreResults() && stmt.getUpdateCount() == 1);
192: assertTrue(stmt.getMoreResults());
193: rs = stmt.getResultSet();
194: assertTrue(rs.next());
195: assertTrue(!rs.next());
196: assertTrue(stmt.getMoreResults());
197: rs = stmt.getResultSet();
198: assertTrue(rs.next());
199: assertTrue(!rs.next());
200: assertTrue(!stmt.getMoreResults()
201: && stmt.getUpdateCount() == -1);
202:
203: assertTrue(stmt.execute(sqlnocount1)); // set
204: rs = stmt.getResultSet();
205: assertTrue(rs.next());
206: assertTrue(rs.getString(1).equals("a"));
207: assertTrue(!rs.next());
208: assertTrue(stmt.getMoreResults());
209: rs = stmt.getResultSet();
210: assertTrue(rs.next());
211: assertTrue(rs.getString(1).equals("b"));
212: assertTrue(!rs.next());
213: assertTrue(stmt.getMoreResults()); // select
214: rs = stmt.getResultSet();
215: assertTrue(rs.next());
216: assertTrue(!rs.next());
217: assertTrue(stmt.getMoreResults());
218: rs = stmt.getResultSet();
219: assertTrue(rs.next());
220: assertTrue(!rs.next());
221: assertTrue(!stmt.getMoreResults()
222: && stmt.getUpdateCount() == -1);
223: stmt.close();
224: }
225:
226: public void testBug457955() throws Exception {
227: Statement stmt = con.createStatement();
228: dropProcedure("#Bug457955");
229: stmt
230: .executeUpdate(" create procedure #Bug457955 (@par1 VARCHAR(10)) as select @par1");
231: stmt.close();
232: String param = "123456789";
233: CallableStatement cstmt = con.prepareCall("exec #Bug457955 ?");
234: cstmt.setString(1, param);
235: cstmt.executeQuery();
236: cstmt.close();
237: }
238:
239: public void testBugAttTest2() throws Exception {
240: String tabdef = "CREATE TABLE #ICEributeTest_AttributeTest2( "
241: + " ICEobjectId NUMERIC(19) "
242: + " /*CONSTRAINT ICEributeTest_AttributeTest2_PKICEobjectId PRIMARY KEY */ "
243: + " , " + " ICEtestShort INTEGER "
244: + " NULL, " + " ICEtestFloat NUMERIC(28,10) "
245: + " NULL, " + " ICEtestDecimal NUMERIC(28,10) "
246: + " NULL, " + " ICEtestCharacter INTEGER "
247: + " NULL, " + " ICEtestInteger INTEGER "
248: + " NULL, " + " ICEtestString VARCHAR(20) "
249: + " NULL, " + " ICEtestBoolean BIT "
250: + " NULL, " + " ICEtestByte INTEGER "
251: + " NULL, " + " ICEtestDouble NUMERIC(28,10) "
252: + " NULL, " + " ICEtestLong NUMERIC(19) "
253: + " NULL, " + " ICEtestCombined1 VARBINARY(8000) "
254: + " NULL, " + " ICEtestDate DATETIME "
255: + " NULL, "
256: + " testCombined_testFloat NUMERIC(28,10) "
257: + " NULL, " + " testCombined_testShort INTEGER "
258: + " NULL, "
259: + " testCombined_testDecimal NUMERIC(28,10) "
260: + " NULL, "
261: + " testCombined_testCharacter INTEGER "
262: + " NULL, " + " testCombined_testInteger INTEGER "
263: + " NULL, "
264: + " testCombined_testString VARCHAR(50) "
265: + " NULL, " + " testCombined_testBoolean BIT "
266: + " NULL, " + " testCombined_testByte INTEGER "
267: + " NULL, "
268: + " testCombined_testDouble NUMERIC(28,10) "
269: + " NULL, "
270: + " testCombined_testLong NUMERIC(19) "
271: + " NULL, " + " testCombined_testDate DATETIME "
272: + " NULL, "
273: + " ICEtestContainedArrays VARBINARY(8000) "
274: + " NULL, "
275: + " BSF_FILTER_ATTRIBUTE_NAME INTEGER "
276: + " NOT NULL, " + " updateCount INTEGER "
277: + " NOT NULL " + " ) ";
278: Statement stmt = con.createStatement();
279: dropTable("#ICEributeTest_AttributeTest2");
280: stmt.executeUpdate(tabdef);
281: stmt.close();
282: PreparedStatement istmt = con
283: .prepareStatement("INSERT INTO #ICEributeTest_AttributeTest2 ("
284: + "ICEobjectId,BSF_FILTER_ATTRIBUTE_NAME,ICEtestShort,ICEtestFloat,ICEtestDecimal,"
285: + "ICEtestCharacter,ICEtestInteger,ICEtestString,ICEtestBoolean,ICEtestByte,"
286: + "ICEtestDouble,ICEtestLong,ICEtestCombined1,ICEtestDate,testCombined_testFloat,"
287: + "testCombined_testShort,testCombined_testDecimal,testCombined_testCharacter,testCombined_testInteger,testCombined_testString,"
288: + "testCombined_testBoolean,testCombined_testByte,testCombined_testDouble,testCombined_testLong"
289: + ",testCombined_testDate,ICEtestContainedArrays,updateCount ) "
290: + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
291: istmt.setLong(1, (long) 650002);
292: istmt.setInt(2, -1461101755);
293: istmt.setNull(3, java.sql.Types.INTEGER);
294: istmt.setNull(4, java.sql.Types.REAL);
295: try {
296: istmt.setNull(5, java.sql.Types.NUMERIC);
297: } catch (java.sql.SQLException e) {
298: istmt.setNull(5, java.sql.Types.DECIMAL);
299: }
300: istmt.setNull(6, java.sql.Types.INTEGER);
301: istmt.setNull(7, java.sql.Types.INTEGER);
302: istmt.setNull(8, java.sql.Types.VARCHAR);
303: istmt.setNull(9, java.sql.Types.BIT);
304: istmt.setNull(10, java.sql.Types.INTEGER);
305: istmt.setNull(11, java.sql.Types.DOUBLE);
306: istmt.setNull(12, java.sql.Types.BIGINT);
307: istmt.setNull(13, java.sql.Types.LONGVARBINARY);
308: istmt.setNull(14, java.sql.Types.TIMESTAMP);
309: istmt.setNull(15, java.sql.Types.REAL);
310: istmt.setNull(16, java.sql.Types.INTEGER);
311: try {
312: istmt.setNull(17, java.sql.Types.NUMERIC);
313: } catch (java.sql.SQLException e) {
314: istmt.setNull(17, java.sql.Types.DECIMAL);
315: }
316: istmt.setNull(18, java.sql.Types.INTEGER);
317: istmt.setNull(19, java.sql.Types.INTEGER);
318: istmt.setNull(20, java.sql.Types.VARCHAR);
319: istmt.setNull(21, java.sql.Types.BIT);
320: istmt.setNull(22, java.sql.Types.INTEGER);
321: istmt.setNull(23, java.sql.Types.DOUBLE);
322: istmt.setNull(24, java.sql.Types.BIGINT);
323: istmt.setNull(25, java.sql.Types.TIMESTAMP);
324: istmt.setNull(26, java.sql.Types.LONGVARBINARY);
325: istmt.setInt(27, 1);
326:
327: assertEquals(1, istmt.executeUpdate());
328: istmt.close();
329: }
330:
331: public void testBigInt() throws Throwable {
332: // String crtab = "create table #testBigInt (a bigint)";
333: String crtab = "create table #testBigInt (a NUMERIC(19) NULL)";
334: dropTable("#testBigInt");
335: Statement stmt = con.createStatement();
336: stmt.executeUpdate(crtab);
337: stmt.close();
338: PreparedStatement pstmt = con
339: .prepareStatement("insert into #testBigInt values (?)");
340: pstmt.setNull(1, java.sql.Types.BIGINT);
341: assertTrue(!pstmt.execute());
342: assertTrue(pstmt.getUpdateCount() == 1);
343: pstmt.setLong(1, 99999999999L);
344: assertTrue(!pstmt.execute());
345: assertTrue(pstmt.getUpdateCount() == 1);
346: pstmt.setLong(1, -99999999999L);
347: assertTrue(!pstmt.execute());
348: assertTrue(pstmt.getUpdateCount() == 1);
349: pstmt.setLong(1, 9999999999999L);
350: assertTrue(!pstmt.execute());
351: assertTrue(pstmt.getUpdateCount() == 1);
352: pstmt.setLong(1, -9999999999999L);
353: assertTrue(!pstmt.execute());
354: assertTrue(pstmt.getUpdateCount() == 1);
355: pstmt.setLong(1, 99999999999L);
356: assertTrue(!pstmt.execute());
357: assertTrue(pstmt.getUpdateCount() == 1);
358: pstmt.close();
359: }
360:
361: public void testBoolean() throws Throwable {
362: // String crtab = "create table #testBigInt (a bigint)";
363: String crtab = "create table #testBit (a BIT NULL)";
364: dropTable("#testBit");
365: Statement stmt = con.createStatement();
366: stmt.executeUpdate(crtab);
367: stmt.executeUpdate("insert into #testBit values (NULL)");
368: stmt.executeUpdate("insert into #testBit values (0)");
369: stmt.executeUpdate("insert into #testBit values (1)");
370: ResultSet rs = stmt
371: .executeQuery("select * from #testBit where a is NULL");
372: rs.next();
373: rs.getBoolean(1);
374: rs = stmt.executeQuery("select * from #testBit where a = 0");
375: rs.next();
376: rs.getBoolean(1);
377: rs = stmt.executeQuery("select * from #testBit where a = 1");
378: rs.next();
379: rs.getBoolean(1);
380: stmt.close();
381: PreparedStatement pstmt = con
382: .prepareStatement("insert into #testBit values (?)");
383: pstmt.setBoolean(1, true);
384: assertTrue(!pstmt.execute());
385: assertTrue(pstmt.getUpdateCount() == 1);
386: pstmt.setBoolean(1, false);
387: assertTrue(!pstmt.execute());
388: assertTrue(pstmt.getUpdateCount() == 1);
389: pstmt.setNull(1, java.sql.Types.BIT);
390: assertTrue(!pstmt.execute());
391: assertTrue(pstmt.getUpdateCount() == 1);
392: pstmt.close();
393: }
394:
395: public void testBinary() throws Throwable {
396: String crtab = "create table #testBinary (a varbinary(8000))";
397: dropTable("#testBinary");
398: byte[] ba = new byte[8000];
399: for (int i = 0; i < ba.length; i++) {
400: ba[i] = (byte) (i % 256);
401: }
402: Statement stmt = con.createStatement();
403: stmt.executeUpdate(crtab);
404: stmt.close();
405: PreparedStatement pstmt = con
406: .prepareStatement("insert into #testBinary values (?)");
407: pstmt.setObject(1, ba);
408: pstmt.execute();
409: pstmt.close();
410: }
411:
412: private void checkTime(long time) throws Throwable {
413: PreparedStatement pstmt = con
414: .prepareStatement("insert into #testTimestamp values (?)");
415: java.sql.Timestamp ts = new java.sql.Timestamp(time);
416: pstmt.setTimestamp(1, ts);
417: pstmt.executeUpdate();
418: pstmt.close();
419: Statement stmt = con.createStatement();
420: ResultSet rs = stmt
421: .executeQuery("select * from #testTimestamp");
422: rs.next();
423: java.sql.Timestamp tsres = rs.getTimestamp(1);
424: assertTrue(ts.equals(tsres));
425: stmt.executeUpdate("truncate table #testTimestamp");
426: stmt.close();
427: }
428:
429: public void testSpecTime() throws Throwable {
430: String crtab = "create table #testTimestamp (a datetime)";
431: dropTable("#testTimestamp");
432: Statement stmt = con.createStatement();
433: stmt.executeUpdate(crtab);
434: stmt.close();
435: checkTime(92001000);
436: checkTime(4200000); // sent in 4 Bytes
437: checkTime(4201000);
438: checkTime(1234567000);
439: checkTime(420000000000L); // sent in 4 Bytes
440: checkTime(840000000000L);
441: }
442:
443: public void testBigDecimal() throws Throwable {
444: String crtab = "create table #testBigDecimal (a decimal(28,10) NULL)";
445: dropTable("#testBigDecimal");
446: Statement stmt = con.createStatement();
447: stmt.executeUpdate(crtab);
448: stmt.close();
449: PreparedStatement pstmt = con
450: .prepareStatement("insert into #testBigDecimal values (?)");
451: pstmt.setObject(1, new BigDecimal("10.200"));
452: pstmt.execute();
453: // FIXME With Sybase this should probably throw a DataTruncation, not just a plain SQLException
454: pstmt.setObject(1, new BigDecimal(10.200));
455: pstmt.execute();
456: pstmt.setObject(1, null);
457: pstmt.execute();
458: pstmt.setObject(1, new Integer(20));
459: pstmt.execute();
460: pstmt.setObject(1, new Double(2.10));
461: pstmt.execute();
462: pstmt.setObject(1, new BigDecimal(-10.200));
463: pstmt.execute();
464: pstmt.setObject(1, new Long(200));
465: pstmt.execute();
466: pstmt.setByte(1, (byte) 1);
467: pstmt.execute();
468: pstmt.setInt(1, 200);
469: pstmt.execute();
470: pstmt.setLong(1, 200L);
471: pstmt.execute();
472: pstmt.setFloat(1, (float) 1.1);
473: pstmt.execute();
474: pstmt.setDouble(1, 1.1);
475: pstmt.execute();
476: pstmt.close();
477: }
478: }
|