Source code: postgresql/jdbc2/PreparedStatement.java
1 package postgresql.jdbc2;
2
3 // IMPORTANT NOTE: This file implements the JDBC 2 version of the driver.
4 // If you make any modifications to this file, you must make sure that the
5 // changes are also made (if relevent) to the related JDBC 1 class in the
6 // postgresql.jdbc1 package.
7
8 import java.io.*;
9 import java.math.*;
10 import java.sql.*;
11 import java.text.*;
12 import java.util.*;
13 import postgresql.largeobject.*;
14 import postgresql.util.*;
15
16 /**
17 * A SQL Statement is pre-compiled and stored in a PreparedStatement object.
18 * This object can then be used to efficiently execute this statement multiple
19 * times.
20 *
21 * <p><B>Note:</B> The setXXX methods for setting IN parameter values must
22 * specify types that are compatible with the defined SQL type of the input
23 * parameter. For instance, if the IN parameter has SQL type Integer, then
24 * setInt should be used.
25 *
26 * <p>If arbitrary parameter type conversions are required, then the setObject
27 * method should be used with a target SQL type.
28 *
29 * @see ResultSet
30 * @see java.sql.PreparedStatement
31 */
32 public class PreparedStatement extends Statement implements java.sql.PreparedStatement
33 {
34 String sql;
35 String[] templateStrings;
36 String[] inStrings;
37 Connection connection;
38
39 /**
40 * Constructor for the PreparedStatement class.
41 * Split the SQL statement into segments - separated by the arguments.
42 * When we rebuild the thing with the arguments, we can substitute the
43 * args and join the whole thing together.
44 *
45 * @param conn the instanatiating connection
46 * @param sql the SQL statement with ? for IN markers
47 * @exception SQLException if something bad occurs
48 */
49 public PreparedStatement(Connection connection, String sql) throws SQLException
50 {
51 super(connection);
52
53 Vector v = new Vector();
54 boolean inQuotes = false;
55 int lastParmEnd = 0, i;
56
57 this.sql = sql;
58 this.connection = connection;
59 for (i = 0; i < sql.length(); ++i)
60 {
61 int c = sql.charAt(i);
62
63 if (c == '\'')
64 inQuotes = !inQuotes;
65 if (c == '?' && !inQuotes)
66 {
67 v.addElement(sql.substring (lastParmEnd, i));
68 lastParmEnd = i + 1;
69 }
70 }
71 v.addElement(sql.substring (lastParmEnd, sql.length()));
72
73 templateStrings = new String[v.size()];
74 inStrings = new String[v.size() - 1];
75 clearParameters();
76
77 for (i = 0 ; i < templateStrings.length; ++i)
78 templateStrings[i] = (String)v.elementAt(i);
79 }
80
81 /**
82 * A Prepared SQL query is executed and its ResultSet is returned
83 *
84 * @return a ResultSet that contains the data produced by the
85 * query - never null
86 * @exception SQLException if a database access error occurs
87 */
88 public java.sql.ResultSet executeQuery() throws SQLException
89 {
90 StringBuffer s = new StringBuffer();
91 int i;
92
93 for (i = 0 ; i < inStrings.length ; ++i)
94 {
95 if (inStrings[i] == null)
96 throw new PSQLException("postgresql.prep.param",new Integer(i + 1));
97 s.append (templateStrings[i]);
98 s.append (inStrings[i]);
99 }
100 s.append(templateStrings[inStrings.length]);
101 return super.executeQuery(s.toString()); // in Statement class
102 }
103
104 /**
105 * Execute a SQL INSERT, UPDATE or DELETE statement. In addition,
106 * SQL statements that return nothing such as SQL DDL statements can
107 * be executed.
108 *
109 * @return either the row count for INSERT, UPDATE or DELETE; or
110 * 0 for SQL statements that return nothing.
111 * @exception SQLException if a database access error occurs
112 */
113 public int executeUpdate() throws SQLException
114 {
115 StringBuffer s = new StringBuffer();
116 int i;
117
118 for (i = 0 ; i < inStrings.length ; ++i)
119 {
120 if (inStrings[i] == null)
121 throw new PSQLException("postgresql.prep.param",new Integer(i + 1));
122 s.append (templateStrings[i]);
123 s.append (inStrings[i]);
124 }
125 s.append(templateStrings[inStrings.length]);
126 return super.executeUpdate(s.toString()); // in Statement class
127 }
128
129 /**
130 * Set a parameter to SQL NULL
131 *
132 * <p><B>Note:</B> You must specify the parameters SQL type (although
133 * PostgreSQL ignores it)
134 *
135 * @param parameterIndex the first parameter is 1, etc...
136 * @param sqlType the SQL type code defined in java.sql.Types
137 * @exception SQLException if a database access error occurs
138 */
139 public void setNull(int parameterIndex, int sqlType) throws SQLException
140 {
141 set(parameterIndex, "null");
142 }
143
144 /**
145 * Set a parameter to a Java boolean value. The driver converts this
146 * to a SQL BIT value when it sends it to the database.
147 *
148 * @param parameterIndex the first parameter is 1...
149 * @param x the parameter value
150 * @exception SQLException if a database access error occurs
151 */
152 public void setBoolean(int parameterIndex, boolean x) throws SQLException
153 {
154 set(parameterIndex, x ? "'t'" : "'f'");
155 }
156
157 /**
158 * Set a parameter to a Java byte value. The driver converts this to
159 * a SQL TINYINT value when it sends it to the database.
160 *
161 * @param parameterIndex the first parameter is 1...
162 * @param x the parameter value
163 * @exception SQLException if a database access error occurs
164 */
165 public void setByte(int parameterIndex, byte x) throws SQLException
166 {
167 set(parameterIndex, (new Integer(x)).toString());
168 }
169
170 /**
171 * Set a parameter to a Java short value. The driver converts this
172 * to a SQL SMALLINT value when it sends it to the database.
173 *
174 * @param parameterIndex the first parameter is 1...
175 * @param x the parameter value
176 * @exception SQLException if a database access error occurs
177 */
178 public void setShort(int parameterIndex, short x) throws SQLException
179 {
180 set(parameterIndex, (new Integer(x)).toString());
181 }
182
183 /**
184 * Set a parameter to a Java int value. The driver converts this to
185 * a SQL INTEGER value when it sends it to the database.
186 *
187 * @param parameterIndex the first parameter is 1...
188 * @param x the parameter value
189 * @exception SQLException if a database access error occurs
190 */
191 public void setInt(int parameterIndex, int x) throws SQLException
192 {
193 set(parameterIndex, (new Integer(x)).toString());
194 }
195
196 /**
197 * Set a parameter to a Java long value. The driver converts this to
198 * a SQL BIGINT value when it sends it to the database.
199 *
200 * @param parameterIndex the first parameter is 1...
201 * @param x the parameter value
202 * @exception SQLException if a database access error occurs
203 */
204 public void setLong(int parameterIndex, long x) throws SQLException
205 {
206 set(parameterIndex, (new Long(x)).toString());
207 }
208
209 /**
210 * Set a parameter to a Java float value. The driver converts this
211 * to a SQL FLOAT value when it sends it to the database.
212 *
213 * @param parameterIndex the first parameter is 1...
214 * @param x the parameter value
215 * @exception SQLException if a database access error occurs
216 */
217 public void setFloat(int parameterIndex, float x) throws SQLException
218 {
219 set(parameterIndex, (new Float(x)).toString());
220 }
221
222 /**
223 * Set a parameter to a Java double value. The driver converts this
224 * to a SQL DOUBLE value when it sends it to the database
225 *
226 * @param parameterIndex the first parameter is 1...
227 * @param x the parameter value
228 * @exception SQLException if a database access error occurs
229 */
230 public void setDouble(int parameterIndex, double x) throws SQLException
231 {
232 set(parameterIndex, (new Double(x)).toString());
233 }
234
235 /**
236 * Set a parameter to a java.lang.BigDecimal value. The driver
237 * converts this to a SQL NUMERIC value when it sends it to the
238 * database.
239 *
240 * @param parameterIndex the first parameter is 1...
241 * @param x the parameter value
242 * @exception SQLException if a database access error occurs
243 */
244 public void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException
245 {
246 set(parameterIndex, x.toString());
247 }
248
249 /**
250 * Set a parameter to a Java String value. The driver converts this
251 * to a SQL VARCHAR or LONGVARCHAR value (depending on the arguments
252 * size relative to the driver's limits on VARCHARs) when it sends it
253 * to the database.
254 *
255 * @param parameterIndex the first parameter is 1...
256 * @param x the parameter value
257 * @exception SQLException if a database access error occurs
258 */
259 public void setString(int parameterIndex, String x) throws SQLException
260 {
261 // if the passed string is null, then set this column to null
262 if(x==null)
263 set(parameterIndex,"null");
264 else {
265 StringBuffer b = new StringBuffer();
266 int i;
267
268 b.append('\'');
269 for (i = 0 ; i < x.length() ; ++i)
270 {
271 char c = x.charAt(i);
272 if (c == '\\' || c == '\'')
273 b.append((char)'\\');
274 b.append(c);
275 }
276 b.append('\'');
277 set(parameterIndex, b.toString());
278 }
279 }
280
281 /**
282 * Set a parameter to a Java array of bytes. The driver converts this
283 * to a SQL VARBINARY or LONGVARBINARY (depending on the argument's
284 * size relative to the driver's limits on VARBINARYs) when it sends
285 * it to the database.
286 *
287 * <p>Implementation note:
288 * <br>With postgresql, this creates a large object, and stores the
289 * objects oid in this column.
290 *
291 * @param parameterIndex the first parameter is 1...
292 * @param x the parameter value
293 * @exception SQLException if a database access error occurs
294 */
295 public void setBytes(int parameterIndex, byte x[]) throws SQLException
296 {
297 LargeObjectManager lom = connection.getLargeObjectAPI();
298 int oid = lom.create();
299 LargeObject lob = lom.open(oid);
300 lob.write(x);
301 lob.close();
302 setInt(parameterIndex,oid);
303 }
304
305 /**
306 * Set a parameter to a java.sql.Date value. The driver converts this
307 * to a SQL DATE value when it sends it to the database.
308 *
309 * @param parameterIndex the first parameter is 1...
310 * @param x the parameter value
311 * @exception SQLException if a database access error occurs
312 */
313 public void setDate(int parameterIndex, java.sql.Date x) throws SQLException
314 {
315 SimpleDateFormat df = new SimpleDateFormat("''yyyy-MM-dd''");
316
317 set(parameterIndex, df.format(x));
318
319 // The above is how the date should be handled.
320 //
321 // However, in JDK's prior to 1.1.6 (confirmed with the
322 // Linux jdk1.1.3 and the Win95 JRE1.1.5), SimpleDateFormat seems
323 // to format a date to the previous day. So the fix is to add a day
324 // before formatting.
325 //
326 // PS: 86400000 is one day
327 //
328 //set(parameterIndex, df.format(new java.util.Date(x.getTime()+86400000)));
329 }
330
331 /**
332 * Set a parameter to a java.sql.Time value. The driver converts
333 * this to a SQL TIME value when it sends it to the database.
334 *
335 * @param parameterIndex the first parameter is 1...));
336 * @param x the parameter value
337 * @exception SQLException if a database access error occurs
338 */
339 public void setTime(int parameterIndex, Time x) throws SQLException
340 {
341 set(parameterIndex, "'" + x.toString() + "'");
342 }
343
344 /**
345 * Set a parameter to a java.sql.Timestamp value. The driver converts
346 * this to a SQL TIMESTAMP value when it sends it to the database.
347 *
348 * @param parameterIndex the first parameter is 1...
349 * @param x the parameter value
350 * @exception SQLException if a database access error occurs
351 */
352 public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException
353 {
354 set(parameterIndex, "'" + x.toString() + "'");
355 }
356
357 /**
358 * When a very large ASCII value is input to a LONGVARCHAR parameter,
359 * it may be more practical to send it via a java.io.InputStream.
360 * JDBC will read the data from the stream as needed, until it reaches
361 * end-of-file. The JDBC driver will do any necessary conversion from
362 * ASCII to the database char format.
363 *
364 * <P><B>Note:</B> This stream object can either be a standard Java
365 * stream object or your own subclass that implements the standard
366 * interface.
367 *
368 * @param parameterIndex the first parameter is 1...
369 * @param x the parameter value
370 * @param length the number of bytes in the stream
371 * @exception SQLException if a database access error occurs
372 */
373 public void setAsciiStream(int parameterIndex, InputStream x, int length) throws SQLException
374 {
375 setBinaryStream(parameterIndex, x, length);
376 }
377
378 /**
379 * When a very large Unicode value is input to a LONGVARCHAR parameter,
380 * it may be more practical to send it via a java.io.InputStream.
381 * JDBC will read the data from the stream as needed, until it reaches
382 * end-of-file. The JDBC driver will do any necessary conversion from
383 * UNICODE to the database char format.
384 *
385 * ** DEPRECIATED IN JDBC 2 **
386 *
387 * <P><B>Note:</B> This stream object can either be a standard Java
388 * stream object or your own subclass that implements the standard
389 * interface.
390 *
391 * @param parameterIndex the first parameter is 1...
392 * @param x the parameter value
393 * @exception SQLException if a database access error occurs
394 * @deprecated
395 */
396 public void setUnicodeStream(int parameterIndex, InputStream x, int length) throws SQLException
397 {
398 setBinaryStream(parameterIndex, x, length);
399 }
400
401 /**
402 * When a very large binary value is input to a LONGVARBINARY parameter,
403 * it may be more practical to send it via a java.io.InputStream.
404 * JDBC will read the data from the stream as needed, until it reaches
405 * end-of-file.
406 *
407 * <P><B>Note:</B> This stream object can either be a standard Java
408 * stream object or your own subclass that implements the standard
409 * interface.
410 *
411 * @param parameterIndex the first parameter is 1...
412 * @param x the parameter value
413 * @exception SQLException if a database access error occurs
414 */
415 public void setBinaryStream(int parameterIndex, InputStream x, int length) throws SQLException
416 {
417 throw new PSQLException("postgresql.prep.is");
418 }
419
420 /**
421 * In general, parameter values remain in force for repeated used of a
422 * Statement. Setting a parameter value automatically clears its
423 * previous value. However, in coms cases, it is useful to immediately
424 * release the resources used by the current parameter values; this
425 * can be done by calling clearParameters
426 *
427 * @exception SQLException if a database access error occurs
428 */
429 public void clearParameters() throws SQLException
430 {
431 int i;
432
433 for (i = 0 ; i < inStrings.length ; i++)
434 inStrings[i] = null;
435 }
436
437 /**
438 * Set the value of a parameter using an object; use the java.lang
439 * equivalent objects for integral values.
440 *
441 * <P>The given Java object will be converted to the targetSqlType before
442 * being sent to the database.
443 *
444 * <P>note that this method may be used to pass database-specific
445 * abstract data types. This is done by using a Driver-specific
446 * Java type and using a targetSqlType of java.sql.Types.OTHER
447 *
448 * @param parameterIndex the first parameter is 1...
449 * @param x the object containing the input parameter value
450 * @param targetSqlType The SQL type to be send to the database
451 * @param scale For java.sql.Types.DECIMAL or java.sql.Types.NUMERIC
452 * types this is the number of digits after the decimal. For
453 * all other types this value will be ignored.
454 * @exception SQLException if a database access error occurs
455 */
456 public void setObject(int parameterIndex, Object x, int targetSqlType, int scale) throws SQLException
457 {
458 switch (targetSqlType)
459 {
460 case Types.TINYINT:
461 case Types.SMALLINT:
462 case Types.INTEGER:
463 case Types.BIGINT:
464 case Types.REAL:
465 case Types.FLOAT:
466 case Types.DOUBLE:
467 case Types.DECIMAL:
468 case Types.NUMERIC:
469 if (x instanceof Boolean)
470 set(parameterIndex, ((Boolean)x).booleanValue() ? "1" : "0");
471 else
472 set(parameterIndex, x.toString());
473 break;
474 case Types.CHAR:
475 case Types.VARCHAR:
476 case Types.LONGVARCHAR:
477 setString(parameterIndex, x.toString());
478 break;
479 case Types.DATE:
480 setDate(parameterIndex, (java.sql.Date)x);
481 break;
482 case Types.TIME:
483 setTime(parameterIndex, (Time)x);
484 break;
485 case Types.TIMESTAMP:
486 setTimestamp(parameterIndex, (Timestamp)x);
487 break;
488 case Types.OTHER:
489 setString(parameterIndex, ((PGobject)x).getValue());
490 break;
491 default:
492 throw new PSQLException("postgresql.prep.type");
493 }
494 }
495
496 public void setObject(int parameterIndex, Object x, int targetSqlType) throws SQLException
497 {
498 setObject(parameterIndex, x, targetSqlType, 0);
499 }
500
501 /**
502 * This stores an Object into a parameter.
503 * <p>New for 6.4, if the object is not recognised, but it is
504 * Serializable, then the object is serialised using the
505 * postgresql.util.Serialize class.
506 */
507 public void setObject(int parameterIndex, Object x) throws SQLException
508 {
509 if (x instanceof String)
510 setString(parameterIndex, (String)x);
511 else if (x instanceof BigDecimal)
512 setBigDecimal(parameterIndex, (BigDecimal)x);
513 else if (x instanceof Short)
514 setShort(parameterIndex, ((Short)x).shortValue());
515 else if (x instanceof Integer)
516 setInt(parameterIndex, ((Integer)x).intValue());
517 else if (x instanceof Long)
518 setLong(parameterIndex, ((Long)x).longValue());
519 else if (x instanceof Float)
520 setFloat(parameterIndex, ((Float)x).floatValue());
521 else if (x instanceof Double)
522 setDouble(parameterIndex, ((Double)x).doubleValue());
523 else if (x instanceof byte[])
524 setBytes(parameterIndex, (byte[])x);
525 else if (x instanceof java.sql.Date)
526 setDate(parameterIndex, (java.sql.Date)x);
527 else if (x instanceof Time)
528 setTime(parameterIndex, (Time)x);
529 else if (x instanceof Timestamp)
530 setTimestamp(parameterIndex, (Timestamp)x);
531 else if (x instanceof Boolean)
532 setBoolean(parameterIndex, ((Boolean)x).booleanValue());
533 else if (x instanceof PGobject)
534 setString(parameterIndex, ((PGobject)x).getValue());
535 else
536 setLong(parameterIndex, connection.putObject(x));
537 }
538
539 /**
540 * Some prepared statements return multiple results; the execute method
541 * handles these complex statements as well as the simpler form of
542 * statements handled by executeQuery and executeUpdate
543 *
544 * @return true if the next result is a ResultSet; false if it is an
545 * update count or there are no more results
546 * @exception SQLException if a database access error occurs
547 */
548 public boolean execute() throws SQLException
549 {
550 StringBuffer s = new StringBuffer();
551 int i;
552
553 for (i = 0 ; i < inStrings.length ; ++i)
554 {
555 if (inStrings[i] == null)
556 throw new PSQLException("postgresql.prep.param",new Integer(i + 1));
557 s.append (templateStrings[i]);
558 s.append (inStrings[i]);
559 }
560 s.append(templateStrings[inStrings.length]);
561 return super.execute(s.toString()); // in Statement class
562 }
563
564 /**
565 * Returns the SQL statement with the current template values
566 * substituted.
567 */
568 public String toString() {
569 StringBuffer s = new StringBuffer();
570 int i;
571
572 for (i = 0 ; i < inStrings.length ; ++i)
573 {
574 if (inStrings[i] == null)
575 s.append( '?' );
576 else
577 s.append (templateStrings[i]);
578 s.append (inStrings[i]);
579 }
580 s.append(templateStrings[inStrings.length]);
581 return s.toString();
582 }
583
584 // **************************************************************
585 // END OF PUBLIC INTERFACE
586 // **************************************************************
587
588 /**
589 * There are a lot of setXXX classes which all basically do
590 * the same thing. We need a method which actually does the
591 * set for us.
592 *
593 * @param paramIndex the index into the inString
594 * @param s a string to be stored
595 * @exception SQLException if something goes wrong
596 */
597 private void set(int paramIndex, String s) throws SQLException
598 {
599 if (paramIndex < 1 || paramIndex > inStrings.length)
600 throw new PSQLException("postgresql.prep.range");
601 inStrings[paramIndex - 1] = s;
602 }
603
604 // ** JDBC 2 Extensions **
605
606 public void addBatch() throws SQLException
607 {
608 throw postgresql.Driver.notImplemented();
609 }
610
611 public java.sql.ResultSetMetaData getMetaData() throws SQLException
612 {
613 throw postgresql.Driver.notImplemented();
614 }
615
616 public void setArray(int i,Array x) throws SQLException
617 {
618 throw postgresql.Driver.notImplemented();
619 }
620
621 public void setBlob(int i,Blob x) throws SQLException
622 {
623 throw postgresql.Driver.notImplemented();
624 }
625
626 public void setCharacterStream(int i,java.io.Reader x,int length) throws SQLException
627 {
628 throw postgresql.Driver.notImplemented();
629 }
630
631 public void setClob(int i,Clob x) throws SQLException
632 {
633 throw postgresql.Driver.notImplemented();
634 }
635
636 public void setNull(int i,int t,String s) throws SQLException
637 {
638 throw postgresql.Driver.notImplemented();
639 }
640
641 public void setRef(int i,Ref x) throws SQLException
642 {
643 throw postgresql.Driver.notImplemented();
644 }
645
646 public void setDate(int i,java.sql.Date d,java.util.Calendar cal) throws SQLException
647 {
648 throw postgresql.Driver.notImplemented();
649 }
650
651 public void setTime(int i,Time t,java.util.Calendar cal) throws SQLException
652 {
653 throw postgresql.Driver.notImplemented();
654 }
655
656 public void setTimestamp(int i,Timestamp t,java.util.Calendar cal) throws SQLException
657 {
658 throw postgresql.Driver.notImplemented();
659 }
660
661 }