Stored Procedures in General
Stored procedures are programs consisting of SQL statements as well as logic to control the flow of processing. They can contain both input and output parameters and can return values. A stored procedure is stored in the database and can be executed by calling it directly from a client program (such as a VB program). Compared to the use of "in-line" SQL statements that have been used in the other sample applications, the use of stored procedures offer a couple of advantages:
· With stored procedures, there are less SQL statements to be transmitted across the network – only the name of the stored procedure and any parameters it may require need to be sent over the wire.
· Stored procedures are parsed and optimized when they are created. They are compiled in the database when created and remain a part of the database. This is in contrast to the use of in-line SQL statements in your program, which must be parsed and compiled by the DBMS every time the program is run.
Stored procedures can be used to execute routine data functions such as selecting, inserting, updating, and deleting data; they can also be used to perform any number of database functions such as backing up the database and transaction logging.
In this article we will look at some very simple stored procedures using examples in MS-Access, SQL Server, and Oracle. For each, we will look at a sample VB application that uses ADO to execute these stored procedures. (Note: You will need to have access to SQL Server and Oracle in order to run the respective examples using those database systems.)
All three versions of the sample application presented here perform identically to the "Customer Table Maintenance" application presented in the preceding topic.
"Stored Procedures" in MS-Access
MS-Access does not support stored procedures per se, however, saved Query objects can be used. The ADO syntax used to work with Access Query objects is the same syntax used to work with stored procedures in the high-end databases. Compared to high-end database stored procedures, Access queries are quite limited. As mentioned above, stored procedures in high-end databases like SQL Server or Oracle can contain multiple SQL statements, logic statements to control processing flow, return values, etc. – however, with Access queries, you can have only ONE SQL statement – period. The only other language element that can be present in an Access query is a set of parameters - queries that have this are referred to as "parameterized queries".
For each of the stored procedure sample applications, we will use the equivalent of the "Customer" database that was used in the previous topic on ADO. As you may recall, the Customer database consists of one table, called Customer, defined as follows in MS-Access:
|
Column Name |
Data Type |
|
CustID |
Number (Long Integer) |
|
LastName |
Text (50) |
|
FirstName |
Text (50) |
|
Address |
Text (50) |
|
City |
Text (25) |
|
State |
Text (2) |
|
Zip |
Text (5) |
|
PhoneNumber |
Text (10) |
In the copy of the Cust.mdb MS-Access database used for this sample application, the following Query objects were created:
SelectCustomer
SELECT FirstName, LastName, Address, City, State, Zip, PhoneNumber, CustID
FROM Customer
ORDER BY LastName, FirstName;
InsertCustomer
PARAMETERS pCustID Long, pFName Text(50), pLName Text(50), pAddr Text(50),
pCity Text(25), pState Text(2), pZip Text(5), pPhone Text(10);
INSERT INTO Customer( CustID, FirstName, LastName, Address, City, State, Zip, PhoneNumber )
VALUES (pCustID, pFName, pLName, pAddr, pCity, pState, pZip, pPhone);
UpdateCustomer
PARAMETERS pCustID Long, pFName Text(50), pLName Text(50), pAddr Text(50),
pCity Text(25), pState Text(2), pZip Text(5), pPhone Text(10);
UPDATE Customer SET FirstName = pFName, LastName = pLName, Address = pAddr,
City = pCity, State = pState, Zip = pZip, PhoneNumber = pPhone
WHERE CustID=pCustID;
DeleteCustomer
PARAMETERS pCustID Long;
DELETE *
FROM Customer
WHERE CustID=pCustID;
Note that the InsertCustomer, UpdateCustomer, and DeleteCustomer queries have a PARAMETERS statement that precedes the actual SQL statement. The PARAMETERS statement begins with the keyword PARAMETERS followed by one or more pairs of variable name and datatype. Multiple pairs of variable name / datatype are comma separated. The PARAMETERS statement ends with a semicolon. The parameter variables are then used as values in the body of the SQL statement that follows.
Highlights of the code, as it relates to ADO syntax to handle stored procedures as well as how it compares to the application presented in the preceding topic (where "in-line" SQL was used), are presented below.
· As is in the preceding sample app, three ADO object variables (representing and ADO Connection, Command, and Recordset, respectively) are declared at the form level:
Private mobjConn As ADODB.Connection
Private mobjCmd As ADODB.Command
Private mobjRst As ADODB.Recordset
· The "ConnectToDB" Sub is nearly identical to that of the preceding sample application. The Connection object variable is instantiated, its ConnectionString property is set up to specify a DSN-less connection, and the connection is then opened. The Command object variable is then instatiated, and its ActiveConnection property is set to reference the connection that was just opened. The only difference between this Sub and that of the preceding sample application is that the Command object's CommandType is not set here – this is because in the preceding sample application, the CommandType was always a textual string, so we could set CommandType to adCmdText just once and "forget about it". In this application, however, we will use both adCmdText and adCmdStoredProc, so we will set the CommandType property before each use of the Command object throughout the application.
'-----------------------------------------------------------------------------
Private Sub ConnectToDB()
'-----------------------------------------------------------------------------
Set mobjConn = New ADODB.Connection
mobjConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" _
& GetAppPath _
& "Cust.mdb"
mobjConn.Open
Set mobjCmd = New ADODB.Command
Set mobjCmd.ActiveConnection = mobjConn
End Sub
· In the "LoadCustomerListView" Sub, the contents of the Customer table is loaded into the listview. In contrast to the preceding application (which built an in-line SQL statement using a variable which was then assigned to the CommandText property of the Command object), this application first sets the CommandType property of the Command object to adCmdStoredProc, then sets the CommandText property to "SelectCustomer", the name of the stored procedure to execute. Since the SelectCustomer procedure contains a SELECT statement, the procedure returns a recordset to the caller – so the recordset returned by the Execute method of the Command object is assigned to the Recordset object variable mobjRst. Note that prior to using the Command object variable mobjCmd, the programmer-defined procedure "ClearCommandParameters" is called. The purpose of this is described a little later below.
'-----------------------------------------------------------------------------
Private Sub LoadCustomerListView()
'-----------------------------------------------------------------------------
Dim objCurrLI As ListItem
Dim strZip As String
Dim strPhone As String
ClearCommandParameters
mobjCmd.CommandType = adCmdStoredProc
mobjCmd.CommandText = "SelectCustomer"
Set mobjRst = mobjCmd.Execute
lvwCustomer.ListItems.Clear
With mobjRst
Do Until .EOF
strPhone = !PhoneNumber & ""
If Len(strPhone) > 0 Then
strPhone = "(" & Left$(strPhone, 3) & ") " _
& Mid$(strPhone, 4, 3) & "-" _
& Right$(strPhone, 4)
End If
Set objCurrLI = lvwCustomer.ListItems.Add(, , !FirstName & "", , "Custs")
objCurrLI.SubItems(mlngCUST_LAST_IDX) = !LastName & ""
objCurrLI.SubItems(mlngCUST_ADDR_IDX) = !Address & ""
objCurrLI.SubItems(mlngCUST_CITY_IDX) = !City & ""
objCurrLI.SubItems(mlngCUST_ST_IDX) = !State & ""
objCurrLI.SubItems(mlngCUST_ZIP_IDX) = !Zip & ""
objCurrLI.SubItems(mlngCUST_PHONE_IDX) = strPhone
objCurrLI.SubItems(mlngCUST_ID_IDX) = CStr(!CustID)
.MoveNext
Loop
End With
With lvwCustomer
If .ListItems.Count > 0 Then
Set .SelectedItem = .ListItems(1)
lvwCustomer_ItemClick .SelectedItem
End If
End With
Set objCurrLI = Nothing
Set mobjRst = Nothing
End Sub
· The "ClearCommandParameters" Sub is called prior to each use of the Command object. This routine clears the Parameters collection of the Command object. The Parameters collection is used to specify the parameters that are to be passed to the stored procedure that will be called. (It was not necessary to use the Parameters collection to call the SelectCustomer procedure shown above because the SelectCustomer procedure does not have parameters.) Anyway, since we have declared the mobjCmd object at the form level, this same object is reused over and over again, so we must clear any existing Parameters collection from it prior to each use.
'------------------------------------------------------------------------
Private Sub ClearCommandParameters()
'------------------------------------------------------------------------
Dim lngX As Long
For lngX = (mobjCmd.Parameters.Count - 1) To 0 Step -1
mobjCmd.Parameters.Delete lngX
Next
End Sub
· In the "cmdSave_Click" event procedure, the code that checks to see whether an add or update has been initiated (among other things) sets the string variable strSPName to "InsertCustomer" or "UpdateCustomer" accordingly. After the "If" statement, the "ClearCommandParameters" Sub is called to clear any existing parameters in the Parameters collection, the Command object's CommandType is set to adCmdStoredProc, the Parameters collection is built (explained in more detail below), the CommandText property is set to strSPName (which we set above to hold the name of the stored procedure to call), then the Execute method is called to run the procedure. The process can be coded this way because although the InsertCustomer and UpdateCustomer procedures do different things, they take the exact same set of parameters (this is true only for the Access example presented in this article).
To build the Parameters collection of the Command object, we use two methods on top of each other. The CreateParameter method of the Command object specifies the details of the parameter to be passed to the stored procedure. The resulting parameter object that is created must then be added to the Parameters collection via the Append method of the Parameters collection of the Command object.
The syntax of the CreateParameter method is as follows:
Set parameter = command.CreateParameter(Name, Type, Direction, Size, Value)
where parameter is an ADO Parameter object, command is an ADO Command object, and the arguments are described in the table below:
|
Argument |
Description |
|
Name |
An optional string representing the name of the Parameter object. If given, this name is the "ADO name" that is used to represent the Parameter object in the client-side (e.g. VB) program – it can be – be need not be – the same name as the actual parameter in the server-side stored procedure. |
|
Type |
An optional Long value specifying the data type of the Parameter object. This value is usually specified by an ADO constant. There are well over 30 possible values for the data type. The examples presented in this topic use two of these: adInteger (a four-byte integer equivalent to a VB or Access Long datatype) and adVarChar (a variable-length character field equivalent to a VB String or Access Text datatype). Other possible values include adDate (date/time datatype), adSingle (single-precision floating point datatype), and adDouble (double-precision floating point datatype). |
|
Direction |
An optional Long value specifying the type of Parameter object. This value is usually specified by an ADO constant. The possible values are: · adParamInput – indicates an input parameter (Note: This is only direction supported by Access parameterized queries). · adParamOuput – indicates an output parameter · adParamInputOutput – indicates a two-way parameter · adParamReturnValue – indicates a return value |
|
Size |
An optional Long value specifying the maximum length for the parameter value in characters or bytes. Required for text (string) datatypes. |
|
Value |
An optional variant value specifying the value for the Parameter object (specified for adParamInput and adParamInputOutput parameters). |
The ADO Parameter object that is created by the CreateParameter method is not automatically appended to the Parameters collection of the Command object, where it ultimately needs to be. This allows additional properties of the Parameter object to be set, if needed (in the sample applications presented here, we do not need to set additional properties). So to append the newly created Parameter object to the Parameters collection of the Command object, the following syntax is used:
command.Parameters.Append parameter
where parameter is an ADO Parameter object, command is an ADO Command object
In the sample application, we are taking a "shortcut" by using CreateParameter and Parameters.Append in one statement. For example, we are using statements like:
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pFName", adVarChar, adParamInput, 50, txtFirst.Text)
The same result could have been accomplished in separate steps, as follows:
' a separate ADO parameter object variable would have to be declared
Dim objParm As ADODB.Parameter
' a pair of statements like the following would be used for each parameter
' to be passed to the stored procedure
Set objParm = mobjCmd.CreateParameter("pFName", adVarChar, adParamInput, 50, txtFirst.Text)
mobjCmd.Parameters.Append objParm
Note that the same logic that dealt with the record ID in the previous topic's sample application is still in place here. For an add, the new record ID is obtained by calling the programmer-defined function GetNextCustID, which is described a little later below. For an update, the record ID of the currently selected item in the listview is used in the UPDATE statement to update the record. In either case, the contents of the listview is updated approriately to reflect the insert or update.
'-----------------------------------------------------------------------------
Private Sub cmdSave_Click()
'-----------------------------------------------------------------------------
Dim strPhone As String
Dim objNewListItem As ListItem
Dim lngIDField As Long
Dim strSPName As String
If Not ValidateFormFields Then Exit Sub
strPhone = txtArea.Text & txtPrfx.Text & txtLine.Text
If mstrMaintMode = "ADD" Then
lngIDField = GetNextCustID()
strSPName = "InsertCustomer"
Set objNewListItem = lvwCustomer.ListItems.Add(, , txtFirst.Text, , "Custs")
PopulateListItem objNewListItem
With objNewListItem
.SubItems(mlngCUST_ID_IDX) = CStr(lngIDField)
.EnsureVisible
End With
Set lvwCustomer.SelectedItem = objNewListItem
Set objNewListItem = Nothing
Else
lngIDField = CLng(lvwCustomer.SelectedItem.SubItems(mlngCUST_ID_IDX))
strSPName = "UpdateCustomer"
lvwCustomer.SelectedItem.Text = txtFirst.Text
PopulateListItem lvwCustomer.SelectedItem
End If
ClearCommandParameters
mobjCmd.CommandType = adCmdStoredProc
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pCustID", adInteger, adParamInput, , lngIDField)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pFName", adVarChar, adParamInput, 50, txtFirst.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pLName", adVarChar, adParamInput, 50, txtLast.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pAddr", adVarChar, adParamInput, 50, txtAddr.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pCity", adVarChar, adParamInput, 25, txtCity.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pState", adVarChar, adParamInput, 2, txtState.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pZip", adVarChar, adParamInput, 5, txtZip.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pPhone", adVarChar, adParamInput, 10, strPhone)
mobjCmd.CommandText = strSPName
mobjCmd.Execute
SetFormState True
mblnUpdateInProgress = False
End Sub
· When we need to add a new record, a new, unique record ID must generated. In this particular sample application, the record ID is defined as a Long Integer in the database. The GetNextCustID function shown below uses the SQL MAX function to find the highest existing value for the CustID field, adds one to it, and returns that value to the caller. Note that in this application, the "ClearCommandParameters" sub is called, then the CommandType property of the Command object is set to adCmdText. The CommandText property is then set to the appropriate SQL string. This is the only place in the program where in-line SQL is used instead of a stored procedure.
It should be noted that if the CustID field was defined as an Access AutoNumber field, the logic of this application would have to be modified accordingly. Basically, we want to be able to know what the value of the new record ID is and use it after the record has been added to the table. For an Autonumber field, we would have to use the AddNew method of the Recordset and then assign the record ID field to a variable for later use. In that we wanted to demonstrate the use of SQL INSERT statements in this and the preceding sample applications, it was decided to not use an Autonumber field for the record ID.
In the SQL Server and Oracle examples presented later on in this article, the "autonumber" concept IS applied to the record ID. With the additional capabilities of the stored procedures in those database systems, it is possible to retrieve the value of the record ID for a newly inserted record for subsequent processing.
'------------------------------------------------------------------------
Private Function GetNextCustID() As Long
'------------------------------------------------------------------------
ClearCommandParameters
mobjCmd.CommandType = adCmdText
mobjCmd.CommandText = "SELECT MAX(CustID) AS MaxID FROM Customer"
Set mobjRst = mobjCmd.Execute
If mobjRst.EOF Then
GetNextCustID = 1
ElseIf IsNull(mobjRst!MaxID) Then
GetNextCustID = 1
Else
GetNextCustID = mobjRst!MaxID + 1
End If
Set mobjRst = Nothing
End Function
· In the "cmdDelete_Click" event procedure, after it has been confirmed that the user truly wants to delete the record, the set of statements to accomplish this via the "DeleteCustomer" stored procedure is executed. As is done prior to each use of the Command object, the "ClearCommandParameters" Sub is called to clear the Parameters collection of the Command object. The CommandType property of the Command object is then set to adCmdStoredProc. One parameter (the record ID of the customer record to be deleted) is created and appended to the Parameters collection. The name of the stored procedure to be called, "DeleteCustomer", is assigned to the Command object's CommandText property. The Execute method of the Command object is then invoked to run the procedure to delete the record from the Customer table. Logic is then executed to update the listview to remove the deleted record.
'-----------------------------------------------------------------------------
Private Sub cmdDelete_Click()
'-----------------------------------------------------------------------------
Dim strFirstName As String
Dim strLastName As String
Dim lngCustID As Long
Dim lngNewSelIndex As Long
If lvwCustomer.SelectedItem Is Nothing Then
MsgBox "No Customer selected to delete.", _
vbExclamation, _
"Delete"
Exit Sub
End If
With lvwCustomer.SelectedItem
strFirstName = .Text
strLastName = .SubItems(mlngCUST_LAST_IDX)
lngCustID = CLng(.SubItems(mlngCUST_ID_IDX))
End With
If MsgBox("Are you sure that you want to delete Customer '" _
& strFirstName & " " & strLastName & "'?", _
vbYesNo + vbQuestion, _
"Confirm Delete") = vbNo Then
Exit Sub
End If
ClearCommandParameters
mobjCmd.CommandType = adCmdStoredProc
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pCustID", adInteger, adParamInput, , lngCustID)
mobjCmd.CommandText = "DeleteCustomer"
mobjCmd.Execute
With lvwCustomer
If .SelectedItem.Index = .ListItems.Count Then
lngNewSelIndex = .ListItems.Count - 1
Else
lngNewSelIndex = .SelectedItem.Index
End If
.ListItems.Remove .SelectedItem.Index
If .ListItems.Count > 0 Then
Set .SelectedItem = .ListItems(lngNewSelIndex)
lvwCustomer_ItemClick .SelectedItem
Else
ClearCurrRecControls
End If
End With
End Sub
· When the program ends, the DisconnectFromDB sub is called to clean up the database objects. The Command object variable mobjCmd is set to Nothing. The ADO Connection is then closed by issuing the Close method on the Connection object variable mobjConn, and then that object is set to Nothing as well. Note: It was not necessary to "clean up" the Recordset object variable mobjRst, because it was set to Nothing at the end of each Sub in which it was used.
'-----------------------------------------------------------------------------
Private Sub DisconnectFromDB()
'-----------------------------------------------------------------------------
Set mobjCmd = Nothing
mobjConn.Close
Set mobjConn = Nothing
End Sub
Download the project files for the Access version of the sample application here.
SQL Server Stored Procedures
Note: In order to run the next sample application, you will need to have access to SQL Server. If you do not have access to SQL Server, possible options are to find a trial version of SQL Server that Microsoft offers from time to time (check Microsoft's website; you may also find a trial version on a CD that comes with a book on SQL Server). Another alternative is to use the MSDE (Microsoft Data Engine). MSDE is a scaled-down version of SQL Server that Microsoft offers for free as a download from their website. MSDE does NOT come with the SQL Server front end tools (Enterprise Manager, Query Analyzer, etc.) – although you may find third-party products that fill that need.
For this sample application, SQL Server 2000 was used. A database called CustomerTest was created, and in that database, the Customer table was created using Enterprise Manager. Following is the equivalent CREATE TABLE statement that could have been used to create the Customer table:
CREATE TABLE Customer(CustID int IDENTITY(1, 1) NOT NULL,
LastName varchar(50),
FirstName varchar(50),
Address varchar(50),
City varchar(25),
State varchar(2),
Zip varchar(5),
PhoneNumber varchar(10))
Note that the CustID field was defined as an int datatype. In SQL Server, the int datatype is a four-byte integer (equivalent to a Long in Access). Note also that the IDENTITY attribute was assigned to the CustID field. This allows for the automatic population of this field with a unqiue value whenever a Customer record is inserted into the table. The (1, 1) after IDENTITY instructs SQL Server to start the seed value with 1 and increment by 1. The remaining fields are defined as varchar, which enables them to store variable length text data up to a maximum length specified by the number in parentheses.
In SQL Server, stored procedures are coded in a language called Transact-SQL (or T-SQL for short). In the SQL Server user interface, stored procedures are entered via the Enterprise Manager or the Query Analyzer; stored procedures may also be entered into a SQL Server database via batch scripts. The general syntax structure of a SQL stored procedure (simplified) is:
CREATE PROCEDURE procedurename[(@parameter datatype [OUTPUT] [, ...])] AS
statement 1;
. . .
statement n;
In the syntax above, procedurename represents the name of the stored procedure, which may be up 128 characters in length. This is followed by an optional parameter list. If present, the parameters are comma-delimited and enclosed in parentheses. Each parameter consists of a name beginning with an "at" sign (@), followed by its datatype, optionally followed by the keyword OUTPUT (only if it is in fact an output parameter – otherwise, the parameter is assumed to be input). Following the parameter list (if present) is the keyword AS. That completes the stored procedure "header". The body of the stored procedure consists of one or more statements (which may be a mix of SQL queries and T-SQL logic statements); each statement ends with a semicolon (;).
The following four stored procedures were created for this sample application:
SelectCustomer. This is a very basic procedure, consisting of one SQL SELECT statement. When called from the VB program with ADO, a recordset object will be returned.
CREATE PROCEDURE SelectCustomer AS
SELECT FirstName
, LastName
, Address
, City
, State
, Zip
, PhoneNumber
, CustID
FROM Customer
ORDER BY LastName, FirstName;
InsertCustomer. This procedure has an argument list consisting of the values for the data that will be inserted into a new row of the Customer table. Recall that the CustID is defined with the IDENTITY attribute, and as such, must not be specified in the INSERT statement itself – SQL Server will populate that column automatically. However, we want to know what value was inserted for the CustID. The SQL Server built-in function @@IDENTITY returns the last inserted Identity value. The last statement in the procedure assigns the value returned by @@IDENTITY to the @pCustID parameter, which is defined as an OUTPUT parameter in the parameter list. In our VB program, we will be able to retrieve this value from the corresponding ADO Parameter object. (Note: In T-SQL, a form of the SELECT statement is used for assignment statements.)
CREATE PROCEDURE InsertCustomer( @pCustID int OUTPUT
, @pFName varchar(50)
, @pLName varchar(50)
, @pAddr varchar(50)
, @pCity varchar(25)
, @pState varchar(2)
, @pZip varchar(5)
, @pPhone varchar(10)
)
AS
INSERT INTO Customer( FirstName
, LastName
, Address
, City
, State
, Zip
, PhoneNumber )
VALUES ( @pFName
, @pLName
, @pAddr
, @pCity
, @pState
, @pZip
, @pPhone
);
SELECT @pCustID = @@IDENTITY;
UpdateCustomer. This procedure has an argument list consisting of the values for the row of data (based on the CustID) that will be updated in the Customer table. The body of the procedure consists of a single UPDATE statement.
CREATE PROCEDURE UpdateCustomer( @pCustID int
, @pFName varchar(50)
, @pLName varchar(50)
, @pAddr varchar(50)
, @pCity varchar(25)
, @pState varchar(2)
, @pZip varchar(5)
, @pPhone varchar(10)
)
AS
UPDATE Customer
SET FirstName = @pFName,
LastName = @pLName,
Address = @pAddr,
City = @pCity,
State = @pState,
Zip = @pZip,
PhoneNumber = @pPhone
WHERE CustID = @pCustID;
DeleteCustomer. This procedure takes in one parameter – the CustID of the Customer row to be deleted. The body of the procedure consists of a single DELETE statement.
CREATE PROCEDURE DeleteCustomer(@pCustID int)
AS
DELETE FROM Customer
WHERE CustID = @pCustID;
Highlights of the SQL Server version of the sample application, as it relates to ADO syntax to handle stored procedures are presented below.
· As is in the preceding sample app, three ADO object variables (representing and ADO Connection, Command, and Recordset, respectively) are declared at the form level:
Private mobjConn As ADODB.Connection
Private mobjCmd As ADODB.Command
Private mobjRst As ADODB.Recordset
· The "ConnectToDB" Sub is nearly identical to that of the preceding sample application. The Connection object variable is instantiated, its ConnectionString property is set up to specify a DSN-less connection, and the connection is then opened. The ConnectionString specifies the required arguments needed for the SQL Server installation used in the sample application. The required arguments and/or values for your SQL Server installation will be different. The Command object variable is then instatiated, and its ActiveConnection property is set to reference the connection that was just opened.
'-----------------------------------------------------------------------------
Private Sub ConnectToDB()
'-----------------------------------------------------------------------------
Set mobjConn = New ADODB.Connection
mobjConn.ConnectionString = "Driver=SQL Server;" _
& "Server=BRUCE-DESKTOP;" _
& "Database=CustomerTest"
mobjConn.Open
Set mobjCmd = New ADODB.Command
Set mobjCmd.ActiveConnection = mobjConn
End Sub
The other database routines in this SQL Server version of the application are identical to that of the Access version, with the exception of the “cmdSave_Click” event procedure as described below:
· In the "cmdSave_Click" event procedure, prior to the code that checks to see whether an add or update has been initiated, the "ClearCommandParameters" Sub is called to clear any existing parameters in the Parameters collection and the Command object's CommandType is set to adCmdStoredProc. Within the “If” structure that checks to see whether an add or update has been initiated, the code on “both sides” builds the Parameters collection, sets the CommandText property to “InsertCustomer” or “UpdateCustomer” accordingly, and calls the Execute method is called to run the appropriate procedure.
The set of Parameters built on both sides is the same, except for the “pCustID” parameter (for the Customer ID). On the “ADD” side, where we are getting ready for an Insert, the direction argument of the CreateParameter method must be specified as adParamOutput because that parameter was defined as OUTPUT in the stored procedure. Note that after the stored procedure is executed, the output parameter is used to populate the Customer ID column of the newly added ListItem in the ListView, corresponding to the newly added record:
With objNewListItem
.SubItems(mlngCUST_ID_IDX) = mobjCmd.Parameters("pCustID")
.EnsureVisible
End With
Because of the use of the Identity attribute on the Customer ID field in the Customer table and the use of the output parameter, the programmer-defined function “GetNextCustID” used in the Access version of the application is not needed.
'-----------------------------------------------------------------------------
Private Sub cmdSave_Click()
'-----------------------------------------------------------------------------
Dim strPhone As String
Dim objNewListItem As ListItem
Dim lngIDField As Long
If Not ValidateFormFields Then Exit Sub
strPhone = txtArea.Text & txtPrfx.Text & txtLine.Text
ClearCommandParameters
mobjCmd.CommandType = adCmdStoredProc
If mstrMaintMode = "ADD" Then
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pCustID", adInteger, adParamOutput)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pFName", adVarChar, adParamInput, 50, txtFirst.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pLName", adVarChar, adParamInput, 50, txtLast.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pAddr", adVarChar, adParamInput, 50, txtAddr.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pCity", adVarChar, adParamInput, 25, txtCity.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pState", adVarChar, adParamInput, 2, txtState.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pZip", adVarChar, adParamInput, 5, txtZip.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pPhone", adVarChar, adParamInput, 10, strPhone)
mobjCmd.CommandText = "InsertCustomer"
mobjCmd.Execute
Set objNewListItem = lvwCustomer.ListItems.Add(, , txtFirst.Text, , "Custs")
PopulateListItem objNewListItem
With objNewListItem
.SubItems(mlngCUST_ID_IDX) = mobjCmd.Parameters("pCustID")
.EnsureVisible
End With
Set lvwCustomer.SelectedItem = objNewListItem
Set objNewListItem = Nothing
Else
lngIDField = CLng(lvwCustomer.SelectedItem.SubItems(mlngCUST_ID_IDX))
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pCustID", adInteger, adParamInput, , lngIDField)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pFName", adVarChar, adParamInput, 50, txtFirst.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pLName", adVarChar, adParamInput, 50, txtLast.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pAddr", adVarChar, adParamInput, 50, txtAddr.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pCity", adVarChar, adParamInput, 25, txtCity.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pState", adVarChar, adParamInput, 2, txtState.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pZip", adVarChar, adParamInput, 5, txtZip.Text)
mobjCmd.Parameters.Append mobjCmd.CreateParameter("pPhone", adVarChar, adParamInput, 10, strPhone)
mobjCmd.CommandText = "UpdateCustomer"
mobjCmd.Execute
lvwCustomer.SelectedItem.Text = txtFirst.Text
PopulateListItem lvwCustomer.SelectedItem
End If
SetFormState True
mblnUpdateInProgress = False
End Sub
Download the project files for the SQL Server version of the sample application here.
Oracle Stored Procedures
Note: In order to run the next sample application, you will need to have access to Oracle. If you do not already have access to Oracle, you may be able to download a version for personal use from Oracle's website. Be advised that the download is quite large and may not be feasible without a high-speed connection. The installation process is lengthy as well. In any event, a visit to their website may be worthwhile to see what is available.
Oracle is one of the oldest and most robust relational database products. Unlike SQL Server, which runs only on Microsoft operating systems, Oracle is cross-platform and runs on UNIX and other systems as well as Windows. It is not as easy to use as SQL Server, but what it lacks in ease of use, it makes up for in horsepower; it is well-suited to high-volume transaction processing. As far as a front-end interface is concerned, Oracle has traditionally offered SQL Plus, which is essentially a command-line interface, although in recent years their GUI-based interfaces have improved. Many folks use third-party interface products such as Quest SQL Navigator, Toad, or PL/SQL Developer from Allround Automations.
For this sample application, Personal Oracle 9i was used. The Customer table was created in the default "SCOTT" schema within the default database. The CREATE TABLE statement is as follows:
CREATE TABLE CUSTOMER(CUSTID NUMBER NOT NULL,
LASTNAME VARCHAR2(50),
FIRSTNAME VARCHAR2(50),
ADDRESS VARCHAR2(50),
CITY VARCHAR2(25),
STATE VARCHAR2(2),
ZIP VARCHAR2(5),
PHONENUMBER VARCHAR2(10))
The CUSTID field was defined a NUMBER datatype, which can hold any numeric value. The remaining fields are defined as VARCHAR2, which enables them to store variable length text data up to a maximum length specified by the number in parentheses. Although not seen in the CREATE TABLE statement above, a primary key constraint was placed on the CUSTID field to ensure that its value will always be unique.
Oracle does not have an AutoNumber or Identity attribute that can be assigned to individual fields; however, Oracle does provide a SEQUENCE object which can be used to accomplish the same objective. For this application an Oracle sequence object called CUST_ID_SEQ was created. When you create a sequence object, you specify a starting value and an increment value. To use the sequence, you refer to its NEXTVAL property (which will retrieve the next value of the sequence). You can also refer the sequence's CURRVAL property, which retrieves the current value of the sequence. For example, in the list of values to be inserted into a new CUSTOMER record, CUST_ID_SEQ.NEXTVAL could be used for the value of the CUSTID field.
In Oracle, stored procedures are coded in a language called PL/SQL. It is a fairly robust language similar in syntax to Pascal and Ada. The general syntax structure of an Oracle stored procedure (simplified) is:
CREATE [OR REPLACE] PROCEDURE procedurename[(parameter direction datatype [, ...])] IS
[variable_1 datatype;
...
variable_n datatype;]
BEGIN
statement 1;
. . .
statement n;
END procedurename;
In the syntax above, procedurename represents the name of the stored procedure. This is followed by an optional parameter list. If present, the parameters are comma-delimited and enclosed in parentheses. Each parameter consists of the parameter name, its direction (either IN, OUT, or IN OUT), and its datatype. Following the parameter list (if present) is the keyword IS. If any local variables are required, they are declared next (a variable declaration consists of its name followed by its datatype, ending with a semicolon). The body of the stored procedure starts with the keyword BEGIN and consists of one or more statements (which may be a mix of SQL queries and PL/SQL logic statements); each statement ends with a semicolon (;). The procedure ends with the keyword END followed by the procedure name.
The following four procedures were created for this application:
SelectCustomer. Returning a recordset from an Oracle stored procedure has traditionally not been a simple matter. However, from Oracle 9i forward as well as ADO 2.5 forward, it is now almost as easy as it is in SQL Server and Access. To return a recordset from an Oracle stored procedure, you must declare an output (OUT) parameter of type SYS_REFCURSOR. In the body of the procedure, you code a PL/SQL OPEN statement using the syntax:
OPEN ref_cusrsor_parameter_name FOR select_statement;
The recordset will be returned to the VB program via the SYS_REFCURSOR parameter. You need NOT create an ADO Parameter object to hold the recordset (if there were other parameters used in this procedure, you WOULD of course need ADO Parameters for those).
create procedure SelectCustomer(p_recordset out SYS_REFCURSOR) is
begin
open p_recordset for
SELECT FirstName, LastName, Address, City, State, Zip, PhoneNumber, CustID
FROM Customer
ORDER BY LastName, FirstName;
end SelectCustomer;
InsertCustomer. This procedure has an argument list consisting of the values for the data that will be inserted into a new row of the Customer table. The first statement is a special form of the SELECT statement. The INTO option enables you to select a single value from a table and store it in a local variable. In this case, the value we want to select is the NEXTVAL property of the CUST_ID_SEQ sequence, and the variable we are selecting that into is the pCustID output parameter. In our VB program, we will be able to retrieve this value from the corresponding ADO Parameter object. The table that we are selecting from is DUAL, which is built in "dummy" table in Oracle (used when you have to select from "something" but it doesn’t matter what). The value we set for pCustID, along with the other input parameters, are then used in the VALUES list of the INSERT statement.
CREATE PROCEDURE InsertCustomer(pCustID out number,
pFName in varchar2,
pLName in varchar2,
pAddr in varchar2,
pCity in varchar2,
pState in varchar2,
pZip in varchar2,
pPhone in varchar2)
IS
BEGIN
SELECT CUST_ID_SEQ.NEXTVAL INTO pCustID FROM DUAL;
INSERT INTO Customer( CustID, FirstName, LastName, Address, City, State, Zip, PhoneNumber )
VALUES ( pCustID, pFName, pLName, pAddr, pCity, pState, pZip, pPhone );
END InsertCustomer;
UpdateCustomer. This procedure has an argument list consisting of the values for the row of data (based on the CustID) that will be updated in the Customer table. The body of the procedure consists of a single UPDATE statement.
CREATE PROCEDURE UpdateCustomer(pCustID in number,
pFName in varchar2,
pLName in varchar2,
pAddr in varchar2,
pCity in varchar2,
pState in varchar2,
pZip in varchar2,
pPhone in varchar2)
IS
BEGIN
UPDATE Customer
SET FirstName = pFName,
LastName = pLName,
Address = pAddr,
City = pCity,
State = pState,
Zip = pZip,
PhoneNumber = pPhone
WHERE CustID = pCustID;
END UpdateCustomer;
DeleteCustomer. This procedure takes in one parameter – the CustID of the Customer row to be deleted. The body of the procedure consists of a single DELETE statement.
CREATE PROCEDURE DeleteCustomer(pCustID in number)
IS
BEGIN
DELETE FROM Customer
WHERE CustID = pCustID;
END DeleteCustomer;
The VB code for the Oracle version of the application is identical to the SQL Server version, with the sole exception of the ConnectionString. In the statement below, the ConnectionString specifies the required arguments needed for the Oracle installation used in the sample application. The required arguments and/or values for your Oracle installation will be different.
mobjConn.ConnectionString = "Provider=MSDAORA.1;" _
& "Data Source=THEVBPRO;" _
& "User ID=scott;" _
& "Password=tiger"
Download the project files for the Oracle version of the sample application here.