Forum: Building VoltDB Clients

Post: Do you need to compile and run a stored procedure to query a volt database?

Do you need to compile and run a stored procedure to query a volt database?
akh2103
Oct 10, 2013
I am learning volt. I see from this tutorial that you can query a voltdb from a sql prompt: http://voltdb.com/docs/GettingStarted/HWtest.php

I also see from the tutorial that you can write and compile java stored procedures and clients for Volt : http://voltdb.com/docs/GettingStarted/HWinsert.php

Do you have to query volt by writing and compiling a java client and java stored procedures? Is it possible to write a SQL query on the fly and then pass it to Volt? I essentially want to write a sql statement in the shell and query volt that way? So basically is there a Volt analog to this in mysql

prompt$mysql -u vivek -p -e 'select count (*) from mytable'

If so, what are the kinds of performance costs to querying Volt from the shell as opposed to writing a java client and qerying volt in that way?
jpiekos
Oct 11, 2013
VoltDB absolutely has a sql prompt, we call it "sqlcmd". Check out our recent developer "recipe" on how to use it: http://voltdb.com/dev-center/cookbook/sqlcmd

For a summary of various methods to execute SQL (interactive/ad hoc, and stored procedures), please see this blog post: http://voltdb.com/programming-voltdb-easy-flexible-and-ultra-fast/

And of course, Volt University (https://voltdb.com/resources/volt-university/) and our getting started documentation (https://voltdb.com/docs/GettingStarted/) can help as well.
akh2103
Oct 12, 2013
Thanks so much. Using your links I was able to run sql from the shell command line like this:
memsql@memsql-virtual-machine:~$ sqlcmd --output-format=csv < myscript.sql

Can I use this method to execute a stored procedure from the linux command prompt in volt? I want to do something like:

myscript.sql = callProcedure("Select", "Spanish")

memsql@memsql-virtual-machine:~$ sqlcmd --output-format=csv < myscript.sql //runs the select stored proc form command line
bballard
Oct 12, 2013
You can use STDIN/STDOUT with sqlcmd, so there are various command line techniques that can work:

echo "exec Select 'Spanish';" | sqlcmd --output-format=csv > output.csv

sqlcmd --output-format=csv < myscript.sql > output.csv

There is an issue in your myscript.sql: "callProcedure()" is a method of the Client object that you would use within a java client application. To call a stored procedure in sqlcmd, you would use "exec <procedurename> <space-delimited parameters>;"
darla
Sep 20, 2014
Yes, it will be much more efficient to use the C++ client library than the JSON interface. The C++ client library uses a binary protocol to talk to the server, whereas the JSON interface has to go through an additional Jetty web server embedded in the VoltDB server process.