MartyConnelly
martyconnelly at shaw.ca
Wed Feb 12 15:31:01 CST 2003
If using MSDORA and mdac 2.7 there is a memory leak
http://support.microsoft.com/default.aspx?scid=kb;en-us;322968
There are a couple of others
Seth Galitzer wrote:
>Hi folks,
>
>I'm asking here because I'm hoping somebody can help me out with this
>problem.
>
>I wrote this little VB app that collects temperature data in our
>greenhouses and then posts that data to an external server (using ADO)
>over the network. The app runs on three PCs and posts data every 15
>minutes. The database server is Oracle. I've got the Oracle drivers
>installed on all PCs.
>
>So here's the problem: the app runs great... for about three days, at
>which point it is unable to connect to the Oracle server (returns
>"Unknown Error" from the Oracle software layer). Once this happens, it
>is unable to reconnect to the server until I stop and restart the app.
>
>I do not keep the ADO connection open for the duration of the app run.
>I create a new connection each time it posts data. If it is unable to
>connect to the server every once in a while, that's fine, so long as it
>can connect on the next cycle. The problem is that it does not pick up
>on the nxt cycle, or the next, or the next... It will only connect
>again after I have closed the app and restarted it.
>
>So my question is this: where is this breaking down? Is it a problem
>with my code, or VB, or the ADO layer, or the Oracle driver layer? I'm
>pretty sure it's not my code (I'll include a copy of the relevant
>function at the bottom) since it really doesn't do anything
>extraordinary. I don't know enough about ADO to be able to blame it
>(this was my first real try at using ADO). I do know that the Oracle
>software layer is pain in the arse to install and is big, fat, and
>heavy, but don't know where to start poking to evaluate it. Could this
>be a limitation of th VB engine and do I just need to rewrite it in C++?
>
>Any suggestions would be welcome.
>
>Seth
>
>'-----Begin code snippet-----
>' Watch for word wrap, this is copied and pasted right
>' out of the code window
>' The error is reported as ocurring on line 31 below
>
>' Constants for trapping Oracle errors
>Public Const conORA04031 = &H80004005
>Public Const conORA04031B = -2147217900
>
>Public Function basPostData(cBoard As clsBoard)
> Dim strConnect As String
> Dim conn As ADODB.Connection
> Dim rst As ADODB.Recordset
> Dim lngSensorID As Long
> Dim dteTimeStamp As Date
> Dim intI As Integer
> Dim strSQL As String
> Dim strError As String
> Dim lngError As Long
>
>21 On Error GoTo Err_basPostData
>
>
>24 dteTimeStamp = Now()
>25 Set conn = New ADODB.Connection
>26 Set rst = New ADODB.Recordset
>
> ' Assemble connection string using system settings
>29 conn.ConnectionString = "Provider=" & conProviderString & ";Data
>Source=" & DBServer(False) & _
> ";User ID=" & DBUser(False) & ";Password=" &
>DBPassword(False) & ";"
>31 conn.Open 'error reported on this line
>
>34 For intI = 0 To cBoard.NumSensors
> ' First need to get sensor ID from database for insertion later
>36 rst.Open "SELECT SensorID FROM tbl_Sensors WHERE ComputerID =
>" & ComputerID(False) & _
> " AND BoardNumber = " & cBoard.BoardNum & _
> " AND ChannelNumber = " & intI + 1, conn '
>1-based index in data table
>39 rst.MoveFirst 'should only get one row back
>in the recordset
>40 lngSensorID = rst("SensorID") 'store value for later
> ' Insert new row into sensor_data table in database with data
>for this channel
>42 conn.BeginTrans
>43 strSQL = "INSERT INTO tbl_Sensors_Data (datetime, sensorid,
>data) VALUES (TO_DATE('" & Format(dteTimeStamp, "yy-mmm-dd:hh:mm") & "',
>'YY-MON-DD:HH24:MI'), " & lngSensorID & ", '" & cBoard.sData(intI) &
>"')"
>44 conn.Execute strSQL
>45 conn.CommitTrans
>46 rst.Close 'need to close recordset before opening the
>next one
>47 Next intI
>
>Exit_basPostData:
> ' Clean-up local object variables
>51 On Error Resume Next
>52 rst.Close
>53 conn.Close
>
>55 Set rst = Nothing
>56 Set conn = Nothing
>Exit Function
>Err_basPostData:
>59 strError = Err.Description
>60 lngError = Err.Number
>61 If boolLog Then basLogIt Hex(Err.Number) & ": " &
>Err.Description, "basPostData", Erl
>62 Select Case lngError
> Case 0
>64 Resume Next
> Case conORA04031, conORA04031B
>66 Resume Exit_basPostData
> Case Else
>68 MsgBox lngError & ": " & strError & vbCrLf & "in function
>basPostData", , "Unhandled Error"
>70 Resume Exit_basPostData
>71 End Select
>72 Resume 0
>
>End Function
>
>'-----End code snippet-----
>
>
>
>