Linking to Oracle DBs

I'd take a look in the MSKB; Microsoft Knowledge Base (http://support.microsoft.com)

  It's loaded with articles and code samples for doing all kinds of things with ODBC sources.

JimD.

Some good resources there, JDettman, but most pertain to ACC2.0.  I'm actually looking for for Access 2000, using ADO/RDO.

Hi jkpcs.
I found Help file describes how to set up, configure, and use the ODBC Driver for Oracle. See Office2000\System folder on your CD (MSORCL32.HLP) - if you can't find it, let me know and I'll send it by e-mail (45K). It seems this file can help you.
Regards,
Dedushka

Hi jkpcs,

I have used this to open a record set from an Oracle database and an ODBC connection.
Let me know if it helps.  

    Dim strSQL as string
    Dim qdfOracle as Querydef
    Dim rstOracle as Recordset
      
    strSQL = _
        "Select " & _
            "Field1, " & _
            "Field2, " & _
            "Field3, " & _
            "Field4 " & _
        "From " & _
            "YourOracleTable " & _
        "Where " & _
            "Field1 = 'AValue'"
       
    Set qdfOracle = CurrentDb.CreateQueryDef("")
    With qdfOracle
        .Connect = "ODBC;DSN=YourDSN;DBQ=YourDBQ;UID=YourUserID;PWD=YourPassword"
        .SQL = strSQL
        Set rstOracle = .OpenRecordset()
        .Close
    End With

Hi jkpcs,

I have used this to open a record set from an Oracle database and an ODBC connection.
Let me know if it helps.  

    Dim strSQL as string
    Dim qdfOracle as Querydef
    Dim rstOracle as Recordset
      
    strSQL = _
        "Select " & _
            "Field1, " & _
            "Field2, " & _
            "Field3, " & _
            "Field4 " & _
        "From " & _
            "YourOracleTable " & _
        "Where " & _
            "Field1 = 'AValue'"
       
    Set qdfOracle = CurrentDb.CreateQueryDef("")
    With qdfOracle
        .Connect = "ODBC;DSN=YourDSN;DBQ=YourDBQ;UID=YourUserID;PWD=YourPassword"
        .SQL = strSQL
        Set rstOracle = .OpenRecordset()
        .Close
    End With

Heres some info from the MSDN Subscription.

Connection String Format and Attributes
Instead of using a dialog box, some applications might require a connection string that specifies data source connection information. The connection string is made up of a number of attributes that specify how a driver connects to a data source. An attribute identifies a specific piece of information that the driver needs to know before it can make the appropriate data source connection. Each driver might have a different set of attributes, but the connection string format is always the same. A connection string has the following format:

"DSN=data-source-name[;SERVER=value] [;PWD=value] [;UID=value] [;<Attribute>=<value>]"

Note   The Microsoft ODBC Driver for Oracle supports the connection string format of the first version of the driver, which used CONNECTSTRING= instead of SERVER=.

You must specify the data source name if you do not specify the UID, PWD, SERVER (or CONNECTSTRING), and DRIVER attributes. However, all other attributes are optional. If you do not specify an attribute, that attribute defaults to the one specified in the relevant DSN tab of the ODBC Data Source Administrator dialog box. The attribute value might be case-sensitive.

The attributes for the connection string are as follows:

Attribute Description Default value
DSN The data source name listed in the Drivers tab of the ODBC Data Source Administrator dialog box. "" 
PWD The password for the Oracle Server that you want to access. This driver supports limitations that Oracle places on passwords. "" 
SERVER The connect string for the Oracle Server that you want to access. "" 
UID The Oracle Server user name. Depending on your system, this attribute might not be optional—that is, certain databases and tables might require this attribute for security purposes.
Use  "/" to use Oracle’s operating system authentication.
 ""
BUFFERSIZE The optimal buffer size used when fetching columns.
The driver optimizes fetching so that one fetch from the Oracle Server returns enough rows to fill a buffer of this size.  Larger values tend to increase performance if you fetch a lot of data.
 65535
SYNONYMCOLUMNS When this value is true (1), an SQLColumn( ) API call returns column information. Otherwise, SQLColumn( ) returns only columns for tables and views. The ODBC Driver for Oracle provides faster access when this value is not set. 1
REMARKS When this value is true (1), the driver returns Remarks columns for the SQLColumns result set. The ODBC Driver for Oracle provides faster access when this value is not set. 0
StdDayOfWeek Enforces the ODBC standard for the DAYOFWEEK scalar. By default this is turned on, but users who need the localized version can change the behavior to use whatever Oracle returns. 1
GuessTheColDef Specifies whether or not the driver should return a non-zero value for the cbColDef argument of SQLDescribeCol. Applies only to columns where there is no Oracle-defined scale, such as computed numeric columns and columns defined as NUMBER without a precision or scale. A SQLDescribeCol call returns 130 for the precision when Oracle does not provide that information. 0


For example, a connection string that connects to the Employees data source using the mickey.world Oracle Server and the Oracle User cindy would be:

"DSN=Employees;UID=cindy;PWD=secret;SERVER=mickey.world"

A connection string that connects to the Payroll data source using operating system authentication and the moola Oracle Server would be:

"DSN=Payroll;UID=/;PWD=;SERVER=moola"

This is an example of pretty much all the details you will experience with Oracle.  From MSDN Universal, the infamous "Fitch & Mather".

Fitch & Mather Stocks: Data Access Layer for Oracle 8
Scott Hernandez
Vertigo Software, Inc.

July 1999

Summary: Describes the implementation of the FMStocks data access layer (DAL) for Microsoft® SQL Server™ for a new data provider, Oracle 8. (13 printed pages)

Contents
Introduction
Implementation
Using Prepared Statements
Using Recordsets as Part of Your Interface
Changes to Database.bas and ADO
Using RunSQL* Functions
Source Code for the Oracle 8 Schema
About the Author
For More Information

Introduction
This article explores the changes needed for writing a compatible data access layer (DAL) for a new data provider. In this case, we are porting the DAL from SQL Server to an Oracle 8 database on Microsoft Windows NT® Server and Solaris 2.6 Server. The article does not explore the general function of a DAL; if you wish read more about the conceptual design of the DAL please read Scott Stanfield's "Fitch & Mather Stocks: Data Access Layer."

We were able to write the DAL to work with Oracle in less than one day, without changing one line of code anywhere else in the project. Thus, our application looks and functions exactly the same with both DALs and data providers. It took us more than one day to get the database installed and the schema and data moved over to Oracle from Microsoft SQL Server.

Note several differences for the DAL on Oracle:

It was never tested under load, nor tuned for optimal performance under any test load.


We have chosen to use prepared statements instead of stored procedures when returning rows of data from the data provider. (This should actually make this DAL even more portable, from one data provider to another, then the original.)


We changed the Database.bas file to support prepared statements through the RunSQL* functions.


There are actual SQL statements in our middle-tier DAL code.
Although we have not finely tuned the Oracle DAL, you will see that we did take steps so that we could tune it later. You will find that there are many choices when porting code from one data provider to another. Below are the notes for best practices when taking on these types of projects.

Implementation
The first step in creating a new DAL was to copy the original Microsoft Visual Basic® project. This gave us an identical configuration and left the project binary compatible with the SQL DAL. Now that we had a project to work from, we started to port each class one at a time.

In less than one day, we were able to write the Oracle DAL using prepared statements and some existing stored procedures. Before we started writing the DAL, we had our database schema and data moved over to Oracle. It took us a few weeks of work to get the Oracle database schema and plumbing done before we could start coding.

Other Ways to Write a DAL
There is another configuration that we never tested. It is possible to keep both DALs compiled and installed at one time. If we wanted to use the Oracle Server for read-only operations and the SQL Server to updates, or vise versa, it would be completely possible. We could take one of three routes to do this.

We could name the DLL two different names with two different ProgIDs and ClassIDs. Then we could change our Business Logic Layer (BLL) component to use each DAL for read-only or update procedures.


We could combine the DALs.


We could write a new DAL that just used the two DALs depending on the method called.
Enabling MTS Transaction Support for the Oracle DAL
In our first attempt to run any component under a Microsoft Transaction Server (MTS) transaction context, we received a "Catastrophic Failure" error when we opened an ADO connection. We found an article in the Microsoft Knowledge Base, "Using Oracle Databases with Microsoft Transaction Server" (Q193893), which explained in details the steps necessary to enable MTS Transactions with Oracle databases.

The default MTS installation was already set up and ready on our server, except for an update of the Oracle Client to 8.0.5. Section 5 of "Setting up Oracle Support" describes how to "Update the Oracle Client Software Registry Keys" to allow MTS to use the Oracle 8.0.5 client. After we changed two registry keys, the Oracle DAL worked in MTS with transactions turned on.

In the source code we have left transaction support turned off. If you need to enable transactions please refer to the Knowledge Base article for help.

Using Prepared Statements
One of the first technical changes we made to the Oracle DAL was to use prepared statements rather than stored procedures to return rows of data from the data provider.

In design and implementation the RunSQL* functions closely resemble the RunSP* versions. However, these RunSQL* functions cause the DAL to be written very differently. In the code samples that follow, you will see that you must send a SQL statement, or prepared SQL statement, instead of a stored procedure name, to the RunSQL* functions. This means that you will actually be embedding SQL statements in your Visual Basic code.

    StrSQL = "Select AccountID, Firstname, LastName from accounts " 
    StrSQL = strSQL & " where email = ? and password = ?"
    Set rs = RunSQLReturnRS(strSQL, _
                            mp("", adVarChar, 25, email), _
                            mp("", adVarChar, 25, password))

In the SQL DAL we abstracted the table names and queries with stored procedures. For ease and speed of development, in this DAL we have chosen to embed SQL code in the DAL. This choice makes the DAL more dependent on the database schema. If we make any changes to the database schema, we will need to recompile and fix any incongruities between the DAL SQL statements and the database. This makes our DAL much more dependant on naming and structural changes to the database.

Even though we have made the DAL more tightly coupled to the data provider, we are still able to localize any database changes to the DAL without affecting the BLL or the presentation layer.

Using Oracle stored procedures to Return Recordsets
We decided not to use Oracle stored procedures to return result sets. You may think that is a big performance issue, and it might be. Using and writing Oracle stored procedures that return result sets is not for the light of heart.

In Microsoft SQL Server you can simply write a stored procedure that returns a SQL SELECT statement, but in Oracle you can't. Following are the equivalent stored procedures from Microsoft SQL Server and Oracle that do a stock ticker search by company name.

First, the SQL stored procedure:

CREATE PROCEDURE Ticker_ListByCompany
(@Company varchar(30))
AS
    select rtrim(ticker) as ticker, rtrim(company) as company,
           rtrim(exchange) as exchange
    from stocks
    where company like @Company + '%'

In Oracle, you must define a package that contains your procedures if you want to return result sets. Then your stored procedure returns a set of special parameters that look like a result set (ADO recordset).

PACKAGE ticker_pkg as

TYPE status_table_type   is table of fundamentals.status%type
        INDEX BY BINARY_INTEGER;
TYPE ticker_table_type   is table of fundamentals.ticker%type
        INDEX BY BINARY_INTEGER;
TYPE company_table_type  is table of activelist.company%type
        INDEX BY BINARY_INTEGER;
TYPE exchange_table_type is table of activelist.exchange%type
        INDEX BY BINARY_INTEGER;
TYPE vc20_table_type     is table of varchar2(20)
        INDEX BY BINARY_INTEGER;
TYPE vc9_table_type      is table of varchar2(9)
        INDEX BY BINARY_INTEGER;

PROCEDURE ticker_listbycompany
  (v_company_in       IN   activelist.company%type,
   t_ticker           OUT  ticker_table_type,
   t_company          OUT  company_table_type,
   t_exchange         OUT  exchange_table_type)
IS
v_company_arg      varchar2(61);
v_ticker           activelist.ticker%type;
v_company          activelist.company%type;
v_exchange         activelist.exchange%type;
v_indx             binary_integer :=0;

CURSOR ticker_cursor IS
  select ticker, company, exchange
   from activelist
   where company like v_company_arg
   order by company;

BEGIN
  v_company_arg := RTRIM(v_company_in)||'%';
  open ticker_cursor;
  LOOP
     FETCH ticker_cursor
       INTO v_ticker, v_company, v_exchange;
     EXIT WHEN ticker_cursor%NOTFOUND;
  v_indx := v_indx + 1;
  t_ticker(v_indx)           := v_ticker;
  t_company(v_indx)          := v_company;
  t_exchange(v_indx)         := v_exchange;
  END LOOP;

END ticker_listbycompany;

The complexity of writing Oracle stored procedures that return result sets kept us from writing too many. We hired an Oracle consultant to do that work for us. We ended up not needing or using most of them.

As a first pass, we decided to use prepared statements instead of taking the time to use Oracle stored procedures in packages. One other important note: this DAL is also more generic and should work for almost any data provider that supports prepared statements.

Note   In Oracle, result sets are the conceptual equivalent to ADO recordsets.

Using Recordsets as Part of Your Interface
In this DAL Interface you will notice that many times we return recordsets to the BLL, and then up to the presentation layer. However, since the end user of these recordsets could be three levels away from where the actual recordset is created, it is very important that these recordsets contain what is expected. In most cases you probably use ADO recordset to encompass complex sets of data. By choosing to use ADO recordsets as part of your interface, you may also have to jump some hoops when you make changes to the internal working of the method.

The Account.Summary method returns a recordset. It really only needs to return three numbers. In earlier versions of the SQL DAL, we were locked into an interface that returned rows of data in a recordset; thus, we had to return these three numbers as a single row in a recordset. In this version of the DAL, I wanted to change the method signature from

Public Function Summary(ByVal AccountID As Long) As ADODB.Recordset
to this:

Public Function Summary(ByVal AccountID As Long, _
    ByRef MarketValue as Double, _
    ByRef TotalInvestment as Double, _
    ByRef CashBalance as Double)

If I made this type of change, it would have caused the new DAL to be incompatible with previous versions of the DAL. Instead of doing so, I decided to create an ADO recordset from scratch using the new creatable recordset object. In the following method it was necessary to write custom code that did not exist in our Database.bas file.

In this method we create a custom Command object that takes one input parameter and has three output parameters. Since we have to maintain binary compatible with the existing DAL, we must take those three numbers and put them in a recordset to return. This code may look complicated but if you look closely it almost exactly matches the internals of the RunSPReturnInteger helper function.

Public Function Summary(ByVal AccountID As Long) As ADODB.Recordset
    'returns a recordset with a single row
    'that represents the account's MarketValue, TotalInvestment,
    'CashBalance
    On Error GoTo errorHandler
   
    'run a special Command object
    Dim cmd As ADODB.Command
    Set cmd = CtxCreateObject("ADODB.Command")
   
    'Run the procedure
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = "Account_Summary"
    cmd.CommandType = adCmdStoredProc
   
    With cmd
        .Parameters.Append .CreateParameter("v_accountid", adNumeric, _
            adParamInput, 8, AccountID)
        .Parameters.Append .CreateParameter("v_marketvalue", adNumeric, _
            adParamOutput, 8)
        .Parameters.Append .CreateParameter("v_totalinvestment", _
            adNumeric, adParamOutput, 8)
        .Parameters.Append .CreateParameter("v_cashbalance", adNumeric, _
            adParamOutput, 8)
    End With
   
    cmd.Execute , , ADODB.adExecuteNoRecords
    cmd.ActiveConnection = Nothing
   
    'Create a recordset to return
    Dim rsnew As New ADODB.Recordset
    rsnew.CursorLocation = adUseClient
   
    'Add Some Fields
    rsnew.Fields.Append "MarketValue", adCurrency
    rsnew.Fields.Append "TotalInvestment", adCurrency
    rsnew.Fields.Append "CashBalance", adCurrency
   
    rsnew.Open , , adOpenStatic, adLockBatchOptimistic
   
    rsnew.AddNew Array("MarketValue", "TotalInvestment","CashBalance"), _
          Array( _
                NullsToZero(cmd.Parameters("v_marketvalue").Value), _
                NullsToZero(cmd.Parameters("v_totalinvestment").Value), _
                NullsToZero(cmd.Parameters("v_cashbalance").Value) _
               )
   
    'we don't need the cmd object anymore.-get rid of it
    Set cmd = Nothing

    rsnew.UpdateBatch adAffectAll
    rsnew.MoveFirst
    Set Summary = rsnew
   
    CtxSetComplete
    Exit Function
       
errorHandler:
    CtxRaiseError g_modName, "Summary"
End Function

Document your Recordset Data Members
In our DAL interfaces, we document what each method takes as arguments and what will be passed back at end of the method call. Things become a little more thorny when you use recordsets as an input or output parameter to your method. Since a recordset is a dynamic way to store or return many types of data it is hard to document what it represents internally. Let's look at an example of where we pass back a recordset from our DAL. It is very important that your method document the fields and order that are returned in the recordset.

Public Function ListByTicker(ByVal Ticker As String) As ADODB.Recordset
   'Returns Recordset[ticker,company,exchange]
   On Error GoTo errorHandler
   Dim strSQL As String

   'Add % to end of partial Stock Symbol
   Ticker = UCase(Ticker) & "%"
   
   strSQL = "Select ticker, company,exchange from activelist where" & _
            "ticker"
   strSQL = strSQL & " like ? order by ticker"
   Set ListByTicker = _
             RunSQLReturnRS(strSQL, mp("@Ticker", adVarChar, 12, Ticker))
   CtxSetComplete
   Exit Function
       
errorHandler:
    CtxRaiseError g_modName, "ListByTicker"
End Function

Referencing ADO Fields
In ADO there are two ways to reference a field in a recordset. You can use RS(0), short hand for RS.Fields.Item(0).Value, which will reference the first Field in the recordset. You can also reference that Field by using RS("fieldname").

Referencing a field by name
Here is an example of how to reference a field by name. This example was taken from the Portfolio page.

set obj = Server.CreateObject("FMStocks_Bus.Account")
set rs = obj.ListPositions(g_AccountID)
....
tempMarketValue = CDbl(rs("SharesOwned").value) * CDbl(rs("LastPrice").value)

Referencing a field by ordinal
This same code would like something like this if the fields were referenced by ordinal.

set obj = Server.CreateObject("FMStocks_Bus.Account")
set rs = obj.ListPositions(g_AccountID)
....
tempMarketValue = CDbl(rs(2).value) * CDbl(rs(3).value)

In the strictest sense, this is the fastest and best way to reference a field, but you are trading off readability for speed. It is a trade-off that we did not make.

Changes to Database.bas and ADO
From looking at the final version of Database.bas, you might not be able to tell what was added for the Oracle DAL. There are many functions there that were added to support prepared statements during the Oracle DAL port. The following is an outline of the changes and additions we made during the port to the new Oracle DAL.

By now you should be familiar with the RunSP* functions used in the SQL DAL. (If not, please read "Fitch & Mather Stocks: Data Access Layer"; it goes into detail about the inner workings and architecture of the RunSP* functions.) They are used to return different types of data from stored procedures. The RunSQL* functions differ only in one place with the RunSP* functions. You have seen how we use ADO Command objects to run statements against the data provider with input and output parameters. If you look carefully at the following line, you will see the parameter that we set to indicate what type of statement the Command object will execute.

    cmd.CommandType = adCmdStoredProc

By simply changing this property from adCmdStoredProc to adCmdText, we will be able to transform the RunSP* functions into RunSQL* functions so we can execute prepared statements with parameters.

    cmd.CommandType = adCmdText

In addition to RunSQLReturnRS, the other prepared statement helper functions and their return values are listed in Table 1. As you can see, RunSPReturnInteger and RunSQLReturnRS are the most popular functions.

Table 1. Run* Function Return Values

Function Return Value Times
Used
RunSQLReturnRS A disconnected, read-only, ADO recordset. 9
RunSPReturnInteger An output parameter of type adInteger passed back as a variant: assumes the stored procedure's last parameter is a number output parameter(retval). 5
RunSP Nothing. Useful for delete or update oriented stored procedures. 1
RunSQLReturnRS_RW A connected, updateable ADO recordset. (RW means read/write) 1
RunSP Nothing. Useful for delete or update oriented SQL statements. 0


RunSQL
RunSQL does exactly what you would expect. It runs a SQL statement and raises an error if something goes wrong. The only difference between this function and RunSP is the CommandType property of the ADO Command object. Even though we don't use this function, it is worth including to round out the functionality of Database.bas functions.

Function RunSQL(ByVal strSP As String, ParamArray params() As Variant)
    On Error GoTo errorHandler
   
    ' Create the ADO objects
    Dim cmd As ADODB.Command
    Set cmd = CtxCreateObject("ADODB.Command")
   
    ' Init the ADO objects & the stored proc parameters
    cmd.ActiveConnection = GetConnectionString()
    cmd.CommandText = strSP
    cmd.CommandType = adCmdText

    collectParams cmd, params
   
    ' Execute the query without returning a recordset
    cmd.Execute , , ADODB.adExecuteNoRecords
   
    ' Cleanup and return nothing
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing
    Exit Function

errorHandler:
    Set cmd = Nothing
    RaiseError g_modName, "RunSQL(" & strSP & ", ...)"
End Function

This function can also be written using only the ADO Connection object. If you were to do it without the Command object, you would be unable to use prepared statements and use input parameters.

Function RunSQL(ByVal strSP As String)
    On Error GoTo errorHandler
   
    ' Create the ADO objects
    Dim conn As ADODB.Connection
    Set conn = CtxCreateObject("ADODB.Connection")
   
    ' Init the ADO objects & the stored proc parameters
    conn.Open GetConnectionString()
    conn.Execute strSP, 0
   
    ' Cleanup and return nothing
    Set conn = Nothing
    Exit Function

errorHandler:
    Set conn = Nothing
    RaiseError g_modName, "RunSQL(" & strSP & ", ...)"
End Function

RunSQLReturnRS
RunSQLReturnRS runs a SQL statement and returns a read-only recordset. Much like RunSQL, the only difference between this function and the RunSP version is the CommandType property of the ADO Command object.

Little Helper Functions
You may see the "make parameter" function in database.bas and wonder why we took the time to write it. We could have just used the Array function directly. In the earlier versions of database.bas we did. But one of the most annoying things about calling our Run* functions was having the go back to the documents to look up the Enumeration values for the data types. So we created a little helper function that took a function parameter of type ADODB.

Using RunSQL* Functions
With the move to prepared statements we are able to do much more dynamic queries from Visual Basic instead of delving back into SQL to write stored procedures. Let's take a look at how we are able use prepared statements to do from Visual Basic with Oracle what we were doing directly from stored procedures with SQL Server.

Using prepared statements instead of using stored procedures also has its downsides. In the Account.VerifyUser method we have to retrieve a recordset and validate the return data in Visual Basic to decide if the user could log on. In the SQL DAL this was simplified in a single stored procedure.

Public Function VerifyUser(ByVal email As String, _
                           ByVal password As String, _
                           ByRef AccountID As Variant, _
                           ByRef FullName As Variant) As Boolean
    Dim rs As Recordset,strSQL as String
    On Error GoTo errorHandler

   StrSQL = "Select AccountID, Firstname, LastName from accounts " 
   StrSQL = strSQL & " where email = ? and password = ?"
   Set rs = RunSQLReturnRS(strSQL, _
                           mp("", adVarChar, 25, email), _
                           mp("", adVarChar, 25, password))
    If Not rs.EOF Then
        VerifyUser = True
        If rs.RecordCount > 1 Then VerifyUser = False
        AccountID = rs("AccountID")
        FullName = rs("FirstName") & " " & rs("LastName")
    Else
        VerifyUser = False
    End If
   
    Set rs = Nothing
    CtxSetComplete
   
    Exit Function

errorHandler:
    CtxRaiseError g_modName, "VerifyUser"
End Function

Source Code for the Oracle 8 Schema
You can find instructions on how to create the Oracle 8 database and the support files at our live FMStocks site at www.fmstocks.com/.

We have posted instructions and files on the live FMStocks site. Please get the latest version at www.fmstocks.com/.

About the Author
Scott Hernandez is a Software Developer at Vertigo Software, Inc. Vertigo Software is a San Francisco Bay area-based consulting firm that specializes in the design and development of Windows® DNA applications and components. He can be reached at skot@vertigosoftware.com or on the Web at www.vertigosoftware.com/. You might also catch him on some of the Microsoft newsgroups responding as an MVP for Visual InterDev®, Visual Basic, and Data Access technologies.

For More Information
The complete documentation for FMStocks can be found at http://msdn.microsoft.com/vstudio/downloads/solutions.asp.


For a live version of the site, latest installation tech notes, bug fixes, and errata, visit www.fmstocks.com/.

Mickey, Carey, great stuff.  Please look for seperate questions to collect points.  (100 each)  thanks!!

Thanks IanB