KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > ziclix > python > sql > Procedure


1 /*
2  * Jython Database Specification API 2.0
3  *
4  * $Id: Procedure.java,v 1.11 2005/06/20 17:12:12 fwierzbicki Exp $
5  *
6  * Copyright (c) 2001 brian zimmer <bzimmer@ziclix.com>
7  *
8  */

9 package com.ziclix.python.sql;
10
11 import org.python.core.Py;
12 import org.python.core.PyInteger;
13 import org.python.core.PyList;
14 import org.python.core.PyObject;
15 import org.python.core.PyString;
16
17 import java.sql.CallableStatement JavaDoc;
18 import java.sql.DatabaseMetaData JavaDoc;
19 import java.sql.SQLException JavaDoc;
20 import java.util.BitSet JavaDoc;
21
22 /**
23  * This class provides the necessary functionality to call stored
24  * procedures. It handles managing the database metadata and binding
25  * the appropriate parameters.
26  *
27  * @author brian zimmer
28  * @author last modified by $Author: fwierzbicki $
29  * @version $Revision: 1.11 $
30  */

31 public class Procedure extends Object JavaDoc {
32
33     /**
34      * Field NAME
35      */

36     protected static final int NAME = 3;
37
38     /**
39      * Field COLUMN_TYPE
40      */

41     protected static final int COLUMN_TYPE = 4;
42
43     /**
44      * Field DATA_TYPE
45      */

46     protected static final int DATA_TYPE = 5;
47
48     /**
49      * Field DATA_TYPE_NAME
50      */

51     protected static final int DATA_TYPE_NAME = 6;
52
53     /**
54      * Field PRECISION
55      */

56     protected static final int PRECISION = 7;
57
58     /**
59      * Field LENGTH
60      */

61     protected static final int LENGTH = 8;
62
63     /**
64      * Field SCALE
65      */

66     protected static final int SCALE = 9;
67
68     /**
69      * Field NULLABLE
70      */

71     protected static final int NULLABLE = 11;
72
73     /**
74      * Field cursor
75      */

76     protected PyCursor cursor;
77
78     /**
79      * Field columns
80      */

81     protected PyObject columns;
82
83     /**
84      * Field procedureCatalog
85      */

86     protected PyObject procedureCatalog;
87
88     /**
89      * Field procedureSchema
90      */

91     protected PyObject procedureSchema;
92
93     /**
94      * Field procedureName
95      */

96     protected PyObject procedureName;
97
98     /**
99      * Field inputSet
100      */

101     protected BitSet JavaDoc 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) throws SQLException JavaDoc {
111
112         this.cursor = cursor;
113         this.inputSet = new BitSet JavaDoc();
114
115         if (name instanceof PyString) {
116             this.procedureCatalog = getDefault();
117             this.procedureSchema = getDefault();
118             this.procedureName = name;
119         } else if (PyCursor.isSeq(name)) {
120             if (name.__len__() == 3) {
121                 this.procedureCatalog = name.__getitem__(0);
122                 this.procedureSchema = name.__getitem__(1);
123                 this.procedureName = name.__getitem__(2);
124             } else {
125
126                 // throw an exception
127
}
128         } else {
129
130             // throw an exception
131
}
132
133         fetchColumns();
134     }
135
136     /**
137      * Prepares the statement and registers the OUT/INOUT parameters (if any).
138      *
139      * @return CallableStatement
140      * @throws SQLException
141      */

142     public CallableStatement JavaDoc prepareCall() throws SQLException JavaDoc {
143         return prepareCall(Py.None, Py.None);
144     }
145
146     /**
147      * Prepares the statement and registers the OUT/INOUT parameters (if any).
148      *
149      * @param rsType the value of to be created ResultSet type
150      * @param rsConcur the value of the to be created ResultSet concurrency
151      * @return CallableStatement
152      * @throws SQLException
153      */

154     public CallableStatement JavaDoc prepareCall(PyObject rsType, PyObject rsConcur) throws SQLException JavaDoc {
155
156         // prepare the statement
157
CallableStatement JavaDoc statement = null;
158         boolean normal = ((rsType == Py.None) && (rsConcur == Py.None));
159
160         try {
161
162             // build the full call syntax
163
String JavaDoc sqlString = toSql();
164
165             if (normal) {
166                 statement = cursor.connection.connection.prepareCall(sqlString);
167             } else {
168                 int t = ((PyInteger)rsType.__int__()).getValue();
169                 int c = ((PyInteger)rsConcur.__int__()).getValue();
170
171                 statement = cursor.connection.connection.prepareCall(sqlString, t, c);
172             }
173
174             // prepare the OUT parameters
175
registerOutParameters(statement);
176         } catch (SQLException JavaDoc e) {
177             if (statement != null) {
178                 try {
179                     statement.close();
180                 } catch (Exception JavaDoc ex) {
181                 }
182             }
183
184             throw e;
185         }
186
187         return statement;
188     }
189
190     /**
191      * Prepare the binding dictionary with the correct datatypes.
192      *
193      * @param params a non-None list of params
194      * @param bindings a dictionary of bindings
195      */

196     public void normalizeInput(PyObject params, PyObject bindings) throws SQLException JavaDoc {
197
198         if (this.columns == Py.None) {
199             return;
200         }
201
202         // do nothing with params at the moment
203
for (int i = 0, len = this.columns.__len__(), binding = 0; i < len; i++) {
204             PyObject column = this.columns.__getitem__(i);
205             int colType = ((PyInteger)column.__getitem__(COLUMN_TYPE).__int__()).getValue();
206
207             switch (colType) {
208
209                 case DatabaseMetaData.procedureColumnIn:
210                 case DatabaseMetaData.procedureColumnInOut:
211
212                     // bindings are Python-indexed
213
PyInteger key = Py.newInteger(binding++);
214
215                     if (bindings.__finditem__(key) == null) {
216                         int dataType = ((PyInteger)column.__getitem__(DATA_TYPE).__int__()).getValue();
217                         bindings.__setitem__(key, Py.newInteger(dataType));
218                     }
219
220                     // inputs are JDBC-indexed
221
this.inputSet.set(i + 1);
222                     break;
223             }
224         }
225     }
226
227     /**
228      * This method determines whether the param at the specified index is an
229      * IN or INOUT param for a stored procedure. This is only configured properly
230      * AFTER a call to normalizeInput().
231      *
232      * @param index JDBC indexed column index (1, 2, ...)
233      * @return true if the column is an input, false otherwise
234      * @throws SQLException
235      */

236     public boolean isInput(int index) throws SQLException JavaDoc {
237         return this.inputSet.get(index);
238     }
239
240     /**
241      * Returns the call in the syntax:
242      * <p/>
243      * {? = call <procedure-name>(?, ?, ...)}
244      * {call <procedure-name>(?, ?, ...)}
245      * <p/>
246      * As of now, all parameters variables are created and no support for named variable
247      * calling is supported.
248      *
249      * @return String
250      */

251     public String JavaDoc toSql() throws SQLException JavaDoc {
252
253         int colParam = 0;
254         int colReturn = 0;
255
256         if (this.columns != Py.None) {
257             for (int i = 0, len = this.columns.__len__(); i < len; i++) {
258                 PyObject column = this.columns.__getitem__(i);
259                 int colType = ((PyInteger)column.__getitem__(COLUMN_TYPE).__int__()).getValue();
260
261                 switch (colType) {
262
263                     case DatabaseMetaData.procedureColumnUnknown:
264                         throw zxJDBC.makeException(zxJDBC.NotSupportedError, "procedureColumnUnknown");
265                     case DatabaseMetaData.procedureColumnResult:
266                         throw zxJDBC.makeException(zxJDBC.NotSupportedError, "procedureColumnResult");
267
268                         // these go on the right hand side
269
case DatabaseMetaData.procedureColumnIn:
270                     case DatabaseMetaData.procedureColumnInOut:
271                     case DatabaseMetaData.procedureColumnOut:
272                         colParam++;
273                         break;
274
275                         // these go on the left hand side
276
case DatabaseMetaData.procedureColumnReturn:
277                         colReturn++;
278                         break;
279
280                     default :
281                         throw zxJDBC.makeException(zxJDBC.DataError, "unknown column type [" + colType + "]");
282                 }
283             }
284         }
285
286         StringBuffer JavaDoc sql = new StringBuffer JavaDoc("{");
287
288         if (colReturn > 0) {
289             PyList list = new PyList();
290
291             for (; colReturn > 0; colReturn--) {
292                 list.append(Py.newString("?"));
293             }
294
295             sql.append(Py.newString(",").join(list)).append(" = ");
296         }
297
298         String JavaDoc name = this.getProcedureName();
299
300         sql.append("call ").append(name).append("(");
301
302         if (colParam > 0) {
303             PyList list = new PyList();
304
305             for (; colParam > 0; colParam--) {
306                 list.append(Py.newString("?"));
307             }
308
309             sql.append(Py.newString(",").join(list));
310         }
311
312         return sql.append(")}").toString();
313     }
314
315     /**
316      * Registers the OUT/INOUT parameters of the statement.
317      *
318      * @param statement statement
319      * @throws SQLException
320      */

321     protected void registerOutParameters(CallableStatement JavaDoc statement) throws SQLException JavaDoc {
322
323         if (this.columns == Py.None) {
324             return;
325         }
326
327         for (int i = 0, len = this.columns.__len__(); i < len; i++) {
328             PyObject column = this.columns.__getitem__(i);
329             int colType = ((PyInteger)column.__getitem__(COLUMN_TYPE).__int__()).getValue();
330             int dataType = ((PyInteger)column.__getitem__(DATA_TYPE).__int__()).getValue();
331             String JavaDoc dataTypeName = column.__getitem__(DATA_TYPE_NAME).toString();
332
333             switch (colType) {
334
335                 case DatabaseMetaData.procedureColumnInOut:
336                 case DatabaseMetaData.procedureColumnOut:
337                 case DatabaseMetaData.procedureColumnReturn:
338                     cursor.datahandler.registerOut(statement, i + 1, colType, dataType, dataTypeName);
339                     break;
340             }
341         }
342     }
343
344     /**
345      * Get the columns for the stored procedure.
346      *
347      * @throws SQLException
348      */

349     protected void fetchColumns() throws SQLException JavaDoc {
350
351         PyExtendedCursor pec = (PyExtendedCursor) cursor.connection.cursor();
352
353         try {
354             pec.datahandler = this.cursor.datahandler;
355
356             pec.procedurecolumns(procedureCatalog, procedureSchema, procedureName, Py.None);
357
358             this.columns = pec.fetchall();
359         } finally {
360             pec.close();
361         }
362     }
363
364     /**
365      * The value for a missing schema or catalog. This value is used to find
366      * the column names for the procedure. Not all DBMS use the same default
367      * value; for instance Oracle uses an empty string and SQLServer a null.
368      * This implementation returns the empty string.
369      *
370      * @return the default value (the empty string)
371      * @see java.sql.DatabaseMetaData#getProcedureColumns
372      */

373     protected PyObject getDefault() {
374         return Py.EmptyString;
375     }
376
377     /**
378      * Construct a procedure name for the relevant schema and catalog information.
379      */

380     protected String JavaDoc getProcedureName() {
381
382         StringBuffer JavaDoc proc = new StringBuffer JavaDoc();
383
384         if (this.procedureCatalog.__nonzero__()) {
385             proc.append(this.procedureCatalog.toString()).append(".");
386         }
387
388         return proc.append(this.procedureName.toString()).toString();
389     }
390 }
391
Popular Tags