Hi Michel
If you are eventually thinking about changing the backend have you looked at the Advantage Database Server? I think you will find the cost fairly reasonable and you can still use DBF files if you wish.
Regards,
Simon White
>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.
Simon White
dCipher Computing
