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