Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

Any simple examples of using the MobiLink Java Direct Row API that just does something really easy like use the existing synchronization connection to the consolidated database to execute JDBC commands to upload/download data?

asked 28 Oct '10, 20:28

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118
accept rate: 37%


1) Create a file called Example.java with the following code

import ianywhere.ml.script.*;
import java.sql.*;
import java.util.*;
import java.io.*;

public class Example
{
    DBConnectionContext _conn_context;
    Connection _sync_connection;
    Timestamp _last;
    String _mluser;

public Example( DBConnectionContext cc ) throws SQLException
    {
        try {
            _conn_context = cc;
            _sync_connection = _conn_context.getConnection();
        } catch ( SQLException e ) {
            freeJDBCResources();
            throw e;
        } catch ( Error e ) {
            freeJDBCResources();
            throw e;
        }
    }

protected void finalize() throws SQLException, Throwable
    {
        super.finalize();
        freeJDBCResources();
    }

private void freeJDBCResources() throws SQLException
    {
        _conn_context = null;
        _sync_connection = null;
    }

public void endConnection() throws SQLException
    {
        freeJDBCResources();
    }

public void beginDownload( Timestamp last_download, String mlUser )
    {
        _last = last_download;
        _mluser = mlUser;
    }

public void handleUpload( UploadData ud ) throws SQLException, IOException
    {
        int i;

UploadedTableData AdminTable = ud.getUploadedTableByName("Admin");

handleUploadAdminInsert( AdminTable.getInserts() );
        handleUploadAdminUpdate( AdminTable.getUpdates() );
        handleUploadAdminDelete( AdminTable.getDeletes() );

}

public void handleUploadAdminInsert( java.sql.ResultSet ins ) throws SQLException, IOException
    {
        PreparedStatement ps1 = _sync_connection.prepareStatement( "insert into Admin values ( ?, ?, DEFAULT )" );
        while( ins.next() )
        {
            ps1.setLong( 1, ins.getLong(1) );
            ps1.setString( 2, ins.getString(2) );
            ps1.executeUpdate();
        }
    }

// For Updates use old row values in the where clause
    // as well to avoid conflicts
    // First one in wins in this setup

public void handleUploadAdminUpdate( UpdateResultSet upd ) throws SQLException, IOException
    {
        PreparedStatement ps4 = _sync_connection.prepareStatement( "update Admin set data = ? where admin_id = ? and data = ? " );
        while( upd.next() ) {
            upd.setNewRowValues();
            ps4.setString( 1, upd.getString(2) );
            ps4.setLong( 2, upd.getLong(1) );
            upd.setOldRowValues();
            ps4.setString( 3, upd.getString(2) );
            if( ps4.executeUpdate() == 0 )
            {
                System.out.println("Updated Row Ignored in table Admin because of conflict.");
                upd.setNewRowValues();
                System.out.println("  New Row Values : admin_id = " + upd.getLong(1) + ", data = " + upd.getString(2) );
                upd.setOldRowValues();
                System.out.println("  Old Row Values : admin_id = " + upd.getLong(1) + ", data = " + upd.getString(2) );
            }
        }
    }

public void handleUploadAdminDelete( java.sql.ResultSet del ) throws SQLException, IOException
    {
        PreparedStatement ps7 = _sync_connection.prepareStatement( "delete from Admin where admin_id = ?" );
        while( del.next() ) {
            ps7.setLong( 1, del.getLong(1) );
            if( ps7.executeUpdate() == 0 )
            {
                System.out.println("Deleted Row Ignored in table Admin.");
                System.out.println("  admin_id = " + del.getLong(1) );
            }
        }
    }

public void handleDownload() throws SQLException
    {
        DownloadData dd = _conn_context.getDownloadData();

DownloadTableData dtdAdmin = dd.getDownloadTableByName("Admin");

PreparedStatement downCurAdmin = dtdAdmin.getUpsertPreparedStatement();
        PreparedStatement dataAdmin = _sync_connection.prepareStatement( "select admin_id, data from Admin where last_modified >= ?" );
        dataAdmin.setTimestamp( 1, _last );
        ResultSet rsAdmin = dataAdmin.executeQuery();
        while( rsAdmin.next() ) {
            downCurAdmin.setLong( 1, rsAdmin.getLong(1) );
            downCurAdmin.setString( 2, rsAdmin.getString(2) );
            if( downCurAdmin.executeUpdate() == 0 ) {
                System.out.println("Download Row Filtered from table Admin.");
                System.out.println("  admin_id = " + rsAdmin.getLong(1) + ", data = " + rsAdmin.getString(2) );
            }
        }
        downCurAdmin.close();
        rsAdmin.close();

PreparedStatement downDelCurAdmin = dtdAdmin.getDeletePreparedStatement();
        PreparedStatement dataDelAdmin = _sync_connection.prepareStatement( "select admin_id from Admin_del where del_time >= ?" );
        dataDelAdmin.setTimestamp( 1, _last );
        ResultSet rsDelAdmin = dataDelAdmin.executeQuery();
        while( rsDelAdmin.next() ) {
            downDelCurAdmin.setLong( 1, rsDelAdmin.getLong(1) );
            if( downDelCurAdmin.executeUpdate() == 0 ) {
                System.out.println("Download Delete Row Filtered from table Admin.");
                System.out.println("  admin_id = " + rsDelAdmin.getLong(1) );
            }
        }
        downDelCurAdmin.close();
        rsDelAdmin.close();

}
}

2a) Find out the version of the JVM being used by your ML Server. Run "java -version" in the sub-directory of the SA install where the JVM is stored. I'm just being paranoid, but if you're going to run a v1.6.0_16 JVM, use a v1.6.0_16 java compiler too. For example :

[c:\sybase\asa1200\sun\jre160_x64\bin]
[0][1200][32] java -version
java version "1.6.0_16"
Java(TM) SE Runtime Environment (build 1.6.0_16-b01)
Java HotSpot(TM) 64-Bit Server VM (build 14.2-b01, mixed mode)

2b) Compile the java code, then place the generated Example.class file "somewhere important" (for example c:\mljava ).

javac -classpath "%SQLANY12%\java\mlscript.jar" Example.java
copy Example.class c:\mljava

3) Define the schema of your consolidate database :

-- 
-- Set some Database Options
--

set option public.Global_database_id = 0;

--
-- Create Base Table
--

create table Admin (
  admin_id  bigint default global autoincrement ( 1000000000000 ) primary key,
  data  varchar(64),
  last_modified  timestamp default timestamp
);

--
-- Tables and triggers to track deletes
--

create table Admin_del (
  pk bigint default autoincrement primary key,
  admin_id bigint,
  del_time timestamp default current timestamp
);

create trigger bd_admin before delete on Admin
referencing old as pr for each row
begin
  insert into Admin_del(admin_id) values (pr.admin_id);
end;

--
-- Upload and Download Scripts
--

call ml_add_java_connection_script( 'v1', 'handle_UploadData', 'Example.handleUpload' );
call ml_add_java_connection_script( 'v1', 'handle_DownloadData', 'Example.handleDownload' );
call ml_add_java_connection_script( 'v1', 'begin_download', 'Example.beginDownload' );

-- 
-- Resource Cleanup for Java Code
--

call ml_add_java_connection_script( 'v1', 'end_connection', 'Example.endConnection' );

--
-- Procedure to add test data
--

CREATE PROCEDURE rep_data(IN iter      INTEGER DEFAULT 1, 
                          IN do_commit INTEGER DEFAULT 0) 
BEGIN
  DECLARE loop_var INTEGER;

SET loop_var = 0;
  WHILE loop_var < iter LOOP
    INSERT INTO Admin(data) VALUES(UUIDTOSTR(NEWID()));
    SET loop_var = loop_var + 1;
    IF do_commit = 1 THEN
      COMMIT;
    END IF;
  END LOOP;
END;

-- 
-- Add some test data
--

call rep_data( 5 );
commit;

4) Start the MobiLink Server and point the JVM to the location of your class file

mlsrv12 -v+ -ot ml12.txt -c "dsn=cons_sa12" -sl java(-cp c:\mljava)

5) Create a remote database and synchronize away.

--
-- Set some Database Options
--

set option public.Global_database_id = 1;
set option public.delete_old_logs = 'ON';

--
-- Create Base Tables
--

create table Admin (
  admin_id  bigint default global autoincrement ( 1000000000000 ) primary key,
  data  varchar(64)
);

--
-- Procedure to add test data
--

create procedure DBA.rep_data(in iter integer default 1,
                              in do_commit integer default 0)
begin
  declare loop_var integer;

set loop_var=0;
  while loop_var < iter loop
    insert into Admin(data) values(STRTOUUID(NEWID()));
    set loop_var=loop_var+1;
    if do_commit = 1 then
      commit;
    end if
  end loop;
end;

--
-- Create Synchronization Definitions
--

CREATE PUBLICATION p1 ( TABLE Admin );
CREATE SYNCHRONIZATION USER "rem1";
CREATE SYNCHRONIZATION SUBSCRIPTION TO p1 FOR "rem1"
   TYPE 'TCPIP' ADDRESS 'host=localhost' OPTION SV='v1';

--
-- Add some test data
--

call rep_data( 2 );
commit;
permanent link

answered 28 Oct '10, 20:47

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118
accept rate: 37%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×371

question asked: 28 Oct '10, 20:28

question was seen: 2,193 times

last updated: 28 Oct '10, 20:47