Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 (Français) » MobiLink - Getting Started » MobiLink Tutorials » Tutorial: Introduction to direct row handling

 

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

  3. 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;
  4. 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;
        }
  5. 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();
        }
  6. 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, 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.

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

  7. 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;
            }
        }
    }
  8. 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.

  9. See Complete MobiLinkOrders code listing (Java) or Complete MobiLinkOrders code listing (.NET) to verify the code.

  10. Compile your class file.

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

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

 Further reading

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