001: // jTDS JDBC Driver for Microsoft SQL Server and Sybase
002: // Copyright (C) 2004 The jTDS Project
003: //
004: // This library is free software; you can redistribute it and/or
005: // modify it under the terms of the GNU Lesser General Public
006: // License as published by the Free Software Foundation; either
007: // version 2.1 of the License, or (at your option) any later version.
008: //
009: // This library is distributed in the hope that it will be useful,
010: // but WITHOUT ANY WARRANTY; without even the implied warranty of
011: // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
012: // Lesser General Public License for more details.
013: //
014: // You should have received a copy of the GNU Lesser General Public
015: // License along with this library; if not, write to the Free Software
016: // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
017: //
018: package net.sourceforge.jtds.jdbc;
019:
020: import java.io.InputStream;
021: import java.io.Reader;
022: import java.math.BigDecimal;
023: import java.net.URL;
024: import java.net.MalformedURLException;
025: import java.sql.Array;
026: import java.sql.Blob;
027: import java.sql.CallableStatement;
028: import java.sql.Clob;
029: import java.sql.Date;
030: import java.sql.Ref;
031: import java.sql.SQLException;
032: import java.sql.Time;
033: import java.sql.Timestamp;
034: import java.util.ArrayList;
035: import java.util.Calendar;
036: import java.util.Map;
037:
038: /**
039: * jTDS implementation of the java.sql.CallableStatement interface.
040: *<p>
041: * Implementation note:
042: * <ol>
043: * <li>This class is a simple subclass of PreparedStatement and mainly adds support for
044: * setting parameters.
045: * <li>The class supports named parameters in a similar way to the
046: * patch supplied by Tommy Sandstrom to the original jTDS code.
047: * </ol>
048: *
049: * @author Mike Hutchinson
050: * @version $Id: JtdsCallableStatement.java,v 1.23 2007/07/12 21:03:23 bheineman Exp $
051: */
052: public class JtdsCallableStatement extends JtdsPreparedStatement
053: implements CallableStatement {
054: /** Last parameter retrieved was null. */
055: protected boolean paramWasNull;
056:
057: /**
058: * Construct a CallableStatement object.
059: *
060: * @param connection The connection owning this statement.
061: * @param sql The SQL statement specifying the procedure to call.
062: * @param resultSetType The result set type eg FORWARD_ONLY.
063: * @param concurrency The result set concurrency eg READ_ONLY.
064: * @throws SQLException
065: */
066: JtdsCallableStatement(ConnectionJDBC2 connection, String sql,
067: int resultSetType, int concurrency) throws SQLException {
068: super (connection, sql, resultSetType, concurrency, false);
069: }
070:
071: /**
072: * Find a parameter by name.
073: *
074: * @param name The name of the parameter to locate.
075: * @param set True if function is called from a set / register method.
076: * @return The parameter index as an <code>int</code>.
077: * @throws SQLException
078: */
079: int findParameter(String name, boolean set) throws SQLException {
080: checkOpen();
081: for (int i = 0; i < parameters.length; i++) {
082: if (parameters[i].name != null
083: && parameters[i].name.equalsIgnoreCase(name))
084: return i + 1;
085: }
086:
087: if (set && !name.equalsIgnoreCase("@return_status")) {
088: for (int i = 0; i < parameters.length; i++) {
089: if (parameters[i].name == null) {
090: parameters[i].name = name;
091:
092: return i + 1;
093: }
094: }
095: }
096:
097: throw new SQLException(Messages.get("error.callable.noparam",
098: name), "07000");
099: }
100:
101: /**
102: * Retrieve the value of an output parameter.
103: *
104: * @param parameterIndex the ordinal position of the parameter
105: * @return the parameter value as an <code>Object</code>
106: * @throws SQLException if the parameter has not been set
107: */
108: protected Object getOutputValue(int parameterIndex)
109: throws SQLException {
110: checkOpen();
111: ParamInfo parameter = getParameter(parameterIndex);
112: if (!parameter.isOutput) {
113: throw new SQLException(Messages.get(
114: "error.callable.notoutput", new Integer(
115: parameterIndex)), "07000");
116: }
117: Object value = parameter.getOutValue();
118: paramWasNull = (value == null);
119: return value;
120: }
121:
122: /**
123: * Check that this statement is still open.
124: *
125: * @throws SQLException if statement closed.
126: */
127: protected void checkOpen() throws SQLException {
128: if (closed) {
129: throw new SQLException(Messages.get("error.generic.closed",
130: "CallableStatement"), "HY010");
131: }
132: }
133:
134: /**
135: * Execute the SQL batch on a MS server.
136: * @param size the total size of the batch.
137: * @param executeSize the maximum number of statements to send in one request.
138: * @param counts the returned update counts.
139: * @return Chained exceptions linked to a <code>SQLException</code>.
140: * @throws SQLException
141: */
142: protected SQLException executeMSBatch(int size, int executeSize,
143: ArrayList counts) throws SQLException {
144: if (parameters.length == 0) {
145: // No parameters so we can execute as a simple batch
146: return super .executeMSBatch(size, executeSize, counts);
147: }
148: SQLException sqlEx = null;
149: for (int i = 0; i < size;) {
150: Object value = batchValues.get(i);
151: ++i;
152: // Execute batch now if max size reached or end of batch
153: boolean executeNow = (i % executeSize == 0) || i == size;
154:
155: tds.startBatch();
156: tds.executeSQL(sql, procName, (ParamInfo[]) value, false,
157: 0, -1, -1, executeNow);
158:
159: // If the batch has been sent, process the results
160: if (executeNow) {
161: sqlEx = tds.getBatchCounts(counts, sqlEx);
162:
163: // If a serious error then we stop execution now as count
164: // is too small.
165: if (sqlEx != null && counts.size() != i) {
166: break;
167: }
168: }
169: }
170: return sqlEx;
171: }
172:
173: /**
174: * Execute the SQL batch on a Sybase server.
175: * <p/>
176: * For the rare case of CallableStatement batches each statement is executed individually. This ensures that
177: * problems with the server reading into the middle of a statement are avoided. See bug report [1374518] for more
178: * details.
179: *
180: * @param size the total size of the batch
181: * @param executeSize the maximum number of statements to send in one request (ignored for this version of the
182: * method as only one statement will be sent at a time)
183: * @param counts the returned update counts
184: * @return chained exceptions linked to a <code>SQLException</code>
185: * @throws SQLException if a serious error occurs during execution
186: */
187: protected SQLException executeSybaseBatch(int size,
188: int executeSize, ArrayList counts) throws SQLException {
189: if (parameters.length == 0) {
190: // No parameters so we can execute as a simple batch
191: return super .executeSybaseBatch(size, executeSize, counts);
192: }
193:
194: SQLException sqlEx = null;
195:
196: for (int i = 0; i < size;) {
197: Object value = batchValues.get(i);
198: ++i;
199: tds.executeSQL(sql, procName, (ParamInfo[]) value, false,
200: 0, -1, -1, true);
201:
202: // If the batch has been sent, process the results
203: sqlEx = tds.getBatchCounts(counts, sqlEx);
204:
205: // If a serious error then we stop execution now as count
206: // is too small.
207: if (sqlEx != null && counts.size() != i) {
208: break;
209: }
210: }
211: return sqlEx;
212: }
213:
214: // ---------- java.sql.CallableStatement methods follow ----------
215:
216: public boolean wasNull() throws SQLException {
217: checkOpen();
218:
219: return paramWasNull;
220: }
221:
222: public byte getByte(int parameterIndex) throws SQLException {
223: return ((Integer) Support.convert(this ,
224: getOutputValue(parameterIndex), java.sql.Types.TINYINT,
225: null)).byteValue();
226: }
227:
228: public double getDouble(int parameterIndex) throws SQLException {
229: return ((Double) Support.convert(this ,
230: getOutputValue(parameterIndex), java.sql.Types.DOUBLE,
231: null)).doubleValue();
232: }
233:
234: public float getFloat(int parameterIndex) throws SQLException {
235: return ((Float) Support.convert(this ,
236: getOutputValue(parameterIndex), java.sql.Types.REAL,
237: null)).floatValue();
238: }
239:
240: public int getInt(int parameterIndex) throws SQLException {
241: return ((Integer) Support.convert(this ,
242: getOutputValue(parameterIndex), java.sql.Types.INTEGER,
243: null)).intValue();
244: }
245:
246: public long getLong(int parameterIndex) throws SQLException {
247: return ((Long) Support.convert(this ,
248: getOutputValue(parameterIndex), java.sql.Types.BIGINT,
249: null)).longValue();
250: }
251:
252: public short getShort(int parameterIndex) throws SQLException {
253: return ((Integer) Support.convert(this ,
254: getOutputValue(parameterIndex),
255: java.sql.Types.SMALLINT, null)).shortValue();
256: }
257:
258: public boolean getBoolean(int parameterIndex) throws SQLException {
259: return ((Boolean) Support.convert(this ,
260: getOutputValue(parameterIndex), BOOLEAN, null))
261: .booleanValue();
262: }
263:
264: public byte[] getBytes(int parameterIndex) throws SQLException {
265: checkOpen();
266: return ((byte[]) Support.convert(this ,
267: getOutputValue(parameterIndex),
268: java.sql.Types.VARBINARY, connection.getCharset()));
269: }
270:
271: public void registerOutParameter(int parameterIndex, int sqlType)
272: throws SQLException {
273: if (sqlType == java.sql.Types.DECIMAL
274: || sqlType == java.sql.Types.NUMERIC) {
275: registerOutParameter(parameterIndex, sqlType,
276: TdsData.DEFAULT_SCALE);
277: } else {
278: registerOutParameter(parameterIndex, sqlType, 0);
279: }
280: }
281:
282: public void registerOutParameter(int parameterIndex, int sqlType,
283: int scale) throws SQLException {
284: checkOpen();
285:
286: if (scale < 0 || scale > connection.getMaxPrecision()) {
287: throw new SQLException(Messages
288: .get("error.generic.badscale"), "HY092");
289: }
290:
291: ParamInfo pi = getParameter(parameterIndex);
292:
293: pi.isOutput = true;
294:
295: if ("ERROR".equals(Support.getJdbcTypeName(sqlType))) {
296: throw new SQLException(
297: Messages.get("error.generic.badtype", Integer
298: .toString(sqlType)), "HY092");
299: }
300:
301: if (sqlType == java.sql.Types.CLOB) {
302: pi.jdbcType = java.sql.Types.LONGVARCHAR;
303: } else if (sqlType == java.sql.Types.BLOB) {
304: pi.jdbcType = java.sql.Types.LONGVARBINARY;
305: } else {
306: pi.jdbcType = sqlType;
307: }
308:
309: pi.scale = scale;
310: }
311:
312: public Object getObject(int parameterIndex) throws SQLException {
313: Object value = getOutputValue(parameterIndex);
314:
315: // Don't return UniqueIdentifier objects as the user won't know how to
316: // handle them
317: if (value instanceof UniqueIdentifier) {
318: return value.toString();
319: }
320:
321: // If the user requested String/byte[] instead of LOBs, do the conversion
322: if (!connection.getUseLOBs()) {
323: value = Support.convertLOB(value);
324: }
325:
326: return value;
327: }
328:
329: public String getString(int parameterIndex) throws SQLException {
330: checkOpen();
331: return (String) Support.convert(this ,
332: getOutputValue(parameterIndex), java.sql.Types.VARCHAR,
333: connection.getCharset());
334: }
335:
336: public void registerOutParameter(int parameterIndex, int sqlType,
337: String typeName) throws SQLException {
338: notImplemented("CallableStatement.registerOutParameter(int, int, String");
339: }
340:
341: public byte getByte(String parameterName) throws SQLException {
342: return getByte(findParameter(parameterName, false));
343: }
344:
345: public double getDouble(String parameterName) throws SQLException {
346: return getDouble(findParameter(parameterName, false));
347: }
348:
349: public float getFloat(String parameterName) throws SQLException {
350: return getFloat(findParameter(parameterName, false));
351: }
352:
353: public int getInt(String parameterName) throws SQLException {
354: return getInt(findParameter(parameterName, false));
355: }
356:
357: public long getLong(String parameterName) throws SQLException {
358: return getLong(findParameter(parameterName, false));
359: }
360:
361: public short getShort(String parameterName) throws SQLException {
362: return getShort(findParameter(parameterName, false));
363: }
364:
365: public boolean getBoolean(String parameterName) throws SQLException {
366: return getBoolean(findParameter(parameterName, false));
367: }
368:
369: public byte[] getBytes(String parameterName) throws SQLException {
370: return getBytes(findParameter(parameterName, false));
371: }
372:
373: public void setByte(String parameterName, byte x)
374: throws SQLException {
375: setByte(findParameter(parameterName, true), x);
376: }
377:
378: public void setDouble(String parameterName, double x)
379: throws SQLException {
380: setDouble(findParameter(parameterName, true), x);
381: }
382:
383: public void setFloat(String parameterName, float x)
384: throws SQLException {
385: setFloat(findParameter(parameterName, true), x);
386: }
387:
388: public void registerOutParameter(String parameterName, int sqlType)
389: throws SQLException {
390: registerOutParameter(findParameter(parameterName, true),
391: sqlType);
392: }
393:
394: public void setInt(String parameterName, int x) throws SQLException {
395: setInt(findParameter(parameterName, true), x);
396: }
397:
398: public void setNull(String parameterName, int sqlType)
399: throws SQLException {
400: setNull(findParameter(parameterName, true), sqlType);
401: }
402:
403: public void registerOutParameter(String parameterName, int sqlType,
404: int scale) throws SQLException {
405: registerOutParameter(findParameter(parameterName, true),
406: sqlType, scale);
407: }
408:
409: public void setLong(String parameterName, long x)
410: throws SQLException {
411: setLong(findParameter(parameterName, true), x);
412: }
413:
414: public void setShort(String parameterName, short x)
415: throws SQLException {
416: setShort(findParameter(parameterName, true), x);
417: }
418:
419: public void setBoolean(String parameterName, boolean x)
420: throws SQLException {
421: setBoolean(findParameter(parameterName, true), x);
422: }
423:
424: public void setBytes(String parameterName, byte[] x)
425: throws SQLException {
426: setBytes(findParameter(parameterName, true), x);
427: }
428:
429: public BigDecimal getBigDecimal(int parameterIndex)
430: throws SQLException {
431: return (BigDecimal) Support.convert(this ,
432: getOutputValue(parameterIndex), java.sql.Types.DECIMAL,
433: null);
434: }
435:
436: public BigDecimal getBigDecimal(int parameterIndex, int scale)
437: throws SQLException {
438: BigDecimal bd = (BigDecimal) Support.convert(this ,
439: getOutputValue(parameterIndex), java.sql.Types.DECIMAL,
440: null);
441:
442: return bd.setScale(scale);
443: }
444:
445: public URL getURL(int parameterIndex) throws SQLException {
446: checkOpen();
447: String url = (String) Support.convert(this ,
448: getOutputValue(parameterIndex), java.sql.Types.VARCHAR,
449: connection.getCharset());
450:
451: try {
452: return new java.net.URL(url);
453: } catch (MalformedURLException e) {
454: throw new SQLException(Messages.get(
455: "error.resultset.badurl", url), "22000");
456: }
457: }
458:
459: public Array getArray(int parameterIndex) throws SQLException {
460: notImplemented("CallableStatement.getArray");
461: return null;
462: }
463:
464: public Blob getBlob(int parameterIndex) throws SQLException {
465: byte[] value = getBytes(parameterIndex);
466:
467: if (value == null) {
468: return null;
469: }
470:
471: return new BlobImpl(connection, value);
472: }
473:
474: public Clob getClob(int parameterIndex) throws SQLException {
475: String value = getString(parameterIndex);
476:
477: if (value == null) {
478: return null;
479: }
480:
481: return new ClobImpl(connection, value);
482: }
483:
484: public Date getDate(int parameterIndex) throws SQLException {
485: return (java.sql.Date) Support.convert(this ,
486: getOutputValue(parameterIndex), java.sql.Types.DATE,
487: null);
488: }
489:
490: public Ref getRef(int parameterIndex) throws SQLException {
491: notImplemented("CallableStatement.getRef");
492: return null;
493: }
494:
495: public Time getTime(int parameterIndex) throws SQLException {
496: return (Time) Support.convert(this ,
497: getOutputValue(parameterIndex), java.sql.Types.TIME,
498: null);
499: }
500:
501: public Timestamp getTimestamp(int parameterIndex)
502: throws SQLException {
503: return (Timestamp) Support.convert(this ,
504: getOutputValue(parameterIndex),
505: java.sql.Types.TIMESTAMP, null);
506: }
507:
508: public void setAsciiStream(String parameterName, InputStream x,
509: int length) throws SQLException {
510: setAsciiStream(findParameter(parameterName, true), x, length);
511: }
512:
513: public void setBinaryStream(String parameterName, InputStream x,
514: int length) throws SQLException {
515: setBinaryStream(findParameter(parameterName, true), x, length);
516: }
517:
518: public void setCharacterStream(String parameterName, Reader reader,
519: int length) throws SQLException {
520: setCharacterStream(findParameter(parameterName, true), reader,
521: length);
522: }
523:
524: public Object getObject(String parameterName) throws SQLException {
525: return getObject(findParameter(parameterName, false));
526: }
527:
528: public void setObject(String parameterName, Object x)
529: throws SQLException {
530: setObject(findParameter(parameterName, true), x);
531: }
532:
533: public void setObject(String parameterName, Object x,
534: int targetSqlType) throws SQLException {
535: setObject(findParameter(parameterName, true), x, targetSqlType);
536: }
537:
538: public void setObject(String parameterName, Object x,
539: int targetSqlType, int scale) throws SQLException {
540: setObject(findParameter(parameterName, true), x, targetSqlType,
541: scale);
542: }
543:
544: public Object getObject(int parameterIndex, Map map)
545: throws SQLException {
546: notImplemented("CallableStatement.getObject(int, Map)");
547: return null;
548: }
549:
550: public String getString(String parameterName) throws SQLException {
551: return getString(findParameter(parameterName, false));
552: }
553:
554: public void registerOutParameter(String parameterName, int sqlType,
555: String typeName) throws SQLException {
556: notImplemented("CallableStatement.registerOutParameter(String, int, String");
557: }
558:
559: public void setNull(String parameterName, int sqlType,
560: String typeName) throws SQLException {
561: notImplemented("CallableStatement.setNull(String, int, String");
562: }
563:
564: public void setString(String parameterName, String x)
565: throws SQLException {
566: setString(findParameter(parameterName, true), x);
567: }
568:
569: public BigDecimal getBigDecimal(String parameterName)
570: throws SQLException {
571: return getBigDecimal(findParameter(parameterName, false));
572: }
573:
574: public void setBigDecimal(String parameterName, BigDecimal x)
575: throws SQLException {
576: setBigDecimal(findParameter(parameterName, true), x);
577: }
578:
579: public URL getURL(String parameterName) throws SQLException {
580: return getURL(findParameter(parameterName, false));
581: }
582:
583: public void setURL(String parameterName, URL x) throws SQLException {
584: setObject(findParameter(parameterName, true), x);
585: }
586:
587: public Array getArray(String parameterName) throws SQLException {
588: return getArray(findParameter(parameterName, false));
589: }
590:
591: public Blob getBlob(String parameterName) throws SQLException {
592: return getBlob(findParameter(parameterName, false));
593: }
594:
595: public Clob getClob(String parameterName) throws SQLException {
596: return getClob(findParameter(parameterName, false));
597: }
598:
599: public Date getDate(String parameterName) throws SQLException {
600: return getDate(findParameter(parameterName, false));
601: }
602:
603: public void setDate(String parameterName, Date x)
604: throws SQLException {
605: setDate(findParameter(parameterName, true), x);
606: }
607:
608: public Date getDate(int parameterIndex, Calendar cal)
609: throws SQLException {
610: java.sql.Date date = getDate(parameterIndex);
611:
612: if (date != null && cal != null) {
613: date = new java.sql.Date(Support.timeToZone(date, cal));
614: }
615:
616: return date;
617: }
618:
619: public Ref getRef(String parameterName) throws SQLException {
620: return getRef(findParameter(parameterName, false));
621: }
622:
623: public Time getTime(String parameterName) throws SQLException {
624: return getTime(findParameter(parameterName, false));
625: }
626:
627: public void setTime(String parameterName, Time x)
628: throws SQLException {
629: setTime(findParameter(parameterName, true), x);
630: }
631:
632: public Time getTime(int parameterIndex, Calendar cal)
633: throws SQLException {
634: java.sql.Time time = getTime(parameterIndex);
635:
636: if (time != null && cal != null) {
637: time = new java.sql.Time(Support.timeToZone(time, cal));
638: }
639:
640: return time;
641: }
642:
643: public Timestamp getTimestamp(String parameterName)
644: throws SQLException {
645: return getTimestamp(findParameter(parameterName, false));
646: }
647:
648: public void setTimestamp(String parameterName, Timestamp x)
649: throws SQLException {
650: setTimestamp(findParameter(parameterName, true), x);
651: }
652:
653: public Timestamp getTimestamp(int parameterIndex, Calendar cal)
654: throws SQLException {
655: Timestamp timestamp = getTimestamp(parameterIndex);
656:
657: if (timestamp != null && cal != null) {
658: timestamp = new Timestamp(Support
659: .timeToZone(timestamp, cal));
660: }
661:
662: return timestamp;
663: }
664:
665: public Object getObject(String parameterName, Map map)
666: throws SQLException {
667: return getObject(findParameter(parameterName, false), map);
668: }
669:
670: public Date getDate(String parameterName, Calendar cal)
671: throws SQLException {
672: return getDate(findParameter(parameterName, false), cal);
673: }
674:
675: public Time getTime(String parameterName, Calendar cal)
676: throws SQLException {
677: return getTime(findParameter(parameterName, false), cal);
678: }
679:
680: public Timestamp getTimestamp(String parameterName, Calendar cal)
681: throws SQLException {
682: return getTimestamp(findParameter(parameterName, false), cal);
683: }
684:
685: public void setDate(String parameterName, Date x, Calendar cal)
686: throws SQLException {
687: setDate(findParameter(parameterName, true), x, cal);
688: }
689:
690: public void setTime(String parameterName, Time x, Calendar cal)
691: throws SQLException {
692: setTime(findParameter(parameterName, true), x, cal);
693: }
694:
695: public void setTimestamp(String parameterName, Timestamp x,
696: Calendar cal) throws SQLException {
697: setTimestamp(findParameter(parameterName, true), x, cal);
698: }
699: }
|