001: /*
002: * $Id: TestDatatypes.java,v 1.2 2005/10/13 02:25:32 ahimanikya Exp $
003: * =======================================================================
004: * Copyright (c) 2005 Axion Development Team. All rights reserved.
005: *
006: * Redistribution and use in source and binary forms, with or without
007: * modification, are permitted provided that the following conditions
008: * are met:
009: *
010: * 1. Redistributions of source code must retain the above
011: * copyright notice, this list of conditions and the following
012: * disclaimer.
013: *
014: * 2. Redistributions in binary form must reproduce the above copyright
015: * notice, this list of conditions and the following disclaimer in
016: * the documentation and/or other materials provided with the
017: * distribution.
018: *
019: * 3. The names "Tigris", "Axion", nor the names of its contributors may
020: * not be used to endorse or promote products derived from this
021: * software without specific prior written permission.
022: *
023: * 4. Products derived from this software may not be called "Axion", nor
024: * may "Tigris" or "Axion" appear in their names without specific prior
025: * written permission.
026: *
027: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
028: * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
029: * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
030: * PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
031: * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
032: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
033: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
034: * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
035: * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
036: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
037: * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
038: * =======================================================================
039: */
040: package org.axiondb.functional;
041:
042: import java.io.File;
043: import java.io.FileWriter;
044: import java.math.BigDecimal;
045: import java.sql.ResultSet;
046: import java.sql.ResultSetMetaData;
047: import java.sql.SQLException;
048:
049: import junit.framework.Test;
050: import junit.framework.TestSuite;
051:
052: import org.axiondb.types.BigDecimalType;
053:
054: /**
055: *
056: * @author Jonathan Giron
057: * @version $Revision: 1.2 $
058: */
059: public class TestDatatypes extends AbstractFunctionalTest {
060:
061: public TestDatatypes(String testName) {
062: super (testName);
063: }
064:
065: public static Test suite() {
066: return new TestSuite(TestDatatypes.class);
067: }
068:
069: public void testCustomDataType() throws Exception {
070: final BigDecimal first = new BigDecimal("12.56");
071: final BigDecimal second = new BigDecimal("1.2");
072:
073: _rset = _stmt
074: .executeQuery("select 1 as col1, cast('12.56' as NUMERIC(4,2)) as col2, cast('1.23' as NUMERIC(3,1)) as col3");
075: assertTrue(_rset.next());
076:
077: BigDecimal result = (BigDecimal) _rset.getObject(2);
078: assertEquals(first, result);
079:
080: result = (BigDecimal) _rset.getObject(3);
081: assertEquals(second, result);
082: }
083:
084: public void testNumericWithDefaultScale() throws Exception {
085: _stmt
086: .executeUpdate("create table num_def_test (myvalue numeric(10))");
087: _stmt.executeUpdate("insert into num_def_test values (10.40)");
088:
089: _rset = _stmt.executeQuery("select myvalue from num_def_test");
090: assertTrue(_rset.next());
091: assertEquals(new BigDecimal("10"), _rset.getObject(1));
092:
093: }
094:
095: public void testNumericWithZeroScale() throws Exception {
096: final int scale = 0;
097: final String originalValueStr = "10.41";
098:
099: _stmt
100: .executeUpdate("create table num_zero_test (myvalue numeric(10, "
101: + scale + "))");
102: _stmt.executeUpdate("insert into num_zero_test values ("
103: + originalValueStr + ")");
104:
105: _rset = _stmt.executeQuery("select myvalue from num_zero_test");
106: assertTrue(_rset.next());
107: assertEquals(new BigDecimal(originalValueStr).setScale(scale,
108: BigDecimalType.ROUNDING_RULE), _rset.getObject(1));
109:
110: final String addValueStr = "10.4444";
111: _rset = _stmt.executeQuery("select myvalue + " + addValueStr
112: + " from num_zero_test");
113: assertTrue(_rset.next());
114: assertEquals(new BigDecimal("20.4444"), _rset.getObject(1));
115:
116: _stmt
117: .executeUpdate("update num_zero_test set myvalue = myvalue + "
118: + addValueStr);
119: _rset = _stmt.executeQuery("select myvalue from num_zero_test");
120: assertTrue(_rset.next());
121: assertEquals(new BigDecimal("20"), _rset.getObject(1));
122: }
123:
124: public void testNumericWithNonzeroScale() throws Exception {
125: final int scale = 3;
126: final BigDecimal[] values = new BigDecimal[] {
127: new BigDecimal("10"), new BigDecimal("10.1"),
128: new BigDecimal("10.22"), new BigDecimal("10.333"),
129: new BigDecimal("10.4444"), new BigDecimal("11") };
130: final String addValueStr = "10.4444";
131:
132: BigDecimal[] scaledValues = new BigDecimal[values.length];
133: BigDecimal[] addValues = new BigDecimal[values.length];
134: BigDecimal[] updateValues = new BigDecimal[values.length];
135:
136: _stmt
137: .executeUpdate("create table num_nonzero_test (myvalue numeric(10, "
138: + scale + "))");
139: for (int i = 0; i < values.length; i++) {
140: scaledValues[i] = values[i].setScale(scale,
141: BigDecimalType.ROUNDING_RULE);
142: addValues[i] = scaledValues[i].add(new BigDecimal(
143: addValueStr));
144: updateValues[i] = addValues[i].setScale(scale,
145: BigDecimalType.ROUNDING_RULE);
146: assertEquals(
147: 1,
148: _stmt
149: .executeUpdate("insert into num_nonzero_test values ("
150: + values[i].toString() + ")"));
151: }
152:
153: _rset = _stmt
154: .executeQuery("select myvalue from num_nonzero_test");
155: for (int i = 0; i < scaledValues.length; i++) {
156: assertTrue(_rset.next());
157: assertEquals(scaledValues[i], _rset.getObject(1));
158: }
159:
160: _rset = _stmt.executeQuery("select myvalue + " + addValueStr
161: + " from num_nonzero_test");
162: for (int i = 0; i < addValues.length; i++) {
163: assertTrue(_rset.next());
164: assertEquals(addValues[i], _rset.getObject(1));
165: }
166:
167: _stmt
168: .executeUpdate("update num_nonzero_test set myvalue = myvalue + "
169: + addValueStr);
170: _rset = _stmt
171: .executeQuery("select myvalue from num_nonzero_test");
172: for (int i = 0; i < updateValues.length; i++) {
173: assertTrue(_rset.next());
174: assertEquals(updateValues[i], _rset.getObject(1));
175: }
176:
177: }
178:
179: public void testNumericValueAddition() throws Exception {
180: final BigDecimal first = new BigDecimal("2.4444");
181: final BigDecimal second = new BigDecimal("1.05");
182:
183: _rset = _stmt.executeQuery("select " + first + " + " + second);
184: assertTrue(_rset.next());
185:
186: BigDecimal result = (BigDecimal) _rset.getObject(1);
187: assertEquals(first.add(second), result);
188:
189: assertEquals(Math.max(first.scale(), second.scale()), result
190: .scale());
191:
192: }
193:
194: public void testNumericValueSubtraction() throws Exception {
195: final BigDecimal first = new BigDecimal("2.4444");
196: final BigDecimal second = new BigDecimal("1.05");
197:
198: _rset = _stmt.executeQuery("select " + first + " - " + second);
199: assertTrue(_rset.next());
200:
201: BigDecimal result = (BigDecimal) _rset.getObject(1);
202: assertEquals(first.subtract(second), result);
203:
204: assertEquals(Math.max(first.scale(), second.scale()), result
205: .scale());
206: }
207:
208: public void testNumericValueMultiplication() throws Exception {
209: final BigDecimal first = new BigDecimal("2.4444");
210: final BigDecimal second = new BigDecimal("1.05");
211:
212: // Multiplication
213: _rset = _stmt.executeQuery("select " + first + " * " + second);
214: assertTrue(_rset.next());
215:
216: BigDecimal result = (BigDecimal) _rset.getObject(1);
217: assertEquals(first.multiply(second), result);
218:
219: assertEquals(first.scale() + second.scale(), result.scale());
220: }
221:
222: public void testNumericValueDivision() throws Exception {
223: final BigDecimal first = new BigDecimal("2.4444");
224: final BigDecimal second = new BigDecimal("1.05");
225:
226: _rset = _stmt.executeQuery("select " + first + " / " + second);
227: assertTrue(_rset.next());
228:
229: BigDecimal result = (BigDecimal) _rset.getObject(1);
230: assertEquals(
231: first.divide(second, BigDecimalType.ROUNDING_RULE),
232: result);
233:
234: assertEquals(first.scale(), result.scale());
235: }
236:
237: public void testNumericChainedArithmetic() throws Exception {
238: final BigDecimal first = new BigDecimal("2.4444");
239: final BigDecimal second = new BigDecimal("1.05");
240: final BigDecimal third = new BigDecimal("3.1234567");
241: final BigDecimal fourth = new BigDecimal("2.0");
242:
243: _rset = _stmt.executeQuery("select " + first + " + " + second
244: + " - " + third + " - " + fourth);
245: assertTrue(_rset.next());
246:
247: BigDecimal result = (BigDecimal) _rset.getObject(1);
248: assertEquals(
249: first.add(second).subtract(third).subtract(fourth),
250: result);
251: assertEquals(Math.max(Math.max(Math.max(first.scale(), second
252: .scale()), third.scale()), fourth.scale()), result
253: .scale());
254:
255: _rset = _stmt.executeQuery("select " + first + " * " + second
256: + " * " + third);
257: assertTrue(_rset.next());
258:
259: result = (BigDecimal) _rset.getObject(1);
260: assertEquals(first.multiply(second).multiply(third), result);
261: assertEquals(first.scale() + second.scale() + third.scale(),
262: result.scale());
263:
264: _rset = _stmt.executeQuery("select " + first + " * " + second
265: + " - " + third + " * " + fourth);
266: assertTrue(_rset.next());
267:
268: result = (BigDecimal) _rset.getObject(1);
269: assertEquals(first.multiply(second).subtract(
270: third.multiply(fourth)), result);
271: assertEquals(Math.max(first.scale() + second.scale(), third
272: .scale()
273: + fourth.scale()), result.scale());
274: }
275:
276: public void testNegativeNullArithmetic() throws Exception {
277: _rset = _stmt.executeQuery("select null + 1.05");
278: assertTrue(_rset.next());
279: assertEquals(null, _rset.getBigDecimal(1));
280: assertEquals(null, _rset.getObject(1));
281: assertTrue(_rset.wasNull());
282:
283: _rset = _stmt.executeQuery("select 1.05 + null");
284: assertTrue(_rset.next());
285: assertEquals(null, _rset.getBigDecimal(1));
286: assertEquals(null, _rset.getObject(1));
287: assertTrue(_rset.wasNull());
288:
289: _rset = _stmt.executeQuery("select null + null");
290: assertTrue(_rset.next());
291: assertEquals(null, _rset.getBigDecimal(1));
292: assertEquals(null, _rset.getObject(1));
293: assertTrue(_rset.wasNull());
294:
295: _rset = _stmt.executeQuery("select null - 1.05");
296: assertTrue(_rset.next());
297: assertEquals(null, _rset.getBigDecimal(1));
298: assertEquals(null, _rset.getObject(1));
299: assertTrue(_rset.wasNull());
300:
301: _rset = _stmt.executeQuery("select 1.05 - null");
302: assertTrue(_rset.next());
303: assertEquals(null, _rset.getBigDecimal(1));
304: assertEquals(null, _rset.getObject(1));
305: assertTrue(_rset.wasNull());
306:
307: _rset = _stmt.executeQuery("select null - null");
308: assertTrue(_rset.next());
309: assertEquals(null, _rset.getBigDecimal(1));
310: assertEquals(null, _rset.getObject(1));
311: assertTrue(_rset.wasNull());
312:
313: _rset = _stmt.executeQuery("select null * 1.05");
314: assertTrue(_rset.next());
315: assertEquals(null, _rset.getBigDecimal(1));
316: assertEquals(null, _rset.getObject(1));
317: assertTrue(_rset.wasNull());
318:
319: _rset = _stmt.executeQuery("select 1.05 * null");
320: assertTrue(_rset.next());
321: assertEquals(null, _rset.getBigDecimal(1));
322: assertEquals(null, _rset.getObject(1));
323: assertTrue(_rset.wasNull());
324:
325: _rset = _stmt.executeQuery("select null * null");
326: assertTrue(_rset.next());
327: assertEquals(null, _rset.getBigDecimal(1));
328: assertEquals(null, _rset.getObject(1));
329: assertTrue(_rset.wasNull());
330:
331: _rset = _stmt.executeQuery("select null / 1.05");
332: assertTrue(_rset.next());
333: assertEquals(null, _rset.getBigDecimal(1));
334: assertEquals(null, _rset.getObject(1));
335: assertTrue(_rset.wasNull());
336:
337: _rset = _stmt.executeQuery("select 1.05 / null");
338: assertTrue(_rset.next());
339: assertEquals(null, _rset.getBigDecimal(1));
340: assertEquals(null, _rset.getObject(1));
341: assertTrue(_rset.wasNull());
342:
343: _rset = _stmt.executeQuery("select null / null");
344: assertTrue(_rset.next());
345: assertEquals(null, _rset.getBigDecimal(1));
346: assertEquals(null, _rset.getObject(1));
347: assertTrue(_rset.wasNull());
348: }
349:
350: public void testPrecisionOverflow() throws Exception {
351: _stmt.executeUpdate("create table smallval (id numeric(4, 2))");
352:
353: assertEquals(1, _stmt
354: .executeUpdate("insert into smallval values (0.01)"));
355: assertEquals(1, _stmt
356: .executeUpdate("insert into smallval values (0.1)"));
357: assertEquals(1, _stmt
358: .executeUpdate("insert into smallval values (1)"));
359: assertEquals(1, _stmt
360: .executeUpdate("insert into smallval values (10)"));
361:
362: try {
363: _stmt.executeUpdate("insert into smallval values (100)");
364: fail("Expected SQLException - inserted value exceeds specified precision.");
365: } catch (SQLException expected) {
366: // Expected
367: // TODO: When BigDecimalType throws SQLException(22003), test expected for that sqlstate code.
368: }
369:
370: _stmt.executeUpdate("create table fractions (id numeric(2,2))");
371: assertEquals(1, _stmt
372: .executeUpdate("insert into fractions values (0)"));
373: assertEquals(1, _stmt
374: .executeUpdate("insert into fractions values (0.10)"));
375: assertEquals(
376: 1,
377: _stmt
378: .executeUpdate("insert into fractions values (0.512345)"));
379: assertEquals(1, _stmt
380: .executeUpdate("insert into fractions values (0.99)"));
381: assertEquals(1, _stmt
382: .executeUpdate("insert into fractions values (0.994)"));
383:
384: try {
385: assertEquals(
386: 1,
387: _stmt
388: .executeUpdate("insert into fractions values (0.995)"));
389: fail("Expected SQLException - inserted value exceeds specified precision.");
390: } catch (SQLException expected) {
391: // Expected
392: assertEquals(
393: "Expected SQLException 22003 - inserted value exceeds specified precision",
394: "22003", expected.getSQLState());
395: }
396:
397: try {
398: assertEquals(
399: 1,
400: _stmt
401: .executeUpdate("insert into fractions values (0.99 + 0.005)"));
402: fail("Expected SQLException - inserted value exceeds specified precision.");
403: } catch (SQLException expected) {
404: // Expected
405: assertEquals(
406: "Expected SQLException 22003 - inserted value exceeds specified precision",
407: "22003", expected.getSQLState());
408: }
409:
410: try {
411: assertEquals(
412: 1,
413: _stmt
414: .executeUpdate("insert into fractions values (1.0)"));
415: fail("Expected SQLException - inserted value exceeds specified precision.");
416: } catch (SQLException expected) {
417: // Expected
418: assertEquals(
419: "Expected SQLException 22003 - inserted value exceeds specified precision",
420: "22003", expected.getSQLState());
421: }
422:
423: try {
424: assertEquals(1, _stmt
425: .executeUpdate("insert into fractions values (1)"));
426: fail("Expected SQLException - inserted value exceeds specified precision.");
427: } catch (SQLException expected) {
428: // Expected
429: assertEquals(
430: "Expected SQLException 22003 - inserted value exceeds specified precision",
431: "22003", expected.getSQLState());
432: }
433:
434: _stmt.executeUpdate("create table intonly (id numeric(2))");
435:
436: assertEquals(1, _stmt
437: .executeUpdate("insert into intonly values (1)"));
438: assertEquals(1, _stmt
439: .executeUpdate("insert into intonly values (10)"));
440: assertEquals(1, _stmt
441: .executeUpdate("insert into intonly values (20.1)"));
442:
443: try {
444: assertEquals(1, _stmt
445: .executeUpdate("insert into intonly values (100)"));
446: fail("Expected SQLException - inserted value exceeds specified precision.");
447: } catch (SQLException expected) {
448: // Expected
449: assertEquals(
450: "Expected SQLException 22003 - inserted value exceeds specified precision",
451: "22003", expected.getSQLState());
452: }
453:
454: _rset = _stmt.executeQuery("select * from intonly");
455:
456: assertTrue(_rset.next());
457: assertEquals("1", _rset.getString(1));
458: assertTrue(_rset.next());
459: assertEquals("10", _rset.getString(1));
460: assertTrue(_rset.next());
461: assertEquals("20", _rset.getString(1));
462:
463: assertFalse(_rset.next());
464: }
465:
466: public void testNegativeVarcharInvalidSize() {
467: try {
468: _stmt
469: .executeUpdate("create table error (myvalue varchar(a))");
470: fail("Expected SQLException - value must be a non-negative integer.");
471: } catch (SQLException expected) {
472: // Expected
473: }
474:
475: try {
476: _stmt
477: .executeUpdate("create table error (myvalue numeric(-1))");
478: fail("Expected SQLException - value must be a positive integer.");
479: } catch (SQLException expected) {
480: // Expected
481: }
482: }
483:
484: public void testNegativeNumericInvalidPrecisionScale() {
485: try {
486: _stmt
487: .executeUpdate("create table error (myvalue numeric(a))");
488: fail("Expected SQLException - precision value must be a positive integer.");
489: } catch (SQLException expected) {
490: // Expected
491: }
492:
493: try {
494: _stmt
495: .executeUpdate("create table error (myvalue numeric(-1))");
496: fail("Expected SQLException - precision value must be a positive integer.");
497: } catch (SQLException expected) {
498: // Expected
499: }
500:
501: try {
502: _stmt
503: .executeUpdate("create table error (myvalue numeric(0))");
504: fail("Expected SQLException - precision value must be a positive integer.");
505: } catch (SQLException expected) {
506: // Expected
507: }
508:
509: try {
510: _stmt
511: .executeUpdate("create table error (myvalue numeric(5, zzz))");
512: fail("Expected SQLException - scale value must be a non-negative integer.");
513: } catch (SQLException expected) {
514: // Expected
515: }
516:
517: try {
518: _stmt
519: .executeUpdate("create table error (myvalue numeric(5, -1))");
520: fail("Expected SQLException - scale value must be a non-negative integer.");
521: } catch (SQLException expected) {
522: // Expected
523: }
524: }
525:
526: public void testVarcharType() throws Exception {
527: StringBuffer buf = new StringBuffer();
528: for (int i = 0; i < 255; i++) {
529: buf.append(i % 10);
530: }
531:
532: final String str_5 = "12345";
533: final String str_10 = "1234567890";
534: final String str_20 = "12345678901234567890";
535: final String str_20_pad = "12345678901234567890 ";
536: final String str_255 = buf.toString();
537:
538: _stmt
539: .executeUpdate("create table vc_test (vc_20 varchar(20), vc_255 varchar(255))");
540: ResultSet dbmdRs = _conn.getMetaData().getColumns(null, null,
541: "vc_test", "vc_20");
542: assertTrue(dbmdRs.next());
543: assertEquals(20, dbmdRs.getInt("COLUMN_SIZE"));
544:
545: dbmdRs = _conn.getMetaData().getColumns(null, null, "vc_test",
546: "vc_255");
547: assertTrue(dbmdRs.next());
548: assertEquals(255, dbmdRs.getInt("COLUMN_SIZE"));
549:
550: assertEquals(1, _stmt
551: .executeUpdate("insert into vc_test values ('" + str_20
552: + "', '" + str_255 + "')"));
553:
554: assertEquals(1, _stmt
555: .executeUpdate("insert into vc_test values (concat('"
556: + str_5 + "', '" + str_10 + "'), '" + str_255
557: + "')"));
558:
559: assertEquals(1, _stmt
560: .executeUpdate("insert into vc_test values ('"
561: + str_20_pad + "', '" + str_255 + "')"));
562:
563: _rset = _stmt.executeQuery("select vc_20, vc_255 from vc_test");
564:
565: ResultSetMetaData rsmd = _rset.getMetaData();
566: assertEquals(20, rsmd.getPrecision(1));
567: assertEquals(255, rsmd.getPrecision(2));
568:
569: assertTrue(_rset.next());
570: assertEquals(str_20, _rset.getObject(1));
571: assertEquals(str_255, _rset.getObject(2));
572:
573: assertTrue(_rset.next());
574: assertEquals(str_5 + str_10, _rset.getObject(1));
575: assertEquals(str_255, _rset.getObject(2));
576:
577: assertTrue(_rset.next());
578: assertEquals(str_20, _rset.getObject(1));
579: assertEquals(str_255, _rset.getObject(2));
580:
581: assertFalse(_rset.next());
582:
583: _rset = _stmt
584: .executeQuery("select length(vc_20), length(vc_255) from vc_test");
585:
586: assertTrue(_rset.next());
587: assertEquals(20, _rset.getInt(1));
588: assertEquals(255, _rset.getInt(2));
589:
590: assertTrue(_rset.next());
591: assertEquals(15, _rset.getInt(1));
592: assertEquals(255, _rset.getInt(2));
593:
594: assertTrue(_rset.next());
595: assertEquals(20, _rset.getInt(1));
596: assertEquals(255, _rset.getInt(2));
597:
598: assertFalse(_rset.next());
599: }
600:
601: public void testNegativeVarcharOverflow() throws Exception {
602: StringBuffer buf = new StringBuffer();
603: for (int i = 0; i < 255; i++) {
604: buf.append(i % 10);
605: }
606:
607: final String str_10 = "1234567890";
608: final String str_20 = "12345678901234567890";
609: final String str_255 = buf.toString();
610: final String overFlowStr = buf.append("_").toString();
611:
612: _stmt
613: .executeUpdate("create table vc_test (vc_20 varchar(20), vc_255 varchar(255))");
614:
615: try {
616: _stmt
617: .executeUpdate("insert into vc_test values ('12345678901234567890', '"
618: + overFlowStr + "')");
619: fail("Expected SQLException with SQLSTATE 22001 - string data, right truncation.");
620: } catch (SQLException expected) {
621: assertEquals(
622: "Expected SQLException with SQLSTATE 22001 - string data, right truncation.",
623: "22001", expected.getSQLState());
624: }
625:
626: assertEquals(1, _stmt
627: .executeUpdate("insert into vc_test values ('" + str_20
628: + "', '" + str_255 + "')"));
629: try {
630: assertEquals(
631: 1,
632: _stmt
633: .executeUpdate("insert into vc_test values (concat('"
634: + str_20
635: + "', '"
636: + str_10
637: + "'), '" + str_255 + "')"));
638: fail("Expected SQLException with SQLSTATE 22001 - string data, right truncation.");
639: } catch (SQLException expected) {
640: assertEquals(
641: "Expected SQLException with SQLSTATE 22001 - string data, right truncation.",
642: "22001", expected.getSQLState());
643: }
644:
645: try {
646: assertEquals(
647: 1,
648: _stmt
649: .executeUpdate("update vc_test set vc_20 = concat(vc_20, '"
650: + str_20 + "')"));
651: fail("Expected SQLException with SQLSTATE 22001 - string data, right truncation.");
652: } catch (SQLException expected) {
653: assertEquals(
654: "Expected SQLException with SQLSTATE 22001 - string data, right truncation.",
655: "22001", expected.getSQLState());
656: }
657: }
658:
659: public void testCharType() throws Exception {
660: final String str_5 = "12345";
661: final String str_10 = "1234567890";
662:
663: _stmt
664: .executeUpdate("create table char_test (char_10 char(10))");
665:
666: ResultSet dbmdRs = _conn.getMetaData().getColumns(null, null,
667: "char_test", "char_10");
668: assertTrue(dbmdRs.next());
669: assertEquals(10, dbmdRs.getInt("COLUMN_SIZE"));
670:
671: assertEquals(1, _stmt
672: .executeUpdate("insert into char_test values ('"
673: + str_5 + "')"));
674: assertEquals(1, _stmt
675: .executeUpdate("insert into char_test values ('"
676: + str_10 + "')"));
677: assertEquals(1, _stmt
678: .executeUpdate("insert into char_test values (concat('"
679: + str_5 + "', '" + str_5 + "'))"));
680:
681: _rset = _stmt.executeQuery("select char_10 from char_test");
682:
683: ResultSetMetaData rsmd = _rset.getMetaData();
684: assertEquals(10, rsmd.getPrecision(1));
685:
686: assertTrue(_rset.next());
687: assertEquals(padValue(str_5, 10, ' '), _rset.getObject(1));
688:
689: assertTrue(_rset.next());
690: assertEquals(padValue(str_10, 10, ' '), _rset.getObject(1));
691:
692: assertTrue(_rset.next());
693: assertEquals(padValue(str_5 + str_5, 10, ' '), _rset
694: .getObject(1));
695:
696: assertFalse(_rset.next());
697: _rset.close();
698:
699: _rset = _stmt
700: .executeQuery("select length(char_10) from char_test");
701:
702: assertTrue(_rset.next());
703: assertEquals(10, _rset.getInt(1));
704:
705: assertTrue(_rset.next());
706: assertEquals(10, _rset.getInt(1));
707:
708: assertTrue(_rset.next());
709: assertEquals(10, _rset.getInt(1));
710:
711: assertFalse(_rset.next());
712: }
713:
714: public void testNegativeCharOverflow() throws Exception {
715: final String str_10 = "1234567890";
716: final String str_20 = "12345678901234567890";
717:
718: _stmt
719: .executeUpdate("create table char_test (char_10 char(10))");
720: try {
721: _stmt.executeUpdate("insert into char_test values ('"
722: + str_20 + "')");
723: fail("Expected SQLException with SQLSTATE 22001 - string data, right truncation.");
724: } catch (SQLException expected) {
725: assertEquals(
726: "Expected SQLException with SQLSTATE 22001 - string data, right truncation.",
727: "22001", expected.getSQLState());
728: }
729:
730: try {
731: assertEquals(
732: 1,
733: _stmt
734: .executeUpdate("insert into char_test values (concat('"
735: + str_10 + "', '" + str_10 + "'))"));
736: fail("Expected SQLException with SQLSTATE 22001 - string data, right truncation.");
737: } catch (SQLException expected) {
738: assertEquals(
739: "Expected SQLException with SQLSTATE 22001 - string data, right truncation.",
740: "22001", expected.getSQLState());
741: }
742:
743: assertEquals(1, _stmt
744: .executeUpdate("insert into char_test values ('"
745: + str_10 + "')"));
746: try {
747: _stmt.executeUpdate("update char_test set char_10 = '"
748: + str_20 + "'");
749: fail("Expected SQLException with SQLSTATE 22001 - string data, right truncation.");
750: } catch (SQLException expected) {
751: assertEquals(
752: "Expected SQLException with SQLSTATE 22001 - string data, right truncation.",
753: "22001", expected.getSQLState());
754: }
755: }
756:
757: public void testDataTypes_DelimitedFlatfile() throws Exception {
758: final String eol = "\n";
759:
760: File data = new File(".", "chartest_delim.csv");
761: FileWriter out = new FileWriter(data);
762: out.write("ID,NAME,CNTDOWN,VAL" + eol); // Header
763: out.write("1,a ,1234567890,1.0" + eol); // 1
764: out.write("2,bb ,123456789 ,2.0" + eol); // 2
765: out.write("3,ccc ,12345678 ,3.0" + eol); // 3
766: out.write("4,dddd ,1234567 ,4.0" + eol); // 4
767: out.write("5,eeeee ,123456 ,5.0" + eol); // 5
768: out.write("6,ffffff ,12345 ,6.0" + eol); // 6
769: out.write("7,ggggggg ,1234 ,7.0" + eol); // 7
770: out.write("8,hhhhhhhh ,123 ,8.0" + eol); // 8
771: out.write("9,iiiiiiiii ,12 ,9.0" + eol); // 9
772: out.write("10,jjjjjjjjjj,1 ,10.0" + eol); // 10
773: out.write("11,invalid row,invalid row,11.0" + eol); // 11 - invalid (char fields too long)
774: out.write("12, , ," + eol); // 12
775: out.close();
776:
777: _stmt
778: .executeUpdate("create external table chartest_delim ( id numeric(2), name char(10), "
779: + "cntdown char(10), val numeric(3,1)) organization (loadtype='delimited' "
780: + "filename='./chartest_delim.csv' isfirstlineheader='true' "
781: + "recorddelimiter='\\n')");
782:
783: try {
784: _rset = _stmt
785: .executeQuery("select count(id) from chartest_delim");
786: assertTrue(_rset.next());
787: assertEquals(11, _rset.getInt(1));
788: _rset.close();
789:
790: final String cntdownTemplate = "1234567890";
791: _rset = _stmt
792: .executeQuery("select id, name, cntdown, val from chartest_delim");
793: for (int i = 0; i < 10; i++) {
794: StringBuffer namebuf = new StringBuffer(10);
795: for (int j = 0; j < (i + 1) % 11; j++) {
796: String charVal = String.valueOf((char) ('a' + i));
797: namebuf.append(charVal);
798: }
799:
800: assertTrue(_rset.next());
801: assertEquals(new BigDecimal(i + 1), _rset
802: .getBigDecimal(1));
803: assertEquals(padValue(namebuf.toString(), 10, ' '),
804: _rset.getString(2));
805:
806: String expectedStr = padValue(cntdownTemplate
807: .substring(0, 10 - i), 10, ' ');
808: assertEquals(expectedStr, _rset.getString(3));
809:
810: BigDecimal expected = new BigDecimal(String
811: .valueOf(i + 1)
812: + ".0");
813: expected = expected.setScale(1);
814: assertEquals(expected, _rset.getBigDecimal(4));
815: }
816:
817: assertTrue(_rset.next());
818: assertEquals(new BigDecimal(12), _rset.getBigDecimal(1));
819: assertNull(_rset.getString(2));
820: assertTrue(_rset.wasNull());
821: assertNull(_rset.getString(3));
822: assertTrue(_rset.wasNull());
823: assertEquals(0, _rset.getInt(4));
824: assertTrue(_rset.wasNull());
825:
826: assertFalse(_rset.next());
827: _rset.close();
828:
829: _rset = _stmt
830: .executeQuery("select count(id) from chartest_delim");
831: assertTrue(_rset.next());
832: assertEquals(11, _rset.getInt(1));
833: _rset.close();
834: } finally {
835: try {
836: if (_rset != null) {
837: _rset.close();
838: }
839:
840: if (_stmt != null) {
841: _stmt.execute("drop table chartest_delim");
842: _stmt.close();
843: }
844: } catch (SQLException ignore) {
845: // ignore
846: }
847: data.delete();
848: }
849: }
850:
851: private String padValue(String raw, int fieldSize, char padChar) {
852: StringBuffer buf = new StringBuffer(raw);
853: for (int i = 0; i < fieldSize - raw.length(); i++) {
854: buf.append(padChar);
855: }
856: return buf.toString();
857: }
858: }
|