001: /*
002: * Jython Database Specification API 2.0
003: *
004: * $Id: Procedure.java 2542 2005-06-20 17:12:15Z fwierzbicki $
005: *
006: * Copyright (c) 2001 brian zimmer <bzimmer@ziclix.com>
007: *
008: */
009: package com.ziclix.python.sql;
010:
011: import org.python.core.Py;
012: import org.python.core.PyInteger;
013: import org.python.core.PyList;
014: import org.python.core.PyObject;
015: import org.python.core.PyString;
016:
017: import java.sql.CallableStatement;
018: import java.sql.DatabaseMetaData;
019: import java.sql.SQLException;
020: import java.util.BitSet;
021:
022: /**
023: * This class provides the necessary functionality to call stored
024: * procedures. It handles managing the database metadata and binding
025: * the appropriate parameters.
026: *
027: * @author brian zimmer
028: * @author last modified by $Author: fwierzbicki $
029: * @version $Revision: 2542 $
030: */
031: public class Procedure extends Object {
032:
033: /**
034: * Field NAME
035: */
036: protected static final int NAME = 3;
037:
038: /**
039: * Field COLUMN_TYPE
040: */
041: protected static final int COLUMN_TYPE = 4;
042:
043: /**
044: * Field DATA_TYPE
045: */
046: protected static final int DATA_TYPE = 5;
047:
048: /**
049: * Field DATA_TYPE_NAME
050: */
051: protected static final int DATA_TYPE_NAME = 6;
052:
053: /**
054: * Field PRECISION
055: */
056: protected static final int PRECISION = 7;
057:
058: /**
059: * Field LENGTH
060: */
061: protected static final int LENGTH = 8;
062:
063: /**
064: * Field SCALE
065: */
066: protected static final int SCALE = 9;
067:
068: /**
069: * Field NULLABLE
070: */
071: protected static final int NULLABLE = 11;
072:
073: /**
074: * Field cursor
075: */
076: protected PyCursor cursor;
077:
078: /**
079: * Field columns
080: */
081: protected PyObject columns;
082:
083: /**
084: * Field procedureCatalog
085: */
086: protected PyObject procedureCatalog;
087:
088: /**
089: * Field procedureSchema
090: */
091: protected PyObject procedureSchema;
092:
093: /**
094: * Field procedureName
095: */
096: protected PyObject procedureName;
097:
098: /**
099: * Field inputSet
100: */
101: protected BitSet inputSet;
102:
103: /**
104: * Constructor Procedure
105: *
106: * @param cursor cursor an open cursor
107: * @param name name a string or tuple representing the name
108: * @throws SQLException
109: */
110: public Procedure(PyCursor cursor, PyObject name)
111: throws SQLException {
112:
113: this .cursor = cursor;
114: this .inputSet = new BitSet();
115:
116: if (name instanceof PyString) {
117: this .procedureCatalog = getDefault();
118: this .procedureSchema = getDefault();
119: this .procedureName = name;
120: } else if (PyCursor.isSeq(name)) {
121: if (name.__len__() == 3) {
122: this .procedureCatalog = name.__getitem__(0);
123: this .procedureSchema = name.__getitem__(1);
124: this .procedureName = name.__getitem__(2);
125: } else {
126:
127: // throw an exception
128: }
129: } else {
130:
131: // throw an exception
132: }
133:
134: fetchColumns();
135: }
136:
137: /**
138: * Prepares the statement and registers the OUT/INOUT parameters (if any).
139: *
140: * @return CallableStatement
141: * @throws SQLException
142: */
143: public CallableStatement prepareCall() throws SQLException {
144: return prepareCall(Py.None, Py.None);
145: }
146:
147: /**
148: * Prepares the statement and registers the OUT/INOUT parameters (if any).
149: *
150: * @param rsType the value of to be created ResultSet type
151: * @param rsConcur the value of the to be created ResultSet concurrency
152: * @return CallableStatement
153: * @throws SQLException
154: */
155: public CallableStatement prepareCall(PyObject rsType,
156: PyObject rsConcur) throws SQLException {
157:
158: // prepare the statement
159: CallableStatement statement = null;
160: boolean normal = ((rsType == Py.None) && (rsConcur == Py.None));
161:
162: try {
163:
164: // build the full call syntax
165: String sqlString = toSql();
166:
167: if (normal) {
168: statement = cursor.connection.connection
169: .prepareCall(sqlString);
170: } else {
171: int t = ((PyInteger) rsType.__int__()).getValue();
172: int c = ((PyInteger) rsConcur.__int__()).getValue();
173:
174: statement = cursor.connection.connection.prepareCall(
175: sqlString, t, c);
176: }
177:
178: // prepare the OUT parameters
179: registerOutParameters(statement);
180: } catch (SQLException e) {
181: if (statement != null) {
182: try {
183: statement.close();
184: } catch (Exception ex) {
185: }
186: }
187:
188: throw e;
189: }
190:
191: return statement;
192: }
193:
194: /**
195: * Prepare the binding dictionary with the correct datatypes.
196: *
197: * @param params a non-None list of params
198: * @param bindings a dictionary of bindings
199: */
200: public void normalizeInput(PyObject params, PyObject bindings)
201: throws SQLException {
202:
203: if (this .columns == Py.None) {
204: return;
205: }
206:
207: // do nothing with params at the moment
208: for (int i = 0, len = this .columns.__len__(), binding = 0; i < len; i++) {
209: PyObject column = this .columns.__getitem__(i);
210: int colType = ((PyInteger) column.__getitem__(COLUMN_TYPE)
211: .__int__()).getValue();
212:
213: switch (colType) {
214:
215: case DatabaseMetaData.procedureColumnIn:
216: case DatabaseMetaData.procedureColumnInOut:
217:
218: // bindings are Python-indexed
219: PyInteger key = Py.newInteger(binding++);
220:
221: if (bindings.__finditem__(key) == null) {
222: int dataType = ((PyInteger) column.__getitem__(
223: DATA_TYPE).__int__()).getValue();
224: bindings.__setitem__(key, Py.newInteger(dataType));
225: }
226:
227: // inputs are JDBC-indexed
228: this .inputSet.set(i + 1);
229: break;
230: }
231: }
232: }
233:
234: /**
235: * This method determines whether the param at the specified index is an
236: * IN or INOUT param for a stored procedure. This is only configured properly
237: * AFTER a call to normalizeInput().
238: *
239: * @param index JDBC indexed column index (1, 2, ...)
240: * @return true if the column is an input, false otherwise
241: * @throws SQLException
242: */
243: public boolean isInput(int index) throws SQLException {
244: return this .inputSet.get(index);
245: }
246:
247: /**
248: * Returns the call in the syntax:
249: * <p/>
250: * {? = call <procedure-name>(?, ?, ...)}
251: * {call <procedure-name>(?, ?, ...)}
252: * <p/>
253: * As of now, all parameters variables are created and no support for named variable
254: * calling is supported.
255: *
256: * @return String
257: */
258: public String toSql() throws SQLException {
259:
260: int colParam = 0;
261: int colReturn = 0;
262:
263: if (this .columns != Py.None) {
264: for (int i = 0, len = this .columns.__len__(); i < len; i++) {
265: PyObject column = this .columns.__getitem__(i);
266: int colType = ((PyInteger) column.__getitem__(
267: COLUMN_TYPE).__int__()).getValue();
268:
269: switch (colType) {
270:
271: case DatabaseMetaData.procedureColumnUnknown:
272: throw zxJDBC.makeException(
273: zxJDBC.NotSupportedError,
274: "procedureColumnUnknown");
275: case DatabaseMetaData.procedureColumnResult:
276: throw zxJDBC.makeException(
277: zxJDBC.NotSupportedError,
278: "procedureColumnResult");
279:
280: // these go on the right hand side
281: case DatabaseMetaData.procedureColumnIn:
282: case DatabaseMetaData.procedureColumnInOut:
283: case DatabaseMetaData.procedureColumnOut:
284: colParam++;
285: break;
286:
287: // these go on the left hand side
288: case DatabaseMetaData.procedureColumnReturn:
289: colReturn++;
290: break;
291:
292: default:
293: throw zxJDBC.makeException(zxJDBC.DataError,
294: "unknown column type [" + colType + "]");
295: }
296: }
297: }
298:
299: StringBuffer sql = new StringBuffer("{");
300:
301: if (colReturn > 0) {
302: PyList list = new PyList();
303:
304: for (; colReturn > 0; colReturn--) {
305: list.append(Py.newString("?"));
306: }
307:
308: sql.append(Py.newString(",").join(list)).append(" = ");
309: }
310:
311: String name = this .getProcedureName();
312:
313: sql.append("call ").append(name).append("(");
314:
315: if (colParam > 0) {
316: PyList list = new PyList();
317:
318: for (; colParam > 0; colParam--) {
319: list.append(Py.newString("?"));
320: }
321:
322: sql.append(Py.newString(",").join(list));
323: }
324:
325: return sql.append(")}").toString();
326: }
327:
328: /**
329: * Registers the OUT/INOUT parameters of the statement.
330: *
331: * @param statement statement
332: * @throws SQLException
333: */
334: protected void registerOutParameters(CallableStatement statement)
335: throws SQLException {
336:
337: if (this .columns == Py.None) {
338: return;
339: }
340:
341: for (int i = 0, len = this .columns.__len__(); i < len; i++) {
342: PyObject column = this .columns.__getitem__(i);
343: int colType = ((PyInteger) column.__getitem__(COLUMN_TYPE)
344: .__int__()).getValue();
345: int dataType = ((PyInteger) column.__getitem__(DATA_TYPE)
346: .__int__()).getValue();
347: String dataTypeName = column.__getitem__(DATA_TYPE_NAME)
348: .toString();
349:
350: switch (colType) {
351:
352: case DatabaseMetaData.procedureColumnInOut:
353: case DatabaseMetaData.procedureColumnOut:
354: case DatabaseMetaData.procedureColumnReturn:
355: cursor.datahandler.registerOut(statement, i + 1,
356: colType, dataType, dataTypeName);
357: break;
358: }
359: }
360: }
361:
362: /**
363: * Get the columns for the stored procedure.
364: *
365: * @throws SQLException
366: */
367: protected void fetchColumns() throws SQLException {
368:
369: PyExtendedCursor pec = (PyExtendedCursor) cursor.connection
370: .cursor();
371:
372: try {
373: pec.datahandler = this .cursor.datahandler;
374:
375: pec.procedurecolumns(procedureCatalog, procedureSchema,
376: procedureName, Py.None);
377:
378: this .columns = pec.fetchall();
379: } finally {
380: pec.close();
381: }
382: }
383:
384: /**
385: * The value for a missing schema or catalog. This value is used to find
386: * the column names for the procedure. Not all DBMS use the same default
387: * value; for instance Oracle uses an empty string and SQLServer a null.
388: * This implementation returns the empty string.
389: *
390: * @return the default value (the empty string)
391: * @see java.sql.DatabaseMetaData#getProcedureColumns
392: */
393: protected PyObject getDefault() {
394: return Py.EmptyString;
395: }
396:
397: /**
398: * Construct a procedure name for the relevant schema and catalog information.
399: */
400: protected String getProcedureName() {
401:
402: StringBuffer proc = new StringBuffer();
403:
404: if (this .procedureCatalog.__nonzero__()) {
405: proc.append(this .procedureCatalog.toString()).append(".");
406: }
407:
408: return proc.append(this.procedureName.toString()).toString();
409: }
410: }
|