No announcement yet.

VoltDB - Help need in writing Stored procedures

  • Filter
  • Time
  • Show
Clear All
new posts

  • VoltDB - Help need in writing Stored procedures


    I'm new to VoltDB But i have worked a bit on MySQL. Now I'm interested in writing stored procedures in VoltDB.

    I couldn't find any good documentation to write extensive stored procedures in VoltDB.

    I created a below table successfully and tried a sample procedure and ended up having errors which i couldn't resolve,

    371> CREATE PROCEDURE crtsampleproc @Limit INT
    372> AS
    373> DECLARE @intFlag INT
    374> SET @intFlag = 1
    375> WHILE (@intFlag <= Limit)
    376> BEGIN
    377> INSERT INTO table1 (table1id, table1name, table1name2, recordtmstmp) values(@intFlag, CONCAT('firstname', @intFlag), CONCAT('table1name2_', @intFlag), CURRENT_TIMESTAMP)
    378> SET @intFlag = @intFlag + 1
    379> END
    380> GO
    381> ;
    [Ad Hoc DDL Input]: VoltDB DDL Error: "Invalid CREATE PROCEDURE statement: "CREATE PROCEDURE crtsampleproc @Limit INT AS DECLARE @intFlag INT SET @intFlag = 1 WHILE (@intFlag <= Limit) BEGIN INSERT INTO table1 (table1id, table1name, table1name2, recordtmstmp) values(@intFlag, CONCAT('firstname', @intFlag), CONCAT('table1name2_', @intFlag), CURRENT_TIMESTAMP) SET @intFlag = @intFlag + 1 END GO", expected syntax: "CREATE PROCEDURE [ALLOW <role> [, <role> ...] FROM CLASS <class-name>" or: "CREATE PROCEDURE <name> [ALLOW <role> [, <role> ...] AS <single-select-or-dml-statement>" or: "CREATE PROCEDURE <proc-name> [ALLOW <role> ...] AS ### <code-block> ### LANGUAGE GROOVY""

    Can someone provide me the sample stored procedure which adheres to the following requirements,

    1) It should take different data types of arguments
    2) It should contain declaration of variables & assign values to them
    3) It should contain while loop
    4) It should return some output values
    5) It should have exception handling

    Please do the needful.

  • #2
    "CREATE PROCEDURE .. AS .." is for single-statement SQL. For anything more complicated, use Java. See This covers all the requirements you listed. In addition to the docs, you can see many types of procedures in our examples. examples/voter is a good starting place.


    • #3
      Thanks for the pointer. I need to develop my client using C++. Before i write my c++ code, i want to test the stored procedures separately that's why i requested for standalone procedures with the requirements i mentioned already.

      Can you please provide me some more pointers for standalone stored procedures development ?


      • #4
        After you develop your Java stored procedures and load them into VoltDB, but before you start writing you C++ client code, you can run them in sqlcmd or in VMC with the 'exec' command and test inputs.

        For example, with the example/voter code, to run a vote by hand you can type:
        exec Vote "7815551212" "MA" 1;

        You can, of course script this to try your various code paths. See for more information.

        Or you can run voltdb in eclipse and debug your Java stored procs there. See for more information.


        • #5
          Thanks rmorgenstein!. Sorry for the delayed reply. i had park the work on VoltDB due to some professional commitments then.

          Please clarify the below questions,
          1) Is Java Programming the only way to write complex Stored Procedures?
          2) Can i use SQL constructs in a sql file to create complex stored procedures ?


          • #6
            I'd like to assist you in this matter.
            1) Currently, Java is the only way to write complex stored procedures. I'd like to direct you to the docs regarding Java Stored procedures.
            2) We have simple buildin stored procedures as well as defining your own stored procedure. You'll need to assess if whether you can just define it in a sql file.
            Peter Zhao


            • #7
              Thanks for the pointer, Peter !

              I posed those questions since i'm familiar with only C/C++. Java is something new and started learning just now.
              I will take couple of days to go through the documentation and let you know my question if any.


              • #8
                Hi Peter,
                By going through the given documentation, i could write a small stored procedure & loaded it successfully. But the cmd "show classes" doesn't show it.

                Here are the details for you,

                Step1 - Written Stored Procedure

                [root@myhost]# cat
                import org.voltdb.*;

                public class myproc extends VoltProcedure
                public final SQLStmt SecId = new SQLStmt("SELECT uid, lpfield FROM sector WHERE part1 = ? AND part2 = ?;");

                public long run( String inPart1, String inPart2 ) throws VoltAbortException
                long vPart1 = 0;
                long vPart2 = 0;

                voltQueueSQL( SecId, inPart1, inPart2 );
                VoltTable[] Res1 = voltExecuteSQL();

                if( Res1[0].getRowCount() < 1)
                return -1;

                vPart1 = Res1[0].fetchRow(0).getLong(0);
                vPart2 = Res1[0].fetchRow(0).getLong(1);

                return vPart1;

                Step2 - Compiled the Java Code & Created Jar File

                [root@myhost]# javac -cp "$CLASSPATH:/MyExp/6VoltDB/voltdb/voltdb/*"
                [root@myhost]# jar cvf myproc.jar *.java
                added manifest
                adding: = 546) (out= 320)(deflated 41%)

                Step3 - Loaded the Jar

                [root@myhost]# sqlcmd
                SQL Command :: localhost:21212
                1> load classes myproc.jar;
                Command succeeded.

                Step4 - Display Loaded Classes

                2> show classes;

                --- Empty Class List -----------------------


                Since I couldn't see the loaded classes, i couldn't proceed further.

                Please point my mistakes and tell me how to invoke the stored procedure from "sqlcmd".


                • #9
                  It appears you've placed the java files into the jar. You'll need to place the compiled java files, *.class, into your jar file, myproc.jar.
                  Please take a look at 5.3.1. Compiling, Packaging, and Loading Stored Procedures which explains how to get your stored procedures compiled and loaded into VoltDB.
                  Peter Zhao


                  • #10
                    Peter, Thanks for pointing the blunder i did. I'm able to run the procedure and see the results now.
                    Also, i'm gonna write complex stored procedures now. Will post again if i run into any problem again.


                    • #11
                      I'm glad I could help.
                      Peter Zhao