Q:Access Parameterized Query to Oracle?

[totierne]

comp.databases. ms-access,

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.

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:

[My session variables have an API, but basically 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:i nput_name' references 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.Worksp aces(0).Databas es(0)
Set qitem = db.CreateQueryD ef("")
qitem.Connect = "ODBC;DSN=o92;U ID=scott;PWD=ti ger"
qitem.ReturnsRe cords = False
qitem.ODBCTimeo ut = 15
qitem.SQL = "CALL
omwb_emulation. utilities.setva rchar2('QUERYEX AMPLE:input_nam e','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...b;en-us;281998

looks like adodb.recordset s 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(Cance l As Integer)
Dim cn As ADODB.Connectio n
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;U ID=northwindalt ;PWD=oracle"
'Create a new ADO Connection object
Set cn = New ADODB.Connectio n

With cn
.Provider = "MSDASQL"
.Properties("Da ta 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
.ActiveConnecti on = cn
.CommandText = "omwb_emulation .utilities.setv archar2"
.CommandType = adCmdStoredProc
End With

Set param1 = setstring.Creat eParameter(, adVarChar, adParamInput,
100, "QUERYEXAMPLE:i nput_name")
setstring.Param eters.Append param1

Set param2 = setstring.Creat eParameter(, adVarChar, adParamInput,
100, "smith")
setstring.Param eters.Append param2

setstring.Execu te

Set setstring = Nothing

Set rs = New ADODB.Recordset
With rs
Set .ActiveConnecti on = cn
.Source = "SELECT * FROM queryexample"
.LockType = adLockOptimisti c
.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(Can cel As Integer)
'Close the ADO connection we opened
Dim cn As ADODB.Connectio n
Set cn = Me.Recordset.Ac tiveConnection
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_MAC HINE\Software\M icrosoft\Jet\4. 0\Engines\ODBC folder
contains initialization settings for the Microsoft Jet database engine.

ConnectionTimeo ut 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.getva rchar2('QUERYEX AMPLE:input_nam e') FROM
dual)

which can be called from Oracle sqlplus:

SQL> begin
2
omwb_emulation. utilities.setva rchar2('QUERYEX AMPLE:input_nam e','smith');
3 end;
4 /

SQL> select * from queryexample;

TELEPHONE
--------------------------------------------------
123