SnapShot Property

Applies To

OraDynaset Object

Description

Returns the SnapshotID.

Read and write at run time.

Usage

SnapshotID = OraDynaset.Snapshot

Remarks

The SnapshotID represents the snapshot from which this dynaset was created. It can be thought of as a timestamp. It can be passed into other CreateDynaset method calls to cause them to be created using data from the same point in time as the original dynaset.

The Snapshot property can be set with the value of another Snapshot. That new snapshot is used during the next Refresh operation when the query is reexecuted. The Snapshot property always returns the SnapshotID on which this OraDynaset object was based, not any other SnapshotID set through the snapshot property.

The SnapshotID becomes invalid after a certain amount of time; that amount of time is dependent on the amount of activity and the configuration of the database. When this happens, you get a Snapshot too old error message. For more information about snapshots, see the Oracle Database Concepts.

This SnapshotID represents the point in time when this dynaset was created. Changes to this dynaset (Edit, Delete, and AddNew operations) is not reflected in additional dynasets created using this SnapshotID because they occurred after that point in time.

SnapshotID objects are only meaningful for SELECT statements where the tables referenced are real database tables, as opposed to pseudo tables such as DUAL.

One valuable use of the SnapshotID is to calculate the number of rows in a table without using the RecordCount property which causes every row to be fetched. See "Example: Counting Rows in a Dynaset".

Data Type

Object

Examples

Example: Using the SnapShot Property

This example shows the use of the SnapShot property.

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraDynaset1 As OraDynaset 
Dim OraDynaset2 As OraDynaset 
Dim SnapshotID as SnapshotID 
 
 '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&) 
 
'ALLEN's JOB is initially SALESMAN 
OraDatabase.ExecuteSql("Update EMP set JOB = 'SALESMAN' where ENAME = 'ALLEN'") 
  
 'Create initial OraDynaset Object. 
 Set OraDynaset1 = OraDatabase.CreateDynaset("select empno, ename," & _ 
            "job from emp", 0&) 
MsgBox "OraDynaset1 -- Value of JOB is " & OraDynaset1.Fields("JOB").Value 
 
'Change Allen's JOB 
OraDatabase.ExecuteSql("Update EMP set JOB = 'CLERK' where ENAME = 'ALLEN'") 
 
'This SnapshotID represents the point in time in which OraDynaset1 was created
Set SnapshotID = OraDynaset1.Snapshot 
 
'Create OraDynaset2 from the same point in time as OraDynaset1 
Set OraDynaset2 = OraDatabase.CreateDynaset("select JOB from EMP" & _ 
               "where ENAME = 'ALLEN'", 0&, SnapshotID) 

MsgBox "OraDynaset2 -- Value of JOB from point of time of OraDynaset1 is " & _
                OraDynaset2.Fields("JOB").Value 
 
'We set the snapshot to NULL which will get us current point in time. 
OraDynaset2.Snapshot = Null 
 
'We refresh it and it will get us the data from the current point in time  
OraDynaset2.Refresh 
MsgBox "OraDynaset2 -- Value of JOB from current point of time is " & _
                OraDynaset2.Fields("JOB").Value 
 
'And back again to the old point in time -- 
OraDynaset2.Snapshot = SnapshotID 
OraDynaset2.Refresh 
MsgBox "OraDynaset2 -- Value of JOB from point of time of OraDynaset1 is " & _
              OraDynaset2.Fields("JOB").Value 
 

Example: Counting Rows in a Dynaset

This example counts the number of rows in a dynaset without using the RecordCount property, which fetches every row. Note that the record count this returns cannot take into account any AddNew or Delete operations, making the information meaningful only immediately after the dynaset is created

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraDynaset As OraDynaset 
Dim OraDynCount As OraDynaset 
Dim SnapshotID as SnapshotID 
 
 '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 Dynaset 
Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&) 
Set SnapshotID = OraDynaset.Snapshot 
 
'Use the snapshot for count query to guarantee the same point in time 
Set OraDynCount = OraDatabase.CreateDynaset("select count(*) NUMROWS" & _
         "from emp", 0&, SnapshotID)
MsgBox "Number of rows in the table is " & OraDynCount.Fields("NUMROWS").Value