Lesson 4: Create a Java or .NET class for 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 a file called orderComments.txt.
SetDownload You use this method for the handle_DownloadData event. SetDownload uses the orderResponses.txt file to download responses to remote clients.
EndDownload You use this method for the end_download event. EndDownload frees memory resources.

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

To create a Java or .NET class for direct row handling

Create a class named MobiLinkOrders in Java or .NET.

For Java, use the following code:

import ianywhere.ml.script.*;
import java.io.*;
import java.sql.*;
    
public class MobiLinkOrders {

For .NET, use the following code:

using iAnywhere.MobiLink.Script;
using System.IO;
using System.Data;
using System.Text;

public class MobiLinkOrders {

Declare a class-level DBConnectionContext instance.

For Java, use the following code:

    // Class level DBConnectionContext
    DBConnectionContext _cc;

For .NET, use the following code:

    // Class level DBConnectionContext
    private DBConnectionContext _cc = null;

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

Declare objects used for file input and output.

For Java, declare a java.io.FileWriter and java.io.BufferedReader as follows:

    // Java objects for file i/o
    FileWriter my_writer;
    BufferedReader my_reader;

For .NET, declare a StreamWriter and StreamReader as follows:

    // Instances for file I/O
    private static StreamWriter my_writer = null;
    private static StreamReader my_reader = null;

Create your class constructor.

Your class constructor sets your class-level DBConnectionContext instance.

For Java, use the following code:

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

For .NET, use the following code:

    public MobiLinkOrders(DBConnectionContext cc) {
        _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 Lesson 6: Set up your MobiLink client database. The UploadedTableData getInserts method returns a result set for new order comments. The writeOrderComment method writes out each row in the result set to a text file.

For Java, use the following code:

    public void writeOrderComment( int _commentID, int _orderID, String _comments ) 
        throws IOException
    {
        if (my_writer == null)
            // A FileWriter for writing order comments
            my_writer = new FileWriter( "C:\\MLdirect\\orderComments.txt",true);
              
        // Write out the order comments to remoteOrderComments.txt
        my_writer.write(_commentID + "\t" + _orderID + "\t" + _comments);
        my_writer.write( "\n" );
        my_writer.flush();
    }
    
    //  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();
         
        while ( insertResultSet.next() ) 
        {
            // Get order comments
            int _commentID = insertResultSet.getInt("comment_id");
            int _orderID = insertResultSet.getInt("order_id");
            String _specialComments = insertResultSet.getString("order_comment");              
            if (_specialComments != null) {
                writeOrderComment(_commentID,_orderID,_specialComments);
            }
        }
        insertResultSet.close();
    }

For .NET, use the following code:

    public void WriteOrderComment(int comment_id,
        int order_id,
        string comments)
    {
        if (my_writer == null) {
            my_writer = new StreamWriter("c:\\MLdirect\\orderComments.txt");
        }
        my_writer.WriteLine("{0}\t{1}\t{2}", comment_id, order_id, comments);
        my_writer.Flush();
    }

    // Method for the handle_UploadData synchronization event.
    public void GetUpload(UploadData ut) {
        // Get UploadedTableData for remote table called OrderComments
        UploadedTableData order_comments_table_data =
            ut.GetUploadedTableByName("OrderComments");

        // Get inserts uploaded by the MobiLink client
        IDataReader new_comment_reader =
            order_comments_table_data.GetInserts();

        while (new_comment_reader.Read()) {
            // Columns are
            // 0 - "order_comment"
            // 1 - "comment_id"
            // 2 - "order_id"
            // You can look up these values using the DataTable returned by:
            // order_comments_table_data.GetSchemaTable() if the send 
            // column names option is turned on at the remote. 
            // In this example, you just use the known column order to
            // determine the column indexes

            // Only process this insert if the order_comment is not null
            if (!new_comment_reader.IsDBNull(2)) {
                int comment_id = new_comment_reader.GetInt32(0);
                int order_id = new_comment_reader.GetInt32(1);
                string comments = new_comment_reader.GetString(2);
                WriteOrderComment(comment_id, order_id, comments);
            }
        }
        // Always close the reader when you are done with it!
        new_comment_reader.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.

For Java, use the following code:

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

For .NET, use the following code:

    private const string read_file_path =
        "c:\\MLdirect\\orderResponses.txt";

    // Method for the handle_DownloadData synchronization event
    public void SetDownload() {
        if ((my_reader == null) && !File.Exists(read_file_path)) {
            System.Console.Out.Write("There is no file to read.");
            return;
        }
        DownloadTableData comments_for_download =
            _cc.GetDownloadData().GetDownloadTableByName("OrderComments");

Note

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

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

For Java, use the DownloadTableData getUpsertPreparedStatement method to return a java.sql.PreparedStatement instance as follows:

        PreparedStatement update_ps = download_td.getUpsertPreparedStatement();

For .NET, use the DownloadTableData GetUpsertCommand method as follows:

        // Add upserts to the set of operation that are going to be 
        // applied at the remote database
        IDbCommand comments_upsert =
            comments_for_download.GetUpsertCommand();

Set the download data for each row.

This code traverses through the orderResponses.txt and adds data to the MobiLink download.

For Java, use the following code:

        // A BufferedReader for reading in responses
        if (my_reader == null)
            my_reader = new BufferedReader(new FileReader( "c:\\MLdirect\\orderResponses.txt"));
          
        // Get the next line from orderResponses
        String commentLine;
        commentLine = my_reader.readLine();
          
        // Send comment responses down to clients
        while (commentLine != null) {
            // Get the next line from orderResponses.txt
            String[] response_details = commentLine.split("\t");
            
            if (response_details.length != 3) {
                System.err.println("Error reading from orderResponses.txt");
                System.err.println("Error setting direct row handling download");
                return;
            }
            int comment_id = Integer.parseInt(response_details[0]);
            int order_id = Integer.parseInt(response_details[1]);
            String updated_comment = response_details[2];
            
            // Set an order comment response in the MobiLink download
            update_ps.setInt(1, comment_id);
            update_ps.setInt(2, order_id);
            update_ps.setString(3, updated_comment);
            update_ps.executeUpdate();
               
            // Get next line
            commentLine = my_reader.readLine();
        }

For .NET, use the following code:

        if (my_reader == null) {
            my_reader = new StreamReader(read_file_path);
        }
        string comment_line;
        while ((comment_line = my_reader.ReadLine()) != null) {
            // Three values are on each line separated by '\t'
            string[] response_details = comment_line.Split('\t');
            if (response_details.Length != 3) {
                throw (new SynchronizationException(
                    "Error reading from orderResponses.txt"));
            }
            int comment_id = System.Int32.Parse(response_details[0]);
            int order_id = System.Int32.Parse(response_details[1]);
            string comments = response_details[2];

            // Parameters of the correct number and type have 
            // already been added so you just need to set the 
            // values of the IDataParameter
            ((IDataParameter)(comments_upsert.Parameters[0])).Value =
                comment_id;
            ((IDataParameter)(comments_upsert.Parameters[1])).Value =
                order_id;
            ((IDataParameter)(comments_upsert.Parameters[2])).Value =
                comments;
            // Add the upsert operation
            comments_upsert.ExecuteNonQuery();
        }
    }

Close the prepared statement used for adding insert or update operations to the download.

For Java, use the following code:

        update_ps.close();
    }

For .NET, you do not need to close the IDbCommand. The object is destroyed automatically at the end of the download.

Write the EndDownload method.

This method handles the end_download connection event and gives you an opportunity to free resources.

For Java, use the following code:

    public void EndDownload() 
        throws IOException
    {
        // Close i/o resources
        if (my_reader != null) my_reader.close();
        if (my_writer != null) my_writer.close();
    }
}

For .NET, use the following code:

    public void EndDownload()
    {
        if (my_writer != null) {
            my_writer.Close();
            my_writer = null;
        }
        if (my_reader != null) {
            my_reader.Close();
            my_reader = null;
        }
    }
}

Save your code.

For Java, save your code as MobiLinkOrders.java in your working directory c:\MLdirect.

For .NET, save your code as MobiLinkOrders.cs in your working directory c:\MLdirect.
See Complete MobiLinkOrders code listing (Java) or Complete MobiLinkOrders code listing (.NET) to verify the code.

Compile your class file.

Navigate to the directory containing your Java or .NET source files.

Compile MobiLinkOrders and refer to the MobiLink server API library for Java or .NET.

For Java, you need to reference mlscript.jar, located in install-dir\java.

For 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

For .NET, run the following command, replacing C:\Program Files\SQL Anywhere 12\ with your SQL Anywhere 12 directory:

csc /out:MobiLinkServerCode.dll /target:library /reference:"C:\Program Files\SQL Anywhere 12\Assembly\v2\iAnywhere.MobiLink.Script.dll" MobiLinkOrders.cs

Note

The example shown here does not ensure that Primary Key values are unique. See Maintaining unique primary keys.