Re: problem connecting to db.

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 31 Dec 2007 06:35:59 -0800 (PST)
Message-ID: <4a41f6bc-0bf3-47d9-8336-9c279e543fba@d4g2000prg.googlegroups.com>

On Dec 31, 5:28 am, "Mr. X." <nospam_at_no_spam_please.com> wrote:
> Thanks.
> I have tried the select statement as you suggest,
> and the current processes count are not near by the maximum limit (right
> now, respectively : 50, 200, 180).
> I think the current processes number grows on time, but I don't know how to
> follow it, unless I do some code (schedule that inspect the current status
> and write it to log every 5 minutes).
> There are no other relative files at bdump, udump etc ...
> How can I check the current service pack & MDAC 2.8, and why those
> installation related to the problem I have described.
>
> Thanks :)

There are too many "sometimes" for me to determine what is causing the problem. The following is not necessary Oracle related:

In the Visual Basic 6 editor, from the menu select Help, then about. If Visual Studio (98) service pack 3 was installed, the first line will show "Microsoft Visual Basic 6.0 (SP3)".

For the Windows 2000 Service Pack version, look in the System Control Panel on the server.

Why is it important to know the service packs that are installed? Windows uses dynamic link libraries (DLL) files that are read into memory as needed, and there are several needed by your VB program, as well as other programs that may be running at the same time. When Windows needs to read a DLL file, it first looks in the same folder as the VB program, and then searches the system path (you can check the value of the current system path by opening a command prompt and typing PATH). Some DLL files need the help of other DLL files to work correctly, and the helper DLL files must be the correct version. It can be very difficult to check all files needed by your VB6 program. A program that can help is Dependency Walker, which is installed automatically with newer versions of Visual Studio. The latest version can be downloaded for free here:   http://www.dependencywalker.com/

A Microsoft article that describes one way how to use the Dependency Walker is here:
  http://support.microsoft.com/kb/256872

Are there any other programs running using the Windows task scheduler, or other programs that run at the same time as your scheduled program? Those programs may be loading older or newer versions of the DLL files, causing your VB6 program to lock up once in a while.

You previously posted your code in brief like this:

'--------------------

Public OraCon As New ADODB.Connection
Public mainConnectionStr As String
...

public sub connectToDB
  On Error GoTo connectToDB_err
  mainConnectionStr = ...
  OraCon.ConnectionString = mainConnectionStr   Call OraCon.Open ' ***** here programs hang sometimes ******

                                ' ***** also, when hang - not always

there
is a line on alert.log, but sometimes there is. ******   goto end_proc
connectToDB_err:
' ****** when the above error, this code is never reachable *******   msgBox "" & err.number & "," & err.description end_proc:
end sub

'--------------------

One of the problems is that you do not know when the program is locking up - it may be before the connection is attempted or after the connection is attempted. You are taking a shortcut by using "Public OraCon As New ADODB.Connection", which can cause performance problems - the "New" keyword causes VB to check whether or not the connection has been used previously every time it is used. Maybe try the following modifications, which correct those problems and adds a file log to indicate connection progress:

'--------------------

Public OraCon As ADODB.Connection
Public mainConnectionStr As String
...

public sub connectToDB
  Dim intFileNum as Integer
  intFileNum = Freefile
  Open "C:\ProgramStatus.txt" for Output as #intFileNum   Print #intFileNum, "Setting Connection "; Now   On Error GoTo connectToDB_err
  Set OraCon = New ADODB.Connection
  Print #intFileNum, "Preparing to Connect"; Now   mainConnectionStr = ...

  OraCon.ConnectionString = mainConnectionStr
  OraCon.ConnectionTimeout = 30
  OraCon.CursorLocation = adUseClient
  OraCon.Open

  Print #intFileNum, "Connect complete"; Now   Close #intFileNum
  goto end_proc

connectToDB_err:
' ****** when the above error, this code is never reachable *******   Print #intFileNum, "Connect Failed"; Now   Close #intFileNum
  msgBox "" & err.number & "," & err.description end_proc:
end sub

'--------------------

One other suggestion. Since you are having problems connecting sometimes, and the program that is having problems connecting is started using the Windows Scheduler, is it possible to have your program run all the time, and sit quiet until needed? For example, create a timer, and set the Interval to 60000 - the timer will run its code every 60 seconds. If your scheduled task should start every 2 hours, you can use code similar to this: Private Sub Timer1_Timer()

    Static intCounter As Integer
    intCounter = intCounter + 1
    If intCounter >= 120 Then

        'The program has waited 120 minutes
        intCounter = 0
        'Do the work that is requred here

    End If
End Sub

That is enough VB code for now. If the above does not help, you may need to try a Microsoft related group for additional help.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Dec 31 2007 - 08:35:59 CST