Oracle Parameterized views and Access

From: <totierne_at_hotmail.com>
Date: 9 Jun 2005 11:41:09 -0700
Message-ID: <1118342469.510190.107000@g49g2000cwa.googlegroups.com>

I want to know how to use Oracle views with session variables in Access.
The parameterised views in access, are migrated to views with per session
variables.

I tried comp.databases.ms-access just thought I would give this group a go,
in case there is some access front end to oracle backend expertise...

The open questions:
How to display a resultset
How to handle Oracle exceptions

Closed questions:
How to call a stored procedure from DAO
How to call a stored procedure from ado and then display a view in a form;
Using a cache to get the same session.

[Sample database setup]

(I have an Oracle backgroud)

The open questions:
How to display a resultset

I am not sure how to display a recordset that is created in VBA.

How to handle Oracle exceptions

Oracle handles errors by 'exceptions',
I am not sure how to handle them in Access.

Closed questions:

What I have been able to do is:

[The session variables have an API, but bassically one needs to call a stored procedure/function to set/get them]

How to call a stored procedure from DAO

Set session variables over DAO/ODBC:
where 'QUERYEXAMPLE:input_name' refernces a variable and 'smith' is the value to set.

call to set string via dao
Function SetVal() As Boolean
Dim db As Database
Set db = DBEngine.Workspaces(0).Databases(0) Set qitem = db.CreateQueryDef("")

  qitem.Connect = "ODBC;DSN=o92;UID=scott;PWD=tiger"
  qitem.ReturnsRecords = False
  qitem.ODBCTimeout = 15
  qitem.SQL = "CALL

omwb_emulation.utilities.setvarchar2('QUERYEXAMPLE:input_name','smith')"   qitem.Execute
  Set qitem = Nothing
  SetVal = True
End Function

How to call a stored procedure from ado and then display a view in a form;
using ado/oledb

How to bind Microsoft Access forms to ADO recordsets: http://support.microsoft.com/default.aspx?scid=kb;en-us;281998

looks like adodb.recordsets bound to forms are read only. The following is based on the Microsoft information with nformation fille in about using odbc to reach the oracle database with the views. The same session is used to set the parameter and then select from the view to populate the form.

Option Compare Database

Private Sub Form_Open(Cancel As Integer) Dim cn As ADODB.Connection

   Dim rs As ADODB.Recordset
   Dim strConnection As String
   Dim setstring As ADODB.Command
   Dim param1 As ADODB.Parameter
   Dim param2 As ADODB.Parameter

strConnection = "ODBC;DSN=o92;UID=northwindalt;PWD=oracle" 'Create a new ADO Connection object
Set cn = New ADODB.Connection

With cn

.Provider = "MSDASQL"
.Properties("Data Source").Value = strConnection
.Open

End With

'Create an instance of the ADO Recordset class, and 'set its properties

Set setstring = New ADODB.Command
With setstring

.ActiveConnection = cn
.CommandText = "omwb_emulation.utilities.setvarchar2"
.CommandType = adCmdStoredProc

End With

Set param1 = setstring.CreateParameter(, adVarChar, adParamInput, 100, "QUERYEXAMPLE:input_name")

setstring.Parameters.Append param1

Set param2 = setstring.CreateParameter(, adVarChar, adParamInput, 100, "smith")

setstring.Parameters.Append param2

setstring.Execute

Set setstring = Nothing

Set rs = New ADODB.Recordset
With rs

      Set .ActiveConnection = cn


      .Source = "SELECT * FROM queryexample"

      .LockType = adLockOptimistic

      .CursorType = adOpenKeyset

      .CursorLocation = adUseClient

      .Open

End With

   'Set the form's Recordset property to the ADO recordset    Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)

   'Close the ADO connection we opened
   Dim cn As ADODB.Connection
   Set cn = Me.Recordset.ActiveConnection    cn.Close
   Set cn = Nothing
End Sub

Using a cache to get the same session.

The thing is there is a timeout

The Microsoft Jet database engine uses the ODBC entries as follows.

Initialization Settings for Microsoft Jet-connected ODBC Databases The \HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC folder contains initialization settings for the Microsoft Jet database engine.

ConnectionTimeout The number of seconds a cached connection can remain idle
before timing out. The default is 600 (values are of type REG_DWORD).

Sample database setup

example:
table example1.
ID, name, telephone
1,jones,321
2,smith,123

query
query queryexample:
Parameters [input name] text;
select telephone from example1 where name=[input name];

becomes in oracle
CREATE OR REPLACE FORCE VIEW queryexample AS SELECT telephone
FROM example1

WHERE name = (SELECT
omwb_emulation.utilities.getvarchar2('QUERYEXAMPLE:input_name') FROM dual)

which can be called from Oracle sqlplus:

SQL> begin
  2
omwb_emulation.utilities.setvarchar2('QUERYEXAMPLE:input_name','smith');   3 end;
  4 /

SQL> select * from queryexample;

TELEPHONE

123 Received on Thu Jun 09 2005 - 13:41:09 CDT