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)
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
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:
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.
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.
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.
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.
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.
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.
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.Recordsetto 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
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
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").
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)
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.
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 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 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.
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.
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
You can find instructions on how to create the Oracle 8 database and the support files at our live FMStocks site at http://www.fmstocks.com/.
We have posted instructions and files on the live FMStocks site. Please get the latest version at http://www.fmstocks.com/.
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 http://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.