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----- > > > >