Chapter 9. OpenAPI Programming

Index:

  1. Extend SQL functions
  2. Create/Remove/Start/Stop Server Programmatically with/without GUI manager
  3. Customer Connection

Extend SQL functions

HXTT Access supports more than 250 SQL functions. Please email us if you wish to complement some new SQL functions. HXTT Access supports also user-defined SQL functions, and you should use only this feature to provide special SQL functions in your project.
First, you need to implement com.hxtt.sql.ExtendedFunctionInterface.

public interface ExtendedFunctionInterface {
    /**
     * Used to verify whether functionName is supported, and has a correct prarameter count.
     * @param functionName the name of function
     * @param parameters the parameter list of function,  which can be null
     * @return value
     * @throws SQLException if has an incorrect parameter number
     */
     public boolean isExtendedFunction(String functionName,Object[] parameters)throws SQLException;

    /**
     * Used to evaluate function value.
     * @param functionName the name of function
     * @param values the value list of function, which can be null
     * @return value
     * @throws SQLException if failed to calculate the function
     */
    public Object evaluate(String functionName,Object[] values)throws SQLException;

    /**
     * Used to get the SQL type of the value that is expected to be returned when evaluate() is called.
     * @param functionName
     * @return the SQL type or Types.NULL if functionName is supported
     */
    public int getType(String functionName);

    /**
     * Used to get the SQL types of the parameter values that are expected to be returned when evaluate() is called.
     * return null if function hasn't any parameter, or you wish to use the default SQL types.
     * use Types.NULL for that specific parameter if you wish to get the default SQL type.
     * @param functionName
     * @return the SQL type list or null if functionName is supported
     */
    public int[] getParameterTypes(String functionName);

    /**
     * Used to estimate the maximum number of characters that should be contained in a String returned by evaluate(String functionName,Object[] values).
     * Zero is returned if this value object does not represent Types.VARCHAR, Types.BINARY, Types.LONGVARCHAR, or Types.LONGBINARY.
     * @param functionName
     * @return maximum size
     * @throws SQLException  if functionName is supported
     */
    public int estimateValueSize(String functionName) throws SQLException;
}

Let us see a sample:

import com.hxtt.sql.ExtendedFunctionInterface;
import java.sql.SQLException;
import java.sql.Types;

/**
 * Show how to complement some sql functions.
 * This sample complements tostring(value) and random() for demo purpose
 */
public class Functions implements ExtendedFunctionInterface {
    public Functions() {
    }

    /**
     * Used to verify whether functionName is supported, and has a correct prarameter count.
     * @param functionName the name of function
     * @param parameters the parameter list of function,  which can be null
     * @return value
     * @throws SQLException if has an incorrect parameter number
     */
    public boolean isExtendedFunction(String functionName, Object[] parameters) throws SQLException {
        if (functionName.equalsIgnoreCase("tostring")) {
            if (parameters != null && parameters.length == 1) {
                return true;
            }
            else {
                throw new SQLException("Invalid parameter value in tostring function");
            }
        }
        else if (functionName.equalsIgnoreCase("random")) {
            if (parameters == null) {
                return true;
            }
            else {
                throw new SQLException("Invalid parameter value in random function");
            }
        }
        return false;
    }

    /**
     * Used to evaluate function value.
     * @param functionName the name of function
     * @param values the value list of function, which can be null
     * @return value
     * @throws SQLException if failed to calculate the function
     */
    public Object evaluate(String functionName, Object[] values) throws SQLException {
        if (functionName.equalsIgnoreCase("tostring")) {
            return values[0] + "";
        }
        else if (functionName.equalsIgnoreCase("random")) {
            return new Double(Math.random());
        }
        throw new SQLException("Inner Error:(");
    }

    /**
     * Used to get the SQL type of the value that is expected to be returned when evaluate() is called.
     * @param functionName
     * @return the SQL type or Types.NULL if functionName is supported
     */
    public int getType(String functionName) {
        if (functionName.equalsIgnoreCase("tostring")) {
            return Types.VARCHAR;
        }
        else if (functionName.equalsIgnoreCase("random")) {
            return Types.DOUBLE;
        }
        return Types.NULL;

    }

    /**
     * Used to get the SQL types of the parameter values that are expected to be returned when evaluate() is called.
     * return null if function hasn't any parameter, or you wish to use the default SQL types.
     * use Types.NULL for that specific parameter if you wish to get the default SQL type.
     * @param functionName
     * @return the SQL type list or null if functionName is supported
     */
    public int[] getParameterTypes(String functionName) {
        if (functionName.equalsIgnoreCase("tostring")) {
            return new int[] {
                Types.VARCHAR};
        }
        return null;
    }

    /**
     * Used to estimate the maximum number of characters that should be contained in a String returned by evaluate(String functionName,Object[] values).
     * Zero is returned if this value object does not represent Types.VARCHAR, Types.BINARY, Types.LONGVARCHAR, or Types.LONGBINARY.
     * @param functionName
     * @return maximum size
     * @throws SQLException  if functionName is supported
     */
    public int estimateValueSize(String functionName) throws SQLException {
        if (functionName.equalsIgnoreCase("tostring")) {
            return 20;
        }
        else if (functionName.equalsIgnoreCase("random")) {
            return 8;
        }
        return 10;

    }
}

Then you can use com.hxtt.sql.OpenAPI.registerExtendedFunction("Functions"); to regiester Functions class. Then you can use those user-defined functions in SQL. For instance, "select abs(random()),tostring(date) from test;".


Create/Remove/Start/Stop Server Programmatically

If you wish to create,remove,start a GUI server for remote connections from your application, you can call four functions of com.hxtt.sql.admin.Admin class:
public String createServer(String serverConfigName,String serverConfigURL,boolean serverAutoStart,boolean isServerLog,String serverLogFilePath) throws Exception
public void removeServer(String serverName)
public void startServer(String serverName)throws SQLException
public void stopServer(String serverName)throws SQLException

For instance:
        try {
            com.hxtt.sql.admin.Admin admin = new com.hxtt.sql.admin.Admin();

            admin.show();//It can be invisible too.
						
              String createResult = admin.createServer("test1","jdbc:access://192.168.1.1:1027/mnt/accessfiles",true,true,"/tmp/test1.log");
                    			
            if (createResult!=null)
               System.out.println("Failure to create this server for " + createResult);
            			
            admin.startServer("test1");

            admin.stopServer("test1");

            admin.stopServer("test4");
            
            admin.removeServer("test1");

        }
        catch (SQLException e) {
            System.out.println(e.getMessage());
        }

On LINUX and UNIX, if you got "Cannot connect to X11 window server. The environment variable DISPLAY is not set.", you should use -Djava.awt.headless=true to run Java in headless mode. On OS/400, if you got still a java.awt.HeadlessException thrown with -Djava.awt.headless=true, you should read Run HXTT AccessServer as Windows Service or Linux(Solaris) Daemon to consider running directly com.hxtt.sql.admin.HxttService. If you wish Create/Remove/Start/Stop Server Programmatically without GUI or invisible GUI, call four same functions of com.hxtt.sql.admin.HxttService class:
public String createServer(String serverConfigName,String serverConfigURL,boolean serverAutoStart,boolean isServerLog,String serverLogFilePath) throws Exception
public void removeServer(String serverName)
public void startServer(String serverName)throws SQLException
public void stopServer(String serverName)throws SQLException

For instance:
        try {
            com.hxtt.sql.admin.HxttService admin = new com.hxtt.sql.admin.HxttService();

              String createResult = admin.createServer("test1","jdbc:access://192.168.1.1:1027/mnt/accessfiles",true,true,"/tmp/test1.log");
					
                    			
            if (createResult!=null)
               System.out.println("Failure to create this server for " + createResult);
            			
            admin.startServer("test1");

            admin.stopServer("test1");

            admin.stopServer("test4");
            
            admin.removeServer("test1");

        }
        catch (SQLException e) {
            System.out.println(e.getMessage());
        }


Customer Connection

First, let us know the relation of TCP/IP connection and java.sql.Connection. java.sql.Connection objects can share TCP/IP connection. The max number of alive TCP/IP connections between one client and one server is 20, but maybe more than 1000 alive java.sql.Connection objects are using those 20 TCP/IP connections. One java.sql.Connection object maybe build 0, 1, or more than one TCP/IP connections too.
If you haven't read SSL Connection, please read.
To construct your customer connection, you need to provides two override class(Socket and ServerSocket). For Socket, you should have one construction method( public YourSocket(String host, int port)throws IOException). For ServerSocket, you should have one construction method( public YourServerSocket(int port, int backlog, InetAddress bindAddr) throws IOException). Then you can use:
java -Dhxtt.socketclass=yourPackage.YourServerSocket -cp yourClassPath com.hxtt.sql.admin.Admin
Or
java -Dhxtt.socketclass=yourPackage.YourSocket -cp yourClassPath com.hxtt.sql.admin.Admin
Or
java -Dhxtt.socketclass=yourPackage.YourServerSocket -cp yourClassPath yourApplication
Or
java -Dhxtt.socketclass=yourPackage.YourSocket -cp yourClassPath yourApplication
hxtt.socketclass can be used for client connection property too. The class name should be yourPackage.*Socket* and yourPackage.*ServerSocket* so that HXTT AccessServer can guess the other class name according to one class name.


Copyright © 2003-2019 Heng Xing Tian Tai Lab | All Rights Reserved. |