Results 1 to 5 of 5

Thread: Query on converting oracle queries to VOLTDB

  1. #1
    New Member
    Join Date
    Nov 2013
    Posts
    9

    Query on converting oracle queries to VOLTDB

    Hi,

    I have the following 2 queries in oracle timesten.I am creating these 2 as preparedstatements in my Web application, passing the bind parameters dynamically and getting the results. I would like o know what is the best way to convert this into a query in VOLTDB?

    select PARENTTYPE, PARENTVALUE,'Parent' FROM table1 WHERE LASTOPTIMESTAMP > 0 AND CHILDTYPE=? AND CHILDVALUE=? union select CHILDTYPE ,CHILDVALUE,'child' FROM table1 WHERE LASTOPTIMESTAMP > 0 AND PARENTTYPE=? AND PARENTVALUE=? union select PARENTTYPE,parentvalue,'RootParent' FROM table1 WHERE LASTOPTIMESTAMP > 0 AND PARENTTYPE IN (?) AND PARENTVALUE=?

    select a.PARENTTYPE,a.PARENTVALUE,b.PARENTTYPE,b.PARENTVA LUE,c.PARENTTYPE,c.PARENTVALUE,d.PARENTTYPE,d.PARE NTVALUE,e.PARENTTYPE,e.PARENTVALUE,f.PARENTTYPE,f. PARENTVALUE,g.PARENTTYPE,g.PARENTVALUE,h.PARENTTYP E,h.PARENTVALUE from table1 h,table1 g,table1 f,table1 e,table1 d, table1 c,table1 b, table1 a where h.LASTOPTIMESTAMP(+) > 0 AND h.CHILDVALUE(+)=g.PARENTVALUE AND h.CHILDTYPE (+)=g.PARENTTYPE AND g.LASTOPTIMESTAMP(+) > 0 AND g.CHILDVALUE(+)=f.PARENTVALUE AND g.CHILDTYPE (+)=f.PARENTTYPE AND f.LASTOPTIMESTAMP(+) > 0 AND f.CHILDVALUE(+)=e.PARENTVALUE AND f.CHILDTYPE (+)=e.PARENTTYPE AND e.LASTOPTIMESTAMP(+) > 0 AND e.CHILDVALUE(+)=d.PARENTVALUE AND e.CHILDTYPE (+)=d.PARENTTYPE AND d.LASTOPTIMESTAMP(+) > 0 AND d.CHILDVALUE(+)=c.PARENTVALUE AND d.CHILDTYPE (+)=c.PARENTTYPE AND c.LASTOPTIMESTAMP(+) > 0 AND c.CHILDVALUE(+)=b.PARENTVALUE AND c.CHILDTYPE (+)=b.PARENTTYPE AND b.LASTOPTIMESTAMP(+) > 0 AND b.CHILDVALUE(+)=a.PARENTVALUE AND b.CHILDTYPE (+)=a.PARENTTYPE AND a.LASTOPTIMESTAMP(+) > 0 AND a.PARENTVALUE= ? AND a.PARENTTYPE= ?

  2. #2
    VoltDB Team
    Join Date
    Nov 2009
    Posts
    150
    So I think these queries will be tricky to get running in VoltDB 3.x, the currently shipping versions. In 4.0, we're introducing self-joins and self-unions. Our target for that release is early 2014.

    Besides the SQL, there a few ways to call queries from a web application.
    - In any of our native clients (including HTTP/JSON), they could be called as an Ad-Hoc query using the @AdHoc command. In the 3.x versions you would have to textually substitute for the ? on the client side. In 4.0 we expect to support parameterized @AdHoc calls. https://voltdb.com/docs/UsingVoltDB/sysprocadhoc.php
    - In JDBC, you could use the PreparedStatement class. Currently substitution of parameters must be typed, but we're working on implementing the setString methods for non-string types. http://voltdb.com/docs/UsingVoltDB/ProgLangjdbc.php
    - In our DDL file, you could create a callable and parameterized single-statement procedure. http://voltdb.com/docs/UsingVoltDB/D...eFreeProcs.php
    - You could create a full stored procedure with a mix of Java logic and SQL. http://voltdb.com/docs/UsingVoltDB/DesignProc.php

    One additional caveat to be aware of:
    Joins with more than 5 tables (including self-joins) require an explicit join ordering. Currently that's only possible to add in full stored procedures. We're working on adding that to DDL statement procedures. Some info here: http://voltdb.com/docs/PerfGuide/ExecPlansRead.php

    To get this logic running in 3.x, you would have to do multiple queries and then some post-processing in a Java stored procedure. This is inelegant, but may still perform well.

  3. #3
    New Member
    Join Date
    Nov 2013
    Posts
    9

    Some more questions

    Hi,

    As suggested by you I have converted the queries into a stored procedure class in VOLTDB -

    import java.util.ArrayList;
    import java.util.List;

    import org.voltdb.*;

    public class SelectFromIDAlias extends VoltProcedure {
    public final SQLStmt sql = new SQLStmt(
    "select PARENTTYPE, PARENTVALUE,'Parent' FROM idalias WHERE LASTOPTIMESTAMP > 0 AND CHILDTYPE=? AND CHILDVALUE=?;");
    public final SQLStmt sql1 = new SQLStmt(
    "select CHILDTYPE ,CHILDVALUE,'child' FROM idaliasview1 WHERE LASTOPTIMESTAMP > 0 AND PARENTTYPE=? AND PARENTVALUE=?;");
    public final SQLStmt sql2 = new SQLStmt(
    "select PARENTTYPE,parentvalue,'RootParent' FROM idaliasview2 WHERE LASTOPTIMESTAMP > 0 AND PARENTTYPE IN (?) AND PARENTVALUE=?;");

    // public final SQLStmt sql = new SQLStmt("select * from DUAL");
    // public VoltTable[] run(long childType1,String childValue1,long
    // childType2,String childValue2,long childType3,String childValue4)

    public VoltTable[] run(long childType1, String childValue1,
    long childType2, String childValue2, long childType3,
    String childValue3) throws VoltAbortException {
    voltQueueSQL(sql, childType1, childValue1);
    voltQueueSQL(sql1, childType2, childValue2);
    voltQueueSQL(sql2, childType3, childValue3);
    return voltExecuteSQL();
    }

    }


    1) Is this the correct way?
    2) Now I am getting the results of the 3 queries as PARENTTYPE, PARENTVALUE,'Parent',CHILDTYPE ,CHILDVALUE,'child',PARENTTYPE,parentvalue,'RootPa rent' . Instead of this, I want to read the results into 3 variables named -PARENTTYPE, PARENTVALUE, C3, how can I do that?
    3) Also when I try using jdbc interface ResultSet results = proc.executeQuery(), gives me only the first row, how to read the rest?

  4. #4
    VoltDB Team
    Join Date
    Nov 2009
    Posts
    150
    1) This is generally the right way, yes.

    2-3) JDBC will only currently return the first result from a list of VoltTables. What you're going to have to do is get the three results inside the procedure and programatically merge them into the result you are looking for. You can create a VoltTable with arbitrary schema (see constructor) and populate it will arbitrary data (addRow method). Then return a single table to the JDBC client.

    Let me know if this is helpful or not. Thanks.

  5. #5
    VoltDB Team
    Join Date
    Nov 2009
    Posts
    150
    I'm actually told that PreparedStatement.getMoreResults() will allow you to access multiple VoltTables in a JDBC result set.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •