RWDBStoredProc
Module: DB Interface Module Group: Database Classes
Does not inherit
- Local Index
- Synopsis
- Description
- Example
- Related Objects
- Public Constructor
- Public Member Operators
- Public Member Functions
Local Index
Members
Non-Members
Synopsis
#include <rw/db/stored.h>
RWDBStoredProc myProc = myDbase.storedProc("myProcName");
Description
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 stored procedure implementations vary widely among RDBMS vendors. If an RWDBStoredProc feature is not supported by the underlying database, SourcePro Core reports an RWDBStatus::notSupported error. The remainder of this section assumes that all features are supported. Check the SourcePro Core Access Module 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 is interested in obtaining 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 RWDBStoredProc's status 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, the DB Interface Module passes along whatever the database reports to the application. The DB Interface Module 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, the DB Interface Module passes along whatever the database reports to the application.
In order to support parameter passing, the DB Interface Module 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 object's implementation is a base class from which a family of database-specific selector implementations is derived.
Example
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;
}
Related Objects
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.
RWDBStoredProc's formal parameter list 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 SourcePro Core datatype.
Public Constructor
RWDBStoredProc();
The default constructor creates an RWDBStoredProc whose status is RWDBStatus::notInitialized. This constructor is provided as a convenience (so that an array of RWDBStoredProcs may be declared, for example). Usable RWDBStoredProcs are obtained from RWDBDatabases.
RWDBStoredProc(const RWDBStoredProc& proc);
Copy constructor. Self shares an implementation with proc.
Public Member Operators
RWDBStoredProc& operator=(const RWDBStoredProc& proc);
Assignment operator. Self shares an 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 RWDateTime& 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); RWDBStoredProc& operator<<(const RWBasicUString& 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.
NOTE -- This function accepts Unicode strings. For more information, see the entry for RWBasicUString in the Essential Tools Module Reference Guide. For more information on internationalization, see Chapter 12 of the DB Interface Module User's Guide.
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<<(RWDateTime* 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); RWDBStoredProc& operator<<(RWBasicUString* 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().
NOTE -- This function accepts Unicode strings. For more information, see the entry for RWBasicUString in the Essential Tools Module Reference Guide. For more information on internationalization, see Chapter 12 of the DB Interface Module User's Guide.
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.
Public Member Functions
void acquire() const;
The function attempts to acquire the internal mutex lock. If the mutex is already locked, 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 which produced self. Returns an RWDBDatabase whose status is RWDBStatus::notInitialized if self was created with the default constructor.
RWDBStatus drop();
When used in an XA-environment, this call may generate a server error. This method can still be used before XA connectivity is established.
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. Check the isValid() method of the returned value to see if the operation succeeded.
RWDBStatus drop(const RWDBConnection& connection);
When used in an XA-environment, this call may generate a server error. This method can still be used before XA connectivity is established.
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. Check the isValid() method of the returned 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 fetchReturns() or returnValue() is called, or the RWDBStoredProc is destroyed. This function can behave asynchronously if executed using an asynchronous connection.
bool exists(bool 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 and sets self's status to reflect the error.
bool
exists(const RWDBConnection& connection,
bool 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 and sets self's status to reflect the error.
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 will behave asynchronously if the stored procedure was created using an asynchronous connection.
bool 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.
bool 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.
bool 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.
bool isReady() const;
This function returns true if the object is in ready state, indicating that accessing the object will not block. Accessing a non-ready object may potentially block.
bool isValid() const;
Returns true if self's status is RWDBStatus::ok, otherwise false.
RWCString name() const;
Returns self's name.
RWDBSchema params(bool 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,
bool 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;
This function 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 RWDBStoredProc's 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(bool 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,
bool 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 Rogue Wave Software, Inc. All Rights Reserved.
Rogue Wave and SourcePro are registered trademarks of Rogue Wave Software, Inc. in the United States and other countries. All other trademarks are the property of their respective owners.
Contact Rogue Wave about documentation or support issues.