2 Accessing remote Microsoft Navision data from Linux, UNIX, and Java

Accessing remote Microsoft Navision data from Linux, UNIX, and Java

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 other platforms. For example:

Navision versions

Navision version 4 includes an ODBC driver that's 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 this is described in our document.

Establishing the connection

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

To solve this problem, enable the multi-process option in the web administrator provided by both bridges. Refer to the Easysoft product documentation for further information about this.

Server ownership

The Navision ODBC driver seems to be unable to run under the ownership of the Local System Account. This is the default owner of all Windows services, so if you have problems with the Easysoft service and Navision ODBC driver, change the service to run a local user with sufficient privileges. Do this in Windows Services.

User authentication

If you turn on Navision authentication, a valid user must be specified when connecting.

For the Easysoft ODBC-ODBC Bridge, specify the user and password with the TARGETUSER and TARGETAUTH attributes in odbc.ini. Alternatively, specify them in the connection string with SQLDriverConnect or in the user and password arguments to SQLConnect.

For the Easysoft JDBC-ODBC Bridge, specify the user and password in the JDBC URL or by using the optional parameter array in a Driver.connect() call.

For more details, consult the Easysoft product documentation.

Navision licensing

Each connection a remote application makes to the Navision ODBC driver through an Easysoft bridge creates a unique driver connection. The Navision system therefore must have sufficient licenses for the required number of concurrent connections. As the connection to the Navision driver is released when you close the connection, these licenses are available for reuse. If the application that's connecting though the Easysoft server uses connection pooling, this may increase the total number of concurrent Navision licenses required.

Driver restrictions

The Navision ODBC drivers implement a subset of the ODBC 2 API. Certain calls and operations will fail therefore. Often this is beyond our control, however for some cases Easysoft provide a solution, either in the bridge software or the Easysoft SQL Engine, which can function as a intermediate driver and provide the calling application with the missing API conformance.

The common problems caused by the Navision driver restrictions are detailed below, along with suggested solutions.

Scrollable result sets

The Navision ODBC driver reports that it provides FORWARD_ONLY, STATIC, and KEYSET cursors. However, in all three cases, the driver 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 support SQLFetchScroll or SQLExtendedFetch, this restriction's symptoms may be shown either at result set creation time, or when fetching the data.

For the Easysoft JDBC-ODBC Bridge, attempts to create a TYPE_SCROLL_INSENSITIVE result set cause a "Driver does not support this function" exception. If the result set is TYPE_SCROLL_SENSITIVE, the exception is "ODBC Driver does not support the requested cursor type."

There are two solutions for this. Either change the application to use FORWARD_ONLY cursors, or contact Easysoft for information about using the Easysoft SQL Engine to add 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

You may get "The driver doesn't support the version of ODBC behaviour that the application requested" when attempting to connect to Navision. This ODBC Driver Manager message is saying that the application requested ODBC 3 behaviour from the driver, but the driver only supports ODBC 2. As this is an informational message, it may safely be ignored. For more information, refer to the ODBC reference on the SQL_SUCCESS_WITH_INFO return code.

Arrays of results and parameters

The Navision ODBC driver doesn't support row and parameter array operations. So, if your application can't be changed, the Easysoft SQL Engine is again needed to add this functionality.

Metadata column names

The Navision ODBC driver doesn't provide the expected names for some columns returned by JDBC DataBaseMetaData calls. This normally does not cause a problem. However, Servoy (a cross-platform application development environment) fails 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.

SQLMoreResults

Navision 3 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 web service platforms), upgrade to a later version.

Using dates as prepared statement input parameters

Navision 3 When testing the CODBC driver (Navision 2.50.00.1869) with the Easysoft JDBC-ODBC Bridge and 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, 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'}");