To create a Java class for download-only direct row handling

1. Create a class Called MobiLinkOrders .

   Type the following code in a text editor or development environment.

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

2. Declare a class-level DBConnectionContext instance.

   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.

3. Create your class constructor.

   Your class constructor sets your class-level DBConnectionContext instance.

   Type the following code in a text editor or development environment.

   public MobiLinkOrders( DBConnectionContext cc ) { _cc = cc; }

4. 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. The UploadedTableData getInserts method returns a result set for new order comments.

   Type the following code in a text editor or development environment.

   // 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 Statement st = con.createStatement(); st.executeQuery( "insert into [order_sheet$]" + "(order_id, comment_id, order_comment) VALUES (" + Integer.toString(_orderID) + ", " + Integer.toString(_commentID) + ", '" + _specialComments + "')"); st.close(); } con.close(); } catch(Exception ex) { System.err.print("Exception: "); System.err.println(ex.getMessage()); } insertResultSet.close(); }

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

      Type the following code in a text editor or development environment.

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

      Note

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

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

      Type the following code in a text editor or development environment.

      PreparedStatement download_upserts = download_td.getUpsertPreparedStatement();

   3. Set the download data for each row.

      The following example traverses through the order_central.xls worksheet and adds data to the MobiLink download.

      Type the following code in a text editor or development environment.

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

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

      Type the following code in a text editor or development environment.

      download_upserts.close();

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

7. Compile your class file.

   • Navigate to the directory containing your Java source files.
   • Compile MobiLinkOrders with references to the MobiLink server API library 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 "c:\Program Files\SQL Anywhere 11\java\mlscript.jar" MobiLinkOrders.java