Re: Oracle Objects for OLE: NOCACHE-Parameter

From: Mr Angus Beare <ucfaabe_at_ucl.ac.uk>
Date: 1995/07/10
Message-ID: <1995Jul10.145626.30386_at_ucl.ac.uk>#1/1

u7l11ai_at_sun1.lrz-muenchen.de (Mark Sandor) writes:

>Hi out there,  

>i've got a question concering Oracle Objects OLE for VB.
>I'm using VB 3.0 and always get GPF when I try to do the 
>following:
>...(other lines)
>dim dy as object
>set dy = oradb.dbcreateDynaset("SELECT * FROM table...", ORADYN_NOCACHE)
>                                                       ^ ^ ^ ^ ^ ^ ^ ^ ^ ^
 
>If I leave out the _NOCACHE-switch and insert 0 instead (default), all works
>fine. But as documented in the Online-Help, the NOCACHE allows you to

>open the Dynaset for a fast-access read with no possibilities to access >previous rows. That's exactly the thing i want to do.  

>Any known work-arounds?  

>Thanx in advance
>cu..Mark  

>u7l11ai_at_sun.lrz-muenchen.de

I'm glad someone else is having similar problems. I've been struggling with a similar situation for some time.

I don't know the answer to your problem. But I suspect it may be that you have to set the value of ORADYN_NOCACHE before using it??

My situation may be of use to you, if you're looping OO4OLE dynasets.

I needed to loop
a dynaset in order to fill an ordinary array with lookup codes. I found that by setting an object variable and using this (avoiding excessive object references) I was able to loop the dynaset very much more quickly. The object variable is referenced instead of the dynaset properties.

Here's my example:-

Sub load_diatcode_array ()
' this proc loads an array of codes
' from an oradynaset
' and uses them to fill a TrueGrid column

Dim Orasession as object
Dim OraDatabse as object
Dim Oradynaset as object
Dim flds() As object 'NB object variable to avoid object refs Dim i, fldcount As Integer

'error trapper
On Error GoTo getout

' set up Oracle session
Set OraSession = CreateObject("OracleInProcServer.XOraSession") Set OraDatabase = OraSession.OpenDatabase("Amphora", "archive/o187978nh", 0&)

' Set text for the SQL statement.

SQLStmt = "select * from dcode"

' Get total number of DCODES in list so we can REDIM arrays
Set oradynaset = OraDatabase.DbCreateDynaset("select count(*) from dcode", &H8& + &H4&)
' you may find that using CONSTANTS in the above form gets rid of those GPF's

' 'nelements' is a GLOBAL integer variable
nelements = oradynaset.fields(0).value
' set it to number of codes

' get the list of codes itself

Set oradynaset = OraDatabase.DbCreateDynaset("select * from dcode", &H8& + &H4&)

' get number of fields in dynaset

fldcount = oradynaset.fields.count

' redim variables

ReDim dcodelist(nelements) 'GLOBAL variant array for CODES ReDim dnamelist(nelements) 'GLOBAL variant array for diatom names ReDim flds(0 To fldcount - 1)

' NOW FOR THE NAUGHTY BIT
' set object variable to avoid objects references with
' oradynaset.fields

Set flds(0) = oradynaset.fields(0)
Set flds(1) = oradynaset.fields(1)

' run through dynaset
' and fill arrays with taxon codes and names
' The purpose of doing this is two use the arrays in
' an UNBOUND FETCH event (TrueGrid) and look up codes
' that are retrieved in batches from the database
i = 0
Do Until oradynaset.EOF

   dcodelist(i) = flds(0).value 'the oradynaset.fields("code").value is avoided    dnamelist(i) = flds(1).value 'the oradynaset.fields("name").value is avoided    i = i + 1
   oradynaset.DbMoveNext
   DoEvents 'let Windows get on with other processing Loop

' Having done this it may be better to use the object
' variables themselves in the UNBOUND FETCH and do away with
' the arrays
' I will have to see whether object vars and arrays offer
' equal performance etc.
' at the moment I'm just happy it works

Exit Sub

' the error trapper

getout:

msg = "Error loading code lookup list ( " & Err & ") " & Error$ MsgBox msg
End

End Sub

---

I recommend that if you're looping a dynaset of more that a few records you take a serious look at this code. My app now takes about 1/8th of the time to start. I've used the NOCACHE option and it seems to work OK. I have had a lot of GP's trying to write the above though.

If you need any further help let me know.

Gus Received on Mon Jul 10 1995 - 00:00:00 CEST