Two bugs and one compability issue

Archived Topics
1

Hi,
I downloaded the demo version (2.2.6) of FactorySQL to evaluate it, and ran into serious problems. After some troubleshooting I think that I have found the source of my problem. It has to do with localized number format settings in Windows. I got this error all the time with the first server i tried:

SQLExecutionException
“Error executing update query: ERROR [21S01] [Microsoft][ODBC SQL Server Driver][SQL Server]There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.”

Nothing got written to the database, obviously. I’m using Microsoft SQL Server 2005. I first thought that I had configured it in a bad way, but then I realized that it could be the fact that I use Swedish locale in Windows while trying to log floats. The decimal separator in Sweden is ‘ , ‘ (comma) not ‘ . ‘ (dot). My guess is that the way FactorySQL updates the database doesn’t consider this. Let’s say I have the value 2,5 (that is, 2.5 in “English”) my guess is that it gets interpreted as the values 2 and 5, leading to the error above. The OPC servers returns floats according to the format set up in Windows and there is nothing I can do about that except change my locale to English. That is not really a good option. It would be better if this could be solved in the actual software if it really is what’s causing this.
When trying with integers, strings and dates everything works fine.

The second bug (kind of) was found when installing the demo on my co-workers machine. When starting the frontend we get this:

Necessary DLL Missing

Unable to start FactorySQL, as a necessary DLL was not found. You may need to reinstall FactorySQL, if the DLL cannot be added manually. The following line may indicate the missing file:

Det gick inte att ladda DLL-filen (sqlite3).

OK

The last line translates to “Unable to load DLL-file (sqlite3)”. The file is where it should be and has correct access rights. The same installation package installs correctly on other machines. The “bug” is IMHO that the installer somehow fails to install/register the DLL correctly, without giving any error messages during the installation. Right now we have 2 running installations out of 4 attempts. Both failed installations gives the above message. This is not very reliable, is it? All installations were done with administrative access.

The compability issue we have is with " Beijer Electronics OPC Server ". Other installed servers works just fine (on the sucessfull installations and apart from the comma-bug). FactorySQL cannot connect to Beijer or browse it. Other apps like Matrikon OPC Explorer have no issues. The security settings on all installed servers are identical, and we have tried to set Authentication level to “none” as mentioned in other posts. Any ideas?

The software looks promising, but has some issues that we need answers to before we can buy it. If we are going to deploy it on our customers systems, we want to make sure that we don’t get DLL errors on random machines for example. Thanks in advance for your answers :slight_smile:

2

Hello Jonas,

First off, thanks for your post. It’s always useful to hear about these experiences that users have.

Unfortunately, I won’t be able to clear up too much in this post, I mainly wanted to reply and let you know that I’m going to look in depth into these issues. However, I’ll make a few quick comments:

-Given the error message and the scenario, I believe your evaluation of the comma problem is correct. I will look into this, and file the necessary bug report.
-The DLL error: This has me a bit perplexed as that dll is not, in fact, registered with the system at all. FactorySQL uses SQLite.NET.dll, which in turn uses sqlite3.dll. If they are all in the same directory, it should work correctly.
Are there any noticeable similarities/differences between the machines that work and don’t work? Specifically: Version of windows, and version of the .NET framework installed? (You can find the frameworks that are installed by going to add/remove programs.)
Again, I’ll look into this more on my own, but any additional observations would help.

And finally:
-The OPC server: I could hypothesize, but first I will download the demo for the server, try it out, and get back to you.

Ok, hopefully I will be posting back within the day with some results on these issues. Sorry for the inconvenience.

Regards,

3

Hello Jonas,

I’ve done a bit of work today on these issues, and have managed to answer 2 of the 3. The unresolved problem is the inability to find the sqlite dll, as I was not able to replicate this anywhere. More information on the operating systems of all 4 machines might be helpful.

So first, the float issue. Indeed FactorySQL is attempting to convert the numbers to strings in the context of the current operating system locale, and thus coming up with these numbers containing the commas. There should be a fix for this very soon, with the 2.2.7 release (which will also address several other small issues).

For the Beijer OPC server: This is a mixed problem, half FSQL, and half their configuration. The problem is that they rely on an older version of the OPC Core Components, which FactorySQL does not work with. However, I think that you currently are a bit beyond this, as you are able to see the server, but not connect to it. In my experience today, after installing the Beijer server I was no longer able to see any servers on my machine at all, which I fixed by installing the newer core components. At this point, I could see the server, but not connect.

The reason is because the newer components check for different registry entries than the old ones. These entries are partially there for this server, but not complete.
To see the entries for this server, run Regedit, and look under HKEY_CLASSES_ROOT for Beijer. After installing on my machine, I had the following 3 keys:
Beijer Electronics OPC Server
Beijer.ElectronicsOPCServer.1
Beijer.ElectronicsOPCServer=

(note the “=” on the last item is not a typo)

The OPC browse program (OPCEnum.exe), uses these to enumerate the server and track down its CLSID. Unfortunately, the browser was returning the server name “Beijer.ElectronicsOPCServer” to FactorySQL. The “failure to connect” was coming when FactorySQL attempted to use that name to lookup the CLSID of the server. Not only is the name wrong in the registry (there is an additional “=”), it does not even have the CLSID subkey below it.

Ultimately, I was able to get the server to work by doing the following:
-Rename the key and remove the “=”.
-Add a new subkey, called CLSID
-Edit the (Default) value of the CLSID key and change it to the CLSID of the server, which is:
{8F268B40-00EB-11d6-BC71-006008D0085F}
(including brackets)

After doing this, and hitting the “Refresh Servers” button in FactorySQL, I was able to browse and use the server as normal.

So, as I said, it’s a mixed issue. I’ve filed a report on it, though, because I believe FactorySQL should be able to better handle servers who rely on the older enumeration method. In the mean time, I hope this gets you up and running.

Again, check for the 2.2.7 update soon to fix that number formating issue, and please feel free to post with any other comments/questions.

Regards,