Lesson 4: Creating a Java class using MobiLink direct row handling

In this lesson, you use direct row handling to process rows in the OrderComments table in your client database. You add the following methods for direct row handling:

  • GetUpload   You use this method for the handle_UploadData event. GetUpload writes uploaded comments to the excel worksheet order_central.xls.

  • SetDownload   You use this method for the handle_DownloadData event. SetDownload retrieves the data stored in the excel worksheet order_central.xls and sends it to remote clients.

The following procedure shows you how to create a Java class including your methods for processing. For a complete listing, see Complete MobiLinkOrders code listing (Java).

To create a Java class for download-only direct row handling

  1. Create a class Called MobiLinkOrders .

    Type the following code in a text editor or development environment.

    import ianywhere.ml.script.*;
    import java.io.*;
    import java.sql.*;
    
    public class MobiLinkOrders {
        // ...
    }
  2. Declare a class-level DBConnectionContext instance.

    DBConnectionContext _cc;

    The MobiLink server passes a DBConnectionContext instance to your class constructor. DBConnectionContext encapsulates information about the current connection with the MobiLink consolidated database.

  3. Create your class constructor.

    Your class constructor sets your class-level DBConnectionContext instance.

    Type the following code in a text editor or development environment.

    public MobiLinkOrders( DBConnectionContext cc ) {
        _cc = cc;
    }
  4. Write the GetUpload() method.

    The GetUpload method obtains an UploadedTableData class instance representing the OrderComments table. The OrderComments table contains special comments made by remote sales employees. You create this table in Lesson 6: Set up your MobiLink client. The UploadedTableData getInserts method returns a result set for new order comments.

    Type the following code in a text editor or development environment.

    //  method for the handle_UploadData synchronization event
    public void GetUpload( UploadData ut )
      throws SQLException, IOException { 
      //  get an UploadedTableData for OrderComments
      UploadedTableData orderCommentsTbl = ut.getUploadedTableByName("OrderComments");
     
      // get inserts uploaded by the MobiLink client
      ResultSet insertResultSet = orderCommentsTbl.getInserts();
      try {
        // connect to the excel worksheet through ODBC
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection con = DriverManager.getConnection( "jdbc:odbc:excel_datasource" ); 
      
        while( insertResultSet.next() ) {
        
          // get order comments
          int _commentID = insertResultSet.getInt("comment_id");
          int _orderID = insertResultSet.getInt("order_id");
          String _specialComments = insertResultSet.getString("order_comment");
          
          // execute an insert statement to add the order comment to the worksheet
          Statement st = con.createStatement();
          st.executeQuery( "insert into [order_sheet$]"
            + "(order_id, comment_id, order_comment) VALUES ("
            + Integer.toString(_orderID) + ", "
            + Integer.toString(_commentID) + ", '"
            + _specialComments + "')");
          
          st.close();
        }
        con.close();
      } catch(Exception ex) {
        System.err.print("Exception: ");
        System.err.println(ex.getMessage());
      }   
      insertResultSet.close();
    }
  5. Write the SetDownload method:

    1. Obtain a class instance representing the OrderComments table.

      Use the DBConnectionContext getDownloadData method to obtain a DownloadData instance. Use the DownloadData getDownloadTableByName method to return a DownloadTableData instance for the OrderComments table.

      Type the following code in a text editor or development environment.

      DownloadData download_d = _cc.getDownloadData();
      DownloadTableData download_td = download_d.getDownloadTableByName( "OrderComments" );
      Note

      You create this table on the remote database in Lesson 6: Set up your MobiLink client.

    2. Obtain a prepared statement or IDBCommand that allows you to add insert or update operations to the download.

      Use the DownloadTableData getUpsertPreparedStatement method to return a java.sql.PreparedStatement instance.

      Type the following code in a text editor or development environment.

      PreparedStatement download_upserts = download_td.getUpsertPreparedStatement();
    3. Set the download data for each row.

      The following example traverses through the order_central.xls worksheet and adds data to the MobiLink download.

      Type the following code in a text editor or development environment.

      try {
        // connect to the excel worksheet through ODBC
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection con = DriverManager.getConnection( "jdbc:odbc:excel_datasource" );
      
        // retrieve all the rows in the worksheet
        Statement st = con.createStatement();
        ResultSet Excel_rs = st.executeQuery( "select * from [order_sheet$]" );
      
        while (Excel_rs.next()) {
          // retrieve the row data
          int Excel_comment_id = Excel_rs.getInt(1);
          int Excel_order_id = Excel_rs.getInt(2);
          String Excel_comment = Excel_rs.getString(3);
      
          // add the Excel data to the MobiLink download.
          download_upserts.setInt( 1, Excel_comment_id );
          download_upserts.setInt( 2, Excel_order_id );
          download_upserts.setString( 3, Excel_comment ); 
          download_upserts.executeUpdate();
        }
        // close the excel result set, statement, and connection.
        Excel_rs.close();
        st.close();
        con.close();
      } catch(Exception ex) {
        System.err.print("Exception: ");
        System.err.println(ex.getMessage());
      }
    4. Close the prepared statement used for adding insert or update operations to the download.

      Type the following code in a text editor or development environment.

      download_upserts.close();
  6. Save your Java code as MobiLinkOrders.java in your working directory c:\MLobjexcel.

  7. Compile your class file.

    • Navigate to the directory containing your Java source files.
    • Compile MobiLinkOrders with references to the MobiLink server API library for Java.

      You need to reference mlscript.jar in the Java subdirectory of your SQL Anywhere installation. Run the following command to compile your Java class, replacing c:\Program Files\SQL Anywhere 11\ with your SQL Anywhere 11 directory:

      javac -classpath "c:\Program Files\SQL Anywhere 11\java\mlscript.jar" MobiLinkOrders.java
Further reading

For more information about synchronization logic, see Writing synchronization scripts in Java.

For more information about direct row handling, see Direct row handling.


Complete MobiLinkOrders code listing (Java)