This chapter describes the Oracle Data Control Properties. For an introduction to Data Control, see "Oracle Data Control".
See Also: For more information, see the Microsoft Visual Basic help and documentation. |
This chapter contains these topics:
The following properties apply to the OraDynaset
object and to the Oracle Data Control.
Determines whether the user can move to the last record using the Data Control's MoveLast
button. Read/write at design time and run time.
oradata1.AllowMoveLast = [True | False]
By default, AllowMoveLast
is True
, in which case the user has no restriction upon record motion, even when moving to the last record may be very time consuming.
When AllowMoveLast
is False
, the Data Control's MoveLast
button is grayed out and disabled. However, once the last record has been encountered (either because the user has navigated to the end of the set, or because code has positioned the record pointer to the last record), the button is enabled. This gives the user visual feedback about whether or not the entire query has been fetched. Setting this property to False
does not prevent you from using the MoveLast
method.
Changing this property has no effect until a Refresh
method is sent to the data control.
Integer
(Boolean)
Determines whether the automatic binding of database object parameters will occur. Read/write at design time and run time.
oradata1.AutoBinding = [ True | False
By default, AutoBinding
is True
, in which case the parameters in the OraParameters
collection are bound to the SQL statement of the RecordSource
property before data control refresh (before the SQL statement is executed). Technically speaking, the parameters are rebound when the recordset is re-created.
Setting Autobinding
to False
takes effect only if the SQL statement of the RecordSource
property needs to be rebound and reexecuted. This is not the case when you simply change a parameter value and refresh the data control or simply refresh the recordset (the SQL statement only needs to be reexecuted). This is the case if you alter the RecordSource
property and change the SQL statement.
Use this property to disable all parameter binding when executing a SQL statement that does not contain any parameters (using CreateDynaset
, Refresh
, or ExecuteSQL
).
Changing this property does not take effect until a Refresh
method is sent to the data control (and the appropriate conditions apply). Changing this property has no effect when a recordset.Refresh
is executed.
Integer
(Boolean)
This example demonstrates the use of AutoBinding
to show how it affects data control and recordset refresh. Copy this code into the definition section of a new form containing the Oracle Data Control named oradata1
, Then, press F5 to run.
Sub Form_Load () 'Set the username and password. oradata1.Connect = "scott/tiger" 'Set the databasename. oradata1.DatabaseName = "ExampleDb" 'Refresh the data control without setting the RecordSource. This has the 'effect of creatingthe underlying database object so that parameters 'can be added. oradata1.Refresh 'Set the RecordSource and use a SQL parameter for job. oradata1.RecordSource = "select * from emp where job = :job" 'Add the job input parameter with initial value MANAGER. oradata1.Database.Parameters.Add "job", "MANAGER", 1 'Add the deptno input parameter with initial value 10. oradata1.Database.Parameters.Add "deptno", 10, 1 'Refresh the data control. oradata1.Refresh MsgBox "Employee #" & oradata1.Recordset.fields("empno") & ", Job=" & _ oradata1.Recordset.fields("job") 'Only employees with job=MANAGER will be contained in the dynaset. 'Turn off Automatic parameter binding. oradata1.AutoBinding = False 'Change the value of the job parameter to SALESMAN. oradata1.Database.Parameters("job").Value = "SALESMAN" 'Refresh ONLY the recordset. oradata1.Recordset.Refresh MsgBox "Employee #" & oradata1.Recordset.fields("empno") & ", Job=" & _ oradata1.Recordset.fields("job") 'The query will still execute even with AutoBinding=False 'because the dynaset has not been re-created. 'Set the RecordSource and use a SQL parameter for deptno. oradata1.RecordSource = "select * from emp where deptno = :deptno" On Error GoTo paramerr 'Attempt to refresh the data control. An error should occur, because ' AutoBind=False, the SQL statement contains a parameter, and the 'SQL statement needs to be bound before execution. oradata1.Refresh Exit Sub paramerr: MsgBox oradata1.Database.Session.LastServerErrText Exit Sub End Sub
The username and password to be used when connecting the data control to an Oracle database. Read/write at design time and run time.
oradata1.Connect = [ username/password
This string is passed to the OpenDatabase
method of the OraSession
object when the control is refreshed. Changing this property does not take effect until a Refresh
method is sent to the data control.
If the data control is refreshed and the Connect
property has not been specified, the refresh will fail.
Examples of valid Connect
properties include:
"scott/tiger" "system/manager"
String
Returns the OraDatabase
object associated with the data control. Not available at design time and read-only at run time.
oradatabase = oradata1.Database
If the data control has not been refreshed, any references to this property results in an Object
variable
not
set
runtime
error.
Changing this property has no effect until a Refresh
method is sent to the data control.
OLE Object (OraDatabase)
The Oracle SQL*Net specifier used when connecting the data control to an Oracle database. Read/write at design time and run time.
oradata1.DatabaseName = [ DatabaseName ]
The Oracle SQL*Net specifier should include the Oracle SQL*Net protocol identifier, Oracle database name, and optional database instance. (SQL*Net aliases can also be used.) This string is passed to the OpenDatabase
method of the OraSession
object when the control is refreshed. Changing this property does not take effect until a Refresh
method is sent to the data control.
If the data control is refreshed and DatabaseName
has not been specified, the refresh fails.
Examples of valid DatabaseName
properties include:
"t:oracle:PROD" "p:Oracle10:demo" "x:orasrv" "mydbalias"(Where mydbalias represents "t:mfg:prod")
String
Determines whether or not Update
and Delete
will
or will not check for read inconsistencies.
oradata1.DirtyWrite = [ True | False ]
Integer
(Boolean)
By default, DirtyWrite
is False
, meaning that read consistency will be maintained for Update
and Delete
operation on underlying recordset/dynaset object. Changing this property has no effect until a Refresh
method is sent to the data control.
Returns the current editing state for the current row. Not available at design time and read-only at run time.
edit_mode = oradata1.EditMode
The possible EditMode
property values are:
Constant | Value | Description |
---|---|---|
ORADATA_EDITNONE |
0 |
No editing in progress |
ORADATA_EDITMODE |
1 |
Editing is in progress on an existing row |
ORADATA_EDITADD |
2 |
A new record is being added and the copy buffer does not currently represent an actual row in the database. |
These values are located in the oraconst.txt
file and are intended to match similar constants in the Visual Basic oraconst.txt
file.
This property is affected only by the Edit
, AddNew
, and Update
methods.
Integer
Determines the font object to be used for text displayed in a data control.
Oradata1.Font.Bold = True
Specifies the number that uniquely identifies a control in a control array. Available at design time only if the control is part of a control array; read-only at run time.
Determines the distance between the internal left edge of an object and the left edge of its container.
Determines the type of mouse pointer displayed when the mouse is over a particular part of a form or control at run time.
Specifies the name used in code to identify a form, control, or data access object. Not available at run time.
By default, NoRefetch
is False
, this means that default data set by Oracle Database will not be refetched to the local cache. If the ORADB_NO_REFETCH
option is True
, by default, the underlying recordset or dynaset will inherit this property.
Changing this property has no effect until a Refresh
method is sent to the data control.
oradata1.NoRefetch = [ True | False ]
Integer
(Boolean)
Determines one or more characteristics of the database and all dynasets associated with the data control. Read/write at design time and run time.
oradata1.Options = database_optionsdatabase_options = oradata1.Options
Long
Integer
This property is a bit flag word used to set the optional modes of the database. If options = 0, the default settings will apply. The following modes are available:
Column Defaulting mode
The default mode is called VB mode. In VB mode, field (column) values not explicitly set are set to NULL
when using AddNew
or Edit
.
Optionally, you can use Oracle mode. Oracle mode indicates that changes made to fields (columns) are immediately reflected in the local mirror by retrieving the changed row from the database, thus allowing Oracle Database to set defaults for the columns and perform required calculations. Column Defaulting mode affects the behavior of the AddNew
and Edit
methods.
Lock Wait mode
The default mode is called Wait mode. In Wait mode, when dynaset rows are about to be modified (using Edit
), the existing row in the database is retrieved using the SQL "SELECT
...
FOR
UPDATE"
statement to lock the row in the database. If the row about to be changed has been locked by another process (or user), the "SELECT
...
FOR
UPDATE"
statement, waits until the row is unlocked before proceeding.
Optionally, you can use NoWait mode. NoWait mode results in an immediate return of an error code, indicating that the row about to be updated is locked.
Lock Wait mode also affects any SQL statements processed using ExecuteSQL
.
No Refetch mode
In this mode NULL
s are not explicitly inserted as in the ORADB_ORAMODE
. In ORADB_NO_REFETCH
mode, performance is boosted, because data is not refetched to the local cache.
Options Property Flag Values
The Options
property flag values are:
Constant | Value | Description |
---|---|---|
ORADB_DEFAULT |
&H0& |
Accepts the default behavior. |
ORADB_ORAMODE |
&H1& |
Lets Oracle Database set default field (column) values. |
ORADB_NOWAIT |
&H2& |
Does not wait on row locks when executing a SQL "SELECT ... FOR UPDATE" statement. |
These values can be found in the oraconst.txt
file. Options may be combined by adding their respective values.
This property is the same as the options passed to the OpenDatabase
method. Just as with OpenDatabase
, these options affect the OraDatabase
object and all associated dynasets created from that database.
Changing this property does not take effect until a Refresh
method is sent to the data control.
Determine whether the changes made to fields (columns) are immediately reflected in the local mirror by retrieving the changed row from the database, thus allowing Oracle to set defaults for the columns and perform required calculations.
oradata1.OracleMode = [ True | False ]
Integer
(Boolean)
This property value is set to True
by default, which means that fields (columns) changes are reflected in the local cache immediately. Changing this property value has no effect until the Refresh
method is invoked. If the ORADB_ORAMODE
mode is used for the database option, the underlying recordset/dynaset inherits this mode.
Determines whether the dynaset will be used for read-only operations. Read/write at design time and run time.
oradata1.ReadOnly = [ True | False ]
Integer
(Boolean)
By default, ReadOnly
is False
which means that an attempt will be made to create an updatable dynaset by selecting ROWID
s from the database. If ReadOnly
is set to True
, a non-updatable dynaset is created (ROWID
s are not selected from the database and cached) and operations will be somewhat faster.
If the SELECT
statement contains a LONG
or LONG
RAW
column, ROWID
s are needed whether the dynaset will be updatable or not.
Changing this property does not take effect until a Refresh
method is sent to the data control.
Returns a dynaset defined by the data control's Connect
, DatabaseName
, and RecordSource
properties. Not available at design time and read and write at run time.
Set oradynaset = oradata1.RecordsetSet oradata1.Recordset = Oradynaset
OLE Object (OraDynaset
)
The properties and methods of this dynaset are the same as those of any other dynaset object. The Recordset
property of the Oracle Data Control (.OCX
) can be set to external dynaset, or the Recordset property of the other data control. After the setting, Oracle Data control Database, session, and options properties now set to the corresponding properties of the external dynaset. Oracle data control shares the advisories of the external dynaset. This is very useful when attaching dynaset returned from the PL/SQL cursor by CreatePlsqlDynaset
Method.
This example demonstrates setting Recordset
property to external dynaset created by CreatePlsqlDynaset
method. This example returns a PL/SQL cursor as a external dynaset for the different values of DEPTNO
parameter. Make sure that corresponding stored procedure (found in EMPCUR.SQL
) is available in the Oracle Database. Copy this code into the definition section of a form containing the Oracle Data Control named oradata1
. Then, press F5.
Sub Form_Load () 'Declare variables as OLE Objects. Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraDynaset As OraDynaset 'Create the OraSession Object. Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraDatabase Object by opening a connection to Oracle. Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&) ' Create the Deptno parameter OraDatabase.Parameters.Add "DEPTNO", 10, ORAPARM_INPUT OraDatabase.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER ' Create OraDynaset based on "EmpCursor" created in stored procedure. Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Employee.GetEmpData (:DEPTNO,:EmpCursor); end;", "EmpCursor", 0&) ' Now attach the Oradynaset to Data control's recordset. set oradata1.recordset = OraDynaset ... ' Do some operation ... ' Now set the deptno value to 20 OraDatabase.Parameters("DEPTNO").Value = 20 'Refresh the sqlstmt Oradata1.recordset.Refresh 'Remove the parameter. OraDatabase.Parameters.Remove ("DEPTNO") End Sub
The SQL SELECT
statement to be used to create the data control's RecordSet
. Read/write at design time and run time.
oradata1.RecordSource = [ SQL SELECT Statement ]
String
The SQL statement must be a SELECT
statement; otherwise an error is returned. Features such as views, synonyms, column aliases, schema references, table joins, nested selects, and remote database references can be used freely; object names are not modified in any way.
Whether or not the resultant dynaset can be updated depends on the Oracle SQL rules of updatability, the access you have been granted, and the ReadOnly
property. In order to be updatable, three conditions must be met:
The SQL statement must refer to a simple column list or to the entire column list (*).
The SQL statement must not set the read-only flag of the options argument.
Oracle must permit ROWID references to the selected rows of the query.
Any SQL statement that does not meet these criteria is processed, but the results are not updatable and the dynaset's Updatable
property returns False
.
Changing this property does not take effect until a Refresh
method is sent to the data control.
You can use SQL bind variables in conjunction with the OraParameters
collection.
If this property is NULL
or empty, then an OraDynaset
object is not created, but OraSession
, OraConnection
, and OraDatabase
objects are created for the data control. This behavior enables access to these objects prior to creation of a dynaset. For example, a NULL
RecordSource
might be used to instantiate the database object to add parameters. The RecordSource
property can then be set at run time, making use of the automatic binding of database parameters.
Changing this property and calling the Refresh
method of the RecordSet
property will create a new dynaset object, but the old dynaset continues to be available for use until all references to it are removed.
This example demonstrates the use of SQL bind variables (parameters) in the RecordSource
property of the data control. To run this demonstration, copy this code into the definition section of a form containing a data control named oradata1
, then, press F5.
Sub Form_Load () 'Set the username and password. oradata1.Connect = "scott/tiger" 'Set the databasename. oradata1.DatabaseName = "ExampleDb" 'Refresh the data control without setting the ' RecordSource. This has the effect of creating ' the underlying database object so that parameters may be added. oradata1.Refresh 'Set the RecordSource and use a SQL parameter. oradata1.RecordSource = "select * from emp where job = :job" 'Add the job input parameter with initial value MANAGER. oradata1.Database.Parameters.Add "job", "MANAGER", 1 'Refresh the data control. 'Only employees with the job MANAGER will be contained in the dynaset. oradata1.Refresh 'Change the value of the job parameter to SALESMAN. oradata1.Database.Parameters("job").Value = "SALESMAN" 'Refresh ONLY the recordset. 'Only employees with the job SALESMAN will be contained in the dynaset. oradata1.Recordset.Refresh End Sub
The session object associated with the data control. Not available at design time and read-only at run time.
orasession = oradata1.Session
OLE Object (OraSession
)
This property is equivalent to referencing oradata1.Database.Session
. If the data control has not been refreshed, any references to this property result in an Object
variable
not
set
runtime error.
Determines the distance between the internal top edge of an object and the top edge of its container.
Determines whether trailing blanks should be removed from character string data retrieved from the database. Read/write at design time and run time.
oradata1.TrailingBlanks = [ True | False ]
Integer
(Boolean)
By default, TrailingBlanks
is False
. This means that trailing blanks will be removed from character string data retrieved from the database.
Changing this property has no effect until a Refresh
method is sent to the data control.