1
package org.postgresql.jdbc1;
3
// IMPORTANT NOTE: This file implements the JDBC 1 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 2 class in the
6
// org.postgresql.jdbc2 package.
13
import org.postgresql.largeobject.*;
14
import org.postgresql.util.*;
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
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.
26
* <p>If arbitrary parameter type conversions are required, then the setObject
27
* method should be used with a target SQL type.
30
* @see java.sql.PreparedStatement
32
public class PreparedStatement extends Statement implements java.sql.PreparedStatement
35
String[] templateStrings;
37
Connection connection;
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.
45
* @param conn the instanatiating connection
46
* @param sql the SQL statement with ? for IN markers
47
* @exception SQLException if something bad occurs
49
public PreparedStatement(Connection connection, String sql) throws SQLException
53
Vector v = new Vector();
54
boolean inQuotes = false;
55
int lastParmEnd = 0, i;
58
this.connection = connection;
59
for (i = 0; i < sql.length(); ++i)
61
int c = sql.charAt(i);
65
if (c == '?' && !inQuotes)
67
v.addElement(sql.substring (lastParmEnd, i));
71
v.addElement(sql.substring (lastParmEnd, sql.length()));
73
templateStrings = new String[v.size()];
74
inStrings = new String[v.size() - 1];
77
for (i = 0 ; i < templateStrings.length; ++i)
78
templateStrings[i] = (String)v.elementAt(i);
82
* A Prepared SQL query is executed and its ResultSet is returned
84
* @return a ResultSet that contains the data produced by the
85
* * query - never null
86
* @exception SQLException if a database access error occurs
88
public java.sql.ResultSet executeQuery() throws SQLException
90
StringBuffer s = new StringBuffer();
93
for (i = 0 ; i < inStrings.length ; ++i)
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]);
100
s.append(templateStrings[inStrings.length]);
101
return super.executeQuery(s.toString()); // in Statement class
105
* Execute a SQL INSERT, UPDATE or DELETE statement. In addition,
106
* SQL statements that return nothing such as SQL DDL statements can
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
113
public int executeUpdate() throws SQLException
115
StringBuffer s = new StringBuffer();
118
for (i = 0 ; i < inStrings.length ; ++i)
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]);
125
s.append(templateStrings[inStrings.length]);
126
return super.executeUpdate(s.toString()); // in Statement class
130
* Set a parameter to SQL NULL
132
* <p><B>Note:</B> You must specify the parameters SQL type (although
133
* PostgreSQL ignores it)
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
139
public void setNull(int parameterIndex, int sqlType) throws SQLException
141
set(parameterIndex, "null");
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.
148
* @param parameterIndex the first parameter is 1...
149
* @param x the parameter value
150
* @exception SQLException if a database access error occurs
152
public void setBoolean(int parameterIndex, boolean x) throws SQLException
154
set(parameterIndex, x ? "'t'" : "'f'");
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.
161
* @param parameterIndex the first parameter is 1...
162
* @param x the parameter value
163
* @exception SQLException if a database access error occurs
165
public void setByte(int parameterIndex, byte x) throws SQLException
167
set(parameterIndex, Integer.toString(x));
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.
174
* @param parameterIndex the first parameter is 1...
175
* @param x the parameter value
176
* @exception SQLException if a database access error occurs
178
public void setShort(int parameterIndex, short x) throws SQLException
180
set(parameterIndex, Integer.toString(x));
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.
187
* @param parameterIndex the first parameter is 1...
188
* @param x the parameter value
189
* @exception SQLException if a database access error occurs
191
public void setInt(int parameterIndex, int x) throws SQLException
193
set(parameterIndex, Integer.toString(x));
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.
200
* @param parameterIndex the first parameter is 1...
201
* @param x the parameter value
202
* @exception SQLException if a database access error occurs
204
public void setLong(int parameterIndex, long x) throws SQLException
206
set(parameterIndex, Long.toString(x));
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.
213
* @param parameterIndex the first parameter is 1...
214
* @param x the parameter value
215
* @exception SQLException if a database access error occurs
217
public void setFloat(int parameterIndex, float x) throws SQLException
219
set(parameterIndex, Float.toString(x));
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
226
* @param parameterIndex the first parameter is 1...
227
* @param x the parameter value
228
* @exception SQLException if a database access error occurs
230
public void setDouble(int parameterIndex, double x) throws SQLException
232
set(parameterIndex, Double.toString(x));
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
240
* @param parameterIndex the first parameter is 1...
241
* @param x the parameter value
242
* @exception SQLException if a database access error occurs
244
public void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException
246
set(parameterIndex, x.toString());
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
255
* @param parameterIndex the first parameter is 1...
256
* @param x the parameter value
257
* @exception SQLException if a database access error occurs
259
public void setString(int parameterIndex, String x) throws SQLException
261
// if the passed string is null, then set this column to null
263
setNull(parameterIndex, Types.OTHER);
266
StringBuffer b = new StringBuffer();
270
for (i = 0 ; i < x.length() ; ++i)
272
char c = x.charAt(i);
273
if (c == '\\' || c == '\'')
274
b.append((char)'\\');
278
set(parameterIndex, b.toString());
283
* Set a parameter to a Java array of bytes. The driver converts this
284
* to a SQL VARBINARY or LONGVARBINARY (depending on the argument's
285
* size relative to the driver's limits on VARBINARYs) when it sends
286
* it to the database.
288
* <p>Implementation note:
289
* <br>With org.postgresql, this creates a large object, and stores the
290
* objects oid in this column.
292
* @param parameterIndex the first parameter is 1...
293
* @param x the parameter value
294
* @exception SQLException if a database access error occurs
296
public void setBytes(int parameterIndex, byte x[]) throws SQLException
298
if (connection.haveMinimumCompatibleVersion("7.2"))
300
//Version 7.2 supports the bytea datatype for byte arrays
303
setNull(parameterIndex, Types.OTHER);
307
setString(parameterIndex, PGbytea.toPGString(x));
312
//Version 7.1 and earlier support done as LargeObjects
313
LargeObjectManager lom = connection.getLargeObjectAPI();
314
int oid = lom.create();
315
LargeObject lob = lom.open(oid);
318
setInt(parameterIndex, oid);
323
* Set a parameter to a java.sql.Date value. The driver converts this
324
* to a SQL DATE value when it sends it to the database.
326
* @param parameterIndex the first parameter is 1...
327
* @param x the parameter value
328
* @exception SQLException if a database access error occurs
330
public void setDate(int parameterIndex, java.sql.Date x) throws SQLException
334
setNull(parameterIndex, Types.OTHER);
338
SimpleDateFormat df = new SimpleDateFormat("''yyyy-MM-dd''");
339
set(parameterIndex, df.format(x));
341
// The above is how the date should be handled.
343
// However, in JDK's prior to 1.1.6 (confirmed with the
344
// Linux jdk1.1.3 and the Win95 JRE1.1.5), SimpleDateFormat seems
345
// to format a date to the previous day. So the fix is to add a day
346
// before formatting.
348
// PS: 86400000 is one day
350
//set(parameterIndex, df.format(new java.util.Date(x.getTime()+86400000)));
354
* Set a parameter to a java.sql.Time value. The driver converts
355
* this to a SQL TIME value when it sends it to the database.
357
* @param parameterIndex the first parameter is 1...));
358
* @param x the parameter value
359
* @exception SQLException if a database access error occurs
361
public void setTime(int parameterIndex, Time x) throws SQLException
365
setNull(parameterIndex, Types.OTHER);
369
set(parameterIndex, "'" + x.toString() + "'");
374
* Set a parameter to a java.sql.Timestamp value. The driver converts
375
* this to a SQL TIMESTAMP value when it sends it to the database.
377
* @param parameterIndex the first parameter is 1...
378
* @param x the parameter value
379
* @exception SQLException if a database access error occurs
381
public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException
385
setNull(parameterIndex, Types.OTHER);
389
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
390
df.setTimeZone(TimeZone.getTimeZone("GMT"));
392
// Make decimal from nanos.
393
StringBuffer decimal = new StringBuffer("000000000"); // max nanos length
394
String nanos = String.valueOf(x.getNanos());
395
decimal.setLength(decimal.length() - nanos.length());
396
decimal.append(nanos);
397
if (! connection.haveMinimumServerVersion("7.2")) {
398
// Because 7.1 include bug that "hh:mm:59.999" becomes "hh:mm:60.00".
399
decimal.setLength(2);
402
StringBuffer strBuf = new StringBuffer("'");
403
strBuf.append(df.format(x)).append('.').append(decimal).append("+00'");
404
set(parameterIndex, strBuf.toString());
409
* When a very large ASCII value is input to a LONGVARCHAR parameter,
410
* it may be more practical to send it via a java.io.InputStream.
411
* JDBC will read the data from the stream as needed, until it reaches
412
* end-of-file. The JDBC driver will do any necessary conversion from
413
* ASCII to the database char format.
415
* <P><B>Note:</B> This stream object can either be a standard Java
416
* stream object or your own subclass that implements the standard
419
* @param parameterIndex the first parameter is 1...
420
* @param x the parameter value
421
* @param length the number of bytes in the stream
422
* @exception SQLException if a database access error occurs
424
public void setAsciiStream(int parameterIndex, InputStream x, int length) throws SQLException
426
if (connection.haveMinimumCompatibleVersion("7.2"))
428
//Version 7.2 supports AsciiStream for all PG text types (char, varchar, text)
429
//As the spec/javadoc for this method indicate this is to be used for
430
//large String values (i.e. LONGVARCHAR) PG doesn't have a separate
431
//long varchar datatype, but with toast all text datatypes are capable of
432
//handling very large values. Thus the implementation ends up calling
433
//setString() since there is no current way to stream the value to the server
436
InputStreamReader l_inStream = new InputStreamReader(x, "ASCII");
437
char[] l_chars = new char[length];
438
int l_charsRead = l_inStream.read(l_chars, 0, length);
439
setString(parameterIndex, new String(l_chars, 0, l_charsRead));
441
catch (UnsupportedEncodingException l_uee)
443
throw new PSQLException("postgresql.unusual", l_uee);
445
catch (IOException l_ioe)
447
throw new PSQLException("postgresql.unusual", l_ioe);
452
//Version 7.1 supported only LargeObjects by treating everything
454
setBinaryStream(parameterIndex, x, length);
459
* When a very large Unicode value is input to a LONGVARCHAR parameter,
460
* it may be more practical to send it via a java.io.InputStream.
461
* JDBC will read the data from the stream as needed, until it reaches
462
* end-of-file. The JDBC driver will do any necessary conversion from
463
* UNICODE to the database char format.
465
* <P><B>Note:</B> This stream object can either be a standard Java
466
* stream object or your own subclass that implements the standard
469
* @param parameterIndex the first parameter is 1...
470
* @param x the parameter value
471
* @exception SQLException if a database access error occurs
473
public void setUnicodeStream(int parameterIndex, InputStream x, int length) throws SQLException
475
if (connection.haveMinimumCompatibleVersion("7.2"))
477
//Version 7.2 supports AsciiStream for all PG text types (char, varchar, text)
478
//As the spec/javadoc for this method indicate this is to be used for
479
//large String values (i.e. LONGVARCHAR) PG doesn't have a separate
480
//long varchar datatype, but with toast all text datatypes are capable of
481
//handling very large values. Thus the implementation ends up calling
482
//setString() since there is no current way to stream the value to the server
485
InputStreamReader l_inStream = new InputStreamReader(x, "UTF-8");
486
char[] l_chars = new char[length];
487
int l_charsRead = l_inStream.read(l_chars, 0, length);
488
setString(parameterIndex, new String(l_chars, 0, l_charsRead));
490
catch (UnsupportedEncodingException l_uee)
492
throw new PSQLException("postgresql.unusual", l_uee);
494
catch (IOException l_ioe)
496
throw new PSQLException("postgresql.unusual", l_ioe);
501
//Version 7.1 supported only LargeObjects by treating everything
503
setBinaryStream(parameterIndex, x, length);
508
* When a very large binary value is input to a LONGVARBINARY parameter,
509
* it may be more practical to send it via a java.io.InputStream.
510
* JDBC will read the data from the stream as needed, until it reaches
513
* <P><B>Note:</B> This stream object can either be a standard Java
514
* stream object or your own subclass that implements the standard
517
* @param parameterIndex the first parameter is 1...
518
* @param x the parameter value
519
* @exception SQLException if a database access error occurs
521
public void setBinaryStream(int parameterIndex, InputStream x, int length) throws SQLException
523
if (connection.haveMinimumCompatibleVersion("7.2"))
525
//Version 7.2 supports BinaryStream for for the PG bytea type
526
//As the spec/javadoc for this method indicate this is to be used for
527
//large binary values (i.e. LONGVARBINARY) PG doesn't have a separate
528
//long binary datatype, but with toast the bytea datatype is capable of
529
//handling very large values. Thus the implementation ends up calling
530
//setBytes() since there is no current way to stream the value to the server
531
byte[] l_bytes = new byte[length];
535
l_bytesRead = x.read(l_bytes, 0, length);
537
catch (IOException l_ioe)
539
throw new PSQLException("postgresql.unusual", l_ioe);
541
if (l_bytesRead == length)
543
setBytes(parameterIndex, l_bytes);
547
//the stream contained less data than they said
548
byte[] l_bytes2 = new byte[l_bytesRead];
549
System.arraycopy(l_bytes, 0, l_bytes2, 0, l_bytesRead);
550
setBytes(parameterIndex, l_bytes2);
555
//Version 7.1 only supported streams for LargeObjects
556
//but the jdbc spec indicates that streams should be
557
//available for LONGVARBINARY instead
558
LargeObjectManager lom = connection.getLargeObjectAPI();
559
int oid = lom.create();
560
LargeObject lob = lom.open(oid);
561
OutputStream los = lob.getOutputStream();
564
// could be buffered, but then the OutputStream returned by LargeObject
565
// is buffered internally anyhow, so there would be no performance
566
// boost gained, if anything it would be worse!
569
while (c > -1 && p < length)
577
catch (IOException se)
579
throw new PSQLException("postgresql.unusual", se);
581
// lob is closed by the stream so don't call lob.close()
582
setInt(parameterIndex, oid);
587
* In general, parameter values remain in force for repeated used of a
588
* Statement. Setting a parameter value automatically clears its
589
* previous value. However, in coms cases, it is useful to immediately
590
* release the resources used by the current parameter values; this
591
* can be done by calling clearParameters
593
* @exception SQLException if a database access error occurs
595
public void clearParameters() throws SQLException
599
for (i = 0 ; i < inStrings.length ; i++)
604
* Set the value of a parameter using an object; use the java.lang
605
* equivalent objects for integral values.
607
* <P>The given Java object will be converted to the targetSqlType before
608
* being sent to the database.
610
* <P>note that this method may be used to pass database-specific
611
* abstract data types. This is done by using a Driver-specific
612
* Java type and using a targetSqlType of java.sql.Types.OTHER
614
* @param parameterIndex the first parameter is 1...
615
* @param x the object containing the input parameter value
616
* @param targetSqlType The SQL type to be send to the database
617
* @param scale For java.sql.Types.DECIMAL or java.sql.Types.NUMERIC
618
* * types this is the number of digits after the decimal. For
619
* * all other types this value will be ignored.
620
* @exception SQLException if a database access error occurs
622
public void setObject(int parameterIndex, Object x, int targetSqlType, int scale) throws SQLException
626
setNull(parameterIndex, Types.OTHER);
629
switch (targetSqlType)
640
if (x instanceof Boolean)
641
set(parameterIndex, ((Boolean)x).booleanValue() ? "1" : "0");
643
set(parameterIndex, x.toString());
647
case Types.LONGVARCHAR:
648
setString(parameterIndex, x.toString());
651
setDate(parameterIndex, (java.sql.Date)x);
654
setTime(parameterIndex, (Time)x);
656
case Types.TIMESTAMP:
657
setTimestamp(parameterIndex, (Timestamp)x);
660
if (x instanceof Boolean)
662
set(parameterIndex, ((Boolean)x).booleanValue() ? "TRUE" : "FALSE");
666
throw new PSQLException("postgresql.prep.type");
670
case Types.VARBINARY:
671
setObject(parameterIndex, x);
674
setString(parameterIndex, ((PGobject)x).getValue());
677
throw new PSQLException("postgresql.prep.type");
681
public void setObject(int parameterIndex, Object x, int targetSqlType) throws SQLException
683
setObject(parameterIndex, x, targetSqlType, 0);
687
* This stores an Object into a parameter.
688
* <p>New for 6.4, if the object is not recognised, but it is
689
* Serializable, then the object is serialised using the
690
* org.postgresql.util.Serialize class.
692
public void setObject(int parameterIndex, Object x) throws SQLException
696
setNull(parameterIndex, Types.OTHER);
699
if (x instanceof String)
700
setString(parameterIndex, (String)x);
701
else if (x instanceof BigDecimal)
702
setBigDecimal(parameterIndex, (BigDecimal)x);
703
else if (x instanceof Short)
704
setShort(parameterIndex, ((Short)x).shortValue());
705
else if (x instanceof Integer)
706
setInt(parameterIndex, ((Integer)x).intValue());
707
else if (x instanceof Long)
708
setLong(parameterIndex, ((Long)x).longValue());
709
else if (x instanceof Float)
710
setFloat(parameterIndex, ((Float)x).floatValue());
711
else if (x instanceof Double)
712
setDouble(parameterIndex, ((Double)x).doubleValue());
713
else if (x instanceof byte[])
714
setBytes(parameterIndex, (byte[])x);
715
else if (x instanceof java.sql.Date)
716
setDate(parameterIndex, (java.sql.Date)x);
717
else if (x instanceof Time)
718
setTime(parameterIndex, (Time)x);
719
else if (x instanceof Timestamp)
720
setTimestamp(parameterIndex, (Timestamp)x);
721
else if (x instanceof Boolean)
722
setBoolean(parameterIndex, ((Boolean)x).booleanValue());
723
else if (x instanceof PGobject)
724
setString(parameterIndex, ((PGobject)x).getValue());
726
setLong(parameterIndex, connection.storeObject(x));
730
* Some prepared statements return multiple results; the execute method
731
* handles these complex statements as well as the simpler form of
732
* statements handled by executeQuery and executeUpdate
734
* @return true if the next result is a ResultSet; false if it is an
735
* * update count or there are no more results
736
* @exception SQLException if a database access error occurs
738
public boolean execute() throws SQLException
740
StringBuffer s = new StringBuffer();
743
for (i = 0 ; i < inStrings.length ; ++i)
745
if (inStrings[i] == null)
746
throw new PSQLException("postgresql.prep.param", new Integer(i + 1));
747
s.append (templateStrings[i]);
748
s.append (inStrings[i]);
750
s.append(templateStrings[inStrings.length]);
751
return super.execute(s.toString()); // in Statement class
755
* Returns the SQL statement with the current template values
758
public String toString()
760
StringBuffer s = new StringBuffer();
763
for (i = 0 ; i < inStrings.length ; ++i)
765
if (inStrings[i] == null)
768
s.append (templateStrings[i]);
769
s.append (inStrings[i]);
771
s.append(templateStrings[inStrings.length]);
775
// **************************************************************
776
// END OF PUBLIC INTERFACE
777
// **************************************************************
780
* There are a lot of setXXX classes which all basically do
781
* the same thing. We need a method which actually does the
784
* @param paramIndex the index into the inString
785
* @param s a string to be stored
786
* @exception SQLException if something goes wrong
788
private void set(int paramIndex, String s) throws SQLException
790
if (paramIndex < 1 || paramIndex > inStrings.length)
791
throw new PSQLException("postgresql.prep.range");
792
inStrings[paramIndex - 1] = s;
added
removed
src/interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java
