Announcement

Collapse
No announcement yet.

How do you create a sql query of dynamic fields?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How do you create a sql query of dynamic fields?

    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?

  • #2
    VoltDB doesn't support the

    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.

    Comment


    • #3
      What's your SQL logic for

      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.

      Comment


      • #4
        Updates

        Originally posted by runescape1 View Post
        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.

        Comment


        • #5
          Just had an idea for

          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?

          Comment

          Working...
          X