Connecting to Sage from Java

Before you can connect your Java application to Sage by using the JDBC-ODBC Bridge, you need to configure a Sage ODBC driver data source. An ODBC data source stores the connection details for the target database (e.g. Sage) and the ODBC driver that is required to connect to it (e.g. the Sage ODBC driver).

  1. Start ODBC Administrator. To do this, in the Windows Run dialog box, type:
    %windir%\syswow64\odbcad32.exe
  2. In the ODBC Administrator, choose the System DSN tab, and then choose Add.
  3. In the Create New Data Source dialog box, choose Sage ODBC Driver, and then choose Finish.
  4. Complete the Sage ODBC Driver configuration dialog box.
  5. Download the Easysoft JDBC-ODBC Bridge. (Registration required.)
  6. Install and license the Easysoft JDBC-ODBC Bridge on the machine where you created the data source.

    For installation instructions, see the Easysoft JDBC-ODBC Bridge documentation.

Connecting to Sage from Java

  1. Copy EJOB.jar to a machine where you have a Java Development Kit installed.

    If the Easysoft JDBC-ODBC Bridge is already installed on this machine, skip this step.

    EJOB.jar is installed in the following location on the machine where you install the Easysoft JDBC-ODBC Bridge:

    <easysoft_install>\Jars

    The default location for <easysoft_install> is <drive>:\Program Files (x86)\Easysoft Limited\Easysoft JDBC-ODBC Bridge.

  2. Add this Java code to a file named ConnectToSage.java:
    import easysoft.sql.*;
    import java.sql.*;
    import java.util.Calendar;
    import java.util.Date;
    
    
    public class ConnectToSage {
    
      public static void main(String[] args) throws Exception {
    
            String[] szTableTypes;
            szTableTypes = new String[100];
    
            String[] szTableNames;
            szTableNames = new String[1000];
    
            int[]  szTypeCount;
            szTypeCount = new int[100];
    
            int intTableTypes;
    
        try {
    
          easysoft.sql.jobDriver driver = (easysoft.sql.jobDriver)Class.forName("easysoft.sql.jobDriver").newInstance();
    
          String jobUrl= "jdbc:easysoft://localhost/My32bitSageSystemDSN:logonuser=mywindowsuser:logonpassword=mywindowspassword";
          Connection con = DriverManager.getConnection(jobUrl, "mysageuser", "mysagepassword");
          System.out.println(" ");
          System.out.println("------------------ Method Summary ------------------");
          try {
              int i;
              boolean bl;
              String st;
    
              DatabaseMetaData dbMD = null;
              dbMD = con.getMetaData();
    
    
    //------------------------------------------------------------------------
              try {
                  ResultSet rt = dbMD.getTableTypes();
                  intTableTypes=0;
                  st="";
                  while (rt.next()) {
                      szTableTypes[intTableTypes]=rt.getString(1);
                      intTableTypes++;
                  }
                  for (i=0; i<intTableTypes; i++)
                  {
                      st=st + szTableTypes[i] + "; ";
                  }
                  System.out.println("Table types (" + intTableTypes + ") : "+ st );
    
                  // Go get the tables e.t.c.
    
                  try {
                      int x;
                      ResultSet rs = dbMD.getTables( null, null, "%", null );
                      while (rs.next()) {
                          for (x=0; x<intTableTypes; x++)
                          {
                              System.out.println("TABLE_CAT : " + rs.getString(1));
                              System.out.println("TABLE_SCHEM : " + rs.getString(2));
                              System.out.println("TABLE_NAME : " + rs.getString(3));
                              System.out.println("TABLE_TYPE : " + rs.getString(4));
                              System.out.println("REMARKS  : " + rs.getString(5));
     System.out.println("------------------------------------------------------");
                          }
                      }
                  } catch (Exception e) {
                      System.out.println("getTables error : " + e );
                  }
    
              } catch (Exception e) {
                  System.out.println("getTableTypes error : " + e );
              }
     //------------------------------------------------------------------------
          }  catch(Exception e) {
              System.out.println("db Meta Data exception: " + e);
              e.printStackTrace();
          }
        } catch(Exception e){
                System.out.println("Java code error : " + e);
        }
        }
    } 
  3. In a command window, add EJOB.jar to the Java class path. For example:
    set CLASSPATH="%CLASSPATH%;C:\Program Files (x86)\Easysoft Limited\Easysoft JDBC-ODBC Bridge\Jars\EJOB.jar"
  4. cd to the directory where ConnectToSage.java is located, and compile and run the Java file. For example:
    "C:\Program Files\Java\jdk1.8.0_144\bin\javac.exe" ConnectToSage.java
    java ConnectToSage