OParameter

A parameter is an object that enables you to place variables within a SQL statement. The simplest use would be in the SQL statement that opens a dynaset:

select * from mytable where column = :pvalue

In this example :pvalue is a parameter. (In SQL syntax, a parameter name is prefixed with a colon.) When the SQL statement is used, the current value of the parameter is substituted for :pvalue. Such a parameter can be used wherever a literal value can be placed: values within "update" and "insert" statements and values that are part of "where" clauses.

Parameters are also used to represent arguments in calls to stored procedures. A stored procedure is a PL/SQL program that is stored in the Oracle database. Parameters can be used both as input and as output variables. See the example in ExecuteSQL for a sample of calling a stored procedure.

Parameters are managed as OParameter objects. OParameter objects are managed by way of an OParameterCollection that exists for every database object. You attach parameter objects to databases by using the Add method of the OParameterCollection class. OParameter is a subclass of OOracleObject.

The primary benefit of using OParameter is efficiency. The Oracle database knows how its records are to be fetched by remembering each SQL statement. When a precisely equal SQL statement is handed to the server, it can be processed quickly, because the database simply reuses the existing information. By using parameters instead of explicit values, you do not have to change the SQL statement when the database values change. Therefore, the SQL statement can be reused. This reuse is also convenient for you, because you also can modify the SQL statement without having to retain the entire string.

Parameters are attached to an ODatabase object. By default, whenever an ODynaset is opened or refreshed with a new SQL statement, all parameters that are attached to the parent ODatabase (the ODatabase on which the ODynaset is being opened) are bound to the ODynaset. A bound OParameter is said to be "enabled". By default OParameters are auto-enabled. This means that they can bind to any ODynasets. By using the AutoEnable method, you can turn this default behavior off and on. This can be useful if there are a large number of OParameter objects, which could result in many parameters being unnecessarily bound. Such a condition does not cause errors, but may be inefficient.

When the parameter is created (using the OParameterCollection::Add method) you need to specify whether the parameter is used for input, output or both. Use one of the following defines:

OPARAMETER_INVAR	// input variable
OPARAMETER_OUTVAR	\// output variable
OPARAMETER_INOUTVAR	// input and output variable

You can query the status of a parameter, in which case the parameter returns a long that contains bits set to indicate the status. The bits are defined as:

OPARAMETER_STATUS_IN	// this is an input variable
OPARAMETER_STATUS_OUT	// this is an output variable
OPARAMETER_AUTOENABLED	// this parameter is bound automatically
OPARAMETER_ENABLED	// this parameter is ready to be bound

When the parameter is created you also need to specify the server type of the parameter. The server type is one of the following Oracle types:

OTYPE_VARCHAR2

OTYPE_NUMBER

OTYPE_LONG

OTYPE_ROWID

OTYPE_DATE

OTYPE_RAW

OTYPE_LONGRAW

OTYPE_CHAR

OTYPE_MSLABEL

OTYPE_CURSOR

Please consult the Oracle SQL Language Reference Manual for more information about these types. In general you can use OTYPE_VARCHAR2 for strings and OTYPE_NUMBER for numbers.

The OParameter class supports the following methods:

Construction and destruction:

OParameter

~OParameter

operator=

Attributes:

operator== operator!= GetName

GetServerType GetStatus IsOpen

Operations:

AutoEnable Clear Close GetValue MinimumSize operator const char * operator double operator int

operator long operator OBfile operator OBlob operator OClob operator OCollection operator OObject operator ORef SetValue

Error Handling:

ErrorNumber

ErrorReset

GetErrorText

LookupErrorText