CreateCustomDynaset Method

Applies To

Description

Creates a dynaset using custom cache and fetch parameters

Usage

Set oradynaset = oradatabase.CreateCustomDynaset(sql_statement, options, slicesize, perblock, blocks, FetchLimit, FetchSize, SnapShotID)

Arguments

The arguments for the method are:

Arguments	Description
`sql_statement`	Any valid Oracle SQL `SELECT` statement.
`slicesize`	Cache slice size.
`perblock`	Cache slices for each block.
`blocks`	Cache maximum number of blocks.
`FetchLimit`	Fetch array size.
`FetchSize`	Fetch array buffer size.
`options`	A bit flag indicating the status of any optional states of the dynaset. You can combine one or more options by adding their respective values. Specifying the constant `ORADYN_DEFAULT` or the value `&H0&` gives the following defaults for the dynaset: Behave like Visual Basic Mode for a database: Field values not explicitly set are set to `Null`, overriding database column defaults. Perform automatic binding of database parameters. Remove trailing blanks from character string data retrieved from the database. Create an updatable dynaset. Cache data on the client. Force a `MoveFirst` operation when the dynaset is created. Maintain read-consistency.
`SnapShotID` [optional]	The ID of a Snapshot obtained from the `SnapShot` property of an `OraDynaset`.

Constants

The following table lists constants and values for the options flag.

Constant	Value	Description
`ORADYN_DEFAULT`	`&H0&`	Accept the default behavior.
`ORADYN_NO_AUTOBIND`	`&H1&`	Do not perform automatic binding of database parameters.
`ORADYN_NO_BLANKSTRIP`	`&H2&`	Do not remove trailing blanks from character string data retrieved from the database.
`ORADYN_READONLY`	`&H4&`	Force dynaset to be read-only.
`ORADYN_NOCACHE`	`&H8&`	Do not create a local dynaset data cache. Without the local cache, previous rows within a dynaset are unavailable; however, increased performance results during retrieval of data from the database (move operations) and from the rows (field operations). Use this option in applications that make single passes through the rows of a dynaset for increased performance and decreased resource usage.
`ORADYN_ORAMODE`	`&H10&`	Same as Oracle Mode for a database except it affects only the dynaset being created. If database was created in Oracle Mode, the dynaset inherits the property from it (for compatibility).
`ORADYN_NO_REFETCH`	`&H20&`	Behaves same as `ORADB_NO_REFETCH` mode for a database except this mode affects only the dynaset being created. If the database was created in `ORADB_NO_REFETCH` mode, the dynaset inherits the property for compatibility.
`ORADYN_N_MOVEFIRST`	`&H40&`	Does not force a `MoveFirst` when the dynaset is created. `BOF` and `EOF` are both true.
`ORADYN_DIRTY_WRITE`	`&H80&`	`Update` and `Delete` methods do not check for read consistency.

These values can be found in the oraconst.txt file located in:

ORACLE_BASE\\ORACLE_HOME\rdbms\oo4o

Remarks

The SQL statement must be a SELECT statement or an error is returned. Features such as simple views and synonyms can be used freely. You can also use schema references, column aliases, table joins, nested select statements, and remote database references, but in each case you end up with a read-only dynaset.

If you use a complex expression or SQL function on a column, such as "sal + 100" or "abs(sal)" , you get an updatable dynaset, but the column associated with the complex expression is not updatable.

Object names generally are not modifed, but in certain cases, they can be changed. For example, if you use a column alias, you must use the alias to refer to the field by name. If you use spaces in a complex expression, you must refer to the column without the spaces, because the database removes spaces. Note that you can always refer to a field by number, that is, by its ordinal position in the SELECT statement.

Executing the SQL SELECT statement generates a commit operation to the database by default. To avoid this, use the BeginTrans method on the session object before using the CreateDynaset method.

The updatability of the resultant dynaset depends on the Oracle SQL rules of updatability, on the access you have been granted, and on the options flag.

Updatability Conditions

For the dynaset to be updatable, three conditions must be met:

A SQL statement must refer to a simple column list or to the entire column list (*).
The 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 Updatable property of the dynaset returns False.

This method automatically moves to the first row of the created dynaset.

You can use SQL bind variables in conjunction with the OraParameters collection.

Examples

This example demonstrates the CreateCustomDynaset method. Copy and paste this code into the definition section of a form, then press F5.

Sub Form_Load ()
 
 'Declare variables
 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 OraDynaset Object using sliceSize as 256,perblock size as 16, no. of 
 'blocks as 20, fetchLimit as 20,FetchSize as 4096
  
 Set OraDynaset = OraDatabase.CreateCustomDynaset("select empno, " & _ 
               "ename from emp", 0&,256,16,20,20,4096)
 
 'Display the first record.
 MsgBox "Employee " & OraDynaset.Fields("empno").value & ", #" & _ 
                OraDynaset.Fields("ename").value
 
End Sub