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;
answered
28 Oct '10, 20:47
Reg Domaratzki
7.6k●3●43●115
accept rate:
37%