0001: /*
0002:
0003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.outparams
0004:
0005: Licensed to the Apache Software Foundation (ASF) under one or more
0006: contributor license agreements. See the NOTICE file distributed with
0007: this work for additional information regarding copyright ownership.
0008: The ASF licenses this file to You under the Apache License, Version 2.0
0009: (the "License"); you may not use this file except in compliance with
0010: the License. You may obtain a copy of the License at
0011:
0012: http://www.apache.org/licenses/LICENSE-2.0
0013:
0014: Unless required by applicable law or agreed to in writing, software
0015: distributed under the License is distributed on an "AS IS" BASIS,
0016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0017: See the License for the specific language governing permissions and
0018: limitations under the License.
0019:
0020: */
0021:
0022: package org.apache.derbyTesting.functionTests.tests.lang;
0023:
0024: import java.sql.*;
0025:
0026: import org.apache.derby.tools.ij;
0027: import org.apache.derby.iapi.reference.JDBC30Translation;
0028: import java.io.PrintStream;
0029: import java.math.BigInteger;
0030: import java.math.BigDecimal;
0031: import org.apache.derbyTesting.functionTests.util.BigDecimalHandler;
0032:
0033: public class outparams {
0034:
0035: private static boolean HAVE_BIG_DECIMAL;
0036: private static boolean HAVE_DRIVER_CLASS;
0037: private static String CLASS_NAME;
0038:
0039: //Get the class name to be used for the procedures
0040: //outparams - J2ME; outparams30 - non-J2ME
0041: static {
0042: if (BigDecimalHandler.representation != BigDecimalHandler.BIGDECIMAL_REPRESENTATION)
0043: HAVE_BIG_DECIMAL = false;
0044: else
0045: HAVE_BIG_DECIMAL = true;
0046: if (HAVE_BIG_DECIMAL)
0047: CLASS_NAME = "org.apache.derbyTesting.functionTests.tests.lang.outparams30.";
0048: else
0049: CLASS_NAME = "org.apache.derbyTesting.functionTests.tests.lang.outparams.";
0050: }
0051:
0052: static {
0053: try {
0054: Class.forName("java.sql.Driver");
0055: HAVE_DRIVER_CLASS = true;
0056: } catch (ClassNotFoundException e) {
0057: //Used for JSR169
0058: HAVE_DRIVER_CLASS = false;
0059: }
0060: }
0061:
0062: static String[] outputMethods;
0063: //Get the array to be used based on HAVE_BIG_DECIMAL
0064: static {
0065: if (HAVE_BIG_DECIMAL) {
0066: outputMethods = new String[] { "takesNothing",
0067:
0068: null,
0069:
0070: null,
0071:
0072: "takesShortPrimitive", null,
0073:
0074: "takesIntegerPrimitive", null,
0075:
0076: "takesLongPrimitive", null,
0077:
0078: "takesFloatPrimitive", null,
0079:
0080: "takesDoublePrimitive", null,
0081:
0082: "takesBigDecimal",
0083:
0084: "takesByteArray",
0085:
0086: "takesString",
0087:
0088: "takesDate",
0089:
0090: "takesTimestamp",
0091:
0092: "takesTime",
0093:
0094: null };
0095: } else {
0096: outputMethods = new String[] { "takesNothing",
0097:
0098: null,
0099:
0100: null,
0101:
0102: "takesShortPrimitive", null,
0103:
0104: "takesIntegerPrimitive", null,
0105:
0106: "takesLongPrimitive", null,
0107:
0108: "takesFloatPrimitive", null,
0109:
0110: "takesDoublePrimitive", null,
0111:
0112: null,
0113:
0114: "takesByteArray",
0115:
0116: "takesString",
0117:
0118: "takesDate",
0119:
0120: "takesTimestamp",
0121:
0122: "takesTime",
0123:
0124: null };
0125: }
0126: }
0127:
0128: // parameter types for outputMethods.
0129: private static final String[] outputProcParam = { null, // "takesNothing",
0130:
0131: null,
0132:
0133: null,
0134:
0135: "SMALLINT", // "takesShortPrimitive",
0136: null,
0137:
0138: "INT", // "takesIntegerPrimitive",
0139: null,
0140:
0141: "BIGINT", // "takesLongPrimitive",
0142: null,
0143:
0144: "REAL", // "takesFloatPrimitive",
0145: null,
0146:
0147: "DOUBLE", // "takesDoublePrimitive",
0148: null,
0149:
0150: "DECIMAL(10,4)", // "takesBigDecimal",
0151:
0152: "VARCHAR(40) FOR BIT DATA", // "takesByteArray",
0153:
0154: "VARCHAR(40)", // "takesString",
0155:
0156: "DATE", // "takesDate",
0157:
0158: "TIMESTAMP", // "takesTimestamp",
0159:
0160: "TIME", // "takesTime",
0161:
0162: null };
0163:
0164: static String returnMethods[];
0165: //Get the array to be used based on HAVE_BIG_DECIMAL
0166: static {
0167: if (HAVE_BIG_DECIMAL) {
0168: returnMethods = new String[] { "returnsNothing",
0169:
0170: null, null,
0171:
0172: "returnsShortP", null,
0173:
0174: "returnsIntegerP", null,
0175:
0176: "returnsLongP", null,
0177:
0178: "returnsFloatP", null,
0179:
0180: "returnsDoubleP", null,
0181:
0182: "returnsBigDecimal",
0183:
0184: "returnsByteArray",
0185:
0186: "returnsString",
0187:
0188: "returnsDate",
0189:
0190: "returnsTimestamp",
0191:
0192: "returnsTime",
0193:
0194: null };
0195: } else {
0196: returnMethods = new String[] { "returnsNothing",
0197:
0198: null, null,
0199:
0200: "returnsShortP", null,
0201:
0202: "returnsIntegerP", null,
0203:
0204: "returnsLongP", null,
0205:
0206: "returnsFloatP", null,
0207:
0208: "returnsDoubleP", null,
0209:
0210: null,
0211:
0212: "returnsByteArray",
0213:
0214: "returnsString",
0215:
0216: "returnsDate",
0217:
0218: "returnsTimestamp",
0219:
0220: "returnsTime",
0221:
0222: null };
0223: }
0224: }
0225:
0226: static String[] returnMethodType = { null, // "returnsNothing",
0227:
0228: null, // "returnsBytePrimitive",
0229: null, // "returnsByte",
0230:
0231: "SMALLINT", // "returnsShortPrimitive",
0232: null, // "returnsShort",
0233:
0234: "INT", // "returnsIntegerPrimitive",
0235: null, // "returnsInteger",
0236:
0237: "BIGINT", // "returnsLongPrimitive",
0238: null, // "returnsLong",
0239:
0240: "REAL", // "returnsFloatPrimitive",
0241: null, // "returnsFloat",
0242:
0243: "DOUBLE", // "returnsDoublePrimitive",
0244: null, // "returnsDouble",
0245:
0246: "DECIMAL(10,2)", // "returnsBigDecimal",
0247:
0248: "VARCHAR(40) FOR BIT DATA", // "returnsByteArray",
0249:
0250: "VARCHAR(40)", // "returnsString",
0251:
0252: "DATE", // "returnsDate",
0253:
0254: "TIMESTAMP", // "returnsTimestamp",
0255:
0256: "TIME", // "returnsTime",
0257:
0258: null, // "returnsBigInteger"
0259: };
0260:
0261: //JDBC type (java.sql.Types) corresponding to the methods
0262: static int[] paramJDBCType = { Types.NULL, // "returnsNothing",
0263:
0264: Types.NULL, // "returnsBytePrimitive",
0265: Types.NULL, // "returnsByte",
0266:
0267: Types.SMALLINT, // "returnsShortPrimitive",
0268: Types.NULL, // "returnsShort",
0269:
0270: Types.INTEGER, // "returnsIntegerPrimitive",
0271: Types.NULL, // "returnsInteger",
0272:
0273: Types.BIGINT, // "returnsLongPrimitive",
0274: Types.NULL, // "returnsLong",
0275:
0276: Types.REAL, // "returnsFloatPrimitive",
0277: Types.NULL, // "returnsFloat",
0278:
0279: Types.DOUBLE, // "returnsDoublePrimitive",
0280: Types.NULL, // "returnsDouble",
0281:
0282: Types.NUMERIC, // "returnsBigDecimal",
0283:
0284: Types.VARBINARY, // "returnsByteArray",
0285:
0286: Types.VARCHAR, // "returnsString",
0287:
0288: Types.DATE, // "returnsDate",
0289:
0290: Types.TIMESTAMP, // "returnsTimestamp",
0291:
0292: Types.TIME, // "returnsTime",
0293:
0294: Types.NULL, // "returnsBigInteger"
0295: };
0296:
0297: static final int types[] = { Types.BIT,
0298: JDBC30Translation.SQL_TYPES_BOOLEAN, Types.TINYINT,
0299: Types.SMALLINT, Types.INTEGER, Types.BIGINT, Types.FLOAT,
0300: Types.REAL, Types.DOUBLE, Types.NUMERIC, Types.DECIMAL,
0301: Types.CHAR, Types.VARCHAR, Types.LONGVARCHAR, Types.DATE,
0302: Types.TIME, Types.TIMESTAMP, Types.BINARY, Types.VARBINARY,
0303: Types.LONGVARBINARY, Types.OTHER };
0304:
0305: static final String typeNames[] = { "BIT", "BOOLEAN", "TINYINT",
0306: "SMALLINT", "INTEGER", "BIGINT", "FLOAT", "REAL", "DOUBLE",
0307: "NUMERIC", "DECIMAL", "CHAR", "VARCHAR", "LONGVARCHAR",
0308: "DATE", "TIME", "TIMESTAMP", "BINARY", "VARBINARY",
0309: "LONGVARBINARY", "OTHER" };
0310:
0311: //public static Connection conn;
0312:
0313: public static void main(String[] argv) throws Throwable {
0314: ij.getPropertyArg(argv);
0315: Connection conn = ij.startJBMS();
0316:
0317: runTests(conn);
0318: }
0319:
0320: public static void runTests(Connection conn) throws Throwable {
0321: conn.setAutoCommit(false);
0322:
0323: testMisc(conn);
0324: testNull(conn);
0325: testUpdate(conn);
0326: testEachOutputType(conn);
0327: testReturnTypes(conn);
0328: testOtherOutputType(conn);
0329: testManyOut(conn);
0330: //Uses a procedure with nested connection - Cannot be tested with JSR169
0331: if (HAVE_DRIVER_CLASS)
0332: test5116(conn);
0333: }
0334:
0335: private static void testMisc(Connection conn) throws Throwable {
0336: System.out
0337: .println("==============================================");
0338: System.out.println("TESTING BOUNDARY CONDITIONS");
0339: System.out
0340: .println("==============================================\n");
0341:
0342: Statement scp = conn.createStatement();
0343:
0344: scp
0345: .execute("CREATE PROCEDURE takesString(OUT P1 VARCHAR(40), IN P2 INT) "
0346: + "EXTERNAL NAME '"
0347: + CLASS_NAME
0348: + "takesString'"
0349: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
0350:
0351: CallableStatement cs = conn
0352: .prepareCall("call takesString(?,?)");
0353:
0354: // register a normal int as an output param, should fail
0355: boolean failed = false;
0356: try {
0357: cs.registerOutParameter(2, Types.INTEGER);
0358: } catch (SQLException se) {
0359: failed = true;
0360: System.out.println("Expected exception " + se);
0361: }
0362: if (!failed) {
0363: System.out
0364: .println("registerOutParameter on non-output didn't fail");
0365: }
0366:
0367: // invalid param number
0368: failed = false;
0369: try {
0370: cs.registerOutParameter(9, Types.INTEGER);
0371: } catch (SQLException se) {
0372: failed = true;
0373: System.out.println("Expected exception " + se);
0374: }
0375: if (!failed) {
0376: System.out
0377: .println("registerOutParameter on bad value didn't fail");
0378: }
0379:
0380: // invalid param number
0381: failed = false;
0382: try {
0383: cs.registerOutParameter(0, Types.INTEGER);
0384: } catch (SQLException se) {
0385: failed = true;
0386: System.out.println("Expected exception " + se);
0387: }
0388: if (!failed) {
0389: System.out
0390: .println("registerOutParameter on bad value didn't fail");
0391: }
0392:
0393: // set before register, bad type, should fail as is output parameter.
0394: try {
0395: cs.setDouble(1, 1);
0396: System.out
0397: .println("FAIL setDouble() on takesString() accepted");
0398: } catch (SQLException se) {
0399: System.out.println("Expected exception " + se);
0400: }
0401:
0402: // set before register, should fail as is output parameter.
0403: try {
0404: cs.setString(1, "hello");
0405: System.out
0406: .println("FAIL setString() on takesString() accepted");
0407: } catch (SQLException se) {
0408: System.out.println("Expected exception " + se);
0409: }
0410:
0411: cs.registerOutParameter(1, Types.CHAR);
0412: cs.setInt(2, Types.INTEGER);
0413: try {
0414: cs.execute();
0415: } catch (SQLException se) {
0416: System.out
0417: .println("cs.execute() got unexpected exception: "
0418: + se);
0419: }
0420:
0421: // shouldn't have to reregister the type, and shouldn't
0422: // need to set the output parameters
0423: cs.clearParameters();
0424: cs.setInt(2, Types.INTEGER);
0425: try {
0426: cs.execute();
0427: } catch (SQLException se) {
0428: System.out
0429: .println("cs.execute() got unexpected exception: "
0430: + se);
0431: }
0432: cs.close();
0433: scp.execute("DROP PROCEDURE takesString");
0434:
0435: scp
0436: .execute("CREATE FUNCTION returnsString(P2 INT) RETURNS VARCHAR(40) "
0437: + "EXTERNAL NAME '"
0438: + CLASS_NAME
0439: + "returnsString'"
0440: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
0441: // return output params -- cannot do set on return output param
0442: cs = conn.prepareCall("? = call returnsString(?)");
0443: try {
0444: cs.setString(1, new String("test"));
0445: System.out
0446: .println("ERROR: setString() on return output parameter succeeded");
0447: } catch (SQLException se) {
0448: System.out
0449: .println("Expected exception on setString() on a return output param: "
0450: + se);
0451: }
0452: cs.close();
0453: scp.execute("DROP FUNCTION returnsString");
0454:
0455: // lets try ? = call syntax on a call that doesn't return anything
0456:
0457: scp.execute("CREATE PROCEDURE returnsNothing() "
0458: + "EXTERNAL NAME '" + CLASS_NAME + "returnsNothing'"
0459: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
0460: try {
0461: cs = conn.prepareCall("? = call returnsNothing()");
0462: System.out
0463: .println("ERROR: no exception on prepare of '? = call returnsNothing()");
0464: } catch (SQLException se) {
0465: System.out
0466: .println("Expected exception on prepare of '? = call returnsNothing()': "
0467: + se);
0468: }
0469: scp.execute("DROP PROCEDURE returnsNothing");
0470: }
0471:
0472: private static void testNull(Connection conn) throws Throwable {
0473: System.out
0474: .println("==============================================");
0475: System.out.println("TESTING NULLS");
0476: System.out
0477: .println("==============================================\n");
0478: System.out
0479: .println("Test for bug 4317, passing null value for a parameter");
0480:
0481: Statement scp = conn.createStatement();
0482:
0483: scp
0484: .execute("CREATE PROCEDURE testNullBug4317(IN P1 VARCHAR(10)) "
0485: + "EXTERNAL NAME '"
0486: + CLASS_NAME
0487: + "testNullBug4317'"
0488: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
0489:
0490: CallableStatement cs0 = conn
0491: .prepareCall("call testNullBug4317(?)");
0492: try {
0493: cs0.setString(1, null); // passing in null
0494: cs0.execute();
0495: } catch (SQLException se) {
0496: System.out
0497: .println("cs0.execute() got unexpected exception: "
0498: + se);
0499: }
0500:
0501: try {
0502: // BUG 5928 - setNull throws an exception - fixed.
0503: cs0.setNull(1, java.sql.Types.VARCHAR); // passing in null
0504: cs0.execute();
0505: } catch (SQLException se) {
0506: System.out
0507: .println("cs0.execute() got unexpected exception: "
0508: + se);
0509: }
0510: cs0.close();
0511: scp.execute("DROP PROCEDURE testNullBug4317");
0512:
0513: }
0514:
0515: // test: do we get an appropriate update count when using ?=call?
0516: private static void testUpdate(Connection conn) throws Throwable {
0517: System.out
0518: .println("==============================================");
0519: System.out.println("TESTING UPDATE COUNT");
0520: System.out
0521: .println("==============================================\n");
0522:
0523: Statement scp = conn.createStatement();
0524:
0525: scp
0526: .execute("CREATE FUNCTION returnsIntegerP(P1 INT) RETURNS INTEGER "
0527: + "EXTERNAL NAME '"
0528: + CLASS_NAME
0529: + "returnsIntegerP'"
0530: + " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
0531:
0532: CallableStatement cs = conn
0533: .prepareCall("? = call returnsIntegerP(0)");
0534: cs.registerOutParameter(1, Types.INTEGER);
0535: try {
0536: int updCount = cs.executeUpdate();
0537: System.out
0538: .println("executeUpdate on ? = call returnsIntegerP returned "
0539: + updCount);
0540: System.out.println("getString(1) returned "
0541: + cs.getString(1));
0542: } catch (SQLException se) {
0543: System.out
0544: .println("cs.execute() got unexpected exception: "
0545: + se);
0546: }
0547:
0548: cs.close();
0549: scp.execute("DROP FUNCTION returnsIntegerP");
0550: scp.close();
0551: }
0552:
0553: // should do get setString() to use a string that is appropriate for
0554: // the target type
0555: private static void testEachOutputType(Connection conn)
0556: throws Throwable {
0557: System.out
0558: .println("==============================================");
0559: System.out.println("TESTING NORMAL OUTPUT PARAMETERS");
0560: System.out
0561: .println("==============================================\n");
0562: CallableStatement cs = null;
0563:
0564: for (int doSetObject = 0; doSetObject < 3; doSetObject++) {
0565: switch (doSetObject) {
0566: case 0:
0567: System.out
0568: .println("...starting doing setXXX for each type xxx");
0569: break;
0570: case 1:
0571: System.out
0572: .println("...now doing setObject on each type xxx");
0573: break;
0574: case 2:
0575: System.out
0576: .println("...not doing any setXXX, just OUT parameters, not IN/OUT");
0577: break;
0578: }
0579:
0580: for (int method = 0; method < outputMethods.length; method++) {
0581: String methodName = outputMethods[method];
0582: if (methodName == null)
0583: continue;
0584:
0585: System.out
0586: .println("\n------------------------------------");
0587:
0588: Statement scp = conn.createStatement();
0589: String str;
0590: if (methodName.indexOf("Nothing") == -1) {
0591:
0592: scp
0593: .execute("CREATE PROCEDURE "
0594: + methodName
0595: + "(INOUT P1 "
0596: + outputProcParam[method]
0597: + ", IN P2 INT) "
0598: + "EXTERNAL NAME '"
0599: + CLASS_NAME
0600: + ""
0601: + methodName
0602: + "' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
0603:
0604: if (method % 2 == 0)
0605: str = "{call " + methodName + "(?,?)}";
0606: else
0607: str = "call " + methodName + "(?,?)";
0608: } else {
0609: scp
0610: .execute("CREATE PROCEDURE "
0611: + methodName
0612: + "() "
0613: + "EXTERNAL NAME '"
0614: + CLASS_NAME
0615: + ""
0616: + methodName
0617: + "' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
0618: str = "{call " + methodName + "()}";
0619: }
0620:
0621: System.out.println(str);
0622: try {
0623: cs = conn.prepareCall(str);
0624: } catch (SQLException se) {
0625: System.out.println("ERROR: unexpected exception "
0626: + se);
0627: throw se;
0628: }
0629:
0630: for (int type = 0; type < types.length; type++) {
0631: cs.clearParameters();
0632: System.out.println();
0633: try {
0634: System.out
0635: .println("\n\tcs.registerOutParameter(1, "
0636: + typeNames[type] + ")");
0637: cs.registerOutParameter(1, types[type]);
0638: } catch (SQLException se) {
0639: System.out.println("\tException " + se);
0640: continue;
0641: }
0642:
0643: StringBuffer buf = new StringBuffer();
0644: try {
0645: if (doSetObject == 0) {
0646: callSetMethod(cs, 1, types[type], buf);
0647: } else if (doSetObject == 1) {
0648: callSetObject(cs, 1, types[type], buf);
0649: } else {
0650: // only try this once
0651: type = types.length - 1;
0652: buf.append("...no setXXX(1) at all");
0653: }
0654: } catch (SQLException se) {
0655: System.out.println("\t" + buf.toString());
0656: System.out.println("\tException " + se);
0657: continue;
0658: }
0659: System.out.println("\t" + buf.toString());
0660: cs.setInt(2, types[type]);
0661:
0662: try {
0663: System.out.println("\tcs.execute()");
0664: boolean hasResultSet = cs.execute();
0665: if (hasResultSet)
0666: System.out
0667: .println("testEachOutputType HAS RESULT SET cs.execute() returned true");
0668: } catch (SQLException se) {
0669: System.out.println("\tException " + se);
0670: continue;
0671: }
0672: for (int getType = 0; getType < types.length; getType++) {
0673: StringBuffer getbuf = new StringBuffer();
0674: try {
0675: callGetMethod(cs, 1, types[getType],
0676: paramJDBCType[method], getbuf);
0677: } catch (SQLException se) {
0678: getbuf.append(se);
0679: }
0680: System.out
0681: .println("\t\t\t" + getbuf.toString());
0682: }
0683:
0684: }
0685:
0686: cs.close();
0687:
0688: scp.execute("DROP PROCEDURE " + methodName);
0689: scp.close();
0690: }
0691: }
0692:
0693: System.out.println("------------------------------------\n");
0694:
0695: }
0696:
0697: // test that everything works ok when we regsiter the param as type OTHER.
0698: // should be able to get/setXXX of the appropriate type
0699: private static void testOtherOutputType(Connection conn)
0700: throws Throwable {
0701: System.out
0702: .println("==============================================");
0703: System.out
0704: .println("TESTING OUTPUT PARAMETERS WITH register(OTHER)");
0705: System.out
0706: .println("==============================================\n");
0707: CallableStatement cs = null;
0708:
0709: for (int method = 0; method < outputMethods.length; method++) {
0710: String methodName = outputMethods[method];
0711: if (methodName == null)
0712: continue;
0713: System.out
0714: .println("\n------------------------------------");
0715:
0716: Statement scp = conn.createStatement();
0717: String str;
0718: if (methodName.indexOf("Nothing") == -1) {
0719:
0720: scp
0721: .execute("CREATE PROCEDURE "
0722: + methodName
0723: + "(INOUT P1 "
0724: + outputProcParam[method]
0725: + ", IN P2 INT) "
0726: + "EXTERNAL NAME '"
0727: + CLASS_NAME
0728: + ""
0729: + methodName
0730: + "' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
0731:
0732: if (method % 2 == 0)
0733: str = "{call " + methodName + "(?,?)}";
0734: else
0735: str = "call " + methodName + "(?,?)";
0736: } else {
0737: scp
0738: .execute("CREATE PROCEDURE "
0739: + methodName
0740: + "() "
0741: + "EXTERNAL NAME '"
0742: + CLASS_NAME
0743: + ""
0744: + methodName
0745: + "' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
0746: str = "{call " + methodName + "()}";
0747: }
0748:
0749: System.out.println(str);
0750: try {
0751: cs = conn.prepareCall(str);
0752: } catch (SQLException se) {
0753: System.out.println("ERROR: unexpected exception " + se);
0754: throw se;
0755: }
0756:
0757: for (int type = 0; type < types.length; type++) {
0758: cs.clearParameters();
0759: System.out.println();
0760: try {
0761: System.out
0762: .println("\n\tcs.registerOutParameter(1, Types.OTHER)");
0763: cs.registerOutParameter(1, Types.OTHER);
0764: } catch (SQLException se) {
0765: System.out.println("\tException " + se);
0766: continue;
0767: }
0768:
0769: StringBuffer buf = new StringBuffer();
0770: try {
0771: callSetMethod(cs, 1, types[type], buf);
0772: } catch (SQLException se) {
0773: System.out.println("\t" + buf.toString());
0774: System.out.println("\tException " + se);
0775: continue;
0776: }
0777: System.out.println("\t" + buf.toString());
0778: cs.setInt(2, types[type]);
0779:
0780: try {
0781: System.out.println("\tcs.execute()");
0782: cs.execute();
0783: } catch (SQLException se) {
0784: System.out.println("\tException " + se);
0785: continue;
0786: }
0787: for (int getType = 0; getType < types.length; getType++) {
0788: StringBuffer getbuf = new StringBuffer();
0789: try {
0790: callGetMethod(cs, 1, types[getType],
0791: Types.OTHER, getbuf);
0792: } catch (SQLException se) {
0793: getbuf.append(se);
0794: }
0795: System.out.println("\t\t\t" + getbuf.toString());
0796: }
0797:
0798: }
0799:
0800: cs.close();
0801:
0802: scp.execute("DROP PROCEDURE " + methodName);
0803: scp.close();
0804: }
0805:
0806: System.out.println("------------------------------------\n");
0807: }
0808:
0809: private static void testReturnTypes(Connection conn)
0810: throws Throwable {
0811: System.out
0812: .println("==============================================\n");
0813: System.out.println("TESTING RETURN OUTPUT PARAMETERS");
0814: System.out
0815: .println("==============================================\n");
0816: CallableStatement cs = null;
0817: for (int method = 0; method < returnMethods.length; method++) {
0818: String methodName = returnMethods[method];
0819: if (methodName == null)
0820: continue;
0821:
0822: Statement scf = conn.createStatement();
0823: String str;
0824: String dropRoutine;
0825: if (methodName.indexOf("Nothing") != -1) {
0826:
0827: scf
0828: .execute("CREATE PROCEDURE "
0829: + methodName
0830: + "()"
0831: + " EXTERNAL NAME '"
0832: + CLASS_NAME
0833: + ""
0834: + methodName
0835: + "' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
0836:
0837: dropRoutine = "DROP PROCEDURE " + methodName;
0838:
0839: str = "{call " + returnMethods[method] + "()}";
0840: } else {
0841:
0842: scf
0843: .execute("CREATE FUNCTION "
0844: + methodName
0845: + "(P1 INT) RETURNS "
0846: + returnMethodType[method]
0847: + " EXTERNAL NAME '"
0848: + CLASS_NAME
0849: + ""
0850: + methodName
0851: + "' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
0852: dropRoutine = "DROP FUNCTION " + methodName;
0853:
0854: str = "{? = call " + returnMethods[method] + "(?)}";
0855: }
0856:
0857: System.out
0858: .println("\n------------------------------------");
0859:
0860: System.out.println(str);
0861: try {
0862: cs = conn.prepareCall(str);
0863: } catch (SQLException se) {
0864: System.out.println("ERROR: unexpected exception " + se);
0865: throw se;
0866: }
0867:
0868: for (int type = 0; type < types.length; type++) {
0869: cs.clearParameters();
0870: System.out.println();
0871: try {
0872: System.out
0873: .println("\n\tcs.registerOutParameter(1, "
0874: + typeNames[type] + ")");
0875: cs.registerOutParameter(1, types[type]);
0876: } catch (SQLException se) {
0877: System.out.println("\tException " + se);
0878: continue;
0879: }
0880: try {
0881: cs.setInt(2, types[type]);
0882: } catch (SQLException se) {
0883: System.out
0884: .println("\tUnexpected exception on cs.setInt(2, "
0885: + types[type] + "): " + se);
0886: continue;
0887: }
0888:
0889: try {
0890: System.out.println("\tcs.execute()");
0891: boolean hasResultSet = cs.execute();
0892: if (hasResultSet)
0893: System.out
0894: .println("testReturnTypes HAS RESULT SET cs.execute() returned true");
0895:
0896: } catch (SQLException se) {
0897: System.out.println("\tException " + se);
0898: continue;
0899: }
0900: for (int getType = 0; getType < types.length; getType++) {
0901: StringBuffer getbuf = new StringBuffer();
0902: try {
0903: callGetMethod(cs, 1, types[getType],
0904: paramJDBCType[method], getbuf);
0905: } catch (SQLException se) {
0906: getbuf.append(se);
0907: }
0908: System.out.println("\t\t\t" + getbuf.toString());
0909: }
0910:
0911: }
0912:
0913: cs.close();
0914: scf.execute(dropRoutine);
0915: scf.close();
0916: }
0917:
0918: System.out.println("------------------------------------\n");
0919:
0920: }
0921:
0922: private static void callSetObject(CallableStatement cs, int arg,
0923: int type, StringBuffer strbuf) throws Throwable {
0924:
0925: switch (type) {
0926: case Types.BIT:
0927: case JDBC30Translation.SQL_TYPES_BOOLEAN:
0928: strbuf.append("setObject(" + arg + ", true)");
0929: cs.setObject(arg, new Boolean(true));
0930: break;
0931:
0932: case Types.TINYINT:
0933: strbuf.append("setObject(" + arg + ", 6)");
0934: cs.setObject(arg, new Integer((byte) 6));
0935: break;
0936:
0937: case Types.SMALLINT:
0938: strbuf.append("setObject(" + arg + ", 66)");
0939: cs.setObject(arg, new Integer((short) 66));
0940: break;
0941:
0942: case Types.INTEGER:
0943: strbuf.append("setObject(" + arg + ", 666)");
0944: cs.setObject(arg, new Integer(666));
0945: break;
0946:
0947: case Types.BIGINT:
0948: strbuf.append("setObject(" + arg + ", 666)");
0949: cs.setObject(arg, new Long(666));
0950: break;
0951:
0952: case Types.FLOAT:
0953: case Types.REAL:
0954: strbuf.append("setObject(" + arg + ", 666)");
0955: cs.setObject(arg, new Float(666));
0956: break;
0957:
0958: case Types.DOUBLE:
0959: strbuf.append("setObject(" + arg + ", 666)");
0960: cs.setObject(arg, new Double(666));
0961: break;
0962:
0963: case Types.DECIMAL:
0964: case Types.NUMERIC:
0965: strbuf.append("setObject(" + arg + ", 666.666)");
0966: BigDecimalHandler.setObjectString(cs, arg, "666.666");
0967: break;
0968:
0969: case Types.CHAR:
0970: case Types.VARCHAR:
0971: case Types.LONGVARCHAR:
0972: strbuf.append("setObject(" + arg
0973: + ", \"Set via setString()\")");
0974: cs.setObject(arg, "Set via setString()");
0975: break;
0976:
0977: case Types.BINARY:
0978: case Types.VARBINARY:
0979: case Types.LONGVARBINARY:
0980: strbuf.append("setObject(" + arg + ", byte[])");
0981: byte[] myarray = new byte[16];
0982: myarray[0] = (byte) 255;
0983: cs.setObject(arg, myarray);
0984: break;
0985:
0986: case Types.DATE:
0987: strbuf.append("setObject(" + arg
0988: + ", Date.valueOf(1999-09-09))");
0989: cs.setObject(arg, Date.valueOf("1999-09-09"));
0990: break;
0991:
0992: case Types.TIME:
0993: strbuf.append("setObject(" + arg
0994: + ", Time.valueOf(09:09:09))");
0995: cs.setObject(arg, Time.valueOf("09:09:09"));
0996: break;
0997:
0998: case Types.TIMESTAMP:
0999: strbuf.append("setObject(" + arg
1000: + ", Timestamp.valueOf(1999-09-09 09:09:09.999))");
1001: cs.setObject(arg, Timestamp
1002: .valueOf("1999-09-09 09:09:09.999"));
1003: break;
1004:
1005: case Types.OTHER:
1006: strbuf
1007: .append("setObject(" + arg
1008: + ", new BigInteger(666))");
1009: cs.setObject(arg, new BigInteger("666"));
1010: break;
1011:
1012: default:
1013: throw new Throwable("TEST ERROR: unexpected type " + type);
1014: }
1015: }
1016:
1017: private static void callSetMethod(CallableStatement cs, int arg,
1018: int type, StringBuffer strbuf) throws Throwable {
1019: switch (type) {
1020: case Types.BIT:
1021: case JDBC30Translation.SQL_TYPES_BOOLEAN:
1022: strbuf.append("setBoolean(" + arg + ", true)");
1023: cs.setBoolean(arg, true);
1024: break;
1025:
1026: case Types.TINYINT:
1027: strbuf.append("setByte(" + arg + ", 6)");
1028: cs.setByte(arg, (byte) 6);
1029: break;
1030:
1031: case Types.SMALLINT:
1032: strbuf.append("setShort(" + arg + ", 66)");
1033: cs.setShort(arg, (short) 66);
1034: break;
1035:
1036: case Types.INTEGER:
1037: strbuf.append("setInt(" + arg + ", 666)");
1038: cs.setInt(arg, 666);
1039: break;
1040:
1041: case Types.BIGINT:
1042: strbuf.append("setLong(" + arg + ", 666)");
1043: cs.setLong(arg, 666);
1044: break;
1045:
1046: case Types.FLOAT:
1047: case Types.REAL:
1048: strbuf.append("setFLoat(" + arg + ", 666)");
1049: cs.setFloat(arg, 666);
1050: break;
1051:
1052: case Types.DOUBLE:
1053: strbuf.append("setDouble(" + arg + ", 666)");
1054: cs.setDouble(arg, 666);
1055: break;
1056:
1057: case Types.DECIMAL:
1058: case Types.NUMERIC:
1059: strbuf.append("setBigDecimal(" + arg + ", 666.666)");
1060: BigDecimalHandler.setBigDecimalString(cs, arg, "666.666");
1061: break;
1062:
1063: case Types.CHAR:
1064: case Types.VARCHAR:
1065: case Types.LONGVARCHAR:
1066: strbuf.append("setString(" + arg
1067: + ", \"Set via setString()\")");
1068: cs.setString(arg, "Set via setString()");
1069: break;
1070:
1071: case Types.BINARY:
1072: case Types.VARBINARY:
1073: case Types.LONGVARBINARY:
1074: strbuf.append("setBytes(" + arg + ", byte[])");
1075: byte[] myarray = new byte[16];
1076: myarray[0] = (byte) 255;
1077: cs.setBytes(arg, myarray);
1078: break;
1079:
1080: case Types.DATE:
1081: strbuf.append("setDate(" + arg
1082: + ", Date.valueOf(1999-09-09))");
1083: cs.setDate(arg, Date.valueOf("1999-09-09"));
1084: break;
1085:
1086: case Types.TIME:
1087: strbuf.append("setTime(" + arg
1088: + ", Time.valueOf(09:09:09))");
1089: cs.setTime(arg, Time.valueOf("09:09:09"));
1090: break;
1091:
1092: case Types.TIMESTAMP:
1093: strbuf.append("setTimestamp(" + arg
1094: + ", Timestamp.valueOf(1999-09-09 09:09:09.999))");
1095: cs.setTimestamp(arg, Timestamp
1096: .valueOf("1999-09-09 09:09:09.999"));
1097: break;
1098:
1099: case Types.OTHER:
1100: strbuf
1101: .append("setObject(" + arg
1102: + ", new BigInteger(666))");
1103: cs.setObject(arg, new BigInteger("666"));
1104: break;
1105:
1106: default:
1107: throw new Throwable("TEST ERROR: unexpected type " + type);
1108: }
1109: }
1110:
1111: private static void callGetMethod(CallableStatement cs, int arg,
1112: int type, int paramType, StringBuffer strbuf)
1113: throws Throwable {
1114: switch (type) {
1115: case Types.BIT:
1116: case JDBC30Translation.SQL_TYPES_BOOLEAN:
1117: strbuf.append("getBoolean(" + arg + ") = ");
1118: strbuf.append(cs.getBoolean(arg));
1119: break;
1120:
1121: case Types.TINYINT:
1122: strbuf.append("getByte(" + arg + ") = ");
1123: strbuf.append(Byte.toString(cs.getByte(arg)));
1124: break;
1125:
1126: case Types.SMALLINT:
1127: strbuf.append("getShort(" + arg + ") = ");
1128: strbuf.append(Short.toString(cs.getShort(arg)));
1129: break;
1130:
1131: case Types.INTEGER:
1132: strbuf.append("getInt(" + arg + ") = ");
1133: strbuf.append(Integer.toString(cs.getInt(arg)));
1134: break;
1135:
1136: case Types.BIGINT:
1137: strbuf.append("getLong(" + arg + ") = ");
1138: strbuf.append(Long.toString(cs.getLong(arg)));
1139: break;
1140:
1141: case Types.FLOAT:
1142: case Types.REAL:
1143: strbuf.append("getFloat(" + arg + ") = ");
1144: strbuf.append(Float.toString(cs.getFloat(arg)));
1145: break;
1146:
1147: case Types.DOUBLE:
1148: strbuf.append("getDouble(" + arg + ") = ");
1149: strbuf.append(Double.toString(cs.getDouble(arg)));
1150: break;
1151:
1152: case Types.DECIMAL:
1153: case Types.NUMERIC:
1154: strbuf.append("getBigDecimal(" + arg + ") = ");
1155: strbuf.append(BigDecimalHandler.getBigDecimalString(cs,
1156: arg, paramType));
1157: break;
1158:
1159: case Types.CHAR:
1160: case Types.VARCHAR:
1161: case Types.LONGVARCHAR:
1162: strbuf.append("getString(" + arg + ") = ");
1163: String s = cs.getString(arg);
1164: if (s.startsWith("[B@"))
1165: s = "byte[] reference";
1166: strbuf.append(s);
1167: break;
1168:
1169: case Types.BINARY:
1170: case Types.VARBINARY:
1171: case Types.LONGVARBINARY:
1172: strbuf.append("getBytes(" + arg + ") = ");
1173: byteArrayToString(cs.getBytes(arg), strbuf);
1174: break;
1175:
1176: case Types.DATE:
1177: strbuf.append("getDate(" + arg + ") = ");
1178: Date date = cs.getDate(arg);
1179: strbuf.append(date == null ? "null" : date.toString());
1180: break;
1181:
1182: case Types.TIME:
1183: strbuf.append("getTime(" + arg + ") = ");
1184: Time time = cs.getTime(arg);
1185: strbuf.append(time == null ? "null" : time.toString());
1186: break;
1187:
1188: case Types.TIMESTAMP:
1189: strbuf.append("getTimestamp(" + arg + ") = ");
1190: Timestamp timestamp = cs.getTimestamp(arg);
1191: strbuf.append(timestamp == null ? "null" : timestamp
1192: .toString());
1193: break;
1194:
1195: case Types.OTHER:
1196: strbuf.append("getObject(" + arg + ") = ");
1197: Object o = cs.getObject(arg);
1198: if (o == null) {
1199: strbuf.append("null");
1200: } else if (o instanceof byte[]) {
1201: byteArrayToString((byte[]) o, strbuf);
1202: } else {
1203: strbuf.append(o.toString());
1204: }
1205:
1206: break;
1207:
1208: default:
1209: throw new Throwable("TEST ERROR: unexpected type " + type);
1210: }
1211: }
1212:
1213: static private void byteArrayToString(byte[] barray,
1214: StringBuffer strbuf) {
1215: if (barray == null) {
1216: strbuf.append("null");
1217: } else {
1218: for (int i = 0; i < barray.length; i++) {
1219: strbuf.append(barray[i]);
1220: }
1221: }
1222: }
1223:
1224: private static String getStringOfType(int type) throws Throwable {
1225: switch (type) {
1226: case Types.CHAR:
1227: case Types.VARCHAR:
1228: case Types.LONGVARCHAR:
1229: return "I am a string";
1230:
1231: case Types.TINYINT:
1232: case Types.SMALLINT:
1233: case Types.INTEGER:
1234: case Types.BIGINT:
1235: case Types.OTHER: // other is bigInt
1236: return "3";
1237:
1238: case Types.FLOAT:
1239: case Types.REAL:
1240: case Types.DECIMAL:
1241: case Types.NUMERIC:
1242: return "3.33";
1243:
1244: case Types.DATE:
1245: return "1933-03-03";
1246:
1247: case Types.TIME:
1248: return "03:03:03";
1249:
1250: case Types.TIMESTAMP:
1251: return "1933-03-03 03:03:03.333";
1252:
1253: case Types.BINARY:
1254: case Types.VARBINARY:
1255: case Types.LONGVARBINARY:
1256: return "00680065006c006c006f";
1257:
1258: case Types.BIT:
1259: case JDBC30Translation.SQL_TYPES_BOOLEAN:
1260: return "true";
1261:
1262: default:
1263: throw new Throwable("bad type " + type);
1264: }
1265: }
1266:
1267: /////////////////////////////////////////////////////////////
1268: //
1269: // OUTPUT PARAMETER METHODS
1270: //
1271: /////////////////////////////////////////////////////////////
1272: public static void testNull(Boolean passedInNull,
1273: Boolean setToNull, Integer[] retval) throws Throwable {
1274: if (passedInNull.booleanValue()) {
1275: if (retval[0] != null) {
1276: throw new Throwable(
1277: "testNull() got a non-null param when it should have been null");
1278: }
1279: }
1280:
1281: retval[0] = (setToNull.booleanValue()) ? null : new Integer(
1282: (short) 66);
1283: }
1284:
1285: public static void testNullBug4317(String passedInNull)
1286: throws Throwable {
1287: }
1288:
1289: public static void takesNothing() {
1290: }
1291:
1292: public static void takesBytePrimitive(byte[] outparam, int type) {
1293: outparam[0] += outparam[0];
1294: }
1295:
1296: public static void takesByte(Byte[] outparam, int type) {
1297: outparam[0] = new Byte((byte) (outparam[0] == null ? 33
1298: : outparam[0].byteValue() * 2));
1299: }
1300:
1301: public static void takesShortPrimitive(short[] outparam, int type) {
1302: outparam[0] += outparam[0];
1303: }
1304:
1305: public static void takesShort(Short[] outparam, int type) {
1306: outparam[0] = new Short((byte) (outparam[0] == null ? 33
1307: : outparam[0].shortValue() * 2));
1308: }
1309:
1310: public static void takesIntegerPrimitive(int[] outparam, int type) {
1311: outparam[0] += outparam[0];
1312: }
1313:
1314: public static void takesInteger(Integer[] outparam, int type) {
1315: outparam[0] = new Integer(outparam[0] == null ? 33
1316: : outparam[0].intValue() * 2);
1317: }
1318:
1319: public static void takesLongPrimitive(long[] outparam, int type) {
1320: outparam[0] += outparam[0];
1321: }
1322:
1323: public static void takesLong(Long[] outparam, int type) {
1324: outparam[0] = new Long(outparam[0] == null ? 33 : outparam[0]
1325: .longValue() * 2);
1326: }
1327:
1328: public static void takesDoublePrimitive(double[] outparam, int type) {
1329: outparam[0] += outparam[0];
1330: }
1331:
1332: public static void takesDouble(Double[] outparam, int type) {
1333: outparam[0] = new Double(outparam[0] == null ? 33 : outparam[0]
1334: .doubleValue() * 2);
1335: }
1336:
1337: public static void takesFloatPrimitive(float[] outparam, int type) {
1338: outparam[0] += outparam[0];
1339: }
1340:
1341: public static void takesFloat(Float[] outparam, int type) {
1342: outparam[0] = new Float(outparam[0] == null ? 33 : outparam[0]
1343: .floatValue() * 2);
1344: }
1345:
1346: public static void takesBooleanPrimitive(boolean[] outparam,
1347: int type) {
1348: outparam[0] = true;
1349: }
1350:
1351: public static void takesBoolean(Boolean[] outparam, int type) {
1352: outparam[0] = new Boolean(true);
1353: }
1354:
1355: public static void takesByteArray(byte[][] outparam, int type) {
1356: byte[] myarray = new byte[16];
1357: myarray[0] = (byte) 255;
1358: outparam[0] = myarray;
1359: }
1360:
1361: public static void takesDate(Date[] outparam, int type) {
1362: outparam[0] = Date.valueOf("1966-06-06");
1363: }
1364:
1365: public static void takesTime(Time[] outparam, int type) {
1366: outparam[0] = Time.valueOf("06:06:06");
1367: }
1368:
1369: public static void takesTimestamp(Timestamp[] outparam, int type) {
1370: outparam[0] = Timestamp.valueOf("1966-06-06 06:06:06.666");
1371: }
1372:
1373: public static void takesString(String[] outparam, int type)
1374: throws Throwable {
1375: outparam[0] = getStringOfType(type);
1376: }
1377:
1378: public static void takesBigInteger(BigInteger[] outparam, int type) {
1379: outparam[0] = (outparam[0] == null ? new BigInteger("33")
1380: : outparam[0].add(outparam[0]));
1381: }
1382:
1383: /////////////////////////////////////////////////////////////
1384: //
1385: // RETURN PARAMETER METHODS
1386: //
1387: /////////////////////////////////////////////////////////////
1388: public static void returnsNothing() {
1389: }
1390:
1391: public static byte returnsByteP(int type) {
1392: return 66;
1393: }
1394:
1395: public static Byte returnsByte(int type) {
1396: return new Byte((byte) 66);
1397: }
1398:
1399: public static short returnsShortP(int type) {
1400: return 666;
1401: }
1402:
1403: public static Short returnsShort(int type) {
1404: return new Short((short) 666);
1405: }
1406:
1407: public static int returnsIntegerP(int type) {
1408: return 666;
1409: }
1410:
1411: public static Integer returnsInteger(int type) {
1412: return new Integer(666);
1413: }
1414:
1415: public static long returnsLongP(int type) {
1416: return 666;
1417: }
1418:
1419: public static Long returnsLong(int type) {
1420: return new Long(666);
1421: }
1422:
1423: public static float returnsFloatP(int type) {
1424: return 666;
1425: }
1426:
1427: public static Float returnsFloat(int type) {
1428: return new Float(666);
1429: }
1430:
1431: public static double returnsDoubleP(int type) {
1432: return 666;
1433: }
1434:
1435: public static Double returnsDouble(int type) {
1436: return new Double(666);
1437: }
1438:
1439: public static byte[] returnsByteArray(int type) {
1440: byte[] myarray = new byte[16];
1441: myarray[0] = (byte) 255;
1442: return myarray;
1443: }
1444:
1445: public static String returnsString(int type) throws Throwable {
1446: return getStringOfType(type);
1447: }
1448:
1449: public static Date returnsDate(int type) {
1450: return Date.valueOf("1966-06-06");
1451: }
1452:
1453: public static Time returnsTime(int type) {
1454: return Time.valueOf("06:06:06");
1455: }
1456:
1457: public static Timestamp returnsTimestamp(int type) {
1458: return Timestamp.valueOf("1966-06-06 06:06:06.666");
1459: }
1460:
1461: public static BigInteger returnsBigInteger(int type) {
1462: return new BigInteger("666");
1463: }
1464:
1465: // these come from the performance test JDBC.Parameters that was failing
1466: private static void testManyOut(Connection conn)
1467: throws SQLException {
1468:
1469: System.out.println("start testManyOut");
1470:
1471: Statement scp = conn.createStatement();
1472:
1473: scp
1474: .execute("CREATE PROCEDURE OP_OUT "
1475: + "(OUT I1 INT, OUT I2 INT, OUT I3 INT, OUT I4 INT, OUT I5 INT, "
1476: + "OUT V1 VARCHAR(40), OUT V2 VARCHAR(40), OUT V3 VARCHAR(40), OUT V4 VARCHAR(40), OUT V5 VARCHAR(40)) "
1477: +
1478:
1479: "EXTERNAL NAME '"
1480: + CLASS_NAME
1481: + "output' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
1482:
1483: scp
1484: .execute("CREATE PROCEDURE OP_INOUT "
1485: + "(INOUT I1 INT, INOUT I2 INT, INOUT I3 INT, INOUT I4 INT, INOUT I5 INT, "
1486: + "INOUT V1 VARCHAR(40), INOUT V2 VARCHAR(40), INOUT V3 VARCHAR(40), INOUT V4 VARCHAR(40), INOUT V5 VARCHAR(40)) "
1487: +
1488:
1489: "EXTERNAL NAME '"
1490: + CLASS_NAME
1491: + "output' NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
1492:
1493: CallableStatement csOut_cs = conn
1494: .prepareCall("CALL OP_OUT(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
1495: CallableStatement csInOut_cs = conn
1496: .prepareCall("CALL OP_INOUT(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
1497:
1498: System.out.println("Ten OUT parameters");
1499:
1500: executeOutput(csOut_cs);
1501: executeOutput(csOut_cs);
1502:
1503: csOut_cs.close();
1504:
1505: System.out.println("Ten INOUT parameters");
1506:
1507: setupInput(csInOut_cs);
1508: executeOutput(csInOut_cs);
1509:
1510: setupInput(csInOut_cs);
1511: executeOutput(csInOut_cs);
1512:
1513: csInOut_cs.close();
1514:
1515: scp.execute("DROP PROCEDURE OP_OUT");
1516: scp.execute("DROP PROCEDURE OP_INOUT");
1517: scp.close();
1518:
1519: System.out.println("end testManyOut");
1520:
1521: }
1522:
1523: private static void setupInput(PreparedStatement ps)
1524: throws SQLException {
1525:
1526: ps.setInt(1, 0);
1527: ps.setInt(2, 0);
1528: ps.setInt(3, 99);
1529: ps.setInt(4, 103);
1530: ps.setInt(5, 1456);
1531:
1532: ps.setNull(6, Types.CHAR);
1533: ps.setString(7, null);
1534: ps.setString(8, "hello");
1535: ps.setString(9, "goodbye");
1536: ps.setString(10, "welcome");
1537: }
1538:
1539: private static void executeOutput(CallableStatement cs)
1540: throws SQLException {
1541:
1542: for (int p = 1; p <= 5; p++)
1543: cs.registerOutParameter(p, Types.INTEGER);
1544:
1545: for (int p = 6; p <= 10; p++)
1546: cs.registerOutParameter(p, Types.VARCHAR);
1547:
1548: cs.execute();
1549:
1550: for (int p = 1; p <= 5; p++) {
1551: System.out.println(" " + p + " = " + cs.getInt(p)
1552: + " was null " + cs.wasNull());
1553:
1554: }
1555: for (int p = 6; p <= 10; p++) {
1556: System.out.println(" " + p + " = " + cs.getString(p)
1557: + " was null " + cs.wasNull());
1558: }
1559: }
1560:
1561: public static void output(int[] a1, int[] a2, int[] a3, int[] a4,
1562: int[] a5, String[] s1, String[] s2, String[] s3,
1563: String[] s4, String[] s5) {
1564:
1565: System.out.println(" a1 = " + a1[0]);
1566: System.out.println(" a2 = " + a2[0]);
1567: System.out.println(" a3 = " + a3[0]);
1568: System.out.println(" a4 = " + a4[0]);
1569: System.out.println(" a5 = " + a5[0]);
1570:
1571: System.out.println(" s1 = " + s1[0]);
1572: System.out.println(" s2 = " + s2[0]);
1573: System.out.println(" s3 = " + s3[0]);
1574: System.out.println(" s4 = " + s4[0]);
1575: System.out.println(" s5 = " + s5[0]);
1576:
1577: a1[0] = 0;
1578: a2[0] = 0;
1579: a3[0] = 77;
1580: a4[0] = 4;
1581: a5[0] = 2003;
1582:
1583: s1[0] = null;
1584: s2[0] = null;
1585: s3[0] = "cloudscape";
1586: s4[0] = "jbms";
1587: s5[0] = "IBM CS";
1588: }
1589:
1590: private static void test5116(Connection conn) throws Throwable {
1591: System.out
1592: .println("==============================================");
1593: System.out
1594: .println("TESTING FIX OF 5116 -- VAR BIT VARYING INPUT");
1595: System.out
1596: .println("==============================================\n");
1597:
1598: Statement stmt = conn.createStatement();
1599: stmt
1600: .executeUpdate("CREATE TABLE ACTIVITY_INSTANCE_T ("
1601: + "AIID char(16) for bit data NOT NULL ,"
1602: + "KIND INTEGER NOT NULL ,"
1603: + "PIID char(16) for bit data NOT NULL ,"
1604: + "PTID char(16) for bit data NOT NULL ,"
1605: + "ATID char(16) for bit data NOT NULL ,"
1606: + "RUN_MODE INTEGER NOT NULL ,"
1607: + "FINISHED TIMESTAMP ,"
1608: + "ACTIVATED TIMESTAMP ,"
1609: + "STARTED TIMESTAMP ,"
1610: + "LAST_MODIFIED TIMESTAMP ,"
1611: + "LAST_STATE_CHANGE TIMESTAMP ,"
1612: + "STATE INTEGER NOT NULL ,"
1613: + "TRANS_COND_VALUES VARCHAR(66) FOR BIT DATA NOT NULL ,"
1614: + "NUM_CONN_ACT_EVA INTEGER NOT NULL ,"
1615: + "NUMBER_OF_ITERATIONS INTEGER NOT NULL ,"
1616: + "NUMBER_OF_RETRIES INTEGER NOT NULL ,"
1617: + "HAS_CUSTOM_ATTRIBUTES SMALLINT NOT NULL ,"
1618: + "NON_BLOCK_PTID char(16) for bit data NOT NULL ,"
1619: + "NON_BLOCK_PIID char(16) for bit data NOT NULL ,"
1620: + "EXPIRES TIMESTAMP ,"
1621: + "TASK_ID VARCHAR(254) ,"
1622: + "UNHANDLED_EXCEPTION BLOB(3993600) ,"
1623: + "SUB_PROCESS_PIID char(16) for bit data ,"
1624: + "OWNER VARCHAR(32) ,"
1625: + "USER_INPUT VARCHAR(130) FOR BIT DATA ,"
1626: + "DESCRIPTION VARCHAR(254) ,"
1627: + "VERSION_ID SMALLINT NOT NULL ,"
1628: + "PRIMARY KEY ( AIID ) )");
1629:
1630: stmt
1631: .execute("CREATE PROCEDURE doInsertion(IN P1 VARCHAR(2) FOR BIT DATA) "
1632: + "EXTERNAL NAME '"
1633: + CLASS_NAME
1634: + "doInsertion'"
1635: + " MODIFIES SQL DATA LANGUAGE JAVA PARAMETER STYLE JAVA");
1636:
1637: CallableStatement cs = conn.prepareCall("call doInsertion (?)");
1638: cs.setNull(1, java.sql.Types.VARBINARY);
1639: cs.execute();
1640: byte[] b = new byte[2];
1641: b[0] = 1;
1642: b[1] = 2;
1643: cs.setBytes(1, b);
1644: cs.execute();
1645: cs.close();
1646: stmt.executeUpdate("DROP PROCEDURE doInsertion");
1647: stmt.close();
1648: }
1649:
1650: public static void doInsertion(byte[] p25) throws Throwable {
1651: Connection connNested = DriverManager
1652: .getConnection("jdbc:default:connection");
1653: Statement stmt = connNested.createStatement();
1654: stmt.executeUpdate("delete from ACTIVITY_INSTANCE_T");
1655:
1656: String strStmt = "INSERT INTO ACTIVITY_INSTANCE_T VALUES( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )";
1657: PreparedStatement pstmt = connNested.prepareStatement(strStmt);
1658:
1659: byte[] b = new byte[2];
1660: b[0] = 1;
1661: byte[] b2 = new byte[1];
1662: b2[0] = 0;
1663:
1664: pstmt.setBytes(1, b); //ids
1665: pstmt.setInt(2, 0);
1666: pstmt.setBytes(3, b);
1667: pstmt.setBytes(4, b);
1668: pstmt.setBytes(5, b);
1669: pstmt.setInt(6, 0);
1670: pstmt.setNull(7, java.sql.Types.TIMESTAMP);
1671: pstmt.setNull(8, java.sql.Types.TIMESTAMP);
1672: pstmt.setNull(9, java.sql.Types.TIMESTAMP);
1673: pstmt.setNull(10, java.sql.Types.TIMESTAMP);
1674: pstmt.setNull(11, java.sql.Types.TIMESTAMP);
1675: pstmt.setInt(12, 0);
1676: pstmt.setBytes(13, b);
1677:
1678: pstmt.setInt(14, 0);
1679: pstmt.setInt(15, 0);
1680: pstmt.setInt(16, 0);
1681: pstmt.setBoolean(17, false);
1682: pstmt.setBytes(18, b);
1683: pstmt.setBytes(19, b);
1684: pstmt.setNull(20, java.sql.Types.TIMESTAMP);
1685: pstmt.setNull(21, java.sql.Types.VARCHAR);
1686: pstmt.setNull(22, java.sql.Types.BLOB);
1687: pstmt.setNull(23, java.sql.Types.VARBINARY);
1688: pstmt.setNull(24, java.sql.Types.VARCHAR);
1689: if (p25 == null)
1690: pstmt.setNull(25, java.sql.Types.VARBINARY);
1691: else
1692: pstmt.setBytes(25, p25);
1693: pstmt.setNull(26, java.sql.Types.VARCHAR);
1694: pstmt.setShort(27, (short) 0);
1695: pstmt.executeUpdate();
1696: pstmt.close();
1697:
1698: pstmt = connNested
1699: .prepareStatement("SELECT version_id, user_input FROM activity_instance_t");
1700: ResultSet resultSet = pstmt.executeQuery();
1701: System.out.println("Executed query");
1702: while (resultSet.next()) {
1703: System.out.println("i= " + resultSet.getInt(1));
1704: byte[] userInput = resultSet.getBytes(2);
1705: if (userInput == null || resultSet.wasNull()) {
1706: if (userInput == null)
1707: System.out.println("UserInput = null");
1708: if (resultSet.wasNull())
1709: System.out.println("resultSet wasNull");
1710: } else {
1711: System.out.println("UserInput length = "
1712: + userInput.length + " bytes");
1713: for (int i = 0; i < userInput.length; i++) {
1714: System.out.println(i + ") = " + userInput[i]);
1715: }
1716: }
1717: }
1718: System.out.println("Close result set.");
1719: resultSet.close();
1720: pstmt.close();
1721: stmt.close();
1722: connNested.close();
1723: }
1724: }
|