Re: OCI8 Stored procedure execution / Design question

On Wed, Jun 14, 2000 at 11:40:13AM -0400, Fred M. Bulah wrote: > > I removed the OCIFetch() and instead replaced it by > > for( OCIExecute( $stmt ); !isset( $return_val ); OCIExecute( $stmt ) ) > { > } > > where $return_val is a bound output parameter to the stored procedure. > The stored procedure sets the value to null once cursor%NOTFOUND condition > occurs. > This works fine. > > What this means is that there are at least 2 ways to write stored procedures > that return rows: > 1. by opening a cursor and returning a ref cursor pointing to it. > 2. by opening a cursor and fetching data into an output variable > > Case 1 uses OCIFetch() to retrieve rows while case 2 uses OCIExecute(). > > Design Questions: > > - Which is more efficient? Are there any clear drawbacks? you'd have to benchmark it. > - I have a wrapper class that is a thin layer over the MySQL API. I > converted this to be a wrapper over the OCI8 API. The existing wrapper > class has a single db_query() interface which directly executed embedded SQL > for MySQL (via mysql_db_query). I am now faced with adapting this interface > for OCI8 and would like to make it handle both raw SQL and calls to stored > procedures and functions. Unfortunately, the mechanism to retrieve data is > different depending on whether the operation is a retrieval or a > modification. Furthermore, there does not seem to be a way to determine > ahead of time whether a stored procedure will return a cursor based results > set or not. My only options appear to be: > - break it into separate interfaces for retrieval, retrieval by cursor, and > modification. > - a single interface with some medium-to-complex logic hmm, it seems rather unlikely that you have to be cross-database-api. mysql does not support stored procedures (yet) and has not refcursort stuff AFAIK. tc > > Has anyone faced this issue before? Are there any other possibilities? <snip>