Accessing Remote Microsoft Navision Data by using the Easysoft ODBC-ODBC Bridge and JDBC-ODBC Bridge

Contents

Introduction

Microsoft Navision is an enterprise resource planning (ERP) application that provides an ODBC driver to allow third party applications and reporting tools to access Navision data. As the Navision ODBC driver is only available on the Windows platform, a common use for Easysoft bridge products ( Easysoft ODBC-ODBC Bridge and Easysoft JDBC-ODBC Bridge) is to access the Navison ODBC driver from the more comprehensive range of platforms supported by the Easysoft client software. For example:

Navision Versions

The current version of Navision is 4. The previous version 3 of the software is still in wide use.

Navision version 4 is supplied with an ODBC driver that is described in the Navision documentation as NODBC. Navision 3, known as Navision Attain, was supplied with a driver called the CODBC driver. These two drivers have different behaviour, and where the differences are relevant to the information in this document, a flag will be shown to indicate which version of the ODBC driver the text refers to.

Establishing the Connection

One of the most common issues when configuring remote access to Navision is the restriction the driver places on applications using it. The driver will only function reliably if the application making the calls is a single threaded application. If a multi threaded application is used, the driver will:

To solve this problem, enable the "multi-process" option provided in both bridges. This option may be set in the product web administrator, and further details may be found in the Easysoft product documentation.

Server Ownership

The Navision ODBC driver seems to be unable to run with the ownership of the "Local System Account". This is the default owner of all Windows services, so if problems are encountered connecting via the Easysoft service to the Navision ODBC driver, the ownership of the process should be changed in Windows Services (accessible from Control Panel) to run as a local user with sufficient privileges.

User Authentication

If the Navision system is configured to use authentication, a valid user must be specified when connecting.

In the case of the Easysoft ODBC-ODBC Bridge, the user and password may be specified in the odbc.ini configuration file as the TARGETUSER and TARGETAUTH entries, or specified in the connection string in a call to SQLDriverConnect or in the user and password arguments to SQLConnect.

In the case of the Easysoft JDBC-ODBC Bridge, the user and password may be specified in the connection URL as the user= and password= parameters or via the optional parameter array at the call to Driver.connect().

For more details, consult the Easysoft product documentation.

Navision Licensing

As each connection to the Easysoft server from a remote application establishes a unique connection to the Navision driver, the Navision system must have sufficient licenses to allow the required number of concurrent connections. As the connection to the Navision driver is released when the connection is closed, these licenses will be available for reuse. If the application software that is connecting though the Easysoft server employs connection pooling techniques to improve performance, this may increase the total number of concurrent Navision licenses required.

Driver Restrictions

As both the ODBC drivers provided with Navision implement a subset of the ODBC 2 API, there are certain calls and operations that will fail. Often this is beyond our control, as it is due to restrictions in the driver. However in some cases Easysoft have provided a solution, either in the bridge software, or through the use of the Easysoft SQL Engine, which can function as a intermediate driver and provide the calling application with the missing API conformance. For details on the Easysoft SQL Engine consult the Easysoft SQL Engine documentation, or contact Easysoft support at support@easysoft.com for instructions and help with implementing a composite solution.

The common problems caused by the Navision driver restrictions are detailed below, and the suggested solution given.

Scrollable Result Sets

The Navision driver reports that it provides FORWARD_ONLY, STATIC and KEYSET cursors. However in all three cases, it only allows fetches in a forward direction. Reporting that it allows STATIC and KEYSET connections may cause applications to assume that fully scrollable cursor movement is available. This can lead to the application failing. As the driver does not provide the calls SQLFetchScroll or SQLExtendedFetch, the symptom of the restriction may be shown either at the creation of the result set, or during the later fetching of data.

In the case of the Easysoft JDBC-ODBC Bridge, attempting to create a TYPE_SCROLL_INSENSITIVE result set will result in the exception "Driver does not support this function" and if the result set is of type TYPE_SCROLL_SENSITIVE, the exception will be "ODBC Driver does not support the requested cursor type".

Whatever the cause of the problems, there are only two solutions, either alter the operation of the application to use FORWARD_ONLY cursors, or consult Easysoft for details on using the Easysoft SQL Engine to add the missing features to the Navision ODBC Driver.

The following diagram shows a composite solution that combines the Easysoft SQL Engine with the Easysoft JDBC-ODBC Bridge:

This composite solution combines the Easysoft SQL Engine with the Easysoft JDBC-ODBC Bridge to add missing features to the Navision ODBC Driver.

Connections Come Back with a Warning

It may be found that the connection to the database returns the message "The driver doesn’t support the version of ODBC behaviour that the application requested". This message is produced by the ODBC Driver Manager and it is an informational message that while the application requested ODBC 3 type behaviour from the driver, the driver is only able to provide ODBC 2 operations. As this is only a informational message, it may safely be ignored. For more details, consult the ODBC reference on the SQL_SUCCESS_WITH_INFO return code.

Arrays of Results and Parameters

The Navision ODBC driver does not provide support for row and parameter array operations. So if the application cannot be altered to remove this requirement, the Easysoft SQL Engine will again be needed to add this functionality.

Metadata Column Names

The Navision ODBC driver does not provide the expected names for some of the columns returned by the JDBC DataBaseMetaData calls. This normally does not cause a problem. However, Servoy (a cross-platform application development environment) will fail if the IS_NULLABLE column from the getColumns result set is incorrectly named. Easysoft can provide a solution to this problem in the form of a modified JDBC client JAR file. Contact Easysoft support for further details of this.

SQLMoreResults

V3: The driver does not support the SQLMoreResults function. Applications that require this function may fail. If you use a version of the Easysoft JDBC-ODBC Bridge earlier than 1.3.3 with applications that use this function (including several webservice platforms), contact Easysoft support for details about upgrading to a later version that avoids this problem.

Using Dates as Prepared Statement Input Parameters

V3: When testing the CODBC driver (Navision 2.50.00.1869) with the Easysoft JDBC-ODBC Bridge / Easysoft SQL Engine, we got the following error when using a java.sql.Date as an input parameter to a prepared statement.

java.sql.SQLException: [JOB] RPC Exception: null
at easysoft.rpc.Client.job_pset_one_go(Unknown Source)
at easysoft.sql.BPS.sendAndBindParametersInOneGo(Unknown Source)
at easysoft.sql.BPS.executeUpdate(Unknown Source)
at easysoft.sql.jobPreparedStatement2.executeUpdate(Unknown Source)

This error can be reproduced by running this sample code:

import easysoft.sql.*;

import java.sql.*;
import java.util.Calendar;
import java.util.Date;

public class Test {
  public static void main(String[] args) throws Exception {

    try {

      easysoft.sql.jobDriver driver = (easysoft.sql.jobDriver)Class.forName("easysoft.sql.jobDriver").newInstance();

      String jobUrl= "jdbc:easysoft://localhost/ES_ENGINE";
      Connection con = DriverManager.getConnection(jobUrl);

      System.out.println("!! Connected !!");

      Date date = Calendar.getInstance().getTime();
      String sql = "UPDATE DateTest SET DateCol=? WHERE KeyID='1'";
      PreparedStatement stmt = con.prepareStatement(sql);
      stmt.setDate(1, new java.sql.Date(date.getTime()));
      stmt.executeUpdate();

      con.close();
      System.out.println("!! Finished !!");

    } catch(Exception e) {
      System.out.println("exception: " + e);
      e.printStackTrace();
    }
  }
}

To work around this issue, set the statement parameter to a String value rather than a java.sql.Date. To do this, change the PreparedStatement object’s setter method from setDate to setString. The string value must be a valid ODBC date escape sequence:

{d 'value'}

For example, to apply this workaround to the sample code, change the stmt.setDate call to:

stmt.setString(1, "{d '2008-02-08'}");

Article Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)