| Oracle® Objects for OLE Developer's Guide 11g Release 1 (11.1) Part Number B28378-01 |
|
|
View PDF |
| Oracle® Objects for OLE Developer's Guide 11g Release 1 (11.1) Part Number B28378-01 |
|
|
View PDF |
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.
Applies To
Description
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.
Usage
oradata1.AllowMoveLast = [True | False]
Remarks
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.
Datatype
Integer (Boolean)
Applies To
Description
Determines whether the automatic binding of database object parameters will occur. Read/write at design time and run time.
Usage
oradata1.AutoBinding = [ True | False
Remarks
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.
Data Type
Integer (Boolean)
Example
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
Applies To
Description
Determines the background color of an object.
See Also:
ForeColor PropertyApplies To
Description
The username and password to be used when connecting the data control to an Oracle database. Read/write at design time and run time.
Usage
oradata1.Connect = [ username/password
Remarks
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"
Data Type
String
Applies To
Description
Returns the OraDatabase object associated with the data control. Not available at design time and read-only at run time.
Usage
oradatabase = oradata1.Database
Remarks
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.
Data Type
OLE Object (OraDatabase)
See Also:
Applies To
Description
The Oracle SQL*Net specifier used when connecting the data control to an Oracle database. Read/write at design time and run time.
Usage
oradata1.DatabaseName = [ DatabaseName ]
Remarks
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")
Data Type
String
Applies To
Description
Determines whether or not Update and Delete will or will not check for read inconsistencies.
Usage
oradata1.DirtyWrite = [ True | False ]
Data Type
Integer (Boolean)
Remarks
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.
Applies To
Description
Determines the icon to be displayed as the pointer in a drag-and-drop operation.
Applies To
Description
Determines manual or automatic dragging mode for a drag-and-drop operation.
Applies To
Description
Returns the current editing state for the current row. Not available at design time and read-only at run time.
Usage
edit_mode = oradata1.EditMode
Remarks
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.
Data Type
Integer
See Also:
Applies To
Description
Determines whether the control can respond to user-generated events.
See Also:
Visible PropertyApplies To
Description
Determines the font object to be used for text displayed in a data control.
Usage
Oradata1.Font.Bold = True
Applies To
Description
Determines the foreground color used to display text and graphics in an object.
See Also:
BackColor PropertyApplies To
Description
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.
See Also:
Applies To
Description
Determines the distance between the internal left edge of an object and the left edge of its container.
See Also:
Applies To
Description
Determines the type of mouse pointer displayed when the mouse is over a particular part of a form or control at run time.
See Also:
Applies To
Description
Specifies the name used in code to identify a form, control, or data access object. Not available at run time.
Applies To
Description
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.
Usage
oradata1.NoRefetch = [ True | False ]
Data Type
Integer (Boolean)
Applies To
Description
Determines one or more characteristics of the database and all dynasets associated with the data control. Read/write at design time and run time.
Usage
oradata1.Options = database_optionsdatabase_options = oradata1.Options
Data Type
Long Integer
Remarks
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 NULLs 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.
Applies To
Description
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.
Usage
oradata1.OracleMode = [ True | False ]
Data Type
Integer (Boolean)
Remarks
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.
Applies To
Description
Determines whether the dynaset will be used for read-only operations. Read/write at design time and run time.
Usage
oradata1.ReadOnly = [ True | False ]
Data Type
Integer (Boolean)
Remarks
By default, ReadOnly is False which means that an attempt will be made to create an updatable dynaset by selecting ROWIDs from the database. If ReadOnly is set to True, a non-updatable dynaset is created (ROWIDs 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, ROWIDs 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.
See Also:
Applies To
Description
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.
Usage
Set oradynaset = oradata1.RecordsetSet oradata1.Recordset = Oradynaset
Data Type
OLE Object (OraDynaset)
Remarks
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.
Example
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
Applies To
Description
The SQL SELECT statement to be used to create the data control's RecordSet. Read/write at design time and run time.
Usage
oradata1.RecordSource = [ SQL SELECT Statement ]
Data Type
String
Remarks
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.
Example
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
Applies To
Description
The session object associated with the data control. Not available at design time and read-only at run time.
Usage
orasession = oradata1.Session
Data Type
OLE Object (OraSession)
Remarks
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.
Applies To
Description
Determines the distance between the internal top edge of an object and the top edge of its container.
See Also:
Applies To
Description
Determines whether trailing blanks should be removed from character string data retrieved from the database. Read/write at design time and run time.
Usage
oradata1.TrailingBlanks = [ True | False ]
Data Type
Integer (Boolean)
Remarks
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.
See Also:
Applies To
Description
Determines whether an object is visible or hidden.
See Also:
Enabled Property