I have spent two weeks trying to figure out a way to make my Web application work with the VFPOleDb.1 provider. This provider, from VB.NET, under .NET 2.0, works ok as long as I don't hit the server that much. It was difficult to run a stress test until the data classes fully completed. But, during our application phase, this turned out to be a terrible disaster. At 3000 hits a day, during our development phase with unitary tests, this was working quite ok. There was about 1 to 3 errors appearing on a daily basis and thought that I would have been able to resolve them. Then, when everything was completed, I started to run stress tests against the server. I sent over thousands of hits since last week and a quarter millions last night. I have done it over again today. Basically, I ended up with dozens of errors every half hours. So, this means about a few hundreds errors per day.
This situation is a complete non sense. I eliminated all kinds of factors. This, when it happens, happens only at the SQL time such as SQLExec(), SQLUpdate(), SQLInsert() and SQLDelete(). The more aliases in a SQL command, the greater the chances of having the errors.
The errors also do not make any sense. I got syntax error, missing fields, column not found and index does not match errors, for examples. When comes time to obtain a column not found, most of the cases the column mentioned is not even in the SQL.
Tonight, I removed the Global.asax completely from the Web site, removed any DLL calls and simply dropped this in a test page:
<%@ Page Language="VB" AutoEventWireup="false" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Sub Page_Error(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Error
CreateFile(Server.GetLastError().GetBaseException().Message, "c:\iis\direct martial arts\Temp\a.txt")
End Sub
Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim lcSQL As String = "SELECT Video.Numero FROM Video " + _
"INNER JOIN Masters ON Video.NoMaster=Masters.Numero " + _
"INNER JOIN VideoType ON Video.NoVideoT=VideoType.Numero " + _
"INNER JOIN VideoLanguage ON Video.NoVideoL=VideoLanguage.Numero " + _
"INNER JOIN VideoClassification ON Video.NoClass=VideoClassification.Numero " + _
"INNER JOIN VideoSize ON Video.NoVideoSiz=VideoSize.Numero " + _
"WHERE Video.Active2 AND Video.Numero=21 ORDER BY Video.VideoT"
Dim oDataSet As System.Data.DataSet = New System.Data.DataSet
Dim oDataAdapter As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(lcSQL, _
"Provider=VFPOLEDB.1;Data Source=""c:\iis\Direct Martial Arts\App_Data"";Collating Sequence=general")
oDataAdapter.Fill(oDataSet)
oDataAdapter.Dispose()
End Sub
Private Function CreateFile(ByVal tcString As String, ByVal tcFile As String) As Boolean
Dim loFileStream As System.IO.FileStream
Dim loStreamWriter As IO.StreamWriter
loFileStream = New System.IO.FileStream(tcFile, System.IO.FileMode.Create)
loStreamWriter = New System.IO.StreamWriter(loFileStream, System.Text.Encoding.Default)
loStreamWriter.Write(tcString)
loStreamWriter.Close()
loFileStream.Close()
Return True
End Function
</script>
<html>
<body>
<asp:Label ID="Header2" runat="server" Text=""></asp:Label>
<asp:Label ID="Html" runat="server" Text=""></asp:Label>
<asp:Label ID="Footer" runat="server" Text=""></asp:Label>
</body>
</html>So, when calling the page, no DLL is being invoked, no object created and basic code so I could issue one simple little SQL. When there is an error, I simply create a file on disk. The file gets created pretty fast and as soon as I remove it, a few seconds later or even within the next minute, I get more errors.
The SQL commands work about 99.9%. I have a VB.NET stress test application that is launching four simultaneous calls to this page every .25 second. So, this means, the page receives about 16 hits a second. So, in a minute, the page receives about 960 hits. When I reach about 3 to 5 minutes, I could have 3 to 5 errors. Sometimes, I could have more. Sometimes, it could take about 10 minutes before I receive the first one.
To me, after having wasted two weeks to troubleshoot this, we have to drop VFP as the backend. Unless someone can provide me another way to access the VFP data, I really can't see how we can rely on such a provider which is terribly so buggy. It has already been confirmed that the provider generates the index does not match error on an occasional basis. But, what I have is far worst then that. It simply doesn't make any sense to launch an application in production within this context. I just can't afford to provide an image of the product to our customers while they will obtain an error about once every hour.
So, this comes down to moving it up to SQL Server. Unless I am missing something here, I really can't find a solution. I brought down the code to a really simple design. It just cannot be any simpler than that.
If anyone would like to use my VB.NET application stress test, I would be happy to send them the project so they could launch the four simultaneous hits at every .25 second against their own Web server having such a test page. The only thing needed would be to adjust the directories and the connection string. The creation of test tables would also apply.
I tested that on three servers. One of them was Windows Server 2000 and the other two Windows Server 2003. I was not able to simulate that on my Windows XP Pro development PC however. I just can't explain that.
