#include <rw/db/stored.h>
RWDBStoredProc myProc = myDbase.storedProc("myProcName");
Many modern RDBMS implementations include a mechanism to enforce database policy through stored procedures. Applications may be required to do much of their data manipulation through stored procedures. Unfortunately, the standards bodies have had little to say about stored procedures, so implementations vary widely among RDBMS vendors. If an RWDBStoredProc feature is not supported by the underlying database, DBTools.h++ reports an RWDBStatus::notSupported error. The remainder of this section assumes that all features are supported. Check the DBTools.h++ access library guide for information about what features are supported by a particular database.
RWDBStoredProc is an encapsulation of a database stored procedure. RWDBStoredProc supports creating and deleting stored procedures, retrieving stored procedures definitions, executing stored procedures, and processing results returned from stored procedure execution. Parameters may be passed to an RWDBStoredProc prior to execution, and the values of output parameters may be retrieved. If a database vendor's stored procedure may return multiple sets of results, RWDBStoredProc can access each result set in turn.
RWDBStoredProc uses an RWDBSchema to store information about its formal parameters. Use the insertion operator << to pass actual parameters to an RWDBStoredProc. Insert values if the stored procedure expects IN parameters; insert pointers if the stored procedure expects OUT or IN/OUT parameters and your application needs to obtain results through the parameters. Insert rwdbNull to pass a literal NULL by value.
NOTE:It is an error to insert a NULL pointer; if this occurs, the status of the RWDBStoredProc changes to RWDBStatus::nullReference.
RWDBStoredProc maintains a notion of the current position in its parameter list. The current position is set to zero when the RWDBStoredProc is created, and reset to zero whenever the RWDBStoredProc is executed. Each insertion of an actual parameter increments the current position by one. The indexing operator [] can be used to access a particular parameter position by number or by name. Given a stored procedure myStoredProc which expects the parameters number and name in that order, the following notations are equivalent:
RWCString hello("Hello World");
myStoredProc << 1 << hello;
myStoredProc[0] << 1;
myStoredProc[1] << hello;
myStoredProc["name"] << hello;
myStoredProc["number"] << 1;
RWDBStoredProc does not check actual parameters for type; it allows the database to do type conversion. If there is a type incompatibility, DBTools.h++ passes along whatever the database reports to the application. DBTools.h++ produces an RWDBStatus::invalidPosition error if too many arguments are inserted into an RWDBStoredProc. No check is made for too few arguments. The database may supply defaults; if not, DBTools.h++ passes along whatever the database reports to the application.
In order to support parameter passing, DBTools.h++ uses a connection to query the database for schema information whenever an RWDBStoredProc is produced by an RWDBDatabase.
RWDBStoredProc is designed around the Interface/Implementation paradigm. An RWDBStoredProc instance is an interface to a reference-counted implementation; copy constructors and assignment operators produce additional references to a shared implementation. An RWDBStoredProc implementation is a base class from which a family of database-specific selector implementations is derived.
In this example, suppose there is a database procedure getId, which expects two parameters: name, an input string representing a customer's name, and id, an output integer parameter representing the customer's id. The procedure is supposed to look up the customer id for the given customer name. If successful, it places the id in its id parameter and returns 0. Otherwise it returns -1 and does not change id.
RWDBStoredProc getId = myDbase.storedProc("getId");
RWCString name("John Doe");
int id;
getId << name << &id;
getId.execute();
RWDBValue retValue = getId.returnValue();
if (retValue.asInt() == 0)
{
cout << "John Doe's id is " << id << endl;
}
else
{
cout << "Can't find John Doe's id" << endl;
}
RWDBValueManip rwdbNull;
rwdbNull is used to represent a literal NULL value. A literal NULL value can be passed to a stored procedure by inserting rwdbNull into the RWDBStoredProc:
storedProc << rwdbNull;
RWDBValueManip is a typedef:
typedef void (*RWDBValueManip)(RWDBValue&);
The result of RWDBStoredProc::execute() is an RWDBResult, which represents a sequence of zero or more RWDBTables. See RWDBResult and RWDBTable for details.
The formal parameter list for RWDBStoredProc is stored as an RWDBSchema, which is a list of RWDBColumns. See RWDBSchema and RWDBColumn for details.
The result of RWDBStoredProc::returnValue() is an RWDBValue, which can store any DBTools.h++ datatype.
RWDBStoredProc();
The default constructor creates an RWDBStoredProc whose status is RWDBStatus::notInitialized. This constructor is provided as a convenience, for example, for declaring an array of RWDBStoredProcs. Usable RWDBStoredProcs are obtained from RWDBDatabases.
RWDBStoredProc(const RWDBStoredProc& proc);
Copy constructor. Self shares an implementation with proc.
RWDBStoredProc& operator=(const RWDBStoredProc& proc);
Assignment operator. Self share san implementation with proc.
RWDBStoredProc& operator[](size_t index);
Self's current row position is set to index. If index is greater than or equal to the number of parameters expected by self, self's status is changed to RWDBStatus::invalidPosition. Returns a reference to self.
RWDBStoredProc& operator[](const RWCString& paramName);
Self's current row position is set to the index in self's schema of the first parameter whose name is paramName. If there is no such parameter, self's status changes to RWDBStatus::invalidPosition. Returns a reference to self.
RWDBStoredProc& operator[](const RWDBColumn& paramColumn);
Self's current row position is set to the index in self's schema of the first parameter whose name is paramColumn. If there is no such parameter, self's status changes to RWDBStatus::invalidPosition. Returns reference to self.
RWDBStoredProc& operator<<(char value); RWDBStoredProc& operator<<(unsigned char value); RWDBStoredProc& operator<<(short value); RWDBStoredProc& operator<<(unsigned short value); RWDBStoredProc& operator<<(int value); RWDBStoredProc& operator<<(unsigned int value); RWDBStoredProc& operator<<(long value); RWDBStoredProc& operator<<(unsigned long value); RWDBStoredProc& operator<<(float value); RWDBStoredProc& operator<<(double value); RWDBStoredProc& operator<<(const RWDecimalPortable& value); RWDBStoredProc& operator<<(const RWDate& value); RWDBStoredProc& operator<<(const RWDBDateTime& value); RWDBStoredProc& operator<<(const RWDBDuration& value); RWDBStoredProc& operator<<(const RWCString& value); RWDBStoredProc& operator<<(const RWDBBlob& value); RWDBStoredProc& operator<<(const RWDBMBString& value); RWDBStoredProc& operator<<(const RWWString& value); RWDBStoredProc& operator<<(RWDBValueManip value); RWDBStoredProc& operator<<(const RWDBValue& value);
Passes value as the nth parameter to self, where n is the current position in self's formal parameter list, and increments the current position by one. The parameter is passed by value.
RWDBStoredProc& operator<< (short* valPtr); RWDBStoredProc& operator<< (unsigned short* valPtr); RWDBStoredProc& operator<< (int* valPtr); RWDBStoredProc& operator<< (unsigned int* valPtr); RWDBStoredProc& operator<< (long* valPtr); RWDBStoredProc& operator<< (unsigned long* valPtr); RWDBStoredProc& operator<< (float* valPtr); RWDBStoredProc& operator<< (double* valPtr); RWDBStoredProc& operator<< (RWDecimalPortable* valPtr); RWDBStoredProc& operator<< (RWDate* valPtr); RWDBStoredProc& operator<< (RWDBDateTime* valPtr); RWDBStoredProc& operator<< (RWDBDuration* valPtr); RWDBStoredProc& operator<< (RWDBBlob* valPtr); RWDBStoredProc& operator<< (RWCString* valPtr); RWDBStoredProc& operator<<(RWDBMBString* valPtr); RWDBStoredProc& operator<<(RWWString* valPtr);
Passes valPtr as the nth parameter to self, where n is the current position in self's formal parameter list, and increments the current position by 1. The parameter is passed by reference. After executing the stored procedure, the variable addressed by valPtr is updated by calling either fetchReturnParams() or returnValue().
RWDBStoredProc& operator<<(const RWDBBoundExpr& boundexpr);
Inserts a placeholder and performs binding for in-type parameters. Here in-type binding includes in/out binding. The meaning of shifting in an RWDBBoundExpr with entries greater than 1 is vendor-dependent.
void acquire() const;
Attempts to acquire the internal mutex lock. If the mutex is already locked by another thread, the function blocks until the mutex is released. This function can be called from a const object. Note: in nonmultithreaded builds, this function evaluates to a no-op.
RWDBStatus clear();
Clears all internal state.
RWDBDatabase database() const;
Returns the RWDBDatabase that produced self. Returns an RWDBDatabase whose status is RWDBStatus::notInitialized, if self was created with the default constructor.
RWDBStatus drop();
Uses a default database connection to execute the database-specific equivalent of the SQL statement:
DROP PROCEDURE <name>
where <name> is the name of the database stored procedure encapsulated by self. You can check the isValid() method of the return value to see if the operation succeeded.
RWDBStatus drop(const RWDBConnection& connection);
Uses the supplied connection to execute the database-specific equivalent of the SQL statement:
DROP PROCEDURE <name>
where <name> is the name of the database stored procedure encapsulated by self. You can check the isValid() method of the return value to see if the operation succeeded. This function can behave asynchronously if executed using an asynchronous connection.
RWDBStatus::ErrorHandler errorHandler() const;
Returns the error handler attached to self.
RWDBResult execute();
Uses a default database connection to execute the stored procedure encapsulated by self. The connection is held by the RWDBResult until the RWDBResult is destroyed. The connection is also held by self until either fetchReturnParams() or returnValue() is called, or the RWDBStoredProc is destroyed.
RWDBResult execute(const RWDBConnection& connection);
Uses the supplied connection to execute the stored procedure encapsulated by self. The connection is held by the RWDBResult until the RWDBResult is destroyed. The connection is also held by self until either fetchReturnParams() or returnValue() is called, or the RWDBStoredProc is destroyed. This function can behave asynchronously if executed using an asynchronous connection.
RWBoolean exists(RWBoolean forceLookup = FALSE) const;
Returns TRUE if the stored procedure encapsulated by self exists in the database. The result is cached in self. If self's internal cache is empty, or if forceLookup is TRUE, uses a default database connection to obtain the information from the database, otherwise returns the cached information. If an error occurs, returns FALSE.
RWBoolean
exists(const RWDBConnection& connection,
RWBoolean forceLookup = FALSE) const;
Returns TRUE if the stored procedure encapsulated by self exists in the database. The result is cached in self. If self's internal cache is empty, or if forceLookup is TRUE, uses the supplied connection to obtain the information from the database, otherwise returns the cached information. If an error occurs, returns FALSE.
RWDBStatus fetchReturnParams();
The connection used by the last call to execute() is used to obtain any output parameters returned by the database stored procedure. If execute() has not yet been successfully invoked, sets self's status and returns RWDBStatus::notConnected. Some databases do not support return parameters, in this case RWDBStatus::notSupported is returned. This function behaves asynchronously if the stored procedure was created using an asynchronous connection.
RWBoolean isNull(size_t index) const;
Returns TRUE if the last value fetched into the output parameter at position index was NULL. Otherwise returns FALSE. If there is no such output parameter at index, self's status changes to RWDBStatus::invalidPosition.
RWBoolean isNull(const RWCString& name) const;
Returns TRUE if the last value fetched into the output parameter with the given name was NULL. Otherwise returns FALSE. If there is no such output parameter with that given name, self's status changes to RWDBStatus::invalidPosition.
RWBoolean isNull(const RWDBColumn& column) const;
Returns TRUE if the last value fetched into the output parameter with the same name as column was NULL. Otherwise returns FALSE. If there is no such output parameter with the same name, self's status changes to RWDBStatus::invalidPosition.
RWBoolean isReady() const;
This function returns TRUE if the object is in ready state, indicating that accessing the object will not block. Accessing a nonready object may potentially block.
RWBoolean isValid() const;
Returns TRUE if self's status is RWDBStatus::ok, otherwise returns FALSE.
RWCString name() const;
Returns self's name.
RWDBSchema params(RWBoolean forceLookup = FALSE) const;
Returns an RWDBSchema which represents self's formal parameter list. The result is cached in self. If self's internal cache is empty, or if forceLookup is TRUE, a default database connection is used to obtain schema from the database. Otherwise, returns the cached schema. Returns an empty schema if self has no parameters or if an error occurs. In the latter case, sets self's status to reflect the error.
RWDBSchema
params(const RWDBConnection& connection,
RWBoolean forceLookup = FALSE) const;
Returns an RWDBSchema, which represents self's formal parameter list. The result is cached in self. If self's internal cache is empty, or if forceLookup is TRUE, the supplied connection is used to obtain schema from the database. Otherwise, returns the cached schema. Returns an empty schema if self has no parameters or if an error occurs. In the latter case, sets self's status to reflect the error.
void release() const;
Releases a previously acquired mutex. This function can be called from a const object. Note: in nonmultithreaded builds, this function evaluates to a no-op.
RWDBValue returnValue() const;
Calls fetchReturnParams(), then, if no error has occurred, obtains the return value of the most recent execution of self in the database and returns it. If there is no return value, or if an error has occurred, returns a NULL RWDBValue. In the latter case sets self's status to reflect the error.
void setErrorHandler(RWDBStatus::ErrorHandler handler);
Installs handler as self's error handler. The supplied handler is inherited by all objects produced by self. By default, an RWDStoredProc error handler is inherited from the object which produced it; this method overrides the default. ErrorHandler is declared as a typedef within the scope of RWDBStatus:
typedef void (*ErrorHandler)(const RWDBStatus&);
RWDBStatus status() const;
Returns the current status of self.
RWCString text(RWBoolean forceLookup = FALSE) const;
Returns the SQL text used to define the stored procedure encapsulated by self. The result is cached in self. If self's internal cache is empty, or if forceLookup is TRUE, a default database connection is used to obtain the SQL text from the database. Otherwise, returns the cached text. If unsuccessful, returns an empty string and sets self's status to reflect the error.
RWCString
text(const RWDBConnection& connection,
RWBoolean forceLookup = FALSE) const;
Returns the SQL text used to define the stored procedure encapsulated by self. The result is cached in self. If self's internal cache is empty, or if forceLookup is TRUE, the supplied connection is used to obtain the SQL text from the database. Otherwise, returns the cached text. If unsuccessful, returns an empty string and sets self's status to reflect the error.
©Copyright 1999, Rogue Wave Software, Inc.
Contact Rogue Wave about documentation or support issues.