MobiLink Synchronization User's Guide
  Writing Synchronization Scripts in Java
    Java synchronization example

Create your Java synchronization script

Setup 

The following sets up the Java synchronization logic. The import statements tell the Java virtual machine the location of needed files. The public class statement declares the class.

// use a package when you create your own script
import   ianywhere.ml.script.InOutInteger;
import   ianywhere.ml.script.DBConnectionContext;
import   ianywhere.ml.script.ServerContext;
import   java.sql.*;
public class CustEmpScripts {
/* Context for this synchronization connection.
*/
    DBConnectionContext _conn_context;
/* Same connection MobiLink uses for sync we can't commit or
 * close this.
*/
    Connection       _sync_connection;
    Connection       _audit_connection;
/* Get a user id given the user name. On audit connection.
*/
    PreparedStatement      _get_user_id_pstmt;
/* Add record of user logins added. On audit connection.
*/
    PreparedStatement      _insert_login_pstmt;
/* Prepared statement to add a record to the audit table.
 * On audit connection.
*/
    PreparedStatement      _insert_audit_pstmt;

The CustEmpScripts constructor sets up all the prepared statements for the authenticateUser method. It sets up member data.

public CustEmpScripts( DBConnectionContext cc )
    throws SQLException
{
try
    {
    _conn_context = cc;
    _sync_connection = _conn_context.getConnection();

    ServerContext serv_context =
    _conn_context.getServerContext();
    _audit_connection = serv_context.makeConnection();

    // get the prep statements ready
    _get_user_id_pstmt =
    _audit_connection.prepareStatement(
        "select user_id from ml_user where name = ?"
        );
    _insert_login_pstmt =

   _audit_connection.prepareStatement(
        "insert into login_added( ml_user, add_time )
        " + " values( ?, { fn CONVERT( { fn NOW() },
        SQL_VARCHAR ) })"
        );
    _insert_audit_pstmt =
        _audit_connection.prepareStatement(
        "insert into login_audit( ml_user_id,
        audit_time, audit_action ) " +
        " values( ?, { fn CONVERT( { fn NOW() },
        SQL_VARCHAR ) }, ? ) "
        );
    } catch ( SQLException e ) {
        freeJDBCResources();
        throw e;
    } catch ( Error e ) {
        freeJDBCResources();
        throw e;
    }
}

The finalize method cleans up JDBC resources if end_connection is not called.

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

The freeJDBCResources method frees allocated memory and we close the audit connection. It is a housekeeping procedure.

private void freeJDBCResources()
        throws SQLException
    {
   if( _get_user_id_pstmt != null ) {
       _get_user_id_pstmt.close();
   }
   if( _insert_login_pstmt != null ) {
       _insert_login_pstmt.close();
   }
   if( _insert_audit_pstmt != null ) {
       _insert_audit_pstmt.close();
   }
   if( _audit_connection != null ) {
       _audit_connection.close();
   }
   _conn_context        = null;
   _sync_connection    = null;
   _audit_connection   = null;
   _get_user_id_pstmt  = null;
   _insert_login_pstmt = null;
   _insert_audit_pstmt = null;
    }

The endConnection method cleans up resources once the resources are not needed. This is also a housekeeping procedure.

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

The authenticateUser method below approves all user logins and logs user information to database tables. If the user is not in the ml_user table they are logged to login_added. If the user id is found in ml_user then they are logged to login_audit. In a real system we would not ignore the user_password but in order to keep this sample simple we approve all users. The procedure throws SQLException if any of the database operations we perform fail with an exception

public void authenticateUser( InOutInteger auth_status,
        String user_name )
    throws SQLException
{
        boolean new_user;
        int user_id;

   // get ml_user id
    _get_user_id_pstmt.setString( 1, user_name );
    ResultSet user_id_rs =
    _get_user_id_pstmt.executeQuery();
    new_user = !user_id_rs.next();
    if( !new_user ) {
        user_id = user_id_rs.getInt(1);
    } else {
        user_id = 0;
    }

   user_id_rs.close();
    user_id_rs = null;
    // in this tutorial always allow the login
    auth_status.setValue( 1000 );
    if( new_user ) {
        _insert_login_pstmt.setString( 1, user_name );
        _insert_login_pstmt.executeUpdate();
        java.lang.System.out.println( "user: " +
            user_name + " added. " );
    } else {
        _insert_audit_pstmt.setInt( 1, user_id );
        _insert_audit_pstmt.setString( 2, "LOGIN ALLOWED" );
        _insert_audit_pstmt.executeUpdate();
    }
    _audit_connection.commit();
    return;
}

The following methods use SQL code to act as cursors on the database tables. Since these are cursor scripts, they must return a SQL string.

public static String empUploadInsertStmt()
{
    return( "INSERT INTO emp(
       emp_id, emp_name) VALUES( ?, ?) " );
}

public static String empUploadDeleteStmt()
{
    return( "DELETE FROM emp WHERE emp_id = ?" );
}

public static String empUploadUpdateStmt()
{
    return( "UPDATE emp SET emp_name = ?
             WHERE emp_id = ? " );
}

public static String empDownloadCursor()
{
    return( "SELECT emp_id, emp_name FROM emp" );
}

public static String custUploadInsertStmt()
{
    return( "INSERT INTO cust(
        cust_id, emp_id, cust_name)
        VALUES ( ?, ?, ? ) " );
}

public static String custUploadDeleteStmt()
{
    return( "DELETE FROM cust WHERE cust_id = ? " );
}

public static String custUploadUpdateStmt()
{
    return( "UPDATE cust
            SET emp_id = ?, cust_name = ?
            WHERE cust_id = ? " );
}

public static String custDownloadCursor()
{
    return( "SELECT cust_id, emp_id, cust_name
             FROM cust" );
    }
}

This code would be compiled using the command

javac -cp %asany9%\java\mlscript.jar CustEmpScripts.jar

and we could run the MobiLink synchronization server with the location of CustEmpScripts.class in the classpath. Following is a partial command line:

dbmlsrv9 ... -sl java (-cp <class_location>)