⬅ Forum: Managing VoltDB

Post: How to store the data executed after voltExecuteSQL() in new VoltTable into VoltDB via Java stored procedure?

How to store the data executed after voltExecuteSQL() in new VoltTable into VoltDB via Java stored procedure?

pavangadiya

Oct 22, 2015

Hello All,

I am writing a java stored procedure. In which I have to store the data in the new VoltTable(Physical VoltTable) into the VoltDB executed after the voltExecuteSQL() method. (Well, I know voltExecuteSQL() execute the statements in the queue & returns its results in a VoltTable structure. )

But, VoltDB java stored procedure does not support the "CREATE TABLE" statements inside the stored procedure itself.

So, I want to store the data in the new physical VoltTable into the VoltDB (In short, storing the data into the VoltDB while returning voltExecuteSQL() method)

So, please suggest me how can I do that ?

Thank You!

Best Regards,
Pavan

bballard

Oct 22, 2015

Hi Pavan,

VoltTable is a java object, which has no persistence. You can certainly use a "while (t.advanceRow()) {...}" loop in a java stored procedure to iterate through the rows of a VoltTable and call voltQueueSQL() to insert into other tables. You could also consider using the "INSERT INTO ... SELECT ..." SQL syntax to directly insert the results of a query. If you create the destination table ahead of time in the schema, you should be able to use either of these techniques.

It's true you cannot call CREATE TABLE within a stored procedure. No DDL statements are allowed within procedures, and all DML statements must be compile-time constants (not dynamically changing which tables are being queried, or dynamically building the WHERE clause, for example).

Best regards,
Ben