Lesson 5: Creating a Java class for MobiLink direct row handling

This lesson assumes you have completed all preceding lessons. See Lesson 1: Setting up an Excel worksheet.

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.xlsx.
SetDownload You use this method for the handle_DownloadData event. SetDownload retrieves the data stored in the excel worksheet order_central.xlsx 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).

Create a Java class for download-only direct row handling

Start writing a new class named MobiLinkOrders.

Write the following code:

import ianywhere.ml.script.*;
import java.io.*;
import java.sql.*;

public class MobiLinkOrders {

Declare a class-level DBConnectionContext instance.

Append the following code:
// Class level DBConnectionContext 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.

Create your class constructor.

Your class constructor sets your class-level DBConnectionContext instance.

Append the following code:

    public MobiLinkOrders( DBConnectionContext cc )
        throws IOException, FileNotFoundException {
        // Declare a class-level DBConnectionContext
        _cc = cc;
    }

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 a later lesson.

The UploadedTableData getInserts method returns a result set for new order comments.

Append the following code:

    //  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
                PreparedStatement st = con.prepareStatement("INSERT INTO [order_sheet$]"
                    + "(order_id, comment_id, order_comment) VALUES (?,?,?)" );
                st.setString( 1, Integer.toString(_orderID) );
                st.setString( 2, Integer.toString(_commentID) );
                st.setString( 3, _specialComments );
                st.executeUpdate();      
                st.close();
            }
            con.close();
        } catch(Exception ex) {
            System.err.print("Exception: ");
            System.err.println(ex.getMessage());
        } finally { 
	    insertResultSet.close();
	}
    }

Write the SetDownload method:

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.

Append the following code:

    public void SetDownload() throws SQLException, IOException {
        DownloadData download_d = _cc.getDownloadData();
        DownloadTableData download_td = download_d.getDownloadTableByName( "OrderComments" );

Note

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

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.

Append the following code:

        // Prepared statement to compile upserts (inserts or updates).
        PreparedStatement download_upserts = download_td.getUpsertPreparedStatement();

Set the download data for each row.

The following code traverses through the order_central.xlsx worksheet and adds data to the MobiLink download.

Append the following code:

        
        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());
        }

Close the prepared statement used for adding insert or update operations to the download, and end the method and the class.

Append the following code:

        finally {
	    download_upserts.close();
	}
    }
}

Save your Java code as MobiLinkOrders.java in your working directory c:\MLobjexcel.

To verify the code in MobiLinkOrders.java, see Complete MobiLinkOrders code listing (Java).

Compile your class file.

Navigate to the directory containing your Java source files.

Compile MobiLinkOrders that refer to the MobiLink server API library for Java.

You need to reference mlscript.jar, located in %SQLANY12%\Java.

Run the following command, replacing C:\Program Files\SQL Anywhere 12\ with your SQL Anywhere 12 directory:

javac -classpath "C:\Program Files\SQL Anywhere 12\java\mlscript.jar" MobiLinkOrders.java

Proceed to Lesson 6: Starting the MobiLink server.