Solved

COM objects efficiency design for database connection

Posted on 2002-06-24
9
188 Views
Last Modified: 2010-05-02
I just studied COM and tried implementing a puzzle solver application using Active Exe as my server component and a standard exe as my client.

But I created my objects to create a database connection in its exposed public function. Thus everytime when I request for the component to run the puzzle solver's logic, I have to create a new database connection. Which I believe will create an overhead, if I choose to reuse the component object.

I was hoping that I could pass the exposed public function of my COM object as an ADO recordset object. Thereby, I wouldn't have to create a new connection everytime I instantiate the class (clsDictionary), which actually creates an overhead, especially if the database is rather large in size.

But this isn't possible as an error saying 'wrong data type' will appear, if I implement it that way.

Is there any particular technique or design that I could adhere to, in order to lessen the overhead by using a single database connection only at the point of entry.

I would like to attach my codes so that it can be downloaded but there isn't any place I can do it in experts-exchange. So i'll just have to explain it through text.

This part sits in clsDictionary of my ActiveX exe project
_________________________________________________________

Public Function SearchStrSQL(SQLString As String, SearchType As Integer) As Boolean
    Set conDictionary = New Connection
    Set comWordlist = New Command
    Set rstWord = New Recordset
   
    On Error GoTo errhandler
   
    With conDictionary
        .ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Dictionary;Data Source=PHILIP"
        .Provider = "SQLOLEDB"
        .Open
    End With
   
    If SearchType = 1 Then
        SQLStr = "select * from wordlist where word like '" & SQLString & "' order by word "
        rstWord.Open SQLStr, conDictionary, adOpenDynamic, adLockOptimistic
    ElseIf SearchType = 0 Then
        SQLStr = "select meaning from wordlist where word like '" & SQLString & "' order by word "
        rstWord.Open SQLStr, conDictionary, adOpenDynamic, adLockOptimistic
    End If
   
        Call WriteToFile(SearchType)
        SearchStrSQL = 1
    Exit Function
   
errhandler:
    SearchStrSQL = 0
    MsgBox Err.Number & "Error encountered"
End Function
________________________________________________________

This part sits in my Client, standard exe project
_________________________________________________________

Private Sub cmdFind_Click()
Dim blnValid As Boolean
Dim Searchstr As String
Set objDictionary = New clsDictionary
Searchstr = txtStringSearch.Text
blnValid = objDictionary.SearchStrSQL(Searchstr, 1)
If blnValid = True Then
    Call ReadFromFile
    If indicator = 0 Then
        Call PrintToListBox
    End If
    indicator = 0
Else
    MsgBox "Error occured"
End If
End Sub

0
Comment
Question by:philcp
  • 5
  • 4
9 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 100 total points
ID: 7104467
You can reduce overhead by creating a class-global connection, a client-global objDictionary, setting objDictionary on the client start-up, and opening the connection in an Initialization step for the class.  

Once that is set, make sure that the class is destroyed when the client closes, and that the class closes the connection when the object is destroyed.
0
 

Author Comment

by:philcp
ID: 7104492
Hi bhess1,

When you say, i have to create a class-global connection, what function is similar to a Sub form_load() module to a class that has been instantiated?

Phil
0
 

Author Comment

by:philcp
ID: 7104508
sorry,
i think i didn't make myself. my words are lil' out of place. Allow me to phrase my question again.

To a ActiveX exe project where could I place my codes for database connection other then my exposed function. so that I don't have to create a new connection everytime i'm interfacing with the object.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:philcp
ID: 7104532
nvm i got it, is it to be place at class initialize?
but how do u destroy the class when the client closes?
0
 
LVL 32

Expert Comment

by:bhess1
ID: 7104691
Set the Class variable in the CLient to Nothing on the termination of the client.  For example, if Form_Unload is the last action in the client, you could add:


Sub Form_Unload(Cancel as Integer)

If Not objDictionary is Nothing Then
   Set objDictionary = Nothing
End If

End Sub
0
 
LVL 32

Expert Comment

by:bhess1
ID: 7104695
Yes, class_Initialize should be where you open the connection.  And class_Terminate should close it.
0
 

Author Comment

by:philcp
ID: 7112782
When I put my data connection codes in the class_initialize() standard procedure, along with a error handler. I seem to catching an err.number numbered '0'. I added the err.description but there's no description written down to explain it. And I tried looking up in MSDN, but nothing too. What could actually caused it?
0
 
LVL 32

Expert Comment

by:bhess1
ID: 7114464
Error number 0 is no error.  I have had this happen in my code... are you remembering to Exit Sub before dropping into your error handler code?
0
 

Author Comment

by:philcp
ID: 7118590
Well i guess I'll check my codes. Thanks for the help bhess1. I feel more confident with doing COM now... :) But I guess I have lots to learn.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question