Hi we are using the latest version of DataDirect to connect to FoxPro dbf database tables.
Everything is working fine, except when we try to change the path to the database. We do this by changing the registry value HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\<My DSN NAME>\Database and then run an SQL query pointed at that DSN.
We find that sometimes the query uses the previous value of the Database registry value.
I have verified that the proper value is getting set in the registry by retrieving and logging its value right before the SQL statement is executed.
Is DataDirect perhaps caching any of the DSN information? I tried setting the "CacheSize" and "FileOpenCache" registry values to "0" but still see the same error.
If you use a connection that was opened prior to the registry change being made you'll see the undesired behavior you describe. It wouldn't only be intermittent if sometimes you're using a connection opened after the change and somteimes you're using an old connection from before the registry change.
What's the reason for modifying the registry? Many applications give you control over the connection string for an ODBC connection. If this is possible with your app, the connection string could simply look like DSN=<my DSN Name>;database=<my database name>. This would help avoid confusion on database you're connected to and let you avoid mucking with the registry. What application are you using?
Thanks for the reply, Brody. Good suggestions.
Your suggestion to supply the database name in the connection string makes sense, unfortunately I can't use it because the DSN is controlled by another application, so I have to rely on whatever value of the Database name has been set by that application (which my application doesn't know or control).
I am connecting with Dotnet using the System.Data.Odbc.OdbcConnection class. I am simply doing:
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString = "SourceType=DBF;DSN=<MY DSN NAME>";
conn.Open();
...
conn.Close();
conn.Dispose();
In between calls to this block of code, another application may change the registry value mentioned in my first post. I put a breakpoint on the above conn.Open() call and check the registry value. It is always as it should be, therefore I know the issue is not with writing the value to the registry. But once I step over the line, the conn.Database property is sometimes the expected new value, but it is sometimes the old value.
Is the DataDirect driver caching the database name? If so, can I control this via a connection string attribute? I know some drivers cache "meta data" so it doesn't have to be retrieved from the registry every time. If DataDirect is doing this, I need a way to turn it off...
Thanks for your attention Brody!
Andrew
Hey Andrew,
Is your .net app that is opening the connection writing to the registry to set the database? Or is that the other application? (I assume it's the "other" application...but just wanted to verify).
The driver shouldn't be caching...but it's not obvious to me what could be happening...unless the .net to ODBC provider is somehow causing the driver to lookup the DSN info prior to the conn.Open(). You could potentially try to investigate that a bit. Otherwise I'd suggest contacting support...they'll help dig deeper to find out what's going wrong.
Brody
I kind of misspoke. It is two different assemblies, one writing to the registry and the other creating the OdbcConnection object. So, same application, but different assemblies that aren't coupled.
I expect this would be easy for you to reproduce. Just implement the above code and put a breakpoint on the line that instantiates OdbcConnection. When the breakpoint hits, change the Database value in RegEdit and then let the debug session continue. I'm betting your OdbcConnection sometimes doesn't get the new value (check the value of OdbcConnection.Database attribute).