001: /*
002: * Created on Jun 21, 2005
003: *
004: * TODO To change the template for this generated file go to
005: * Window - Preferences - Java - Code Style - Code Templates
006: */
007: package org.postgresql.test.jdbc3;
008:
009: import java.math.BigDecimal;
010: import java.sql.CallableStatement;
011: import java.sql.Connection;
012: import java.sql.PreparedStatement;
013: import java.sql.ResultSet;
014: import java.sql.SQLException;
015: import java.sql.Statement;
016: import java.sql.Types;
017:
018: import org.postgresql.test.TestUtil;
019: import org.postgresql.util.PSQLState;
020:
021: import junit.framework.TestCase;
022:
023: /**
024: * @author davec
025: *
026: * TODO To change the template for this generated type comment go to
027: * Window - Preferences - Java - Code Style - Code Templates
028: */
029: public class Jdbc3CallableStatementTest extends TestCase {
030:
031: Connection con;
032:
033: /* (non-Javadoc)
034: * @see junit.framework.TestCase#setUp()
035: */
036: protected void setUp() throws Exception {
037: con = TestUtil.openDB();
038: Statement stmt = con.createStatement();
039: stmt
040: .execute("create temp table numeric_tab (MAX_VAL NUMERIC(30,15), MIN_VAL NUMERIC(30,15), NULL_VAL NUMERIC(30,15) NULL)");
041: stmt
042: .execute("insert into numeric_tab values ( 999999999999999,0.000000000000001, null)");
043: stmt
044: .execute("CREATE OR REPLACE FUNCTION myiofunc(a INOUT int, b OUT int) AS 'BEGIN b := a; a := 1; END;' LANGUAGE plpgsql");
045: stmt
046: .execute("CREATE OR REPLACE FUNCTION myif(a INOUT int, b IN int) AS 'BEGIN a := b; END;' LANGUAGE plpgsql");
047:
048: stmt
049: .execute("create or replace function "
050: + "Numeric_Proc( OUT IMAX NUMERIC(30,15), OUT IMIN NUMERIC(30,15), OUT INUL NUMERIC(30,15)) as "
051: + "'begin "
052: + "select max_val into imax from numeric_tab;"
053: + "select min_val into imin from numeric_tab;"
054: + "select null_val into inul from numeric_tab;"
055:
056: + " end;' " + "language 'plpgsql';");
057:
058: stmt.execute("CREATE OR REPLACE FUNCTION test_somein_someout("
059: + "pa IN int4," + "pb OUT varchar," + "pc OUT int8)"
060: + " AS "
061:
062: + "'begin " + "pb := ''out'';" + "pc := pa + 1;"
063: + "end;'"
064:
065: + "LANGUAGE 'plpgsql' VOLATILE;"
066:
067: );
068: stmt.execute("CREATE OR REPLACE FUNCTION test_allinout("
069: + "pa INOUT int4," + "pb INOUT varchar,"
070: + "pc INOUT int8)" + " AS " + "'begin "
071: + "pa := pa + 1;" + "pb := ''foo out'';"
072: + "pc := pa + 1;" + "end;'"
073: + "LANGUAGE 'plpgsql' VOLATILE;");
074:
075: }
076:
077: /* (non-Javadoc)
078: * @see junit.framework.TestCase#tearDown()
079: */
080: protected void tearDown() throws Exception {
081: Statement stmt = con.createStatement();
082: stmt
083: .execute("drop function Numeric_Proc(out decimal, out decimal, out decimal)");
084: stmt.execute("drop function test_somein_someout(int4)");
085: stmt
086: .execute("drop function test_allinout( inout int4, inout varchar, inout int8)");
087: stmt.execute("drop function myiofunc(a INOUT int, b OUT int) ");
088: stmt.execute("drop function myif(a INOUT int, b IN int)");
089: stmt.close();
090:
091: }
092:
093: public void testSomeInOut() throws Throwable {
094:
095: CallableStatement call = con
096: .prepareCall("{ call test_somein_someout(?,?,?) }");
097:
098: call.registerOutParameter(2, Types.VARCHAR);
099: call.registerOutParameter(3, Types.BIGINT);
100: call.setInt(1, 20);
101: call.execute();
102:
103: }
104:
105: public void testNotEnoughParameters() throws Throwable {
106:
107: CallableStatement cs = con.prepareCall("{call myiofunc(?,?)}");
108: cs.setInt(1, 2);
109: cs.registerOutParameter(2, Types.INTEGER);
110: try {
111: cs.execute();
112: fail("Should throw an exception ");
113: } catch (SQLException ex) {
114: assertTrue(ex.getSQLState().equalsIgnoreCase(
115: PSQLState.SYNTAX_ERROR.getState()));
116: }
117:
118: }
119:
120: public void testTooManyParameters() throws Throwable {
121:
122: CallableStatement cs = con.prepareCall("{call myif(?,?)}");
123: try {
124: cs.setInt(1, 1);
125: cs.setInt(2, 2);
126: cs.registerOutParameter(1, Types.INTEGER);
127: cs.registerOutParameter(2, Types.INTEGER);
128: cs.execute();
129: fail("should throw an exception");
130: } catch (SQLException ex) {
131: assertTrue(ex.getSQLState().equalsIgnoreCase(
132: PSQLState.SYNTAX_ERROR.getState()));
133: }
134:
135: }
136:
137: public void testAllInOut() throws Throwable {
138:
139: CallableStatement call = con
140: .prepareCall("{ call test_allinout(?,?,?) }");
141:
142: call.registerOutParameter(1, Types.INTEGER);
143: call.registerOutParameter(2, Types.VARCHAR);
144: call.registerOutParameter(3, Types.BIGINT);
145: call.setInt(1, 20);
146: call.setString(2, "hi");
147: call.setInt(3, 123);
148: call.execute();
149: call.getInt(1);
150: call.getString(2);
151: call.getLong(3);
152:
153: }
154:
155: public void testNumeric() throws Throwable {
156:
157: CallableStatement call = con
158: .prepareCall("{ call Numeric_Proc(?,?,?) }");
159:
160: call.registerOutParameter(1, Types.NUMERIC, 15);
161: call.registerOutParameter(2, Types.NUMERIC, 15);
162: call.registerOutParameter(3, Types.NUMERIC, 15);
163:
164: call.executeUpdate();
165: java.math.BigDecimal ret = call.getBigDecimal(1);
166: assertTrue(
167: "correct return from getNumeric () should be 999999999999999.000000000000000 but returned "
168: + ret.toString(), ret
169: .equals(new java.math.BigDecimal(
170: "999999999999999.000000000000000")));
171:
172: ret = call.getBigDecimal(2);
173: assertTrue("correct return from getNumeric ()", ret
174: .equals(new java.math.BigDecimal("0.000000000000001")));
175: try {
176: ret = call.getBigDecimal(3);
177: } catch (NullPointerException ex) {
178: assertTrue("This should be null", call.wasNull());
179: }
180:
181: }
182:
183: public void testGetObjectDecimal() throws Throwable {
184: try {
185: Statement stmt = con.createStatement();
186: stmt
187: .execute("create temp table decimal_tab ( max_val numeric(30,15), min_val numeric(30,15), nul_val numeric(30,15) )");
188: stmt
189: .execute("insert into decimal_tab values (999999999999999.000000000000000,0.000000000000001,null)");
190:
191: boolean ret = stmt
192: .execute("create or replace function "
193: + "decimal_proc( OUT pmax numeric, OUT pmin numeric, OUT nval numeric) as "
194: + "'begin "
195: + "select max_val into pmax from decimal_tab;"
196: + "select min_val into pmin from decimal_tab;"
197: + "select nul_val into nval from decimal_tab;"
198:
199: + " end;' " + "language 'plpgsql';");
200: } catch (Exception ex) {
201: fail(ex.getMessage());
202: throw ex;
203: }
204: try {
205: CallableStatement cstmt = con
206: .prepareCall("{ call decimal_proc(?,?,?) }");
207: cstmt.registerOutParameter(1, Types.DECIMAL);
208: cstmt.registerOutParameter(2, Types.DECIMAL);
209: cstmt.registerOutParameter(3, Types.DECIMAL);
210: cstmt.executeUpdate();
211: BigDecimal val = (BigDecimal) cstmt.getObject(1);
212: assertTrue(val.compareTo(new BigDecimal(
213: "999999999999999.000000000000000")) == 0);
214: val = (BigDecimal) cstmt.getObject(2);
215: assertTrue(val
216: .compareTo(new BigDecimal("0.000000000000001")) == 0);
217: val = (BigDecimal) cstmt.getObject(3);
218: assertTrue(val == null);
219: } catch (Exception ex) {
220: fail(ex.getMessage());
221: } finally {
222: try {
223: Statement dstmt = con.createStatement();
224: dstmt.execute("drop function decimal_proc()");
225: } catch (Exception ex) {
226: }
227: }
228: }
229:
230: public void testVarcharBool() throws Throwable {
231: try {
232: Statement stmt = con.createStatement();
233: stmt
234: .execute("create temp table vartab( max_val text, min_val text)");
235: stmt.execute("insert into vartab values ('a','b')");
236: boolean ret = stmt
237: .execute("create or replace function "
238: + "updatevarchar( in imax text, in imin text) returns int as "
239: + "'begin "
240: + "update vartab set max_val = imax;"
241: + "update vartab set min_val = imin;"
242: + "return 0;" + " end;' "
243: + "language 'plpgsql';");
244: stmt.close();
245: } catch (Exception ex) {
246: fail(ex.getMessage());
247: throw ex;
248: }
249: try {
250: String str = Boolean.TRUE.toString();
251: CallableStatement cstmt = con
252: .prepareCall("{ call updatevarchar(?,?) }");
253: cstmt.setObject(1, Boolean.TRUE, Types.VARCHAR);
254: cstmt.setObject(2, Boolean.FALSE, Types.VARCHAR);
255:
256: cstmt.executeUpdate();
257: cstmt.close();
258: ResultSet rs = con.createStatement().executeQuery(
259: "select * from vartab");
260: assertTrue(rs.next());
261: assertTrue(rs.getString(1).equals(Boolean.TRUE.toString()));
262:
263: assertTrue(rs.getString(2).equals(Boolean.FALSE.toString()));
264: rs.close();
265: } catch (Exception ex) {
266: fail(ex.getMessage());
267: } finally {
268: try {
269: Statement dstmt = con.createStatement();
270: dstmt.execute("drop function updatevarchar(text,text)");
271: } catch (Exception ex) {
272: }
273: }
274: }
275:
276: public void testInOut() throws Throwable {
277: try {
278: Statement stmt = con.createStatement();
279: stmt.execute(createBitTab);
280: stmt.execute(insertBitTab);
281: boolean ret = stmt
282: .execute("create or replace function "
283: + "insert_bit( inout IMAX boolean, inout IMIN boolean, inout INUL boolean) as "
284: + "'begin "
285: + "insert into bit_tab values( imax, imin, inul);"
286: + "select max_val into imax from bit_tab;"
287: + "select min_val into imin from bit_tab;"
288: + "select null_val into inul from bit_tab;"
289: + " end;' " + "language 'plpgsql';");
290: } catch (Exception ex) {
291: fail(ex.getMessage());
292: throw ex;
293: }
294: try {
295: CallableStatement cstmt = con
296: .prepareCall("{ call insert_bit(?,?,?) }");
297: cstmt.setObject(1, "true", Types.BIT);
298: cstmt.setObject(2, "false", Types.BIT);
299: cstmt.setNull(3, Types.BIT);
300: cstmt.registerOutParameter(1, Types.BIT);
301: cstmt.registerOutParameter(2, Types.BIT);
302: cstmt.registerOutParameter(3, Types.BIT);
303: cstmt.executeUpdate();
304:
305: assertTrue(cstmt.getBoolean(1) == true);
306: assertTrue(cstmt.getBoolean(2) == false);
307: cstmt.getBoolean(3);
308: assertTrue(cstmt.wasNull());
309: }
310:
311: finally {
312: try {
313: Statement dstmt = con.createStatement();
314: dstmt
315: .execute("drop function insert_bit(boolean, boolean, boolean)");
316: } catch (Exception ex) {
317: }
318: }
319: }
320:
321: private final String createBitTab = "create temp table bit_tab ( max_val boolean, min_val boolean, null_val boolean )";
322: private final String insertBitTab = "insert into bit_tab values (true,false,null)";
323:
324: public void testSetObjectBit() throws Throwable {
325: try {
326: Statement stmt = con.createStatement();
327: stmt.execute(createBitTab);
328: stmt.execute(insertBitTab);
329: boolean ret = stmt
330: .execute("create or replace function "
331: + "update_bit( in IMAX boolean, in IMIN boolean, in INUL boolean) returns int as "
332: + "'begin "
333: + "update bit_tab set max_val = imax;"
334: + "update bit_tab set min_val = imin;"
335: + "update bit_tab set min_val = inul;"
336: + " return 0;" + " end;' "
337: + "language 'plpgsql';");
338: } catch (Exception ex) {
339: fail(ex.getMessage());
340: throw ex;
341: }
342: try {
343: CallableStatement cstmt = con
344: .prepareCall("{ call update_bit(?,?,?) }");
345: cstmt.setObject(1, "true", Types.BIT);
346: cstmt.setObject(2, "false", Types.BIT);
347: cstmt.setNull(3, Types.BIT);
348: cstmt.executeUpdate();
349: cstmt.close();
350: ResultSet rs = con.createStatement().executeQuery(
351: "select * from bit_tab");
352:
353: assertTrue(rs.next());
354: assertTrue(rs.getBoolean(1) == true);
355: assertTrue(rs.getBoolean(2) == false);
356: rs.getBoolean(3);
357: assertTrue(rs.wasNull());
358: } catch (Exception ex) {
359: fail(ex.getMessage());
360: } finally {
361: try {
362: Statement dstmt = con.createStatement();
363: dstmt
364: .execute("drop function update_bit(boolean, boolean, boolean)");
365: } catch (Exception ex) {
366: }
367: }
368: }
369:
370: public void testGetObjectLongVarchar() throws Throwable {
371: try {
372: Statement stmt = con.createStatement();
373: stmt
374: .execute("create temp table longvarchar_tab ( t text, null_val text )");
375: stmt
376: .execute("insert into longvarchar_tab values ('testdata',null)");
377: boolean ret = stmt
378: .execute("create or replace function "
379: + "longvarchar_proc( OUT pcn text, OUT nval text) as "
380: + "'begin "
381: + "select t into pcn from longvarchar_tab;"
382: + "select null_val into nval from longvarchar_tab;"
383:
384: + " end;' " + "language 'plpgsql';");
385:
386: ret = stmt.execute("create or replace function "
387: + "lvarchar_in_name( IN pcn text) returns int as "
388: + "'begin " + "update longvarchar_tab set t=pcn;"
389: + "return 0;" + " end;' " + "language 'plpgsql';");
390: } catch (Exception ex) {
391: fail(ex.getMessage());
392: throw ex;
393: }
394: try {
395: CallableStatement cstmt = con
396: .prepareCall("{ call longvarchar_proc(?,?) }");
397: cstmt.registerOutParameter(1, Types.LONGVARCHAR);
398: cstmt.registerOutParameter(2, Types.LONGVARCHAR);
399: cstmt.executeUpdate();
400: String val = (String) cstmt.getObject(1);
401: assertTrue(val.equals("testdata"));
402: val = (String) cstmt.getObject(2);
403: assertTrue(val == null);
404: cstmt.close();
405: cstmt = con.prepareCall("{ call lvarchar_in_name(?) }");
406: String maxFloat = "3.4E38";
407: cstmt.setObject(1, new Float(maxFloat), Types.LONGVARCHAR);
408: cstmt.executeUpdate();
409: cstmt.close();
410: Statement stmt = con.createStatement();
411: ResultSet rs = stmt
412: .executeQuery("select * from longvarchar_tab");
413: assertTrue(rs.next());
414: String rval = (String) rs.getObject(1);
415: assertEquals(rval.trim(), maxFloat.trim());
416: } catch (Exception ex) {
417: fail(ex.getMessage());
418: } finally {
419: try {
420: Statement dstmt = con.createStatement();
421: dstmt.execute("drop function longvarchar_proc()");
422: dstmt.execute("drop function lvarchar_in_name(text)");
423: } catch (Exception ex) {
424: }
425: }
426: }
427:
428: public void testGetBytes01() throws Throwable {
429: byte[] testdata = "TestData".getBytes();
430: try {
431: Statement stmt = con.createStatement();
432: stmt
433: .execute("create temp table varbinary_tab ( vbinary bytea, null_val bytea )");
434: boolean ret = stmt
435: .execute("create or replace function "
436: + "varbinary_proc( OUT pcn bytea, OUT nval bytea) as "
437: + "'begin "
438: + "select vbinary into pcn from varbinary_tab;"
439: + "select null_val into nval from varbinary_tab;"
440:
441: + " end;' " + "language 'plpgsql';");
442: stmt.close();
443: PreparedStatement pstmt = con
444: .prepareStatement("insert into varbinary_tab values (?,?)");
445: pstmt.setBytes(1, testdata);
446: pstmt.setBytes(2, null);
447:
448: pstmt.executeUpdate();
449: pstmt.close();
450: } catch (Exception ex) {
451: fail(ex.getMessage());
452: throw ex;
453: }
454: try {
455: CallableStatement cstmt = con
456: .prepareCall("{ call varbinary_proc(?,?) }");
457: cstmt.registerOutParameter(1, Types.VARBINARY);
458: cstmt.registerOutParameter(2, Types.VARBINARY);
459: cstmt.executeUpdate();
460: byte[] retval = cstmt.getBytes(1);
461: for (int i = 0; i < testdata.length; i++) {
462: assertTrue(testdata[i] == retval[i]);
463: }
464:
465: retval = cstmt.getBytes(2);
466: assertTrue(retval == null);
467: } catch (Exception ex) {
468: fail(ex.getMessage());
469: } finally {
470: try {
471: Statement dstmt = con.createStatement();
472: dstmt.execute("drop function varbinary_proc()");
473: } catch (Exception ex) {
474: }
475: }
476: }
477:
478: private final String createDecimalTab = "create temp table decimal_tab ( max_val float, min_val float, null_val float )";
479: private final String insertDecimalTab = "insert into decimal_tab values (1.0E125,1.0E-130,null)";
480: private final String createFloatProc = "create or replace function "
481: + "float_proc( OUT IMAX float, OUT IMIN float, OUT INUL float) as "
482: + "'begin "
483: + "select max_val into imax from decimal_tab;"
484: + "select min_val into imin from decimal_tab;"
485: + "select null_val into inul from decimal_tab;"
486: + " end;' "
487: + "language 'plpgsql';";
488:
489: private final String createUpdateFloat = "create or replace function "
490: + "updatefloat_proc ( IN maxparm float, IN minparm float ) returns int as "
491: + "'begin "
492: + "update decimal_tab set max_val=maxparm;"
493: + "update decimal_tab set min_val=minparm;"
494: + "return 0;"
495: + " end;' " + "language 'plpgsql';";
496:
497: private final String createRealTab = "create temp table real_tab ( max_val float(25), min_val float(25), null_val float(25) )";
498: private final String insertRealTab = "insert into real_tab values (1.0E37,1.0E-37, null)";
499:
500: private final String dropFloatProc = "drop function float_proc()";
501: private final String createUpdateReal = "create or replace function "
502: + "update_real_proc ( IN maxparm float(25), IN minparm float(25) ) returns int as "
503: + "'begin "
504: + "update real_tab set max_val=maxparm;"
505: + "update real_tab set min_val=minparm;"
506: + "return 0;"
507: + " end;' " + "language 'plpgsql';";
508: private final String dropUpdateReal = "drop function update_real_proc(float, float)";
509: private final double[] doubleValues = { 1.0E125, 1.0E-130 };
510: private final float[] realValues = { (float) 1.0E37,
511: (float) 1.0E-37 };
512: private final int[] intValues = { 2147483647, -2147483648 };
513:
514: public void testUpdateReal() throws Throwable {
515: try {
516: Statement stmt = con.createStatement();
517: stmt.execute(createRealTab);
518: boolean ret = stmt.execute(createUpdateReal);
519:
520: stmt.execute(insertRealTab);
521: stmt.close();
522:
523: } catch (Exception ex) {
524: fail(ex.getMessage());
525: throw ex;
526: }
527: try {
528: CallableStatement cstmt = con
529: .prepareCall("{ call update_real_proc(?,?) }");
530: BigDecimal val = new BigDecimal(intValues[0]);
531: float x = val.floatValue();
532: cstmt.setObject(1, val, Types.REAL);
533: val = new BigDecimal(intValues[1]);
534: cstmt.setObject(2, val, Types.REAL);
535: cstmt.executeUpdate();
536: cstmt.close();
537: ResultSet rs = con.createStatement().executeQuery(
538: "select * from real_tab");
539: assertTrue(rs.next());
540: Float oVal = new Float(intValues[0]);
541: Float rVal = new Float(rs.getObject(1).toString());
542: assertTrue(oVal.equals(rVal));
543: oVal = new Float(intValues[1]);
544: rVal = new Float(rs.getObject(2).toString());
545: assertTrue(oVal.equals(rVal));
546: rs.close();
547: } catch (Exception ex) {
548: fail(ex.getMessage());
549: } finally {
550: try {
551: Statement dstmt = con.createStatement();
552: dstmt.execute(dropUpdateReal);
553: dstmt.close();
554: } catch (Exception ex) {
555: }
556: }
557: }
558:
559: public void testUpdateDecimal() throws Throwable {
560: try {
561: Statement stmt = con.createStatement();
562: stmt.execute(createDecimalTab);
563: boolean ret = stmt.execute(createUpdateFloat);
564: stmt.close();
565: PreparedStatement pstmt = con
566: .prepareStatement("insert into decimal_tab values (?,?)");
567: // note these are reversed on purpose
568: pstmt.setDouble(1, doubleValues[1]);
569: pstmt.setDouble(2, doubleValues[0]);
570:
571: pstmt.executeUpdate();
572: pstmt.close();
573: } catch (Exception ex) {
574: fail(ex.getMessage());
575: throw ex;
576: }
577: try {
578: CallableStatement cstmt = con
579: .prepareCall("{ call updatefloat_proc(?,?) }");
580: cstmt.setDouble(1, doubleValues[0]);
581: cstmt.setDouble(2, doubleValues[1]);
582: cstmt.executeUpdate();
583: cstmt.close();
584: ResultSet rs = con.createStatement().executeQuery(
585: "select * from decimal_tab");
586: assertTrue(rs.next());
587: assertTrue(rs.getDouble(1) == doubleValues[0]);
588: assertTrue(rs.getDouble(2) == doubleValues[1]);
589: rs.close();
590: } catch (Exception ex) {
591: fail(ex.getMessage());
592: } finally {
593: try {
594: Statement dstmt = con.createStatement();
595: dstmt
596: .execute("drop function updatefloat_proc(float, float)");
597: } catch (Exception ex) {
598: }
599: }
600: }
601:
602: public void testGetBytes02() throws Throwable {
603: byte[] testdata = "TestData".getBytes();
604: try {
605: Statement stmt = con.createStatement();
606: stmt
607: .execute("create temp table longvarbinary_tab ( vbinary bytea, null_val bytea )");
608: boolean ret = stmt
609: .execute("create or replace function "
610: + "longvarbinary_proc( OUT pcn bytea, OUT nval bytea) as "
611: + "'begin "
612: + "select vbinary into pcn from longvarbinary_tab;"
613: + "select null_val into nval from longvarbinary_tab;"
614:
615: + " end;' " + "language 'plpgsql';");
616: stmt.close();
617: PreparedStatement pstmt = con
618: .prepareStatement("insert into longvarbinary_tab values (?,?)");
619: pstmt.setBytes(1, testdata);
620: pstmt.setBytes(2, null);
621:
622: pstmt.executeUpdate();
623: pstmt.close();
624: } catch (Exception ex) {
625: fail(ex.getMessage());
626: throw ex;
627: }
628: try {
629: CallableStatement cstmt = con
630: .prepareCall("{ call longvarbinary_proc(?,?) }");
631: cstmt.registerOutParameter(1, Types.LONGVARBINARY);
632: cstmt.registerOutParameter(2, Types.LONGVARBINARY);
633: cstmt.executeUpdate();
634: byte[] retval = cstmt.getBytes(1);
635: for (int i = 0; i < testdata.length; i++) {
636: assertTrue(testdata[i] == retval[i]);
637: }
638:
639: retval = cstmt.getBytes(2);
640: assertTrue(retval == null);
641: } catch (Exception ex) {
642: fail(ex.getMessage());
643: } finally {
644: try {
645: Statement dstmt = con.createStatement();
646: dstmt.execute("drop function longvarbinary_proc()");
647: } catch (Exception ex) {
648: }
649: }
650: }
651:
652: public void testGetObjectFloat() throws Throwable {
653: try {
654: Statement stmt = con.createStatement();
655: stmt.execute(createDecimalTab);
656: stmt.execute(insertDecimalTab);
657: boolean ret = stmt.execute(createFloatProc);
658: } catch (Exception ex) {
659: fail(ex.getMessage());
660: throw ex;
661: }
662: try {
663: CallableStatement cstmt = con
664: .prepareCall("{ call float_proc(?,?,?) }");
665: cstmt.registerOutParameter(1, java.sql.Types.FLOAT);
666: cstmt.registerOutParameter(2, java.sql.Types.FLOAT);
667: cstmt.registerOutParameter(3, java.sql.Types.FLOAT);
668: cstmt.executeUpdate();
669: Double val = (Double) cstmt.getObject(1);
670: assertTrue(val.doubleValue() == doubleValues[0]);
671:
672: val = (Double) cstmt.getObject(2);
673: assertTrue(val.doubleValue() == doubleValues[1]);
674:
675: val = (Double) cstmt.getObject(3);
676: assertTrue(cstmt.wasNull());
677: } catch (Exception ex) {
678: fail(ex.getMessage());
679: } finally {
680: try {
681: Statement dstmt = con.createStatement();
682: dstmt.execute(dropFloatProc);
683: } catch (Exception ex) {
684: }
685: }
686: }
687:
688: public void testGetDouble01() throws Throwable {
689: try {
690: Statement stmt = con.createStatement();
691: stmt
692: .execute("create temp table d_tab ( max_val float, min_val float, null_val float )");
693: stmt
694: .execute("insert into d_tab values (1.0E125,1.0E-130,null)");
695: boolean ret = stmt
696: .execute("create or replace function "
697: + "double_proc( OUT IMAX float, OUT IMIN float, OUT INUL float) as "
698: + "'begin "
699: + "select max_val into imax from d_tab;"
700: + "select min_val into imin from d_tab;"
701: + "select null_val into inul from d_tab;"
702:
703: + " end;' " + "language 'plpgsql';");
704: } catch (Exception ex) {
705: fail(ex.getMessage());
706: throw ex;
707: }
708: try {
709: CallableStatement cstmt = con
710: .prepareCall("{ call double_proc(?,?,?) }");
711: cstmt.registerOutParameter(1, java.sql.Types.DOUBLE);
712: cstmt.registerOutParameter(2, java.sql.Types.DOUBLE);
713: cstmt.registerOutParameter(3, java.sql.Types.DOUBLE);
714: cstmt.executeUpdate();
715: assertTrue(cstmt.getDouble(1) == 1.0E125);
716: assertTrue(cstmt.getDouble(2) == 1.0E-130);
717: cstmt.getDouble(3);
718: assertTrue(cstmt.wasNull());
719: } catch (Exception ex) {
720: fail(ex.getMessage());
721: } finally {
722: try {
723: Statement dstmt = con.createStatement();
724: dstmt.execute("drop function double_proc()");
725: } catch (Exception ex) {
726: }
727: }
728: }
729:
730: public void testGetDoubleAsReal() throws Throwable {
731: try {
732: Statement stmt = con.createStatement();
733: stmt
734: .execute("create temp table d_tab ( max_val float, min_val float, null_val float )");
735: stmt
736: .execute("insert into d_tab values (3.4E38,1.4E-45,null)");
737: boolean ret = stmt
738: .execute("create or replace function "
739: + "double_proc( OUT IMAX float, OUT IMIN float, OUT INUL float) as "
740: + "'begin "
741: + "select max_val into imax from d_tab;"
742: + "select min_val into imin from d_tab;"
743: + "select null_val into inul from d_tab;"
744:
745: + " end;' " + "language 'plpgsql';");
746: } catch (Exception ex) {
747: fail(ex.getMessage());
748: throw ex;
749: }
750: try {
751: CallableStatement cstmt = con
752: .prepareCall("{ call double_proc(?,?,?) }");
753: cstmt.registerOutParameter(1, java.sql.Types.REAL);
754: cstmt.registerOutParameter(2, java.sql.Types.REAL);
755: cstmt.registerOutParameter(3, java.sql.Types.REAL);
756: cstmt.executeUpdate();
757: assertTrue(cstmt.getFloat(1) == 3.4E38f);
758: assertTrue(cstmt.getFloat(2) == 1.4E-45f);
759: cstmt.getFloat(3);
760: assertTrue(cstmt.wasNull());
761: } catch (Exception ex) {
762: fail(ex.getMessage());
763: } finally {
764: try {
765: Statement dstmt = con.createStatement();
766: dstmt.execute("drop function double_proc()");
767: } catch (Exception ex) {
768: }
769: }
770: }
771:
772: public void testGetShort01() throws Throwable {
773: try {
774: Statement stmt = con.createStatement();
775: stmt
776: .execute("create temp table short_tab ( max_val int2, min_val int2, null_val int2 )");
777: stmt
778: .execute("insert into short_tab values (32767,-32768,null)");
779: boolean ret = stmt
780: .execute("create or replace function "
781: + "short_proc( OUT IMAX int2, OUT IMIN int2, OUT INUL int2) as "
782: + "'begin "
783: + "select max_val into imax from short_tab;"
784: + "select min_val into imin from short_tab;"
785: + "select null_val into inul from short_tab;"
786:
787: + " end;' " + "language 'plpgsql';");
788: } catch (Exception ex) {
789: fail(ex.getMessage());
790: throw ex;
791: }
792: try {
793: CallableStatement cstmt = con
794: .prepareCall("{ call short_proc(?,?,?) }");
795: cstmt.registerOutParameter(1, java.sql.Types.SMALLINT);
796: cstmt.registerOutParameter(2, java.sql.Types.SMALLINT);
797: cstmt.registerOutParameter(3, java.sql.Types.SMALLINT);
798: cstmt.executeUpdate();
799: assertTrue(cstmt.getShort(1) == 32767);
800: assertTrue(cstmt.getShort(2) == -32768);
801: cstmt.getShort(3);
802: assertTrue(cstmt.wasNull());
803: } catch (Exception ex) {
804: fail(ex.getMessage());
805: } finally {
806: try {
807: Statement dstmt = con.createStatement();
808: dstmt.execute("drop function short_proc()");
809: } catch (Exception ex) {
810: }
811: }
812: }
813:
814: public void testGetInt01() throws Throwable {
815: try {
816: Statement stmt = con.createStatement();
817: stmt
818: .execute("create temp table i_tab ( max_val int, min_val int, null_val int )");
819: stmt
820: .execute("insert into i_tab values (2147483647,-2147483648,null)");
821: boolean ret = stmt
822: .execute("create or replace function "
823: + "int_proc( OUT IMAX int, OUT IMIN int, OUT INUL int) as "
824: + "'begin "
825: + "select max_val into imax from i_tab;"
826: + "select min_val into imin from i_tab;"
827: + "select null_val into inul from i_tab;"
828:
829: + " end;' " + "language 'plpgsql';");
830: } catch (Exception ex) {
831: fail(ex.getMessage());
832: throw ex;
833: }
834: try {
835: CallableStatement cstmt = con
836: .prepareCall("{ call int_proc(?,?,?) }");
837: cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
838: cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
839: cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
840: cstmt.executeUpdate();
841: assertTrue(cstmt.getInt(1) == 2147483647);
842: assertTrue(cstmt.getInt(2) == -2147483648);
843: cstmt.getInt(3);
844: assertTrue(cstmt.wasNull());
845: } catch (Exception ex) {
846: fail(ex.getMessage());
847: } finally {
848: try {
849: Statement dstmt = con.createStatement();
850: dstmt.execute("drop function int_proc()");
851: } catch (Exception ex) {
852: }
853: }
854: }
855:
856: public void testGetLong01() throws Throwable {
857: try {
858: Statement stmt = con.createStatement();
859: stmt
860: .execute("create temp table l_tab ( max_val int8, min_val int8, null_val int8 )");
861: stmt
862: .execute("insert into l_tab values (9223372036854775807,-9223372036854775808,null)");
863: boolean ret = stmt
864: .execute("create or replace function "
865: + "bigint_proc( OUT IMAX int8, OUT IMIN int8, OUT INUL int8) as "
866: + "'begin "
867: + "select max_val into imax from l_tab;"
868: + "select min_val into imin from l_tab;"
869: + "select null_val into inul from l_tab;"
870:
871: + " end;' " + "language 'plpgsql';");
872: } catch (Exception ex) {
873: fail(ex.getMessage());
874: throw ex;
875: }
876: try {
877: CallableStatement cstmt = con
878: .prepareCall("{ call bigint_proc(?,?,?) }");
879: cstmt.registerOutParameter(1, java.sql.Types.BIGINT);
880: cstmt.registerOutParameter(2, java.sql.Types.BIGINT);
881: cstmt.registerOutParameter(3, java.sql.Types.BIGINT);
882: cstmt.executeUpdate();
883: assertTrue(cstmt.getLong(1) == 9223372036854775807l);
884: assertTrue(cstmt.getLong(2) == -9223372036854775808l);
885: cstmt.getLong(3);
886: assertTrue(cstmt.wasNull());
887: } catch (Exception ex) {
888: fail(ex.getMessage());
889: } finally {
890: try {
891: Statement dstmt = con.createStatement();
892: dstmt.execute("drop function bigint_proc()");
893: } catch (Exception ex) {
894: }
895: }
896: }
897:
898: public void testGetBoolean01() throws Throwable {
899: try {
900: Statement stmt = con.createStatement();
901: stmt.execute(createBitTab);
902: stmt.execute(insertBitTab);
903: boolean ret = stmt
904: .execute("create or replace function "
905: + "bit_proc( OUT IMAX boolean, OUT IMIN boolean, OUT INUL boolean) as "
906: + "'begin "
907: + "select max_val into imax from bit_tab;"
908: + "select min_val into imin from bit_tab;"
909: + "select null_val into inul from bit_tab;"
910:
911: + " end;' " + "language 'plpgsql';");
912: } catch (Exception ex) {
913: fail(ex.getMessage());
914: throw ex;
915: }
916: try {
917: CallableStatement cstmt = con
918: .prepareCall("{ call bit_proc(?,?,?) }");
919: cstmt.registerOutParameter(1, java.sql.Types.BIT);
920: cstmt.registerOutParameter(2, java.sql.Types.BIT);
921: cstmt.registerOutParameter(3, java.sql.Types.BIT);
922: cstmt.executeUpdate();
923: assertTrue(cstmt.getBoolean(1));
924: assertTrue(cstmt.getBoolean(2) == false);
925: cstmt.getBoolean(3);
926: assertTrue(cstmt.wasNull());
927: } catch (Exception ex) {
928: fail(ex.getMessage());
929: } finally {
930: try {
931: Statement dstmt = con.createStatement();
932: dstmt.execute("drop function bit_proc()");
933: } catch (Exception ex) {
934: }
935: }
936: }
937:
938: public void testGetByte01() throws Throwable {
939: try {
940: Statement stmt = con.createStatement();
941: stmt
942: .execute("create temp table byte_tab ( max_val int2, min_val int2, null_val int2 )");
943: stmt.execute("insert into byte_tab values (127,-128,null)");
944: boolean ret = stmt
945: .execute("create or replace function "
946: + "byte_proc( OUT IMAX int2, OUT IMIN int2, OUT INUL int2) as "
947: + "'begin "
948: + "select max_val into imax from byte_tab;"
949: + "select min_val into imin from byte_tab;"
950: + "select null_val into inul from byte_tab;"
951:
952: + " end;' " + "language 'plpgsql';");
953: } catch (Exception ex) {
954: fail(ex.getMessage());
955: throw ex;
956: }
957: try {
958: CallableStatement cstmt = con
959: .prepareCall("{ call byte_proc(?,?,?) }");
960: cstmt.registerOutParameter(1, java.sql.Types.TINYINT);
961: cstmt.registerOutParameter(2, java.sql.Types.TINYINT);
962: cstmt.registerOutParameter(3, java.sql.Types.TINYINT);
963: cstmt.executeUpdate();
964: assertTrue(cstmt.getByte(1) == 127);
965: assertTrue(cstmt.getByte(2) == -128);
966: cstmt.getByte(3);
967: assertTrue(cstmt.wasNull());
968: } catch (Exception ex) {
969: fail(ex.getMessage());
970: } finally {
971: try {
972: Statement dstmt = con.createStatement();
973: dstmt.execute("drop function byte_proc()");
974: } catch (Exception ex) {
975: }
976: }
977: }
978: }
|