Pages [ 1 2 3 4 ]

Database Operation Services (continued)

There are four procedures that were coded for this example application, the Form_Load event procedure, the cmdExecute_Click button control event procedure, the cmdExit_Click button control event procedure, and the Form_QueryUnload event procedure. The code in black type is what we want to focus on.

The following code is from the declarative section of the Form’s code module. It is here that we declare a module level variable for the top level database operations object:

Option Explicit

'Copyright (c) 1997-2001 VantagePoint Software, Inc.
'All rights reserved.

'Product
'Program Name
'Form Name
'Author
'Date
'Description
'
'
'
'Revisions mvComponents
Demosp
frmMain
L W Wilcox
11/11/97
Program for demonstrating mvComponents Database
Operations objects and the use of stored
procedures from a MultiValue (Pick) Database.
Form for displaying results of stored procedure.

Private mDbs As DbSession

The next section of code is the procedure for the Form Load event.

Private Sub Form_Load()

'Purpose Form Load Event handler. Define needed virtual database

' definition (vdd) and database objects. Open connection to database.

'Inputs Void

'Outputs Void

'Revisions

Dim vdd As VirtualDatabase

Dim dbCustomers As Database

On Error GoTo ErrorHandler

'Recommended when using the EXE component VPDir20.exe or VPRem20.exe that handles

‘communication to the Pick Host system.

App.OleServerBusyRaiseError = False

App.OleServerBusyMsgText = "dbObjects Database Server is currently busy. Choose OK to try again."

App.OleServerBusyMsgTitle = "dbObjects Database Server Operations"

App.OleRequestPendingTimeout = 25000

App.OleRequestPendingMsgText = "A dbObjects Database Server operation has timed out. Choose OK to continue processing."

App.OleRequestPendingMsgTitle = "dbObjects Database Server Operations"

'Create Virtual Database Definition object

Set vdd = New VirtualDatabase

'Load Vdd object with an existing customer database definition

mVdd.Load "c:\program files\dbobjects\examples\demomvbase.vdd"

'Show form

Me.Show

Me.Refresh

lblStatus.Caption = "Creating database objects..."

'Create database session object

Set mDbs = New DbSession

mDbs.Name = "Customer List"

mDbs.AppName = App.EXEName

'Add Customers database and set a reference as dbCustomers

mDbs.Databases.Add "Customers"

'Set a temporary reference as dbCustomers

Set dbCustomers = mDbs.Databases("Customers")

lblStatus.Caption = "Attaching virtual database definition object..."

'Attach Vdd to new database object

dbCustomers.Attach vdd

lblStatus.Caption = "Now opening database source..."

'Open a connection to the database source

dbCustomers.OpenDatabase

'Clear temporary object reference

Set vdd = Nothing

Set dbCustomers = Nothing

'Setup grid

msgCustomers.ColWidth(0) = 900

msgCustomers.ColWidth(1) = 2400

msgCustomers.ColWidth(2) = 1100

msgCustomers.TextMatrix(0, 0) = "Cust ID"

msgCustomers.TextMatrix(0, 1) = "Name"

msgCustomers.TextMatrix(0, 2) = "City"

msgCustomers.Row = 0

msgCustomers.Col = 0

msgCustomers.CellAlignment = 4

msgCustomers.Col = 1

msgCustomers.CellAlignment = 4

msgCustomers.Col = 2

msgCustomers.CellAlignment = 4

lblStatus.Caption = ""

cmdExecute.Enabled = True

Exit Sub

ErrorHandler:

If Err.Number < 0 Then Err.Number = Err.Number - vbObjectError

'If Err.Number <> 52 Then

MsgBox "Error: " & LTrim(Str(Err.Number)) & " was generated by " _

& Err.Source & vbCr & Err.Description, , _

"Customer List", _

Err.HelpFile, _

Err.HelpContext

'End If

Set vdd = Nothing

Set dbCustomers = Nothing

Set frmMain = Nothing

End

End Sub

We first dimension two variables, one to use as a reference to a Virtual Database Definition object and the second to use as a reference to the database object we will later add to a collection of database objects.

Dim vdd As VirtualDatabase

Dim dbCustomers As Database

We next alter how visual basic handles busy requests to an out-of-process server. This is done by setting a number of properties associated with the VB App object. This is necessary because the default settings are not very good and the database object of dbObjects uses an out-of-process server (VPGwsv20.exe) to actually make the network calls to the MultiValue (PICK) computer system. We use an out-of-process server in case there is a communication failure that crashes the communication process. By having the communication process separate from our application process a failure during communication will not cause our application to crash as well which it would if it ran in-process as a DLL.

App.OleServerBusyMsgText = "mvComponents Database Server is currently busy. Choose OK to try again."

App.OleServerBusyMsgTitle = "mvComponents Database Server Operations"

App.OleRequestPendingTimeout = 25000

App.OleRequestPendingMsgText = "A mvComponents Database Server operation has timed out. Choose OK to continue processing."

App.OleRequestPendingMsgTitle = "mvComponents Database Server Operations"

We next instantiate a Virtual Database Definition object (VDD) and establish its state (set up all of its properties) by executing the Load method and giving it the location on disk where a persistent version of a particular VDD object can be found. Typically you would not hard code the disk location in code but would read the location value from an entry in the system registry or from an INI file into a variable and use that variable as the argument to the Load method. Or by not passing any value to the Load method a common Find dialog box would be opened by the Load method and you could let the user search and find a particular VDD object of their choice.

Set mVdd = New VirtualDatabase

'Load Vdd object with an existing customer database definition

mVdd.Load "c:\program files\dbobjects\examples\demomvbase.vdd"

We next instantiate the top level DbSession object, give it a name, and assign our application name from the VB App object to the new session object.

Set mDbs = New DbSession

mDbs.Name = "Customer List"

mDbs.AppName = App.EXEName

Once the session object is created, the Database object server (VPDb20.dll) automatically creates an empty collection object for holding individual database objects that we might want to work with. We then use the Add method of the collection object (Databases) to create a new Database object that we called “Customers”. Once the Database object is created, we set the object variable dbCustomers that we dimensioned at the beginning of the procedure to this newly created Database object within the collection of Databases. We do this to simplify our coding so that to refer to the Customers Database object we just use the variable dbCustomers rather than the actual object reference of mDbs.Databases("Customers") from the collection.

'Add Customers database and set a reference as dbCustomers

mDbs.Databases.Add "Customers"

'Set a temporary reference as dbCustomers

Set dbCustomers = mDbs.Databases("Customers")

We next take the Virtual Database Definition object (VDD) that we created earlier and assign its object reference variable to our new Database object with the Attach method. This allows our new Database object to take on all the characteristics and properties of the database defined by the Virtual Database Definition. Thus our dbCustomers Database variable points to a Database object that has been defined to work with a MultiValue (PICK) database system, knows where that system is, how to connect to it, what protocol to use, and any other information it needs to operate as that database. This is all done with the Attach method.

dbCustomers.Attach mVdd

Once our Database object is fully defined, we are ready to use it. The first thing we must do is to open a connection to the database and the MVSERVER account software that manages our client/server connection. This is done by executing the OpenDatabase method.

dbCustomers.OpenDatabase

The last thing to do in this procedure is to destroy any object references we don’t need by setting them to nothing. Because the relevant object reference to our database session object is a module level variable, it will have sufficient scope so that it will be available to us in any other procedure associated with the VB Form. Setting the dbCustomers object reference to nothing does not destroy our database object just the local variable reference created in this Form_Load event procedure.

Set vdd = Nothing

Set dbCustomers = Nothing

Other code in this procedure is needed to initialize and set certain properties of the grid control and normal error handling.

The next section of code is the procedure for the Execute command button Click event. It is here that we use the ExecuteProc method of the database object to execute the "CUSTLIST" stored procedure that returns our customer list data. We then take that data and load it into the grid control.

Private Sub cmdExecute_Click()

'Purpose Command button Click Event handler. Execute database

' stored procedure. Load output into grid control.

'Inputs Void

'Outputs Void

'Revisions

Dim dbCustomers As Database

Dim strCustList As String

Dim dsaCustList As New DynamicArray

Dim lngCnt As Long

Dim i As Long

On Error GoTo ErrorHandler

'Clear grid

msgCustomers.Rows = 1

'Set reference to Database object

Set dbCustomers = mDbs.Databases("Customers")

'Execute stored procedure

strCustList = dbCustomers.ExecuteProc("SP.CUSTLIST")

'Create temporary dynamic string array for customer list

dsaCustList.Create strCustList, vxRM, vxAM

dsaCustList.Delete 1 'Ignore Condition flag

'Load returned customer list into grid control

'Change attribute marks to tabs for grid

dsaCustList.Delimiter(2) = vbTab

lngCnt = dsaCustList.Count

For i = 1 To lngCnt

msgCustomers.AddItem dsaCustList.Extract(i)

Next i

'Clear object reference

Set dsaCustList = Nothing

Set dbCustomers = Nothing

Exit Sub

ErrorHandler:

If Err.Number < 0 Then Err.Number = Err.Number - vbObjectError

MsgBox "Error: " & LTrim(Str(Err.Number)) & " was generated by " _

& Err.Source & vbCr & Err.Description, , _

"Customer List", _

Err.HelpFile, _

Err.HelpContext

Resume Next

End Sub

We first dimension several variables, one to use as a reference to the database object, a string variable that will be used to hold the result data from the stored procedure, an object variable to serve as a reference to a Dynamic String Array object that we will create, a long integer variable to hold a counter of the number of rows or customer records returned, and another long integer to act as a temporary index value.

Dim dbCustomers As Database

Dim strCustList As String

Dim dsaCustList As New DynamicArray

Dim lngCnt As Long

Dim i As Long

We next set a reference to our Database object within our collection of Database objects using the module level variable reference to the top level database session object created in the Form Load event procedure. Again, we do this to simplify our coding so that to refer to the Customers Database object we just use the variable dbCustomers rather than the actual object reference of mDbs.Databases("Customers") from the collection.

Set dbCustomers = mDbs.Databases("Customers")

We next execute the stored procedure "SP.CUSTLIST" using the ExecuteProc method of the Database object. There are no arguments needed by this simple stored procedure and other than the name of the procedure nothing is passed as a parameter. If additional arguments were required they could be done with additional parameters to the ExecuteProc method. The resulting list of customer information is returned by the stored procedure that runs on the MultiValue (PICK) Server system and this data is assigned by the ExecuteProc method to the string variable "strCustList" that we dimensioned at the top of the Click event procedure.

strCustList = dbCustomers.ExecuteProc("SP.CUSTLIST")

Now that we have our data we need to get it into the grid control for display within the VB form. We can do this a number of ways, the easiest is by using the Dynamic String Array object from Power Strings. We first instantiate a new DynamicArray object from the Dynamic Array object server (VPDsa20.dll). We then initialize a new array object with our returned string data “strCustList” and identify to the object that this data has two dimensions and the data is delimited by record marks (CHAR 255) between each customer record and attribute marks (CHAR 254) between each field within a record. The constants vxRM and vxAM are defined by the DynamicArray object server and represent the delimiter characters we need.

dsaCustList.Create strCustList, vxRM, vxAM

Because the SP.CUSTLIST stored procedure was constructed to work with the high-level MVDatabase component it includes a condition flag value as the first record of the returned data. We don't need to respond to this condition flag data that is the first record and so in this example program we can just delete it.

dsaCustList.Delete 1 'Ignore Condition flag

We next change the delimiter used to separate each field from the attribute mark to a Tab character by setting the Delimiter property of the DynamicArray object. This is necessary because the Grid control will expect a Tab character to separate each field within a row of data.

dsaCustList.Delimiter(2) = vbTab

We next get a count of the number of rows using the Count method of the DynamicArray object.

lngCnt = dsaCustList.Count

Using this count we use a For/Next loop to load the Grid control with each Customer’s data, one row at a time.

For i = 1 To lngCnt

msgCustomers.AddItem dsaCustList.Extract(i)

Next i

The last thing we need to do is clear any temporary object references we don’t need.

Set dsaCustList = Nothing

Set dbCustomers = Nothing

The rest of the code in this procedure is for error handling.

The next section of code is the procedure for the Exit command button Click event. It is here that we unload the VB form and end the execution of the program. The Unload method will trigger the QueryUnload event code in the next procedure before the program actually ends execution.

Private Sub cmdExit_Click()

'Purpose Exit command button Click Event handler. Unload form and End program.

'Inputs Void

'Outputs Void

'Revisions

Unload frmMain

End

End Sub

This last section of code is the procedure for the Form’s QueryUnload event. It is here that we close the connection to our database and clear all remaining object references, including the reference to the form itself before the program ends.

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

'Purpose Form QueryUnload Event handler. Close database connection.

' Clear object references and reference to form.

'Inputs Void

'Outputs Void

'Revisions

On Error Resume Next

'Close Database session

mDbs.CloseSession

'Clear all references

Set mDbs = Nothing

Set frmMain = Nothing