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  ;
18  import java.sql.DatabaseMetaData  ;
19  import java.sql.SQLException  ;
20  import java.util.BitSet  ;
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   {
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   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   {
111 
112         this.cursor = cursor;
113         this.inputSet = new BitSet  ();
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   prepareCall() throws SQLException   {
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   prepareCall(PyObject rsType, PyObject rsConcur) throws SQLException   {
155 
156         // prepare the statement
157         CallableStatement   statement = null;
158         boolean normal = ((rsType == Py.None) && (rsConcur == Py.None));
159 
160         try {
161 
162             // build the full call syntax
163             String   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   e) {
177             if (statement != null) {
178                 try {
179                     statement.close();
180                 } catch (Exception   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   {
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   {
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   toSql() throws SQLException   {
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   sql = new StringBuffer  ("{");
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   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   statement) throws SQLException   {
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   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   {
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   getProcedureName() {
381 
382         StringBuffer   proc = new StringBuffer  ();
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
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags