Example: SnapShot Property

Example 1

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 2

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 it is only meaningful 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