Forum: Building VoltDB Clients

Post: VoltDB - Help need in writing Stored procedures

VoltDB - Help need in writing Stored procedures
pachiappan
Nov 27, 2015
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.
rmorgenstein
Nov 27, 2015
"CREATE PROCEDURE .. AS .." is for single-statement SQL. For anything more complicated, use Java. See https://docs.voltdb.com/UsingVoltDB/DesignProcAnatomy.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.
pachiappan
Nov 27, 2015
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 ?
rmorgenstein
Nov 27, 2015
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/wiki/Using-Eclipse-to-Develop-and-Debug-VoltDB-Stored-Procedures-and-Client-Applications for more information.
pachiappan
Feb 5, 2016
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 ?
pzhao
Feb 5, 2016
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
pachiappan
Feb 8, 2016
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.
pachiappan
Feb 15, 2016
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".
pzhao
Feb 15, 2016
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
pachiappan
Feb 16, 2016
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.
pzhao
Feb 16, 2016
pachiappan,
I'm glad I could help.
Peter Zhao