Forum: Building VoltDB Clients

Post: How do you create a sql query of dynamic fields?

How do you create a sql query of dynamic fields?
runescape1
Dec 8, 2011
I want to create a sql query of dynamic fields that are decided at runtime, such as:
SELECT some, random, field FROM table WHERE id = ?
Because there is a restriction that you must declare an instance variable SQLStmt:
public final SQLStmt sql = new SQLStmt("SELECT field0, field1 FROM table WHERE id = ?");
all fields are hard-coded or else VoltDB will not compile, I cannot set the fields I want to read.
How do you create a sql query of dynamic fields?
VoltDB doesn't support the
rbetts
Dec 8, 2011
VoltDB doesn't support the random row query you want here. You have to declare your SQL in advance. We only support parameterization of predicate expressions. If there is limited (say < 50MB) of data associated to id, you can use SELECT * FROM table WHERE id = ?; and filter in your stored procedure logic. Not ideal - but not particularly difficult, either. If ID is the partition attribute for this table, filtering in Java should be fast.
Ryan.
What's your SQL logic for
runescape1
Dec 9, 2011
What's your SQL logic for updates then?
Wouldn't this rigidness make updates extremely difficult?
Say if you have a table with three rows update-able, you'd have to write 6 different procedures.
Updates
rbetts
Dec 9, 2011
What's your SQL logic for updates then?
Wouldn't this rigidness make updates extremely difficult?
Say if you have a table with three rows update-able, you'd have to write 6 different procedures.


You would do a select to read unchanged values and then a full row update.
6 different procedures
As a note, you can have multiple SQL statements planned per procedure and you can call them arbitrarily from within the procedure.
Efficiently planning arbitrary SQL inline with execution sites - or caching single partitioned AdHoc plans - or providing some higher level VoltTable manipulation Java libraries would all be great improvements that would make VoltDB application development easier.
We don't disagree with your observations and we look forward to making those things better. Generally, we set our priorities based on customer feedback.
Ryan.
Just had an idea for
runescape1
Dec 10, 2011
Just had an idea for updates.
For n update-able columns, we only need n procedures:
UPDATE table SET ? = ? WHERE id = ?;
UPDATE table SET ? = ?, ? = ? WHERE id = ?;
...
UPDATE table SET (? = ?)*n WHERE id = ?;
Would this be feasible and more efficient than the SELECT then full UPDATE?