Lesson 3: Write Java or .NET logic for processing 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. Set download uses the orderResponses.txt file to download responses to remote clients.

You also add the EndDownload method to handle the end_download event.

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 listing (Java) or Complete MobiLinkOrders listing (.NET).

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

  1. Create a class called MobiLinkOrders using Java or .NET.

    For Java, type the following code in a text editor or development environment.

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

    For .NET, use the following code:

    using iAnywhere.MobiLink.Script;
    using System.IO;
    using System.Data;
    using System.Text;
    
    public class MobiLinkOrders
    
    // to do...
  2. Declare a class-level DBConnectionContext instance.

    In Java:

    // class level DBConnectionContext
        DBConnectionContext _cc;

    In .NET:

    // 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.

  3. Create your class constructor.

    Your class constructor sets your class-level DBConnectionContext instance.

    For Java:

    public MobiLinkOrders( DBConnectionContext cc )
    {
     // set your class-level DBConnectionContext
     _cc = cc;
    }

    For .NET:

    public MobiLinkOrders( DBConnectionContext cc )
    {
     _cc = cc;
    }
  4. Declare objects used for file input and output.

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

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

    For .NET, declare a Stream Writer and Stream Reader:

    // instances for file I/O
    private static StreamWriter   my_writer = null;
    private static StreamReader   my_reader = null;
  5. Write the EndDownload method.

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

    For Java:

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

    For .NET:

    public void EndDownload()
    {
      if( my_writer != null ) {
         my_writer.Close();
         my_writer = null;
      }
  6. 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 5: Set up your MobiLink client. 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:

    //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();
    
       }
    
    // writes out comment details to file
    public void writeOrderComment( int _commentID, int _orderID, String _comments ) 
       throws IOException
        {
    
          // a FileWriter for writing order comments
          if(my_writer == null)
          {
            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();
         
        }

    In .NET:

    // 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 upload 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 the remote. 
         // in this example you just use the known column order to determine the column
         // indexes
    
         // only process this insert of 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();
        }
  7. 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.

      For Java:

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

      For .NET:

      DownloadTableData comments_for_download = 
          _cc.GetDownloadData().GetDownloadTableByName( "OrderComments" );
      Note

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

    2. 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.

      PreparedStatement update_ps = download_td.getUpsertPreparedStatement();

      For .NET, use the DownloadTableData GetUpsertCommand method:

      // add upserts to the set of operation that are going to be applied at the
      // remote database
      IDbCommand  comments_upsert = comments_for_download.GetUpsertCommand();
    3. Send down responses to remote clients.

      Create a text file called orderResponses.txt in c:\MLdirect. This file contains responses to comments. For example, orderResponses.txt can contain the following entries including tab-delimited values representing the comment_id, order_id, and order_comment.

      ...
      786  34  OK, we will ship promotional material.
      787  35  Yes, the product is going out of production.
      788  36  No, we can't increase your commission...
      ...
    4. Set the download data for each row.

      For Java, the following example traverses through the orderResponses.txt and adds data to the MobiLink download.

      For Java:

      //   a BufferedReader for reading in responses
      if (my_reader==null)
       my_reader = new BufferedReader(new FileReader( "c:\\MLdirect\\orderResponses.txt"));
            
      // send updated comments down to clients
      String commentLine;
            
      // read the first line from orderResponses.txt
      commentLine = my_reader.readLine();
            
         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[0]);
         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 from orderResponses.txt
               commentLine  = my_reader.readLine();
            }

      For .NET:

      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 IDataParameters
           ((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();
      }
    5. Close the prepared statement used for adding insert or update operations to the download.

      For Java:

      update_ps.close();

      For .NET, you do not need to close the IDBCommand. It is destroyed by MobiLink at the end of the download.

  8. Compile your class file.

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

      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 "%SQLANY11%\java\mlscript.jar" MobiLinkOrders.java

      For .NET, run the following command:

      csc /out:MobiLinkServerCode.dll /target:library /reference:"%SQLANY11%\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.

Further reading

For more information about class constructors and DBConnectionContext, see:

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

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

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


Complete MobiLinkOrders listing (Java)
Complete MobiLinkOrders listing (.NET)