001: //
002: // Copyright 1998,1999 CDS Networks, Inc., Medford Oregon
003: //
004: // All rights reserved.
005: //
006: // Redistribution and use in source and binary forms, with or without
007: // modification, are permitted provided that the following conditions are met:
008: // 1. Redistributions of source code must retain the above copyright
009: // notice, this list of conditions and the following disclaimer.
010: // 2. Redistributions in binary form must reproduce the above copyright
011: // notice, this list of conditions and the following disclaimer in the
012: // documentation and/or other materials provided with the distribution.
013: // 3. All advertising materials mentioning features or use of this software
014: // must display the following acknowledgement:
015: // This product includes software developed by CDS Networks, Inc.
016: // 4. The name of CDS Networks, Inc. may not be used to endorse or promote
017: // products derived from this software without specific prior
018: // written permission.
019: //
020: // THIS SOFTWARE IS PROVIDED BY CDS NETWORKS, INC. ``AS IS'' AND
021: // ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
022: // IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
023: // ARE DISCLAIMED. IN NO EVENT SHALL CDS NETWORKS, INC. BE LIABLE
024: // FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
025: // DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
026: // OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
027: // HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
028: // LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
029: // OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
030: // SUCH DAMAGE.
031: //
032:
033: package com.internetcds.jdbc.tds;
034:
035: import java.sql.*;
036: import java.math.BigDecimal;
037: import java.util.StringTokenizer;
038: import java.util.Vector;
039: import java.util.Calendar;
040: import java.io.*;
041:
042: /**
043: * <P>A SQL statement is pre-compiled and stored in a
044: * PreparedStatement object. This object can then be used to
045: * efficiently execute this statement multiple times.
046: *
047: * <P><B>Note:</B> The setXXX methods for setting IN parameter values
048: * must specify types that are compatible with the defined SQL type of
049: * the input parameter. For instance, if the IN parameter has SQL type
050: * Integer then setInt should be used.
051: *
052: * <p>If arbitrary parameter type conversions are required then the
053: * setObject method should be used with a target SQL type.
054: *
055: * @author Craig Spannring
056: * @author The FreeTDS project
057: * @version $Id: PreparedStatement_base.java,v 1.2 2007-10-19 13:21:40 sinisa Exp $
058: *
059: * @see Connection#prepareStatement
060: * @see ResultSet
061: */
062: public class PreparedStatement_base extends
063: com.internetcds.jdbc.tds.Statement implements
064: PreparedStatementHelper {
065: public static final String cvsVersion = "$Id: PreparedStatement_base.java,v 1.2 2007-10-19 13:21:40 sinisa Exp $";
066:
067: String rawQueryString = null;
068: Vector procedureCache = null;
069: ParameterListItem[] parameterList = null;
070:
071: public PreparedStatement_base(java.sql.Connection conn_, Tds tds_,
072: String sql) throws SQLException {
073: super (conn_, tds_);
074:
075: rawQueryString = sql;
076:
077: int i;
078: int numberOfParameters = ParameterUtils
079: .countParameters(rawQueryString);
080:
081: parameterList = new ParameterListItem[numberOfParameters];
082: for (i = 0; i < numberOfParameters; i++) {
083: parameterList[i] = new ParameterListItem();
084: }
085:
086: procedureCache = new Vector();
087: }
088:
089: protected void NotImplemented() throws java.sql.SQLException {
090: throw new SQLException("Not Implemented");
091: }
092:
093: /**
094: * <P>In general, parameter values remain in force for repeated use of a
095: * Statement. Setting a parameter value automatically clears its
096: * previous value. However, in some cases it is useful to immediately
097: * release the resources used by the current parameter values; this can
098: * be done by calling clearParameters.
099: *
100: * @exception SQLException if a database-access error occurs.
101: */
102: public void clearParameters() throws SQLException {
103: int i;
104: for (i = 0; i < parameterList.length; i++) {
105: parameterList[i].clear();
106: }
107: }
108:
109: public void dropAllProcedures() {
110: procedureCache = null;
111: procedureCache = new Vector();
112: }
113:
114: /**
115: * Some prepared statements return multiple results; the execute
116: * method handles these complex statements as well as the simpler
117: * form of statements handled by executeQuery and executeUpdate.
118: *
119: * @exception SQLException if a database-access error occurs.
120: * @see Statement#execute
121: */
122: public boolean execute() throws SQLException {
123: //
124: // TDS can handle prepared statements by creating a temporary
125: // procedure. Since procedure must have the datatype specified
126: // in the procedure declaration we will have to defer creating
127: // the actual procedure until the statement is executed. By
128: // that time we know all the types of all of the parameters.
129: //
130:
131: Procedure procedure = null;
132: boolean result = false;
133:
134: closeResults();
135: updateCount = -2;
136:
137: // First make sure the caller has filled in all the parameters.
138: ParameterUtils.verifyThatParametersAreSet(parameterList);
139:
140: // Find a stored procedure that is compatible with this set of
141: // parameters if one exists.
142: procedure = findCompatibleStoredProcedure();
143:
144: // if we don't have a suitable match then create a new
145: // temporary stored procedure
146: if (procedure == null) {
147:
148: // create the stored procedure
149: procedure = new Procedure(rawQueryString, tds
150: .getUniqueProcedureName(), parameterList, tds);
151:
152: // store it in the procedureCache
153: procedureCache.addElement(procedure);
154:
155: // create it on the SQLServer.
156: submitProcedure(procedure);
157: }
158: result = executeCall(procedure.getProcedureName(), procedure
159: .getParameterList(), // formal params
160: parameterList); // actual params
161:
162: return result;
163: }
164:
165: protected boolean executeCall(String name,
166: ParameterListItem[] formalParameterList,
167: ParameterListItem[] actualParameterList)
168: throws SQLException {
169:
170: boolean result;
171: boolean wasCanceled = false;
172:
173: try {
174: SQLException exception = null;
175: PacketResult tmp = null;
176:
177: // execute the stored procedure.
178: tds.executeProcedure(name, formalParameterList,
179: actualParameterList, this , timeout);
180:
181: while (tds.isErrorPacket() || tds.isMessagePacket()) {
182: tmp = tds.processSubPacket();
183: exception = warningChain
184: .addOrReturn((PacketMsgResult) tmp);
185: if (exception != null) {
186: throw exception;
187: }
188: }
189:
190: while (tds.isDoneInProc()) {
191: tmp = tds.processSubPacket();
192: }
193:
194: if (tds.isProcId()) {
195: tmp = tds.processSubPacket();
196: }
197:
198: if (tds.isResultSet()) {
199: result = true;
200: } else {
201: result = false;
202: boolean done = false;
203: do {
204: tmp = tds.processSubPacket();
205: if (tmp instanceof PacketEndTokenResult) {
206: done = !((PacketEndTokenResult) tmp)
207: .moreResults();
208: wasCanceled = wasCanceled
209: || ((PacketEndTokenResult) tmp)
210: .wasCanceled();
211: updateCount = ((PacketEndTokenResult) tmp)
212: .getRowCount();
213: } else if (tmp.getPacketType() == TdsDefinitions.TDS_RET_STAT_TOKEN) {
214: // nop
215: } else {
216: throw new SQLException("Protocol confusion"
217: + "Found a "
218: + tmp.getClass().getName()
219: + " (packet type 0x"
220: + Integer.toHexString(tmp
221: .getPacketType() & 0xff) + ")");
222: }
223: } while (!done);
224: }
225: } catch (TdsException e) {
226: e.printStackTrace();
227: throw new SQLException(e.getMessage());
228: } catch (java.io.IOException e) {
229: e.printStackTrace();
230: throw new SQLException(e.getMessage());
231: }
232: if (wasCanceled) {
233: throw new SQLException("Query was canceled or timed out.");
234: }
235:
236: return result;
237: }
238:
239: private Procedure findCompatibleStoredProcedure()
240: throws SQLException {
241:
242: Procedure procedure = null;
243: int i;
244:
245: for (i = 0; i < procedureCache.size(); i++) {
246: Procedure tmp = (Procedure) procedureCache.elementAt(i);
247: if (tmp.compatibleParameters(parameterList)) {
248: procedure = tmp;
249: if (!tmp.hasLobParameters()) {
250: break;
251: }
252: }
253: }
254:
255: return procedure;
256: }
257:
258: private void submitProcedure(Procedure proc) throws SQLException {
259: String sql = proc.getPreparedSqlString();
260: tds.submitProcedure(sql, warningChain);
261: }
262:
263: /**
264: * A prepared SQL query is executed and its ResultSet is returned.
265: *
266: * @return a ResultSet that contains the data produced by the
267: * query; never null
268: * @exception SQLException if a database-access error occurs.
269: */
270: public java.sql.ResultSet executeQuery() throws SQLException {
271: if (execute()) {
272: startResultSet();
273: } else {
274: throw new SQLException("Was expecting a result set");
275: }
276: return results;
277: }
278:
279: /**
280: * Execute a SQL INSERT, UPDATE or DELETE statement. In addition,
281: * SQL statements that return nothing such as SQL DDL statements
282: * can be executed.
283: *
284: * @return either the row count for INSERT, UPDATE or DELETE; or 0
285: * for SQL statements that return nothing
286: * @exception SQLException if a database-access error occurs.
287: */
288: public int executeUpdate() throws SQLException {
289: closeResults();
290:
291: if (execute()) {
292: startResultSet();
293: closeResults();
294: throw new SQLException(
295: "executeUpdate can't return a result set");
296: } else {
297: return getUpdateCount();
298: }
299: }
300:
301: /**
302: * When a very large ASCII value is input to a LONGVARCHAR
303: * parameter, it may be more practical to send it via a
304: * java.io.InputStream. JDBC will read the data from the stream
305: * as needed, until it reaches end-of-file. The JDBC driver will
306: * do any necessary conversion from ASCII to the database char format.
307: *
308: * <P><B>Note:</B> This stream object can either be a standard
309: * Java stream object or your own subclass that implements the
310: * standard interface.
311: *
312: * @param parameterIndex the first parameter is 1, the second is 2, ...
313: * @param x the java input stream which contains the ASCII parameter value
314: * @param length the number of bytes in the stream
315: * @exception SQLException if a database-access error occurs.
316: */
317: public void setAsciiStream(int parameterIndex,
318: java.io.InputStream x, int length) throws SQLException {
319: //NotImplemented();
320: if (length == 0) {
321: setParam(parameterIndex, " ", 12, 1);
322: } else {
323: byte[] b = new byte[length];
324: try {
325: int i = x.read(b);
326: } catch (IOException ioe) {
327: }
328: setParam(parameterIndex, new String(b), 12, length);
329: }
330: }
331:
332: /**
333: * Set a parameter to a java.lang.BigDecimal value.
334: * The driver converts this to a SQL NUMERIC value when
335: * it sends it to the database.
336: *
337: * @param parameterIndex the first parameter is 1, the second is 2, ...
338: * @param x the parameter value
339: * @exception SQLException if a database-access error occurs.
340: */
341: public void setBigDecimal(int parameterIndex, BigDecimal x)
342: throws SQLException {
343: //NotImplemented();
344: setParam(parameterIndex, new Float(x.floatValue()), 7, -1);
345: }
346:
347: /**
348: * When a very large binary value is input to a LONGVARBINARY
349: * parameter, it may be more practical to send it via a
350: * java.io.InputStream. JDBC will read the data from the stream
351: * as needed, until it reaches end-of-file.
352: *
353: * <P><B>Note:</B> This stream object can either be a standard
354: * Java stream object or your own subclass that implements the
355: * standard interface.
356: *
357: * @param parameterIndex the first parameter is 1, the second is 2, ...
358: * @param x the java input stream which contains the binary parameter value
359: * @param length the number of bytes in the stream
360: * @exception SQLException if a database-access error occurs.
361: */
362: public void setBinaryStream(int parameterIndex,
363: java.io.InputStream x, int length) throws SQLException {
364: //NotImplemented();
365: byte[] b = new byte[length];
366: try {
367: int i = x.read(b);
368: } catch (IOException io) {
369: }
370: if (b == null || length <= 255) {
371: setParam(parameterIndex, b, -3, -1);
372: } else {
373: setParam(parameterIndex, b, -4, -1);
374: }
375: }
376:
377: /**
378: * Set a parameter to a Java boolean value. The driver converts this
379: * to a SQL BIT value when it sends it to the database.
380: *
381: * @param parameterIndex the first parameter is 1, the second is 2, ...
382: * @param x the parameter value
383: * @exception SQLException if a database-access error occurs.
384: */
385: public void setBoolean(int parameterIndex, boolean x)
386: throws SQLException {
387: byte[] b = new byte[1];
388:
389: if (x == true) {
390: b[0] = 1;
391: //setParam(parameterIndex, b, -7, -1); }
392: setParam(parameterIndex, new Byte("1"), -7, -1);
393: } else {
394: b[0] = 0;
395: //setParam(parameterIndex, b, -7, -1);
396: setParam(parameterIndex, new Byte("0"), -7, -1);
397: }
398:
399: }
400:
401: /**
402: * Set a parameter to a Java byte value. The driver converts this
403: * to a SQL TINYINT value when it sends it to the database.
404: *
405: * @param parameterIndex the first parameter is 1, the second is 2, ...
406: * @param x the parameter value
407: * @exception SQLException if a database-access error occurs.
408: */
409: public void setByte(int index, byte x) throws SQLException {
410: //throw new SQLException("Not implemented");
411: byte[] b = new byte[1];
412: b[0] = x;
413: setParam(index, b, -3, -1);
414: }
415:
416: /**
417: * Set a parameter to a Java array of bytes. The driver converts
418: * this to a SQL VARBINARY or LONGVARBINARY (depending on the
419: * argument's size relative to the driver's limits on VARBINARYs)
420: * when it sends it to the database.
421: *
422: * @param parameterIndex the first parameter is 1, the second is 2, ...
423: * @param x the parameter value
424: * @exception SQLException if a database-access error occurs.
425: */
426: public void setBytes(int parameterIndex, byte x[])
427: throws SQLException {
428: // when this method creates the parameter the formal type should
429: // be a varbinary if the length of 'x' is <=255, image if length>255.
430: if (x == null || x.length <= 255) {
431: setParam(parameterIndex, x, java.sql.Types.VARBINARY, -1);
432: } else {
433: setParam(parameterIndex, x, java.sql.Types.LONGVARBINARY,
434: -1);
435: }
436: }
437:
438: /**
439: * Set a parameter to a java.sql.Date value. The driver converts this
440: * to a SQL DATE value when it sends it to the database.
441: *
442: * @param parameterIndex the first parameter is 1, the second is 2, ...
443: * @param x the parameter value
444: * @exception SQLException if a database-access error occurs.
445: */
446: public void setDate(int parameterIndex, java.sql.Date value)
447: throws SQLException {
448:
449: setParam(parameterIndex,
450: //new java.util.Date(value.getYear(), value.getMonth(), value.getDate()),
451: new java.sql.Date(value.getYear(), value.getMonth(),
452: value.getDate()), java.sql.Types.DATE, -1);
453: }
454:
455: /**
456: * Set a parameter to a Java double value. The driver converts this
457: * to a SQL DOUBLE value when it sends it to the database.
458: *
459: * @param parameterIndex the first parameter is 1, the second is 2, ...
460: * @param x the parameter value
461: * @exception SQLException if a database-access error occurs.
462: */
463: public void setDouble(int parameterIndex, double value)
464: throws SQLException {
465: setParam(parameterIndex, new Double(value),
466: java.sql.Types.DOUBLE, -1);
467: }
468:
469: /**
470: * Set a parameter to a Java float value. The driver converts this
471: * to a SQL FLOAT value when it sends it to the database.
472: *
473: * @param parameterIndex the first parameter is 1, the second is 2, ...
474: * @param x the parameter value
475: * @exception SQLException if a database-access error occurs.
476: */
477: public void setFloat(int parameterIndex, float value)
478: throws SQLException {
479: setParam(parameterIndex, new Float(value), java.sql.Types.REAL,
480: -1);
481: }
482:
483: /**
484: * Set a parameter to a Java int value. The driver converts this
485: * to a SQL INTEGER value when it sends it to the database.
486: *
487: * @param parameterIndex the first parameter is 1, the second is 2, ...
488: * @param x the parameter value
489: * @exception SQLException if a database-access error occurs.
490: */
491: public void setInt(int index, int value) throws SQLException {
492: setParam(index, new Integer(value), java.sql.Types.INTEGER, -1);
493: }
494:
495: /**
496: * Set a parameter to a Java long value. The driver converts this
497: * to a SQL BIGINT value when it sends it to the database.
498: *
499: * @param parameterIndex the first parameter is 1, the second is 2, ...
500: * @param x the parameter value
501: * @exception SQLException if a database-access error occurs.
502: */
503: public void setLong(int parameterIndex, long value)
504: throws SQLException {
505: setParam(parameterIndex, new Long(value),
506: java.sql.Types.BIGINT, -1);
507: }
508:
509: /**
510: * Set a parameter to SQL NULL.
511: *
512: * <P><B>Note:</B> You must specify the parameter's SQL type.
513: *
514: * @param parameterIndex the first parameter is 1, the second is 2, ...
515: * @param sqlType SQL type code defined by java.sql.Types
516: * @exception SQLException if a database-access error occurs.
517: */
518: public void setNull(int index, int type) throws SQLException {
519: setParam(index, null, type, -1);
520: }
521:
522: /**
523: * <p>Set the value of a parameter using an object; use the
524: * java.lang equivalent objects for integral values.
525: *
526: * <p>The JDBC specification specifies a standard mapping from
527: * Java Object types to SQL types. The given argument java object
528: * will be converted to the corresponding SQL type before being
529: * sent to the database.
530: *
531: * <p>Note that this method may be used to pass datatabase
532: * specific abstract data types, by using a Driver specific Java
533: * type.
534: *
535: * @param parameterIndex The first parameter is 1, the second is 2, ...
536: * @param x The object containing the input parameter value
537: * @exception SQLException if a database-access error occurs.
538: */
539: public void setObject(int parameterIndex, Object x)
540: throws SQLException {
541: //throw new SQLException("Not implemented");
542: String xname = x.getClass().getName();
543: if (xname.equalsIgnoreCase("java.math.BigDecimal")) {
544: BigDecimal b = new BigDecimal(x.toString());
545: Float f = new Float(b.floatValue());
546: setParam(parameterIndex, new Float(b.floatValue()), 7, -1);
547: } else if (xname.equalsIgnoreCase("java.lang.Boolean")) {
548: int[] i = new int[1];
549: Boolean b = new Boolean(x.toString());
550: if (b.equals("true")) {
551: i[0] = 1;
552: setParam(parameterIndex, i, -7, -1);
553: } else {
554: i[0] = 1;
555: setParam(parameterIndex, i, -7, -1);
556: }
557: } else if (xname.equalsIgnoreCase("java.lang.Byte")) {
558: Byte by = new Byte(x.toString());
559: byte[] b = new byte[1];
560: b[0] = by.byteValue();
561: setParam(parameterIndex, b, -3, -1);
562: } else if (xname.equalsIgnoreCase("java.sql.Date")) {
563: Date d = Date.valueOf(x.toString());
564: setParam(parameterIndex, new Date(d.getYear(),
565: d.getMonth(), d.getDate()), 91, -1);
566: } else if (xname.equalsIgnoreCase("java.lang.Double")) {
567: setParam(parameterIndex, new Double(x.toString()), 8, -1);
568: } else if (xname.equalsIgnoreCase("java.lang.Float")) {
569: setParam(parameterIndex, new Float(x.toString()), 6, -1);
570: } else if (xname.equalsIgnoreCase("java.lang.Integer")) {
571: setParam(parameterIndex, new Integer(x.toString()), 4, -1);
572: } else if (xname.equalsIgnoreCase("java.lang.Long")) {
573: setParam(parameterIndex, new Long(x.toString()), -5, -1);
574: } else if (xname.equalsIgnoreCase("java.lang.Short")) {
575: setParam(parameterIndex, new Integer(x.toString()), 5, -1);
576: } else if (xname.equalsIgnoreCase("java.lang.String")) {
577: setParam(parameterIndex, x.toString(), 12, x.toString()
578: .length());
579: } else if (xname.equalsIgnoreCase("java.sql.Time")) {
580: Time t = Time.valueOf(x.toString());
581: setParam(parameterIndex, t, 92, -1);
582: } else if (xname.equalsIgnoreCase("java.sql.Timestamp")) {
583: Timestamp ts = Timestamp.valueOf(x.toString());
584: setParam(parameterIndex, ts, 93, -1);
585: } else {
586: throw new SQLException("No validate Object type1.");
587: }
588: }
589:
590: /**
591: * This method is like setObject above, but assumes a scale of zero.
592: *
593: * @exception SQLException if a database-access error occurs.
594: */
595: public void setObject(int parameterIndex, Object x,
596: int targetSqlType) throws SQLException {
597: //throw new SQLException("Not implemented");
598: if (targetSqlType == 7) {
599: BigDecimal b = new BigDecimal(x.toString());
600: Float f = new Float(b.floatValue());
601: setParam(parameterIndex, new Float(b.floatValue()), 7, -1);
602: } else if (targetSqlType == -7) {
603: int[] i = new int[1];
604: Boolean b = new Boolean(x.toString());
605: if (b.equals("true")) {
606: i[0] = 1;
607: setParam(parameterIndex, i, -7, -1);
608: } else {
609: i[0] = 1;
610: setParam(parameterIndex, i, -7, -1);
611: }
612: } else if (targetSqlType == -3) {
613: Byte by = new Byte(x.toString());
614: byte[] b = new byte[1];
615: b[0] = by.byteValue();
616: setParam(parameterIndex, b, -3, -1);
617: } else if (targetSqlType == 91) {
618: Date d = Date.valueOf(x.toString());
619: setParam(parameterIndex, new Date(d.getYear(),
620: d.getMonth(), d.getDate()), 91, -1);
621: } else if (targetSqlType == 8) {
622: setParam(parameterIndex, new Double(x.toString()), 8, -1);
623: } else if (targetSqlType == 6) {
624: setParam(parameterIndex, new Float(x.toString()), 6, -1);
625: } else if (targetSqlType == 4) {
626: setParam(parameterIndex, new Integer(x.toString()), 4, -1);
627: } else if (targetSqlType == -5) {
628: setParam(parameterIndex, new Long(x.toString()), -5, -1);
629: } else if (targetSqlType == 5) {
630: setParam(parameterIndex, new Integer(x.toString()), 5, -1);
631: } else if (targetSqlType == 12) {
632: setParam(parameterIndex, x.toString(), 12, x.toString()
633: .length());
634: } else if (targetSqlType == 92) {
635: Time t = Time.valueOf(x.toString());
636: setParam(parameterIndex, t, 92, -1);
637: } else if (targetSqlType == 93) {
638: Timestamp ts = Timestamp.valueOf(x.toString());
639: setParam(parameterIndex, ts, 93, -1);
640: } else {
641: throw new SQLException("No validate Object type2.");
642: }
643: }
644:
645: /**
646: * initialize one element in the parameter list
647: *
648: * @param index (in-only) index (first column is 1) of the parameter
649: * @param value (in-only)
650: * @param type (in-only) JDBC type
651: */
652: private void setParam(int index, Object value, int type,
653: int strLength) throws SQLException {
654: if (index < 1) {
655: throw new SQLException("Invalid Parameter index " + index
656: + ". JDBC indexes start at 1.");
657: }
658: if (index > parameterList.length) {
659: throw new SQLException("Invalid Parameter index " + index
660: + ". This statement only has "
661: + parameterList.length + " parameters");
662: }
663:
664: // JDBC indexes start at 1, java array indexes start at 0 :-(
665: index--;
666:
667: parameterList[index].type = type;
668: parameterList[index].isSet = true;
669: parameterList[index].value = value;
670:
671: parameterList[index].maxLength = strLength;
672: } // setParam()
673:
674: //----------------------------------------------------------------------
675: // Advanced features:
676:
677: /**
678: * <p>Set the value of a parameter using an object; use the
679: * java.lang equivalent objects for integral values.
680: *
681: * <p>The given Java object will be converted to the targetSqlType
682: * before being sent to the database.
683: *
684: * <p>Note that this method may be used to pass datatabase-
685: * specific abstract data types. This is done by using a Driver-
686: * specific Java type and using a targetSqlType of
687: * java.sql.types.OTHER.
688: *
689: * @param parameterIndex The first parameter is 1, the second is 2, ...
690: * @param x The object containing the input parameter value
691: * @param targetSqlType The SQL type (as defined in java.sql.Types) to be
692: * sent to the database. The scale argument may further qualify this type.
693: * @param scale For java.sql.Types.DECIMAL or java.sql.Types.NUMERIC types
694: * this is the number of digits after the decimal. For all other
695: * types this value will be ignored,
696: * @exception SQLException if a database-access error occurs.
697: * @see Types
698: */
699: public void setObject(int parameterIndex, Object x,
700: int targetSqlType, int scale) throws SQLException {
701: //throw new SQLException("Not implemented");
702: if (targetSqlType == 7) {
703: BigDecimal b = new BigDecimal(x.toString());
704: b.setScale(scale);
705: Float f = new Float(b.toString());
706: setParam(parameterIndex, new Float(b.floatValue()), 7, -1);
707: } else if (targetSqlType == -7) {
708: int[] i = new int[1];
709: Boolean b = new Boolean(x.toString());
710: if (b.equals("true")) {
711: i[0] = 1;
712: setParam(parameterIndex, i, -7, -1);
713: } else {
714: i[0] = 1;
715: setParam(parameterIndex, i, -7, -1);
716: }
717: } else if (targetSqlType == -1) {
718: int len = x.toString().length();
719: if (len == 0) {
720: // In SQL trailing spaces aren't significant. SQLServer uses
721: // strings with a single space (" ") to represent a zero length
722: // string.
723: setParam(parameterIndex, " ", java.sql.Types.VARCHAR, 1);
724: } else {
725: setParam(parameterIndex, x.toString(),
726: java.sql.Types.VARCHAR, len);
727: }
728: } else if (targetSqlType == -3) {
729: Byte by = new Byte(x.toString());
730: byte[] b = new byte[1];
731: b[0] = by.byteValue();
732: setParam(parameterIndex, b, -3, -1);
733: } else if (targetSqlType == 91) {
734: Date d = Date.valueOf(x.toString());
735: setParam(parameterIndex, new Date(d.getYear(),
736: d.getMonth(), d.getDate()), 91, -1);
737: } else if (targetSqlType == 8) {
738: setParam(parameterIndex, new Double(x.toString()), 8, -1);
739: } else if (targetSqlType == 6) {
740: setParam(parameterIndex, new Float(x.toString()), 6, -1);
741: } else if (targetSqlType == 4) {
742: setParam(parameterIndex, new Integer(x.toString()), 4, -1);
743: } else if (targetSqlType == -5) {
744: setParam(parameterIndex, new Long(x.toString()), -5, -1);
745: } else if (targetSqlType == 5) {
746: setParam(parameterIndex, new Integer(x.toString()), 5, -1);
747: } else if (targetSqlType == 12) {
748: setParam(parameterIndex, x.toString(), 12, x.toString()
749: .length());
750: } else if (targetSqlType == 92) {
751: Time t = Time.valueOf(x.toString());
752: setParam(parameterIndex, t, 92, -1);
753: } else if (targetSqlType == 93) {
754: Timestamp ts = Timestamp.valueOf(x.toString());
755: setParam(parameterIndex, ts, 93, -1);
756: } else {
757: throw new SQLException("No validate Object type3."
758: + targetSqlType);
759: }
760: }
761:
762: /**
763: * Set a parameter to a Java short value. The driver converts this
764: * to a SQL SMALLINT value when it sends it to the database.
765: *
766: * @param parameterIndex the first parameter is 1, the second is 2, ...
767: * @param x the parameter value
768: * @exception SQLException if a database-access error occurs.
769: */
770: public void setShort(int index, short value) throws SQLException {
771: setParam(index, new Integer(value), java.sql.Types.SMALLINT, -1);
772: }
773:
774: /**
775: * Set a parameter to a Java String value. The driver converts this
776: * to a SQL VARCHAR or LONGVARCHAR value (depending on the arguments
777: * size relative to the driver's limits on VARCHARs) when it sends
778: * it to the database.
779: *
780: * @param parameterIndex the first parameter is 1, the second is 2, ...
781: * @param x the parameter value
782: * @exception SQLException if a database-access error occurs.
783: */
784: public void setString(int index, String str) throws SQLException {
785: int len = str.length();
786: if (len == 0) {
787: // In SQL trailing spaces aren't significant. SQLServer uses
788: // strings with a single space (" ") to represent a zero length
789: // string.
790: setParam(index, " ", java.sql.Types.VARCHAR, 1);
791: } else {
792: setParam(index, str, java.sql.Types.VARCHAR, len);
793: }
794: }
795:
796: /**
797: * Set a parameter to a java.sql.Time value. The driver converts this
798: * to a SQL TIME value when it sends it to the database.
799: *
800: * @param parameterIndex the first parameter is 1, the second is 2, ...
801: * @param x the parameter value
802: * @exception SQLException if a database-access error occurs.
803: */
804: public void setTime(int parameterIndex, java.sql.Time x)
805: throws SQLException {
806: //throw new SQLException("Not implemented");
807: setParam(parameterIndex, new Time(x.getHours(), x.getMinutes(),
808: x.getSeconds()), 92, -1);
809: }
810:
811: /**
812: * Set a parameter to a java.sql.Timestamp value. The driver
813: * converts this to a SQL TIMESTAMP value when it sends it to the
814: * database.
815: *
816: * @param parameterIndex the first parameter is 1, the second is 2, ...
817: * @param x the parameter value
818: * @exception SQLException if a database-access error occurs.
819: */
820: public void setTimestamp(int index, java.sql.Timestamp value)
821: throws SQLException {
822: setParam(index, value, java.sql.Types.TIMESTAMP, -1);
823: }
824:
825: /**
826: * When a very large UNICODE value is input to a LONGVARCHAR
827: * parameter, it may be more practical to send it via a
828: * java.io.InputStream. JDBC will read the data from the stream
829: * as needed, until it reaches end-of-file. The JDBC driver will
830: * do any necessary conversion from UNICODE to the database char format.
831: *
832: * <P><B>Note:</B> This stream object can either be a standard
833: * Java stream object or your own subclass that implements the
834: * standard interface.
835: *
836: * @param parameterIndex the first parameter is 1, the second is 2, ...
837: * @param x the java input stream which contains the
838: * UNICODE parameter value
839: * @param length the number of bytes in the stream
840: * @exception SQLException if a database-access error occurs.
841: */
842: public void setUnicodeStream(int parameterIndex,
843: java.io.InputStream x, int length) throws SQLException {
844: throw new SQLException("Not implemented");
845: }
846:
847: static public void main(String args[])
848: throws java.lang.ClassNotFoundException,
849: java.lang.IllegalAccessException,
850: java.lang.InstantiationException, SQLException {
851:
852: java.sql.PreparedStatement stmt;
853: String query = null;
854: String url = url = "" + "jdbc:freetds:" + "//" + "kap" + "/"
855: + "pubs";
856:
857: Class.forName("com.internetcds.jdbc.tds.Driver").newInstance();
858: java.sql.Connection connection;
859: connection = DriverManager.getConnection(url, "testuser",
860: "password");
861:
862: stmt = connection
863: .prepareStatement(""
864: + "select price, title_id, title, price*ytd_sales gross from titles"
865: + " where title like ?");
866: stmt.setString(1, "The%");
867: java.sql.ResultSet rs = stmt.executeQuery();
868:
869: while (rs.next()) {
870: float price = rs.getFloat("price");
871: if (rs.wasNull()) {
872: System.out.println("price: null");
873: } else {
874: System.out.println("price: " + price);
875: }
876:
877: String title_id = rs.getString("title_id");
878: String title = rs.getString("title");
879: float gross = rs.getFloat("gross");
880:
881: System.out.println("id: " + title_id);
882: System.out.println("name: " + title);
883: System.out.println("gross: " + gross);
884: System.out.println("");
885: }
886: }
887: }
|