Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VoltDB - Help need in writing Stored procedures

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    6

    Smile VoltDB - Help need in writing Stored procedures

    Hi,

    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. #2
    Super Moderator
    Join Date
    Sep 2011
    Posts
    90
    "CREATE PROCEDURE .. AS .." is for single-statement SQL. For anything more complicated, use Java. See https://docs.voltdb.com/UsingVoltDB/...rocAnatomy.php. 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. #3
    New Member
    Join Date
    Nov 2015
    Posts
    6
    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. #4
    Super Moderator
    Join Date
    Sep 2011
    Posts
    90
    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 https://docs.voltdb.com/UsingVoltDB/clisqlcmd.php for more information.

    Or you can run voltdb in eclipse and debug your Java stored procs there. See https://github.com/VoltDB/voltdb/wik...t-Applications for more information.

  5. #5
    New Member
    Join Date
    Nov 2015
    Posts
    6
    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. #6
    Senior Member
    Join Date
    Apr 2014
    Posts
    152
    pachiappan,
    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. #7
    New Member
    Join Date
    Nov 2015
    Posts
    6
    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. #8
    New Member
    Join Date
    Nov 2015
    Posts
    6
    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 myproc.java
    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/*" myproc.java
    [root@myhost]#
    [root@myhost]# jar cvf myproc.jar *.java
    added manifest
    adding: myproc.java(in = 546) (out= 320)(deflated 41%)
    [root@myhost]#

    Step3 - Loaded the Jar
    ***********************

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

    Step4 - Display Loaded Classes
    *******************************

    2> show classes;

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


    3>

    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. #9
    Senior Member
    Join Date
    Apr 2014
    Posts
    152
    pachiappan,
    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. #10
    New Member
    Join Date
    Nov 2015
    Posts
    6
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •