Java > Open Source Codes > com > internetcds > jdbc > tds > PreparedStatement_base


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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags