| CursorExampleFindDirectFlights.java |
1 package com.orindasoft.demo.generated.plsql;
2
3 import java.sql.*;
4
5 // From oracle 9.0.1 "oracle.jdbc.driver" is deprecated and
6 // its contents move to "oracle.jdbc". "oracle.jdbc.driver"
7 // still exists in 9.2.0.
8 import oracle.jdbc.OracleTypes;
9 // import oracle.jdbc.driver.OracleTypes;
10
11 import com.orindasoft.pub.LogInterface;
12 import com.orindasoft.pub.OracleResourceUser;
13 import com.orindasoft.pub.CSException;
14 import com.orindasoft.demo.generated.plsql.AbstractProcCall;
15
16 /**
17 * CursorExampleFindDirectFlights - Access Procedure CURSOR_EXAMPLE.FIND_DIRECT_FLIGHTS
18 *
19 * <p>Generated by JDBCWizard./a> build 2776 at 2011/03/07 19:55:33.626 GMT
20 * <p>JDBCWizard./a> is made by Orinda Software Ltd, Dublin, Ireland
21 * <p>Target Database: Oracle 10.2.0
22 * <p> -------------------------------------------------------------------------
23 * <p> WARNING: This code will stop working around the time JDBCWizard./a> expires.
24 * <p> This restriction only exists in the demo version of JDBCWizard./a>.
25 * <p> -------------------------------------------------------------------------
26 * <p> JDBCWizard./a> Tips 5 of 19:
27 * <p> JDBCWizard./a> can run stored procedures with Oracle TYPE and %ROWTYPE parameters
28 * <p> -------------------------------------------------------------------------
29 *
30 * <p>Copyright Orinda Software 2003-2009
31 * <p>Access Basis: User Object
32 * <p>PLSQL source code:
33 * <code><pre>
34 -----------------------------------------------------------------
35 * PACKAGE CURSOR_EXAMPLE AS
36 * --
37 * /*
38 * THIS EXAMPLE SHOWS HOW TO CALL PL/SQL PROCEDURE THAT RETURNS ONE OR MORE CURSORS FROM JAVA.
39 *
40 *
41 * --
42 * TYPE AIRLINE_REFCURSOR_TYPE IS REF CURSOR RETURN AIRLINES%ROWTYPE;
43 * --
44 * TYPE AIRPORT_REFCURSOR_TYPE IS REF CURSOR RETURN AIRPORTS%ROWTYPE;
45 * --
46 * TYPE AIRCRAFT_REFCURSOR_TYPE IS REF CURSOR RETURN AIRCRAFT%ROWTYPE;
47 * --
48 * TYPE FLIGHTS_REFCURSOR_TYPE IS REF CURSOR RETURN FLIGHTS%ROWTYPE;
49 * --
50 * PROCEDURE GETLISTS(P_AIRLINE_LISTING OUT AIRLINE_REFCURSOR_TYPE
51 * ,P_AIRPORT_LISTING OUT AIRPORT_REFCURSOR_TYPE
52 * ,P_AIRCRAFT_LISTING OUT AIRCRAFT_REFCURSOR_TYPE);
53 * --
54 * FUNCTION DIRECT_FLIGHT_AVAILABLE(P_FROMCITY IN AIRPORTS.AIRPORT_CODE%TYPE
55 * ,P_TOCITY IN AIRPORTS.AIRPORT_CODE%TYPE) RETURN BOOLEAN;
56 * --
57 * PROCEDURE FIND_DIRECT_FLIGHTS (P_FROMCITY IN AIRPORTS.AIRPORT_CODE%TYPE
58 * ,P_TOCITY IN AIRPORTS.AIRPORT_CODE%TYPE
59 * ,P_ORDERBY IN NUMBER
60 * ,P_FLIGHTLIST OUT FLIGHTS_REFCURSOR_TYPE);
61 * --
62 * END;
63 -----------------------------------------------------------------
64 * </pre></code>
65 * @author devteam60@orindabuild.com
66 * @version 1.1
67 */
68 public class CursorExampleFindDirectFlights extends AbstractProcCall implements OracleResourceUser
69 {
70
71 /**
72 * Variable to store P_FROMCITY
73 */
74 public String paramPFromcity = null;
75
76 /**
77 * Variable to store P_TOCITY
78 */
79 public String paramPTocity = null;
80
81 /**
82 * Variable to store P_ORDERBY
83 */
84 public java.math.BigDecimal paramPOrderby = null;
85
86 /**
87 * Variable to store P_FLIGHTLIST
88 */
89 public com.orindasoft.pub.ReadOnlyRowSet paramPFlightlist = null;
90
91 /**
92 * Variable to store Buffer Size for file access.
93 */
94 protected int bufferSize = 4096;
95
96 /**
97 * boolean flag that specifies whether lobs such as CLOBS, BLOBS and BFILES
98 * will be kept as Oracle objects or downloaded into Files.
99 * @since 4.0.1847
100 */
101 protected boolean keepLobs = false;
102
103 /**
104 * boolean flag that controls whether LOBs are turned into byte[]
105 * @since 5.0.2314
106 */
107 protected boolean useByteArraysForLongsAndLOBS = false;
108
109 /**
110 * Variable to store boolean flag that indicates whether created files should be deleted when the JVM exits.
111 */
112 protected boolean keepFiles = true;
113
114 /**
115 * Variable to store temporary directory for downloaded files.
116 */
117 protected java.io.File tempFileDir = new java.io.File (System.getProperty("user.dir"));
118
119 /**
120 * Variable to store Long object loader.
121 */
122 protected com.orindasoft.pub.LongObjectLoader objectLoader = new com.orindasoft.pub.LongObjectLoader();
123
124 /**
125 * The Prefix for downloaded files containing BLOB, CLOB and BFILE data
126 */
127 protected String tempFilePrefix = "OSOFT";
128
129 /**
130 * The Suffix for downloaded files containing BLOB, CLOB and BFILE data
131 */
132 protected String tempFileSuffix = ".tmp";
133
134 /**
135 * If set to true this flag causes Temporary LOB's to be created if no
136 * LOB parameter has been set by the time the database is accessed
137 * @since JDBCWizard./a> 4.0.2107 / Oracle 9.0.1
138 */
139 protected boolean createTempLobsIfNeeded = true;
140
141 /**
142 * If true this flag causes Temporary LOB's to be deleted after
143 * the database is accessed
144 * @since JDBCWizard./a> 4.0.2107 / Oracle 9.0.1
145 */
146 protected boolean deleteTempLobsAfterCall = false;
147
148 /**
149 * Variable to store max rows for ReadOnlyRowSet
150 */
151 protected int maxRows = 10000;
152
153 /**
154 * The name of the procedure or function we will access.
155 * This name be actually refer to a synonym or somebody else's code
156 */
157 public static final String procName = "CURSOR_EXAMPLE.FIND_DIRECT_FLIGHTS";
158
159 /**
160 * Procedure Type
161 * What kind of function this is.
162 */
163 public static final int PROC_TYPE = AbstractProcCall.THIS_USERS_OBJECT;
164
165 /**
166 * Procedure call text
167 * The anonymous PL/SQL block that we use to run the procedure.
168 * This used to be a String but we now use a StringBuffer because
169 * procCall can be > 64K. We fill this in on first use.
170 */
171 private StringBuffer procCall = null;
172
173 /**
174 * Constructor that calls the Constructor of AbstractProcCall
175 *
176 */
177 public CursorExampleFindDirectFlights (Connection theConnection, LogInterface theLog)
178 {
179 super(theConnection, theLog);
180 theLog.debug("CursorExampleFindDirectFlights started"); //DEBUG
181 }
182
183
184 /**
185 * Method to set parameter P_FROMCITY
186 * @param String paramPFromcity
187 */
188 public void setParamPFromcity(String paramPFromcity)
189 {
190 this.paramPFromcity = paramPFromcity;
191 }
192
193 /**
194 * Method to set parameter P_FROMCITY to null
195 */
196 public void setParamPFromcityToNull()
197 {
198 this.paramPFromcity = null;
199 }
200
201 /**
202 * Method to set parameter P_TOCITY
203 * @param String paramPTocity
204 */
205 public void setParamPTocity(String paramPTocity)
206 {
207 this.paramPTocity = paramPTocity;
208 }
209
210 /**
211 * Method to set parameter P_TOCITY to null
212 */
213 public void setParamPTocityToNull()
214 {
215 this.paramPTocity = null;
216 }
217
218 /**
219 * Method to set parameter P_ORDERBY
220 * @param double paramPOrderby
221 */
222 public void setParamPOrderby(double paramPOrderby)
223 {
224 this.paramPOrderby = new java.math.BigDecimal(paramPOrderby);
225 }
226
227 /**
228 * Method to set parameter P_ORDERBY
229 * @param long paramPOrderby
230 */
231 public void setParamPOrderby(long paramPOrderby)
232 {
233 this.paramPOrderby = new java.math.BigDecimal((double)paramPOrderby);
234 }
235
236 /**
237 * Method to set parameter P_ORDERBY
238 * @param int paramPOrderby
239 */
240 public void setParamPOrderby(int paramPOrderby)
241 {
242 this.paramPOrderby = new java.math.BigDecimal((double)paramPOrderby);
243 }
244
245 /**
246 * Method to set parameter P_ORDERBY
247 * @param java.math.BigDecimal paramPOrderby
248 */
249 public void setParamPOrderby(java.math.BigDecimal paramPOrderby)
250 {
251 if (paramPOrderby == null)
252 {
253 this.paramPOrderby = null;
254 }
255 else
256 {
257 this.paramPOrderby = new java.math.BigDecimal(paramPOrderby.doubleValue());
258 }
259 }
260
261 /**
262 * Method to set parameter P_ORDERBY to null
263 */
264 public void setParamPOrderbyToNull()
265 {
266 this.paramPOrderby = null;
267 }
268
269 /**
270 * Set new file io buffer size
271 * @param int bufferSize A new Buffer size in bytes.
272 */
273 public void setBufferSize(int bufferSize)
274 {
275 this.bufferSize = bufferSize;
276 theLog.debug("bufferSize set to " + bufferSize);
277 }
278
279 /**
280 * Set keepLobs
281 * @param boolean keepLobs Keep Lobs as pointers rather than turn them into files.
282 * @since 4.0.1847
283 */
284 public void setKeepLobs(boolean keepLobs )
285 {
286 this.keepLobs = keepLobs;
287 theLog.debug("keepLobs set to " + keepLobs);
288 }
289
290 /**
291 * Set useByteArraysForLongsAndLOBS
292 * @param boolean useByteArraysForLongsAndLOBS Turn lobs into byte[]
293 * @since 5.0.2314
294 */
295 public void setUseByteArraysForLongsAndLOBS(boolean useByteArraysForLongsAndLOBS)
296 {
297 this.useByteArraysForLongsAndLOBS = useByteArraysForLongsAndLOBS;
298 theLog.debug("useByteArraysForLongsAndLOBSset to " + useByteArraysForLongsAndLOBS);
299 }
300
301 /**
302 * Set keepFiles
303 * @param boolean keepFiles Keep generated files after JVM exits
304 */
305 public void setKeepFiles(boolean keepFiles )
306 {
307 this.keepFiles = keepFiles;
308 theLog.debug("keepFiles set to " + keepFiles);
309 }
310
311 /**
312 * Delete all generated files
313 * This method deletes all Files created by accessing CLOB's, BLOB's, etc
314 * It was added because 'setKeepFiles' only works when the JVM exits, which
315 * could be several days in the future in some scenarios. Only use this method if
316 * you want <b>all<b> your generated files to be deleted.
317 * @since 2.0.1176
318 */
319 public void deleteGeneratedFiles()
320 {
321 theLog.debug("Starting to delete files if they exist");
322 theLog.debug("Delete files owned by ReadOnlyRowSetparamPFlightlist if they exist");
323 // Delete ReadOnlyRowSet files for P_FLIGHTLIST
324 if (paramPFlightlist != null)
325 {
326 // Because the fields in a cursor aren't known up front we
327 // have no way of telling whether any ReadOnlyRowSet
328 // has files in it so call the delete method regardless
329 paramPFlightlist.deleteGeneratedFiles();
330 }
331
332 theLog.debug("Finished deleting files");
333 }
334
335 /**
336 * Set temporary directory
337 * @param java.io.File tempFileDir a new Temporary Directory
338 * @throws CSException if the directory is not viable
339 */
340 public void setTempDir(java.io.File tempFileDir) throws CSException
341 {
342 if (tempFileDir == null)
343 {
344 throw (new CSException("Attempt made to set tempFileDir to null"));
345 }
346 else if (! tempFileDir.exists())
347 {
348 try
349 {
350 theLog.info("Creating temporary directory " + tempFileDir.getAbsolutePath());
351 tempFileDir.mkdirs();
352 }
353 catch (Exception e)
354 {
355 throw (new CSException("tempFileDir " + tempFileDir.getAbsolutePath() + " can not be created"));
356 }
357 }
358
359 this.tempFileDir = tempFileDir;
360 theLog.debug("tempFileDir set to " + tempFileDir.getAbsolutePath());
361 }
362
363 /**
364 * Set the prefix used for generating temporary files
365 */
366 public void setTempFilePrefix(String tempFilePrefix)
367 {
368 this.tempFilePrefix = tempFilePrefix;
369 }
370
371 /**
372 * Set the suffix used for generating temporary files
373 */
374 public void setTempFileSuffix(String tempFileSuffix)
375 {
376 this.tempFileSuffix = tempFileSuffix;
377 }
378
379 /**
380 * Set flag that controls whether Temporary Lobs are created before
381 * each database call if no LOB parameter has been set
382 * @since JDBCWizard./a> 4.0.2107 / Oracle 9.0.1
383 */
384 public void setCreateTempLobsIfNeeded(boolean createTempLobsIfNeeded)
385 {
386 this.createTempLobsIfNeeded = createTempLobsIfNeeded;
387 }
388
389 /**
390 * Set flag that controls whether Temporary Lobs are deleted after each database call
391 * @since JDBCWizard./a> 4.0.2107 / Oracle 9.0.1
392 */
393 public void setDeleteTempLobsAfterCall(boolean deleteTempLobsAfterCall)
394 {
395 this.deleteTempLobsAfterCall = deleteTempLobsAfterCall;
396 }
397
398 /**
399 * Set limit to number of rows returned in ReadOnlyRowSets
400 * @param int maxRows Maximum number of rows that will be retrieved. Some JDBC drivers will
401 * stop returning rows after about 30,000.
402 */
403 public void setMaxRows(int maxRows)
404 {
405 this.maxRows = maxRows;
406 theLog.debug("maxRows set to " + maxRows);
407 }
408
409 /**
410 * Get number of rows returned in ReadOnlyRowSets
411 * @return int maxRows Maximum number of rows that will be retrieved.
412 */
413 public int getMaxRows()
414 {
415 return(maxRows);
416 }
417
418
419 /**
420 * Method to get P_FLIGHTLIST
421 * @return com.orindasoft.pub.ReadOnlyRowSet
422 * @throws NullPointerException if P_FLIGHTLIST is null
423 */
424 public com.orindasoft.pub.ReadOnlyRowSet getParamPFlightlist()
425 {
426 return (paramPFlightlist);
427 }
428
429 /**
430 * Return paramPFlightlist as an array of CursorExampleFlightsRefcursorTypeAttrs
431 * @return CursorExampleFlightsRefcursorTypeAttrs[] an Array of CursorExampleFlightsRefcursorTypeAttrs
432 * @throws CSException
433 * @since JDBCWizard./a> V4.0.2160
434 */
435 public CursorExampleFlightsRefcursorTypeAttrs[] getParamPFlightlistArray() throws CSException
436 {
437 if (paramPFlightlist == null)
438 {
439 return(null);
440 }
441
442 return ((CursorExampleFlightsRefcursorTypeAttrs[])CursorExampleFlightsRefcursorType.createCursorExampleFlightsRefcursorTypeArrayFromRowSet(paramPFlightlist,theLog));
443 }
444
445
446 /**
447 * Get current file io buffer size
448 * @return int bufferSize Buffer size in bytes.
449 */
450 public int getBufferSize()
451 {
452 return (bufferSize);
453 }
454
455 /**
456 * Get keepLobs flag
457 * @return int <code>true</code> if LOBS are left as they are found in the DB
458 * @return int <code>false</code> if LOBS are turned into files on retrieval.
459 * @since 4.0.1847
460 */
461 public boolean getKeepLobs()
462 {
463 return (keepLobs);
464 }
465
466 /**
467 * Get flag that controls whether LOBs are turned into byte[].
468 * @return boolean useByteArraysForLongsAndLOBS
469 * @since 5.0.2314
470 */
471 public boolean getUseByteArraysForLongsAndLOBS()
472 {
473 return (useByteArraysForLongsAndLOBS);
474 }
475
476 /**
477 * Get keepFiles flag
478 * @return int <code>true</code> if temporary files are kept after the JVM exits.
479 * @return int <code>false</code> if temporary files are deleted after the JVM exits.
480 */
481 public boolean getKeepFiles()
482 {
483 return (keepFiles);
484 }
485
486 /**
487 * Get temporary directory
488 * @return java.io.File tempFileDir the temporary Directory
489 */
490 public java.io.File getTempFileDir()
491 {
492 return(tempFileDir);
493 }
494
495 /**
496 * Get the prefix used for generating temporary files
497 */
498 public String getTempFilePrefix()
499 {
500 return (tempFilePrefix);
501 }
502
503 /**
504 * Get the suffix used for generating temporary files
505 */
506 public String getTempFileSuffix()
507 {
508 return (tempFileSuffix);
509 }
510
511
512 /**
513 * Unload OUT parameters
514 */
515 protected void getStatementResults() throws CSException
516 {
517 theLog.debug("Starting to unload data");
518 objectLoader.setBufferSize(bufferSize);
519 objectLoader.setKeepFiles(keepFiles);
520
521 theParameters.unloadParameters(theCallableStatement);
522
523 // Unload parameter P_FLIGHTLIST
524
525 theLog.debug("Unloading parameter paramPFlightlist" );
526 try
527 {
528 paramPFlightlist = new com.orindasoft.pub.ReadOnlyRowSet((ResultSet)theParameters.getParam(4), getProcCallStatement(),maxRows, theLog,tempFileDir,keepFiles,tempFilePrefix,tempFileSuffix,keepLobs, useByteArraysForLongsAndLOBS);
529 }
530 catch (com.orindasoft.pub.CSDBNullObjectException e)
531 // A CSDBNullObjectException here means that a stored procedure or function
532 // has returned a null Ref Cursor
533 {
534 paramPFlightlist = null;
535 theLog.debug("Ref Cursor for paramPFlightlist is null. No ReadOnlyRowSet created." );
536 }
537
538 try
539 {
540 theCallableStatement.clearParameters();
541 theParameters.clearParameters();
542 }
543 catch (SQLException e)
544 {
545 throw (new CSException("getStatementResults: Unable to clear parameters:" + e.toString()));
546 }
547 theLog.debug("Finished unloading data");
548 }
549
550
551
552 /**
553 * Associate parameters with statement
554 */
555 protected void bindParams() throws CSException
556 {
557 theLog.debug("Starting to bind parameters");
558 // Bind parameter P_FROMCITY
559
560 theLog.debug("binding input parameter paramPFromcity to position 1");
561 theParameters.setParam(1,paramPFromcity);
562 // Bind parameter P_TOCITY
563
564 theLog.debug("binding input parameter paramPTocity to position 2");
565 theParameters.setParam(2,paramPTocity);
566 // Bind parameter P_ORDERBY
567
568 theLog.debug("binding input parameter paramPOrderby to position 3");
569 theParameters.setParam(3,paramPOrderby);
570 // Bind parameter P_FLIGHTLIST
571 theLog.debug("binding output parameter paramPFlightlist to position 4" );
572 theParameters.setOutParam(4,OracleTypes.CURSOR);
573 theLog.debug("Associating parameters with statement");
574 theParameters.bindParameters(theCallableStatement);
575 theLog.debug("Finished binding parameters");
576 }
577
578
579 /**
580 * Return a SQL statement that will invoke this stored procedure
581 * This may be a synonym or owned by somebody else.
582 *
583 */
584 public String getProcCallStatement()
585 {
586 if (procCall == null)
587 {
588 procCall = new StringBuffer("DECLARE \n"); // 1
589 procCall.append("/* Created By JDBCWizard./a> 6.0.2776 */ \n"); // 2
590 procCall.append("/* Which can be obtained at www.orindasoft.com */ \n"); // 3
591 procCall.append("p_fromcity VARCHAR2(16) := ?; \n"); // 4
592 procCall.append("p_tocity VARCHAR2(16) := ?; \n"); // 5
593 procCall.append("p_orderby NUMBER := ?; \n"); // 6
594 procCall.append("TYPE P_FLIGHTLIST_RC IS REF CURSOR; \n"); // 7
595 procCall.append("p_flightlist P_FLIGHTLIST_RC; \n"); // 8
596 procCall.append("BEGIN \n"); // 9
597 procCall.append(" \n"); // 10
598 procCall.append("CURSOR_EXAMPLE.FIND_DIRECT_FLIGHTS(p_fromcity => p_fromcity,p_tocity => p_tocity,p_orderby => p_orderby,p_flightlist => p_flightlist); \n"); // 11
599 procCall.append("? := p_flightlist; \n"); // 12
600 procCall.append("END; "); // 13 394 characters
601
602 }
603 return(procCall.toString());
604 }
605
606} // Generated by JDBCWizard./a> 6.0.2776
607