Database Troubleshooting

IWS database interface provides powerful tools that will help you to identify configuration problems with databases. If you are having problems interfacing with a database, you should first enable the Database Messages in the Logwindow. You can do so by following the steps below:

In the IWS Development environment, make sure to show the Output window (Output Window check box on the View tab of the ribbon).
Right-click in the Outputwindow (usually located in the lower-right corner of the development environment), and then click Settings on the shortcut menu:
In the Log Settings dialog, check the Database Messages option:
Figure 1. enabling Database Messages

After enabling this option, the Output window will display error messages related to the database. The FAQ section below lists some common errors that you can see in the Output window.

GENERAL QUESTIONS

Q: I configured my database, but the runtime modules (Alarm, Trend, and Events) are not being saved to the database. I only see the following error message in the Output window:

Database: Error: Error to add new register[CMD_ADD].

A: Most of the database errors in the Output window will be followed by additional information such as the SQL command being executed, the Connection String and the Table name. Error messages such as the one described above, will usually happen after a more detailed message. For example, if your Trend task fails to add a register in the database because the cable is disconnected, you should first receive a network error; if the task tries to add more registers before the time specified in the Retry field (see Database Configuration dialog), it will only display Database: Error: Error to add new register[CMD_ADD]. If you think that your configuration is correct, and you want to debug this type of problem, reduce the Retry. Then you should see more detailed information.

Q: When I try to access the MySQL database server, I get the following message:

Object is not set to an instance of an object.

A: This problem was detected under the following conditions:

A known bug in MySQL Connector/Net v6.1.2 would not correctly specify the charset; and
The database table you are trying to access doesn't exist.

To solve this problem, make sure you are using MySQL Connector/Net v6.2.0 and that the table you are accessing exists in the database.

Q: Why is the Database Interface automatically closing some connections?

A: By default, the Database Interface can have a maximum of 1000 connections. When this maximum is exceeded, the oldest connection is automatically closed to allow the new connection and the Output window displays an extended message describing which connection was closed and what was the last command executed.

To increase the maximum number of database connections, open the project file (project_name.app) in a text editor and change the following setting:

[StDB]
MaxConnections=number_of_connections

Keep in mind that increasing the maximum number of connections may decrease project performance.

Q: I configured my Connection String using the browser and the Data Link Properties Window. When I click the Test button, it says "Test succeeded". However, when I run my project, the Database Interface displays error messages, and I am not able to save data.

A: The Data Link Properties Window uses OLE DB to interface with the Database. IWS Database Interface uses ADO.NET; therefore, you can have the OLE DB provider on your machine and be missing the ADO.NET provider. It is also possible that you are using an ADO.NET provider that is not listed in the StADOSvr.ini file. Please refer to Studio Database Gateway for more information about adding ADO.NET providers to the StADOSvr.ini file.

Q: Why, when I update information in one line in the Grid object, is it updating more than one line in my database?

A: The grid object issues an update command in the database using the values in all the columns for the specific row that you are trying to update. If you have rows with duplicate values, you might see this problem. If your table has a primary key or any other unique field that you do not want to display in the Grid object, you can add it to the Columns but specify the Width 0. This will fix the problem.

Q: Why do I have to use a separate Column to store the milliseconds on my database?

A: Some databases do not support milliseconds in the Time Stamp field. IWS Database interface, by default, requires another column for the milliseconds. If your database can handle milliseconds, or if you do not want to record the milliseconds, you can change the default behavior in the Advanced settings. Note that some databases are able to store milliseconds, but they have lower precision. If you mix different databases with different precisions in redundant mode, you can get synchronization problems.

Q: My project works fine when I run in emulation mode. But when I send to the Windows Embedded device, it cannot communicate with my database.

A: It might be the case that your Windows Embedded device does not have the .Net Framework or that it does not have the provider that you are using. Try to use the gateway remotely by following the instructions in Linking the Database Through a Remote DB Provider.

Q: When I try to connect to the database, why do I receive the message, Error to create connection class?

A: The .Net Provider that you are trying to use is not installed on your machine. This error message is usually followed by the provider name; if you are using the Sybase database, for instance, the message is followed by [iAnywhere.Data.AsaClient.AsaConnection]. The Provider is the iAnywhere.Data.AsaClient. You can check if the provider is installed on your machine by going to the Control Panel > Administrative Tools > Microsoft .Net Framework x.x Configuration. The provider should be listed in the Assembly Cache.

Q: What if I have the provider assembly (usually a .dll file) but it is not listed in the AssemblyCache?

A: If your assembly has a strong name, you can register it in the Assembly Cache using the gcautil program. Or it should work if you copy your assembly to the same folder as the StADOSvr.exe (usually the […]\InduSoft Web Studio v7.0\Bin folder).

Q: I am not able to access my table from the Grid when I use a specific condition. But if no condition is applied, it works fine. Why is that?

A: You should check for the following items:

Follow the Troubleshooting steps, and look for error messages in the log. An error message can tell you if you have made a mistake, such as entering with a wrong column name or specifying an invalid data format.
Some databases have problems when you use reserved words as column names. Therefore, you should avoid using column names such as Time, Date, Numeric, etc.
If your column name starts with AND or OR (e.g., ORange), enter the name surrounded by square brackets. For example, instead of ORange=10, enter [ORange]=10.
If you are using SQL Server CE, you might have some problems when querying string fields. It has been identified that filters do not work with NCHAR data types; however, they do work if you declare these fields as NVARCHAR(<Number>). You might try to recreate your table by using this data type. An example of a command that creates a table with strings that can be queried is displayed below:
```
CREATE TABLE Table1 (Name NVARCHAR(128), Age Numeric, Sex NVARCHAR(1))
```

ORACLE

Q: When I lose the connection with an ORACLE database, it does not recover. I receive the following message in the logwin: Database: Error: ORA-03114: not connected to ORACLE. Is that a problem with the IWS Database Interface?

A: The Oracle .Net Provider has a problem managing the connection pool. You need to install a QFE 830173. At the time that this document was written, more information about this problem could be found at this site (external link).

Q: When I try to access the database, I get the following error message: ORA-00162: external dbid length 19 is greater than maximum (16). What should I do?

A: At the time that this document was written, there was a problem on the Oracle .NET Provider; the Server Name (SERVER/TNS) could not exceed 16 characters. In order to fix this problem, you should try to reduce your Server Name field. One way of doing this is to edit the file \WINDOWS\system32\drivers\etc\hosts to add an entry with a smaller server name. For instance, the server name specified by 192.168.89.98, has 13 characters, it could be reduced to 3 by adding the following line in the file:

192.168.89.98  ora

Now you can configure the Server Name configuration using ORA/TNS instead of 192.168.89.98/TNS.

MYSQL

Q: When I try to access the database from my local machine it works fine, but when I move my project to a remote machine, it says Access Denied.

A: Each user on a MySQL database has a property associated with it that indicates the computer fromwhich it can get access to the database. By default, this property is set to localhost, so you will only be able to access the database if you are accessing from the local computer. You should read the MySQL manual for information about changing this setting.

Q: Sometimes when I try to synchronize a remote MySQL database with a local MySQL database, or if I try to use application redundancy, a connection to the ADO.NET interface is opened and never closed.

A: Go to the Database Configuration dialog and uncheck the Automatically Create option.

SYBASE

Q: I configured my Sybase database using the Browse button. When I click the test button, the test succeeds, but when I try to run my project I get the following error: Database: Error: Parse error: DSN 'MyDatabase' does not exist. What am I doing wrong?

A: Please refer to Database Appendix F - Using Sybase for more information about this problem.

Q: Why, when I try to connect to the Sybase database, am I receiving the error Error to create connection class [iAnywhere.Data.AsaClient.AsaConnection]?

A: You do not have the ADO.NET Provider installed on your computer. The database setup program has an option to install the Provider. Rerun the setup program, and make sure to check that option.

SQL SERVER CE

Q: Why does the gateway show TypeLoad failure when I try to access my SQL Server CE database?

A: This problem usually happens when you do not have the SQL Server CE .NET Provider installed on your CE Device.

Q: Why am I getting the error message, There is a file sharing violation. A different process might be using the file?

A: You have another progarm with the SQL Server CE database open. For instance, this will happen if you are using the SQL Server CE configuration software.