1
package org.postgresql.jdbc1;
3
import org.postgresql.core.BaseConnection;
4
import org.postgresql.core.BaseResultSet;
5
import org.postgresql.core.BaseStatement;
6
import org.postgresql.core.Field;
7
import org.postgresql.core.QueryExecutor;
8
import org.postgresql.largeobject.LargeObject;
9
import org.postgresql.largeobject.LargeObjectManager;
10
import org.postgresql.util.PGbytea;
11
import org.postgresql.util.PGobject;
12
import org.postgresql.util.PSQLException;
13
import org.postgresql.util.PSQLState;
14
import java.io.IOException;
15
import java.io.InputStream;
16
import java.io.InputStreamReader;
17
import java.io.OutputStream;
18
import java.io.UnsupportedEncodingException;
19
import java.math.BigDecimal;
20
import java.sql.CallableStatement;
21
import java.sql.ResultSet;
22
import java.sql.SQLException;
23
import java.sql.SQLWarning;
25
import java.sql.Timestamp;
26
import java.sql.Types;
27
import java.util.Vector;
29
/* $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/Attic/AbstractJdbc1Statement.java,v 1.41.2.8 2004/10/21 19:13:55 jurka Exp $
30
* This class defines methods of the jdbc1 specification. This class is
31
* extended by org.postgresql.jdbc2.AbstractJdbc2Statement which adds the jdbc2
32
* methods. The real Statement class (for jdbc1) is org.postgresql.jdbc1.Jdbc1Statement
34
public abstract class AbstractJdbc1Statement implements BaseStatement
36
// The connection who created us
37
protected BaseConnection connection;
39
/** The warnings chain. */
40
protected SQLWarning warnings = null;
42
/** Maximum number of rows to return, 0 = unlimited */
43
protected int maxrows = 0;
45
/** Number of rows to get in a batch. */
46
protected int fetchSize = 0;
48
/** Timeout (in seconds) for a query (not used) */
49
protected int timeout = 0;
51
protected boolean replaceProcessingEnabled = true;
53
/** The current results */
54
protected BaseResultSet result = null;
56
// Static variables for parsing SQL when replaceProcessing is true.
57
private static final short IN_SQLCODE = 0;
58
private static final short IN_STRING = 1;
59
private static final short BACKSLASH = 2;
60
private static final short ESC_TIMEDATE = 3;
62
// Some performance caches
63
private StringBuffer sbuf = new StringBuffer(32);
65
protected String[] m_sqlFragments; // Query fragments.
66
private String[] m_executeSqlFragments; // EXECUTE(...) if useServerPrepare
67
protected Object[] m_binds = new Object[0]; // Parameter values
69
protected String[] m_bindTypes = new String[0]; // Parameter types, for PREPARE(...)
70
protected String m_statementName = null; // Allocated PREPARE statement name for server-prepared statements
71
protected String m_cursorName = null; // Allocated DECLARE cursor name for cursor-based fetch
73
// Constants for allowXXX and m_isSingleStatement vars, below.
74
// The idea is to defer the cost of examining the query until we really need to know,
75
// but don't reexamine it every time thereafter.
77
private static final short UNKNOWN = 0; // Don't know yet, examine the query.
78
private static final short NO = 1; // Don't use feature
79
private static final short YES = 2; // Do use feature
81
private short m_isSingleDML = UNKNOWN; // Is the query a single SELECT/UPDATE/INSERT/DELETE?
82
private short m_isSingleSelect = UNKNOWN; // Is the query a single SELECT?
83
private short m_isSingleStatement = UNKNOWN; // Is the query a single statement?
85
private boolean m_useServerPrepare = false;
87
private boolean isClosed = false;
89
// m_preparedCount is used for naming of auto-cursors and must
90
// be synchronized so that multiple threads using the same
91
// connection don't stomp over each others cursors.
92
private static int m_preparedCount = 1;
93
private synchronized static int next_preparedCount()
95
return m_preparedCount++;
98
//Used by the callablestatement style methods
99
private static final String JDBC_SYNTAX = "{[? =] call <some_function> ([? [,?]*]) }";
100
private static final String RESULT_ALIAS = "result";
101
private String originalSql = "";
102
private boolean isFunction;
103
// functionReturnType contains the user supplied value to check
104
// testReturn contains a modified version to make it easier to
105
// check the getXXX methods..
106
private int functionReturnType;
107
private int testReturn;
108
// returnTypeSet is true when a proper call to registerOutParameter has been made
109
private boolean returnTypeSet;
110
protected Object callResult;
111
protected int maxfieldSize = 0;
113
public abstract BaseResultSet createResultSet(Field[] fields, Vector tuples, String status, int updateCount, long insertOID) throws SQLException;
115
public AbstractJdbc1Statement (BaseConnection connection)
117
this.connection = connection;
120
public AbstractJdbc1Statement (BaseConnection connection, String p_sql) throws SQLException
122
this.connection = connection;
123
parseSqlStmt(p_sql); // this allows Callable stmt to override
126
public BaseConnection getPGConnection() {
130
public String getFetchingCursorName() {
134
public int getFetchSize() {
138
protected void parseSqlStmt (String p_sql) throws SQLException
140
String l_sql = p_sql;
142
l_sql = replaceProcessing(l_sql);
144
if (this instanceof CallableStatement)
146
l_sql = modifyJdbcCall(l_sql);
149
Vector v = new Vector();
150
boolean inQuotes = false;
151
int lastParmEnd = 0, i;
153
m_isSingleSelect = m_isSingleDML = UNKNOWN;
154
m_isSingleStatement = YES;
156
for (i = 0; i < l_sql.length(); ++i)
158
int c = l_sql.charAt(i);
161
inQuotes = !inQuotes;
162
if (c == '?' && !inQuotes)
164
v.addElement(l_sql.substring (lastParmEnd, i));
167
if (c == ';' && !inQuotes)
168
m_isSingleStatement = m_isSingleSelect = m_isSingleDML = NO;
170
v.addElement(l_sql.substring (lastParmEnd, l_sql.length()));
172
m_sqlFragments = new String[v.size()];
173
m_binds = new Object[v.size() - 1];
174
m_bindTypes = new String[v.size() - 1];
176
for (i = 0 ; i < m_sqlFragments.length; ++i)
177
m_sqlFragments[i] = (String)v.elementAt(i);
182
* Deallocate resources allocated for the current query
183
* in preparation for replacing it with a new query.
185
private void deallocateQuery()
187
//If we have already created a server prepared statement, we need
188
//to deallocate the existing one
189
if (m_statementName != null)
193
connection.execSQL("DEALLOCATE " + m_statementName);
200
m_statementName = null;
201
m_cursorName = null; // automatically closed at end of txn anyway
202
m_executeSqlFragments = null;
203
m_isSingleStatement = m_isSingleSelect = m_isSingleDML = UNKNOWN;
207
* Execute a SQL statement that retruns a single ResultSet
209
* @param sql typically a static SQL SELECT statement
210
* @return a ResulSet that contains the data produced by the query
211
* @exception SQLException if a database access error occurs
213
public java.sql.ResultSet executeQuery(String p_sql) throws SQLException
217
String l_sql = replaceProcessing(p_sql);
218
m_sqlFragments = new String[] {l_sql};
219
m_binds = new Object[0];
221
return executeQuery();
225
* A Prepared SQL query is executed and its ResultSet is returned
227
* @return a ResultSet that contains the data produced by the
228
* * query - never null
229
* @exception SQLException if a database access error occurs
231
public java.sql.ResultSet executeQuery() throws SQLException
235
while (result != null && !result.reallyResultSet())
236
result = (BaseResultSet) result.getNext();
238
throw new PSQLException("postgresql.stat.noresult", PSQLState.NO_DATA);
239
return (ResultSet) result;
243
* Execute a SQL INSERT, UPDATE or DELETE statement. In addition
244
* SQL statements that return nothing such as SQL DDL statements
247
* @param sql a SQL statement
248
* @return either a row count, or 0 for SQL commands
249
* @exception SQLException if a database access error occurs
251
public int executeUpdate(String p_sql) throws SQLException
255
String l_sql = replaceProcessing(p_sql);
256
m_sqlFragments = new String[] {l_sql};
257
m_binds = new Object[0];
259
return executeUpdate();
263
* Execute a SQL INSERT, UPDATE or DELETE statement. In addition,
264
* SQL statements that return nothing such as SQL DDL statements can
267
* @return either the row count for INSERT, UPDATE or DELETE; or
268
* * 0 for SQL statements that return nothing.
269
* @exception SQLException if a database access error occurs
271
public int executeUpdate() throws SQLException
274
if (result.reallyResultSet())
275
throw new PSQLException("postgresql.stat.result");
276
return this.getUpdateCount();
280
* Execute a SQL statement that may return multiple results. We
281
* don't have to worry about this since we do not support multiple
282
* ResultSets. You can use getResultSet or getUpdateCount to
283
* retrieve the result.
285
* @param sql any SQL statement
286
* @return true if the next result is a ResulSet, false if it is
287
* an update count or there are no more results
288
* @exception SQLException if a database access error occurs
290
public boolean execute(String p_sql) throws SQLException
294
String l_sql = replaceProcessing(p_sql);
295
m_sqlFragments = new String[] {l_sql};
296
m_binds = new Object[0];
302
* Check if the current query is a single statement.
304
private boolean isSingleStatement()
306
if (m_isSingleStatement != UNKNOWN)
307
return m_isSingleStatement == YES;
309
// Crude detection of multiple statements. This could be
310
// improved by parsing the whole query for quotes, but is
311
// it worth it given that the only queries that get here are
312
// unparameterized queries?
314
for (int i = 0; i < m_sqlFragments.length; ++i) { // a bit redundant, but ..
315
if (m_sqlFragments[i].indexOf(';') != -1) {
316
m_isSingleStatement = NO;
321
m_isSingleStatement = YES;
326
* Helper for isSingleSelect() and isSingleDML(): computes values
327
* of m_isSingleDML and m_isSingleSelect.
329
private void analyzeStatementType()
331
if (!isSingleStatement()) {
332
m_isSingleSelect = m_isSingleDML = NO;
336
String compare = m_sqlFragments[0].trim().toLowerCase();
337
if (compare.startsWith("select")) {
338
m_isSingleSelect = m_isSingleDML = YES;
342
m_isSingleSelect = NO;
344
if (!compare.startsWith("update") &&
345
!compare.startsWith("delete") &&
346
!compare.startsWith("insert")) {
355
* Check if the current query is a single SELECT.
357
private boolean isSingleSelect()
359
if (m_isSingleSelect == UNKNOWN)
360
analyzeStatementType();
362
return m_isSingleSelect == YES;
366
* Check if the current query is a single SELECT/UPDATE/INSERT/DELETE.
368
private boolean isSingleDML()
370
if (m_isSingleDML == UNKNOWN)
371
analyzeStatementType();
373
return m_isSingleDML == YES;
377
* Return the query fragments to use for a server-prepared statement.
378
* The first query executed will include a PREPARE and EXECUTE;
379
* subsequent queries will just be an EXECUTE.
381
private String[] transformToServerPrepare() {
382
if (m_statementName != null)
383
return m_executeSqlFragments;
385
// First time through.
386
m_statementName = "JDBC_STATEMENT_" + next_preparedCount();
388
// Set up m_executeSqlFragments
389
m_executeSqlFragments = new String[m_sqlFragments.length];
390
m_executeSqlFragments[0] = "EXECUTE " + m_statementName;
391
if (m_sqlFragments.length > 1) {
392
m_executeSqlFragments[0] += "(";
393
for (int i = 1; i < m_bindTypes.length; i++)
394
m_executeSqlFragments[i] = ", ";
395
m_executeSqlFragments[m_bindTypes.length] = ")";
398
// Set up the PREPARE.
399
String[] prepareSqlFragments = new String[m_sqlFragments.length];
400
System.arraycopy(m_sqlFragments, 0, prepareSqlFragments, 0, m_sqlFragments.length);
402
synchronized (sbuf) {
404
sbuf.append("PREPARE ");
405
sbuf.append(m_statementName);
406
if (m_sqlFragments.length > 1) {
408
for (int i = 0; i < m_bindTypes.length; i++) {
409
if (i != 0) sbuf.append(", ");
410
sbuf.append(m_bindTypes[i]);
415
sbuf.append(m_sqlFragments[0]);
416
for (int i = 1; i < m_sqlFragments.length; i++) {
420
sbuf.append(m_sqlFragments[i]);
423
sbuf.append(m_executeSqlFragments[0]);
425
prepareSqlFragments[0] = sbuf.toString();
428
System.arraycopy(m_executeSqlFragments, 1, prepareSqlFragments, 1, prepareSqlFragments.length - 1);
429
return prepareSqlFragments;
433
* Return the current query transformed into a cursor-based statement.
434
* This uses a new cursor on each query.
436
private String[] transformToCursorFetch()
439
// Pinch the prepared count for our own nefarious purposes.
440
m_cursorName = "JDBC_CURS_" + next_preparedCount();
442
// Create a cursor declaration and initial fetch statement from the original query.
443
int len = m_sqlFragments.length;
444
String[] cursorBasedSql = new String[len];
445
System.arraycopy(m_sqlFragments, 0, cursorBasedSql, 0, len);
446
cursorBasedSql[0] = "DECLARE " + m_cursorName + " CURSOR FOR " + cursorBasedSql[0];
447
cursorBasedSql[len-1] += "; FETCH FORWARD " + fetchSize + " FROM " + m_cursorName;
449
// Make the cursor based query the one that will be used.
450
if (org.postgresql.Driver.logDebug)
451
org.postgresql.Driver.debug("using cursor based sql with cursor name " + m_cursorName);
453
return cursorBasedSql;
457
* Do transformations to a query for server-side prepare or setFetchSize() cursor
459
* @return the query fragments to execute
461
private String[] getQueryFragments()
463
// nb: isSingleXXX() are relatively expensive, avoid calling them unless we must.
465
// We check the "mutable" bits of these conditions (which may change without
466
// a new query being created) here; isSingleXXX() only concern themselves with
467
// the query structure itself.
469
// We prefer cursor-based-fetch over server-side-prepare here.
470
// Eventually a v3 implementation should let us do both at once.
471
if (fetchSize > 0 && !connection.getAutoCommit() && isSingleSelect())
472
return transformToCursorFetch();
474
if (isUseServerPrepare() && isSingleDML())
475
return transformToServerPrepare();
477
// Not server-prepare or cursor-fetch, just return a plain query.
478
return m_sqlFragments;
482
* Some prepared statements return multiple results; the execute method
483
* handles these complex statements as well as the simpler form of
484
* statements handled by executeQuery and executeUpdate
486
* @return true if the next result is a ResultSet; false if it is an
487
* update count or there are no more results
488
* @exception SQLException if a database access error occurs
490
public boolean execute() throws SQLException
492
if (isFunction && !returnTypeSet)
493
throw new PSQLException("postgresql.call.noreturntype", PSQLState.STATEMENT_NOT_ALLOWED_IN_FUNCTION_CALL);
495
{ // set entry 1 to dummy entry..
496
m_binds[0] = ""; // dummy entry which ensured that no one overrode
497
m_bindTypes[0] = PG_TEXT;
498
// and calls to setXXX (2,..) really went to first arg in a function call..
501
// New in 7.1, if we have a previous resultset then force it to close
502
// This brings us nearer to compliance, and helps memory management.
503
// Internal stuff will call ExecSQL directly, bypassing this.
507
java.sql.ResultSet rs = getResultSet();
512
// Get the actual query fragments to run (might be a transformed version of
513
// the original fragments)
514
String[] fragments = getQueryFragments();
516
// New in 7.1, pass Statement so that ExecSQL can customise to it
517
result = QueryExecutor.execute(fragments,
521
//If we are executing a callable statement function set the return data
524
if (!result.reallyResultSet())
525
throw new PSQLException("postgresql.call.noreturnval", PSQLState.NO_DATA);
527
throw new PSQLException ("postgresql.call.noreturnval", PSQLState.NO_DATA);
528
callResult = result.getObject(1);
529
int columnType = result.getMetaData().getColumnType(1);
530
if (columnType != functionReturnType)
531
throw new PSQLException ("postgresql.call.wrongrtntype", PSQLState.DATA_TYPE_MISMATCH,
533
"java.sql.Types=" + columnType, "java.sql.Types=" + functionReturnType });
539
return (result != null && result.reallyResultSet());
544
* setCursorName defines the SQL cursor name that will be used by
545
* subsequent execute methods. This name can then be used in SQL
546
* positioned update/delete statements to identify the current row
547
* in the ResultSet generated by this statement. If a database
548
* doesn't support positioned update/delete, this method is a
551
* <p><B>Note:</B> By definition, positioned update/delete execution
552
* must be done by a different Statement than the one which
553
* generated the ResultSet being used for positioning. Also, cursor
554
* names must be unique within a Connection.
556
* <p>We throw an additional constriction. There can only be one
557
* cursor active at any one time.
559
* @param name the new cursor name
560
* @exception SQLException if a database access error occurs
562
public void setCursorName(String name) throws SQLException
564
connection.setCursorName(name);
569
* getUpdateCount returns the current result as an update count,
570
* if the result is a ResultSet or there are no more results, -1
571
* is returned. It should only be called once per result.
573
* @return the current result as an update count.
574
* @exception SQLException if a database access error occurs
576
public int getUpdateCount() throws SQLException
582
if (result.reallyResultSet())
584
return result.getResultCount();
588
* getMoreResults moves to a Statement's next result. If it returns
589
* true, this result is a ResulSet.
591
* @return true if the next ResultSet is valid
592
* @exception SQLException if a database access error occurs
594
public boolean getMoreResults() throws SQLException
596
result = (BaseResultSet) result.getNext();
597
return (result != null && result.reallyResultSet());
603
* Returns the status message from the current Result.<p>
604
* This is used internally by the driver.
606
* @return status message from backend
608
public String getResultStatusString()
612
return result.getStatusString();
616
* The maxRows limit is set to limit the number of rows that
617
* any ResultSet can contain. If the limit is exceeded, the
618
* excess rows are silently dropped.
620
* @return the current maximum row limit; zero means unlimited
621
* @exception SQLException if a database access error occurs
623
public int getMaxRows() throws SQLException
629
* Set the maximum number of rows
631
* @param max the new max rows limit; zero means unlimited
632
* @exception SQLException if a database access error occurs
635
public void setMaxRows(int max) throws SQLException
637
if (max<0) throw new PSQLException("postgresql.input.rows.gt0");
642
* If escape scanning is on (the default), the driver will do escape
643
* substitution before sending the SQL to the database.
645
* @param enable true to enable; false to disable
646
* @exception SQLException if a database access error occurs
648
public void setEscapeProcessing(boolean enable) throws SQLException
650
replaceProcessingEnabled = enable;
654
* The queryTimeout limit is the number of seconds the driver
655
* will wait for a Statement to execute. If the limit is
656
* exceeded, a SQLException is thrown.
658
* @return the current query timeout limit in seconds; 0 = unlimited
659
* @exception SQLException if a database access error occurs
661
public int getQueryTimeout() throws SQLException
667
* Sets the queryTimeout limit
669
* @param seconds - the new query timeout limit in seconds
670
* @exception SQLException if a database access error occurs
672
public void setQueryTimeout(int seconds) throws SQLException
674
if (seconds<0) throw new PSQLException("postgresql.input.query.gt0");
679
* This adds a warning to the warning chain.
680
* @param msg message to add
682
public void addWarning(String msg)
684
if (warnings != null)
685
warnings.setNextWarning(new SQLWarning(msg));
687
warnings = new SQLWarning(msg);
691
* The first warning reported by calls on this Statement is
692
* returned. A Statement's execute methods clear its SQLWarning
693
* chain. Subsequent Statement warnings will be chained to this
696
* <p>The Warning chain is automatically cleared each time a statement
699
* <p><B>Note:</B> If you are processing a ResultSet then any warnings
700
* associated with ResultSet reads will be chained on the ResultSet
703
* @return the first SQLWarning on null
704
* @exception SQLException if a database access error occurs
706
public SQLWarning getWarnings() throws SQLException
712
* The maxFieldSize limit (in bytes) is the maximum amount of
713
* data returned for any column value; it only applies to
714
* BINARY, VARBINARY, LONGVARBINARY, CHAR, VARCHAR and LONGVARCHAR
715
* columns. If the limit is exceeded, the excess data is silently
718
* @return the current max column size limit; zero means unlimited
719
* @exception SQLException if a database access error occurs
721
public int getMaxFieldSize() throws SQLException
727
* Sets the maxFieldSize
729
* @param max the new max column size limit; zero means unlimited
730
* @exception SQLException if a database access error occurs
732
public void setMaxFieldSize(int max) throws SQLException
734
if (max < 0) throw new PSQLException("postgresql.input.field.gt0");
739
* After this call, getWarnings returns null until a new warning
740
* is reported for this Statement.
742
* @exception SQLException if a database access error occurs
744
public void clearWarnings() throws SQLException
750
* Cancel can be used by one thread to cancel a statement that
751
* is being executed by another thread.
753
* Not implemented, this method is a no-op.
755
* @exception SQLException only because thats the spec.
757
public void cancel() throws SQLException
759
throw new PSQLException("postgresql.unimplemented", PSQLState.NOT_IMPLEMENTED);
763
* getResultSet returns the current result as a ResultSet. It
764
* should only be called once per result.
766
* @return the current result set; null if there are no more
767
* @exception SQLException if a database access error occurs (why?)
769
public java.sql.ResultSet getResultSet() throws SQLException
771
if (result != null && result.reallyResultSet())
772
return (ResultSet) result;
777
* In many cases, it is desirable to immediately release a
778
* Statement's database and JDBC resources instead of waiting
779
* for this to happen when it is automatically closed. The
780
* close method provides this immediate release.
782
* <p><B>Note:</B> A Statement is automatically closed when it is
783
* garbage collected. When a Statement is closed, its current
784
* ResultSet, if one exists, is also closed.
786
* @exception SQLException if a database access error occurs (why?)
788
public void close() throws SQLException
790
// closing an already closed Statement is a no-op.
794
// Force the ResultSet to close
795
java.sql.ResultSet rs = getResultSet();
801
// Disasociate it from us (For Garbage Collection)
807
* This finalizer ensures that statements that have allocated server-side
808
* resources free them when they become unreferenced.
810
protected void finalize() {
812
catch (SQLException e) {}
816
* Filter the SQL string of Java SQL Escape clauses.
818
* Currently implemented Escape clauses are those mentioned in 11.3
819
* in the specification. Basically we look through the sql string for
820
* {d xxx}, {t xxx} or {ts xxx} in non-string sql code. When we find
821
* them, we just strip the escape part leaving only the xxx part.
822
* So, something like "select * from x where d={d '2001-10-09'}" would
823
* return "select * from x where d= '2001-10-09'".
825
protected String replaceProcessing(String p_sql)
827
if (replaceProcessingEnabled)
829
// Since escape codes can only appear in SQL CODE, we keep track
830
// of if we enter a string or not.
831
StringBuffer newsql = new StringBuffer(p_sql.length());
832
short state = IN_SQLCODE;
835
int len = p_sql.length();
838
char c = p_sql.charAt(i);
842
if (c == '\'') // start of a string?
844
else if (c == '{') // start of an escape code?
847
char next = p_sql.charAt(i + 1);
850
state = ESC_TIMEDATE;
854
else if (next == 't')
856
state = ESC_TIMEDATE;
857
i += (i + 2 < len && p_sql.charAt(i + 2) == 's') ? 2 : 1;
865
if (c == '\'') // end of string?
867
else if (c == '\\') // a backslash?
881
state = IN_SQLCODE; // end of escape code.
888
return newsql.toString();
898
* The following methods are postgres extensions and are defined
899
* in the interface BaseStatement
904
* Returns the Last inserted/updated oid. Deprecated in 7.2 because
905
* range of OID values is greater than a java signed int.
906
* @deprecated Replaced by getLastOID in 7.2
908
public int getInsertedOID() throws SQLException
912
return (int) result.getLastOID();
916
* Returns the Last inserted/updated oid.
917
* @return OID of last insert
920
public long getLastOID() throws SQLException
924
return result.getLastOID();
928
* Set a parameter to SQL NULL
930
* <p><B>Note:</B> You must specify the parameters SQL type (although
931
* PostgreSQL ignores it)
933
* @param parameterIndex the first parameter is 1, etc...
934
* @param sqlType the SQL type code defined in java.sql.Types
935
* @exception SQLException if a database access error occurs
937
public void setNull(int parameterIndex, int sqlType) throws SQLException
943
l_pgType = PG_INTEGER;
957
l_pgType = PG_DOUBLE;
961
l_pgType = PG_NUMERIC;
965
case Types.LONGVARCHAR:
974
case Types.TIMESTAMP:
975
l_pgType = PG_TIMESTAMPTZ;
978
l_pgType = PG_BOOLEAN;
981
case Types.VARBINARY:
982
case Types.LONGVARBINARY:
991
bind(parameterIndex, "null", l_pgType);
995
* Set a parameter to a Java boolean value. The driver converts this
996
* to a SQL BIT value when it sends it to the database.
998
* @param parameterIndex the first parameter is 1...
999
* @param x the parameter value
1000
* @exception SQLException if a database access error occurs
1002
public void setBoolean(int parameterIndex, boolean x) throws SQLException
1004
bind(parameterIndex, x ? "'1'" : "'0'", PG_BOOLEAN);
1008
* Set a parameter to a Java byte value. The driver converts this to
1009
* a SQL TINYINT value when it sends it to the database.
1011
* @param parameterIndex the first parameter is 1...
1012
* @param x the parameter value
1013
* @exception SQLException if a database access error occurs
1015
public void setByte(int parameterIndex, byte x) throws SQLException
1017
bind(parameterIndex, Integer.toString(x), PG_INT2);
1021
* Set a parameter to a Java short value. The driver converts this
1022
* to a SQL SMALLINT value when it sends it to the database.
1024
* @param parameterIndex the first parameter is 1...
1025
* @param x the parameter value
1026
* @exception SQLException if a database access error occurs
1028
public void setShort(int parameterIndex, short x) throws SQLException
1030
bind(parameterIndex, Integer.toString(x), PG_INT2);
1034
* Set a parameter to a Java int value. The driver converts this to
1035
* a SQL INTEGER value when it sends it to the database.
1037
* @param parameterIndex the first parameter is 1...
1038
* @param x the parameter value
1039
* @exception SQLException if a database access error occurs
1041
public void setInt(int parameterIndex, int x) throws SQLException
1043
bind(parameterIndex, Integer.toString(x), PG_INTEGER);
1047
* Set a parameter to a Java long value. The driver converts this to
1048
* a SQL BIGINT value when it sends it to the database.
1050
* @param parameterIndex the first parameter is 1...
1051
* @param x the parameter value
1052
* @exception SQLException if a database access error occurs
1054
public void setLong(int parameterIndex, long x) throws SQLException
1056
bind(parameterIndex, Long.toString(x), PG_INT8);
1060
* Set a parameter to a Java float value. The driver converts this
1061
* to a SQL FLOAT value when it sends it to the database.
1063
* @param parameterIndex the first parameter is 1...
1064
* @param x the parameter value
1065
* @exception SQLException if a database access error occurs
1067
public void setFloat(int parameterIndex, float x) throws SQLException
1069
bind(parameterIndex, Float.toString(x), PG_FLOAT);
1073
* Set a parameter to a Java double value. The driver converts this
1074
* to a SQL DOUBLE value when it sends it to the database
1076
* @param parameterIndex the first parameter is 1...
1077
* @param x the parameter value
1078
* @exception SQLException if a database access error occurs
1080
public void setDouble(int parameterIndex, double x) throws SQLException
1082
bind(parameterIndex, Double.toString(x), PG_DOUBLE);
1086
* Set a parameter to a java.lang.BigDecimal value. The driver
1087
* converts this to a SQL NUMERIC value when it sends it to the
1090
* @param parameterIndex the first parameter is 1...
1091
* @param x the parameter value
1092
* @exception SQLException if a database access error occurs
1094
public void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException
1097
setNull(parameterIndex, Types.DECIMAL);
1100
bind(parameterIndex, x.toString(), PG_NUMERIC);
1105
* Set a parameter to a Java String value. The driver converts this
1106
* to a SQL VARCHAR or LONGVARCHAR value (depending on the arguments
1107
* size relative to the driver's limits on VARCHARs) when it sends it
1110
* @param parameterIndex the first parameter is 1...
1111
* @param x the parameter value
1112
* @exception SQLException if a database access error occurs
1114
public void setString(int parameterIndex, String x) throws SQLException
1116
setString(parameterIndex, x, PG_TEXT);
1119
public void setString(int parameterIndex, String x, String type) throws SQLException
1121
// if the passed string is null, then set this column to null
1123
setNull(parameterIndex, Types.VARCHAR);
1126
// use the shared buffer object. Should never clash but this makes
1131
sbuf.ensureCapacity(2 + x.length() + (int)(x.length() / 10));
1133
escapeString(x, sbuf);
1135
bind(parameterIndex, sbuf.toString(), type);
1140
private void escapeString(String p_input, StringBuffer p_output) {
1141
for (int i = 0 ; i < p_input.length() ; ++i)
1143
char c = p_input.charAt(i);
1148
p_output.append('\\');
1152
throw new IllegalArgumentException("\\0 not allowed");
1161
* Set a parameter to a Java array of bytes. The driver converts this
1162
* to a SQL VARBINARY or LONGVARBINARY (depending on the argument's
1163
* size relative to the driver's limits on VARBINARYs) when it sends
1164
* it to the database.
1166
* <p>Implementation note:
1167
* <br>With org.postgresql, this creates a large object, and stores the
1168
* objects oid in this column.
1170
* @param parameterIndex the first parameter is 1...
1171
* @param x the parameter value
1172
* @exception SQLException if a database access error occurs
1174
public void setBytes(int parameterIndex, byte x[]) throws SQLException
1176
if (connection.haveMinimumCompatibleVersion("7.2"))
1178
//Version 7.2 supports the bytea datatype for byte arrays
1181
setNull(parameterIndex, Types.VARBINARY);
1185
setString(parameterIndex, PGbytea.toPGString(x), PG_BYTEA);
1190
//Version 7.1 and earlier support done as LargeObjects
1191
LargeObjectManager lom = connection.getLargeObjectAPI();
1192
int oid = lom.create();
1193
LargeObject lob = lom.open(oid);
1196
setInt(parameterIndex, oid);
1201
* Set a parameter to a java.sql.Date value. The driver converts this
1202
* to a SQL DATE value when it sends it to the database.
1204
* @param parameterIndex the first parameter is 1...
1205
* @param x the parameter value
1206
* @exception SQLException if a database access error occurs
1208
public void setDate(int parameterIndex, java.sql.Date x) throws SQLException
1212
setNull(parameterIndex, Types.DATE);
1216
bind(parameterIndex, "'" + x.toString() + "'", PG_DATE);
1221
* Set a parameter to a java.sql.Time value. The driver converts
1222
* this to a SQL TIME value when it sends it to the database.
1224
* @param parameterIndex the first parameter is 1...));
1225
* @param x the parameter value
1226
* @exception SQLException if a database access error occurs
1228
public void setTime(int parameterIndex, Time x) throws SQLException
1232
setNull(parameterIndex, Types.TIME);
1236
bind(parameterIndex, "'" + x.toString() + "'", PG_TIME);
1241
* Set a parameter to a java.sql.Timestamp value. The driver converts
1242
* this to a SQL TIMESTAMP value when it sends it to the database.
1244
* @param parameterIndex the first parameter is 1...
1245
* @param x the parameter value
1246
* @exception SQLException if a database access error occurs
1248
public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException
1252
setNull(parameterIndex, Types.TIMESTAMP);
1256
// Use the shared StringBuffer
1260
sbuf.ensureCapacity(32);
1262
//format the timestamp
1263
//we do our own formating so that we can get a format
1264
//that works with both timestamp with time zone and
1265
//timestamp without time zone datatypes.
1266
//The format is '2002-01-01 23:59:59.123456-0130'
1267
//we need to include the local time and timezone offset
1268
//so that timestamp without time zone works correctly
1269
int l_year = x.getYear() + 1900;
1271
// always use four digits for the year so very
1272
// early years, like 2, don't get misinterpreted
1273
int l_yearlen = String.valueOf(l_year).length();
1274
for (int i=4; i>l_yearlen; i--) {
1278
sbuf.append(l_year);
1280
int l_month = x.getMonth() + 1;
1283
sbuf.append(l_month);
1285
int l_day = x.getDate();
1290
int l_hours = x.getHours();
1293
sbuf.append(l_hours);
1295
int l_minutes = x.getMinutes();
1298
sbuf.append(l_minutes);
1300
int l_seconds = x.getSeconds();
1303
sbuf.append(l_seconds);
1304
// Make decimal from nanos.
1305
char[] l_decimal = {'0', '0', '0', '0', '0', '0', '0', '0', '0'};
1306
char[] l_nanos = Integer.toString(x.getNanos()).toCharArray();
1307
System.arraycopy(l_nanos, 0, l_decimal, l_decimal.length - l_nanos.length, l_nanos.length);
1309
if (connection.haveMinimumServerVersion("7.2"))
1311
sbuf.append(l_decimal, 0, 6);
1315
// Because 7.1 include bug that "hh:mm:59.999" becomes "hh:mm:60.00".
1316
sbuf.append(l_decimal, 0, 2);
1318
//add timezone offset
1319
int l_offset = -(x.getTimezoneOffset());
1320
int l_houros = l_offset / 60;
1329
if (l_houros > -10 && l_houros < 10)
1333
sbuf.append(l_houros);
1337
sbuf.append(-l_houros);
1339
int l_minos = l_offset - (l_houros * 60);
1342
if (l_minos > -10 && l_minos < 10)
1346
sbuf.append(l_minos);
1350
sbuf.append(-l_minos);
1354
bind(parameterIndex, sbuf.toString(), PG_TIMESTAMPTZ);
1360
private void setCharacterStreamPost71(int parameterIndex, InputStream x, int length, String encoding) throws SQLException
1365
setNull(parameterIndex, Types.VARCHAR);
1369
//Version 7.2 supports AsciiStream for all PG text types (char, varchar, text)
1370
//As the spec/javadoc for this method indicate this is to be used for
1371
//large String values (i.e. LONGVARCHAR) PG doesn't have a separate
1372
//long varchar datatype, but with toast all text datatypes are capable of
1373
//handling very large values. Thus the implementation ends up calling
1374
//setString() since there is no current way to stream the value to the server
1377
InputStreamReader l_inStream = new InputStreamReader(x, encoding);
1378
char[] l_chars = new char[length];
1379
int l_charsRead = 0;
1382
int n = l_inStream.read(l_chars, l_charsRead, length - l_charsRead);
1388
if (l_charsRead == length)
1392
setString(parameterIndex, new String(l_chars, 0, l_charsRead), PG_TEXT);
1394
catch (UnsupportedEncodingException l_uee)
1396
throw new PSQLException("postgresql.unusual", PSQLState.UNEXPECTED_ERROR, l_uee);
1398
catch (IOException l_ioe)
1400
throw new PSQLException("postgresql.unusual", PSQLState.UNEXPECTED_ERROR, l_ioe);
1405
* When a very large ASCII value is input to a LONGVARCHAR parameter,
1406
* it may be more practical to send it via a java.io.InputStream.
1407
* JDBC will read the data from the stream as needed, until it reaches
1408
* end-of-file. The JDBC driver will do any necessary conversion from
1409
* ASCII to the database char format.
1411
* <P><B>Note:</B> This stream object can either be a standard Java
1412
* stream object or your own subclass that implements the standard
1415
* @param parameterIndex the first parameter is 1...
1416
* @param x the parameter value
1417
* @param length the number of bytes in the stream
1418
* @exception SQLException if a database access error occurs
1420
public void setAsciiStream(int parameterIndex, InputStream x, int length) throws SQLException
1422
if (connection.haveMinimumCompatibleVersion("7.2"))
1424
setCharacterStreamPost71(parameterIndex, x, length, "ASCII");
1428
//Version 7.1 supported only LargeObjects by treating everything
1430
setBinaryStream(parameterIndex, x, length);
1435
* When a very large Unicode value is input to a LONGVARCHAR parameter,
1436
* it may be more practical to send it via a java.io.InputStream.
1437
* JDBC will read the data from the stream as needed, until it reaches
1438
* end-of-file. The JDBC driver will do any necessary conversion from
1439
* UNICODE to the database char format.
1441
* <P><B>Note:</B> This stream object can either be a standard Java
1442
* stream object or your own subclass that implements the standard
1445
* @param parameterIndex the first parameter is 1...
1446
* @param x the parameter value
1447
* @exception SQLException if a database access error occurs
1449
public void setUnicodeStream(int parameterIndex, InputStream x, int length) throws SQLException
1451
if (connection.haveMinimumCompatibleVersion("7.2"))
1453
setCharacterStreamPost71(parameterIndex, x, length, "UTF-8");
1457
//Version 7.1 supported only LargeObjects by treating everything
1459
setBinaryStream(parameterIndex, x, length);
1464
* When a very large binary value is input to a LONGVARBINARY parameter,
1465
* it may be more practical to send it via a java.io.InputStream.
1466
* JDBC will read the data from the stream as needed, until it reaches
1469
* <P><B>Note:</B> This stream object can either be a standard Java
1470
* stream object or your own subclass that implements the standard
1473
* @param parameterIndex the first parameter is 1...
1474
* @param x the parameter value
1475
* @exception SQLException if a database access error occurs
1477
public void setBinaryStream(int parameterIndex, InputStream x, int length) throws SQLException
1479
if (connection.haveMinimumCompatibleVersion("7.2"))
1483
setNull(parameterIndex, Types.VARBINARY);
1487
//Version 7.2 supports BinaryStream for for the PG bytea type
1488
//As the spec/javadoc for this method indicate this is to be used for
1489
//large binary values (i.e. LONGVARBINARY) PG doesn't have a separate
1490
//long binary datatype, but with toast the bytea datatype is capable of
1491
//handling very large values. Thus the implementation ends up calling
1492
//setBytes() since there is no current way to stream the value to the server
1493
byte[] l_bytes = new byte[length];
1494
int l_bytesRead = 0;
1499
int n = x.read(l_bytes, l_bytesRead, length - l_bytesRead);
1505
if (l_bytesRead == length)
1510
catch (IOException l_ioe)
1512
throw new PSQLException("postgresql.unusual", PSQLState.UNEXPECTED_ERROR, l_ioe);
1514
if (l_bytesRead == length)
1516
setBytes(parameterIndex, l_bytes);
1520
//the stream contained less data than they said
1521
byte[] l_bytes2 = new byte[l_bytesRead];
1522
System.arraycopy(l_bytes, 0, l_bytes2, 0, l_bytesRead);
1523
setBytes(parameterIndex, l_bytes2);
1528
//Version 7.1 only supported streams for LargeObjects
1529
//but the jdbc spec indicates that streams should be
1530
//available for LONGVARBINARY instead
1531
LargeObjectManager lom = connection.getLargeObjectAPI();
1532
int oid = lom.create();
1533
LargeObject lob = lom.open(oid);
1534
OutputStream los = lob.getOutputStream();
1537
// could be buffered, but then the OutputStream returned by LargeObject
1538
// is buffered internally anyhow, so there would be no performance
1539
// boost gained, if anything it would be worse!
1542
while (c > -1 && p < length)
1550
catch (IOException se)
1552
throw new PSQLException("postgresql.unusual", PSQLState.UNEXPECTED_ERROR, se);
1554
// lob is closed by the stream so don't call lob.close()
1555
setInt(parameterIndex, oid);
1561
* In general, parameter values remain in force for repeated used of a
1562
* Statement. Setting a parameter value automatically clears its
1563
* previous value. However, in coms cases, it is useful to immediately
1564
* release the resources used by the current parameter values; this
1565
* can be done by calling clearParameters
1567
* @exception SQLException if a database access error occurs
1569
public void clearParameters() throws SQLException
1573
for (i = 0 ; i < m_binds.length ; i++)
1576
m_bindTypes[i] = null;
1580
// Helper method that extracts numeric values from an arbitary Object.
1581
private String numericValueOf(Object x)
1583
if (x instanceof Boolean)
1584
return ((Boolean)x).booleanValue() ? "1" :"0";
1585
else if (x instanceof Integer || x instanceof Long ||
1586
x instanceof Double || x instanceof Short ||
1587
x instanceof Number || x instanceof Float)
1588
return x.toString();
1590
//ensure the value is a valid numeric value to avoid
1591
//sql injection attacks
1592
return new BigDecimal(x.toString()).toString();
1596
* Set the value of a parameter using an object; use the java.lang
1597
* equivalent objects for integral values.
1599
* <P>The given Java object will be converted to the targetSqlType before
1600
* being sent to the database.
1602
* <P>note that this method may be used to pass database-specific
1603
* abstract data types. This is done by using a Driver-specific
1604
* Java type and using a targetSqlType of java.sql.Types.OTHER
1606
* @param parameterIndex the first parameter is 1...
1607
* @param x the object containing the input parameter value
1608
* @param targetSqlType The SQL type to be send to the database
1609
* @param scale For java.sql.Types.DECIMAL or java.sql.Types.NUMERIC
1610
* * types this is the number of digits after the decimal. For
1611
* * all other types this value will be ignored.
1612
* @exception SQLException if a database access error occurs
1614
public void setObject(int parameterIndex, Object x, int targetSqlType, int scale) throws SQLException
1618
setNull(parameterIndex, targetSqlType);
1621
switch (targetSqlType)
1624
bind(parameterIndex, numericValueOf(x), PG_INTEGER);
1627
case Types.SMALLINT:
1628
bind(parameterIndex, numericValueOf(x), PG_INT2);
1631
bind(parameterIndex, numericValueOf(x), PG_INT8);
1635
bind(parameterIndex, numericValueOf(x), PG_FLOAT);
1638
bind(parameterIndex, numericValueOf(x), PG_DOUBLE);
1642
bind(parameterIndex, numericValueOf(x), PG_NUMERIC);
1646
case Types.LONGVARCHAR:
1647
setString(parameterIndex, x.toString());
1650
if (x instanceof java.sql.Date)
1651
setDate(parameterIndex, (java.sql.Date)x);
1654
java.sql.Date tmpd = (x instanceof java.util.Date) ? new java.sql.Date(((java.util.Date)x).getTime()) : dateFromString(x.toString());
1655
setDate(parameterIndex, tmpd);
1659
if (x instanceof java.sql.Time)
1660
setTime(parameterIndex, (java.sql.Time)x);
1663
java.sql.Time tmpt = (x instanceof java.util.Date) ? new java.sql.Time(((java.util.Date)x).getTime()) : timeFromString(x.toString());
1664
setTime(parameterIndex, tmpt);
1667
case Types.TIMESTAMP:
1668
if (x instanceof java.sql.Timestamp)
1669
setTimestamp(parameterIndex ,(java.sql.Timestamp)x);
1672
java.sql.Timestamp tmpts = (x instanceof java.util.Date) ? new java.sql.Timestamp(((java.util.Date)x).getTime()) : timestampFromString(x.toString());
1673
setTimestamp(parameterIndex, tmpts);
1677
if (x instanceof Boolean)
1679
bind(parameterIndex, ((Boolean)x).booleanValue() ? "'1'" : "'0'", PG_BOOLEAN);
1681
else if (x instanceof String)
1683
bind(parameterIndex, Boolean.valueOf(x.toString()).booleanValue() ? "'1'" : "'0'", PG_BOOLEAN);
1685
else if (x instanceof Number)
1687
bind(parameterIndex, ((Number)x).intValue()!=0 ? "'1'" : "'0'", PG_BOOLEAN);
1691
throw new PSQLException("postgresql.prep.type", PSQLState.INVALID_PARAMETER_TYPE);
1695
case Types.VARBINARY:
1696
case Types.LONGVARBINARY:
1697
setObject(parameterIndex, x);
1700
if (x instanceof PGobject)
1701
setString(parameterIndex, ((PGobject)x).getValue(), ((PGobject)x).getType());
1703
throw new PSQLException("postgresql.prep.type", PSQLState.INVALID_PARAMETER_TYPE);
1706
throw new PSQLException("postgresql.prep.type", PSQLState.INVALID_PARAMETER_TYPE);
1710
public void setObject(int parameterIndex, Object x, int targetSqlType) throws SQLException
1712
setObject(parameterIndex, x, targetSqlType, 0);
1716
* This stores an Object into a parameter.
1718
public void setObject(int parameterIndex, Object x) throws SQLException
1722
setNull(parameterIndex, Types.OTHER);
1725
if (x instanceof String)
1726
setString(parameterIndex, (String)x);
1727
else if (x instanceof BigDecimal)
1728
setBigDecimal(parameterIndex, (BigDecimal)x);
1729
else if (x instanceof Short)
1730
setShort(parameterIndex, ((Short)x).shortValue());
1731
else if (x instanceof Integer)
1732
setInt(parameterIndex, ((Integer)x).intValue());
1733
else if (x instanceof Long)
1734
setLong(parameterIndex, ((Long)x).longValue());
1735
else if (x instanceof Float)
1736
setFloat(parameterIndex, ((Float)x).floatValue());
1737
else if (x instanceof Double)
1738
setDouble(parameterIndex, ((Double)x).doubleValue());
1739
else if (x instanceof byte[])
1740
setBytes(parameterIndex, (byte[])x);
1741
else if (x instanceof java.sql.Date)
1742
setDate(parameterIndex, (java.sql.Date)x);
1743
else if (x instanceof Time)
1744
setTime(parameterIndex, (Time)x);
1745
else if (x instanceof Timestamp)
1746
setTimestamp(parameterIndex, (Timestamp)x);
1747
else if (x instanceof Boolean)
1748
setBoolean(parameterIndex, ((Boolean)x).booleanValue());
1749
else if (x instanceof PGobject)
1750
setString(parameterIndex, ((PGobject)x).getValue(), PG_TEXT);
1752
// Try to store as a string in database
1753
setString(parameterIndex, x.toString(), PG_TEXT);
1757
* Before executing a stored procedure call you must explicitly
1758
* call registerOutParameter to register the java.sql.Type of each
1761
* <p>Note: When reading the value of an out parameter, you must use
1762
* the getXXX method whose Java type XXX corresponds to the
1763
* parameter's registered SQL type.
1765
* ONLY 1 RETURN PARAMETER if {?= call ..} syntax is used
1767
* @param parameterIndex the first parameter is 1, the second is 2,...
1768
* @param sqlType SQL type code defined by java.sql.Types; for
1769
* parameters of type Numeric or Decimal use the version of
1770
* registerOutParameter that accepts a scale value
1771
* @exception SQLException if a database-access error occurs.
1773
public void registerOutParameter(int parameterIndex, int sqlType) throws SQLException
1775
if (parameterIndex != 1)
1776
throw new PSQLException ("postgresql.call.noinout", PSQLState.STATEMENT_NOT_ALLOWED_IN_FUNCTION_CALL);
1778
throw new PSQLException ("postgresql.call.procasfunc", PSQLState.STATEMENT_NOT_ALLOWED_IN_FUNCTION_CALL,originalSql);
1780
// functionReturnType contains the user supplied value to check
1781
// testReturn contains a modified version to make it easier to
1782
// check the getXXX methods..
1783
functionReturnType = sqlType;
1784
testReturn = sqlType;
1785
if (functionReturnType == Types.CHAR ||
1786
functionReturnType == Types.LONGVARCHAR)
1787
testReturn = Types.VARCHAR;
1788
else if (functionReturnType == Types.FLOAT)
1789
testReturn = Types.REAL; // changes to streamline later error checking
1790
returnTypeSet = true;
1794
* You must also specify the scale for numeric/decimal types:
1796
* <p>Note: When reading the value of an out parameter, you must use
1797
* the getXXX method whose Java type XXX corresponds to the
1798
* parameter's registered SQL type.
1800
* @param parameterIndex the first parameter is 1, the second is 2,...
1801
* @param sqlType use either java.sql.Type.NUMERIC or java.sql.Type.DECIMAL
1802
* @param scale a value greater than or equal to zero representing the
1803
* desired number of digits to the right of the decimal point
1804
* @exception SQLException if a database-access error occurs.
1806
public void registerOutParameter(int parameterIndex, int sqlType,
1807
int scale) throws SQLException
1809
registerOutParameter (parameterIndex, sqlType); // ignore for now..
1813
* An OUT parameter may have the value of SQL NULL; wasNull
1814
* reports whether the last value read has this special value.
1816
* <p>Note: You must first call getXXX on a parameter to read its
1817
* value and then call wasNull() to see if the value was SQL NULL.
1818
* @return true if the last parameter read was SQL NULL
1819
* @exception SQLException if a database-access error occurs.
1821
public boolean wasNull() throws SQLException
1823
// check to see if the last access threw an exception
1824
return (callResult == null);
1828
* Get the value of a CHAR, VARCHAR, or LONGVARCHAR parameter as a
1831
* @param parameterIndex the first parameter is 1, the second is 2,...
1832
* @return the parameter value; if the value is SQL NULL, the result is null
1833
* @exception SQLException if a database-access error occurs.
1835
public String getString(int parameterIndex) throws SQLException
1837
checkIndex (parameterIndex, Types.VARCHAR, "String");
1838
return (String)callResult;
1843
* Get the value of a BIT parameter as a Java boolean.
1845
* @param parameterIndex the first parameter is 1, the second is 2,...
1846
* @return the parameter value; if the value is SQL NULL, the result is false
1847
* @exception SQLException if a database-access error occurs.
1849
public boolean getBoolean(int parameterIndex) throws SQLException
1851
checkIndex (parameterIndex, Types.BIT, "Boolean");
1852
if (callResult == null)
1854
return ((Boolean)callResult).booleanValue ();
1858
* Get the value of a TINYINT parameter as a Java byte.
1860
* @param parameterIndex the first parameter is 1, the second is 2,...
1861
* @return the parameter value; if the value is SQL NULL, the result is 0
1862
* @exception SQLException if a database-access error occurs.
1864
public byte getByte(int parameterIndex) throws SQLException
1866
checkIndex (parameterIndex, Types.TINYINT, "Byte");
1867
// We expect the above checkIndex call to fail because
1868
// we don't have an equivalent pg type for TINYINT.
1869
// Possibly "char" (not char(N)), could be used, but
1870
// for the moment we just bail out.
1872
throw new PSQLException("postgresql.unusual", PSQLState.UNEXPECTED_ERROR);
1876
* Get the value of a SMALLINT parameter as a Java short.
1878
* @param parameterIndex the first parameter is 1, the second is 2,...
1879
* @return the parameter value; if the value is SQL NULL, the result is 0
1880
* @exception SQLException if a database-access error occurs.
1882
public short getShort(int parameterIndex) throws SQLException
1884
checkIndex (parameterIndex, Types.SMALLINT, "Short");
1885
if (callResult == null)
1887
return (short)((Short)callResult).intValue ();
1892
* Get the value of an INTEGER parameter as a Java int.
1894
* @param parameterIndex the first parameter is 1, the second is 2,...
1895
* @return the parameter value; if the value is SQL NULL, the result is 0
1896
* @exception SQLException if a database-access error occurs.
1898
public int getInt(int parameterIndex) throws SQLException
1900
checkIndex (parameterIndex, Types.INTEGER, "Int");
1901
if (callResult == null)
1903
return ((Integer)callResult).intValue ();
1907
* Get the value of a BIGINT parameter as a Java long.
1909
* @param parameterIndex the first parameter is 1, the second is 2,...
1910
* @return the parameter value; if the value is SQL NULL, the result is 0
1911
* @exception SQLException if a database-access error occurs.
1913
public long getLong(int parameterIndex) throws SQLException
1915
checkIndex (parameterIndex, Types.BIGINT, "Long");
1916
if (callResult == null)
1918
return ((Long)callResult).longValue ();
1922
* Get the value of a FLOAT parameter as a Java float.
1924
* @param parameterIndex the first parameter is 1, the second is 2,...
1925
* @return the parameter value; if the value is SQL NULL, the result is 0
1926
* @exception SQLException if a database-access error occurs.
1928
public float getFloat(int parameterIndex) throws SQLException
1930
checkIndex (parameterIndex, Types.REAL, "Float");
1931
if (callResult == null)
1933
return ((Float)callResult).floatValue ();
1937
* Get the value of a DOUBLE parameter as a Java double.
1939
* @param parameterIndex the first parameter is 1, the second is 2,...
1940
* @return the parameter value; if the value is SQL NULL, the result is 0
1941
* @exception SQLException if a database-access error occurs.
1943
public double getDouble(int parameterIndex) throws SQLException
1945
checkIndex (parameterIndex, Types.DOUBLE, "Double");
1946
if (callResult == null)
1948
return ((Double)callResult).doubleValue ();
1952
* Get the value of a NUMERIC parameter as a java.math.BigDecimal
1955
* @param parameterIndex the first parameter is 1, the second is 2,...
1956
* @param scale a value greater than or equal to zero representing the
1957
* desired number of digits to the right of the decimal point
1958
* @return the parameter value; if the value is SQL NULL, the result is null
1959
* @exception SQLException if a database-access error occurs.
1960
* @deprecated in Java2.0
1962
public BigDecimal getBigDecimal(int parameterIndex, int scale)
1965
checkIndex (parameterIndex, Types.NUMERIC, "BigDecimal");
1966
return ((BigDecimal)callResult);
1970
* Get the value of a SQL BINARY or VARBINARY parameter as a Java
1973
* @param parameterIndex the first parameter is 1, the second is 2,...
1974
* @return the parameter value; if the value is SQL NULL, the result is null
1975
* @exception SQLException if a database-access error occurs.
1977
public byte[] getBytes(int parameterIndex) throws SQLException
1979
checkIndex (parameterIndex, Types.VARBINARY, Types.BINARY, "Bytes");
1980
return ((byte [])callResult);
1985
* Get the value of a SQL DATE parameter as a java.sql.Date object
1987
* @param parameterIndex the first parameter is 1, the second is 2,...
1988
* @return the parameter value; if the value is SQL NULL, the result is null
1989
* @exception SQLException if a database-access error occurs.
1991
public java.sql.Date getDate(int parameterIndex) throws SQLException
1993
checkIndex (parameterIndex, Types.DATE, "Date");
1994
return (java.sql.Date)callResult;
1998
* Get the value of a SQL TIME parameter as a java.sql.Time object.
2000
* @param parameterIndex the first parameter is 1, the second is 2,...
2001
* @return the parameter value; if the value is SQL NULL, the result is null
2002
* @exception SQLException if a database-access error occurs.
2004
public java.sql.Time getTime(int parameterIndex) throws SQLException
2006
checkIndex (parameterIndex, Types.TIME, "Time");
2007
return (java.sql.Time)callResult;
2011
* Get the value of a SQL TIMESTAMP parameter as a java.sql.Timestamp object.
2013
* @param parameterIndex the first parameter is 1, the second is 2,...
2014
* @return the parameter value; if the value is SQL NULL, the result is null
2015
* @exception SQLException if a database-access error occurs.
2017
public java.sql.Timestamp getTimestamp(int parameterIndex)
2020
checkIndex (parameterIndex, Types.TIMESTAMP, "Timestamp");
2021
return (java.sql.Timestamp)callResult;
2024
// getObject returns a Java object for the parameter.
2025
// See the JDBC spec's "Dynamic Programming" chapter for details.
2027
* Get the value of a parameter as a Java object.
2029
* <p>This method returns a Java object whose type coresponds to the
2030
* SQL type that was registered for this parameter using
2031
* registerOutParameter.
2033
* <P>Note that this method may be used to read datatabase-specific,
2034
* abstract data types. This is done by specifying a targetSqlType
2035
* of java.sql.types.OTHER, which allows the driver to return a
2036
* database-specific Java type.
2038
* <p>See the JDBC spec's "Dynamic Programming" chapter for details.
2040
* @param parameterIndex the first parameter is 1, the second is 2,...
2041
* @return A java.lang.Object holding the OUT parameter value.
2042
* @exception SQLException if a database-access error occurs.
2044
public Object getObject(int parameterIndex)
2047
checkIndex (parameterIndex);
2051
//This method is implemeted in jdbc2
2052
public int getResultSetConcurrency() throws SQLException
2058
* Returns the SQL statement with the current template values
2061
public String toString()
2063
if (m_sqlFragments == null)
2064
return super.toString();
2071
for (i = 0 ; i < m_binds.length ; ++i)
2073
sbuf.append (m_sqlFragments[i]);
2074
if (m_binds[i] == null)
2077
sbuf.append (m_binds[i]);
2079
sbuf.append(m_sqlFragments[m_binds.length]);
2080
return sbuf.toString();
2085
* Note if s is a String it should be escaped by the caller to avoid SQL
2086
* injection attacks. It is not done here for efficency reasons as
2087
* most calls to this method do not require escaping as the source
2088
* of the string is known safe (i.e. Integer.toString())
2090
private void bind(int paramIndex, Object s, String type) throws SQLException
2092
if (paramIndex < 1 || paramIndex > m_binds.length)
2093
throw new PSQLException("postgresql.prep.range", PSQLState.INVALID_PARAMETER_VALUE);
2094
if (paramIndex == 1 && isFunction) // need to registerOut instead
2095
throw new PSQLException ("postgresql.call.funcover");
2096
m_binds[paramIndex - 1] = s;
2097
m_bindTypes[paramIndex - 1] = type;
2101
* this method will turn a string of the form
2102
* {? = call <some_function> (?, [?,..]) }
2103
* into the PostgreSQL format which is
2104
* select <some_function> (?, [?, ...]) as result
2105
* or select * from <some_function> (?, [?, ...]) as result (7.3)
2108
private String modifyJdbcCall(String p_sql) throws SQLException
2110
//Check that this is actually a call which should start with a {
2111
//if not do nothing and treat this as a standard prepared sql
2112
if (!p_sql.trim().startsWith("{")) {
2116
// syntax checking is not complete only a few basics :(
2117
originalSql = p_sql; // save for error msgs..
2118
String l_sql = p_sql;
2119
int index = l_sql.indexOf ("="); // is implied func or proc?
2120
boolean isValid = true;
2124
isValid = l_sql.indexOf ("?") < index; // ? before =
2126
l_sql = l_sql.trim ();
2127
if (l_sql.startsWith ("{") && l_sql.endsWith ("}"))
2129
l_sql = l_sql.substring (1, l_sql.length() - 1);
2133
index = l_sql.indexOf ("call");
2134
if (index == -1 || !isValid)
2135
throw new PSQLException ("postgresql.call.malformed",PSQLState.STATEMENT_NOT_ALLOWED_IN_FUNCTION_CALL,
2136
new Object[]{l_sql, JDBC_SYNTAX});
2137
l_sql = l_sql.replace ('{', ' '); // replace these characters
2138
l_sql = l_sql.replace ('}', ' ');
2139
l_sql = l_sql.replace (';', ' ');
2141
// this removes the 'call' string and also puts a hidden '?'
2142
// at the front of the line for functions, this will
2143
// allow the registerOutParameter to work correctly
2144
// because in the source sql there was one more ? for the return
2145
// value that is not needed by the postgres syntax. But to make
2146
// sure that the parameter numbers are the same as in the original
2147
// sql we add a dummy parameter in this case
2148
l_sql = (isFunction ? "?" : "") + l_sql.substring (index + 4);
2149
if (connection.haveMinimumServerVersion("7.3")) {
2150
l_sql = "select * from " + l_sql + " as " + RESULT_ALIAS + ";";
2152
l_sql = "select " + l_sql + " as " + RESULT_ALIAS + ";";
2157
/** helperfunction for the getXXX calls to check isFunction and index == 1
2158
* Compare BOTH type fields against the return type.
2160
protected void checkIndex (int parameterIndex, int type1, int type2, String getName)
2163
checkIndex (parameterIndex);
2164
if (type1 != this.testReturn && type2 != this.testReturn)
2165
throw new PSQLException("postgresql.call.wrongget", PSQLState.MOST_SPECIFIC_TYPE_DOES_NOT_MATCH,
2166
new Object[]{"java.sql.Types=" + testReturn,
2168
"java.sql.Types=" + type1});
2171
/** helperfunction for the getXXX calls to check isFunction and index == 1
2173
protected void checkIndex (int parameterIndex, int type, String getName)
2176
checkIndex (parameterIndex);
2177
if (type != this.testReturn)
2178
throw new PSQLException("postgresql.call.wrongget", PSQLState.MOST_SPECIFIC_TYPE_DOES_NOT_MATCH,
2179
new Object[]{"java.sql.Types=" + testReturn,
2181
"java.sql.Types=" + type});
2184
/** helperfunction for the getXXX calls to check isFunction and index == 1
2185
* @param parameterIndex index of getXXX (index)
2186
* check to make sure is a function and index == 1
2188
private void checkIndex (int parameterIndex) throws SQLException
2191
throw new PSQLException("postgresql.call.noreturntype", PSQLState.STATEMENT_NOT_ALLOWED_IN_FUNCTION_CALL);
2192
if (parameterIndex != 1)
2193
throw new PSQLException("postgresql.call.noinout", PSQLState.STATEMENT_NOT_ALLOWED_IN_FUNCTION_CALL);
2198
public void setUseServerPrepare(boolean flag) throws SQLException {
2199
//Server side prepared statements were introduced in 7.3
2200
if (connection.haveMinimumServerVersion("7.3")) {
2201
if (m_useServerPrepare != flag)
2203
m_useServerPrepare = flag;
2205
//This is a pre 7.3 server so no op this method
2206
//which means we will never turn on the flag to use server
2207
//prepared statements and thus regular processing will continue
2211
public boolean isUseServerPrepare()
2213
return m_useServerPrepare;
2216
private java.sql.Date dateFromString (String s) throws SQLException
2220
long localoffset = 0;
2221
int timezoneLocation = (s.indexOf('+') == -1) ? s.lastIndexOf("-") : s.indexOf('+');
2222
//if the last index of '-' or '+' is past 8. we are guaranteed that it is a timezone marker
2223
//shortest = yyyy-m-d
2224
//longest = yyyy-mm-dd
2227
timezone = (timezoneLocation>7) ? timezoneLocation : s.length();
2228
millis = java.sql.Date.valueOf(s.substring(0,timezone)).getTime();
2232
throw new PSQLException("postgresql.format.baddate", PSQLState.BAD_DATETIME_FORMAT, s , "yyyy-MM-dd[-tz]");
2235
if (timezoneLocation>7 && timezoneLocation+3 == s.length())
2237
timezone = Integer.parseInt(s.substring(timezoneLocation+1,s.length()));
2238
localoffset = java.util.Calendar.getInstance().getTimeZone().getRawOffset();
2239
if (java.util.Calendar.getInstance().getTimeZone().inDaylightTime(new java.sql.Date(millis)))
2240
localoffset += 60*60*1000;
2241
if (s.charAt(timezoneLocation)=='+')
2244
millis = millis + timezone*60*60*1000 + localoffset;
2245
return new java.sql.Date(millis);
2248
private java.sql.Time timeFromString (String s) throws SQLException
2252
long localoffset = 0;
2253
int timezoneLocation = (s.indexOf('+') == -1) ? s.lastIndexOf("-") : s.indexOf('+');
2254
//if the index of the last '-' or '+' is greater than 0 that means this time has a timezone.
2255
//everything earlier than that position, we treat as the time and parse it as such.
2258
timezone = (timezoneLocation==-1) ? s.length() : timezoneLocation;
2259
millis = java.sql.Time.valueOf(s.substring(0,timezone)).getTime();
2263
throw new PSQLException("postgresql.format.badtime", PSQLState.BAD_DATETIME_FORMAT, s, "HH:mm:ss[-tz]");
2266
if (timezoneLocation != -1 && timezoneLocation+3 == s.length())
2268
timezone = Integer.parseInt(s.substring(timezoneLocation+1,s.length()));
2269
localoffset = java.util.Calendar.getInstance().getTimeZone().getRawOffset();
2270
if (java.util.Calendar.getInstance().getTimeZone().inDaylightTime(new java.sql.Date(millis)))
2271
localoffset += 60*60*1000;
2272
if (s.charAt(timezoneLocation)=='+')
2275
millis = millis + timezone*60*60*1000 + localoffset;
2276
return new java.sql.Time(millis);
2279
private java.sql.Timestamp timestampFromString (String s) throws SQLException
2283
long localoffset = 0;
2285
int timezoneLocation = (s.indexOf('+') == -1) ? s.lastIndexOf("-") : s.indexOf('+');
2286
int nanospos = s.indexOf(".");
2287
//if there is a '.', that means there are nanos info, and we take the timestamp up to that point
2288
//if not, then we check to see if the last +/- (to indicate a timezone) is greater than 8
2289
//8 is because the shortest date, will have last '-' at position 7. e.g yyyy-x-x
2293
timezone = nanospos;
2294
else if (timezoneLocation > 8)
2295
timezone = timezoneLocation;
2297
timezone = s.length();
2298
millis = java.sql.Timestamp.valueOf(s.substring(0,timezone)).getTime();
2302
throw new PSQLException("postgresql.format.badtimestamp", PSQLState.BAD_DATETIME_FORMAT, s, "yyyy-MM-dd HH:mm:ss[.xxxxxx][-tz]");
2307
int tmploc = (timezoneLocation > 8) ? timezoneLocation : s.length();
2308
nanosVal = Integer.parseInt(s.substring(nanospos+1,tmploc));
2309
int diff = 8-((tmploc-1)-(nanospos+1));
2310
for (int i=0;i<diff;i++)
2313
if (timezoneLocation>8 && timezoneLocation+3 == s.length())
2315
timezone = Integer.parseInt(s.substring(timezoneLocation+1,s.length()));
2316
localoffset = java.util.Calendar.getInstance().getTimeZone().getRawOffset();
2317
if (java.util.Calendar.getInstance().getTimeZone().inDaylightTime(new java.sql.Date(millis)))
2318
localoffset += 60*60*1000;
2319
if (s.charAt(timezoneLocation)=='+')
2322
millis = millis + timezone*60*60*1000 + localoffset;
2323
java.sql.Timestamp tmpts = new java.sql.Timestamp(millis);
2324
tmpts.setNanos(nanosVal);
2329
private static final String PG_TEXT = "text";
2330
private static final String PG_INTEGER = "integer";
2331
private static final String PG_INT2 = "int2";
2332
private static final String PG_INT8 = "int8";
2333
private static final String PG_NUMERIC = "numeric";
2334
private static final String PG_FLOAT = "float";
2335
private static final String PG_DOUBLE = "double precision";
2336
private static final String PG_BOOLEAN = "boolean";
2337
private static final String PG_DATE = "date";
2338
private static final String PG_TIME = "time";
2339
private static final String PG_TIMESTAMPTZ = "timestamptz";
2340
private static final String PG_BYTEA = "bytea";