Q1

Why when my program is running, why is the data not being saved to the database?

A1

If you are working with deferred transactions, there may have been an error that occurred in a subtask and therefore the entire transaction was not committed to the database. Check to see if there is an error in the Activity Log.

Q2

How can we avoid the status line's "duplicate index" notification and trap the error in an event handler (error handler) when a Duplicate Index error occurs?

A2

Uncheck the Check index Database entry property.

This change will avoid the index check by the Magic xpa engine (available for ISAM databases only).

Once Magic xpa no longer checks the index, a duplicate value will cause the database to throw an exception. These exceptions can be trapped by an error event handler in the Magic xpa program.

Q3

Why am I getting the "Duplicate index, data source" or "Constraint failure, data source" error?

A3

These errors mean that you tried to create or update a record and this update violates a database constraint, such as a having two records with the same ID (duplicate unique index).

You need to correct your data or cancel your changes by opening the Edit menu and selecting Cancel.

Q4

Why I am I getting the "Failed to open lock file, data source: filename" error?

A4

This error means that a physical file you are trying to access does not exist or you do not have the permissions to access it.

To solve this problem, check the logical names. Make sure that:

• The logical names are pointing to the correct directory.

• The translation for the logical names include a trailing backslash (\).

• The databases are using the correct logical names, which are case sensitive.

Q5

Why I am I getting the "Invalid object name '{databasename}..{tablename}'" error?

A5

This error means that a table that you are trying to access does not exist.

If you are trying to create a new data source, you can instruct Magic xpa to create the data source for you by doing the following:

1. Close the Magic xpa project (but not Magic xpa).

2. From Options > Settings > Databases, select the database that you are using in the data source.

3. From the Database Properties, go to the SQL tab and check the Check Existence check box.

4. Click OK.

5. Open the project and try running the APG on the data source.

Q6

Why is the error message "Failed to open, data source: [TEMP_PATH]\MG7832-1583.mem" appearing in the status line and mgerror.log file?

A6

Usually such a message can appear on physical tables and can stem from various reasons (no write permissions, no such location, location inaccessible, etc.).

Sometimes the message appears when working with a memory table (either one created by the engine for its internal usage such as sorting or an application memory tables defined by the programmer). The solution for this case is described below.

There is a default limitation to the number of opened Memory tables.

V9 = 200

Magic xpa = 500

When more than the default number of tables are opened, the error message is displayed, the flow stops and rollback is done.

There are cases in which this limitation is not enough – to change it, you need to modify the Memory table's DBMS parameters with the keyword INITFILE=nnnn (where nnnn is the number of Memory tables one would like the engine to handle).

For example, in Magic xpa, changing this number to 1000 would result in the following line in the ini file:

Memory Tables =22, NotAllowNull, 10.3, INITFILE=1000,N, , NotLogSync, 0, 0, NotCheckExist,

The engine handles the application’s memory tables by using an internal memory table to allocate an entry for each opened memory table.

The engine also counts the sort/temp tables if they are defined as memory.

The INITFILE parameter determines how many entries the engine will allocate in the table that handles all open memory tables.

Each entry in that table consumes a few bytes, so do not use a large number as you will allocate space for no good reason and handling a very large table is always slower that a small one.

Use a number that is 20% higher than the limit you assume you will reach.

Set it to a number that reflects the total number of memory tables you will want to open.

Note that each context opens memory tables of its own, so the number should be:

max number of RIA client (or contexts) * number of memory tables being opened for each context.

(Count how many memory table entries you have in the Data repository that are really being used.)

MSSQL

Q7

Why am I getting an “Incorrect syntax near” error?

A7

When connecting to an MSSQL database whose name begins with a number, contains a space or a hyphen (-), you may receive one of these errors:

• DB Error: Message String: Server Name: Incorrect syntax near

• MSSQL Error - Incorrect syntax near '-'

These are database restrictions. To overcome these errors, in the Database Name column of the Magic xpa Database repository, surround the database name with square brackets, such as [a b] or [TEST-DB].

Q8

MSSQL is defined as case insensitive, but Magic xpa still behaves as case sensitive. Why?

A8

In MSSQL, the database collation is case insensitive, but Magic xpa still behaves as case sensitive.

To make sure that the whole environment will be case insensitive, you need to configure the client to be case insensitive as well, because the client also performs checks.

To do this, use ACS files in the database settings under the SQL properties.

In order for the ACS file to work, the Studio needs to be restarted.

Q9

Why am I getting the “Unable to connect to Server: Maximum connections reached” error?

A9

This error message reflects the fact that the number of connections open to the database has reached its limit.

Applicable to: Microsoft SQL Server

Known possible causes:

• The number of connections allowed from Magic xpa is too low.
• Updates outside a transaction - updates within a transaction will always consume 1 connection. If no transaction is defined - every update will create a new transaction.
• Working without cursors - defining Cursor=No in the database properties.
• Having the database connection set as TCP/IP (only).
• Parallel execution of programs - since this was not possible before Magic xpa, moving from previous versions and defining a program as parallel might cause more connections to be used.
• Direct SQL programs - these programs require their own connection.

Possible Solutions:

• Set the number of connections to an explicit high value (e.g. 9999). By default the value is 0, which means 3 and not an infinite number.
• Check the application and make sure that all updates are done from within a transaction.
• If possible, prefer working with cursors, or leave the value as Default (which means cursors for Online programs).
• Adding named pipes to the client's connection settings.

Parallel programs and Direct SQL tasks will require an extra connection and this cannot be avoided.

Q10

When trying to sort the records in a DSQL program, why is the performance slow?

A10

When using a Main source, Magic xpa generates the SQL statement to the server. Therefore, once you click on a column a new statement is sent with an ORDER BY clause, so the sorting is done on the database.

In DSQL tasks, Magic xpa does not generate the statement because it uses the statement that the developer provided. Therefore, in order to sort the records, all of the records should be fetched from the database and then Magic xpa will sort them in the memory. This process can take a lot of time because fetching the entire data and sorting are very heavy operations in terms of performance.

To solve the problem you can:

1. Add another parameter (:1) at the end of the statement.

2. Once the user clicks on a column you should handle the Column Click event.

3. Add to the statement the ORDER BY clause according to the column that was clicked.

4. Raise the View Refresh event to make the new statement be sent to the database.

In this way you will keep the sorting operation on the database side and Magic xpa will not be needed to sort or to load the entire result set.

Q11

When trying to lock the entire table using Share=None on an MSSQL table it doesn't work. Why?

A11

The Share None was not implemented for the SQL server because it is not recommended to lock the entire table. If you still need to lock the entire table you can use one of the following methods:

1. Use the Magic locking mechanism with the mglock file and it will work.

2. Send the following DSQL statement that will lock the entire database: (Select * from TABLE with(TabLOCK)).

3. Implement the lock in the application where you allow only one user to call to the specific task at a time.

Q12

Why am I getting a database error when using Time(0) column on MSSQL when trying to view or sort according to this specific field?

A12

Magic xpa does not support Time(N) fields, except TIME(7) which is equivalent to TIME. To view or to sort these fields with no error you need to make sure that the data that is fetched from the SQL server will be TIME(7).

To do this, the DB column name column property in Magic xpa should be converted to the relevant column. For example, if there is a TIME(0) field in MSSQL and its name is myTime, the DB column name should be replaced from myTime to CONVERT(TIME(7),myTime). This way, the converted column will be computed and not a real column.

Make sure you don’t convert the data source from Magic xpa or it will effect the database table as well. This will solve the view part. If you need to update the field you will need to add another column that will reference the real field and display it only when there is a need to insert data and not just display it.

ODBC

Q13

I am encountering problems with the ODBC gateway. What can I do?

A13

Use the following layered approach to solve problems you may encounter when using the Database Gateway for ODBC.

1. Test the ODBC driver.

2. If you can access the data successfully but the gateway is still not functioning correctly, test the ODBC driver itself, using a tool such as Microsoft Query or ODBC Test.

3. If the data can be accessed through those tools, then the ODBC driver is correctly installed.

You can also use the ODBC Check Driver Utility provided by Magic xpa.

Q14

Why can't I connect to a PostgreSQL database from a Magic xpa server on Linux using an ODBC gateway?

A14

ODBC connectivity to the Linux server requires the configuration of the odbcinst.ini and odbc.ini files with the relevant ODBC driver. This driver can be obtained either from the original database supplier or any other third party.

The Magic xpa requirement is to first test the ODBC connection outside of Magic xpa using the following tool:

isql -v DSN_PostgreSQL

It is possible that the isql utility can work correctly, but that the same configuration can still fail to connect from Magic xpa.

For example:

File odbcinst.ini

[PostgreSQL]

Description = ODBC for PostgreSQL

Driver = /usr/lib/psqlodbc.so

#Driver = /usr/lib/libodbcpsql.so

Setup = /usr/lib/libodbcpsqlS.so

File odbc.ini

[DSN_PostgreSQL]

Description = PostgreSQL driver

Driver = PostgreSQL

#Driver = /usr/lib/libodbcpsql.so

Database = Test

Servername = localhost

UserName = postgresql_user_name

Password = user_password

Port = 5432

In the above example, DSN_PostgreSQL uses Driver PostgreSQL as defined in the odbcinst.ini file, and uses /usr/lib/psqlodbc.so as the driver file.

In this situation, you should use the /usr/lib/libodbcpsql.so driver instead of the /usr/lib/pssqlodbc.so driver.

The /usr/lib/libodbcpsql.so driver will allow you to connect to PostgreSQL using the isql utility and Magic xpa.

Oracle

Q15

Why am I getting the Oracle error “ORA-01465 Invalid hex number”?

A15

The error occurs when the Oracle table is open for update and one of the fields does not match the data type of the entered values (including default values).

This problem might occur when an old version of Magic is migrated to a new version of Magic xpa.

The error occurs because of differences in the Storage type property of the table. In some cases, there are differences in the Storage Type list sequence between very old Magic versions and Magic xpa.

For example: ZString and String have changed places in the list.

To solve this issue there are two stages:

1. Add a new entry to the database table by using the Get Definition option.

2. Depending on the number of tables with the problem, you can do one of the following:

a. For a single table, or just a few occurrences: Simply override the existing table with the new entry using the Get Definition option.

b. If the problem exists throughout the application, simply export the original migrated table entry and the new one and compare them. Then, export the Data repository and use the search & replace functionality to change the property in the entire repository with the property that appears in the new table entry (Stage 1).

Please verify that custom properties that were changed directly by the developer (such as default values) are still there after the Get Definition.

Q16

Why am I getting the Oracle error “ORA-12514: TNS”?

A16

The reasons that might cause this error:

1. The service name of the TNS names file is incorrect.

2. The service is not started; meaning that the Oracle Listener service of the database server is not started.

3. The firewall blocks port 1521, which is the default listener port.

Q17

When working with two Oracle database connections why am I getting the 01002 Fetch out of Sequence error?

A17

If the application is accessing two different database servers in a single Magic xpa transaction, when the transaction is opened, only the first connection is opened and the transaction is opened on it. A transaction is not opened for the second connection. If you try to lock a record on the second connection, you'll get the "Fetch Out of Sequence" error because Oracle cannot lock outside of a transaction's scope.

To solve this issue, define a table from the second database higher up in the task tree where the Magic xpa transaction started.

Q18

Why doesn’t incremental locate work when using an Oracle database?

A18

Check for the nls_lang parameter of the session and the parameters of the database; they should be identical.

You can get the parameter of the database using:

SELECT value FROM nls_database_parameters WHERE parameter=’NLS_LANGUAGE’

You can get the session parameter using this statement (from Magic xpa):

SELECT value FROM nls_session_parameters WHERE parameter=’NLS_LANGUAGE’

If the values aren’t the same you need to edit the nls_session_parameters using the NLS_LANG key value in the registry or in the environment variable.

MySQL

Q19

When working with MySQL and the Windows 2008 operating system, why am I getting error -130 in the mgerror.log file?

A19

This happens after a long time without an activity, when connecting to the server with a RIA application. When I try to connect to the server (before restarting the broker) I get the "MySQL server has gone away" error. It is impossible to connect to the application. The only way to connect properly is to restart the broker.

Solution: A MySQL database closes the connection after 8 hours by default. The interactive_timeout parameter is set to 28800 seconds by default. To solve the error, increase this value as required by the users and then restart the MySQL database.