Writing Functions and Stored Procedures

This topic shows you the steps required to write functions and stored procedures for use in your Splice Machine database.

Note that the processes for adding functions and stored procedures to your Splice Machine database are quite similar; however, there are some important differences, so we’ve separated them into their own sections below.

Writing a Function in Splice Machine

Follow the steps below to write a Splice Machine database function.

  1. Create a Java method

    Each function maps to a Java method. For example:

    package com.splicemachine.cs.function;
    
    public class Functions {
       public static int addNumbers(int val1, int val2) {
          return val1 + val2;
       }
    }
    
  2. Create the function in the database

    You can find the complete syntax for CREATE FUNCTION in the Splice Machine SQL Reference manual.

    Here’s a quick example of creating a function. In this example, com.splicemachine.cs.function is the package, Functions is the class name, and addNumbers is the method name:

    CREATE FUNCTION add(val1 int, val2 int)
        RETURNS integer
        LANGUAGE JAVA
        PARAMETER STYLE JAVA
        NO SQL
        EXTERNAL NAME 'com.splicemachine.cs.function.Functions.addNumbers';
    
  3. Store your compiled Jar file and update your CLASSPATH

    Follow the instructions in the Storing and Updating Functions and Stored Procedures topic in this section to:

    • store your Jar file
    • update the class path so that Splice Machine can find your code when the function is called.

    Invoke your function

    You can invoke functions just like you would call any built-in database function. For example, if you’re using the Splice Machine command line interface (CLI), and have created a function named add, you could use a statement like the following:

    SELECT add(1,2) FROM SYS.SYSTABLES;
    

Writing a Stored Procedure in Splice Machine

Follow the steps below to write a Splice Machine database stored procedure.

  1. Write your custom stored procedure:

    Here is a very simple stored procedure that uses JDBC:

    package org.splicetest.customprocs;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    
    /**
     * This class contains custom stored procedures that will be dynamically loaded into the Splice Machine
     * database with the SQLJ jar file loading system procedures.
     *
     * @author Splice Machine
     */
    
    
    public class CustomSpliceProcs {
     /**
      * Return the names for all tables in the database.
      *
      * @param rs    result set containing names of all the tables in teh database
      */
    
       public static void GET_TABLE_NAMES(ResultSet[] rs)
    	 throws SQLException
       {
    	Connection conn = DriverManager.getConnection("jdbc:default:connection");
    	PreparedStatement pstmt = conn.prepareStatement("select * from sys.systables");
    	rs[0] = pstmt.executeQuery();
    	conn.close();
       }
    }
    

    You can use any Java IDE or text edit to write your code.

    You can find additional examples in the Functions and Stored Procedure Examples topic in this section.

    See the information about working with ResultSets in the next section.

  2. Compile your code and build a Jar file

    You now need to compile your stored procedure and build a jar file for it.

    You can use any Java IDE or build tool, such as Maven or Ant, to accomplish this. Alternatively, you can use the javac Java compiler and the Java Archive tool packaged with the JDK.

  3. Copy the Jar file to a cluster node

    Next, copy your custom Jar file to a region server (any node running an HBase region server) in your Splice Machine cluster. You can copy the file anywhere that allows the splice> interface to access it.

    You can use any remote copying tool, such as scp or ftp. For example:

    scp custom-splice-procs-1.0.2-SNAPSHOT.jar splice@myServer:myDir
    

    See the Storing and Updating Functions and Stored Procedures topic in this section for more information.

  4. Deploy the Jar file to your cluster

    Deploying the Jar file requires you to install the file in your database, and to add it to your database’s CLASSPATH. You can accomplish both of these steps by calling built-in system procedures from the splice> command line interpreter. For example:

    CALL SQLJ.INSTALL_JAR(
      '/Users/splice/my-directory-for-jar-files/custom-splice-procs-2.7-SNAPSHOT.jar',
      'SPLICE.CUSTOM_SPLICE_PROCS_JAR', 0);
    
    CALL SYSCS_UTIL.SYSCS_SET_GLOBAL_DATABASE_PROPERTY(
    'derby.database.classpath', 'SPLICE.CUSTOM_SPLICE_PROCS_JAR');
    

    The  SQLJ.INSTALL_JAR system procedure uploads the jar file from the local file system where splice> is executing into the HDFS:

    • If you are running a cluster, the Jar files are stored under the /hbase/splicedb/jar directory in HDFS (or MapR-FS).
    • If you are running in standalone mode, the Jar files are stored on the local file system under the splicedb/jar directory in the Splice install directory.
  5. Register your stored procedure with Splice Machine

    Register your stored procedure with the database by calling the  CREATE PROCEDURE statement. For example:

    CREATE PROCEDURE SPLICE.GET_TABLE_NAMES()
       PARAMETER STYLE JAVA
       READS SQL DATA
       LANGUAGE JAVA
       DYNAMIC RESULT SETS 1
       EXTERNAL NAME 'org.splicetest.customprocs.CustomSpliceProcs.GET_TABLE_NAMES';
    

    Note that after running the above CREATE PROCEDURE statement, your procedure will show up in the list of available procedures when you run the Splice Machine show procedures command.

    You can find the complete syntax for  CREATE PROCEDURE in the Splice Machine SQL Reference manual.

  6. Run your stored procedure

    You can run your stored procedure by calling it from the splice> prompt. For example:

    splice> call SPLICE.GET_TABLE_NAMES();
    
  7. Updating/Reloading your stored procedure

    If you make changes to your procedure’s code, you need to create a new Jar file and reload that into your databaseby calling the SQLJ.REPLACE_JAR system procedure:

    CALL SQLJ.REPLACE_JAR(
      '/Users/splice/my-directory-for-jar-files/custom-splice-procs-2.7-SNAPSHOT.jar',
      'SPLICE.CUSTOM_SPLICE_PROCS_JAR');
    

Working with ResultSets

Splice Machine follows the SQL-J part 1 standard for returning ResultSets through Java procedures. Each ResultSet is returned through one of the parameters passed to the java method. For example, the resultSet parameter in the MY_TEST_PROC method in our ExampleStoredProcedure class:

public class ExampleStoredProcedure {
   public static void MY_TEST_PROC(String myInput, ResultSet[] resultSet) throws SQLException {
     ...
   }
}

Here are a set of things you should know about ResultSets[] in stored procedures:

  • The ResultSets are returned in the order in which they were created.
  • The ResultSets must be open and generated from the jdbc:default:connection default connection. Any other ResultSets are ignored.
  • If you close the statement that created the ResultSet within the procedure’s method, that closes the ResultSet you want. Instead, you can close the connection.
  • The Splice Machine database engine itself creates the one element ResultSet arrays that hold the returned ResultSets.
  • Although the  CREATE PROCEDURE call allows you to specify the number of DYNAMIC RESULT SETs, we currently only support returning a single ResultSet.